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
xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 <modelVersion>4.0.0</modelVersion> <groupId>com.neveropen</groupId> <artifactId>ResultSetExtractorExample</artifactId> <version>0.0.1-SNAPSHOT</version> <dependencies> <dependency> <groupId>org.springframework</groupId> <artifactId>spring-jdbc</artifactId> <version>5.3.16</version> </dependency> <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.neveropen.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.neveropen.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 xsi:schemaLocation="http://www.springframework.org/schema/beans <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.neveropen.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.neveropen.dao.StudentDaoImpl;import com.neveropen.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
