Prepared Statements with JDBC

March 9th, 2009
When your Java-application is talking to a database, there are several do’s and don’ts, patterns and anti-patterns you should be aware of. The probably most important ones are the proper use of indices (which is a database-structure-thing), the pooling of connections to the database (which is a Java-thing) and the reasonable use of Prepared Statements (which is also be done in Java). A blog-post concerning the first two issues might come sometimes in the future, but for now I will focus on Prepared Statements in Java using JDBC.

Because I am a fan of tutorials I’ll start with some practical examples before I’ll dive deeper into the pros and cons of Prepared Statements.

Let’s assume you have a table like this:

CREATE TABLE `userTable` (
`id` int(11) NOT NULL auto_increment,
`name` varchar(255) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;


Your application’s task: select the name for a specific id.

Listing 1 shows the code with the use of a regular Statement.

 Java |  copy code |? 
01
import java.sql.Connection;
02
import java.sql.ResultSet;
03
import java.sql.SQLException;
04
import java.sql.Statement;
05
 
06
public class StatementSelect {
07
 
08
      private Connection      connection;
09
      private Statement       statement;
10
      private ResultSet        resultSet;
11
 
12
      public StatementSelect(Connection connection) {
13
            this.connection = connection;
14
      }
15
 
16
      /**
17
       * Selects the name for a specific id from the userTable in the database
18
       *
19
       * @param id
20
       * @return
21
       * @throws SQLException
22
       */
23
      public String select(int id) throws SQLException {
24
            final String nameColumn = "name";
25
            String ret = "";
26
 
27
            String query = String.format("SELECT %s FROM userTable WHERE id = %s",
28
                        nameColumn, id); 
29
 
30
            if (statement == null || statement.isClosed())
31
                 statement = connection.createStatement();
32
 
33
           try {
34
                  resultSet = statement.executeQuery(query);
35
                  /*
36
                   * Let's assume 'id' is a primary key --> There is at most one entry
37
                   * for a specific id.
38
                   */
39
                  if (resultSet.next())
40
                        ret = resultSet.getString(nameColumn);
41
 
42
           } finally {
43
                  try {
44
                        if (resultSet != null)
45
                             resultSet.close();
46
                  } catch (SQLException e) {
47
                        e.printStackTrace();
48
                 }
49
            }
50
           return ret;
51
      }
52
 
53
     public void cleanUp() {
54
            try {
55
                  if (statement != null)
56
                        statement.close();
57
           } catch (SQLException e) {
58
                  e.printStackTrace();
59
            }
60
      }
61
}


Listing 2 makes use of a Prepared Statement.

 Java |  copy code |? 
01
import java.sql.Connection;
02
import java.sql.PreparedStatement;
03
import java.sql.ResultSet;
04
import java.sql.SQLException;
05
 
06
public class PreparedStatementSelect {
07
 
08
      private Connection           connection;
09
      private PreparedStatement    preparedStatement;
10
      private ResultSet            resultSet;
11
 
12
      public PreparedStatementSelect(Connection connection) {
13
            this.connection = connection;
14
      }
15
 
16
      /**
17
       * Selects the name for a specific id from the userTable in the database
18
       *
19
       * @param id
20
       * @return
21
       * @throws SQLException
22
       */
23
      public String select(int id) throws SQLException {
24
            final String nameColumn = "name";
25
            String ret = "";
26
 
27
            String query = String.format("SELECT %s FROM userTable WHERE id = ?",
28
                        nameColumn);
29
 
30
            if (preparedStatement == null || preparedStatement.isClosed())
31
                  preparedStatement = connection.prepareStatement(query);
32
 
33
            preparedStatement.setInt(1, id);
34
 
35
            try {
36
                  resultSet = preparedStatement.executeQuery();
37
 
38
                  /*
39
                   * Let's assume 'id' is a primary key --> There is at most one entry
40
                   * for a specific id.
41
                   */
42
                  if (resultSet.next())
43
                        ret = resultSet.getString(nameColumn);
44
 
45
            } finally {
46
                  try {
47
                        if (resultSet != null)
48
                             resultSet.close();
49
                  } catch (SQLException e) {
50
                        e.printStackTrace();
51
                  }
52
            }
53
 
54
            return ret;
55
      }
56
 
57
      public void cleanUp() {
58
            try {
59
                  if (preparedStatement != null)
60
                        preparedStatement.close();
61
            } catch (SQLException e) {
62
                  e.printStackTrace();
63
            }
64
      }
65
}



Did you spot the differences?

  • Line 27: the queries are not identical: instead of directly specifying for which id the name should be selected in listing 1, a ‘?’-placeholder is used in listing 2 when preparing the Prepared Statement.

  • Line 31: listing 1 creates a regular Statement-object; a Prepared Statement is created in listing 2.

  • Line 33: to tell your database for which id you’d like to have the name, an additional step is needed in listing 2: the ‘?’ that was used to prepare the Prepared Statement is replaced by the correct id.

  • Line 34/36: in listing 1 the query is used as parameter in the executeQuery()-call. Listing 2 doesn’t use a parameter.


So now let’s talk about the two major advantages that come with the use of Prepared Statements:

  • If the above query is executed multiple times, the query in the regular statement has to be compiled by the database every time before execution. With Prepared Statements it has to be compiled only once (but this compilation needs an extra roundtrip to the database). This once-vs-multiple-compilation may save you time, depending on the structure of your requests. As a rule of thumb: The more often you execute a query with the same structure, the more likely it is that you gain a performance boost through Prepared Statements.


  • In regular queries every String in the where-condition has to be escaped by surrounding it with ”. This job is done for you by Prepared Statements automatically.

    The consequences? You don’t have to worry about escaping and you are less vulnerable to SQL-injections.


But there are some points you should have in mind, when going for Prepared Statements:

  • Prepared Statements are built per connection. This means, if you have multiple connections to your database and the same query should be executed a couple of times, you should make sure, that the Prepared Statement for that query is always using the same connection. Otherwise you have to recreate the Prepared Statement for every connection, which produces overhead.

  • The intention of Prepared Statements is reusability, so make your Prepared Statements reusable (like I did in listing 2)! A lot of overhead would be produced, if you recreate the Prepared Statement every time when the query is executed.


  • If you’re executing a query only once, maybe regular Statements will better fit your needs: keep in mind that the preparation of Prepared Statements needs an extra database-call. But don’t forget to validate your input first to prevent SQL-injection!


  • If you’re using MySQL < 5.1.17, the query cache is not used by Prepared Statements. So if you have few modifications on your datasets and you execute the same query over and over, have a close look if the use of Prepared Statements really increases performance.


  • If you think about parametrizing the tablename (e.g. ‘SELECT name FROM ?’): forget it! That won’t work!


So after this short trip through Prepared Statements I hope some of the advantages and disadvantes have become obvious.

If you find any mistakes in my post, or if you are of a different opinion, feel free to leave a comment or use the contact-site.


Happy coding,

Tino.

Share/Save/Bookmark

tino JDBC, Java , , ,