Why are prepared statements faster than statements in JDBC? explain?

When using the Statement object to execute a statement, especially when inserting a statement, each time the query is executed, the entire statement is compiled and executed over and over again, where, between these statements The only difference is the value of the statement.

However, prepared statements are precompiled statements, that is, the query is compiled and stored in the database, using placeholders (?) instead of values, and the values ​​of these placeholders are provided later.

Thus, avoid unnecessary compilation and execution statements again and again.

Example

Suppose, we have a table named Dataset in the database, which contains the columns mobile_brand and unit_sale, if we want to use statement objects to insert records into this table, the code will look like this:

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)");

For each executeUpdate() method call, the code will be compiled and executed the entire statement. Here if you observe that only the value of the statement is changed while the rest of the query is compiled unnecessarily.

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

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 that the insert query is is prepared with placeholders (?), and the query is compiled and stored in the database, and the value is later passed using the setter method of the PreparedStatement interface, thus avoiding unnecessary execution of the statement.

The above is the detailed content of Why are prepared statements faster than statements in JDBC? explain?. For more information, please follow other related articles on the PHP Chinese website!