JDBC FAQ Part11

23:How to iterate the data from Scrollable ResultSet objuect in both forward and backword direction?

• to iterate the data in forward direction from a ResultSet object we will use the following 2 methods.

public Boolean next()
public xxx getXxx(int fieldno.)
            Where xxx may be byte, short, char, int, long, float, double.

• To iterate the data in backward direction from Scrollable ResultSet object we will use the following 2 methods.

public Boolean previous()
public xxx getXxx(int fieldno)
            Where previous() is a Boolean method, which can be used to check whether the previous record is available or not, if it is available then cursor will be moved to previous record position.

The following example demonstrates how to iterate the data in both forward and backward direction from the ResultSet object

import java.sql.*;
public class ScrollResEx
{
            public static void main(String[] args)throws Exception
            {
                        Class.forName(“sun.jdbc.odbc.JdbcOdbcDriver”);
Connection con = DriverMadoeer.getConnection(“jdbc:odbc:doe”,”system”,”jhon”);
Statement st = con.createStatement(ResultSet.TYPE_SCROLL_SENSEITIVE,ResultSet.CONCUR_UPDATABLE);
ResultSet rs = st.executeQuery(“select * from emp1”);
System.out.println(“data in forward direction”);
System.out.println(“ENO       ENAME       ESAL       EADDR”);
System.out.println(“**********************************”);
While(rs.next())
{
System.out.println(rs.getInt(1)+”    ”+rs.getString(2)+”    ”+rs.getFloat(3)+”    ”+rs.getString(4));

}
System.in.read();
System.out.println(“data in backward direction”);
System.out.println(“ENO        ENAME        ESAL        EADDR”);
System.out.println(“***********************************”);
While(rs.previous())
{
System.out.println(rs.getInt(1)+”    ”+rs.getString(2)+”    ”+rs.getFloat(3)+”    ”+rs.getString(4));

}
}
}

24:   how to generate ScrollSensitive Result Set and how to reflect the later updations from database automatically to the ResultSet object?

import java.sql.*;
public class Test
{
            Public static void main(String[] args)throws Exception
            {
                        Class.forName(“sun.jdbc.odbc.JdbcOdbcDriver”);
Connection con = DriverMadoeer.getConnection(“jdbc:odbc:doe”,”system”,”jhon”);
Statement st = con.createStatement(ResultSet.TYPE_SCROLL_SENSEITIVE,ResultSet.CONCUR_UPDATABLE);
ResultSet rs = st.executeQuery(“select * from emp1”);
rs.next();
System.out.println(“old salary emp111…….”+rs.getFloat(3));
System.in.read();//application is in pause, perform database updations
Rs.refreshRow();
System.out.println(“new salary of emp111……..”+rs.getFloat(3));
}
}

            Where refreshRow() is a method from Scrollable ResultSet object, which can be used to refresh the current row in the ResultSet object to allow the later updations from database. Prototype of this method is
            public void refreshRow()
25:  How to insert records into Database throws Updatable ResultSet?

If we want to insert a new record on to the database through Updatable ResultSet object, we will use the following steps.
Step1: Get the Updatable ResultSet object with fetched data.
Step2:  Move ResultSet cursor to the end of the ResultSet object, where we need to take a buffer to hold new records data temporarily, for this we use the following method from updatable ResultSet object.
                  
                        public void moveToInsertRow()

Step3:   Insert new records data on to the buffer temporarily at Updatable ResultSet object for this we will use the following method format.

            public void updateXxx(int fieldno,xxx value)

            Where xxx may be byte, short, int, char, double, float, long.
Step4:  Make the temporary insertion as the permanent insertion in Updatable ResultSet object as will as in database, for this we will use the following method.
public void insertRow()
The following example demonstrates how to insert no. of records onto the database through Updatable ResultSet objects.

import java.sql.*;
import java.io.*;
public class UpdateResEx
{
            public static void main(String[] args)throws Exception
            {
                        Class.forName(“sun.jdbc.odbc.JdbcOdbcDriver”);
Connection con = DriverMadoeer.getConnection(“jdbc:odbc:doe”,”system”,”jhon”);
Statement st = con.createStatement(ResultSet.TYPE_SCROLL_SENSEITIVE,ResultSet.CONCUR_UPDATABLE);
ResultSet rs = st.executeQuery(“select * from emp1”);
BufferedReader br = new BufferedReader(new InputStreamReader(System.in));
rs.moveToInsertRow();
                        while(true)
                        {
                                    System.out.println(“enter employee number”);
                                    int eno = Integer.parseInt(br.readLine());
                                    System.out.println(“enter employee name”);
                                    String ename = br.readLine();
                                    System.out.println(“enter employee salary”);
                                    float esal = Float.parseFloat(br.readLine());
                                    System.out.println(“enter employee address”);
                                    String eaddr = br.readLine();
                                    rs.updateInt(1,eno);
                                    rs.updateString(2,ename);
                                    rs.updateFloat(3,esal);
                                    rs.updateString(4,eaddr);
                                    rs.insertRow();
                                    System.out.println(“record successfully inserted”);
                                    System.out.println(“one more record[y/n]);
                                    String option = br.readLine();
                                    if(option.equals(“n”))
                                                break;
}
}