Blog Archive

Java JDBC preparedstatement example for insert, select, update, delete

The PreparedStatement interface inherits from Statement but differs from it in two ways:
  1. Instances of PreparedStatement contain an SQL statement that has already been compiled. This is what makes a statement "prepared."
  2. The SQL statement contained in a PreparedStatement object may have one or more IN parameters. An IN parameter is a parameter whose value is not specified when the SQL statement is created. Instead, the statement has a question mark ("?") as a placeholder for each IN parameter. The "?" is also known as a parameter marker. An application must set a value for each question mark in a prepared statement before executing the prepared statement.

Because PreparedStatement objects are precompiled, their execution can be faster than that of Statement objects. Consequently, an SQL statement that is executed many times is often created as a PreparedStatement object to increase efficiency.

Being a subclass of Statement, PreparedStatement inherits all the functionality of Statement. In addition, it adds a set of methods that are needed for setting the values to be sent to the database in place of the placeholders for IN parameters. Also, the three methods execute, executeQuery, and executeUpdate are modified so that they take no argument.

The Statement forms of these methods (the forms that take an SQL statement parameter) should never be used with a PreparedStatement object. 

I am going to use Table OHEAD in DB2 database for all my examples here. You can connect to any other database such as MYSQL, SQL Server, Oracle etc using the same JDBC preparedstatement  syntax. Here is my table definition for OHEAD
Field      Type      Size    Key    Text            
X1DEL      Char       1             Status          
X1CMP      Zoned      3,0     1     Company No.     
X1ORD      Zoned      9,0     2     Order Number    
X1TYPE     Char       1             Transaction Type
X1ORDT     Zoned      8,0           Order Date      
X1CUST     Char      10             Customer Number 
X1PO       Char      20             PO Number       
X1OTOT     Zoned     11,2           Order Total   

Sample Java code for PreparedStatement

package com.as400samplecode;

import java.io.FileInputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.util.Properties;

public class JDBCPreparedStatements {

    static Properties props;
    public static final int SQL_INSERT = 1;
    public static final int SQL_SELECT = 2;
    public static final int SQL_UPDATE = 3;
    public static final int SQL_DELETE = 4;

    public static void main(String[] args) {

        int transactionType = 0;
        String propertiesFileName = "";


        if (args.length < 2)
        {
            JDBCPreparedStatements jdbcPreparedStatements = new JDBCPreparedStatements();
            System.err.println("Usage: java "+ jdbcPreparedStatements.getClass().getName()+
            " Properties_File_Name Transaction_Type");
            System.exit(1);
        }

        propertiesFileName = args[0].trim();
        transactionType = Integer.parseInt(args[1].trim());

        switch (transactionType) {
       
        //JDBC Insert PreparedStatement
        case SQL_INSERT: 
            doInsert(propertiesFileName);    
            break;
       
        //JDBC Select PreparedStatement   
        case SQL_SELECT: 
            doSelect(propertiesFileName); 
            break;
       
        //JDBC Update PreparedStatement   
        case SQL_UPDATE: 
            doUpdate(propertiesFileName);    
            break;
       
        //JDBC Delete PreparedStatement   
        case SQL_DELETE: 
            doDelete(propertiesFileName); 
            break;
       
        default: 
            break;
        }
    }

    private static void doInsert(String propertiesFileName){

        Connection Db2Conn = null;
        PreparedStatement Db2Stmt = null;
        String sql = null;

        try {

            props = new Properties();
            props.load(new FileInputStream("properties/" + propertiesFileName.trim()));

            // Database connection string
            String DRIVER = "com.ibm.as400.access.AS400JDBCDriver";        
            String URL = "jdbc:as400://" + props.getProperty("local_system").trim() + ";naming=system;errors=full";

            // Connect to Database                                         
            Class.forName(DRIVER);                                       
            Db2Conn = DriverManager.getConnection(URL,props.getProperty("userId").trim(),props.getProperty("password").trim());   

            // Db2 SQL statement to insert data 
            sql = "INSERT into OHEAD (X1DEL,X1CMP,X1ORD,X1TYPE,X1ORDT,X1CUST,X1PO,X1OTOT) VALUES(?,?,?,?,?,?,?,?)";
            Db2Stmt = Db2Conn.prepareStatement(sql);

            Db2Stmt.setString(1,"A");
            Db2Stmt.setInt(2,1);
            Db2Stmt.setInt(3,123);
            Db2Stmt.setString(4,"O");
            Db2Stmt.setInt(5,20110628);
            Db2Stmt.setString(6,"ABCD");
            Db2Stmt.setString(7,"TEST PO");
            Db2Stmt.setDouble(8, 99.99);
            Db2Stmt.executeUpdate();

            Db2Stmt.setString(1,"A");
            Db2Stmt.setInt(2,1);
            Db2Stmt.setInt(3,124);
            Db2Stmt.setString(4,"O");
            Db2Stmt.setInt(5,20110629);
            Db2Stmt.setString(6,"ABCD");
            Db2Stmt.setString(7,"TEST PO2");
            Db2Stmt.setDouble(8, 19.99);
            Db2Stmt.executeUpdate();

        }

        // Handle any errors that may have occurred.
        catch (Exception e) {
            e.printStackTrace();
        }
        finally {
            if (Db2Stmt != null) try { Db2Stmt.close(); } catch(Exception e) {}
            if (Db2Conn != null) try { Db2Conn.close(); } catch(Exception e) {}
        } 

    }

    private static void doSelect(String propertiesFileName){

        Connection Db2Conn = null;
        PreparedStatement Db2Stmt = null;
        ResultSet Db2Rs = null;
        String sql = null;

        try {

            props = new Properties();
            props.load(new FileInputStream("properties/" + propertiesFileName.trim()));

            // Database connection string
            String DRIVER = "com.ibm.as400.access.AS400JDBCDriver";        
            String URL = "jdbc:as400://" + props.getProperty("local_system").trim() + ";naming=system;errors=full";

            // Connect to Database                                         
            Class.forName(DRIVER);                                       
            Db2Conn = DriverManager.getConnection(URL,props.getProperty("userId").trim(),props.getProperty("password").trim());   

            // Db2 SQL statement to insert data 
            sql = "SELECT * from OHEAD where X1CMP = ? and X1ORD = ?";
            Db2Stmt = Db2Conn.prepareStatement(sql);
            Db2Stmt.setInt(1, 1);
            Db2Stmt.setInt(2, 123);
            Db2Rs = Db2Stmt.executeQuery();

            // Get MetaData for Db2 table
            ResultSetMetaData metaData = Db2Rs.getMetaData();
            int colCount = metaData.getColumnCount();

            // Iterate through the Db2 data in the result set 
            while (Db2Rs.next()) {
                for (int i = 0; i < colCount; i++) {
                    System.out.println(metaData.getColumnName(i + 1) + ": " + Db2Rs.getString(i+1));
                }   
            }

        }

        // Handle any errors that may have occurred.
        catch (Exception e) {
            e.printStackTrace();
        }
        finally {
            if (Db2Rs != null) try { Db2Rs.close(); } catch(Exception e) {}
            if (Db2Stmt != null) try { Db2Stmt.close(); } catch(Exception e) {}
            if (Db2Conn != null) try { Db2Conn.close(); } catch(Exception e) {}
        } 

    }

    private static void doUpdate(String propertiesFileName){

        Connection Db2Conn = null;
        PreparedStatement Db2Stmt = null;
        String sql = null;

        try {

            props = new Properties();
            props.load(new FileInputStream("properties/" + propertiesFileName.trim()));

            // Database connection string
            String DRIVER = "com.ibm.as400.access.AS400JDBCDriver";        
            String URL = "jdbc:as400://" + props.getProperty("local_system").trim() + ";naming=system;errors=full";

            // Connect to Database                                         
            Class.forName(DRIVER);                                       
            Db2Conn = DriverManager.getConnection(URL,props.getProperty("userId").trim(),props.getProperty("password").trim());   

            // Db2 SQL statement to insert data 
            sql = "UPDATE OHEAD set X1ORDT = ?,X1CUST = ?,X1PO = ?,X1OTOT = ? where X1CMP = ? and X1ORD = ?";
            Db2Stmt = Db2Conn.prepareStatement(sql);

            Db2Stmt.setInt(1,20110630);
            Db2Stmt.setString(2,"ABAB");
            Db2Stmt.setString(3,"TEST PO1");
            Db2Stmt.setDouble(4, 101.99);
            Db2Stmt.setInt(5,1);
            Db2Stmt.setInt(6,123);
            Db2Stmt.executeUpdate();

            // Batch Update
            Db2Stmt.setInt(1,20110629);
            Db2Stmt.setString(2,"AAAA");
            Db2Stmt.setString(3,"TEST PO1A");
            Db2Stmt.setDouble(4, 101.01);
            Db2Stmt.setInt(5,1);
            Db2Stmt.setInt(6,123);
            Db2Stmt.addBatch();

            Db2Stmt.setInt(1,20110629);
            Db2Stmt.setString(2,"BBBB");
            Db2Stmt.setString(3,"TEST PO1B");
            Db2Stmt.setDouble(4, 101.02);
            Db2Stmt.setInt(5,1);
            Db2Stmt.setInt(6,124);
            Db2Stmt.addBatch();

            Db2Stmt.executeBatch();

        }

        // Handle any errors that may have occurred.
        catch (Exception e) {
            e.printStackTrace();
        }
        finally {
            if (Db2Stmt != null) try { Db2Stmt.close(); } catch(Exception e) {}
            if (Db2Conn != null) try { Db2Conn.close(); } catch(Exception e) {}
        } 

    }

    private static void doDelete(String propertiesFileName){

        Connection Db2Conn = null;
        PreparedStatement Db2Stmt = null;
        String sql = null;

        try {

            props = new Properties();
            props.load(new FileInputStream("properties/" + propertiesFileName.trim()));

            // Database connection string
            String DRIVER = "com.ibm.as400.access.AS400JDBCDriver";        
            String URL = "jdbc:as400://" + props.getProperty("local_system").trim() + ";naming=system;errors=full";

            // Connect to Database                                         
            Class.forName(DRIVER);                                       
            Db2Conn = DriverManager.getConnection(URL,props.getProperty("userId").trim(),props.getProperty("password").trim());   

            // Db2 SQL statement to insert data 
            sql = "DELETE from OHEAD where X1CMP = ? and X1ORD = ?";
            Db2Stmt = Db2Conn.prepareStatement(sql);

            Db2Stmt.setInt(1,1);
            Db2Stmt.setInt(2,123);
            Db2Stmt.executeUpdate();

        }

        // Handle any errors that may have occurred.
        catch (Exception e) {
            e.printStackTrace();
        }
        finally {
            if (Db2Stmt != null) try { Db2Stmt.close(); } catch(Exception e) {}
            if (Db2Conn != null) try { Db2Conn.close(); } catch(Exception e) {}
        } 

    }

}