2.11 Statement, PreparedStatement

2.11 Statement, PreparedStatement

-        (java.sql) Interface Statement :

-          It’s a very simple and easy so it also calls a “Simple Statement”.

-          The object used for executing a static SQL statement and returning the results it produces.

-          By default, only one ResultSet object per Statement object can be open at the same time.

-          Therefore, if the reading of one ResultSet object is interleaved with the reading of another.

-          Each must have been generated by different Statement objects.

-          All execution methods in the Statement interface implicitly close a statement's current ResultSet object if an open one exists.

-          Creates a Statement object for sending SQL statements to the database.

-          SQL statements without parameters are normally executed using Statement objects.

-          If the same SQL statement is executed many times, it may be more efficient to use a PreparedStatement object.

-          Result sets created using the returned Statement object will by default be type TYPE_FORWARD_ONLY and have a concurrency level of CONCUR_READ_ONLY.

-          It returns a new default Statement object

-          It throws SQLException if database access error occure.

-          The statement interface has several methods for execute the SQL statements and also get the appropriate result as per the query sent to the database.

-          Some of the most common methods are as given below.

-          The most common methods are as given below :

Method	Description
void close()	-          This method frees an object of type Statement -          From database and other JDBC resources.
boolean execute(String s)	-          This method executes the SQL statement specified by ‘stmt’. -          The getResultSet() method is used to retrieve the result.
ResultSet getResultet()	-          This method retrieves the ResultSet -          That is generated by the execute() method.
ResultSet executeQuery(String s)	-          This method is used to execute the SQL statement specified by ‘stmt’ -          And returns the object of type ResultSet.
int getMaxRows()	-          This method returns the maximum number of rows those are generated by executeQuery() method.
Int executeUpdate(String s)	-          This method executes the SQL statement specified by ‘stmt’. -          The SQL statement may be a SQL insert, update and delete statement.

-          (java.sql) Interface PreparedStatement :

-          public interface PreparedStatement extends Statement

-          prepareStatement(java.lang.String sql)

-          The Prepared Statement interface is used to execute a dynamic query (parameterized SQL statement) with IN parameter.

-          IN Parameter:-

1)      In some situation where we need to pass different values to an query then such values can be specified as a “?” in the query and the actual values can be passed using the setXXX() method at the time of execution.

2)      Syntax : setXXX(integer data ,XXX value);

3)      Where XXX means a data type as per the value we want to pass in the query.

4)      For example : String query = "Select * from Data where ID = ? and Name = ? ";

5)      PreparedStatement ps = con.prepareStatement(query);

-          Creates a PreparedStatement object for sending parameterized SQL statements to the database.

-          An object that represents a precompiled SQL statement.

-          A SQL statement is precompiled and stored in a PreparedStatement object.

-          A SQL statement with or without IN parameters can be pre-compiled and stored in a PreparedStatement object.

-          The setter methods (setShort, setString, and so on) for setting IN parameter values must specify types that are compatible with the defined SQL type of the input parameter.

-          For instance, if the IN parameter has SQL type INTEGER, then the method setInt should be used.

-          If arbitrary parameter type conversions are required, the method setObject should be used with a target SQL type.

-          In the following example of setting a parameter, con represents an active connection

-          PreparedStatement pstmt = con.prepareStatement("UPDATE EMPLOYEES SET SALARY = ? WHERE ID = ?");

-             pstmt.setFloat(1, 153833.89)

-             pstmt.setInt(2, 110592)

-          This object can then be used to efficiently execute this statement multiple times.

-          This method is optimized for handling parametric SQL statements that benefit from precompilation.

-          This method is optimized for handling parametric SQL statements that benefit from precompilation.

-          This method is optimized for handling parametric SQL statements that benefit from precompilation.

-          In this case, the statement may not be sent to the database until the PreparedStatement object is executed.

-          This has no direct effect on users; however, it does affect which methods throw certain SQLException objects.

-          Result sets created using the returned PreparedStatement object will by default be type TYPE_FORWARD_ONLY and have a concurrency level of CONCUR_READ_ONLY.

-          Parameter sql :- An SQL statement that may contain one or more '?' IN parameter placeholders.

-          It returns a new default PreparedStatement object containing the pre-compiled SQL statement.

-          It throws SQLException if database access error occur.

-          The Prepared statement interface has several methods to execute the parameterized SQL statements

-          And retrieve appropriate result as per the query sent to the database.

-          Some of the most common methods are as given below.

Method	Description
void close()	-          This method frees an object of type  Prepared Statement -          From database and other JDBC resources.
boolean execute()	-          This method executes the dynamic query in the object of type Prepared Statement. -          The getResult() method is used to retrieve the result.
ResultSet executeQuery()	-          This method is used to execute the dynamic query in the object of type Prepared Statement -          And returns the object of type ResultSet.
Int executeUpdate()	-          This method executes the SQL statement in the object of type Prepared Statement. -          The SQL statement may be a SQL insert, update and delete statement.
ResultSetMetaData getMetaData()	-          The ResultSetMetaData means a deta about the data of ResultSet. -          This method retrieves an object of type ResultSetMetaData -          That contains information about the columns of the ResultSet object -          That will be return when a query is execute.
int getMaxRows()	-          This method returns the maximum number of rows -          Those are generated by the executeQuery() method.