一、前言
上一篇《Spring Boot 入门之 Web 篇（二）》介绍了 Spring Boot 的 Web 开发相关的内容，项目的开发离不开数据，因此本篇开始介绍持久层相关的知识。
二、整合 JdbcTemplate
1、添加依赖
在pom.xml文件中添加
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-jdbc</artifactId>
</dependency>
<!-- mysql 驱动包 -->
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
</dependency>2、配置数据库连接
在 application-local.properties 中添加：
(DataSourceProperties.class，DataSourceAutoConfiguration.class源码)
spring.datasource.url=jdbc:mysql://localhost:3306/springboot?useUnicode=true&characterEncoding=utf8
spring.datasource.username=root
spring.datasource.password=root3、建表
DROP TABLE IF EXISTS `user`;
CREATE TABLE `user` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`username` varchar(50) NOT NULL,
`password` varchar(64) NOT NULL,
`createTime` date NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;4、实体序列化
上篇的User implements Serializable，代码就不贴了
5、Service、Dao
package com.phil.springboot.dao;
import com.phil.springboot.bean.User;
public interface UserDao {
public int insert(User user);
public int deleteById(Integer id);
public int update(User user);
public User getById(Integer id);
}package com.phil.springboot.dao.imp;
import java.sql.ResultSet;
import java.sql.SQLException;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Repository;
import com.phil.springboot.bean.User;
import com.phil.springboot.dao.UserDao;
@Repository
public class UserDaoImpl implements UserDao {
@Autowired
private JdbcTemplate jdbcTemplate;
@Override
public int insert(User user) {
String sql = "insert into user(id,username,password,createTime) values(?,?,?,?)";
return jdbcTemplate.update(sql, user.getId(), user.getUsername(), user.getPassword(), user.getCreateTime());
}
@Override
public int deleteById(Integer id) {
String sql = "delete from user where id = ?";
return jdbcTemplate.update(sql, id);
}
@Override
public int update(User user) {
String sql = "update user set password = ? where id = ?";
return jdbcTemplate.update(sql, user.getPassword(), user.getId());
}
@Override
public User getById(Integer id) {
String sql = "select * from user where id = ?";
return jdbcTemplate.queryForObject(sql, new RowMapper<User>() {
@Override
public User mapRow(ResultSet rs, int rowNum) throws SQLException {
User user = new User();
user.setId(rs.getInt("id"));
user.setUsername(rs.getString("username"));
user.setPassword(rs.getString("password"));
user.setCreateTime(rs.getDate("createTime"));
return user;
}
}, id);
}
}package com.phil.springboot.service;
import com.phil.springboot.bean.User;
public interface UserService {
public int insert(User user);
public int deleteById(Integer id);
public int update(User user);
public User getById(Integer id);
}package com.phil.springboot.service.impl;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Repository;
import com.phil.springboot.bean.User;
import com.phil.springboot.dao.UserDao;
import com.phil.springboot.service.UserService;
@Repository
public class UserServiceImpl implements UserService {
@Autowired
private UserDao userDao;
@Override
public int insert(User user) {
return userDao.insert(user);
}
@Override
public int deleteById(Integer id) {
return userDao.deleteById(id);
}
@Override
public int update(User user) {
return userDao.update(user);
}
@Override
public User getById(Integer id) {
return userDao.getById(id);
}
}之前在架构师封装方法上重构了部分，这里就不贴了。
6、单元测试
package com.phil.springboot.user;
import java.util.Date;
import java.util.UUID;
import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.test.context.junit4.SpringRunner;
import com.phil.springboot.bean.User;
import com.phil.springboot.dao.UserDao;
@RunWith(SpringRunner.class)
@SpringBootTest
public class UserTest {
@Autowired
private UserDao userDao;
@Test
public void testInsert() {
User user = new User();
user.setId(1);
user.setUsername(UUID.randomUUID().toString().replace("-", "").substring(0, 4));
user.setPassword(UUID.randomUUID().toString().replace("-", "").substring(0, 8));
user.setCreateTime(new Date());
int result = userDao.insert(user);
System.out.println(result);
}
@Test
public void testGetById() {
User user = userDao.getById(1);
System.out.println(user.getUsername());
}
@Test
public void testUpdate() {
User user = new User();
user.setId(1);
user.setPassword(UUID.randomUUID().toString().replace("-", "").substring(0, 8));
userDao.update(user);
}
@Test
public void testDeleteById() {
int result = userDao.deleteById(1);
System.out.println(result);
}
}如需打印日志，在日志配置文件中添加如下配置：
<logger name="org.springframework.jdbc.core.JdbcTemplate" level="debug"/>三、整合 Spring-data-jpa
1、添加依赖
在pom.xml文件中添加
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-data-jpa</artifactId>
</dependency>2、添加配置
在application-local.properties中添加
# JPA 配置
spring.jpa.hibernate.ddl-auto=update
spring.jpa.show-sql=true3、建立映射实体类
package com.phil.springboot.bean;
import java.io.Serializable;
import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.Id;
import javax.persistence.Table;
@Entity
@Table(name = "PHIL_EMPLOYEE")
public class Employee implements Serializable {
private static final long serialVersionUID = 3926276668667517847L;
@Id
@GeneratedValue
private Integer id;
@Column
private String name;
@Column
private int age;
@Column
private String tile;
public Employee() {
super();
}
public Employee(Integer id) {
super();
this.id = id;
}
public Employee(Integer id, String name, int age, String tile) {
super();
this.id = id;
this.name = name;
this.age = age;
this.tile = tile;
}
public Integer getId() {
return id;
}
public void setId(Integer id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public int getAge() {
return age;
}
public void setAge(int age) {
this.age = age;
}
public String getTile() {
return tile;
}
public void setTile(String tile) {
this.tile = tile;
}
}4、继承JpaRepository接口
package com.phil.springboot.repository;
import org.springframework.data.jpa.repository.JpaRepository;
import com.phil.springboot.bean.Employee;
public interface EmployeeRepository extends JpaRepository<Employee, Integer>{
}5、单元测试
package com.phil.springboot.employee;
import java.util.UUID;
import org.junit.Assert;
import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.test.context.junit4.SpringRunner;
import com.phil.springboot.bean.Employee;
import com.phil.springboot.repository.EmployeeRepository;
@RunWith(SpringRunner.class)
@SpringBootTest
public class EmployeeTest {
@Autowired
private EmployeeRepository employeeRepository;
@Test
public void testInsert() {
Employee employee = new Employee();
employee.setAge(25);
employee.setName(UUID.randomUUID().toString().replace("-", "").substring(0, 4));
employee.setTile("经理");
Assert.assertNotNull(employeeRepository.save(employee));
}
@Test
public void testFindOne() {
Employee employee = employeeRepository.getOne(1);
// Employee param = new Employee();
// param.setName("经理");
// employeeRepository.findOne(Example.of(param));
Assert.assertNotNull(employee);
}
@Test
public void testUpdate() {
Employee employee = new Employee();
employee.setId(1);
employee.setAge(26);
employee.setTile("总经理");
Assert.assertNotNull(employeeRepository.save(employee));
}
@Test
public void testDelete() {
employeeRepository.deleteById(1);
}
}四、整合 Mybatis（注解）
1、添加依赖
在pom.xml文件中添加
<dependency>
<groupId>org.mybatis.spring.boot</groupId>
<artifactId>mybatis-spring-boot-starter</artifactId>
<version>1.3.2</version>
</dependency>2、配置Mybatis数据源
数据源在application-local.properties已经配置过，在application.properties文件添加配置信息
mybatis.type-aliases-package=com.phil.springboot.bean3、增加Mybatis扫描注解
在springboot启动类，增加Mybatis扫描注解
@SpringBootApplication
@MapperScan("com.phil.springboot.mappper")
public class SpringbootApplication4、创建数据表映射类
SQL
DROP TABLE IF EXISTS `question`;
CREATE TABLE `question` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`number` int(11) DEFAULT NULL,
`description` varchar(255) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;package com.phil.springboot.bean;
public class Question {
private Integer id;
private Integer number;
private String description;
public Question() {
super();
}
public Question(Integer id, Integer number, String description) {
super();
this.id = id;
this.number = number;
this.description = description;
}
public Integer getId() {
return id;
}
public void setId(Integer id) {
this.id = id;
}
public Integer getNumber() {
return number;
}
public void setNumber(Integer number) {
this.number = number;
}
public String getDescription() {
return description;
}
public void setDescription(String description) {
this.description = description;
}
}5、创建Mapper数据持久层操作方法
package com.phil.springboot.mappper;
import java.util.List;
import org.apache.ibatis.annotations.Delete;
import org.apache.ibatis.annotations.Insert;
import org.apache.ibatis.annotations.Result;
import org.apache.ibatis.annotations.Results;
import org.apache.ibatis.annotations.Select;
import org.apache.ibatis.annotations.Update;
import com.phil.springboot.bean.Question;
public interface QuestionMapper {
@Insert("INSERT INTO QUESTION(number,description) VALUES(#{number}, #{description})")
void addQuestion(Question question);
@Delete("DELETE FROM QUESTION WHERE id=#{id}")
void deleteQuestion(Integer id);
@Update("UPDATE QUESTION SET description=#{description} WHERE id=#{id}")
void updateQuestion(Question question);
@Select("SELECT * FROM QUESTION")
@Results({ @Result(property = "number", column = "number"), @Result(property = "description", column = "description") })
List<Question> queryQuestions();
@Select("SELECT * FROM QUESTION WHERE number=#{number}")
@Results({ @Result(property = "number", column = "number"), @Result(property = "description", column = "description") })
Question queryQuestionByNumber(Integer number);
}6、单元测试
package com.phil.springboot.question;
import java.util.List;
import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.test.annotation.Rollback;
import org.springframework.test.context.junit4.SpringRunner;
import org.springframework.transaction.annotation.Transactional;
import com.phil.springboot.bean.Question;
import com.phil.springboot.mappper.QuestionMapper;
@RunWith(SpringRunner.class)
@SpringBootTest
public class QuestionTest {
@Autowired
private QuestionMapper questionMapper;
@Test
public void testAddQuestion() {
Question question = new Question();
question.setNumber(1);
question.setDescription("问题一描述");
questionMapper.addQuestion(question);
Question question_ = new Question();
question_.setNumber(2);
question_.setDescription("问题二描述");
questionMapper.addQuestion(question_);
}
@Test
public void testQuestionByNumber() {
Question question = questionMapper.queryQuestionByNumber(1);
System.out.println(question);
}
@Test
public void testQueryAllQuestions() {
List<Question> questions = questionMapper.queryQuestions();
System.out.println(questions.toArray());
}
@Test
public void testUpdateQuestion(){
Question question = questionMapper.queryQuestionByNumber(1);
question.setDescription("问题一不需要描述了");
questionMapper.updateQuestion(question);
}
@Test
public void testDelQuestion() {
questionMapper.deleteQuestion(1);
}
}
五、整合 Mybatis（配置）
和上节比较
1、添加依赖
不需要改动2、配置Mybatis数据源
在application.properties文件再添加配置信息
mybatis.config-location=classpath:mybatis/mybatis-config.xml
mybatis.mapper-locations=classpath:mybatis/mapper/*.xml3、增加Mybatis扫描注解
不需要改动
4、创建数据表映射类
不需要改动
5、创建Mapper数据持久层操作方法
修改如下
package com.phil.springboot.mappper;
import java.util.List;
import java.util.Map;
public interface QuestionMapper {
public List<Map<String, Object>> findByPage(Map<String, Object> params);
public Map<String, Object> findByProperty(Map<String, Object> params);
public Integer save(Map<String, Object> params);
public Integer update(Map<String, Object> params);
public Integer delete(Integer[] ids );
}6、增加Mybatis主配置文件
mybatis-config.xml<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE configuration
PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>
</configuration>questionMapper.xml
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.phil.springboot.mappper.QuestionMapper" >
<sql id="query" >
select
id, number, description
from question
</sql>
<select id="findByPage" parameterType="java.util.Map" resultType="java.util.Map">
<include refid="query" />
<where>
<if test="id != null">
id = #{id}
</if>
<if test="number != null">
and number = #{number}
</if>
<if test="description != null">
and description like '%${description}%'
</if>
</where>
</select>
<select id="findByProperty" parameterType="java.util.Map" resultType="java.util.Map">
<include refid="query" />
<where>
<if test="id != null">
id = #{id}
</if>
<if test="number != null">
and number = #{number}
</if>
<if test="description != null">
and description like '%${description}%'
</if>
</where>
</select>
<insert id="save" parameterType="java.util.Map" useGeneratedKeys="true" keyProperty="id">
insert into
question
(number,description)
values
(#{number}, #{description})
</insert>
<update id="update" parameterType="java.util.Map">
update
question
set
number = #{number},
description = #{description}
where
id = #{id}
</update>
<delete id="delete" parameterType="java.util.ArrayList" >
delete from
question
where
id in
<foreach item="id" collection="array" open="(" separator="," close=")">
#{id}
</foreach>
</delete>
</mapper>7、单元测试
package com.phil.springboot.question;
import java.util.HashMap;
import java.util.Map;
import org.junit.Assert;
import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.test.context.junit4.SpringRunner;
import com.phil.springboot.mappper.QuestionMapper;
@RunWith(SpringRunner.class)
@SpringBootTest
public class QuestionMybtisTest {
@Autowired
private QuestionMapper questionMapper;
@Test
public void testInsert() {
Map<String, Object> params_ = new HashMap<>();
params_.put("number", 3);
params_.put("description", "问题三描述\"");
Assert.assertNotNull(questionMapper.update(params_));
}
@Test
public void testGet() {
Map<String, Object> params_ = new HashMap<>();
params_.put("id", 3);
Assert.assertNotNull(questionMapper.findByProperty(params_));
}
@Test
public void testList() {
Assert.assertNotNull(questionMapper.findByPage(new HashMap<>()));
}
@Test
public void testUpdate(){
Map<String, Object> params_ = new HashMap<>();
params_.put("id", 5);
params_.put("number", 5);
params_.put("description", "新的问题5描述");
Assert.assertNotNull(questionMapper.update(params_));
}
@Test
public void testDelete () {
Integer[] ids = new Integer[] {3,5};
Assert.assertNotNull(questionMapper.delete(ids));
}
}Service没贴，别忘了加事务注解
六、配置C3P0数据源
1、添加依赖
在pom.xml文件中添加
<!-- c3p0 -->
<dependency>
<groupId>com.mchange</groupId>
<artifactId>c3p0</artifactId>
<version>0.9.5.2</version>
</dependency>2、配置类
package com.phil.springboot.config;
import java.beans.PropertyVetoException;
import java.io.IOException;
import javax.sql.DataSource;
import org.mybatis.spring.SqlSessionFactoryBean;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.boot.autoconfigure.jdbc.DataSourceProperties;
import org.springframework.boot.context.properties.EnableConfigurationProperties;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.Primary;
import org.springframework.core.io.support.PathMatchingResourcePatternResolver;
import com.mchange.v2.c3p0.ComboPooledDataSource;
@Configuration
@EnableConfigurationProperties(DataSourceProperties.class)
public class C3P0DataSourceConfig {
@Value("${mybatis.mapper-locations}")
private String mapperXMLConfigPath;
@Value("${mybatis.type-aliases-package}")
private String mapperPackagePath;
@Autowired
private DataSourceProperties dataSourceProperties;
@Bean(name = "dataSource")
@Qualifier(value = "dataSource") // 限定描述符除了能根据名字进行注入，但能进行更细粒度的控制如何选择候选者
@Primary // 用@Primary区分主数据源
public DataSource createDataSource() throws PropertyVetoException {
ComboPooledDataSource dataSource = new ComboPooledDataSource();
// dataSource.setDriverClass(dataSourceProperties.getDriverClassName());
dataSource.setJdbcUrl(dataSourceProperties.getUrl());
dataSource.setUser(dataSourceProperties.getUsername());
dataSource.setPassword(dataSourceProperties.getPassword());
// 关闭连接后不自动提交
dataSource.setAutoCommitOnClose(false);
return dataSource;
// return DataSourceBuilder.create().type(ComboPooledDataSource.class).build();//创建数据源
}
/**
* 返回sqlSessionFactory
* @throws IOException
* @throws PropertyVetoException
*/
@Bean
public SqlSessionFactoryBean sqlSessionFactoryBean() throws IOException, PropertyVetoException {
SqlSessionFactoryBean sqlSessionFactoryBean = new SqlSessionFactoryBean();
PathMatchingResourcePatternResolver resolver = new PathMatchingResourcePatternResolver();
String packageXMLConfigPath = mapperXMLConfigPath; //PathMatchingResourcePatternResolver.CLASSPATH_ALL_URL_PREFIX +
// 设置mapper 对应的XML 文件的路径
sqlSessionFactoryBean.setMapperLocations(resolver.getResources(packageXMLConfigPath));
// 设置数据源
sqlSessionFactoryBean.setDataSource(createDataSource());
// 设置mapper 接口所在的包
sqlSessionFactoryBean.setTypeAliasesPackage(mapperPackagePath);
return sqlSessionFactoryBean;
}
}控制台
2018-04-04 16:06:39.671 |-INFO [MLog-Init-Reporter] com.mchange.v2.log.MLog [212] -| MLog clients using slf4j logging.
2018-04-04 16:06:39.708 |-INFO [restartedMain] com.mchange.v2.c3p0.C3P0Registry [212] -| Initializing c3p0-0.9.5.2 [built 08-December-2015 22:06:04 -0800; debug? true; trace: 10]
2018-04-04 16:06:39.955 |-INFO [restartedMain] com.mchange.v2.c3p0.impl.AbstractPoolBackedDataSource [212] -| Initializing c3p0 pool... com.mchange.v2.c3p0.ComboPooledDataSource [ acquireIncrement -> 3, acquireRetryAttempts -> 30, acquireRetryDelay -> 1000, autoCommitOnClose -> false, automaticTestTable -> null, breakAfterAcquireFailure -> false, checkoutTimeout -> 0, connectionCustomizerClassName -> null, connectionTesterClassName -> com.mchange.v2.c3p0.impl.DefaultConnectionTester, contextClassLoaderSource -> caller, dataSourceName -> 2s4xuz9u13vbbdda00wcf|5f55253e, debugUnreturnedConnectionStackTraces -> false, description -> null, driverClass -> null, extensions -> {}, factoryClassLocation -> null, forceIgnoreUnresolvedTransactions -> false, forceSynchronousCheckins -> false, forceUseNamedDriverClass -> false, identityToken -> 2s4xuz9u13vbbdda00wcf|5f55253e, idleConnectionTestPeriod -> 0, initialPoolSize -> 3, jdbcUrl -> jdbc:mysql://localhost:3306/springboot?useUnicode=true&characterEncoding=utf8, maxAdministrativeTaskTime -> 0, maxConnectionAge -> 0, maxIdleTime -> 0, maxIdleTimeExcessConnections -> 0, maxPoolSize -> 15, maxStatements -> 0, maxStatementsPerConnection -> 0, minPoolSize -> 3, numHelperThreads -> 3, preferredTestQuery -> null, privilegeSpawnedThreads -> false, properties -> {user=******, password=******}, propertyCycle -> 0, statementCacheNumDeferredCloseThreads -> 0, testConnectionOnCheckin -> false, testConnectionOnCheckout -> false, unreturnedConnectionTimeout -> 0, userOverrides -> {}, usesTraditionalReflectiveProxies -> false ]总结：
注解版更符合springboot微服务设计思想，快速、简洁、零配置的开发模式。
如果习惯了在配置文件中编写sql（特别是复杂的sql），那么可以选用配置版。（我喜欢这种）