- Trending Categories
Data Structure
Networking
RDBMS
Operating System
Java
iOS
HTML
CSS
Android
Python
C Programming
C++
C#
MongoDB
MySQL
Javascript
PHP
Physics
Chemistry
Biology
Mathematics
English
Economics
Psychology
Social Studies
Fashion Studies
Legal Studies
- Selected Reading
- UPSC IAS Exams Notes
- Developer's Best Practices
- Questions and Answers
- Effective Resume Writing
- HR Interview Questions
- Computer Glossary
- Who is Who
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.
- Related Articles
- 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?
- Can we call functions using Callable Statements? Explain with an example in JDBC?
- How can we use prepared statements in a stored procedure?
- Explain why rusting of iron objects is faster in coastal areas than in deserts.
- How is charcoal prepared? Explain why, charcoal is a better fuel than wood.
- What is the similarity between prepared statements and MySQL user variables?
- Why does light travel faster than sound like we see lightning faster than thunder?
- Kerosene oil catches fire faster than wood. Why?
- Explain try, except and finally statements in Python.
- Explain about various financial statements in financial management.
