Friday, March 24, 2017

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.

Btw, if you are new to JDBC and looking for a comprehensive online course to learn JDBC in-depth then I also suggest you check out the Complete JDBC Programming course on Udemy. It's a great course of direct classroom lectures and covers JDBC in depth


What is PreparedStatement in Java

How to use PreparedStatement in Java JDBC – Example Tutorial
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 than 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, 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 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 there access plan is also cached in the database, which allows the 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 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 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 a 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 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 query 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.

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 which gives better performance than Statement class in Java.

2. In the case of PreparedStatement, Database uses 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 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 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, 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.

Further Learning
JSP, Servlets, and JDBC for Beginners: Build a Database App
Complete JDBC Programming Part 1 and 2
Java Platform: Working with Databases Using JDBC

Other Java tutorials you may like

11 comments :

Captain Price said...

Good Work!

"Though there are some workarounds and ways to execute IN queries using PreparedStatement but those are
rather tricky or have performance impact."


Regarding the limitation may i ask what workarounds are in the IN clause.

Javin @ iterate hashmpa java said...

@Captain Price, Thanks for comment. Regarding work around for executing IN clause on PreparedStatment you need to build extra logic which can set extra place folder null or empty e.g. if you have where LOAN IN (?,?,?) than you need to set all three by manual coding. This can be whole new blog post, I will try to put one together.

Anonymous said...

I was actually wondering why prepared statement in java is so important and why we can not use simply statement. with all advantages of prepared statement in place why do some one use Statement object in Java, I understand CallableStatement is specifically used for executing stored procedure and we can not call stored proc using prepared statement but why do we need Statement ? doesn't having Statement just confuse some one using JDBC API for executing queries?

Anonymous said...

PreparedStatement is not a class it is interface.

Anonymous said...

1) Whats happens behind the scene for prepared Statement.
2) What is meant by pre-compiled
3) What is access plan
4) Where pre-compilation happens and where it get stored?
5) difference with Statement in terms of access plan

Thanks

Unknown said...

Prepare statement is more secure than string concatenation.





thanks.

Anonymous said...

Can we use it as..

select ? from employee where age>18;

pstmt.setString(1,employee_id);

GK said...

How can we execute in clause in the query using PreparedStatement

ANKUSH RAINA said...

PreparedStatement is a subinterafce not a class...!

Mehraj Malik said...

What do you mean by pre-compiled ..?

Anonymous said...

where are complied prestatement stored?

Post a Comment