Stored Procedure: A stored procedure is a named group of SQL statements that have been previously created and stored in the server database. Stored procedures accept input parameters so that a single procedure can be used over the network by several clients using different input data. And when the procedure is modified, all clients automatically get the new version. Stored procedures reduce network traffic and improve performance. Stored procedures can be used to help ensure the integrity of the database.

Get ResultSet object from Stored Procedure

Difference between Statement and PreparedStatement

1)Statement is parsed by the database at runtime everytime while PreparedStatements are parsed once and reused.

2)The PreparedStatement is a slightly more powerful version of a Statement, and should always be at least as quick and easy to handle as a Statement.

3)The PreparedStatement may be parametrized while Statement may not.

PreparedStatement Interface: PreparedStatement extends the Statement interface. In most cases it is more efficient (in the context of multiple executions) to use the PreparedStatement because the SQL statement that is sent gets pre-compiled (i.e. a query plan is prepared) in the DBMS. Furthermore, we can use PreparedStatement to safely provide values to the SQL parameters, through a range of setter methods (i.e. setInt(int,int), setString(int,String), etc.).

Types of ResultSet

Resultset contains results of the SQL query. There are 3 basic types of resultset.

Forward-only As name suggest, this type can only move forward and are non-scrollable.

Scroll-insensitive This type is scrollable which means the cursor can move in any direction. It is insensitive which means any change to the database will not show change in the resultset while it open.

Scroll-sensitive This type allows cursor to move in any direction and also propagates the changes done to the database.

Relationship between resultset,rowset,cachedrowset,jdbcrowset,and webrowset

1) ResultSet is a collection of tabular data recieved as a result of executing the Sql statement on datadbase. ResultSet maintains a cursor pointing to the first row, and the cursor can be moved to the next row using the next method.

2) RowSet is a collection of tabular data retrieved from a data source. RowSet extends ResultSet which means it is scrollable and updatable. RowSet may be connected RowSet or disconnected RowSet. Connected: Make a connection to the datasource and maintain the connection through out its life cycle. Disconnceted: Make a connection to the data source , get data from it and close the connection.

3) JdbcRowSet is a connected RowSet that allows programmers to scroll through and update the rows in the ResultSet. you must explicitly set the result-set type constant to TYPESCROLLINSENSITIVE and set the result-set concurrency constant to CONCUR_UPDATABLE to make a ResultSet object scrollable and updatable. A JdbcRowSet object is scrollable and updatable by default.

4) CachedRowSet is a disconnected RowSet that caches the data of a ResultSet in memory and disconnects from the database. Like JdbcRowSet, a CachedRowSet object is scrollable and updatable by default. A CachedRowSet object is also serializable.

5) WebRowSet extends CachedRowSet, has the ability to output the result set to XML and can used to retrieve the data from the XML.