JTC- JDBC

ResultSets

• ResultSet is an interface available in java.sql package.
• Subclass of ResultSet interface is provided by Driver vendor.
• ResultSet object contains the records returned by select statements.
• ResultSet object can be created by using the following methods:
ResultSet rs=st.executeQuery("select ... "); // Statement interface
ResultSet rs=ps.executeQuery(); // PreparedStatement interface
• Assume that there is a table called jtcstudents with 4 columns - sid,sname,email,phone.

Case 1:

String sql = “select * from jtcstudents”;
rs = st.executeQuery(sql);

Case 2:

String sql = “select sid,phone from jtcstudents”;
rs = st.executeQuery(sql);

When ResultSet object is created then initially ResultSet cursor points to before to the first record.
You can use the next() method to move the ResultSet pointer in the forward direction.
public boolean next() {
Checks whether next record is available or not.
If Next record is available then moves the pointer to next Record return true;
If Next record is not available then moves the pointer to next position return false;
}
If Previous record is not available then moves the pointer to previous position return false;
}

When ResultSet pointer is pointing one record then you can access the data of various columns using getXXX() methods.

public int getInt(int columnIndex)
public int getInt(String columnName)
public String getString(int columnIndex)
public String getString(String columnName) etc

Types of ResultSets:

Depending on the ResultSet cursor movements, you can divide the ResultSet into 2 types.

Forward-Only ResultSets
Scrollable ResultSets

Depending on the ResultSet updations, you can divide the ResultSets into 2 types.

Read-only ResultSets.
Updatable ResultSets or Dynamic ResultSets.

Forward-Only ResultSets

• When ResultSet is forward-only then:
• Pointer can be moved in the forward direction only.
• Pointer can be moved only once.
• Pointer can be moved in sequential order only.
• By default, ResultSets are forward only.

st=con.createStatement(); rs=st.executeQuery("select")

ps=con.prepareStatement("select "); rs=ps.executeQuery()

You can explicitly specify the ResultSets as forward only as follows:

st=con.createStatement(ResultSet.TYPE_FORWARD_ONLY,…);
rs=st.executeQuery("select ... ")
ps=con.prepareStatement("select... ", ResultSet.TYPE_FORWARD_ONLY,…);
rs=ps.executeQuery()

You can use the following methods on Forward-only ResultSet:

Scrollable ResultSets

When ResultSet is scrollable then:
Pointer can be moved in both forward and reverse direction.
Pointer can be moved multiple times.
Pointer can be moved in random order.
By default, ResultSets are not scrollable.

You can explicitly specify the ResultSets as scrollable as follows:

st=con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,…);
rs=st.executeQuery("select ... ")
ps=con.prepareStatement("select",ResultSet.TYPE_SCROLL_SENSITIVE,);
rs=ps.executeQuery()

You can use the following methods on Scrollable ResultSet.

public boolean next()
public void close()
public boolean isBeforeFirst()
public boolean isAfterLast()
public boolean isFirst()
public boolean isLast()
public int getRow()
public void beforeFirst()
public void afterLast()
public boolean first()
public boolean last()
public boolean absolute(int)
public boolean relative(int)
public boolean previous()
public XXX getXXX(int)
public XXX getXXX(String)
etc

            
package com.jtcindia.jdbc;

import java.sql.*;

public class resultSet1 {
	public static void displayRow(ResultSet rs) throws SQLException {
		int cid = rs.getInt(1);
		String cn = rs.getString(2);
		String em = rs.getString(3);
		String ci = rs.getString(4);
		System.out.println(cid + "\t" + cn + "\t" + em + "\t" + ci);
	}

	public static void main(String[] args) throws SQLException {
		Connection con = null;
		Statement st = null;
		ResultSet rs = null;
		try {
			Class.forName("com.mysql.cj.jdbc.Driver");
			String url = "jdbc:mysql://localhost:3306/tutorial?autoReconnect=true&useSSL=false";
			con = DriverManager.getConnection(url, "root", "root");
			String SQL = "select * from mycustomers";
			st = con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_READ_ONLY);
			rs = st.executeQuery(SQL);
			System.out.println("RS Type : " + st.getResultSetType());
			System.out.println("RS Updatability : " + st.getResultSetConcurrency());
			System.out.println("RS Holdability : " + st.getResultSetHoldability());
			System.out.println("---------Forward Order-------");
			while (rs.next()) {
				displayRow(rs);
			}
			System.out.println("---------Reverse Order-------");
			while (rs.previous()) {
				displayRow(rs);
			}
			System.out.println(rs.isBeforeFirst());
			System.out.println(rs.isFirst());
			rs.first();
			System.out.println(rs.isBeforeFirst());
			System.out.println(rs.isFirst());
			System.out.println("1st Record");
			displayRow(rs);
			System.out.println(rs.isAfterLast());
			System.out.println(rs.isLast());
			rs.last();
			System.out.println(rs.isAfterLast());
			System.out.println(rs.isLast());
			System.out.println("Last Record");
			displayRow(rs);
			rs.absolute(4);
			System.out.println("4th Record");
			displayRow(rs);
			rs.relative(-1);
			System.out.println("3rd Record");
			displayRow(rs);
			rs.relative(-2);
			System.out.println("1st Record");
			displayRow(rs);
			System.out.println("----Done-----");
		} catch (Exception ex) {
			ex.printStackTrace();
		} finally {
			rs.close();
			st.close();
			con.close();
		}
	}
}

Read-Only ResultSets

When ResultSet is read-only then you can just access the data from ResultSet object by calling getter methods and you can not do any updations on the ResultSet object.
Read-only ResultSet is also called as Static ResultSet.
By default, ResultSets are read-only.

st=con.createStatement(); rs=st.executeQuery("select")

ps=con.prepareStatement("select "); rs=ps.executeQuery()

You can explicitly specify the ResultSets as read-only as follows:

st=con.createStatement(ResultSet.TYPE_FORWARD_ONLY,ResultSet.CONCUR_READ_ONLY);
rs=st.executeQuery(sql)
ps=con.prepareStatement(sql,
ResultSet.TYPE_FORWARD_ONLY,ResultSet.CONCUR_READ_ONLY);
rs=ps.executeQuery()

You can use the following methods on Read-only ResultSet.

Updatable ResultSets

When ResultSet is Updatable then you can do the following operations on ResultSet object
Access the data from ResultSet
Insert records into ResultSet
Update the records of ResultSet
Delete the records from ResultSet.
When ResultSet is Updatable then it must be scrollable.
By default, ResultSets are not updatable.

You can explicitly specify the ResultSets as updatable as follows:

st=con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_UPDATABLE);
rs=st.executeQuery(sql)
ps=con.prepareStatement(sql,ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_UPDATABLE);
rs=ps.executeQuery()
public boolean next()
public void close()
public boolean isBeforeFirst()
public boolean isAfterLast()
public boolean isFirst()
public boolean isLast()
public boolean relative(int)
public boolean previous()
public XXX getXXX(int)
public XXX getXXX(String)
public void updateXXX(int index,XXX val)
public void moveToInsertRow()
public int getRow()
public void beforeFirst()
public void afterLast()
public boolean first()
public boolean last()
public boolean absolute(int)
public viod insertRow();
public void updateRow();
public void deleteRow();
etc

ResultSet can not be updatable even if you use ResultSet.CONCUR_UPDATABLE in the following cases:

When SELECT statement uses JOINS.
When SELECT statement uses Aggregate functions.
When SELECT statement uses * instead of column names. (InOracle only)

            
package com.jtcindia.jdbc;

import java.sql.*;

public class resultSet2 {
	public static void main(String[] args) throws SQLException {
		Connection con = null;
		Statement st = null;
		ResultSet rs = null;
		try {
			Class.forName("com.mysql.cj.jdbc.Driver");
			String url = "jdbc:mysql://localhost:3306/tutorial?autoReconnect=true&useSSL=false";
			con = DriverManager.getConnection(url, "root", "root");
			String SQL = "select * from mycustomers";
			st = con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
			rs = st.executeQuery(SQL);
			System.out.println("RS Type : " + st.getResultSetType());
			System.out.println("RS Updatability : " + st.getResultSetConcurrency());
			System.out.println("RS Holdability : " + st.getResultSetHoldability());
			System.out.println("---------Forward Order Before Update-------");
			while (rs.next()) {
				int cid = rs.getInt(1);
				String cn = rs.getString(2);
				String em = rs.getString(3);
				String ci = rs.getString(4);
				System.out.println(cid + "\t" + cn + "\t" + em + "\t" + ci);
			}
			// Insert Record into RS
			rs.moveToInsertRow();
			rs.updateInt(1, 502);
			rs.updateString(2, "hai");
			rs.updateString(3, "hai@jtc");
			rs.updateString(4, "Noida");
			rs.insertRow(); // Insert the Row in DB
			// Update the Row 4
			rs.absolute(4);
			rs.updateString(2, "test");
			rs.updateString(3, "test@jtc");
			rs.updateInt(4, 666);
			rs.updateRow(); // update the row with DB
			// Delete the Row 3
			rs.absolute(3);
			rs.deleteRow();
			// Delete the Row 1
			rs.absolute(1);
			rs.deleteRow();
			rs.beforeFirst();
			System.out.println("---------Forward Order After Update-------");
			while (rs.next()) {
				int cid = rs.getInt(1);
				String cn = rs.getString(2);
				String em = rs.getString(3);
				String ci = rs.getString(4);
				System.out.println(cid + "\t" + cn + "\t" + em + "\t" + ci);
			}
			System.out.println("----Done-----");
		} catch (Exception ex) {
			ex.printStackTrace();
		} finally {
			rs.close();
			st.close();
			con.close();
		}
	}
}

You can use the following method of Statement interface to find the ResultSet Type:

public int getResultSetType()

You can use the following method of Statement interface to find the ResultSet Concurrency:

public int getResultSetConcurrency()

You can use the following method of Statement interface to find the ResultSet Holdability:

public int getResultSetHoldability()

You can use the following methods of Connection interface to create the Statement:

Statement createSatement()
Statement createSatement(int rsType, int rsConcurrency)
Statement createSatement(int rsType, int rsConcurrency, intrsHoldability)

You can use the following methods of Connection interface to create the PreparedStatement:

PreparedStatement prepareSatement(String sql)
PreparedStatement prepareSatement(String sql, int rsType, int rsConcurrency)
PreparedStatement prepareSatement(String sq, int rsType, int rsConcurrency, int rsHoldability)

ResultSetMetaData

ResultSetMetaData is an interface available in java.sql package.
Subclass of ResultSetMetaData interface is provided by Driver vendor.
ResultSetMetaData is used to get the information about your ResultSet object.
You can use the following method of ResultSet to get the ResultSetMetaData object.
public ResultSetMetaData getMetaData()

            
//package com.jtcindia.jdbc;
import java.sql.*;

public class resultSet3 {
   public static void main(String[] args) throws SQLException {
      Connection con = null;
      Statement st = null;
      ResultSet rs = null;
      try {
      Class.forName("com.mysql.cj.jdbc.Driver");
		String url = "jdbc:mysql://localhost:3306/tutorial?autoReconnect=true&useSSL=false";
		con = DriverManager.getConnection(url, "root", "root");
      String SQL = "select * from mycustomers";
      st = con.createStatement();
      rs = st.executeQuery(SQL);
      ResultSetMetaData rsmd = rs.getMetaData();
      int cc = rsmd.getColumnCount();
      System.out.println("Col Count : " + cc);
      rs.first();
      for (int i = 1; i <= cc; i++) {
         String colName = rsmd.getColumnName(i);
         System.out.println(colName);
         String colLabel = rsmd.getColumnLabel(i);
         System.out.println(colLabel);
         String colType = rsmd.getColumnTypeName(i);
         System.out.println(colType);
         String colClsName = rsmd.getColumnClassName(i);
         System.out.println(colClsName);
         String tabName = rsmd.getTableName(i);
         System.out.println(tabName);
         String catName = rsmd.getCatalogName(i);
         System.out.println(catName);
         System.out.println("------------");
      }
      System.out.println("----Done-----");
      }catch (Exception ex) {
		ex.printStackTrace();
	} finally {
		rs.close();
		st.close();
		con.close();
	}
}
}

DatabaseMetaData

DatabaseMetaData is an interface available in java.sql package.
Subclass of DatabaseMetaData interface is provided by Driver vendor.
DatabaseMetaData is used to get the information about your database i.e you can find whether database is supporting the required features or not.
You can use the following method of Connection to get the DatabaseMetaData object.
public DatabaseMetaData getMetaData()

            
package com.jtcindia.jdbc;

import java.sql.*;
public class resultSet4 {
	public static void main(String[] args) throws SQLException {
		Connection con = null;
		try {
			Class.forName("com.mysql.cj.jdbc.Driver");
			String url = "jdbc:mysql://localhost:3306/tutorial?autoReconnect=true&useSSL=false";
			con = DriverManager.getConnection(url, "root", "root");
			DatabaseMetaData dbmd = con.getMetaData();
			System.out.println(dbmd.getDatabaseProductName());
			System.out.println(dbmd.getDatabaseProductVersion());
			System.out.println(dbmd.getDatabaseMajorVersion());
			System.out.println(dbmd.getDatabaseMinorVersion());
			System.out.println(dbmd.getDefaultTransactionIsolation()); // 2
			System.out.println(dbmd.getDriverName());
			System.out.println(dbmd.getJDBCMajorVersion());
			System.out.println(dbmd.getJDBCMinorVersion());
			System.out.println(dbmd.supportsBatchUpdates());
			System.out.println(dbmd.supportsFullOuterJoins());
			System.out.println(dbmd.supportsTransactions());
			System.out.println(dbmd.supportsGroupBy());
			System.out.println(dbmd.supportsMultipleTransactions());
			System.out.println(dbmd.supportsMultipleTransactions());
			System.out.println(dbmd.supportsMultipleResultSets());
			System.out.println("----Done-----");
		} catch (Exception ex) {
			ex.printStackTrace();
		} finally {
			con.close();
		}
	}
}

ResultSets

Forward-Only ResultSets

Scrollable ResultSets

Read-Only ResultSets

Updatable ResultSets

ResultSetMetaData

DatabaseMetaData

Find us

Call us

Mail us

Useful Links

Subscribe

ResultSets

Forward-Only ResultSets

Scrollable ResultSets

Read-Only ResultSets

Updatable ResultSets

ResultSetMetaData

DatabaseMetaData

Enquire Now