This post talks about Insert and Select Rows with JdbcRowSet Objects.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 this post we will learn how to Insert and Select 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.
ClientTest.java class to show how to Insert and Select Rows with JdbcRowSet Objects
|
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 |
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; public class ClientTest { 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("-------------------------------------------------------"); addEmployee(jdbcRowSet); System.out.println("-------------------------------------------------------"); showEmployeesInfo(jdbcRowSet); } private static void addEmployee(JdbcRowSet jdbcRowSet) throws SQLException { //Moves the cursor to the insert row. jdbcRowSet.moveToInsertRow(); jdbcRowSet.updateString("employee_name", "tommy"); jdbcRowSet.updateDouble("salary", 560000.00); jdbcRowSet.updateTimestamp("date_of_joining", new java.sql.Timestamp(new java.util.Date().getTime())); jdbcRowSet.updateBigDecimal("bonus", new BigDecimal(4000.00)); //Inserts the contents of the insert row into this ResultSet object and into the database jdbcRowSet.insertRow(); System.out.println("A new EMployee is added.."); //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 ClientTest.java program you will look below output on eclipse console:
|
1 2 3 4 5 6 7 8 9 10 11 12 |
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 ------------------------------------------------------- A new EMployee is added.. ------------------------------------------------------- 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 5 tommy 560000.0 tommy.cs@siffy.com 4000 |
Above output shows initially we read all the rows from database by calling showEmployeesInfo method after that we inserted a row with primary key 5
If you query employee_table you will see one row is inserted with primary key 5
That’s all about this topic Insert and Select Rows with JdbcRowSet Objects
You May Also Like:
Transaction basic concept
JDBC transaction management example
Update and Delete Rows with JdbcRowSet Objects
Jdbc RowSetListener example
If you have any feedback or suggestion please feel free to drop in blow comment box.