The PreparedStatement object represents a precompiled SQL statement. A PreparedStatement can be used multiple times and mitigates SQL injection attacks. A PreparedStatement can be a SELECT, INSERT, UPDATE, or DELETE statement.

To execute a prepared statement, you can use the generic execute method of the Statement class. This section describes how to execute a prepared statement.

1. Instantiate a PreparedStatement object with the prepareStatement method of the Connection class.

   See Connecting from Code to create the connection.

2. Declare parameters by calling the PreparedStatement's corresponding setter method. Note that the parameter indices start from one.
3. Call the PreparedStatement's execute method to execute the statement.
4. Call the PreparedStatement's getResultSet method to pull the results into a ResultSet object.
5. Call ResultSet.next to iterate over the result set. Use the ResultSetMetaData class to obtain column information about the result set. To instantiate a ResultSetMetaData object, call the ResultSet's getMetaData method.

Select

The following example shows how to execute a SELECT prepared statement:

String query = "SELECT * FROM Lead WHERE Id=? AND FirstName=?";
PreparedStatement pstmt = conn.prepareStatement(query);
pstmt.setString(1, "XXX");
pstmt.setString(2, "YYY");
boolean ret = pstmt.execute();
if (ret) {
  ResultSet rs=pstmt.getResultSet();
  while(rs.next()) {
    for(int i=1;i<=rs.getMetaData().getColumnCount();i++) {
      System.out.println(rs.getMetaData().getColumnLabel(i) +"="+rs.getString(i));
    }
  }
} 

Insert

To execute an insert, you can use the generic execute method or the executeUpdate method, as shown in the following example.

To obtain the generated keys for new records, specify Statement.RETURN_GENERATED_KEYS in the prepareStatement call. After executing the statement, call the getGeneratedKeys method.

String query = "INSERT INTO Lead (Id, FirstName) VALUES (?,?)";
PreparedStatement pstmt = conn.prepareStatement(query,Statement.RETURN_GENERATED_KEYS );
pstmt.setString(1, "XXX");
pstmt.setString(2, "YYY");
int count = pstmt.executeUpdate();
ResultSet rs = pstmt.getGeneratedKeys();
while(rs.next()) {
  for(int i=1;i<=rs.getMetaData().getColumnCount();i++) {
    System.out.println(rs.getMetaData().getColumnLabel(i) +"="+rs.getString(i));
  }
}

Upsert

Executing an upsert is the same as an insert: use the generic execute method or executeUpdate.

To obtain the generated keys for new records, specify Statement.RETURN_GENERATED_KEYS in the prepareStatement call. After executing the statement, call the getGeneratedKeys method.

String query = "UPSERT INTO Lead (Id, FirstName) VALUES (?,?)";
PreparedStatement pstmt = conn.prepareStatement(query,Statement.RETURN_GENERATED_KEYS );
pstmt.setString(1, "XXX");
pstmt.setString(2, "YYY");
int count = pstmt.executeUpdate();
ResultSet rs = pstmt.getGeneratedKeys();
while(rs.next()) {
  for(int i=1;i<=rs.getMetaData().getColumnCount();i++) {
    System.out.println(rs.getMetaData().getColumnLabel(i) +"="+rs.getString(i));
  }
}

Update

To execute an update, you can use the generic execute method or the executeUpdate method, as shown in the following example. The executeUpdate method returns the affected rows. Or, call getUpdateCount.

String query = "UPDATE Lead SET FirstName = ? WHERE Id=?";
PreparedStatement pstmt = conn.prepareStatement(query);
pstmt.setString(1, "XXX");
pstmt.setString(2, "YYY");
int count = pstmt.executeUpdate();
System.out.println("Affected rows: "+count);

Delete

To execute a delete, you can use the generic execute method or the executeUpdate method, as shown in the following example. The executeUpdate method returns the affected rows. Or, call getUpdateCount.

String query = "DELETE FROM Lead WHERE Id = ?";
PreparedStatement pstmt = conn.prepareStatement(query);
pstmt.setString(1, "XXX");
int count = pstmt.executeUpdate();
System.out.println("Affected rows: "+count);