Spring MVC and Spring JDBC Transaction Tutorial with Examples
2. Script to create tables
No ADS
ORACLE:
create table DEPARTMENT (
DEPT_ID number(10,0) not null,
DEPT_NAME varchar2(255 char) not null,
DEPT_NO varchar2(20 char) not null unique,
LOCATION varchar2(255 char),
primary key (DEPT_ID)
);
MySQL:
create table DEPARTMENT (
DEPT_ID integer not null,
DEPT_NAME varchar(255) not null,
DEPT_NO varchar(20) not null,
LOCATION varchar(255),
primary key (DEPT_ID),
unique (DEPT_NO)
);
SQLServer:
Create table DEPARTMENT (
DEPT_ID int not null,
DEPT_NAME varchar(255) not null,
DEPT_NO varchar(20) not null,
LOCATION varchar(255),
primary key (DEPT_ID),
unique (DEPT_NO)
);
3. Create Maven Project
No ADS
- File/New/Other..
Enter:
- Group Id: org.o7planning
- Artifact Id: SpringMVCJdbcTransaction
- Package: org.o7planning.tutorial.springmvcjdbc
Project was created:
Do not worry with the error message when Project has been created. The reason is that you have not declared Servlet library.
Using Java >= 6.
4. Configure Maven & web.xml
No ADS
Using Servlet >= 3.
web.xml
<web-app xmlns="http://java.sun.com/xml/ns/javaee"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://java.sun.com/xml/ns/javaee
http://java.sun.com/xml/ns/javaee/web-app_3_0.xsd"
version="3.0">
<display-name>SpringMVCJdbcTransaction Web Application</display-name>
</web-app>
In pom.xml, I declared JDBC libaries to 3 types of database Oracle, MySQL and SQL Server.
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/maven-v4_0_0.xsd">
<modelVersion>4.0.0</modelVersion>
<groupId>org.o7planning</groupId>
<artifactId>SpringMVCJdbcTransaction</artifactId>
<packaging>war</packaging>
<version>1.0.0</version>
<name>SpringMVCJdbcTransaction Maven Webapp</name>
<url>http://maven.apache.org</url>
<properties>
<java-version>1.7</java-version>
</properties>
<repositories>
<!-- Repository for ORACLE JDBC Driver -->
<repository>
<id>codelds</id>
<url>https://code.lds.org/nexus/content/groups/main-repo</url>
</repository>
</repositories>
<dependencies>
<dependency>
<groupId>junit</groupId>
<artifactId>junit</artifactId>
<version>3.8.1</version>
<scope>test</scope>
</dependency>
<!-- Servlet API -->
<!-- http://mvnrepository.com/artifact/javax.servlet/javax.servlet-api -->
<dependency>
<groupId>javax.servlet</groupId>
<artifactId>javax.servlet-api</artifactId>
<version>3.1.0</version>
<scope>provided</scope>
</dependency>
<!-- Jstl for jsp page -->
<!-- http://mvnrepository.com/artifact/javax.servlet/jstl -->
<dependency>
<groupId>javax.servlet</groupId>
<artifactId>jstl</artifactId>
<version>1.2</version>
</dependency>
<!-- JSP API -->
<!-- http://mvnrepository.com/artifact/javax.servlet.jsp/jsp-api -->
<dependency>
<groupId>javax.servlet.jsp</groupId>
<artifactId>jsp-api</artifactId>
<version>2.2</version>
<scope>provided</scope>
</dependency>
<!-- Spring dependencies -->
<!-- http://mvnrepository.com/artifact/org.springframework/spring-core -->
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-core</artifactId>
<version>4.3.3.RELEASE</version>
</dependency>
<!-- http://mvnrepository.com/artifact/org.springframework/spring-web -->
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-web</artifactId>
<version>4.3.3.RELEASE</version>
</dependency>
<!-- http://mvnrepository.com/artifact/org.springframework/spring-webmvc -->
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-webmvc</artifactId>
<version>4.3.3.RELEASE</version>
</dependency>
<!-- http://mvnrepository.com/artifact/org.springframework/spring-jdbc -->
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-jdbc</artifactId>
<version>4.3.3.RELEASE</version>
</dependency>
<!-- http://mvnrepository.com/artifact/org.springframework/spring-tx -->
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-tx</artifactId>
<version>4.3.3.RELEASE</version>
</dependency>
<!-- MySQL JDBC driver -->
<!-- http://mvnrepository.com/artifact/mysql/mysql-connector-java -->
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>6.0.4</version>
</dependency>
<!-- Oracle JDBC driver -->
<dependency>
<groupId>com.oracle</groupId>
<artifactId>ojdbc6</artifactId>
<version>11.2.0.3</version>
</dependency>
<!-- SQLServer JDBC driver (JTDS) -->
<!-- http://mvnrepository.com/artifact/net.sourceforge.jtds/jtds -->
<dependency>
<groupId>net.sourceforge.jtds</groupId>
<artifactId>jtds</artifactId>
<version>1.3.1</version>
</dependency>
</dependencies>
<build>
<finalName>SpringMVCJdbcTransaction</finalName>
<plugins>
<!-- Config: Maven Tomcat Plugin -->
<!-- http://mvnrepository.com/artifact/org.apache.tomcat.maven/tomcat7-maven-plugin -->
<plugin>
<groupId>org.apache.tomcat.maven</groupId>
<artifactId>tomcat7-maven-plugin</artifactId>
<version>2.2</version>
<!-- Config: contextPath and Port (Default - /SpringMVCJdbcTransaction,8080) -->
<!--
<configuration>
<path>/</path>
<port>8899</port>
</configuration>
-->
</plugin>
</plugins>
</build>
</project>
5. datasource-cfg.properties
No ADS
In this tutorial, 3 types of different databases as Oracle, MySQL and SQL Server will be supported, you can configure file datasource-cfg.xml by one of the following samples:
datasource-cfg.properties (ORACLE)
# DataSource (ORACLE)
ds.database-driver=oracle.jdbc.driver.OracleDriver
ds.url=jdbc:oracle:thin:@localhost:1521:db12c
ds.username=simplehr
ds.password=12345
datasource-cfg.properties (MySQL)
# DataSource
ds.database-driver=com.mysql.jdbc.Driver
ds.url=jdbc:mysql://localhost:3306/mydatabase
ds.username=root
ds.password=12345
datasource-cfg.properties (SQL Server)
# DataSource
ds.database-driver=net.sourceforge.jtds.jdbc.Driver
ds.url=jdbc:jtds:sqlserver://localhost:1433/simplehr;instance=SQLEXPRESS
ds.username=shoppingcart
ds.password=12345
6. Configure Spring MVC
No ADS
SpringWebAppInitializer.java
package org.o7planning.springmvcjdbc.config;
import javax.servlet.FilterRegistration;
import javax.servlet.ServletContext;
import javax.servlet.ServletException;
import javax.servlet.ServletRegistration;
import org.springframework.web.WebApplicationInitializer;
import org.springframework.web.context.support.AnnotationConfigWebApplicationContext;
import org.springframework.web.filter.CharacterEncodingFilter;
import org.springframework.web.servlet.DispatcherServlet;
public class SpringWebAppInitializer implements WebApplicationInitializer {
@Override
public void onStartup(ServletContext servletContext) throws ServletException {
AnnotationConfigWebApplicationContext appContext = new AnnotationConfigWebApplicationContext();
appContext.register(ApplicationContextConfig.class);
ServletRegistration.Dynamic dispatcher = servletContext.addServlet("SpringDispatcher",
new DispatcherServlet(appContext));
dispatcher.setLoadOnStartup(1);
dispatcher.addMapping("/");
// UtF8 Charactor Filter.
FilterRegistration.Dynamic fr = servletContext.addFilter("encodingFilter", CharacterEncodingFilter.class);
fr.setInitParameter("encoding", "UTF-8");
fr.setInitParameter("forceEncoding", "true");
fr.addMappingForUrlPatterns(null, true, "/*");
}
}
WebMvcConfig.java
package org.o7planning.springmvcjdbc.config;
import org.springframework.context.annotation.Configuration;
import org.springframework.web.servlet.config.annotation.DefaultServletHandlerConfigurer;
import org.springframework.web.servlet.config.annotation.EnableWebMvc;
import org.springframework.web.servlet.config.annotation.ResourceHandlerRegistry;
import org.springframework.web.servlet.config.annotation.WebMvcConfigurerAdapter;
@Configuration
@EnableWebMvc
public class WebMvcConfig extends WebMvcConfigurerAdapter {
@Override
public void addResourceHandlers(ResourceHandlerRegistry registry) {
// Default..
}
@Override
public void configureDefaultServletHandling(DefaultServletHandlerConfigurer configurer) {
configurer.enable();
}
}
In ApplicationContextConfig you need to declare 2 Spring BEAN is dataSource and transactionManager.
dataSource BEAN will load the database information in ds/datasouce-cfg.properties.
dataSource BEAN will load the database information in ds/datasouce-cfg.properties.
ApplicationContextConfig.java
package org.o7planning.springmvcjdbc.config;
import javax.sql.DataSource;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.ComponentScan;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.PropertySource;
import org.springframework.context.annotation.PropertySources;
import org.springframework.core.env.Environment;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;
import org.springframework.jdbc.datasource.DriverManagerDataSource;
import org.springframework.transaction.annotation.EnableTransactionManagement;
import org.springframework.web.servlet.view.InternalResourceViewResolver;
@Configuration
@ComponentScan("org.o7planning.springmvcjdbc.*")
@EnableTransactionManagement
// Load to Environment.
@PropertySources({ @PropertySource("classpath:ds/datasource-cfg.properties") })
public class ApplicationContextConfig {
// The Environment class serves as the property holder
// and stores all the properties loaded by the @PropertySource
@Autowired
private Environment env;
@Bean(name = "viewResolver")
public InternalResourceViewResolver getViewResolver() {
InternalResourceViewResolver viewResolver = new InternalResourceViewResolver();
viewResolver.setPrefix("/WEB-INF/pages/");
viewResolver.setSuffix(".jsp");
return viewResolver;
}
@Bean(name = "dataSource")
public DataSource getDataSource() {
DriverManagerDataSource dataSource = new DriverManagerDataSource();
// See: datasouce-cfg.properties
dataSource.setDriverClassName(env.getProperty("ds.database-driver"));
dataSource.setUrl(env.getProperty("ds.url"));
dataSource.setUsername(env.getProperty("ds.username"));
dataSource.setPassword(env.getProperty("ds.password"));
System.out.println("## getDataSource: " + dataSource);
return dataSource;
}
@Bean(name = "transactionManager")
public DataSourceTransactionManager getTransactionManager() {
DataSourceTransactionManager txManager = new DataSourceTransactionManager();
DataSource dataSource = this.getDataSource();
txManager.setDataSource(dataSource);
return txManager;
}
}
7. Java Classes
No ADS
Department.java
package org.o7planning.springmvcjdbc.model;
public class Department {
private Integer deptId;
private String deptNo;
private String deptName;
private String location;
public Department() {
}
public Department(Integer deptId, String deptNo, String deptName,
String location) {
this.deptId = deptId;
this.deptNo = deptNo;
this.deptName = deptName;
this.location = location;
}
public Integer getDeptId() {
return deptId;
}
public void setDeptId(Integer deptId) {
this.deptId = deptId;
}
public String getDeptNo() {
return deptNo;
}
public void setDeptNo(String deptNo) {
this.deptNo = deptNo;
}
public String getDeptName() {
return deptName;
}
public void setDeptName(String deptName) {
this.deptName = deptName;
}
public String getLocation() {
return location;
}
public void setLocation(String location) {
this.location = location;
}
}
DepartmentMapper.java
package org.o7planning.springmvcjdbc.mapper;
import java.sql.ResultSet;
import java.sql.SQLException;
import org.o7planning.springmvcjdbc.model.Department;
import org.springframework.jdbc.core.RowMapper;
public class DepartmentMapper implements RowMapper<Department> {
public static final String BASE_SQL = //
"Select d.dept_id,d.dept_no,d.dept_name,d.location "//
+ " from Department d ";
@Override
public Department mapRow(ResultSet rs, int rowNum) throws SQLException {
Integer deptId = rs.getInt("dept_id");
String deptNo = rs.getString("dept_no");
String deptName = rs.getString("dept_name");
String location = rs.getString("location");
return new Department(deptId, deptNo, deptName, location);
}
}
@Transactional can annotate on the method, or on the class, it will work on all methods of class.
DepartmentDAO.java
package org.o7planning.springmvcjdbc.dao;
import java.util.List;
import javax.sql.DataSource;
import org.o7planning.springmvcjdbc.mapper.DepartmentMapper;
import org.o7planning.springmvcjdbc.model.Department;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.support.JdbcDaoSupport;
import org.springframework.stereotype.Repository;
import org.springframework.transaction.annotation.Transactional;
@Repository
@Transactional
public class DepartmentDAO extends JdbcDaoSupport {
@Autowired
public DepartmentDAO(DataSource dataSource) {
this.setDataSource(dataSource);
}
private int getMaxDeptId() {
String sql = "Select max(d.dept_id) from Department d";
Integer value = this.getJdbcTemplate().queryForObject(sql, Integer.class);
if (value == null) {
return 0;
}
return value;
}
public Department findDepartment(String deptNo) {
String sql = DepartmentMapper.BASE_SQL //
+ " where d.dept_no = ?";
Object[] params = new Object[] { deptNo };
DepartmentMapper mapper = new DepartmentMapper();
Department dept = this.getJdbcTemplate().queryForObject(sql, params, mapper);
return dept;
}
public List<Department> listDepartment() {
String sql = DepartmentMapper.BASE_SQL;
Object[] params = new Object[] {};
DepartmentMapper mapper = new DepartmentMapper();
List<Department> list = this.getJdbcTemplate().query(sql, params, mapper);
return list;
}
public void insertDepartment(String deptName, String location) {
String sql = "Insert into Department (dept_id,dept_no,dept_name,location) "//
+ " values (?,?,?,?) ";
int deptId = this.getMaxDeptId() + 1;
String deptNo = "D" + deptId;
Object[] params = new Object[] { deptId, deptNo, deptName, location };
this.getJdbcTemplate().update(sql, params);
}
}
Controller:
MainController.java
package org.o7planning.springmvcjdbc.controller;
import java.util.List;
import org.o7planning.springmvcjdbc.dao.DepartmentDAO;
import org.o7planning.springmvcjdbc.model.Department;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.ui.Model;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;
@Controller
public class MainController {
@Autowired
private DepartmentDAO departmentDAO;
@RequestMapping(value = { "/" }, method = RequestMethod.GET)
public String welcomePage(Model model) {
departmentDAO.insertDepartment("HR", "Chicago");
departmentDAO.insertDepartment("INV", "Hanoi");
List<Department> list = departmentDAO.listDepartment();
model.addAttribute("departments", list);
return "index";
}
}
8. Views
No ADS
index.jsp
<%@page contentType="text/html" pageEncoding="UTF-8"%>
<%@ taglib uri="http://java.sun.com/jsp/jstl/core" prefix="c" %>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>Department List</title>
</head>
<body>
<div align="center">
<h1>Department List</h1>
<table border="1">
<tr>
<th>No</th>
<th>Dept No</th>
<th>Dept Name</th>
<th>Location</th>
</tr>
<c:forEach var="dept" items="${departments}" varStatus="status">
<tr>
<td>${status.index + 1}</td>
<td>${dept.deptNo}</td>
<td>${dept.deptName}</td>
<td>${dept.location}</td>
</tr>
</c:forEach>
</table>
</div>
</body>
</html>
No ADS
Spring MVC Tutorials
- Spring Tutorial for Beginners
- Install Spring Tool Suite for Eclipse
- Spring MVC Tutorial for Beginners - Hello Spring 4 MVC
- Configure Static Resources in Spring MVC
- Spring MVC Interceptors Tutorial with Examples
- Create a Multiple Languages web application with Spring MVC
- Spring MVC File Upload Tutorial with Examples
- Simple Login Java Web Application using Spring MVC, Spring Security and Spring JDBC
- Spring MVC Security with Hibernate Tutorial with Examples
- Spring MVC Security and Spring JDBC Tutorial (XML Config)
- Social Login in Spring MVC with Spring Social Security
- Spring MVC and Velocity Tutorial with Examples
- Spring MVC and FreeMarker Tutorial with Examples
- Use Template in Spring MVC with Apache Tiles
- Spring MVC and Spring JDBC Transaction Tutorial with Examples
- Use Multiple DataSources in Spring MVC
- Spring MVC and Hibernate Transaction Tutorial with Examples
- Spring MVC Form Handling and Hibernate Tutorial with Examples
- Run background scheduled tasks in Spring
- Create a Java Shopping Cart Web Application using Spring MVC and Hibernate
- Simple CRUD example with Spring MVC RESTful Web Service
- Deploy Spring MVC on Oracle WebLogic Server
Show More