PreparedStatement

• PreparedStatement represents parameterized queries.

String sql = "SELECT * FROM students WHERE name=?";
PreparedStatement stmt = con.prepareStatement(sql);
System.out.print("Enter name to find: ");
String name = sc.next();
stmt.setString(1, name);
ResultSet rs = stmt.executeQuery();
while(rs.next()) {
    int roll = rs.getInt("roll");
    String name = rs.getString("name");
    double marks = rs.getDouble("marks");
    System.out.printf("%d, %s, %.2f\\n", roll, name, marks);
}

• The same PreparedStatement can be used for executing multiple queries. There is no syntax checking repeated. This improves the performance.

MySQL Programming steps -- PreparedStatement

1. Add JDBC driver into project/classpath.

   • Java project -> Properties -> Java Build Path -> Libraries -> Add External Jars -> select MySQL JDBC driver jar -> Apply and Close.

2. Load and register driver class.

   Class.forName("com.mysql.cj.jdbc.Driver");
   

3. Create database connection.

   Connection con = DriverManager.getConnection("jdbc:mysql://localhost:3306/dbname", "dbuser", "dbpassword");
   

4. Create PreparedStatement with (paramterized) SQL query.

   String sql = "sql query with ?";
   PreparedStatement stmt = con.prepareStatement(sql);
   

5. Set param values, execute the query and process the result.

   stmt.setInt(1, val1); // set 1st param ? value
   stmt.setString(2, val2); // set 2nd param ? value
   // for non-SELECT queries
   int count = stmt.executeUpdate();
   // for SELECT queries
   ResultSet rs = stmt.executeQuery();
   while(rs.next()) {    
   		int val1 = rs.getInt("col1");    
   		String val2 = rs.getString("col2");    
   		// ...
   }
   rs.close();
   

6. Close statement and connection.

   stmt.close();
   con.close();
   

java.sql.PreparedStatement

• Inherited from java.sql.Statement.
• Represents parameterized SQL statement/query.
• The query parameters (?) should be set before executing the query.
• Same query can be executed multiple times, with different parameter values.
• This speed up execution, because query syntax checking is done only once.