Java Program to Update Records using PreparedStatement

In the previous JDBC tutorial, we had developed a Java program to insert records into the table using PreparedStatement object. In this post, we will develop a Java program to update the record using the PreparedStatement object.

In this tutorial we are working with the Oracle database, you can work with any database with the required information. In our Oracle database, we have a “Product” table.

SQL> desc product;

 Name       Null?    Type
 ---------- -------- -------------
 PID        NOT NULL NUMBER(10)
 PNAME               VARCHAR2(15)
 PRICE               FLOAT(126)
 QUANTITY            NUMBER(10)

In the product table we have some records,

SQL> select * from product;

 PID  PNAME   PRICE   QUANTITY
---- -------- ------ ------
 102  Chair   500     5
2051  Fan     999     2
 111  Table   1500    1

Program description:- Develop a JDBC program to update records of product table using PreparedStatement object.

To update the record of a row we need at least one column value to select that row and later update the required values. Required SQL query,

SQL> update product set pname='Book', 
     price = 20, quantity = 5 
     where pid = 111;

The SQL query that goes to database software without input values and becomes parsed or compiled SQL query in database software irrespective of whether it will be executed or not is called pre-compiled SQL query. PreparedStatement object of the Java application represents this pre-compiled SQL query of database software. We can use that object to set input values to SQL query, to execute SQL query and to fetch the output of SQL query for one or multiple times. PreparedStatement object is good to execute the same SQL query for multiple times either with input values or with output values.

Java program to update record using PreparedStatement object

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.Scanner;

public class SelectTest {

   // SQL query
   private static final String UPDATE_PRODUCT_QUERY = 
          "UPDATE PRODUCT SET PNAME = ?, PRICE = ?, QUANTITY = ?"
          + "WHERE PID = ? ";

   public static void main(String[] args ) {

      // declare variables
      Scanner scan = null;
      int pid = 0, qty = 0;
      String name = null;
      float price = 0.0f;
      Connection con = null;
      PreparedStatement ps = null;
      int result = 0;

      try {
         // read input 
         scan = new Scanner(System.in);
         if(scan != null) {
            System.out.println("Enter the existing product ID"
            		+ " to update: ");
            pid = scan.nextInt();
            System.out.println("Enter new details,");
            System.out.print("Name of the product: ");
            name = scan.next();
            System.out.print("Price: ");
            price = scan.nextFloat();
            System.out.print("Quantity: ");
            qty = scan.nextInt();
         }

         // establish the connection
         con = DriverManager.getConnection(
               "jdbc:oracle:thin:@localhost:1521:knowprogram",
               "scott", "tiger");

         // compile SQL query and 
         // store it in PreparedStatemet object
         if(con != null)
            ps = con.prepareStatement(UPDATE_PRODUCT_QUERY);

         // set input values and execute query
         if(ps != null) {
           // set input values to query parameters
           ps.setString(1, name);
           ps.setFloat(2, price);
           ps.setInt(3, qty);
           ps.setInt(4, pid);
           // execute the query
           result = ps.executeUpdate();
         }

         // process the result
         if(result == 0)
           System.out.println("Records not updated");
         else
           System.out.println("Records updated"+
                                " successfully");

      } catch(SQLException se) {
         se.printStackTrace();
      } catch(Exception e) {
         e.printStackTrace();
      } // end of try-catch block 

      finally {
         // close JDBC objects
         try {
            if(ps != null) ps.close();
         } catch(SQLException se) {
            se.printStackTrace();
         }
         try {
            if(con != null) con.close();
         } catch(SQLException se) {
            se.printStackTrace();
         }
         try {
            if(scan != null) scan.close();
         } catch(Exception e) {
            e.printStackTrace();
         }
     }

   } //end of main
} //end of class

Output:-

Enter the existing product ID to update:
102
Enter new details,
Name of the product: Bed
Price: 2000
Quantity: 1
Records updated successfully

Enter the existing product ID to update:
1000
Enter new details,
Name of the product: Book
Price: 50
Quantity: 2
Records not updated

The product id 1000 is not available in the product table so, the records are not updated.

Now, let us verify that records are updated or not. Execute SQL query to display the table details,

SQL> select * from product;

 PID PNAME  PRICE  QUANTITY
---- ------ ------ --------
 102 Bed    2000   1
2051 Fan    999    2
 111 Table  1500   1

By default the JDBC program are executed in autocommit mode. So, no need to explicitly commit the records.

In the next JDBC example, we will develop a program to display the records of the table. Also see:-


If you enjoyed this post, share it with your friends. Do you want to share more information about the topic discussed above or do you find anything incorrect? Let us know in the comments. Thank you!

Leave a Comment

Your email address will not be published. Required fields are marked *