JDBC - Updating a Result Set Examples

Previous
Next

Following is the example, which makes use of the ResultSet.CONCUR_UPDATABLE and ResultSet.TYPE_SCROLL_INSENSITIVE described in the Result Set tutorial. This example would explain INSERT, UPDATE and DELETE operation on a table.

It should be noted that tables you are working on should have Primary Key set properly.

Drop the table and Create the table Employees again as follows −

mysql> use TUTORIALSPOINT;
mysql> drop table Employees;
Query OK, 0 rows affected (0.08 sec)
mysql> create table Employees
    -> (
    -> id int primary key auto_increment,
    -> age int not null,
    -> first varchar (255),
    -> last varchar (255)
    -> );
Query OK, 0 rows affected (0.08 sec)
mysql>

Create Data Records

Finally you create few records in Employee table as follows −

mysql> INSERT INTO Employees(AGE, FIRST, LAST) VALUES (18, 'Zara', 'Ali');
Query OK, 1 row affected (0.05 sec)

mysql> INSERT INTO Employees(AGE, FIRST, LAST) VALUES (25, 'Mahnaz', 'Fatma');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO Employees(AGE, FIRST, LAST) VALUES (30, 'Zaid', 'Khan');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO Employees(AGE, FIRST, LAST) VALUES (28, 'Sumit', 'Mittal');
Query OK, 1 row affected (0.00 sec)

mysql>

Updating and Inserting Records using ResultSet Example

In this example, we've four static strings containing a dababase connection url, username, password and a SELECT query. Now using DriverManager.getConnection() method, we've prepared a database connection. Once connection is prepared, we've created a Statement object using connection.createStatement() method. While creating Statement Object, we've used ResultSet types as TYPE_SCROLL_INSENSITIVE and CONCUR_UPDATABLE, then using statement.executeQuery(), the SELECT Query is executed and result is stored in a resultset.

We've defined a method printResultSet(), where we're printing all records. In main method, we updated age of all records using updateDouble() method and using updateRow(), record is updated. Similarly we've marked resultset to insert a record using moveToInsertRow(). Then using updateString() and updateInt() methods, we've added new values and then using insertRow() method, we've commited the changes and hence inserted the record in the database. Now using printResultSet(), all records are printed again.

Copy and paste the following example in ResultSetExample.java, compile and run as follows −

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class ResultSetExample {
   static final String DB_URL = "jdbc:mysql://localhost/TUTORIALSPOINT";
   static final String USER = "guest";
   static final String PASS = "guest123";
   static final String QUERY = "SELECT id, first, last, age FROM Employees";

   public static void printResultSet(ResultSet rs) throws SQLException{
      // Ensure we start with first row
      rs.beforeFirst();
      while(rs.next()){
         // Display values
         System.out.print("ID: " + rs.getInt("id"));
         System.out.print(", Age: " + rs.getInt("age"));
         System.out.print(", First: " + rs.getString("first"));
         System.out.println(", Last: " + rs.getString("last"));
      }
      System.out.println();
   }

   public static void main(String[] args) {
      // Open a connection
      try(Connection conn = DriverManager.getConnection(DB_URL, USER, PASS);
         Statement stmt = conn.createStatement(
         ResultSet.TYPE_SCROLL_INSENSITIVE,
         ResultSet.CONCUR_UPDATABLE);
         ResultSet rs = stmt.executeQuery(QUERY);
      ) {		

         System.out.println("List result set for reference....");
         printResultSet(rs);

         // Loop through result set and add 5 in age
         // Move to Before first position so while-loop works properly
         rs.beforeFirst();
         //STEP 7: Extract data from result set
         while(rs.next()){
            // Retrieve by column name
            int newAge = rs.getInt("age") + 5;
            rs.updateDouble( "age", newAge );
            rs.updateRow();
         }
         System.out.println("List result set showing new ages...");
         printResultSet(rs);

         // Insert a record into the table.
         // Move to insert row and add column data with updateXXX()
         System.out.println("Inserting a new record...");
         rs.moveToInsertRow();
         rs.updateString("first","John");
         rs.updateString("last","Paul");
         rs.updateInt("age",40);
         // Commit row
         rs.insertRow();

         System.out.println("List result set showing new set...");
         printResultSet(rs);	

      } catch (SQLException e) {
         e.printStackTrace();
      } 
   }
}

Output

Now let us compile the above example as follows −

C:\>javac ResultSetExample.java
C:\>

When you run ResultSetExample, it produces the following result −

C:\>java ResultSetExample
List result set for reference....
ID: 1, Age: 18, First: Zara, Last: Ali
ID: 2, Age: 25, First: Mahnaz, Last: Fatma
ID: 3, Age: 30, First: Zaid, Last: Khan
ID: 4, Age: 28, First: Sumit, Last: Mittal

List result set showing new ages...
ID: 1, Age: 23, First: Zara, Last: Ali
ID: 2, Age: 30, First: Mahnaz, Last: Fatma
ID: 3, Age: 35, First: Zaid, Last: Khan
ID: 4, Age: 33, First: Sumit, Last: Mittal

Inserting a new record...
List result set showing new set...
ID: 1, Age: 23, First: Zara, Last: Ali
ID: 2, Age: 30, First: Mahnaz, Last: Fatma
ID: 3, Age: 35, First: Zaid, Last: Khan
ID: 4, Age: 33, First: Sumit, Last: Mittal
ID: 5, Age: 40, First: John, Last: Paul
C:\>

Inserting and Deleting Records using ResultSet Example

In this example, we've four static strings containing a dababase connection url, username, password and a SELECT query. Now using DriverManager.getConnection() method, we've prepared a database connection. Once connection is prepared, we've created a Statement object using connection.createStatement() method. While creating Statement Object, we've used ResultSet types as TYPE_SCROLL_INSENSITIVE and CONCUR_UPDATABLE, then using statement.executeQuery(), the SELECT Query is executed and result is stored in a resultset.

We've defined a method printResultSet(), where we're printing all records. In main method, we've marked resultset to insert a record using moveToInsertRow(). Then using updateString() and updateInt() methods, we've added new values and then using insertRow() method, we've commited the changes and hence inserted the record in the database. Now using printResultSet(), all records are printed. Now using ResultSet.last() method, we've moved to last record and deleted the same using ResultSet.deleteRow() method. Then all records are printed using printResultSet() method.

Copy and paste the following example in ResultSetExample.java, compile and run as follows −

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.PreparedStatement;

public class ResultSetExample {
   static final String DB_URL = "jdbc:mysql://localhost/TUTORIALSPOINT";
   static final String USER = "guest";
   static final String PASS = "guest123";
   static final String QUERY = "SELECT id, first, last, age FROM Employees";

   public static void printResultSet(ResultSet rs) throws SQLException{
      // Ensure we start with first row
      rs.beforeFirst();
      while(rs.next()){
         // Display values
         System.out.print("ID: " + rs.getInt("id"));
         System.out.print(", Age: " + rs.getInt("age"));
         System.out.print(", First: " + rs.getString("first"));
         System.out.println(", Last: " + rs.getString("last"));
      }
      System.out.println("----------------------------");
   }

   public static void main(String[] args) {
      // Open a connection
      try(Connection conn = DriverManager.getConnection(DB_URL, USER, PASS);
         Statement stmt = conn.createStatement(
            ResultSet.TYPE_SCROLL_INSENSITIVE,
            ResultSet.CONCUR_UPDATABLE);
            ResultSet rs = stmt.executeQuery(QUERY);
         ) {

         System.out.println("Inserting a new record...");
         rs.moveToInsertRow();
         rs.updateString("first","Manoj");
         rs.updateString("last","Jain");
         rs.updateInt("age",40);
         // The code below will insert the row in database
         rs.insertRow();

         System.out.println("List result set showing added row.");
         printResultSet(rs);
         // Deleting the added row.
         rs.last();
         rs.deleteRow();

         System.out.println("List result set showing row after deletion.");

         printResultSet(rs);

      } catch (SQLException e) {
         e.printStackTrace();
      } 
   }
}

Output

Now let us compile the above example as follows −

C:\>javac ResultSetExample.java
C:\>

When you run ResultSetExample, it produces the following result −

C:\>java ResultSetExample
Inserting a new record...
List result set showing added row.
ID: 1, Age: 18, First: Zara, Last: Ali
ID: 2, Age: 25, First: Mahnaz, Last: Fatma
ID: 3, Age: 30, First: Zaid, Last: Khan
ID: 4, Age: 28, First: Sumit, Last: Mittal
ID: 6, Age: 40, First: Manoj, Last: Jain
----------------------------
List result set showing row after deletion.
ID: 1, Age: 18, First: Zara, Last: Ali
ID: 2, Age: 25, First: Mahnaz, Last: Fatma
ID: 3, Age: 30, First: Zaid, Last: Khan
ID: 4, Age: 28, First: Sumit, Last: Mittal
----------------------------

C:\>
jdbc-result-sets.htm
Print Page
Previous
Next
Advertisements