spring

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!

Spring RowMapper - Application Project Structure
Fig. 1: Application Project Structure

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.

Spring RowMapper - Create a Maven Project
Fig. 2: Create a 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.

Spring RowMapper - Project Details
Fig. 3: Project Details

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.

Spring RowMapper - Archetype Parameters
Fig. 4: Archetype Parameters

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.

Spring RowMapper - Database & Table Creation
Fig. 5: Database & Table Creation

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:

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!

Spring RowMapper - Run the Application
Fig. 6: Run the Application

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.

Download
You can download the full source code of this example here: SpringJdbcRowMapper

Yatin

An experience full-stack engineer well versed with Core Java, Spring/Springboot, MVC, Security, AOP, Frontend (Angular & React), and cloud technologies (such as AWS, GCP, Jenkins, Docker, K8).
Subscribe
Notify of
guest

This site uses Akismet to reduce spam. Learn how your comment data is processed.

3 Comments
Oldest
Newest Most Voted
Inline Feedbacks
View all comments
fairymaiden
fairymaiden
5 years ago

too old

Martim
Martim
4 years ago

Very useful this article,Congratz!

Back to top button