JDBC Statement vs PreparedStatement

Once a connection is obtained we can interact with the database. The JDBC Statement and PreparedStatement interfaces define the methods and properties that enable you to send SQL commands and write/read data to/from your database.

• JDBC Statement: represents the base statements interface. In terms of efficiency, it is suitable to use Statement only when we know that we will not need to execute the SQL query multiple times. In contrast to PreparedStatement the Statement doesn't offer support for the parameterized SQL queries, which is an important protection from SQL injection attacks. Statement would be suitable for the execution of the DDL (Data Definition Language) statements, such as CREATE, ALTER, DROP.
• JDBC PreparedStatement: extends the Statement interface. In most cases it is more efficient (in the context of multiple executions) to use the PreparedStatement because the SQL statement that is sent gets pre-compiled. We can use PreparedStatement to safely provide values to the SQL parameters, through a range of setter methods setXXXXX (where XXXXX is type int,String, double, etc.).

Example

To see the difference between these two approaches I will use the code from how to select records using jdbc preparedstatement tutorial and add the following method to do the same thing but using Statement instead of PreparedStatement.

   private static void selectDataStatement(String id) throws Exception {
        Statement statement = null;
        try {
            String query = "SELECT * FROM `user` WHERE id = " + id;
            Connection connection = connect();
            statement = connection.createStatement();
            System.out.println("Query: " + query);
            ResultSet rs = statement.executeQuery(query);
            while (rs.next()) {

                long userId = rs.getLong("id");
                String name = rs.getString("name");
                String email = rs.getString("email");
                String password = rs.getString("password");
                String type = rs.getString("type");
                Date dob = rs.getDate("dob");

                System.out.println(
                        "[" + userId + ", " + name + ", " + email + ", " + password + ", " + type + ", " + dob + "]");

            }

        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (statement != null) {
                statement.close();
            }
        }
    }

The main method will be changed to this.

    public static void main(String[] args) {
        try {
            System.out.println("Select records.");
            /** Get the connection. */
            Connection connection = connect();

            if (connection != null) {
                /** select some data. */
                selectDataPreparedStatement("1");
                selectDataStatement("2");
                
                System.out.println("Records selected successfully.");

                /** Close the connection. */
                connection.close();
            }
        } catch (Exception e) {
            System.out.println("Something went wrong.");
        }
    }

The main method will select two lines using one method for each rows.

Output

Select records.
[1, John Doe, john@gmail.com, pass, REGULAR, 2016-03-24]
Query: SELECT * FROM `user` WHERE id = 2
[2, Admin Joe, joe@gmail.com, 12345, ADMIN, 2016-03-24]
Records selected successfully.

Based on the output you can notice that the result is similar. Where is the difference then?

SQL Injection

The difference is that PreparedStatement is SQL injection proof and will escape automatically the special characters. On the other hand Statement will not do that for you.

To see the difference run the program again, but with some modification. Change the following two lines:

selectDataPreparedStatement("1");
selectDataStatement("2");

selectDataPreparedStatement("2' OR id <>'0");
selectDataStatement("2' OR id <> '0");

Output

Select records.
com.mysql.jdbc.JDBC4PreparedStatement@5175f214: SELECT * FROM `user` WHERE id = '2\' OR id <>\'0'
Query: SELECT * FROM `user` WHERE id = '2' OR id <> '0'
[1, John Doe, john@gmail.com, pass, REGULAR, 2016-03-24]
[2, Admin Joe, joe@gmail.com, 12345, ADMIN, 2016-03-24]
[3, Subsriber Jill, jill@gmail.com, qwert, SUBSCRIBER, 2016-03-24]
Records selected successfully.

Conclusions

If you compare the two outputs you can notice the difference:

• when using PreparedStatement there is no result printed and the query look like this:

  SELECT * FROM `user` WHERE id = '2\' OR id <>\'0'

  with escaped special chars.
• when using Statement there is no special characters escaped and instead of returning only one object as expected, we got the entire table content. The query look like this:

  SELECT * FROM `user` WHERE id = '2' OR id <> '0'

  being a valid query.

References

• PreparedStatement
• Statement