PreparedStatement

In this tutorial, we are going to discuss about PreparedStatement. A PreparedStatement in Java is a precompiled SQL statement. It’s used to execute parameterized SQL queries, meaning you can create SQL queries with placeholders for parameters, and then supply the values for those parameters at runtime.

This helps prevent SQL injection attacks and improves performance by allowing the database to reuse the execution plan for the query.

In the case of normal Statement, whenever we are executing SQL Query, every time compilation and execution will be happened at database side.

Statement st = con.createStatement();
ResultSet rs = st.executeQuery ("select * from emp");

Total Time per Query = Request Time + Compile Time + Execution Time+Response Time
                     = 2 ms + 1 ms + 1 ms + 3 ms = 7 ms
per 1000 Queries = 7 * 1000ms = 7000ms

Sometimes in our application,we required to execute same query multiple times with same or different input values.

E.g 1

In IRCTC application,it is common requirement to list out all possible trains between 2 places.

select * from trains where source='XXX' and destination='YYY';

Query is same but source and destination places may be different. This query is required to execute lakhs of times per day.

E.g 2

In BookMyShow application, it is very common requirement to display theater names where a particular movie running/playing in a particular city.

select * from theatres where city='XXX' and movie='YYY';

In this case this query is required to execute lakhs of times per day. May be with different movie names and different locations. For the above requirements if we use Statement object, then the query is required to compile and execute every time, which creates performance problems. To overcome this problem, we should go for PreparedStatement.

The main advantage of PreparedStatement is the query will be compiled only once even though we are executing multiple times, so that overall performance of the application will be improved. We can create PreparedStatement by using prepareStatement() method of Connection interface.

E.g

PreparedStatment pst = con.prepareStatement(sqlQuery);

At this line, sqlQuery will send to the database. Database engine will compile that query and stores in the database. That pre compiled query will be returned to the java application in the form of PreparedStatement object.

Hence PreparedStatement represents “pre compiled sql query”. Whenever we call execute methods,database engine won’t compile query once again and it will directly execute that query,so that overall performance will be improved.

Total Time per Query = Request Time + Response Time + Execution Time
                     = 2 ms + 1 ms + 3 ms = 6 ms
1000 Queries = 6 * 1000 = 6000 ms

That’s all about the PreparedStatement. If you have any queries or feedback, please write us email at contact@waytoeasylearn.com. Enjoy learning, Enjoy Java.!!

PreparedStatement