JDBC - Difference between PreparedStatement and Statement in Java

JDBC API provides three types of Statement for wrapping an SQL query and sending for execution to the database, they are aptly named as Statement, PreparedStatement, and CallableStatement. First one, Statement is used to execute normal SQL queries e.g. select count(*) from Courses. You can also use it to execute DDL, DML and DCL SQL statements. The second one, PreparedStatement is specialized to execute parameterized queries e.g. select * from Courses where courseId=?, you can execute this SQL multiple times by just changing the course parameters. They are compiled and cached at database end, hence quite fast for repeated execution. The third member of this family is CallableStatement, which is there to execute or call stored procedures stored in the database.

So you see, each of the Statement has a different purpose and you should use them for what they have designed for. In this article, we will focus on understanding the difference between first two members of this family, Statement, and PreparedStatement, so that you can use them effectively.

Difference between Statement vs PreparedStatement

Following are some key differences between these two classes, they are based upon syntax, purpose, performance, security, and capabilities.

PreparedStatement's sole purpose is to execute bind queries. If you need to execute a query multiple times with just different data then use PreparedStatement and use a placeholder, the question mark sign (?) for the variable data.

When you first execute the prepared SQL query, the database will compile it and cache it for future reuse, next time you call the same query but with a different parameter, then the database will return the result almost immediately. Because of this pre-compilation, this class is called PreparedStatement in Java.

It's very useful to build search and insert queries e.g. if your application provides an interface to search some data e.g. course details, let's say by course, name, instructor, price, or topic. You can create PreparedStatement to handle that for better performance.

The sole purpose of Statement object is to execute SQL query. You give them any query and it will execute it, but unlike PreparedStatement, it will not provide pre-compilation.

The syntax for Statement is same as SQL query, you can actually copy SQL from your favorite SQL editor and pass it as String to Statement for execution, but for PreparedStatement, you need to include placeholder i.e. questions mark (?) sign in SQL query e.g.

select count(*) from Books; // Uses Sttement to execute

select * from Books where book_id=?; // Use PreparedStatement

The actual value is set before executing the query at runtime by using the various setXXX() methods e.g. if placeholder refers to a varchar column then you can use setString(value) to set the value. Similarly, if placeholder refers to an integer column then you can use setInteger(value) method.

In general, PreparedStatement provides better performance than Statement object because of pre-compilation of SQL query on the database server.

When you use PreparedStatement, the query is compiled the first time but after that it is cached at the database server, making subsequent run faster.

On the other hand, with the Statement object, even if you execute the same query again and again, they are always first compiled and then executed, making them slower compared to PreparedStatement queries.

The PreparedStatement also provides safety against SQL injection, but the incorrect use of Statment can cause SQL injection. If you remember, the cause of SQL injection is malicious SQL code which is injected by malicious users.

For example, you could have written above query which returns a book after passing Id as below:

String id = getFromUser();
String SQL = "select * from Books where book_id=" + id;

If you pass this SQL to Statement object then it can cause SQL injection if a user sends malicious SQL code in form of id e.g.  1== 1 OR id, which will return every single book from the database.

Though books, may not sound a sensitive data it could happen with any sensitive user data as well. PreparedStatement guards against this.

Here is a quick summary of Statement, PreparedStatement and CallableStatement classes of JDBC API for executing queries:

Java - Difference between PreparedStatement and Statement in JDBC

That's all about the difference between Statement and PreparedStatement in Java. You can use Statement to execute SQL queries but it's not recommended, especially if you can use PreparedStatement, which is more secure and fast approach to get the data from the database. If you have to pass parameters always use PreparedStatment, never create dynamic SQL queries by concatenating String, it's not safe and prone to SQL injection attack.

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 JDBC Tutorials and  Resources you may like
10 JDBC Best Practices Every Java Programmer should follow (read)
6 Essential JDBC Performance tips for Java Programmers (tips)
Difference between Type 1 and Type 4 JDBC drivers (answers)
How to connect to MySQL database from Java Program? (tutorial)
JDBC Batch Insert and Update example using PreparedStatement (tutorial)
Top 10 JDBC Interview Question for Java programmers (read)
Difference between java.sql.Date, java.sql.Timestamp, and java.util.Date in JDBC? (answer)

Thanks for reading this article so far. If you like this article then please share with your friends and colleagues. If you have any questions or feedback then please drop a note. 

No comments:

Post a Comment