Wednesday, 1 October 2014

Move cursor in Scrollale ResultSet

ResultSet interface provides number of methods to move cursor to specific position.

Method
Description
boolean absolute(int row)
Moves the cursor to the given row number in this ResultSet object. Returs true if the cursor is moved to a position in this ResultSet object, else false.
void afterLast()
Moves the cursor to the end of this ResultSet object, just after the last row.
void beforeFirst()
Moves the cursor to the front of this ResultSet object, just before the first row.
boolean first()
Moves the cursor to the first row in this ResultSet object. Returns true if the cursor is in valid row, false if there are no rows in ResultSet.
boolean last()
Moves the cursor to the last row in this ResultSet object. Returns true if the cursor is in valid row, false if there are no rows in ResultSet.
boolean next()
Moves the cursor to the next row. Returns true if the new current row is valid, else false if there are no more rows.
boolean previous()
Moves the cursor to the previous row. Returns true if the new current row is valid, else false if there are no more rows.
boolean relative(int rows)
Move the cursor relative to number of rows, relative(5) moves the cursor to 5 rows forward, relative(-5) moves the cursor to 5 rows backward. Returns true if the new current row is valid, else false if there are no more rows.

/* Step 1: Import sql package */
import java.sql.*;

public class SampleApp {
    
    /* Update username, password and driver details here */
    static Connection getConnection() throws ClassNotFoundException, SQLException{
         /* Step 2: Load Driver */
        System.out.println("Loading/Registering driver");  
       
        Class.forName("com.mysql.jdbc.Driver");
         
        /* Step 3: Open connection to database */
        System.out.println("Connecting to database");
        String url = "jdbc:mysql://localhost/world";
        String userName = "root";
        String pasword = "tiger";
        return DriverManager.getConnection(url, userName, pasword);
    }
    
    public static void main(String args[]) throws SQLException, ClassNotFoundException{
        
        Connection conn = getConnection();

          /* Create table employee */
        String query = "CREATE TABLE employee (id int, name varchar(30), PRIMARY KEY(id))";
        Statement stmt = conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
        stmt.execute(query);     
        
        /* Insert data to employee table */
        query = "INSERT INTO employee values(1, \"Krishna\")";
        stmt.execute(query);
        query = "INSERT INTO employee values(2, \"Arjun\")";
        stmt.execute(query);        
        query = "INSERT INTO employee values(3, \"Ptr\")";
        stmt.execute(query);

        query = "SELECT * FROM employee";
        ResultSet rs = stmt.executeQuery(query);
        
        System.out.println("First Row");
        rs.first();
        int id  = rs.getInt("id");
        String name = rs.getString("name");
        System.out.println(id +" " + name);
        
        System.out.println("Last Row");
        rs.last();
        id  = rs.getInt("id");
        name = rs.getString("name");
        System.out.println(id +" " + name);
        
        System.out.println("Middle Row");
        rs.relative(-1);
        id  = rs.getInt("id");
        name = rs.getString("name");
        System.out.println(id +" " + name);
      
        System.out.println("Dropping table employee");
        query = "DROP TABLE employee";
        stmt.execute(query);
        
 rs.close();
        stmt.close();
        conn.close();
    }
}

Output
Loading/Registering driver
Connecting to database
First Row
1 Krishna
Last Row
3 Ptr
Middle Row
2 Arjun
Dropping table employee


Prevoius                                                 Next                                                 Home

No comments:

Post a Comment