Scrollable Results example

The java.sql.ResultSet object contains the data in rows/columns format. By default the traversing data in ResultSet object in only one direction, forward. But it is not always required. Sometimes we require traversing data in both (forward and backward) direction. The JDBC 2.0 version support this feature and many more. With scrollable ResultSet obejct you can move cursor forward, backward, and to specific rows within the result set.

Types of ResultSet

  • ResultSet.TYPE_FORWARD_ONLY Default, It is not scrollable i.e. the ResultSet cursor moves forward only( from before the first row to after the last row).

  • ResultSet.TYPE_SCROLL_INSENSITIVE It is scrollable ResultSet; its cursor can move both forward and backward relative to the current cursor position, and cursor can move to an absolute position also. But it is not sensitive to changed data; means if underlaying data is changed it is not reflects in the ResultSet object.

  • ResultSet.TYPE_SCROLL_SENSITIVE the result set is scrollable; its cursor can move in both forward and backward direction relative to the current cursor position, and it can move to an absolute position also.But it is sensitive to changed data; means if underlaying data is changed it reflects in the ResultSet object.

Types of ResultSet Concurrency

  • ResultSet.CONCUR_READ_ONLY - It is default. If indicates the concurrency mode for the ResultSet read only.

  • ResultSet.CONCUR_UPDATABLE - It indicates the concurrency mode for a ResultSet object that may be updated.

First we need to obtain the ResultSet object by calling following methods from Connection object :

  • createStatement(int resultSetType, int resultSetConcurrency);
  • prepareStatement(String SQL, int resultSetType, int resultSetConcurrency);
  • prepareCall(String sql, int resultSetType, int resultSetConcurrency);

Where resultSetType can be ResultSet.TYPE_FORWARD_ONLY,ResultSet.TYPE_SCROLL_INSENSITIVE or ResultSet.TYPE_SCROLL_SENSITIVE. And resultSetConcurrency can be ResultSet.CONCUR_READ_ONLY or ResultSet.CONCUR_UPDATABLE.

Table structure in MySQL as follows :

use test;
create table Emp
(
code varchar(10) primary key,
name varchar(40) null,
city varchar(20),
salary int
);
insert into Emp values('a001','Ram Kumar','Noida',10000);
Download

Below Java Scource code to demostrates the Scrollable ResultSet

import java.awt.event.*;
import javax.swing.*;
import java.sql.*;
public class ScrollableResultSet extends JFrame implements ActionListener 
{
JPanel mainPanel;
JTextField tempId, tempName, tempCity, tempSalary;
JButton first, prev, next, last;
Connection con;
Statement stmt;
ResultSet res;

public ScrollableResultSet() 
{
super("Scrollable Resultset Demo");
setSize(600600);
getContentPane().add(mainPanel = new JPanel());
mainPanel.add(tempId = new JTextField(20));
mainPanel.add(tempName = new JTextField(20));
mainPanel.add(tempCity = new JTextField(20));
mainPanel.add(tempSalary = new JTextField(20));
mainPanel.add(first = new JButton("First"));
mainPanel.add(prev = new JButton("Previous"));
mainPanel.add(next = new JButton("Next"));
mainPanel.add(last = new JButton("Last"));
try {
  Class.forName("com.mysql.jdbc.Driver");
  con = DriverManager.getConnection
        ("jdbc:mysql://localhost/test?user=root&password=root");
  stmt = con.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
    ResultSet.CONCUR_READ_ONLY);
  res = stmt.executeQuery("select * from emp");
  if (res.next()) {
  display(res);
  }
catch (Exception e) {
  e.printStackTrace();
}
first.addActionListener(this);
prev.addActionListener(this);
next.addActionListener(this);
last.addActionListener(this);
setVisible(true);
}

@Override
public void actionPerformed(ActionEvent evt) {
Object obj = evt.getSource();
try {
  if (obj == first) {
  if (res.first()) {
  display(res);
  else {
  JOptionPane.showMessageDialog(null,"It is not first record");
  }
  }
  if (obj == prev) {
  if (res.previous()) {
  display(res);
  else {
  JOptionPane.showMessageDialog(null,"There is no previous record");
  }
  }
  if (obj == next) {
  if (res.next()) {
    display(res);
  else {
  JOptionPane.showMessageDialog(null, "It is last record");
  }
  }
  if (obj == last) {
  if (res.last()) {
    display(res);

  else {
  JOptionPane.showMessageDialog(null, "It is last record");
  }
  }

catch (Exception e) {
  e.printStackTrace();
}
}

public void display(ResultSet res) {
try {
  tempId.setText(res.getString(1));
  tempName.setText(res.getString(2));
  tempCity.setText(res.getString(3));
  tempSalary.setText(res.getString(4));
catch (Exception e) {
  e.printStackTrace();
}
}
public static void main(String[] args) {
new ScrollableResultSet();
}
}
Download

Previous Next


Write your Comment

Name :
Email Id : [ We will not publish your email id ]
Your Comment : [ Maximum 200 chars ]
Enter Security code