Statement, PreparedStatement In JDBC

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 :


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.



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.