Statement interface and ResultSet

 

Statement interface

The Statement interface provides methods to execute queries with the database. The statement interface is a factory of ResultSet i.e. it provides factory method to get the object of ResultSet.

Commonly used methods of Statement interface:

The important methods of Statement interface are as follows:

1) public ResultSet executeQuery(String sql): is used to execute SELECT query. It returns the object of ResultSet.

2) public int executeUpdate(String sql): is used to execute specified query, it may be create, drop, insert, update, delete etc.

3) public boolean execute(String sql): is used to execute queries that may return multiple results.

4) public int[] executeBatch(): is used to execute batch of commands.

Example of Statement interface

Let’s see the simple example of Statement interface to insert, update and delete the record.

1. import java.sql.*;  
2. class FetchRecord{  
3. public static void main(String args[])throws Exception{  
4. Class.forName("oracle.jdbc.driver.OracleDriver");  
5. Connection con=DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:xe","system","oracle");  
6. Statement stmt=con.createStatement();  
7.   
8. //stmt.executeUpdate("insert into emp765 values(33,'Irfan',50000)");  
9. //int result=stmt.executeUpdate("update emp765 set name='Vimal',salary=10000 where id=33");  
10. int result=stmt.executeUpdate("delete from emp765 where id=33");  
11. System.out.println(result+" records affected");  
12. con.close();  
13. }}  

ResultSet interface

The object of ResultSet maintains a cursor pointing to a row of a table. Initially, cursor points to before the first row.

By default, ResultSet object can be moved forward only and it is not updatable.

But we can make this object to move forward and backward direction by passing either TYPE_SCROLL_INSENSITIVE or TYPE_SCROLL_SENSITIVE in createStatement(int,int) method as well as we can make this object as updatable by:

1. Statement stmt = con.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,  
2.                      ResultSet.CONCUR_UPDATABLE);  

Commonly used methods of ResultSet interface

1) public boolean next():	is used to move the cursor to the one row next from the current position.
2) public boolean previous():	is used to move the cursor to the one row previous from the current position.
3) public boolean first():	is used to move the cursor to the first row in result set object.
4) public boolean last():	is used to move the cursor to the last row in result set object.
5) public boolean absolute(int row):	is used to move the cursor to the specified row number in the ResultSet object.
6) public boolean relative(int row):	is used to move the cursor to the relative row number in the ResultSet object, it may be positive or negative.
7) public int getInt(int columnIndex):	is used to return the data of specified column index of the current row as int.
8) public int getInt(String columnName):	is used to return the data of specified column name of the current row as int.
9) public String getString(int columnIndex):	is used to return the data of specified column index of the current row as String.
10) public String getString(String columnName):	is used to return the data of specified column name of the current row as String.

Example of Scrollable ResultSet

Let’s see the simple example of ResultSet interface to retrieve the data of 3rd row.

1. import java.sql.*;  
2. class FetchRecord{  
3. public static void main(String args[])throws Exception{  
4.   
5. Class.forName("oracle.jdbc.driver.OracleDriver");  
6. Connection con=DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:xe","system","oracle");  
7. Statement stmt=con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_UPDATABLE);  
8. ResultSet rs=stmt.executeQuery("select * from emp765");  
9.   
10. //getting the record of 3rd row  
11. rs.absolute(3);  
12. System.out.println(rs.getString(1)+" "+rs.getString(2)+" "+rs.getString(3));  
13.   
14. con.close();  
15. }}