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 a 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 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 the database and java like 4 tips to improve the 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 tutorials useful and interesting.

What is PreparedStatement in Java

PreparedStatement is a class in java.sql package and allows Java programmers to execute SQL queries by using the JDBC package. You can get a PreparedStatement object by calling connection.prepareStatement() method.SQL queries passed to this method go to Database for pre-compilation if JDBC driver supports it. 

If it doesn't then pre-compilation occurs when you execute prepared queries. Prepared Statement queries are pre-compiled on the database and their access plan will be reused to execute further queries which allow 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, the path will be used if you pass a different parameter like "Standard Charted" or "HSBC". ResultSet returned by prepared statement execution is of "TYPE_FORWARD_ONLY" but can be customized by using an 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 a few advantages of using PreparedStatement in Java:

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

By using PreparedStatement in Java you can write parameterized SQL queries and send different parameters by using the same SQL queries which is a lot better than creating different queries. Here is an example of a 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 the SELECT query is called parametric or parametrized query because it can be invoked with different parameters. Here “?” is used as a place holder for a 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 their access plan is also cached in the database, which allows the database to execute parametric queries written using prepared statements much faster than normal queries because it has less work to do. 

You should always try to use PreparedStatement in the production JDBC code to reduce the load on the database. 

In order to get a performance benefit, it's worth noting to use only a parametrized version of SQL query and not with string concatenation. Out of the following two examples of SELECT queries, the first example of the 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);

The second SQL query is correct to use PreparedStatement in Java and gives better performance than SQL query1. You can also see these free JDBC courses to learn about Parameterized queries in Java. 

3. PreparedStatement prevents SQL Injection attacks in Java

If you have been working in Java web applications you must be familiar with the infamous SQL Injection attacks, last year Sony got a victim of SQL injection and compromised several Sony play station user data. 

In an SQL Injection attack, malicious users pass SQL meta-data combined with input which allowed them to execute SQL queries of their choice, If not validated or prevented before sending a query to the database. 

By using parametric queries and PreparedStatement you prevent many forms of SQL injection because all the parameters passed as part of the place-holder will be escaped automatically by JDBC Driver. 

Though It’s worth remembering that in the above example of two PreparedStatement only the second example will prevent SQL injection attacks and the 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 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 queries with IN clause. Following the 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 those are rather tricky or have a performance impact.  You can also see these online JDBC courses to learn more bout PrepareStatement in Java. 

Important points on PreparedStatement in Java

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

1. PreparedStatement in Java allows you to write a parameterized query that gives better performance than the Statement class in Java.

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

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

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

5. PreparedStatement is 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 the 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 the precompilation of SQL query in that case query is not sent to the database when you call prepareStatement(..) method instead they would be sent to the database when you execute PreparedStatement query.

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

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

Other Java tutorials you may like

10 versatile examples of Enum in Java

Beginners guide to Generics in Java

How to use the Factory design pattern in Java with Example

10 Interview questions on Singleton in Java

How to manage the transaction in the database

How to use mysqldump utility for db backup

Why Java doesn’t support multiple inheritance