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

Published on 20-Mar-2019 11:02:41