In previous post How to retrieve data from JDBC resultset in java we learnt how to read record/records from database using Read only ResultSet. In this post we will learn how to make a ResultSet updatable so that we can insert or update a record in database table.
Project structure in eclipse:
SQL Query to create employee_table and insert few rows in MySQL (jdbcdb schema)
(Note:Make sure you have created employee_table and inserted few records in jdbcdb database schema)
DB.sql
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
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`) VALUES ('Martin','[email protected]',71000,'2017-05-17 00:00:00',400); INSERT INTO `employee_table` (`employee_name`,`email`,`salary`,`date_of_joining`,`bonus`) VALUES ('Paul','[email protected]',91000,'2017-05-18 00:00:00',800); INSERT INTO `employee_table` (`employee_name`,`email`,`salary`,`date_of_joining`,`bonus`) VALUES ('Sam','[email protected]',76000,'2017-05-16 00:00:00',300); INSERT INTO `employee_table` (`employee_name`,`email`,`salary`,`date_of_joining`,`bonus`) VALUES ('Paul','[email protected]',60000,'2017-05-17 00:00:00',400); |
After running above db script in your MySQL jdbcdb schema employee_table will have following content.
DBUtil.java class which is responsible to connect with MySQL database.
|
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 |
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 + "]"; } } |
Now let’s run our ClientTest.java class to update an email adress of an employee whose employee id is 2.
You can modified this program as per your requirement.
|
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 |
package com.infotech.client; import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; import com.infotech.util.DBUtil; /** * @author KK JavaTutorials *JDBC program to update a record in database using JDBC ResultSet */ public class ClientTest { public static void main(String[] args) throws SQLException { int empId = 2; updateEmailById(empId, newEmail); } public static void updateEmailById(int empId, String newEmail) { String SQL = "SELECT *FROM employee_table"; try (Connection connection = DBUtil.getConnection(); PreparedStatement ps = connection.prepareStatement(SQL,ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_UPDATABLE)) { ResultSet rs = ps.executeQuery(); while (rs.next()) { int employeeId = rs.getInt("employee_id"); if(employeeId ==empId ){ rs.updateString("email", newEmail); rs.updateRow(); System.out.println("Email is updated.."); } } } catch (Exception e) { e.printStackTrace(); } } } |
After running ClientTest.java program you will look below output on eclipse console:
|
1 |
Email is updated.. |
Finally you have to query your database employee_table to see whether record is updated or not.
That’s all about How to update a Row in a Database Table Using an updatable ResultSet
If you have any feedback or suggestion please feel free to drop in blow comment box.