Hướng dẫn sử dụng Spring MVC và Spring JDBC Transaction
Xem thêm các chuyên mục:
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


