JDBC - Difference between PreparedStatement and Statement in Java

If you have worked with database interfacing with Java using JDBC API then you may know that the 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 class has a different purpose and you should use them for what they have designed for. It's very important to understand what they are and what is their purpose, along with how to use it correctly.

In this article, we will focus on understanding the difference between the first two members of this family, Statement, and PreparedStatement, so that you can use them effectively.

But, if you want to learn more about how to use them i.e. some practical code and examples, I suggest you take a look at The Complete Java Masterclass on Udemy.

It's one of the most comprehensive course and you can get in just under $10 on Udemy's flash sale. If you are new in Java space, I strongly recommend you to check that post. You will learn a lot in a very short duration of time.



Difference between Statement vs PreparedStatement

Anyway, without wasting any more of your time, let's see some key differences between these two classes, they are based on syntax, purpose, performance, security, and capabilities.

1. Purpose

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.

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




2. Syntax

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 placeholders 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.

You can further see  Java Platform: Working with Databases Using JDBC course on Pluarlsight to learn more about how to use these objects.

JDBC - Difference between PreparedStatement and Statement in Java



Btw, you will need a Pluralsight membership to access this course, monthly cost around $29 which is worthy of your every penny because it will give access to more than 5000+ cutting-edge courses. 

Pluralsight is like the NetFlix for software developers. Last, but not least, you can also get this course by signing up for a 10-day free trial without any obligation. which is simply great.




3. Performance

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.

If you want to learn more about JDBC or Persistence layer performance, then I suggest you go through Vlad Mihalcea's High-Performance Java Persistence course, where he has explained how diferent JDBC objects and strategy can result in different performance.

JDBC - Difference between PreparedStatement and Statement in Java



4. Security

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
The Complete Java Masterclass
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)
  • 5 Free JDBC Courses for Java Developers (courses)
  • 5 Free JDBC books for Java Programmers (books)

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