Spring Boot 学习之持久层篇（三）

该系列并非完全原创，官方文档、作者

一、前言

上一篇《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=root

3、建表

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=true

3、建立映射实体类

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.bean

3、增加Mybatis扫描注解

在springboot启动类，增加Mybatis扫描注解

@SpringBootApplication
@MapperScan("com.phil.springboot.mappper")
public class SpringbootApplication

4、创建数据表映射类

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/*.xml

3、增加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），那么可以选用配置版。（我喜欢这种）