➤ 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
In this post we will discuss the main differences between Statement and PreparedStatement object, and when we should use which object.
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. Simple statement object makes database software to compile the SQL query just before the execution. It can always deal with only static SQL queries (query without inputs or with hardcoded inputs).
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.
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. It 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. The setXxx() method of PreparedStatement object converts the given Java inputs to SQL inputs internally. So, programmers need not give the same conversion explicitly.
Table to show the Difference between Statement and PreparedStatement
|It allows only static SQL queries.||It allows both static and dynamic SQL queries.|
|Pre-compiled SQL queries support|
is not there for the Statement.
|It only work with pre-compiled SQL queries.|
|It doesn’t allow us to take the parameter(?)|
in the SQL query.
|PreparedStatement allow to take parameter(?) in the SQL query.|
|It may raise SQL injection problem.||It can’t raise SQL injection problem.|
|The Statement object is not better to execute the same query multiple times either with no input or with the same inputs or with different inputs.||It is suitable to executed the same query for multiple times.|
|It can be used to insert date values but end-user must supply the date value in the pattern that the underlying database software supports.||PreparedStatement can take date value from end-user in any pattern.|
|It can’t be used to insert large objects like image, audio, video, files e.t.c||It can be used to insert large objects like image, audio, video, files e.t.c|
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.
When to use Statement vs PreparedStatement Object
- 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.
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!