Spring RowMapper Example

Spring Jdbc framework provides a mechanism to fetch the records from a database using the query() method. This tutorial will explore the Spring Jdbc connectivity to fetch the records from the relational database.

1. Introduction

1.1 Spring Framework

• Spring is an open-source framework created to address the complexity of an enterprise application development
• One of the chief advantages of the Spring framework is its layered architecture, which allows the developer to be selective about which of its components they can use while providing a cohesive framework for J2EE application development
• Spring framework provides support and integration to various technologies for e.g.:
  • Support for Transaction Management
  • Support for interaction with the different databases
  • Integration with the Object Relationship frameworks for e.g. Hibernate, iBatis etc
  • Support for Dependency Injection which means all the required dependencies will be resolved with the help of containers
  • Support for REST style web-services

1.2 Spring Jdbc

Introduction of the JdbcTemplate class in the spring framework has helped developers to reduce the boiler-plate code, avoid common jdbc errors and focus on writing the sql queries to fetch the results. To instantiate the JdbcTemplate object, developers must inject the DataSource reference either via constructor or setter. For example:

Constructor

JdbcTemplate template = new JdbcTemplate(dataSource);

Or setting the DataSource reference via a setter. For example:

Setter

JdbcTemplate template = new JdbcTemplate();
template.setDataSource(dataSource);

Then use the template’s query() method for executing the select query. For example:

List<T> results = jdbcTemplate.query("SELECT * FROM table_name", new RowMapper<T>() {...});

1.2.1 RowMapper Interface

The RowMapper interface allows mapping a database record with the instance of a user-defined class. It internally iterates the sql result-set and adds it to a collection. Over here represents the method syntax.

public T mapRow(ResultSet rs, int rowNum) throws SQLException {

	. . . .
}

The advantage of using this interface saves a lot of time as internally it adds the result-set data into a collection. Now, open up the Eclipse IDE and let us see how to fetch the records by spring-jdbc-template using the xml-based configuration in the spring framework.

2. Spring RowMapper Example

Here is a systematic guide for implementing this tutorial in the spring framework.

2.1 Tools Used

We are using Eclipse Kepler SR2, JDK 8, MySQL and Maven. Having said that, we have tested the code against JDK 1.7 and it works well.

2.2 Project Structure

Firstly, let us 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 how to create a Java-based 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 the 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 the next button to proceed.

It will ask you to ‘Enter the group and the artifact id for the project’. 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 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>com.spring</groupId>
	<artifactId>SpringJdbcRowMapper</artifactId>
	<version>0.0.1-SNAPSHOT</version>
	<packaging>jar</packaging>
</project>

We can start adding the dependencies that developers want like Spring Core, Spring Context etc. Let us start building the application!

3. Application Building

Below are the steps involved in developing this application.

3.1 Maven Dependencies

Here, we specify the dependencies for the spring framework and the mysql connectivity. Maven will automatically resolve the rest dependencies such as Spring Beans, Spring Core etc. 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>com.spring</groupId>
	<artifactId>SpringJdbcRowMapper</artifactId>
	<version>0.0.1-SNAPSHOT</version>
	<dependencies>
		<!-- https://mvnrepository.com/artifact/org.springframework/spring-beans -->
		<dependency>
			<groupId>org.springframework</groupId>
			<artifactId>spring-beans</artifactId>
			<version>5.0.8.RELEASE</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>org.springframework</groupId>
			<artifactId>spring-jdbc</artifactId>
			<version>5.0.8.RELEASE</version>
		</dependency>
		<!-- https://mvnrepository.com/artifact/mysql/mysql-connector-java -->
		<dependency>
			<groupId>mysql</groupId>
			<artifactId>mysql-connector-java</artifactId>
			<version>8.0.12</version>
		</dependency>
	</dependencies>
	<build>
		<finalName>${project.artifactId}</finalName>
	</build>
</project>

3.2 Database & Table Creation

The following MySQL script creates a database called springjdbcrowmapper with a table: employee_tbl. Open MySQL terminal or workbench terminal and execute the SQLscript.

--
-- Create database `springjdbcrowmapper`
--

CREATE DATABASE springjdbcrowmapper;

--
-- Table structure for table `employee_tbl`
--

CREATE TABLE `employee_tbl` (
  `EMPLOYEE_ID` int(11) NOT NULL AUTO_INCREMENT,
  `EMPLOYEE_FULLNAME` varchar(100) NOT NULL,
  `EMPLOYEE_DESIGNATION` varchar(100) NOT NULL,
  `EMPLOYEE_SALARY` decimal(10,2) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dumping data for table `employee_tbl`
--

INSERT INTO `employee_tbl` (`EMPLOYEE_ID`, `EMPLOYEE_FULLNAME`, `EMPLOYEE_DESIGNATION`, `EMPLOYEE_SALARY`) VALUES
(1, 'Daniel Atlas', 'Technical Lead', '1300000.00'),
(2, 'Charlotte Neil', 'Technical Lead', '1100000.00'),
(3, 'Rakesh Mohan', 'Software Developer', '550000.00'),
(4, 'Jane Dow', 'Senior Software Developer', '970000.00'),
(5, 'Smith Greg', 'UI Developer', '1000000.00');

--
-- Selecting data from table `employee_tbl`
--

SELECT * FROM `employee_tbl`;

If everything goes well, the table will be shown in the MySQL Workbench.

3.3 Java Class Creation

Let us write the Java classes involved in this application.

3.3.1 Implementation of Model class

This class contains four fields with the constructor and setter properties. Add the following code to it:

Employee.java

package com.spring.model;

public class Employee {

	private int id;
	private String name;
	private String designation;
	private float salary;

	public Employee() {

	}

	public Employee(int id, String name, String designation, float salary) {		
		this.id = id;
		this.name = name;
		this.designation = designation;
		this.salary = salary;
	}

	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 getDesignation() {
		return designation;
	}

	public void setDesignation(String designation) {
		this.designation = designation;
	}

	public float getSalary() {
		return salary;
	}

	public void setSalary(float salary) {
		this.salary = salary;
	}

	@Override
	public String toString() {
		return "Employee [Id=" + id + ", Name=" + name + ", Designation=" + designation + ", Salary=" + salary + "]";
	}
}

3.3.2 Implementation of Row Mapper

This class maps a database record to the java model object. Add the following code to it:

Want to master Spring Framework ?

Subscribe to our newsletter and download the Spring Framework Cookbook right now!

In order to help you master the leading and innovative Java framework, we have compiled a kick-ass guide with all its major 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.

EmployeeRowMapper.java

package com.spring.jdbc.mapper;

import java.sql.ResultSet;
import java.sql.SQLException;

import org.springframework.jdbc.core.RowMapper;

import com.spring.model.Employee;

public class EmployeeRowMapper implements RowMapper {

	public Employee mapRow(ResultSet rs, int rowNum) throws SQLException {
		Employee emp = new Employee();
		emp.setId(rs.getInt("EMPLOYEE_ID"));
		emp.setName(rs.getString("EMPLOYEE_FULLNAME"));
		emp.setDesignation(rs.getString("EMPLOYEE_DESIGNATION"));
		emp.setSalary(rs.getInt("EMPLOYEE_SALARY"));

		return emp;
	}
}

3.3.3 Implementation of Dao

This class contains the jdbc-template property and the methods to fetch the records from the database. Add the following code to it:

EmployeeDaoImpl.java

package com.spring.jdbc.dao;

import java.util.Collection;
import java.util.List;

import org.springframework.jdbc.core.JdbcTemplate;

import com.spring.jdbc.mapper.EmployeeRowMapper;
import com.spring.model.Employee;

public class EmployeeDaoImpl implements EmployeeDao {

	private JdbcTemplate template;

	public JdbcTemplate getTemplate() {
		return template;
	}

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

	public Employee findById(int employeeId) {

		Employee employee = (Employee) getTemplate().queryForObject("SELECT * FROM Employee_tbl WHERE EMPLOYEE_ID=?", new Object[] { employeeId }, new EmployeeRowMapper());		
		return employee;
	}

	public Collection<Employee> findAll() {

		List<Employee> employeeslist = getTemplate().query("SELECT * FROM Employee_tbl", new EmployeeRowMapper());
		return employeeslist;
	}
}

3.3.4 Implementation of Utility class

The configuration class gets the bean definition from the spring configuration file and calls the Dao methods for fetching the records. Add the following code to it:

AppMain.java

package com.spring;

import java.util.Collection;

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

import com.spring.jdbc.dao.EmployeeDao;
import com.spring.model.Employee;

public class AppMain {

	public static void main(String[] args) {

		ApplicationContext ac = new ClassPathXmlApplicationContext("spring-jdbc.xml");

		EmployeeDao employeeDao = (EmployeeDao) ac.getBean("employeeDao");

		// Find By Id!
		Employee emp = employeeDao.findById(1);		
		System.out.println("-------------Find by Id---------------------");
		System.out.println(emp.toString());

		// Find all!
		Collection<Employee> emplist = employeeDao.findAll();
		System.out.println("-------------Find all---------------------");
		for (Employee employee : emplist) {
			System.out.println(employee.toString());
		}

		// Closing the application context!
		((AbstractApplicationContext) ac).close();
	}
}

3.4 Configuration Files

Following is the bean configuration file required for this tutorial.

• DriverManagerDataSource contains the database information such as driver class name, connection url, username and password. Do remember to change the username and password as per the settings on your environment
• The reference of the DriverManagerDataSource object is injected as an argument in the JdbcTemplate class dataSource property

A typical bean configuration file will look like this:

spring-jdbc.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"
	xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans.xsd">

	<bean id="ds"
		class="org.springframework.jdbc.datasource.DriverManagerDataSource">
		<property name="driverClassName" value="com.mysql.cj.jdbc.Driver" />
		<property name="url"
			value="jdbc:mysql://localhost:3306/springjdbcrowmapper" />
		<property name="username" value="root" />
		<property name="password" value="" />
	</bean>

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

	<bean id="employeeDao" class="com.spring.jdbc.dao.EmployeeDaoImpl">
		<property name="template" ref="jdbcTemplate"></property>
	</bean>
</beans>

4. Run the Application

To execute the application, right click on the AppMain class, Run As -> Java Application. Developers can debug the example and see what happens after every step. Enjoy!

5. Project Demo

The code shows the following logs as follows.

Logs

INFO: Loading XML bean definitions from class path resource [spring-jdbc.xml]
INFO: Loaded JDBC driver: com.mysql.jdbc.Driver

-------------Find by Id---------------------
Employee [Id=1, Name=Daniel Atlas, Designation=Technical Lead, Salary=1300000.0]

-------------Find all---------------------
Employee [Id=1, Name=Daniel Atlas, Designation=Technical Lead, Salary=1300000.0]
Employee [Id=2, Name=Charlotte Neil, Designation=Technical Lead, Salary=1100000.0]
Employee [Id=3, Name=Rakesh Mohan, Designation=Software Developer, Salary=550000.0]
Employee [Id=4, Name=Jane Dow, Designation=Senior Software Developer, Salary=970000.0]
Employee [Id=5, Name=Smith Greg, Designation=UI Developer, Salary=1000000.0]

Sep 28, 2018 12:27:25 AM org.springframework.context.support.AbstractApplicationContext doClose
INFO: Closing org.springframework.context.support.ClassPathXmlApplicationContext@4bf558aa: startup date [Fri Sep 28 00:27:25 IST 2018]; root of context hierarchy

That is all for this tutorial and I hope the article served you whatever you were looking for. Happy Learning and do not forget to share!

6. Conclusion

This post defines the RowMapper interface in the spring jdbc framework. Developers can download the sample application as an Eclipse project in the Downloads section.

7. Download the Eclipse Project

This was an example of the RowMapper interface in the spring jdbc framework.

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.