Hướng dẫn sử dụng Spring MVC và Spring JDBC Transaction
Xem thêm các chuyên mục:

Là một website được viết trên công nghệ web Flutter vì vậy hỗ trợ rất tốt cho người học, kể cả những người học khó tính nhất.
Hiện tại website đang tiếp tục được cập nhập nội dung cho phong phú và đầy đủ hơn. Mong các bạn nghé thăm và ủng hộ website mới của chúng tôi.


Xem thêm:
Tài liệu được viết dựa trên:
-
Eclipse 4.6 (NEON)
-
Spring MVC 4
-
Spring JDBC
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)
);
- File/New/Other..



Nhập vào:
- Group Id: org.o7planning
- Artifact Id: SpringMVCJdbcTransaction
- Package: org.o7planning.tutorial.springmvcjdbc

Project đã được tạo ra:

Bạn đừng lo lắng với thông báo lỗi khi Project vừa được tạo ra. Lý do là bạn chưa khai báo thư viện Servlet.

Sử dụng Java >= 6.

Sử dụng 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>
Trong pom.xml tôi đã khai báo các thư viện JDBC cho cả 3 loại database Oracle, MySQL và 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>

Trong hướng dẫn này hỗ trợ 3 loại cơ sở dữ liệu khác nhau là Oracle, MySQL và SQL Server, bạn có thể cấu hình file datasource-cfg.xml theo một trong các mẫu dưới đây:
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

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();
}
}
Trong ApplicationContextConfig bạn cần khai báo 2 Spring BEAN là dataSource và transactionManager.
dataSource BEAN sẽ tải thông tin database tại file ds/datasouce-cfg.properties.
dataSource BEAN sẽ tải thông tin database tại file 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 {
// Lưu trữ các giá thuộc tính load bởi @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;
}
}

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 có thể chú thích trên các method, hoặc chú thích trên class nó sẽ có tác dụng trên hết tất cả các method có trong 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";
}
}

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>
Cấu hình để chạy:


Nhập vào:
- Name: Run SpringMVCJdbcTransaction
- Base directory: ${workspace_loc:/SpringMVCJdbcTransaction}
- Goals: tomcat7:run


