Why use PreparedStatement in Java JDBC

Why use PreparedStatement in Java JDBC – Example Tutorial

PreparedStatement in Java is one of several ways to execute SQL queries using JDBC API. Java provides Statement,

PreparedStatement and CallableStatement for executing queries. Out of these three, Statement is used for general purpose queries, PreparedStatement is used for executing parametric query and CallableStatement is used for executing Stored Procedures. PreparedStatement is also a popular topic in java interviews. Questions like Difference between Statement and PreparedStatement in Java and How to prevent SQL Injection attacks in Java are popular java interview questions. In this Java JDBC tutorial we will see why should you use use PreparedStatement in Java, What are the major advantages of using PreparedStatement in Java and how PreparedStatement prevents SQL Injection attacks in Java.

This article is in continuation of my earlier post on database and java like 4 tips to improve performance of Java application with database and Difference between truncate and delete in SQL.If you haven’t read  them already you may found  those tutorial useful and interesting.

What is PreparedStatement in Java

PreparedStatement is a class in java.sql package and allows Java programmer to execute SQL queries by using JDBC package. You can get PreparedStatement object by calling connection.prepareStatement() method.SQL queries passed to this method goes to Database for pre-compilation if JDBC driver supports it. If it doesn't than pre-compilation occurs when you execute prepared queries. Prepared Statement queries are pre-compiled on database and there access plan will be reused to execute further queries which allows them to execute much quicker than normal queries generated by Statement object. Here is an example of how to use PreparedStatement in Java:

public class PreparedStmtExample {

    public static void main(String args[]) throws SQLException {
        Connection conn = DriverManager.getConnection("mysql:\\localhost:1520", "root", "root");
        PreparedStatement preStatement = conn.prepareStatement("select distinct loan_type from loan where bank=?");
        preStatement.setString(1, "Citibank");
   
        ResultSet result = preStatement.executeQuery();
     
        while(result.next()){
            System.out.println("Loan Type: " + result.getString("loan_type"));
        }      
    }
} 
Output:
Loan Type: Personal Loan
Loan Type: Auto Loan
Loan Type: Home Loan
Loan Type: Gold Loan

In this example of PreparedStatement same query and access path will be used if you pass a different parameter  e.g.

"Standard Charted" or "HSBC". ResultSet returned by prepared statement execution is of "TYPE_FORWARD_ONLY" but can be customized by using overloaded method of prepareStatement().

Benefits of Java Prepared Statement

PreparedStatement in Java JDBC offers several benefits and it’s a recommended way to execute SQL queries in any enterprise Java application or in production code. Here are few advantages of using PreparedStatement in Java:

1. PreparedStatement allows you to write dynamic and parametric query.

By using PreparedStatement in Java you can write parametrized sql queries and send different parameters by using same sql queries which is lot better than creating different queries. Here is an example of parametric query written using PreparedStatement in java:

select interest_rate from loan where loan_type=?

Now you can run this query for any loan type e.g. "personal loan”, "home loan" or "gold loan". This example of SELECT query is called parametric or parametrized query because it can be invoked with different parameter. Here “?” is used as place holder for parameter.

2. PreparedStatement is faster than Statement in Java

One of the major benefits of using PreparedStatement is better performance. PreparedStatement gets pre compiled

In database and there access plan is also cached in database, which allows database to execute parametric query written using prepared statement much faster than normal query because it has less work to do. You should always try to use PreparedStatement in production JDBC code to reduce load on database. In order to get performance benefit its worth noting to use only parametrized version of sql query and not with string concatenation. Out of following two examples of SELECT queries, first example of SELECT query  will not offer any performance benefit:

SQL Query 1: PreparedStatement with String concatenation

String loanType = getLoanType();
PreparedStatement prestmt = conn.prepareStatement("select banks from loan where loan_type=" + loanType);

SQL Query 2: Parameterized query using PreparedStatement

PreparedStatement prestmt = conn.prepareStatement("select banks from loan where loan_type=?");
prestmt.setString(1,loanType);

Second SQL query is correct use of PreparedStatement in Java and give better performance than SQL query1.

3. PreparedStatement prevents SQL Injection attacks in Java

If you have been working in Java web application you must be familiar with infamous SQL Injection attacks, last year Sony got victim of SQL injection and compromised several Sony play station user data. In SQL Injection attack, malicious user pass SQL meta-data combined with input which allowed them to execute sql query of there choice, If not validated or prevented before sending query to database. By using parametric queries and PreparedStatement you prevent many forms of SQL injection because all the parameters passed as part of place-holder will be escaped automatically by JDBC Driver. Though It’s worth remembering that in above example of two PreparedStatement only second example will prevent SQL injection attacks and first example is not secure with SQL injection.

4. At last PreparedStatement queries are more readable and secure than cluttered string concatenated queries.

Limitation of Java PreparedStatement

Despite of being very useful PreparedStatement also has few limitations:

1. In order to prevent SQL Injection attacks in Java, PreparedStatement doesn't allow multiple values for one placeholder (?) who makes it tricky to execute SQL query with IN clause. Following example of SQL query with IN clause using prepared Statement will not work in Java:

select * from loan where loan_type IN ( ?)
preparedSatement.setString(1, "'personal loan', 'home loan', 'gold loan'");

Though there are some workarounds and ways to execute IN queries using PreparedStatement but those are

rather tricky or have performance impact.

Important points on PreparedStatement in Java

Here are few important points about PreparedStatement Class in Java, worth remembering:

1. PreparedStatement in Java allows you to write parametrized query which gives better performance than Statement class in Java.

2. In case of PreparedStatement, Database use an already compiled and defined access plan, this allows prepared statement query to run faster than normal query.

3. Parametrized query written using PreparedStatement in Java prevents many common SQL Injection attacks.

4. PreparedStatement allows you to write dynamic query in Java.

5. PreparedStatement are associated with java.sql.Connection object, once you drop a connection all PreparedStatement  associated with that connection will be dropped by Database.

6. "?" is also called placeholder or IN parameter in Java.

7. PreparedStatement query return FORWARD_ONLY ResultSet, so you can only move in one direction Also concurrency level of ResultSet would be "CONCUR_READ_ONLY".

8. All JDBC Driver doesn't support pre compilation of SQL query in that case query is not sent to database when you call prepareStatement(..) method instead they would be sent to database when you execute PreparedStatement query.

9. Index of placeholder or parameter starts with "1" and not with "0", which is common cause of java.sql.SQLException: Invalid column index . So in a PreparedStatement t of two placeholder, first will be referred by index 1 and second will be reference by index 2.

These were the reasons Why PreparedStatement in java is very popular and useful. You can still use Statement object for test programmers but consider PreparedStatement before moving to production.