This post talks about Update and Delete Rows with JdbcRowSet Object.Based on the JDBC documentation, a JdbcRowSet object is extended from a ResultSet object and defined to be updatable by default. This means that you can use JdbcRowSet objects to update, delete or insert rows back to target tables in the database server.
In previous post Insert and Select Rows with JdbcRowSet Objects we already learnt how insert and select records or rows in database.
In this post we will learn how to Update and Delete Rows with JdbcRowSet Objects
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 15 16 17 18 |
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`) INSERT INTO `employee_table` (`employee_name`,`email`,`salary`,`date_of_joining`,`bonus`) INSERT INTO `employee_table` (`employee_name`,`email`,`salary`,`date_of_joining`,`bonus`) INSERT INTO `employee_table` (`employee_name`,`email`,`salary`,`date_of_joining`,`bonus`) |
After running above db script in your MySQL jdbcdb schema employee_table will have following content.
UpdateRowClientTest.java class to show update a row using JdbcRowSet
|
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 |
package com.infotech.client; import java.math.BigDecimal; import java.sql.SQLException; import javax.sql.rowset.JdbcRowSet; import javax.sql.rowset.RowSetFactory; import javax.sql.rowset.RowSetProvider; /** * @author KK JavaTutorials *JDBC program to show update a row using JdbcRowSet */ public class UpdateRowClientTest { 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"; public static void main(String[] args) throws SQLException { //Creates a new instance of a RowSetFactory implementation. RowSetFactory rowSetFactory = RowSetProvider.newFactory(); //Creates a new instance of a JdbcRowSet. JdbcRowSet jdbcRowSet = rowSetFactory.createJdbcRowSet(); jdbcRowSet.setUrl(DB_URL); jdbcRowSet.setUsername(DB_USERNAME); jdbcRowSet.setPassword(DB_PASSWORD); //Sets this RowSet object's command property to the given SQL query. jdbcRowSet.setCommand("SELECT *FROM employee_table"); jdbcRowSet.execute(); showEmployeesInfo(jdbcRowSet); System.out.println("-------------------------------------------------------"); updateEmployeeEmailById(jdbcRowSet); System.out.println("-------------------------------------------------------"); showEmployeesInfo(jdbcRowSet); } private static void updateEmployeeEmailById(JdbcRowSet jdbcRowSet) throws SQLException { int employeeId = 4; while (jdbcRowSet.next()) { int empId = jdbcRowSet.getInt("employee_id"); if(employeeId ==empId ){ jdbcRowSet.updateRow(); } } //Moves the cursor to the front of this ResultSet object, just before the first row. jdbcRowSet.beforeFirst(); } private static void showEmployeesInfo(JdbcRowSet jdbcRowSet) throws SQLException { while (jdbcRowSet.next()) { int empId = jdbcRowSet.getInt("employee_id"); String eName = jdbcRowSet.getString("employee_name"); String email = jdbcRowSet.getString("email"); Double salary = jdbcRowSet.getDouble("salary"); BigDecimal bonus = jdbcRowSet.getBigDecimal("bonus"); System.out.println(empId + "\t" + eName + "\t" + salary + "\t" + email + "\t" + bonus); } //Moves the cursor to the front of this ResultSet object, just before the first row. jdbcRowSet.beforeFirst(); } } |
After running UpdateRowClientTest.java program you will look below output on eclipse console:
|
1 2 3 4 5 6 7 8 9 10 |
1 Martin 71000.0 martin.cs2017@yahoo.com 400 2 Paul 91000.0 paul.cs2017@gmail.com 800 3 Sam 76000.0 sam.cs2014@gmail.com 300 4 Paul 60000.0 paul.cs2009@yahoo.com 400 ------------------------------------------------------- ------------------------------------------------------- 1 Martin 71000.0 martin.cs2017@yahoo.com 400 2 Paul 91000.0 paul.cs2017@gmail.com 800 3 Sam 76000.0 sam.cs2014@gmail.com 300 4 Paul 60000.0 paul.cs2017@siffy.com 400 |
Above eclipse console output says employee email is updated whose id is 4
You can query database table to check the same.
DeleteRowClientTest.java class to show delete a row using JdbcRowSet
|
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 |
package com.infotech.client; import java.math.BigDecimal; import java.sql.SQLException; import javax.sql.rowset.JdbcRowSet; import javax.sql.rowset.RowSetFactory; import javax.sql.rowset.RowSetProvider; /** * @author KK JavaTutorials *JDBC program to show delete a row using JdbcRowSet */ public class DeleteRowClientTest { 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"; public static void main(String[] args) throws SQLException { //Creates a new instance of a RowSetFactory implementation. RowSetFactory rowSetFactory = RowSetProvider.newFactory(); //Creates a new instance of a JdbcRowSet. JdbcRowSet jdbcRowSet = rowSetFactory.createJdbcRowSet(); jdbcRowSet.setUrl(DB_URL); jdbcRowSet.setUsername(DB_USERNAME); jdbcRowSet.setPassword(DB_PASSWORD); //Sets this RowSet object's command property to the given SQL query. jdbcRowSet.setCommand("SELECT *FROM employee_table"); jdbcRowSet.execute(); showEmployeesInfo(jdbcRowSet); System.out.println("-------------------------------------------------------"); deleteEmployeeById(jdbcRowSet); System.out.println("-------------------------------------------------------"); showEmployeesInfo(jdbcRowSet); } private static void deleteEmployeeById(JdbcRowSet jdbcRowSet) throws SQLException { int employeeId = 1; while (jdbcRowSet.next()) { int empId = jdbcRowSet.getInt("employee_id"); if(employeeId ==empId ){ jdbcRowSet.deleteRow(); } } jdbcRowSet.beforeFirst(); } private static void showEmployeesInfo(JdbcRowSet jdbcRowSet) throws SQLException { while (jdbcRowSet.next()) { int empId = jdbcRowSet.getInt("employee_id"); String eName = jdbcRowSet.getString("employee_name"); String email = jdbcRowSet.getString("email"); Double salary = jdbcRowSet.getDouble("salary"); BigDecimal bonus = jdbcRowSet.getBigDecimal("bonus"); System.out.println(empId + "\t" + eName + "\t" + salary + "\t" + email + "\t" + bonus); } jdbcRowSet.beforeFirst(); } } |
After running UpdateRowClientTest.java program you will look below output on eclipse console:
|
1 2 3 4 5 6 7 8 9 |
1 Martin 71000.0 martin.cs2017@yahoo.com 400 2 Paul 91000.0 paul.cs2017@gmail.com 800 3 Sam 76000.0 sam.cs2014@gmail.com 300 4 Paul 60000.0 paul.cs2017@siffy.com 400 ------------------------------------------------------- ------------------------------------------------------- 2 Paul 91000.0 paul.cs2017@gmail.com 800 3 Sam 76000.0 sam.cs2014@gmail.com 300 4 Paul 60000.0 paul.cs2017@siffy.com 400 |
Above eclipse console output shows all employees info before and after a employee row deleted from database table.
Let’s query database table to verify the same.
That’s all about this topic Update and Delete Rows with JdbcRowSet Objects
You May Also Like:
Transaction basic concept
JDBC transaction management example
Insert and Select Rows with JdbcRowSet Objects
Jdbc RowSetListener example
If you have any feedback or suggestion please feel free to drop in blow comment box.