Spring 通过使用jdbcTemplate和org.springframework.jdbc.core和相关包中的相关类,使使用 JDBC 变得容易。有关JdbcTemplate 基础知识的介绍性教程,请参阅:Spring JDBC 模板简单示例。本教程通过演示如何在Spring MVC应用程序中集成JdbcTemplate来进一步。本教程中的示例应用程序管理如下所示的联系人列表:示例应用程序是使用以下软件/技术开发的(当然,您可以使用较新的版本):
- 爪哇 7
- 日食开普勒
- 弹簧框架 4.0
- JSTL 1.2
- MySQL 数据库 5.5
- 马文 3
1. 创建 MySQL 数据库
执行以下 MySQL 脚本以创建名为contactdb的数据库和一个名为contact 的表:
1
2
3
4
5
6
7
8
9
10
|
create database contactdb;
CREATE TABLE `contact` (
`contact_id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(45) NOT NULL,
`email` varchar(45) NOT NULL,
`address` varchar(45) NOT NULL,
`telephone` varchar(45) NOT NULL,
PRIMARY KEY (`contact_id`)
) ENGINE=InnoDB AUTO_INCREMENT=25 DEFAULT CHARSET=utf8
|
2. 在 Eclipse 中创建 Maven 项目
建议使用spring-mvc-archetype来创建项目(参见:在一分钟内使用 Maven 和 Eclipse 创建 Spring MVC 项目)。以下是项目的最终结构:
pom.xml文件中的以下 XML 部分用于向项目添加依赖项配置:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
|
< properties >
< java.version >1.7</ java.version >
< spring.version >4.0.3.RELEASE</ spring.version >
< cglib.version >2.2.2</ cglib.version >
</ properties >
< dependencies >
<!-- Spring core & mvc -->
< dependency >
< groupId >org.springframework</ groupId >
< artifactId >spring-context</ artifactId >
< version >${spring.version}</ version >
</ dependency >
< dependency >
< groupId >org.springframework</ groupId >
< artifactId >spring-webmvc</ artifactId >
< version >${spring.version}</ version >
</ dependency >
< dependency >
< groupId >org.springframework</ groupId >
< artifactId >spring-orm</ artifactId >
< version >${spring.version}</ version >
< type >jar</ type >
< scope >compile</ scope >
</ dependency >
<!-- CGLib for @Configuration -->
< dependency >
< groupId >cglib</ groupId >
< artifactId >cglib-nodep</ artifactId >
< version >${cglib.version}</ version >
< scope >runtime</ scope >
</ dependency >
<!-- Servlet Spec -->
< dependency >
< groupId >javax.servlet</ groupId >
< artifactId >javax.servlet-api</ artifactId >
< version >3.1.0</ version >
< scope >provided</ scope >
</ dependency >
< dependency >
< groupId >javax.servlet.jsp</ groupId >
< artifactId >javax.servlet.jsp-api</ artifactId >
< version >2.3.1</ version >
< scope >provided</ scope >
</ dependency >
< dependency >
< groupId >jstl</ groupId >
< artifactId >jstl</ artifactId >
< version >1.2</ version >
</ dependency >
</ dependencies >
|
3. 编码模型类
模型类 -Contact.java- 非常简单:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
|
package net.codejava.spring.model;
public class Contact {
private int id;
private String name;
private String email;
private String address;
private String telephone;
public Contact() {
}
public Contact(String name, String email, String address, String telephone) {
this .name = name;
this .email = email;
this .address = address;
this .telephone = telephone;
}
// getters and setters
}
|
此类只是将表联系人中的一行映射到普通的旧 Java 对象 (POJO) -Contact。
4. 编码 DAO 类
ContactDAO接口定义了对联系人表执行 CRUD 操作的方法:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
|
package net.codejava.spring.dao;
import java.util.List;
import net.codejava.spring.model.Contact;
/**
* Defines DAO operations for the contact model.
* @author www.codejava.net
*
*/
public interface ContactDAO {
public void saveOrUpdate(Contact contact);
public void delete( int contactId);
public Contact get( int contactId);
public List<Contact> list();
}
|
这是一个实现 -ContactDAOImpl.java:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
|
package net.codejava.spring.dao;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;
import javax.sql.DataSource;
import net.codejava.spring.model.Contact;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.ResultSetExtractor;
import org.springframework.jdbc.core.RowMapper;
/**
* An implementation of the ContactDAO interface.
* @author www.codejava.net
*
*/
public class ContactDAOImpl implements ContactDAO {
private JdbcTemplate jdbcTemplate;
public ContactDAOImpl(DataSource dataSource) {
jdbcTemplate = new JdbcTemplate(dataSource);
}
@Override
public void saveOrUpdate(Contact contact) {
// implementation details goes here...
}
@Override
public void delete( int contactId) {
// implementation details goes here...
}
@Override
public List<Contact> list() {
// implementation details goes here...
}
@Override
public Contact get( int contactId) {
// implementation details goes here...
}
}
|
请注意声明JdbcTemplate的开头部分,并且通过构造函数注入数据源对象:
1
2
3
4
5
|
private JdbcTemplate jdbcTemplate;
public ContactDAOImpl(DataSource dataSource) {
jdbcTemplate = new JdbcTemplate(dataSource);
}
|
现在,让我们看一下每种方法的实现细节。插入或更新新联系人:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
|
public void saveOrUpdate(Contact contact) {
if (contact.getId() > 0 ) {
// update
String sql = "UPDATE contact SET name=?, email=?, address=?, "
+ "telephone=? WHERE contact_id=?" ;
jdbcTemplate.update(sql, contact.getName(), contact.getEmail(),
contact.getAddress(), contact.getTelephone(), contact.getId());
} else {
// insert
String sql = "INSERT INTO contact (name, email, address, telephone)"
+ " VALUES (?, ?, ?, ?)" ;
jdbcTemplate.update(sql, contact.getName(), contact.getEmail(),
contact.getAddress(), contact.getTelephone());
}
}
|
请注意,如果联系人对象的 ID 大于零,请更新它;否则就是插入。注意:为了提高代码可读性,可以使用NamedParameterJdbcTemplate,而不是使用问号 (?) 作为占位符。您也可以使用SimpleJdbcInsert类,该类使用起来更方便。删除联系人:
1
2
3
4
|
public void delete( int contactId) {
String sql = "DELETE FROM contact WHERE contact_id=?" ;
jdbcTemplate.update(sql, contactId);
}
|
列出所有联系人:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
|
public List<Contact> list() {
String sql = "SELECT * FROM contact" ;
List<Contact> listContact = jdbcTemplate.query(sql, new RowMapper<Contact>() {
@Override
public Contact mapRow(ResultSet rs, int rowNum) throws SQLException {
Contact aContact = new Contact();
aContact.setId(rs.getInt( "contact_id" ));
aContact.setName(rs.getString( "name" ));
aContact.setEmail(rs.getString( "email" ));
aContact.setAddress(rs.getString( "address" ));
aContact.setTelephone(rs.getString( "telephone" ));
return aContact;
}
});
return listContact;
}
|
请注意,使用RowMapper将结果集中的行映射到 POJO 对象。为了更方便,你可以像这样使用BeanPropertyRowMapper类:
1
2
3
4
5
|
public List<Contact> list() {
String sql = "SELECT * FROM Contact" ;
return jdbcTemplate.query(sql, BeanPropertyRowMapper.newInstance(Contact. class ));
}
|
确保 Contact 类声明的字段名称与数据库表中的列名称完全匹配。使用BeanPropertyRowMapper更方便,但性能比使用RowMapper 慢。获取特定联系人:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
|
public Contact get( int contactId) {
String sql = "SELECT * FROM contact WHERE contact_id=" + contactId;
return jdbcTemplate.query(sql, new ResultSetExtractor<Contact>() {
@Override
public Contact extractData(ResultSet rs) throws SQLException,
DataAccessException {
if (rs.next()) {
Contact contact = new Contact();
contact.setId(rs.getInt( "contact_id" ));
contact.setName(rs.getString( "name" ));
contact.setEmail(rs.getString( "email" ));
contact.setAddress(rs.getString( "address" ));
contact.setTelephone(rs.getString( "telephone" ));
return contact;
}
return null ;
}
});
}
|
请注意使用ResultSetExtractor将单行提取为 POJO。你也可以像这样使用BeanPropertyRowMapper类:
1
2
3
4
|
public Contact get( int contactId) {
String sql = "SELECT * FROM Contact WHERE id=" + contactId;
return jdbcTemplate.queryForObject(sql, BeanPropertyRowMapper.newInstance(Contact. class ));
}
|
它大大简化了代码,但换来的是性能降低。
5. 编码弹簧MVC配置
基于Java的类和注释用于配置这个Spring MVC应用程序。下面是MvcConfiguration类的代码:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
|
package net.codejava.spring.config;
import javax.sql.DataSource;
import net.codejava.spring.dao.ContactDAO;
import net.codejava.spring.dao.ContactDAOImpl;
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.ViewResolver;
import org.springframework.web.servlet.config.annotation.EnableWebMvc;
import org.springframework.web.servlet.config.annotation.ResourceHandlerRegistry;
import org.springframework.web.servlet.config.annotation.WebMvcConfigurerAdapter;
import org.springframework.web.servlet.view.InternalResourceViewResolver;
@Configuration
@ComponentScan (basePackages= "net.codejava.spring" )
@EnableWebMvc
public class MvcConfiguration extends WebMvcConfigurerAdapter{
@Bean
public ViewResolver getViewResolver(){
InternalResourceViewResolver resolver = new InternalResourceViewResolver();
resolver.setPrefix( "/WEB-INF/views/" );
resolver.setSuffix( ".jsp" );
return resolver;
}
@Override
public void addResourceHandlers(ResourceHandlerRegistry registry) {
registry.addResourceHandler( "/resources/**" ).addResourceLocations( "/resources/" );
}
@Bean
public DataSource getDataSource() {
DriverManagerDataSource dataSource = new DriverManagerDataSource();
dataSource.setDriverClassName( "com.mysql.jdbc.Driver" );
dataSource.setUrl( "jdbc:mysql://localhost:3306/contactdb" );
dataSource.setUsername( "root" );
dataSource.setPassword( "P@ssw0rd" );
return dataSource;
}
@Bean
public ContactDAO getContactDAO() {
return new ContactDAOImpl(getDataSource());
}
}
|
请注意,getDataSource() 方法返回一个配置的数据源Bean。您可能需要根据您的环境更改数据库 URL、用户名和密码。getContactDAO()方法返回ContactDAO接口的实现,该接口是ContactDAOImpl类。此 Bean 将被注入到控制器类中,如下所述。
6. 配置 Spring MVC 调度程序 Servlet
要为我们的Java Web应用程序启用Spring MVC,请更新Web部署描述符文件(web.xml),如下所示:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
|
<? xml version = "1.0" encoding = "UTF-8" ?>
< web-app version = "2.4" xmlns = "http://java.sun.com/xml/ns/j2ee"
xmlns:xsi = "http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation = "http://java.sun.com/xml/ns/j2ee http://java.sun.com/xml/ns/j2ee/web-app_2_4.xsd" >
< display-name >SpringMvcJdbcTemplate</ display-name >
< context-param >
< param-name >contextClass</ param-name >
< param-value >
org.springframework.web.context.support.AnnotationConfigWebApplicationContext
</ param-value >
</ context-param >
< listener >
< listener-class >org.springframework.web.context.ContextLoaderListener</ listener-class >
</ listener >
< servlet >
< servlet-name >SpringDispatcher</ servlet-name >
< servlet-class >org.springframework.web.servlet.DispatcherServlet</ servlet-class >
< init-param >
< param-name >contextClass</ param-name >
< param-value >
org.springframework.web.context.support.AnnotationConfigWebApplicationContext
</ param-value >
</ init-param >
< init-param >
< param-name >contextConfigLocation</ param-name >
< param-value >net.codejava.spring</ param-value >
</ init-param >
< load-on-startup >1</ load-on-startup >
</ servlet >
< servlet-mapping >
< servlet-name >SpringDispatcher</ servlet-name >
< url-pattern >/</ url-pattern >
</ servlet-mapping >
< session-config >
< session-timeout >30</ session-timeout >
</ session-config >
</ web-app >
|
7. 编码弹簧控制器类
HomeController类的框架:
1
2
3
4
5
6
7
|
public class HomeController {
@Autowired
private ContactDAO contactDAO;
// handler methods go here...
}
|
请注意,我们使用@Autowired注释让 Spring 自动将ContactDAO实现的实例注入到这个控制器中。每个处理程序方法都使用此contactDAO对象来执行必要的 CRUD 操作。让我们看看每种方法的实现细节。列出所有联系人的处理程序方法(也用作主页):
1
2
3
4
5
6
7
8
|
@RequestMapping (value= "/" )
public ModelAndView listContact(ModelAndView model) throws IOException{
List<Contact> listContact = contactDAO.list();
model.addObject( "listContact" , listContact);
model.setViewName( "home" );
return model;
}
|
显示新联系人表单的处理程序方法:
1
2
3
4
5
6
7
|
@RequestMapping (value = "/newContact" , method = RequestMethod.GET)
public ModelAndView newContact(ModelAndView model) {
Contact newContact = new Contact();
model.addObject( "contact" , newContact);
model.setViewName( "ContactForm" );
return model;
}
|
插入/更新联系人的处理程序方法:
1
2
3
4
5
|
@RequestMapping (value = "/saveContact" , method = RequestMethod.POST)
public ModelAndView saveContact( @ModelAttribute Contact contact) {
contactDAO.saveOrUpdate(contact);
return new ModelAndView( "redirect:/" );
}
|
删除联系人的处理程序方法:
1
2
3
4
5
6
|
@RequestMapping (value = "/deleteContact" , method = RequestMethod.GET)
public ModelAndView deleteContact(HttpServletRequest request) {
int contactId = Integer.parseInt(request.getParameter( "id" ));
contactDAO.delete(contactId);
return new ModelAndView( "redirect:/" );
}
|
用于检索特定联系人的详细信息以进行编辑的处理程序方法:
1
2
3
4
5
6
7
8
9
|
@RequestMapping (value = "/editContact" , method = RequestMethod.GET)
public ModelAndView editContact(HttpServletRequest request) {
int contactId = Integer.parseInt(request.getParameter( "id" ));
Contact contact = contactDAO.get(contactId);
ModelAndView model = new ModelAndView( "ContactForm" );
model.addObject( "contact" , contact);
return model;
}
|
8. 编码联系人列表页面(主页)
以下是主页.jsp页面的源代码,其中显示联系人列表以及用于创建新联系人、编辑和删除联系人的操作链接。
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
|
<%@page contentType="text/html" pageEncoding="UTF-8"%>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN"
"http://www.w3.org/TR/html4/loose.dtd">
<%@ taglib uri="http://java.sun.com/jsp/jstl/core" prefix="c" %>
< html >
< head >
< meta http-equiv = "Content-Type" content = "text/html; charset=UTF-8" >
< title >Contact Manager Home</ title >
</ head >
< body >
< div align = "center" >
< h1 >Contact List</ h1 >
< h3 >< a href = "/newContact" >New Contact</ a ></ h3 >
< table border = "1" >
< th >No</ th >
< th >Name</ th >
< th >Email</ th >
< th >Address</ th >
< th >Telephone</ th >
< th >Action</ th >
< c:forEach var = "contact" items = "${listContact}" varStatus = "status" >
< tr >
< td >${status.index + 1}</ td >
< td >${contact.name}</ td >
< td >${contact.email}</ td >
< td >${contact.address}</ td >
< td >${contact.telephone}</ td >
< td >
< a href = "/editContact?id=${contact.id}" >Edit</ a >
< a href = "/deleteContact?id=${contact.id}" >Delete</ a >
</ td >
</ tr >
</ c:forEach >
</ table >
</ div >
</ body >
</ html >
|
请注意,此 JSP 页使用 JSTL 和 EL 表达式。
9. 编码联系表格页面
联系人表单页面 (ContactForm.jsp) 显示用于创建新联系人或更新旧联系人的详细信息。这是它的完整源代码:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
|
<%@ page language="java" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8"%>
<%@ taglib prefix="form" uri="http://www.springframework.org/tags/form"%>
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN"
"http://www.w3.org/TR/html4/loose.dtd">
< html >
< head >
< meta http-equiv = "Content-Type" content = "text/html; charset=UTF-8" >
< title >New/Edit Contact</ title >
</ head >
< body >
< div align = "center" >
< h1 >New/Edit Contact</ h1 >
< form:form action = "saveContact" method = "post" modelAttribute = "contact" >
< table >
< form:hidden path = "id" />
< tr >
< td >Name:</ td >
< td >< form:input path = "name" /></ td >
</ tr >
< tr >
< td >Email:</ td >
< td >< form:input path = "email" /></ td >
</ tr >
< tr >
< td >Address:</ td >
< td >< form:input path = "address" /></ td >
</ tr >
< tr >
< td >Telephone:</ td >
< td >< form:input path = "telephone" /></ td >
</ tr >
< tr >
< td colspan = "2" align = "center" >< input type = "submit" value = "Save" ></ td >
</ tr >
</ table >
</ form:form >
</ div >
</ body >
</ html >
|
请注意,此 JSP 页使用 Spring 窗体标记将窗体的值绑定到模型对象。要测试应用程序,您可以在方便时下载 Eclipse 项目或部署附加的 WAR 文件。
| [可部署的 WAR 文件] | 5521 千字节 |
| [Eclipse-Maven 项目] | 27 千字节 |