Difference between Statement and PreparedStatement

Difference between Statement and  PreparedStatement

In Java, a statement object is responsible for sending the SQL statements to the database mean it is responsible to execute queries. In Statement, the SQL query will be compiled and executed every time. We can use statement for general purpose queries.

                                                              V/S

PreparedStatement is more powerful version of Java Statements and always quicker and easier than statement objects in order to handle SQL queries.We can use preparedStatement for parametric query. PreparedStatement gets pre compiled in database and access plan is also cached in database, which allows database to execute parametric query written using prepared statement much faster than normal query because it has less job to complete. In case of this statement, Db uses an already compiled and defined access plan, this allows prepared statement query to run faster than normal query. It is reducing the load in the db. PreparedStatement helps to prevent SQL injection attacks in java because all the parameters passed as part of place holder will be escaped automatically by JDBC driver. PreparedStatement doesn't allow multiple values for one placeholder (?)  So it is difficult to use IN clause with SQL query.

Below example is a demo through which we can prevent the SQL injection. So we can say that prepare statement is providing more security in application.

PreparedStatement pstmt = con.prepareStatement("select firstname from student where student_id=?");

pstmt.setInt(1,studId);

Note:- '?' also indicates bind variable which is binded with the query.

The work process of PreparedStatement is as follows:

• Prepare: The statement template is created by the application and sent to the database management system(DBMS). Placeholders/Bind variables or parameters are left unspecified

"insert into student(STUDENT_ID,FIRSTNAME,LASTNAME,BIRTHDATE,TOTAL_MARKS,DIVISION) values(?,?,?,?,?,?)";

• The DBMS parses, compiles, and performs query optimization on the statement template, and stores the result without executing it.Execute: At later, when the application supplies placeholder’s values for the parameters, and the DBMS executes the statement (possibly returning a result). The application may execute the statement as many times as it wants with different values. In above example, we can supply any values for the placeholders. 
• So, PreparedStatement reduce the overhead of compiling and optimizing the statement is happened only once as the SQL query can be executed multiple times. Prepared statements are widely supported by major DBMSs, including Microsoft SQL Server, DB2, MySQL, PostgreSQL and Oracle.

Relational Databases handles JDBC/SQL queries by following four steps:-

• Parsing the incoming SQL queries.

• Compiling the SQL queries.

• Planning and optimizing the data acquisition path.

• Executing the optimized query or obtaining the returned data.

A Statement will always processed through the above four steps for each SQL query sent to the database.

On the other hand, a PreparedStatement pre-executes steps 1 to 3 in the execution process. Thus, when creating a PreparedStatement some pre-optimization is performed immediately. Only first time, PreparedStatement will perform all the four steps for handling SQL queries. So, the result is to reduce the load on the database engine during execution time.