Recent Posts

Tuesday, 13 November 2018

Advantages and Disadvantages of PreparedStatement

Advantages
☀ Performance will be improved when compared with simple Statement because query will be compiled only once.
☀ Network traffic will be reduced between java application and database because we are not required to send query every time to the database.
☀ Best Suitable to insert Large Objects (CLOB, BLOB)
☀ We are not required to provide input values at the beginning and we can provide dynamically so that we can execute same query multiple times with different sets of values.
☀ It allows to provide input values in java style and we are not required to convert into database specific format.
☀ Best suitable to insert Date values.
☀ It prevents SQL Injection Attack.

Disadvantages 
     We can use PreparedStatement for only one sql query (Like CDMA Phone), but we can use simple Statement to work with any number of queries (Like GSM Phone).
E.g:
Statement st = con.createStatement();
st.executeUpdate("insert employees...");
st.executeUpdate("update employees...");
st.executeUpdate("delete employees...");
     Here We Are Using One Statement Object To Execute 3 Queries
But,
PreparedStatement pst = con.prepareStatement("insert into employees...");
     Here PreparedStatement object is associated with only insert query.

Note
     Simple Statement can be used only for static queries where as PreparedStatement can used for both static and dynamic queries.

Differences between Statement and PreparedStatement 
1. Statement
☀ At the time of creating Statement Object, we are not required to provide any Query.
Statement st = con.createStatement(); 
     Hence Statement Object is not associated with any Query and we can use for multiple Queries.
☀ Whenever we are using execute Method, every time Query will be compiled and executed.
☀ Statement Object can work only for Static Queries.
☀ Relatively Performance is Low.
☀ Best choice if we want to work with multiple Queries.
☀ There may be a chance of SQL Injection Attack.
☀ Inserting Date and Large Objects (CLOB and BLOB) is difficult.

 2. PreparedStatement
☀ At the time of creating PreparedStatement, we have to provide SQL Query compulsory and will send to the Database and will be compiled.
PrepareStatement pst = con.prepareStatement(query); 
    Hence PrepareStatement is associated with only one Query.
☀ Whenever we are using execute Method, Query won't be compiled just will be executed.
☀ PrepareStatement Object can work for both Static and Dynamic Queries.
☀ Relatively Performance is High.
☀ Best choice if we want to work with only one Query but required to execute multiple times.
☀ There is no chance of SQL Injection Attack.
☀ Inserting Date and Large Objects (CLOB and BLOB) is easy.

Next Tutorial : SQL Injection Attack

Previous Tutorial : Steps to develop JDBC Application by using PreparedStatement


No comments:

Post a Comment