If you have worked with database interfacing with Java using JDBC API then you may know that the JDBC API provides three types of Statements for wrapping an SQL query and sending it for execution to the database, they are aptly named as Statement, PreparedStatement, and CallableStatement. First, a Statement is used to execute normal SQL queries like 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 like select * from Courses where courseId=?, you can execute this SQL multiple times by just changing the course parameters. They are compiled and cached at the 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 classes has a different purpose and you should use them for what they have been designed for. It's very important to understand what they are and what is their purpose, along with how to use them 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 these free JDBC online courses for Java programmers and developers.
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.
The actual value is set before executing the query at runtime by using the various setXXX() methods e.g. if the placeholder refers to a varchar column then you can use setString(value) to set the value. Similarly, if a 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 Pluralsight to learn more about how to use these objects.
Btw, you will need a Pluralsight membership to access this course, monthly cost is around $29 which is worthy of your every penny because it will give access to more than 5000+ cutting-edge courses.
Pluralsight is like 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.
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 High-Performance Java Persistence course, where he has explained how different JDBC objects and strategies can result in different performances.
For example, you could have written the above query which returns a book after passing Id as below:
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:
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.
Other JDBC Tutorials and Resources you may like
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 classes has a different purpose and you should use them for what they have been designed for. It's very important to understand what they are and what is their purpose, along with how to use them 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 these free JDBC online courses for Java programmers and developers.
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 the 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 likeselect 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 the placeholder refers to a varchar column then you can use setString(value) to set the value. Similarly, if a 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 Pluralsight to learn more about how to use these objects.
Btw, you will need a Pluralsight membership to access this course, monthly cost is around $29 which is worthy of your every penny because it will give access to more than 5000+ cutting-edge courses.
Pluralsight is like 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 the 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 High-Performance Java Persistence course, where he has explained how different JDBC objects and strategies can result in different performances.
4. Security
The PreparedStatement also provides safety against SQL injection, but the incorrect use of Statement 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 the 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:
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.
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.
Hola, podrian poner un ejemplo? gracias.
ReplyDelete