How to fetch data from database in Spring MVC

By Atul Rai | Last Updated: September 20, 2018 Previous       Next

In this tutorial, you will learn how to fetch data from database in Spring MVC. Spring framework provides a class JdbcTemplate inside org.springframework.jdbc.core package under the JDBC module. And by calling its query(String sql, RowMapper<T> rowMapper) method it will return the list (in my case, it will return the Employee type list).

You need to pass your SQL statement and object of RowMapper class.

List<Employee> list = jdbcTemp.query("SELECT * FROM EMP_DETAILS", new RowMapper<Employee>());

Steps to follow

To fetch data from database in Spring MVC application, you must follow these steps.

1. Register your front controller and enable Spring MVC feature.
2. Register your database credentials like URL, username, and password.
3. Execute the SQL statement and store in a list.
4. Iterate the list on JSP.

Technologies Used

Find the list of all technologies used in this application.

• Eclipse Oxygen
• Tomcat 9
• JDK 8
• Maven 3
• Spring5.0.2.RELEASE
• MySQL Database
• JSTL 1.2

Dependencies Required

To retrieve data from database in Spring MVC application, you need the following JARs.

<dependencies>

		<!-- spring mvc dependency -->
		<dependency>
			<groupId>org.springframework</groupId>
			<artifactId>spring-webmvc</artifactId>
			<version>5.0.2.RELEASE</version>
		</dependency>

		<!-- spring jdbc dependency -->
		<dependency>
			<groupId>org.springframework</groupId>
			<artifactId>spring-jdbc</artifactId>
			<version>5.0.2.RELEASE</version>
		</dependency>

		<!-- mysql databse connector -->
		<dependency>
			<groupId>mysql</groupId>
			<artifactId>mysql-connector-java</artifactId>
			<version>6.0.6</version>
		</dependency>

		<dependency>
			<groupId>jstl</groupId>
			<artifactId>jstl</artifactId>
			<version>1.2</version>
		</dependency>

</dependencies>

Project Structure

Final project structure of our application in Eclipse IDE will look like as follows.

Model Class

Create an Employee model class and mention its attribute like name, salary, department, etc.

package org.websparrow.model;

public class Employee {

	// Getters and Setters...
	private String name;
	private String designation;
	private String dept;
	private int salary;
	private String location;

}

DAO Classes

Create EmployeeDao interface and define the empList() method of Employee type.

package org.websparrow.dao;

import java.util.List;

import org.websparrow.model.Employee;

public interface EmployeeDao {

	public List<Employee> empList();

}

Create an implementation class EmployeeDaoImpl that implements EmployeeDao.

package org.websparrow.dao;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;

import javax.sql.DataSource;

import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.websparrow.model.Employee;

public class EmployeeDaoImpl implements EmployeeDao {

	private JdbcTemplate jdbcTemp;

	public EmployeeDaoImpl(DataSource dataSource) {
		jdbcTemp = new JdbcTemplate(dataSource);
	}

	@Override
	public List<Employee> empList() {
		List<Employee> list = jdbcTemp.query("SELECT * FROM EMP_DETAILS", new RowMapper<Employee>() {

			@Override
			public Employee mapRow(ResultSet rs, int rowNum) throws SQLException {
				Employee emp = new Employee();

				emp.setName(rs.getString("ename"));
				emp.setDesignation(rs.getString("designation"));
				emp.setDept(rs.getString("dept"));
				emp.setSalary(rs.getInt("salary"));
				emp.setLocation(rs.getString("location"));

				return emp;
			}

		});

		return list;
	}

}

Register Front Controller & Enable Spring MVC

package org.websparrow.config;

import org.springframework.web.servlet.support.AbstractAnnotationConfigDispatcherServletInitializer;

public class FontControllerConfig extends AbstractAnnotationConfigDispatcherServletInitializer {

	@Override
	protected Class<?>[] getRootConfigClasses() {

		return new Class[] { WebMvcConfig.class };
	}

	@Override
	protected Class<?>[] getServletConfigClasses() {

		return null;
	}

	@Override
	protected String[] getServletMappings() {
		return new String[] { "/" };
	}
}

package org.websparrow.config;

import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.ComponentScan;
import org.springframework.context.annotation.Configuration;
import org.springframework.jdbc.datasource.DriverManagerDataSource;
import org.springframework.web.servlet.config.annotation.EnableWebMvc;
import org.springframework.web.servlet.view.InternalResourceViewResolver;
import org.websparrow.dao.EmployeeDao;
import org.websparrow.dao.EmployeeDaoImpl;

@Configuration
@EnableWebMvc
@ComponentScan(basePackages = { "org.websparrow.controller", "org.websparrow.dao" })
public class WebMvcConfig {

	@Bean
	public InternalResourceViewResolver viewResolver() {

		InternalResourceViewResolver vr = new InternalResourceViewResolver();
		vr.setPrefix("/");
		vr.setSuffix(".jsp");

		return vr;
	}

	@Bean
	public DriverManagerDataSource getDataSource() {

		DriverManagerDataSource ds = new DriverManagerDataSource();
		ds.setDriverClassName("com.mysql.jdbc.Driver");
		ds.setUrl("jdbc:mysql://localhost:3306/websparrow");
		ds.setUsername("root");
		ds.setPassword("");

		return ds;
	}

	@Bean
	public EmployeeDao getEmployeeDao() {
		return new EmployeeDaoImpl(getDataSource());
	}

}

The getDataSource() method returns a configured DataSource bean. You may have to change the database URL, username and password according to your environments. And getEmployeeDao() method returns an implementation of the EmploueeDao interface, which is the EmployeeDaoImpl class. This bean will be injected to the controller class.

Controller Class

Create a controller class that handles the user request and @Autowired annotation inject an instance of the EmployeeDao implementation into this controller automatically.

package org.websparrow.controller;

import java.io.IOException;
import java.util.List;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.servlet.ModelAndView;
import org.websparrow.dao.EmployeeDao;
import org.websparrow.model.Employee;

@Controller
public class MyController {

	@Autowired
	private EmployeeDao empDao;

	@RequestMapping(value = "/fetch")
	public ModelAndView listEmployee(ModelAndView model) throws IOException {

		List<Employee> listEmp = empDao.empList();
		model.addObject("listEmp", listEmp);
		model.setViewName("index");

		return model;
	}
}

JSP Page

On the JSP, iterate the list returned by controller class using JSTL.

<%@ taglib uri="http://java.sun.com/jsp/jstl/core" prefix="c"%>
<html>
<body>
	<h2>How to fetch data from database in Spring MVC</h2>


	<table border="1">

		<th>Sr No</th>
		<th>Name</th>
		<th>Designation</th>
		<th>Department</th>
		<th>Salary</th>
		<th>Location</th>

		<c:forEach var="emp" items="${listEmp}" varStatus="status">
			<tr>
				<td>${status.index + 1}</td>
				<td>${emp.name}</td>
				<td>${emp.designation}</td>
				<td>${emp.dept}</td>
				<td>${emp.salary}</td>
				<td>${emp.location}</td>

			</tr>
		</c:forEach>
	</table>

</body>
</html>

Output

Run your application and hit the below URL in your browser address bar.

URL: localhost:8090/spring-mvc-fetch-data/fetch

You will get the following result.

References

1. Spring @Autowired annotation example
2. Data access with JDBC
3. Class JdbcTemplate