In previous couple of posts CRUD(Create,Read,Update and Delete) Operation using JDBC Statement
and Reading data from database using JDBC Statement we have learnt how to work with JDBC Statement.
When You want to execute literal or hard coded SQL then we do go for JDBC Statement.
But most of scenarios/times in application you would like to deal with dynamic SQL Query with place holder(?) then we will have to use JDBC PreparedStatement.
In this post we will learn how to perform CRUD operation using JDBC PreparedStatement.
Project structure in eclipse:
SQL Query to create employee_table in MySQL (jdbcdb schema)
If you run below SQL Script it will create employee_table with single record/row.
|
1 2 3 4 5 6 7 8 9 10 11 12 |
CREATE TABLE `employee_table` ( `employee_id` int(11) NOT NULL auto_increment, `employee_name` varchar(60) NOT NULL, `email` varchar(45) NOT NULL, `salary` double default NULL, `date_of_joining` datetime default NULL, `bonus` decimal(10,0) default NULL, PRIMARY KEY (`employee_id`) ) ; INSERT INTO employee_table(employee_name,email,salary,date_of_joining,bonus) |
DBUtil.java class which is responsible to connect with MySQL database.
This util class uses getConnection() method of java.sql.DriverManager class
public static Connection getConnection(String url,String user, String password) throws SQLException
This method takes three parameters database URL,username and password.
Here database username =”root”,password = “root” what password had supplied during MYSQL database installation time and finally database URL =“jdbc:mysql://localhost:3306/jdbcdb” Where jdbc is the API, mysql is the database, localhost is the server name on which mysql database server is running, we may also use IP address instead machine name, 3306 is the port number and jdbcdb(Make you you have created this schema in MySQL database) is the database name. You may use any database name, in that case, You need to replace the jdbcdb with your database name.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 |
package com.infotech.util; import java.sql.Connection; import java.sql.DriverManager; import java.sql.SQLException; public class DBUtil { private static final String DB_DRIVER_CLASS="com.mysql.jdbc.Driver"; private static final String DB_USERNAME="root"; private static final String DB_PASSWORD="root"; private static final String DB_URL ="jdbc:mysql://localhost:3306/jdbcdb"; private static Connection connection = null; static{ try { Class.forName(DB_DRIVER_CLASS); connection = DriverManager.getConnection(DB_URL, DB_USERNAME, DB_PASSWORD); } catch (ClassNotFoundException | SQLException e) { e.printStackTrace(); } } public static Connection getConnection(){ return connection; } } |
Model class Employee.java
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 |
package com.infotech.model; import java.math.BigDecimal; import java.util.Date; public class Employee { private int employee_id; private String employeeName; private String email; private Double salary; private Date doj; private BigDecimal bonus; public int getEmployee_id() { return employee_id; } public void setEmployee_id(int employee_id) { this.employee_id = employee_id; } public String getEmployeeName() { return employeeName; } public void setEmployeeName(String employeeName) { this.employeeName = employeeName; } public String getEmail() { return email; } public void setEmail(String email) { this.email = email; } public Double getSalary() { return salary; } public void setSalary(Double salary) { this.salary = salary; } public Date getDoj() { return doj; } public void setDoj(Date doj) { this.doj = doj; } public BigDecimal getBonus() { return bonus; } public void setBonus(BigDecimal bonus) { this.bonus = bonus; } @Override public String toString() { return "Employee [employee_id=" + employee_id + ", employeeName=" + employeeName + ", email=" + email + ", salary=" + salary + ", doj=" + doj + ", bonus=" + bonus + "]"; } } |
DAO Interface and It’s implementation
|
1 2 3 4 5 6 7 8 9 10 11 |
package com.infotech.dao; import java.util.List; import com.infotech.model.Employee; public interface EmployeeDAO { public abstract void createEmployee(Employee employee); public abstract Employee getEmployeeById(Integer employeeId); public abstract void updateEmployeeEmailById(String newEmail,Integer employeeId); public abstract void deleteEmployeeById(Integer employeeId); public abstract List<Employee> getAllEmployeesInfo(); } |
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 |
package com.infotech.dao.impl; import java.math.BigDecimal; import java.sql.Connection; import java.sql.Date; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.util.ArrayList; import java.util.List; import com.infotech.dao.EmployeeDAO; import com.infotech.model.Employee; import com.infotech.util.DBUtil; public class EmployeeDAOImpl implements EmployeeDAO { @Override public void createEmployee(Employee employee) { String SQL = "INSERT INTO employee_table(employee_name,email,salary,date_of_joining,bonus)VALUES(?,?,?,?,?)"; try(Connection connection = DBUtil.getConnection();PreparedStatement ps = connection.prepareStatement(SQL)) { ps.setString(1, employee.getEmployeeName()); ps.setString(2, employee.getEmail()); ps.setDouble(3, employee.getSalary()); ps.setDate(4, new Date(employee.getDoj().getTime())); ps.setBigDecimal(5, employee.getBonus()); int executeUpdate = ps.executeUpdate(); if(executeUpdate ==1){ System.out.println("Employee is crated.."); } } catch (Exception e) { e.printStackTrace(); } } @Override public Employee getEmployeeById(Integer employeeId) { Employee employee = null; String SQL = "SELECT *FROM employee_table WHERE employee_id=?"; try(Connection connection = DBUtil.getConnection();PreparedStatement ps = connection.prepareStatement(SQL)) { ps.setInt(1, employeeId); ResultSet rs = ps.executeQuery(); if (rs.next()) { int empId = rs.getInt("employee_id"); String eName = rs.getString("employee_name"); String email = rs.getString("email"); Double salary = rs.getDouble("salary"); BigDecimal bonus = rs.getBigDecimal("bonus"); Date date = rs.getDate("date_of_joining"); employee = new Employee(); employee.setEmployeeName(eName); employee.setBonus(bonus); employee.setDoj(date); employee.setEmail(email); employee.setEmployee_id(empId); employee.setSalary(salary); } } catch (Exception e) { e.printStackTrace(); } return employee; } @Override public void updateEmployeeEmailById(String newEmail, Integer employeeId) { String SQL = "UPDATE employee_table set email=? WHERE employee_id=?;"; try(Connection connection = DBUtil.getConnection();PreparedStatement ps = connection.prepareStatement(SQL)) { ps.setString(1, newEmail); ps.setInt(2,employeeId); int executeUpdate = ps.executeUpdate(); if(executeUpdate ==1){ System.out.println("Employee email is updated.."); } } catch (Exception e) { e.printStackTrace(); } } @Override public void deleteEmployeeById(Integer employeeId) { String SQL = "DELETE FROM employee_table WHERE employee_id=?;"; try(Connection connection = DBUtil.getConnection();PreparedStatement ps = connection.prepareStatement(SQL)) { ps.setInt(1,employeeId); int executeUpdate = ps.executeUpdate(); if(executeUpdate ==1){ System.out.println("Employee is deleted with ID::"+employeeId); } } catch (Exception e) { e.printStackTrace(); } } @Override public List<Employee> getAllEmployeesInfo() { List<Employee> empList = new ArrayList<>(); String SQL = "SELECT *FROM employee_table"; try(Connection connection = DBUtil.getConnection();PreparedStatement ps = connection.prepareStatement(SQL)) { ResultSet rs = ps.executeQuery(); while (rs.next()) { int empId = rs.getInt("employee_id"); String eName = rs.getString("employee_name"); String email = rs.getString("email"); Double salary = rs.getDouble("salary"); BigDecimal bonus = rs.getBigDecimal("bonus"); Date date = rs.getDate("date_of_joining"); Employee employee = new Employee(); employee.setEmployeeName(eName); employee.setBonus(bonus); employee.setDoj(date); employee.setEmail(email); employee.setEmployee_id(empId); employee.setSalary(salary); empList.add(employee); } } catch (Exception e) { e.printStackTrace(); } return empList; } } |
Client class ClientTest.java
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 |
package com.infotech.client; import java.math.BigDecimal; import java.util.Date; import java.util.List; import com.infotech.dao.EmployeeDAO; import com.infotech.dao.impl.EmployeeDAOImpl; import com.infotech.model.Employee; public class ClientTest { public static void main(String[] args) { EmployeeDAO employeeDAO = new EmployeeDAOImpl(); Employee employee = getEmployee(); employeeDAO.createEmployee(employee); //getEmployeeById(employeeDAO); //employeeDAO.updateEmployeeEmailById("[email protected]", 4); //employeeDAO.deleteEmployeeById(6); //getAllEmployeesInfo(employeeDAO); } private static void getAllEmployeesInfo(EmployeeDAO employeeDAO) { List<Employee> empList = employeeDAO.getAllEmployeesInfo(); for (Employee employee : empList) { System.out.println(employee); } } private static void getEmployeeById(EmployeeDAO employeeDAO) { Employee employee2 = employeeDAO.getEmployeeById(4); if(employee2 != null){ System.out.println(employee2); }else{ System.out.println("Employee does not exist.."); } } private static Employee getEmployee() { Employee employee = new Employee(); employee.setBonus(new BigDecimal(290)); employee.setDoj(new Date()); employee.setEmployeeName("John"); employee.setSalary(90000.00); return employee; } } |
That’s all about CRUD operation using JDBC PreparedStatement.
You May Also Like:
What is Java Database Connectivity (JDBC)?
Types of JDBC drivers in java?
JDBC MySQL database Connection example
JDBC database connection using property resource(property file)
CRUD(Create,Read,Update and Delete) Operation using JDBC Statement
Reading data from database using JDBC Statement
If you have any feedback or suggestion please feel free to drop in below comment box.
how to validate in update that if id exists then only ask for name, dept etc
if id does not exist then say no such id