Dynamic SQL with PreparedStatement JDBC

In the following program, an Employee record is inserted using PreparedStatement(executing dynamic SQL statements). PreparedStatement along with CallableStatement is fully discussed in Difference between Statement, PreparedStatement, CallableStatement.

Example on PreparedStatement JDBC

import java.sql.*;
import java.io.*;

public class PreparedDemo
{
  public static void main(String args[]) throws Exception
  {
    Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
    Connection con = DriverManager.getConnection("jdbc:odbc:snrao", "scott", "tiger");
                                         // Statement is to be replaced with PreparedStatement
    PreparedStatement pst = con.prepareStatement("insert into Employee(empid, empname, empsal) values(?,?,?)");
                                         // taking values from keyboard
    BufferedReader br = new BufferedReader(new InputStreamReader(System.in));

    System.out.println("Enter Employee ID:");
    int id = Integer.parseInt(br.readLine());

    System.out.println("Enter Employee Name:");
    String name = br.readLine();

    System.out.println("Enter Employee Salary:");
    double salary = Double.parseDouble(br.readLine( ) );
                                         // values are stored in local variables, id, name and salary
                                         // setXXX() methods
    pst.setInt(1, id);
    pst.setString(2, name);
    pst.setDouble(3, salary);

    pst.executeUpdate();                 // PreparedStatement JDBC executed

    System.out.println(name + " Record is inserted");

    pst.close();
    con.close();
  }
}

Output screenshot on PreparedStatement JDBC

prepareStatement(String) method of Connection interface returns an object of PreparedStatement interface. It is to be remembered that createStatement(String) method Connection interface returns an object of Statement interface.

setInt(), setString() and setDouble() are the methods of PreparedStatement interface. Note, getInt(), getString() and getDouble() are the methods of ResultSet interface.

PreparedStatement execution is faster as PreparedStatement is treated as "partially compiled" statement. At runtime, only values are compiled and inserted.

PreparedStatement pst = con.prepareStatement(“insert into Employee(empid, empname, empsal) values(?,?,?)”);

The above statement can be replaced with the following. It is possible only when all the table fields are inserted.

PreparedStatement pst = con.prepareStatement(“insert into Employee values(?,?,?)”);

Practice more programs to have command over PreparedStatement used very much in realtime applications.