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.
Difference between Statement vs PreparedStatement
Following are some key differences between these two classes, they are based upon syntax, purpose, performance, security, and capabilities.
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.
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.
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 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.
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.
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.
