Java Program to Insert Records using PreparedStatement

The PreparedStatement is used for precompiling SQL statements that might contain input parameters. In this post, we will develop a Java or JDBC program to insert records into a table using PreparedStatement. The PreparedStatement is the object of a JDBC driver software supplied Java class that implements java.sql.PreparedStatement(I). The java.sql.PreparedStatement(I) is extended from java.sql.Statement(I).

Here we will use the Oracle database, but you can work with any database with the required information.

To insert records we need a table, we can also use an existing table. Here we are creating a table “product” which will have the details of product_number (pid), name of the product, price of the product, and quantity of the product.

Create table in Oracle database,

create table product
(
     pid number(10) primary key, 
     pname varchar2(15), 
     price float,
     quantity number(10)
);

How to Write Code

1. Prepare SQL query with positional parameter/place holder/place resolver (?)

String query = "INSERT INTO PRODUCT VALUES(?,?,?,?)";

Here ? symbol represents the parameter for which values can be set later. The first ? represents the first column i.e. product_number, second ? represents the name of the product, third ? represents price, and fourth ? represents the quantity.

The SQL query will remain the same for the entire application so, we generally take SQL query as string constants at the top of the class. The other reasons are:-
a) To identify them easily and to modify them easily.
b) To see them un-modifiable in other parts of the code,
c) To access them in other parts of code without an object.

The static final variables are called constants. Generally, these variables name will be taken in uppercase letters so to differentiate them from other variables.

  1. Send SQL query to Database software.
    Make that SQL query as pre-compiled SQL query in database software and get PreparedStatement object representing the SQL query.
PreparedStatement ps = con.prepareStatement(query);

The pre-compiled SQL query is ready and now, it will go to database only for execution.

3. Set input values to pre-compiled SQL query parameters(?) using setXxx(-) methods.

ps.setInt(1,1001); // product id

// product name
ps.setString(2, “Fan”); 

// price of product
ps.setString(3, “500”); 

ps.setFloat(4, 2); // quantity

4. Execute the pre-compiled SQL query in database software,

int result = ps.executeUpdate();

5. Process the result,

if(result == 0) 
System.out.println("Not inserted");
else
System.out.println("record inserted");

6. Execute the above pre-compiled SQL query for multiple times either with same or difference values (repeat 3 to 5 steps)

7. Close the JDBC objects.

ps.close();
con.close();

Java / JDBC program to insert records using preparedstatement

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

public class InertRecord {

   // SQL query
   private static final String INSERT_PRODUCT_QUERY = 
               "INSERT INTO PRODUCT VALUES(?,?,?,?)";

   public static void main(String[] args ) {

      // declare variables
      Scanner scan = null;
      int item=0, 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.print("Enter the number of items: ");
            item = 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(INSERT_PRODUCT_QUERY);

         // read multiple set of inputs from end-user
         // set input values and execute the query
         if(scan != null && ps != null) {
            for(int i=0; i < item; i++) {

               // read inputs
               System.out.println("\nEnter item-"+ (i+1) +
                               " details, ");
               System.out.print("Product ID: ");
               pid = scan.nextInt();
               System.out.print("Name: ");
               name = scan.next();
               System.out.print("Price: ");
               price = scan.nextFloat();
               System.out.print("Quantity: ");
               qty = scan.nextInt();

               // set input values to query parameters
               ps.setInt(1, pid);
               ps.setString(2, name);
               ps.setFloat(3, price);
               ps.setInt(4, qty);

               // execute the query
               result = ps.executeUpdate();
           }
        }

        // process the result
        if(result == 0)
            System.out.println("\nRecords not inserted");
        else
            System.out.println("\nRecords inserted"+
                                     " 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 number of items: 3

Enter item-1 details,
Product ID: 102
Name: Chair
Price: 500
Quantity: 5

Enter item-2 details,
Product ID: 2051
Name: Fan
Price: 999
Quantity: 2

Enter item-3 details,
Product ID: 111
Name: Table
Price: 1500
Quantity: 1

Records inserted successfully.

Now, verify that records are inserted in Oracle database or not?

SQL> select * from product;

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

We have successfully inserted the records in the table. In the next JDBC program examples we will update, and display the records of the table.

If you want to know the specific reason for the exception that is raised, we can print the SQL error code based message by calling getErrorCode(). Note that the product id is the primary key, so it can’t be duplicated, if we try to insert the same record with existing product id then the Oracle server returns an error: ORA-00001: unique constraint (SCOTT.SYS_C0012404) violated. Similarly, when we try to insert too large value for product id then we get an error: ORA-12899

try {
   // JDBC code
} catch(SQLException se) {

   if(se.getErrorCode()==1)
      System.out.println("Product Id already exist");

   else if(se.getErrorCode()==12899)
      System.out.println("Value is too large for the column");

   else
      System.out.println("Unknown problem from database");

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

When the JDBC application sends a non-select query to database software that query executes in database software in the auto committed environment. It means the query execution result will be committed immediately so that we can’t rollback it later. While working to a non-select query, there is no need of ResultSet object. All operation in the JDBC takes place on database software through JDBC driver.

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!

Also learn,

Leave a Comment

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