PreparedStatement

PreparedStatement is more convenient and effective than the Statement interface. The PreparedStatement interface inherits the Statement interface. The SQL statement that the Statement interface passes to the DBMS is a simple string. The DBMS compiles the string to be understood by the DBMS and executes it. PreparedStatement improves performance by delivering precompiled SQL statements to the DBMS. Not only does it improve performance, but it is also convenient. You can put question marks (?) in an SQL statement, and you can set a value for the question mark. Therefore, you can execute the same SQL statement by replacing the question marks with values.

Our goal is to execute the following SQL:

UPDATE NAMECARD SET COMPANY = ? WHERE NO = ?

Refer to the JDBC programming order to complete the main() of UsingPrepareStatement.java.

  1. Loading a JDBC Driver
  2. Getting a Connection
  3. Execute SQL
  4. [If the SQL statement is a select statement, use the ResultSet to process data.]
  5. Returning Resources
UsingPrepareStatement.java
package net.java_school.jdbc.test;

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

public class UsingPreparedStatement {
  static final String URL = "jdbc:oracle:thin:@127.0.0.1:1521:XE";
  static final String USER = "scott";
  static final String PASS = "tiger";
	
  public static void main(String[] args) {
    try {
      Class.forName("oracle.jdbc.driver.OracleDriver");
    } catch (ClassNotFoundException e) {
      e.printStackTrace();
    }
		
    Connection con = null;
    PreparedStatement pstmt = null;
		
    String sql = "UPDATE NAMECARD SET COMPANY = ? WHERE NO = ?";

    try {
      con = DriverManager.getConnection(URL, USER, PASS);
      
      pstmt = con.prepareStatement(sql);
      pstmt.setString(1, "Google Inc");
      pstmt.setInt(2, 1);
      pstmt.executeUpdate();
      
    } catch (SQLException e) {
      e.printStackTrace();
      System.out.println(sql);
    } finally {
      try {
        pstmt.close();
      } catch (SQLException e) {
        e.printStackTrace();
      }
      try {
        con.close();
      } catch (SQLException e) {
        e.printStackTrace();
      }
    }
  }

}

The followings are the difference from using Statement.

pstmt = con.prepareStatement(sql);

Unlike obtaining a Statement, you must pass an SQL statement with a Java string as an argument to the prepareStatement() method to get a PreparedStatement.

pstmt.executeUpdate();

Unlike the executeUpdate() of the Statement interface, when you execute SQL, do not pass the SQL statement string to the executeUpdate() method as an argument.

pstmt = con.prepareStatement(sql);
pstmt.setString(1, "Google Inc");
pstmt.setInt(2, 1);

If the SQL statement contains a question mark, you must set a value for the question mark using the PrepareStatement's setXXX() method before execute. The XXX part of the setXXX() method means Java data type. The first parameter to the setXXX() method is the order in which question marks appear. The second parameter of the setXXX() method is the value to replace the question mark.