PreparedStatement

The Statement instance passes a simple string to the DBMS. The DBMS compiles that 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 set a value for each 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 question marks, you must set a value for the question mark using the PrepareStatement's setXXX() method before executing.

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.