➤ Intro to JDBC
➤ JDBC vs ODBC
➤ JDBC API
➤ JDBC Driver
Connect to Database
➤ Oracle Connection
➤ MySQL Connection
➤ Using Eclipse IDE
➤ Coding Standards
➤ Close JDBC Object
➤ Java.sql Package
➤ DriverManager Class
➤ Get Connection
➤ Statement Interface
➤ Statement Example
➤ Create a database
➤ Types of statements
➤ Statement vs PreParedStatement
➤ Insert record
➤ Update record
➤ Select record
➤ Create a table
➤ More Examples
➤ Surrogate Key
➤ ResultSetMetaData & ParameterMetaData
Different Types of Statements in JDBC | There are three types of statements in JDBC,
Statement object (Or Simple Statement object):- It is the object of a JDBC driver software supplied Java class that implements java.sql.Statement(I). It is also called as Simple Statement because it is used with simple SQL statements without parameters.
PreparedStatement object:- It is the object of a JDBC driver software supplied Java class that implements java.sql.PreparedStatement(I). It is extended from java.sql.Statement(I), and it is used for precompiling SQL statements that might contain input parameters.
CallableStatment object:- It is the object of a JDBC driver software supplied Java class that implements java.sql.CallableStatement(I). It is also extended from java.sql.Statement(I), and it is used to execute stored procedures that may contain both input and output parameters.
How SQL Query executed?
We will discuss the limiations of Statement object and need of PreparedStatement object with example. But before we should know how SQL Query is executed?
When Java application sends SQL query to database software through the JDBC driver, then database engine of database software performs 3 operations on SQL query.
Parse:- It splits the SQL query into tokens to check the syntax of the query. This is also called a query compilation.
Execute:- It executes the parsed/compiled SQL query and prepares the result.
Fetch:- It gathers the result and sends them to the client app (Java application).
If the Statement object is used to execute the same SQL query for multiple times,
a) The same SQL query goes to database software for multiple times from Java application.
b) The same SQL query will be parsed in Database software for multiple times.
c) The Same SQL query will be executed in Database software for multiple times.
d) The Same SQL query output will be fetched out multiple times from database software to send to the client application.
Note:- Performing a, and b operations on same query for multiple times is unnecessary and it can’t be avoided while working simple statement object. It is not good for executing same query for multiple times.
Limitations of Statement object
- It is not suitable for executing the same query multiple times with the same values or with different values.
- Framing SQL Query by involving variables as input values is a complex process.
- It doesn’t allow parameters(?) in the SQL query to set values to the query later.
- It improves network traffic between Java application and database software.
- The Statement object may raise the SQL injection problem.
- It forces the end-user to supply data values in the pattern that is supported by underlying database software to insert data values.
- It doesn’t allow to insert a large object(files, images, videos) in the database table.
Railway tickect reservation system, Booking ticket 1,00,000 times in a day by using simple Statement object,
- a) 1,00,000 times same insert SQL query goes to database software from Java application. So if it takes 0.1 sec to insert one SQL query then total time=> 1,00,000*0.1 sec = 10,000 secs
- b) 1,00,000 times same insert SQL query will be parsed in database software. So if it takes 0.2 sec to parse one SQL query then total time=> 1,00,000*0.2 sec = 20,000 secs
- c) 1,00,000 times same insert SQL query will be executed in database software with different values or with same values. So if it takes 0.1 sec to execute one SQL query then total time=> 1,00,000*0.1 sec = 10,000 secs
- d) 1,00,000 times same insert SQL query output will be fatched out. So if it takes 0.1 sec to fatch one SQL query then total time=> 1,00,000*0.1 sec = 10,000 secs
Total time using Statement object = 10,000 + 20,000 + 10,000 + 10,000 = 50,000 seconds => 13.88 hours (approx)
But if we use PreparedStatement object then the a and b operations are happing on the SQL query only for one time i.e. database engine is not performing unnecessary operations on the same query multiple times. So, with respect to the previous use-case (using Statement object), the c and d phase will take the same time, but a and b phase will take only 0.1 sec. So, total time = 0.1 sec + 0.1 sec + 10, 000 secs + 10, 000 secs = 20,000.2 secs ~ (5.56 hours approx). Using Statement object we are wasting 30,000 secs (8.3 hours) every day to perform unnecessary operations (a, b operations) on the same query multiple times.
Using Statement object the same SQL query goes to database software multiple times from Java application and the same SQL query will be parsed in Database software multiple times. We can not avoid them while working on a simple Statement object. To overcome this problem we must use “pre-compiled SQL query”
The SQL query that goes to database software without input values and becomes parsed or compiled SQL query in Database software irrespective of whether it will be executed or not is called pre-compiled SQL query. PreparedStatement object of the Java application represents this pre-compiled SQL query of database software. We can use that object to set input values to SQL query, to execute SQL query and to fetch the output of SQL query for one or multiple times.
PreparedStatement object is good to execute the same SQL query for multiple times either with input values or with output values.
Advantages of JDBC PreparedStatement object
- It allows working with a pre-compiled/dynamic SQL query. PreparedStatement object can deal with both static and dynamic SQL queries because the pre-compiled SQL query can be a static query with or without inputs or can be a dynamic query with parameters.
- It allows specifying in parameter/place holders/place resolvers (?) in the SQL query.
- PreparedStatement object allows setting input values to SQL query in Java-style without joining for any kind of conversions. The setXxx() method of PreparedStatement object converts the given Java inputs to SQL inputs internally. So, programmers need not give the same conversion explicitly.
- It doesn’t raise the SQL injection problem.
- It is suitable for inserting date values in the database table by collecting date values from end-user in different patterns.
- PreparedStatement object is suitable for inserting a large object (files, image, video, e.t.c).
- It reduces network traffic between Java application and database software while executing the same query multiple times either the same values or different values.
Since it is the third types of Statements in JDBC, and it is completely different from Statement and PreparedStatement objects.
If we want to execute the same persistence logic/business logic in multiple applications of a module or in multiple modules of a project then place that logic in database software as PL/SQL procedure or function and call them from application or modules by using CallableStatement object.
Instead of writing the same authentication logic in every module or application, it is recommended to write it only for one time in database software as PL/SQL procedure or function and call it from all the modules or applications by using CallableStatement object.
Conclusion on three types of statements in JDBC
- If there is a need for executing SQL query without input values only for one time in the entire execution of the application then use java.sql.Statement.
- But if there is a need for executing a query with input values for one or more times either with the same or different input values then use java.sql.PreparedStatement.
- When there is a need of calling PL/SQL procedure or function of database software then use java.sql.CallableStatement.
If you enjoyed this post, share it with your friends. Do you want to share more information about the topic discussed above or you find anything incorrect? Let us know in the comments. Thank you!