一、前言

上一篇《Spring Boot 入门之 Web 篇(二)》介绍了 Spring Boot 的 Web 开发相关的内容,项目的开发离不开数据,因此本篇开始介绍持久层相关的知识。

二、整合 JdbcTemplate

# 2.1 添加依赖

  1. <!-- jdbc -->
  2. <dependency>
  3. <groupId>org.springframework.boot</groupId>
  4. <artifactId>spring-boot-starter-jdbc</artifactId>
  5. </dependency>
  6. <!-- mysql 驱动包 -->
  7. <dependency>
  8. <groupId>mysql</groupId>
  9. <artifactId>mysql-connector-java</artifactId>
  10. </dependency>

# 2.2 配置数据库连接

在 application.properties 中添加:

  1. spring.datasource.driver-class-name=com.mysql.jdbc.Driver
  2. spring.datasource.url=jdbc:mysql://localhost:3306/springboot?useUnicode=true&characterEncoding=utf8&serverTimezone=UTC
  3. spring.datasource.username=root
  4. spring.datasource.password=tiger

其中,可以不指定 driver-class-name,因为 spring boot 会自动识别 url。

# 2.3 测试

# 2.3.1 建表

在 MySQL 中创建名为 springboot 的数据库,在该库中创建 user 表:

  1. CREATE TABLE `user` (
  2. `id` INT(11) NOT NULL AUTO_INCREMENT,
  3. `username` VARCHAR(50) NOT NULL,
  4. `password` VARCHAR(64) NOT NULL,
  5. `birthday` DATE NOT NULL,
  6. PRIMARY KEY (`id`)
  7. )
  8. COLLATE='utf8_general_ci'
  9. ENGINE=InnoDB
  10. AUTO_INCREMENT=3
  11. ;

# 2.3.2 建实体类

  1. public class User implements Serializable{
  2. private static final long serialVersionUID = -6249397911566315813L;
  3. private Integer id;
  4. private String username;
  5. private String password;
  6. private Date birthday;
  7. }

setter 和 getter 方法此处省略。

# 2.3.3 dao 接口

接口和实现类如下:

  1. public interface UserDao {
  2. public int insert(User user);
  3. public int deleteById(Integer id);
  4. public int update(User user);
  5. public User getById(Integer id);
  6. }
  7. @Repository
  8. public class UserDaoImpl implements UserDao {
  9. @Autowired
  10. private JdbcTemplate jdbcTemplate;
  11. @Override
  12. public int insert(User user) {
  13. String sql = "insert into user(id,username,password,birthday) values(?,?,?,?)";
  14. return this.jdbcTemplate.update(
  15. sql,
  16. user.getId(),
  17. user.getUsername(),
  18. user.getPassword(),
  19. user.getBirthday()
  20. );
  21. }
  22. @Override
  23. public int deleteById(Integer id) {
  24. String sql = "delete from user where id = ?";
  25. return this.jdbcTemplate.update(sql,id);
  26. }
  27. @Override
  28. public int update(User user) {
  29. String sql = "update user set password = ? where id = ?";
  30. return this.jdbcTemplate.update(
  31. sql,
  32. user.getPassword(),
  33. user.getId()
  34. );
  35. }
  36. @Override
  37. public User getById(Integer id) {
  38. String sql = "select * from user where id = ?";
  39. return this.jdbcTemplate.queryForObject(sql, new RowMapper<User>() {
  40. @Override
  41. public User mapRow(ResultSet rs, int rowNum) throws SQLException {
  42. User user = new User();
  43. user.setId(rs.getInt("id"));
  44. user.setUsername(rs.getString("username"));
  45. user.setPassword(rs.getString("password"));
  46. user.setBirthday(rs.getDate("birthday"));
  47. return user;
  48. }
  49. },id);
  50. }
  51. }

# 2.3.4 测试类:

  1. @RunWith(SpringRunner.class)
  2. @SpringBootTest
  3. public class UserDaoTest {
  4. @Autowired
  5. private UserDao userDao;
  6. @Test
  7. public void testInsert() {
  8. User user = new User();
  9. user.setId(1);
  10. user.setUsername("张三");
  11. user.setPassword("zhangsan");
  12. user.setBirthday(new Date());
  13. int result = this.userDao.insert(user);
  14. System.out.println(result);
  15. }
  16. @Test
  17. public void testGetById() {
  18. User user = this.userDao.getById(1);
  19. System.out.println(user.getUsername());
  20. }
  21. @Test
  22. public void testUpdate() {
  23. User user = new User();
  24. user.setId(1);
  25. user.setPassword("zhangsan123");
  26. this.userDao.update(user);
  27. }
  28. @Test
  29. public void testDeleteById() {
  30. int result = this.userDao.deleteById(1);
  31. System.out.println(result);
  32. }
  33. }

测试结果省略...

如需打印日志,在日志配置文件中添加如下配置

  1. <logger name="org.springframework.jdbc.core.JdbcTemplate" level="debug"/>

三、整合 Spring-data-jpa

# 3.1 添加依赖

  1. <!-- jdbc -->
  2. <dependency>
  3. <groupId>org.springframework.boot</groupId>
  4. <artifactId>spring-boot-starter-jdbc</artifactId>
  5. </dependency>
  6. <!-- springboot,jpa 整合包-->
  7. <dependency>
  8. <groupId>org.springframework.boot</groupId>
  9. <artifactId>spring-boot-starter-data-jpa</artifactId>
  10. </dependency>
  11. <!-- mysql 驱动包 -->
  12. <dependency>
  13. <groupId>mysql</groupId>
  14. <artifactId>mysql-connector-java</artifactId>
  15. </dependency>

# 3.2 配置数据库连接

在 application.properties 中添加:

  1. # 数据库连接配置
  2. spring.datasource.driver-class-name=com.mysql.jdbc.Driver
  3. spring.datasource.url=jdbc:mysql://localhost:3306/springboot?useUnicode=true&characterEncoding=utf8&serverTimezone=UTC
  4. spring.datasource.username=root
  5. spring.datasource.password=tiger
  6. # JPA 配置
  7. spring.jpa.hibernate.ddl-auto=update
  8. spring.jpa.show-sql=true

# 3.3 编码

# 3.3.1 建表

在 MySQL 中创建名为 springboot 的数据库,在该库中创建 role 表:

  1. CREATE TABLE `role` (
  2. `id` INT(11) NOT NULL AUTO_INCREMENT,
  3. `name` VARCHAR(10) NOT NULL,
  4. `descr` VARCHAR(100) NULL DEFAULT NULL,
  5. PRIMARY KEY (`id`)
  6. )
  7. COLLATE='utf8_general_ci'
  8. ENGINE=InnoDB
  9. ;

注意,主键 ID 为 AUTO_INCREMENT 自增。

# 3.3.2 建实体类

添加相应的注解

  1. @Entity
  2. public class Role implements Serializable{
  3. private static final long serialVersionUID = 3926276668667517847L;
  4. @Id
  5. @GeneratedValue
  6. private Integer id;
  7. @Column
  8. private String name;
  9. @Column
  10. private String descr;
  11. }

setter 和 getter 方法此处省略。

# 3.3.3 Repository 接口

  1. public interface RoleRepository extends JpaRepository<Role, Integer>{
  2. }

# 3.3.4 测试类

  1. @RunWith(SpringRunner.class)
  2. @SpringBootTest
  3. public class RoleRepositoryTest {
  4. @Autowired
  5. private RoleRepository roleRepository;
  6. @Test
  7. public void testInsert() {
  8. Role role = new Role();
  9. role.setName("管理员");
  10. role.setDescr("测试");
  11. Role result = this.roleRepository.save(role);
  12. System.out.println(result);
  13. }
  14. @Test
  15. public void testFindOne() {
  16. Role role = this.roleRepository.findOne(1);
  17. System.out.println(role);
  18. }
  19. @Test
  20. public void testUpdate() {
  21. Role role = new Role();
  22. role.setId(1);
  23. role.setName("管理员");
  24. role.setDescr("控制权限");
  25. Role result = this.roleRepository.save(role);
  26. System.out.println(result);
  27. }
  28. @Test
  29. public void testDelete() {
  30. this.roleRepository.delete(1);
  31. }
  32. }

测试结果省略...

四、整合 Mybatis

整合 MyBatis 有两种方式:

  1. 使用 mybatis 官方提供的 Spring Boot 整合包实现。

  2. 使用 mybatis-spring 整合的方式,也就是传统的方式(推荐,此方式容易控制 MyBatis 的配置)。

# 4.1 配置依赖

方式一:使用官方整合包

1)添加依赖:

  1. <!-- springboot,mybatis 整合包 -->
  2. <dependency>
  3. <groupId>org.mybatis.spring.boot</groupId>
  4. <artifactId>mybatis-spring-boot-starter</artifactId>
  5. <version>1.3.0</version>
  6. </dependency>
  7. <!-- mysql 驱动包 -->
  8. <dependency>
  9. <groupId>mysql</groupId>
  10. <artifactId>mysql-connector-java</artifactId>
  11. </dependency>

2)配置数据库连接:

在 application.properties 中添加:

  1. # 数据源配置
  2. spring.datasource.driver-class-name=com.mysql.jdbc.Driver
  3. spring.datasource.url=jdbc:mysql://localhost:3306/springboot?useUnicode=true&characterEncoding=utf8&serverTimezone=UTC
  4. spring.datasource.username=root
  5. spring.datasource.password=tiger
  6. # mybatis 配置
  7. mybatis.config-location=classpath:mybatis/mybatis-config.xml
  8. mybatis.mapper-locations=classpath:mybatis/mapper/*.xml

方式二:原始 Jar 包 + 手动编程

1)添加依赖:

  1. <!-- jdbc -->
  2. <dependency>
  3. <groupId>org.springframework.boot</groupId>
  4. <artifactId>spring-boot-starter-jdbc</artifactId>
  5. </dependency>
  6. <!-- mybatis -->
  7. <dependency>
  8. <groupId>org.mybatis</groupId>
  9. <artifactId>mybatis</artifactId>
  10. <version>3.4.4</version>
  11. </dependency>
  12. <!-- spring,mybatis整合包 -->
  13. <dependency>
  14. <groupId>org.mybatis</groupId>
  15. <artifactId>mybatis-spring</artifactId>
  16. <version>1.3.1</version>
  17. </dependency>
  18. <!-- mysql 驱动包 -->
  19. <dependency>
  20. <groupId>mysql</groupId>
  21. <artifactId>mysql-connector-java</artifactId>
  22. </dependency>

2)配置数据库连接:

在 application.properties 中添加:

  1. # 数据源配置
  2. spring.datasource.driver-class-name=com.mysql.jdbc.Driver
  3. spring.datasource.url=jdbc:mysql://localhost:3306/springboot?useUnicode=true&characterEncoding=utf8&serverTimezone=UTC
  4. spring.datasource.username=root
  5. spring.datasource.password=tiger

3)创建配置类:

  1. @Configuration
  2. public class MyBatisConfiguration {
  3. @Bean
  4. @ConditionalOnMissingBean // 当容器里没有指定的 Bean 的情况下创建该对象
  5. public SqlSessionFactoryBean sqlSessionFactory(DataSource dataSource) {
  6. SqlSessionFactoryBean sqlSessionFactoryBean = new SqlSessionFactoryBean();
  7. // 设置数据源
  8. sqlSessionFactoryBean.setDataSource(dataSource);
  9. // 设置mybatis的主配置文件
  10. sqlSessionFactoryBean.setConfigLocation(new ClassPathResource("mybatis/mybatis-config.xml"));
  11. // 设置mapper映射文件
  12. PathMatchingResourcePatternResolver resolver = new PathMatchingResourcePatternResolver();
  13. Resource[] mapperXml;
  14. try {
  15. mapperXml = resolver.getResources("classpath:mybatis/mapper/*.xml");
  16. sqlSessionFactoryBean.setMapperLocations(mapperXml);
  17. } catch (IOException e) {
  18. e.printStackTrace();
  19. }
  20. // 设置别名包
  21. sqlSessionFactoryBean.setTypeAliasesPackage("com.light.springboot.domain");
  22. return sqlSessionFactoryBean;
  23. }
  24. @Bean
  25. @ConditionalOnBean(SqlSessionFactoryBean.class) // 当 SqlSessionFactoryBean 实例存在时创建对象
  26. public MapperScannerConfigurer mapperScannerConfigurer() {
  27. MapperScannerConfigurer mapperScannerConfigurer = new MapperScannerConfigurer();
  28. mapperScannerConfigurer.setBasePackage("com.light.springboot.mapper");
  29. return mapperScannerConfigurer;
  30. }
  31. }

以上便是两种方式的配置的不同之处,不同之处就在于如何配置 mybatis 配置文件和 mapper 映射文件的路径。

在 src/main/resources 下创建 mybatis 文件夹,并在 mybatis 文件夹中创建 "mybatis-config.xml" 配置文件,内容如下:

  1. <?xml version="1.0" encoding="UTF-8"?>
  2. <!DOCTYPE configuration
  3. PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
  4. "http://mybatis.org/dtd/mybatis-3-config.dtd">
  5. <configuration>
  6. <settings>
  7. <!-- 获取数据库自增主键值 -->
  8. <setting name="useGeneratedKeys" value="true"/>
  9. <!-- 使用列别名替换列名,默认为 true -->
  10. <setting name="useColumnLabel" value="true"/>
  11. <!-- 开启驼峰命名转换:Table(create_time) => Entity(createTime) -->
  12. <setting name="mapUnderscoreToCamelCase" value="true"/>
  13. </settings>
  14. </configuration>

mybatis 文件夹下再创建一个 "mapper" 文件夹,里边存放 Mpper 接口对应的 mapper 映射文件。

# 4.2 测试

# 4.2.1 建表

在 MySQL 中创建名为 springboot 的数据库,在该库中创建 role 表:

  1. CREATE TABLE `department` (
  2. `id` INT(11) NOT NULL,
  3. `name` VARCHAR(10) NOT NULL,
  4. `descr` VARCHAR(50) NULL DEFAULT NULL,
  5. PRIMARY KEY (`id`)
  6. )
  7. ENGINE=InnoDB
  8. ;

# 4.2.2 实体类

  1. public class Department implements Serializable{
  2. private static final long serialVersionUID = 6067283535977178571L;
  3. private Integer id;
  4. private String name;
  5. private String descr;
  6. }

setet 和 getter 方法省略。

# 4.2.3 Mapper 接口

  1. @Mapper
  2. public interface DepartmentMapper {
  3. public void insert(Department department);
  4. public Department getById(Integer id);
  5. public void update(Department department);
  6. public void deleteById(Integer id);
  7. }

补充:Mapper 接口需要添加 @Mapper 注解,如果不想使用该注解,可以在启动类上使用 @MapperScan 配置 Mapper 接口路径

mybatis/mapper/departmentMapper.xml :

  1. <?xml version="1.0" encoding="UTF-8"?>
  2. <!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
  3. "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
  4. <mapper namespace="com.light.springboot.dao.DepartmentMapper">
  5. <insert id="insert" parameterType="com.light.springboot.domain.Department">
  6. insert into department(id,name,descr) values(#{id},#{name},#{descr})
  7. </insert>
  8. <select id="getById" parameterType="java.lang.Integer" resultType="com.light.springboot.domain.Department">
  9. select id,name,descr from department where id = #{id}
  10. </select>
  11. <update id="update" parameterType="com.light.springboot.domain.Department">
  12. update department set descr = #{descr} where id = #{id}
  13. </update>
  14. <delete id="deleteById" parameterType="java.lang.Integer">
  15. delete from department where id = #{id}
  16. </delete>
  17. </mapper>

# 4.2.4 测试类

  1. @RunWith(SpringRunner.class)
  2. @SpringBootTest
  3. public class DepartmentTest {
  4. @Autowired
  5. private DepartmentMapper departmentMapper;
  6. @Test
  7. public void testInsert() {
  8. Department department = new Department();
  9. department.setId(1);
  10. department.setName("研发部");
  11. department.setDescr("开发产品");
  12. this.departmentMapper.insert(department);
  13. }
  14. @Test
  15. public void testGetById() {
  16. Department department = this.departmentMapper.getById(1);
  17. System.out.println(department);
  18. }
  19. @Test
  20. public void testUpdate() {
  21. Department department = new Department();
  22. department.setId(1);
  23. department.setDescr("开发高级产品");
  24. this.departmentMapper.update(department);
  25. }
  26. @Test
  27. public void testDeleteById() {
  28. this.departmentMapper.deleteById(1);
  29. }
  30. }

测试结果省略...

五、配置 Druid 数据源

同样地,有两种方式配置:

1)Spring boot,Druid 整合包

2)原始 jar 包 + 手动编程

# 5.1 Spring boot,Druid 整合包方式

# 5.1.1 添加依赖

  1. <!-- springboot,druid 整合包 -->
  2. <dependency>
  3. <groupId>com.alibaba</groupId>
  4. <artifactId>druid-spring-boot-starter</artifactId>
  5. <version>1.1.8</version>
  6. </dependency>

# 5.1.2 添加配置

在 application.properties 中添加:

  1. spring.datasource.driver-class-name=com.mysql.jdbc.Driver
  2. spring.datasource.url=jdbc:mysql://localhost:3306/springboot?useUnicode=true&characterEncoding=utf8&serverTimezone=UTC
  3. spring.datasource.username=root
  4. spring.datasource.password=tiger
  5. # 修改数据源
  6. spring.datasource.type=com.alibaba.druid.pool.DruidDataSource
  7. spring.datasource.druid.initial-size=5
  8. spring.datasource.druid.min-idle=5
  9. spring.datasource.druid.max-active=20
  10. spring.datasource.druid.max-wait=60000
  11. spring.datasource.druid.time-between-eviction-runs-millis=60000
  12. spring.datasource.druid.min-evictable-idle-time-millis=300000
  13. spring.datasource.druid.validation-query=SELECT 1 FROM DUAL
  14. spring.datasource.druid.test-while-idle=true
  15. spring.datasource.druid.test-on-borrow=false
  16. spring.datasource.druid.test-on-return=false
  17. spring.datasource.druid.pool-prepared-statements=true
  18. spring.datasource.druid.max-pool-prepared-statement-per-connection-size=20
  19. spring.datasource.druid.filters=stat,wall,log4j

# 5.2 原始 jar 包 + 手动编程方式

# 5.2.1 添加依赖

  1. <dependency>
  2. <groupId>com.alibaba</groupId>
  3. <artifactId>druid</artifactId>
  4. <version>1.1.8</version>
  5. </dependency>

# 5.2.2 添加配置

  1. spring.datasource.druid.driverClassName=com.mysql.jdbc.Driver
  2. spring.datasource.druid.url=jdbc:mysql://localhost:3306/springboot?useUnicode=true&characterEncoding=utf8&serverTimezone=UTC
  3. spring.datasource.druid.username=root
  4. spring.datasource.druid.password=tiger
  5. spring.datasource.druid.initialSize=5
  6. spring.datasource.druid.minIdle=5
  7. spring.datasource.druid.maxActive=20
  8. spring.datasource.druid.maxWait=60000
  9. spring.datasource.druid.timeBetweenEvictionRunsMillis=60000
  10. spring.datasource.druid.min-evictableIdleTimeMillis=300000
  11. spring.datasource.druid.validationQuery=SELECT 1 FROM DUAL
  12. spring.datasource.druid.testWhileIdle=true
  13. spring.datasource.druid.testOnBorrow=false
  14. spring.datasource.druid.testOnReturn=false
  15. spring.datasource.druid.poolPreparedStatements=true
  16. spring.datasource.druid.maxPoolPreparedStatementPerConnectionSize=20
  17. spring.datasource.druid.filters=stat,wall

注意:配置中都是以 spring.datasource.druid 开头,使用驼峰命名

# 5.2.3 手动编程

  1. @Configuration
  2. public class DruidConfiguration {
  3. @ConfigurationProperties(prefix = "spring.datasource.druid")
  4. @Bean(initMethod = "init",destroyMethod = "close")
  5. public DruidDataSource dataSource() {
  6. DruidDataSource ds = new DruidDataSource();
  7. ds.setProxyFilters(Arrays.asList(statFilter()));
  8. return ds;
  9. }
  10. @Bean
  11. public Filter statFilter() {
  12. StatFilter filter = new StatFilter();
  13. filter.setSlowSqlMillis(5000);
  14. filter.setLogSlowSql(true);
  15. filter.setMergeSql(true);
  16. return filter;
  17. }
  18. }

通过上文 MyBatis 的测试代码,运行结果如下:

项目已经使用了 Druid 数据源了。

六、配置 Druid 监控

默认情况下,Druid 的监控统计功能和页面是开启的。

我们启动项目,访问 http://localhost:8080/druid/index.html,如下图:

为了保证访问的安全性,我们可以如下配置:

在 application.properties 中添加(方式一):

  1. ## druid 监控
  2. spring.datasource.druid.web-stat-filter.enabled=true
  3. spring.datasource.druid.web-stat-filter.url-pattern=/*
  4. spring.datasource.druid.web-stat-filter.exclusions=*.js,*.gif,*.jpg,*.png,*.css,*.ico,/druid/*
  5. ## druid 监控页面
  6. spring.datasource.druid.stat-view-servlet.enabled=true
  7. spring.datasource.druid.stat-view-servlet.url-pattern=/druid/*
  8. spring.datasource.druid.stat-view-servlet.login-username=druid
  9. spring.datasource.druid.stat-view-servlet.login-password=druid123

重启项目,再次访问 http://localhost:8080/druid/index.html 地址时需要身份验证:

七、源码下载

八、参考资料