JDBC – ResultSet – Code Nest

Java Database Connectivity (JDBC) is a Java API that allows developers to interact with relational databases. One of the key objects in JDBC is the ResultSet. In this blog, we’ll discuss what a ResultSet is, how to use it, and some of its important features.

What is a ResultSet

A ResultSet is an object that represents a table of data returned from a database query. It contains the rows of data that are retrieved from the database and can be used to iterate through the rows and access the data.

Using a ResultSet

To use a ResultSet, you first need to execute a query using a Statement or PreparedStatement object. The ResultSet object is then obtained by calling the executeQuery() method on the Statement or PreparedStatement object. The following code demonstrates this process:

Java

import java.sql.*;

public class JDBCExample {
    public static void main(String[] args) throws SQLException {
        // Load the JDBC driver
        Class.forName("com.mysql.cj.jdbc.Driver");

        // Connect to the database
        Connection conn = DriverManager.getConnection("jdbc:mysql://hostname:
                                                      port/dbname","username",
                                                      "password");

        // Create a statement
        Statement stmt = conn.createStatement();

        // Execute a SELECT statement
        ResultSet rs = stmt.executeQuery("SELECT id, name, age FROM Students");

        // Process the result set — without using rs.next, you don't have any results
        while (rs.next()) {
            int id = rs.getInt("id"); // gets Column ID and saves value to variable id
            String name = rs.getString("name");
            String age = rs.getInt("age");
            System.out.println("ID: " + id + ", Name: " + name);
        }

        // Close the resources
        rs.close();
        stmt.close();
        conn.close();
    }
}

Important Features of a ResultSet

Cursor: The ResultSet object has a cursor that can be used to iterate through the rows in the ResultSet. The next() method is used to move the cursor to the next row in the ResultSet.
Accessing Columns: The ResultSet provides various methods for accessing the columns in the ResultSet, such as getInt(), getString(), getDouble(), etc.
Scrollability: The ResultSet can be scrollable or not. A scrollable ResultSet allows you to move the cursor to any row in the ResultSet, whereas a non-scrollable ResultSet only allows you to move the cursor forward through the ResultSet.
Updatability: The ResultSet can be updatable or not. An updatable ResultSet allows you to modify the data in the ResultSet, whereas a non-updatable ResultSet does not allow you to modify the data.
ResultSet Implementation: The ResultSet interface is implemented by the JDBC driver. The implementation details of the ResultSet are determined by the JDBC driver and can vary from driver to driver. However, all implementations of the ResultSet interface must provide the same basic functionality and must provide the same methods.

ResultSet to List

In this chapter we want to take a clooser look, how to get a List from a ResultSet.

Unfortunately you cannot just specify as return parameter list. Therefore you have to iterate through all elements in Resultset

Java

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

public class ResultSetExample {
  public static void main(String[] args) {
    try {
      // Load the JDBC driver
      Class.forName("com.mysql.jdbc.Driver");
      // Establish a connection to the database
      Connection connection = DriverManager.getConnection
          ("jdbc:mysql://localhost:3306/mydb", "root", "password");
      // Create a statement
      Statement statement = connection.createStatement();
      // Execute a query and retrieve the ResultSet
      ResultSet resultSet = statement.executeQuery("SELECT * FROM employees");
      // Create a list to store the results
      
     
      List<Employee> employees = new ArrayList<Employee>();
       
      // Loop through the ResultSet 
      // and get for each employee neccessary columns  
      while (resultSet.next()) {
        int id = resultSet.getInt("id");
        String name = resultSet.getString("name");
        String department = resultSet.getString("department");
        // Create an Employee object and add it to the list
        employees.add(new Employee(id, name, department));
      }
      // Close the ResultSet and Statement
      resultSet.close();
      statement.close();
      // Close the connection
      connection.close();
      // Print the results
      for (Employee employee : employees) {
        System.out.println(employee);
      }
    } catch (Exception e) {
      e.printStackTrace();
    }
  }
}

class Employee {
  private int id;
  private String name;
  private String department;

  public Employee(int id, String name, String department) {
    this.id = id;
    this.name = name;
    this.department = department;
  }

  public int getId() {
    return id;
  }

  public void setId(int id) {
    this.id = id;
  }

  public String getName() {
    return name;
  }

  public void setName(String name) {
    this.name = name;
  }

  public String getDepartment() {
    return department;
  }

  public void setDepartment(String department) {
    this.department = department;
  }

  @Override
  public String toString() {
    return "Employee [id=" + id + ", name=" + name +
        ", department=" + department + "]";
  }
}

What is a ResultSet

Using a ResultSet

Important Features of a ResultSet

ResultSet to List

Related Articles

Kommentar verfassen Kommentieren abbrechen