ResultSetExtractor Interface In Spring JdbcTemplate (With Example)

ResultSetExtractor is a callback interface that is used by query methods of JdbcTemplate. It is a functionality of the Spring JDBC framework to extract the data from the result set. It simplifies the database handling in Spring JDBC. In this interface, you can fetch the records from the database using the query() method of JdbcTemplate class in a very easy and comprehensive manner. You need to pass the instance of ResultSetExtractor.

You don’t need to worry about how to handle exceptions, as they will be caught and handled by JdbcTemplate. The ResultSetExtractor Interface retrieves a ResultSet and returns the list. JDBC framework itself uses this interface for executing the query. The Syntax that is used by the query method using ResultSetExtractor is:

Public T query(String sql,ResultSetExtractor rse)

ResultSetExtractor interface method uses a single method named ‘extractData’ that accepts ResultSet instance as a parameter. The syntax of the method is:

public T extractData(ResultSet rs)throws SQLException,DataAccessException

Now we will give an example that uses the ResultSetExtractor Interface to show all the records of the table.

Let’s create a table inside the Oracle10g database as:

create table employee(  
id number(10),  
name varchar2(100),  
salary number(10)  
); 

Now we create a table named Employee.java that has three properties with constructors and setter and getters with an extra method toString()

package com.intellinuts;  

public class Employee {  
private int id;  
private String name;  
private float salary;  
//no-arg and parameterized constructors  
//getters and setters  
public String toString(){  
    return id+" "+name+" "+salary;  
}  
}  

Now, we will create EmployeeDao.java that includes one property jdbcTemplate and a method called getAllEmployees.

package com.intellinuts;  
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;  

public class EmployeeDao {  
private JdbcTemplate template;  

public void setTemplate(JdbcTemplate template) {  
    this.template = template;  

}  

public List<Employee> getAllEmployees(){  
 return template.query("select * from employee",new ResultSetExtractor<List<Employee>>(){  
    @Override  
     public List<Employee> extractData(ResultSet rs) throws SQLException,  
            DataAccessException {  
        List<Employee> list=new ArrayList<Employee>();  
        while(rs.next()){  
        Employee e=new Employee();  
        e.setId(rs.getInt(1));  
        e.setName(rs.getString(2));  
        e.setSalary(rs.getInt(3));  
        list.add(e);  
        }  
        return list;  
        }  
    });  
  }  
}  

Now we will create the XML configuration file named applicationContext.xml that stores the bean information. The DriverManagerDataSource is used by the applicationContext.xml file to store the details about the database such as driver class name, connection URL, username, and password. The JdbcTemplate class has a property named ‘datasource’ of the DriverManagerDataSource type.

We are required to provide the DriverManagerDataSource object’s reference in the JdbcTemplate class for the datasource property. The JdbcTemplate object is used in the EmployeeDao class which we pass by the setter method. But alternately, you can use the constructor also.

  
<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="ds" class="org.springframework.jdbc.datasource.DriverManagerDataSource">  
<property name="driverClassName" value="oracle.jdbc.driver.OracleDriver" />  
<property name="url" value="jdbc:oracle:thin:@localhost:1521:xe" />  
<property name="username" value="system" />  
<property name="password" value="oracle" />  
</bean>  

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

<bean id="edao" class="com.intellinuts.EmployeeDao">  
<property name="jdbcTemplate" ref="jdbcTemplate"></property>  
</bean>  

</beans>  

Now we create a class named Test.java that will get the bean information from the applicationContext.xml file and call the method getAllEmployees() of EmployeeDao class.

package com.intellinuts;  
 import java.util.List;  

import org.springframework.context.ApplicationContext;  
import org.springframework.context.support.ClassPathXmlApplicationContext;  
public class Test {  

public static void main(String[] args) {  
    ApplicationContext ctx=new ClassPathXmlApplicationContext("applicationContext.xml");  
    EmployeeDao dao=(EmployeeDao)ctx.getBean("edao");  
    List<Employee> list=dao.getAllEmployees();  

    for(Employee e:list)  
        System.out.println(e);  

    }  

}