Why are Prepared Statements in JDBC faster than Statements? Explain?

While executing statements using Statement object, especially insert statements, each time a query is executed the whole statement is compiled and executed again and again where, the only difference among these statements is the values of the statements.

Whereas, prepared statement is a precompiled statement i.e. the query is compiled and stored in the database, using place holders (?) instead of values and values to these place holders are supplied later.

Thus, avoiding unnecessary compilation and execution of the statement again and again.

Example

Suppose, we have a table named Dataset in the database with the columns mobile_brand and unit_sale, if we want to insert records into this table using statement object the code would be like:

stmt.executeUpdate("insert into Dataset values('Iphone', 3000)");
stmt.executeUpdate("insert into Dataset values('Samsung', 4000)");
stmt.executeUpdate("insert into Dataset values('Nokia', 5000)");
stmt.executeUpdate("insert into Dataset values('Vivo', 1500)");
stmt.executeUpdate("insert into Dataset values('Oppo', 9000)");
stmt.executeUpdate("insert into Dataset values('MI', 6400)");
stmt.executeUpdate("insert into Dataset values('MotoG', 4360)");
stmt.executeUpdate("insert into Dataset values('Lenovo', 4100)");
stmt.executeUpdate("insert into Dataset values('RedMi', 4000)");
stmt.executeUpdate("insert into Dataset values('OnePlus', 6334)");

And for every executeUpdate() method invocation the whole statement in it is compiled and executed. Here, if you observe only the values of the statement are being changed and remaining query is getting compiled unnecessarily.

If you write insert query using prepared statement to insert same data in the same table, the code looks like:

PreparedStatement pstmt = con.prepareStatement("insert into Dataset values(?, ?)");

pstmt.setString(1, "Iphone");
pstmt.setInt(2, 3000);
pstmt.executeUpdate();

pstmt.setString(1, "Samsung");
pstmt.setInt(2, 4000);
pstmt.executeUpdate();

pstmt.setString(1, "Nokia");
pstmt.setInt(2, 5000);
pstmt.executeUpdate();

pstmt.setString(1, "Vivo");
pstmt.setInt(2, 1500);
pstmt.executeUpdate();

pstmt.setString(1, "Oppo");
pstmt.setInt(2, 900);
pstmt.executeUpdate();

pstmt.setString(1, "MI");
pstmt.setInt(2, 6400);
pstmt.executeUpdate();

pstmt.setString(1, "MotoG");
pstmt.setInt(2, 4360);
pstmt.executeUpdate();

pstmt.setString(1, "Lenovo");
pstmt.setInt(2, 4100);
pstmt.executeUpdate();

pstmt.setString(1, "RedMi");
pstmt.setInt(2, 4000);
pstmt.executeUpdate();

pstmt.setString(1, "MotoG");
pstmt.setInt(2, 4360);
pstmt.executeUpdate();

pstmt.setString(1, "OnePlus");
pstmt.setInt(2, 6334);
pstmt.executeUpdate();

Here if you observe the insert query is prepared with place holders (?) and this query is compiled and stored in the database and later the values are passed using the setter methods of the PreparedStatement interface thereby, avoiding the unnecessary execution of the statement.

Krantik Chavan

Updated on 30-Jul-2019 22:30:25

• Related Questions & Answers
• Batch Inserts Using JDBC Prepared Statements
• What are the types of statements in JDBC?
• Batch Inserts Using JDBC Statements
• How can we use prepared statements in MySQL?
• How to process SQL statements with JDBC explain with an example?
• Why are "continue" statements bad in JavaScript?
• How can we use prepared statements in a stored procedure?
• Can we call functions using Callable Statements? Explain with an example in JDBC?
• What is the similarity between prepared statements and MySQL user variables?
• What are label statements in JavaScript?
• What are control statements in C#?
• What are provision in financial statements?
• What are reserves in financial statements?
• Explain try, except and finally statements in Python.
• Explain about various financial statements in financial management.