JDBC ResultSetExtractor Example

Spring provides a simplification in handling database access with the Spring JDBC Template. The org.springframework.jdbc.core.ResultSetExtractor interface is a callback interface used by JdbcTemplate’s query methods. Implementations of this interface perform the actual work of extracting results from an SQL ResultSet object.

In this article, we will try to show how the ResultSetExtractor mechanism can be applied to a Java application.
 
 
 
 

1. Introduction

In Spring JDBC development, developers can use JdbcTemplate and JdbcDaoSupport classes to simplify the overall database operation processes. Spring JdbcTemplate is a powerful mechanism to connect to the database and execute SQL queries. It internally uses JDBC API but eliminates a lot of problems of JDBC API.

The Spring JDBC Template has the following advantages compared with the standard JDBC API,

• The Spring JDBC template allows to clean-up the resources automatically, e.g. release the database connections.
• The Spring JDBC template converts the standard JDBC SQL Exceptions into RuntimeExceptions. This allows the programmer to react more flexible to the errors.
• The Spring JDBC template also converts the vendor specific error messages into better understandable error messages.

1.1 Problems of JDBC API

The problems of JDBC API are as follows:

• We need to write a lot of code before and after executing the query, such as creating Connection, Statement, Closing ResultSet, Statement, and Connection.
• We need to perform exception handling on the database logic.
• We need to handle transactions.
• Repetition of all these codes from one database logic to another is a time-consuming task.

Spring JdbcTemplate eliminates all the above-mentioned problems of JDBC API and provides methods to write the queries directly. Let’s take a look and understand the ResultSetExtractor interface.

1.2 ResultSetExtractor Interface

The org.springframework.jdbc.core.ResultSetExtractor interface can be used to fetch records from the database. It accepts a ResultSet as a method argument and returns the List. Implementation of this interface perform the actual work of extracting results from a ResultSet, but you don’t need to worry about exception handling.

SQL Exceptions will be caught and handled by the calling JdbcTemplate. This interface is mainly used within the JDBC framework itself. The org.springframework.jdbc.core.ResultSetExtractor interface defines only one method extractData that accepts ResultSet instance as a parameter. The syntax of the method is given below:

Method Signature

public List extractData(ResultSet rs) throws SQLException, DataAccessException {
	// Database Logic
}

1.3 Download and Install MySQL

You can watch this video in order to download and install the MySQL database on your windows operations system.

Now, open up the Eclipse IDE and let’s start building the application!

2. JDBC ResultSetExtractor Example

2.1 Tools Used

We are using Eclipse Kepler SR2, JDK 8, MySQL database and Maven (to download the MySQL connector and Spring libraries). Having said that, we have tested the code against JDK 1.7 and it works well.

2.2 Project Structure

Firstly, let’s review the final project structure, in case you are confused about where you should create the corresponding files or folder later!

2.3 Project Creation

This section will demonstrate on how to create a Dynamic Web Java Maven project with Eclipse. In Eclipse IDE, go to File -> New -> Maven Project

In the New Maven Project window, it will ask you to select project location. By default, ‘Use default workspace location‘ will be selected. Select the ‘Create a simple project (skip archetype selection)‘ checkbox and just click on next button to proceed.

It will ask you to ‘Enter a group id for the artifact.’ We will input the details as shown in the below image. The version number will be by default 0.0.1-SNAPSHOT.

Click on Finish and now the creation of a maven project is completed. If you observe, it has downloaded the maven dependencies and a pom.xml file will be created. It will have the following code:

pom.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 http://maven.apache.org/xsd/maven-4.0.0.xsd">
	<modelVersion>4.0.0</modelVersion>
	<groupId>JdbcResultSetExtractor</groupId>
	<artifactId>JdbcResultSetExtractor</artifactId>
	<version>0.0.1-SNAPSHOT</version>
</project>

We can start adding the dependencies that developers want like MySQL, Spring Jars etc. Let’s start building the application!

3. Application Building

Below are the steps involved in developing this application:

3.1 Database & Table Creation

This tutorial uses a database called tutorialDb. The database is not included when you create the project in eclipse so you first need to create the database to follow this tutorial:

• Create a new database tutorialDb as:

CREATE DATABASE tutorialDb;

• Use the created database tutorialDb to create table as:

USE tutorialDb;

• Create the table tech_editors as shown below:

CREATE TABLE tech_editors (id int(11) NOT NULL AUTO_INCREMENT, name varchar(100) DEFAULT NULL, salary int(11) NOT NULL, PRIMARY KEY (id));

• Now we shall insert some values into the tech_editors table as:

INSERT INTO tech_editors (id, name, salary) VALUES (1, 'Java Code Geek', 10000);

INSERT INTO tech_editors (id, name, salary) VALUES (2, 'Harry Potter', 5000);

INSERT INTO tech_editors (id, name, salary) VALUES (3, 'Lucifer', 5500);

If everything goes well, the table will be shown as below in the MySQL workbench:

3.2 Maven Dependencies

In this example, we are using latest MySQL version i.e. mysql-connector-java-5.1.41 and Spring dependencies. The updated file will have the following code:

pom.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 http://maven.apache.org/xsd/maven-4.0.0.xsd">
	<modelVersion>4.0.0</modelVersion>
	<groupId>JdbcResultSetExtractor</groupId>
	<artifactId>JdbcResultSetExtractor</artifactId>
	<version>0.0.1-SNAPSHOT</version>
	<packaging>jar</packaging>
	<dependencies>
		<dependency>
			<groupId>mysql</groupId>
			<artifactId>mysql-connector-java</artifactId>
			<version>5.1.41</version>
		</dependency>
		<dependency>
			<groupId>org.springframework</groupId>
			<artifactId>spring-jdbc</artifactId>
			<version>4.3.10.RELEASE</version>
		</dependency>
		<dependency>
			<groupId>org.springframework</groupId>
			<artifactId>spring-context</artifactId>
			<version>4.3.9.RELEASE</version>
		</dependency>
	</dependencies>
	<build>
		<finalName>${project.artifactId}</finalName>
	</build>
</project>

3.3 Java Class Creation

Let’s create the required java files. Right click on src/main/java folder, New -> Package.

A new pop window will open where we will enter the package name as com.jdbc.resultset.extractor.

Once the package is created in the application, we will need to create the implementation class. Right click on the newly created package, New -> Class.

A new pop window will open and enter the file name as TechEditor. The POJO class will be created inside the package: com.jdbc.resultset.extractor.

Repeat the step (i.e. Fig. 8) and enter the filename as TechEditorDao. The DAO class with the JDBC Template will be created inside the package: com.jdbc.resultset.extractor.

Again, repeat the step listed in Fig. 8 and enter the file name as JdbcResultSet. The implementation class for testing the Spring JDBC Template will be created inside the package: com.jdbc.resultset.extractor.

Want to be a JDBC Master ?

Subscribe to our newsletter and download the JDBC Ultimate Guide right now!

In order to help you master database programming with JDBC, we have compiled a kick-ass guide with all the major JDBC features and use cases! Besides studying them online you may download the eBook in PDF format!

Download NOW!

Thank you!

We will contact you soon.

3.3.1 Implementation of POJO Class

This class contains 3 properties with setter and getters method and also defines an extra method toString(). Add the following code to it:

TechEditor.java

package com.jdbc.resultset.extractor;

public class TechEditor {

	private int editor_id;
	private String editor_name;
	private float editor_salary;

	public int getEditor_id() {
		return editor_id;
	}

	public void setEditor_id(int editor_id) {
		this.editor_id = editor_id;
	}

	public String getEditor_name() {
		return editor_name;
	}

	public void setEditor_name(String editor_name) {
		this.editor_name = editor_name;
	}
	public float getEditor_salary() {
		return editor_salary;
	}

	public void setEditor_salary(float editor_salary) {
		this.editor_salary = editor_salary;
	}

	public String toString() {
		return "Editor Id= " + editor_id + ", Name= " + editor_name + ", Salary= " + editor_salary +" $";
	}
}

3.3.2 Implementation of DAO Class

This class contains the JdbcTemplate property and a method to fetch the records from the database. Add the following code to it:

TechEditorDao.java

package com.jdbc.resultset.extractor;

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 TechEditorDao {

	private JdbcTemplate templateObj;

	public void setTemplateObj(JdbcTemplate template) {
		this.templateObj = template;
	}

	// Implementing Custom ResultSetExtractor To Fetch Data From The Db
	public List getAllEditors() {
		return templateObj.query("SELECT * FROM tech_editors", new ResultSetExtractor<List>() {
			public List extractData(ResultSet resultSetObj) throws SQLException, DataAccessException {
				List editorList = new ArrayList();
				while(resultSetObj.next()) {
					TechEditor techEditorObj = new TechEditor();
					techEditorObj.setEditor_id(resultSetObj.getInt(1));
					techEditorObj.setEditor_name(resultSetObj.getString("name"));
					techEditorObj.setEditor_salary(resultSetObj.getFloat(3));
					editorList.add(techEditorObj);
				}
				return editorList;
			}
		});
	}
}

3.3.3 Implementation of Main Class

This class gets the DAO bean from the applicationContext.xml file and calls the DAO class method. Add the following code to it:

JdbcResultSet.java

package com.jdbc.resultset.extractor;

import java.util.List;

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

public class JdbcResultSet {

	@SuppressWarnings("resource")
	public static void main(String[] args) {
		ApplicationContext ctxObj = new ClassPathXmlApplicationContext("applicationContext.xml");  
		TechEditorDao daoObj = (TechEditorDao)ctxObj.getBean("techDao");

		// This Method Is Used To Fetch Records From The Db
		List editorList = daoObj.getAllEditors();
		for(TechEditor techEditorObj : editorList) {
			System.out.println(techEditorObj.toString());
		}
	}
}

3.4 Spring Configuration File

Spring loads applicationContext.xml file and creates the ApplicationContext which provides the configuration information to an application. This interface provides standard bean factory lifecycle capabilities. In this file,

• The DriverManagerDataSource contains the information about the database such as driver class name, connection URL, username and password.
• A property named dataSource in the JdbcTemplate class of org.springframework.jdbc.datasource.DriverManagerDataSource type.

To configure the spring framework, we need to implement a configuration file i.e. applicationContext.xml. Right click on src/main/resources folder, New -> Other.

A new pop window will open and select the wizard as the XML file.

Again, a pop-up window will open. Verify the parent folder location as JdbcResultSetExtractor/src/main/resources and enter the file name as applicationContext.xml. Click Finish.

Once the file is created, add the following code to it:

applicationContext.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">

	<!-- Database Connection Settings -->
	<bean id="dataSourceObj" class="org.springframework.jdbc.datasource.DriverManagerDataSource">
		<property name="driverClassName" value="com.mysql.jdbc.Driver" />
		<property name="url" value="jdbc:mysql://localhost:3306/tutorialDb" />
		<property name="username" value="root" />
		<property name="password" value="admin@123" />
	</bean>

	<!-- Bean Definition For DataSource -->
	<bean id="templateObj" class="org.springframework.jdbc.core.JdbcTemplate">
		<property name="dataSource" ref="dataSourceObj" />
	</bean>

	<!-- Bean Definition For TechEditorDao -->
	<bean id="techDao" class="com.jdbc.resultset.extractor.TechEditorDao">
		<property name="templateObj" ref="templateObj" />
	</bean>
</beans>

4. Run the Application

To run the application, Right click on the JdbcResultSet class, Run As -> Java Application.

5. Project Demo

The code shows the following status as output:

That’s all for this post. Happy Learning!!

6. Conclusion

Here, we understood what is ResultSetExtractor interface and how we can implement the same in Java.

7. Download the Eclipse Project

This was an example of JDBC ResultSetExtractor.

Do you want to know how to develop your skillset to become a Java Rockstar?

Subscribe to our newsletter to start Rocking right now!

To get you started we give you our best selling eBooks for FREE!

1. JPA Mini Book

2. JVM Troubleshooting Guide

3. JUnit Tutorial for Unit Testing

4. Java Annotations Tutorial

5. Java Interview Questions

6. Spring Interview Questions

7. Android UI Design

and many more ....

I agree to the Terms and Privacy Policy

Sign up

Thank you!

We will contact you soon.