PreparedStatement

PreparedStatement is more convenient and effective than the Statement interface. The PreparedStatement interface inherits the Statement interface. The SQL statement that the statement 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 ? In an SQL statement, and you can set a value in ?. Therefore, you can execute the same SQL statement by replacing the value with ?. Refer to the JDBC programming order to complete the main method of UsingPrepareStatement.java. Our goal is to execute the following SQL:

UPDATE NAMECARD SET COMPANY = ? WHERE NO = ?
  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();
			}
		}
	}

}

I will explain the different parts of 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 object.

pstmt.executeUpdate();

Unlike Statement, when you execute SQL, do not pass the SQL statement as Java 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 the value of the question mark using the PrepareStatement's setXXX() method before the statement is executed. The XXX part of the setXXX() method must be determined by the Java data type that maps to the data type of the column. 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.