Spring – ResultSetExtractor

ResultSetExtractor is an interface that is used to fetch the records from the database. It’s a callback interface that is used by JDBC Template’s query() method where we need to pass the instance of ResultSetExtractor in order to fetch the data.

Syntax of query() method of ResultSetExtractor:

public T query(String sqlQuery, ResultSetExtractor<T> resultSetExtractor)

In order to fetch the data using ResultSetExtractor, we need to implement the ResultSetExtractor interface and provide the definition for its method. It has only one method. i.e., extractData() which takes an instance of ResultSet as an argument and returns the list.

Syntax of extractData() method:

public T extractData(ResultSet resultSet) throws SQL Exception, DataAccessException

Example

In this example, we will extract all the records from a Student table using ResultSetExtractor. For this tutorial, we will be using the following schema for the Student table.

Student(id INT, name VARCHAR(45), department VARCHAR(45))

Step By Step Implementation:

Step 1: Create Table

In this step, we will create a Student table to store students’ information. For this tutorial, we will assume you have created the following table in your database.

CREATE TABLE STUDENT(
id INT,
name VARCHAR(45),
department VARCHAR(45));

After creating the table we will insert the following data in our table.

INSERT INTO STUDENT VALUES(1, "geek", "computer science");

Step 2: Adding dependencies

In this step, we will add the maven dependencies to our application. Add the following dependencies to your pom.xml 

XML

<project xmlns="http://maven.apache.org/POM/4.0.0"
         xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:schemaLocation="http://maven.apache.org/POM/4.0.0
                             https://maven.apache.org/xsd/maven-4.0.0.xsd">
  <modelVersion>4.0.0</modelVersion>
  <groupId>com.geeksforgeeks</groupId>
  <artifactId>ResultSetExtractorExample</artifactId>
  <version>0.0.1-SNAPSHOT</version>

  <dependencies>

          <!-- https://mvnrepository.com/artifact/org.springframework/spring-jdbc -->
          <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-jdbc</artifactId>
            <version>5.3.16</version>
        </dependency>

        <!-- https://mvnrepository.com/artifact/org.springframework/spring-context -->
        <dependency>
                <groupId>org.springframework</groupId>
                <artifactId>spring-context</artifactId>
                <version>5.0.8.RELEASE</version>
        </dependency>

        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>5.1.44</version>
        </dependency>

  </dependencies>

</project>

Step 3: Create a model class

Now, we will create a model class for our students. This class will have three-member variables id, name, and department. We will also define its getters and setters method along with the toString() method.

Java

public class Student {

    // Member variables
    private int id;
    private String name;
    private String department;

    // Getters and Setters method
    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;
    }

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

Step 4: Create a Dao interface

Now, we will create an interface and name it is StudentDao, which we will use to access data from the database of data stoarage. We need to define getAllStudentDetails() method which will return all the details of the student.

Java

import java.util.List;

import com.geeksforgeeks.model.Student;

public interface StudentDao {
    // This method will return all
    // the details of the students
    public List<Student> getAllStudentDetails();

}

Step 5: Create an implementation class for Dao Interface

In this step, we will create an implementation class StudentDaoImpl.java. This class implements the StudentDao interface and provides the definition to the getAllStudentDetails() method of the StudentDao interface. In this class, we will also implement the ResultSetExtractor interface and provide the definition of its extractData() method. 

Java

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.ResultSetExtractor;

import com.geeksforgeeks.model.Student;

public class StudentDaoImpl implements StudentDao{

    // Defining JdbcTemplate as member variable in order
    // to use the query() method of the JdbcTemplate's class
    private JdbcTemplate jdbcTemplate;

    public JdbcTemplate getJdbcTemplate() {
        return jdbcTemplate;
    }

    public void setJdbcTemplate(JdbcTemplate jdbcTemplate) {
        this.jdbcTemplate = jdbcTemplate;
    }

    // This method will return the list
    // of all the details of student
    public List<Student> getAllStudentDetails() {

        // Implementation of ResultSetExtractor interface
        return jdbcTemplate.query("SELECT * FROM student", new ResultSetExtractor<List<Student>>() {

            // extractData() is ResultSetExtractor
            // interface's method
            public List<Student> extractData(ResultSet rs) throws SQLException, DataAccessException {
                List<Student> studentDetailList = new ArrayList<Student>();
                while(rs.next()) {
                    Student student = new Student();
                    // 1, 2 and 3 are the indices of the data present
                    // in the database respectively
                    student.setId(rs.getInt(1));
                    student.setName(rs.getString(2));
                    student.setDepartment(rs.getString(3));
                    studentDetailList.add(student);
                }
                return studentDetailList;
            }

        });
    }
}

Step 6: Bean Configuration

In this step, we will create the spring configuration file and name it application-context.xml. We will configure our beans and use the factory-method attribute for bean creation. In order to make a connection to the database, we need the following information username, password, database connection, URL, and the driver class name.  All this information is contained in the DriverManagerDataSource class, it has getConnection() method which returns a connection of java type. We are using the instance of JdbcTemplate in our StudentDao class and passing it using the setter injection method. 

Note: In application-context, you need to define the whole path of your dao class

XML

<?xml version="1.0" encoding="UTF-8"?>
<beans
    xmlns="http://www.springframework.org/schema/beans"
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xmlns:p="http://www.springframework.org/schema/p"
    xsi:schemaLocation="http://www.springframework.org/schema/beans
                         http://www.springframework.org/schema/beans/spring-beans-3.0.xsd">

    <bean id="dataSource" class="org.springframework.jdbc.datasource.DriverManagerDataSource">
        <property name="driverClassName" value="com.mysql.jdbc.Driver" />
        <property name="url" value="jdbc:mysql://localhost:3306/student_db?autoReconnect=true&useSSL=false" />
        <property name="username" value="root" />
        <property name="password" value="root" />
    </bean>

    <bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
        <property name="dataSource" ref="dataSource"></property>
    </bean>

    <bean id="studentDao" class="com.geeksforgeeks.dao.StudentDaoImpl">
        <property name="jdbcTemplate" ref="jdbcTemplate"></property>
    </bean>

</beans>

Step 7: Creating Utilities Class

Now, we will create a Utility class for testing our application. For this create a new class and name it TestResultSetExtractor.java and add the following code to it.

Java

import java.util.List;

import org.springframework.context.support.AbstractApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;

import com.geeksforgeeks.dao.StudentDaoImpl;
import com.geeksforgeeks.model.Student;

public class TestResultSetExtractor {

    public static void main(String[] args) {

        // Reading the application-context file using
        // class path of spring context xml file
        AbstractApplicationContext context = new ClassPathXmlApplicationContext("application-context.xml");

        // Spring check the blueprint for studentDao bean
        // from application-context.xml file and return it
        StudentDaoImpl studentDaoImpl = (StudentDaoImpl)context.getBean("studentDao");

        // Getting student data
        List<Student> studentDetailList = studentDaoImpl.getAllStudentDetails();

        for(Student index : studentDetailList) {
            System.out.println(index);
        }
    }
}

Step 8: Output

Now, we will run our application.

Output