Wednesday, February 1, 2023

Difference between Statement vs PreparedStatement vs CallableStatement in Java JDBC

Hello Java programmers, if you are looking to find out the difference between Statement, PreparedStatement, and CallableStatement in Java then you have come to the right place. Earlier, I have shared common JDBC Interview Questions and in this article, I am going to explain what is the real difference between these Statement types in Java and when to use Statement, PreparedStatement, and CallableStatement in Java programs. JDBC API provides several classes and interfaces for various things, but three of the most important types of Statement classes are Statement, PreparedStatment, and CallableStatement. They are designed to execute different types of SQL queries

For example, the Statement class is responsible for executing any SQL query, like CREATE, UPDATE, DELETE, or SELECT. On the other hand, PreparedStatement is designed to execute parametrically or bind questions, mostly used with SELECT and UPDATE statements. While CallableStatement provides an interface to execute a stored procedure from a Java program.

Apart from this fundamental difference, there are a couple of more subtle differences between these three classes which makes the question what the difference between Statement, PreparedStatement, and CallableStatement one of the most common JDBC questions from Java interviews is. In this article, you will learn how to answer this question.

By the way, if you are new to JDBC then I highly recommend you to join a comprehensive JDBC course like Complete JDBC Programming Part I and Part II on Udemy. This is one of the most comprehensive courses and covers JDBC in depth. 




Difference between Statement vs. Prepared Statement vs. Callable Statement

As I told you, the purpose of these three classes is to execute different types of SQL queries, but there is some more subtle difference between them. Let's see them now:

1. Type of SQL query
One of the fundamental difference and unique feature of each of these three classes is that Statements is used to execute standard SQL queries, PreparedStatement is used to execute bind or dynamic queries, and CallableStatement is used to call the stored procedure from Java.


2. Frequency of SQL query
Another key difference between Statement, PreparedSatement, and CallableStatement is that first is preferred when a particular SQL query is likely to be executed only once, while PreparedStatement is preferred when a particular SQL query is to be executed multiple times but with different values, like SELECT * From Users where UserId=?. On the other hand, CallableStatement is preferred when the stored procedures are to be executed.


3. SQL injection
One more important difference between PreparedStatement, Statement, and CallableStatement is that PreparedStatement prevents your Java web application from SQL injection. This is also one of the common reasons for using PreparedStatement in Java Web applications. It's also a JDBC best practice to use PreparedStatement over Statement for better performance and SQL injection prevention. 





4. Performance
This is probably the most essential difference between Statement, PreparedStatement, and CallableStatement in Java. Performance of Statement is very low as compared to the performance of PreparedStatement because the query is compiled, and the plan is cached at the database end. Performance of CalalbleStatement is high compared to Statement. 


5. Usage
The Statement is mainly used to execute DDL statements like CREATE, ALTER, and DROP, though you can also execute one-off SELECT statements as well. Compared to this, PreparedStatemetn is used for SQL queries which are supposed to be run multiple times but with different parameters. CalalbleStatement is used to call stored procedures and functions.

If you want to learn more about JDBC forms scratch then you can also check out Java Core Libraries: JDBC course on Pluralsight. It's one of the most engaging and up-to-date courses to learn JDBC online. 

Difference between Statement vs PreparedStatement vs CallableStatement in Java



That's all about the difference between Statement, PreparedStatement, and CallableStatement in Java. Just remember that Statement is to execute DDL statements like CREATE, ALTER, DROP. PreparedStatement to execute a parametric SQL query and also to avoid SQL injection and CallableStatement is to execute Stored procedures from Java Program. 

More often than not you will use JdbcTemplate class from Spring Framework for executing SQL queries but knowing how it's done on Java and JDBC layer is important to troubleshoot any production issue or errors. 



Other JDBC Articles and Tutorials You May like:
  • Difference between Time, Timestamp and Date in Java? (answer)
  • Step by Step Guide to connect MySQL database using JDBC API (guide)
  • Java guide to connect Oracle 10g database using JDBC thin driver (guide)
  • Solving java.lang.classnotfoundexception sun.jdbc.odbc.jdbcodbcdriver [solution]
  • Fixing java.lang.ClassNotFoundException: org.postgresql.Driver [solution]
  • Solving java.lang.classnotfoundexception oracle.jdbc.driver.oracledriver [solution]
  • Dealing with java.lang.ClassNotFoundException: com.mysql.jdbc.Driver [fix]
  • How to connect to MySQL database from Java Program [steps]
  • General Guide to solve java.lang.ClassNotFoundException in Java [guide]
  • java.lang.ClassNotFoundException: org.apache.commons.logging.LogFactory? [solution]
  • java.lang.ClassNotFoundException: com.microsoft.sqlserver.jdbc.SQLServerDriver? [solution]
  • How to connect to Oracle Database from Java Application (learn here)
  • What is difference between connected and disconnected RowSet in Java? (answer)
  • How to use JDBC connection pool in Spring framework? (solution)
  • 5 Simple things to improve performance of Java database applications (tips)
  • Difference between java.util.Date and java.sql.Date in Java? (answer)
  • How to do INSERT and UPDATE using JDBC Batch statement? (solution)
  • 5 Best Free Courses to learn JDBC in Depth (free JDBC courses)

Thanks for reading this article so far. If you find these differences between Statement, PreparedStatement  and CallableStatement in Java JDBC useful then please share with your friends and colleagues. If you have any questions or feedback then please drop a note. 

P. S. - If you are new to Java and looking for a free online training course to learn Java in-depth then you can also check out Java Tutorial for Complete Beginners(FREE) course on Udemy. It's completely free and more than 1.2 million people have joined this course to learn Java. 

1 comment:

  1. Good summary. I think you hit the high points well.

    One comment. At one time (and this refers to MySQL in the past, I don't believe it applies any longer), some databases treat, or have treated the PreparedStatement as a standard statement so the performance improvement does not give you an advantage.

    ReplyDelete