JDBC ResultSet Example


 

JDBC ResultSet Example

In this This tutorial you will learn how to use java.sql.ResultSet interface in an application, and how update a database table using ResultSet.

In this This tutorial you will learn how to use java.sql.ResultSet interface in an application, and how update a database table using ResultSet.

JDBC ResultSet Example

JDBC ResultSet is an interface of java.sql package. It is a table of data representing a database query result, which is obtained by executing the execute method of statement. A ResultSet object points the cursor to the first row of the data. Initially the cursor point before the fist row of the data. to move the cursor next() method is called. If there is no any row present in the result then it returns the false value.

 The default ResultSet object is not updateable therefore the cursor moves only forward from the first row to the last row only once. It is possible to make ResultSet object that is updateable and acrollable. to make such type of ResultSet object you need to write the following code.

Statement stmt = con.createStatement(
ResultSet.TYPE_SCROLL_INSENSITIVE,
ResultSet.CONCUR_UPDATABLE);
ResultSet rs = stmt.executeQuery("SELECT * FROM Student");

The ResultSet object also provides a getter method that that gets the value from the ResultSet object table. You need to specify only the Column name or index no of the table. For example- resultSet.getString("ColumnName");, resultSet.getInt("ColumnName");,

You can update the database table from the resultSet object as -

Statement statement = connection.createStatement(
ResultSet.TYPE_SCROLL_INSENSITIVE,
ResultSet.CONCUR_UPDATABLE);

resultSet.first();
// Setting the updating String
resultSet.updateString("Name", "vnay");
// Updating the first row
resultSet.updateRow();

An Example given below illustrate the above explanations, At first create database named student and then create a table student in the student database as

CREATE TABLE student (
RollNo int(9)  PRIMARY KEY NOT NULL,
Name tinytext NOT NULL,
Course varchar(25) NOT NULL,
Address text
 );

Then insert the value into it as

NSERT INTO student VALUES(1, 'Ram', 'B.Tech', 'Delhi') ;

NSERT INTO student VALUES(2, 'Syam', 'M.Tech', 'Mumbai') ;

 

JDBCResultSetExample.java

package roseindia.net;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class JDBCResultSetExample {
	Connection connection = null;

	public JDBCResultSetExample() {
		try {
			// Loading the driver
			Class.forName("com.mysql.jdbc.Driver");
		} catch (ClassNotFoundException e) {
			System.out.println(e.toString());
		}
	}

	public Connection createConnection() {
		Connection con = null;
		if (connection != null) {
			System.out.println("Cant create a connection");
		} else {
			try {
				// Crating a Connection to the Student database
				con = DriverManager.getConnection(
						"jdbc:mysql://localhost:3306/student", "root",
						"root");
				System.out.println("Connection created Successfully");
			} catch (SQLException e) {
				System.out.println(e.toString());
			}
		}
		return con;
	}

	public static void main(String[] args) throws SQLException {
		JDBCResultSetExample resultSetExample = new JDBCResultSetExample();
		Connection connection = resultSetExample.createConnection();
		try {
			// creating a statement object
			Statement statement = connection.createStatement(
					ResultSet.TYPE_SCROLL_INSENSITIVE,
					ResultSet.CONCUR_UPDATABLE);
			String query = "SELECT * FROM student";
			// executing a query string and storing it into the resultSet object
			ResultSet resultSet = statement.executeQuery(query);
			System.out.println("Before Updating....................\n");
			while (resultSet.next()) {
				// Printing results to the console
				System.out.println("Roll No- " + resultSet.getInt("RollNo")
						+ ", Name- " + resultSet.getString("Name")
						+ ", Course- " + resultSet.getString("Course")
						+ ", Address- " + resultSet.getString("Address"));
			}
			// setting the row to we have to update
			resultSet.first();
			// Setting the updating String
			resultSet.updateString("Name", "vnay");
			// Updating the first row
			resultSet.updateRow();
			System.out.println("\n\n After Updatig.......................\n");
			while (resultSet.next()) {
				// Printing results to the console
				System.out.println("Roll No- " + resultSet.getInt("RollNo")
						+ ", Name- " + resultSet.getString("Name")
						+ ", Course- " + resultSet.getString("Course")
						+ ", Address- " + resultSet.getString("Address"));
			}
		} catch (Exception e) {
			System.out.println(e.toString());
		} finally {
			connection.close();
		}
	}
}
When you run this application it will display message as shown below:

Connection created Successfully
Before Updating....................

Roll No- 1, Name- Ram, Course- B.Tech, Address- Delhi
Roll No- 2, Name- Syam, Course- M.Tech, Address- Mumbai


After Updating.......................

Roll No- 2, Name- Syam, Course- BCA, Address- Mumbai

Download this example code

Ads