1 简介

  • 对于数据访问层，无论是SQL还是NoSQL，SpringBoot默认采用整合SpringData的方式进行统一处理，添加大量自动配置，屏蔽了很多设置 。引入各种xxxTemplate、xxxRepository来简化我们对数据访问层的操作。
  • 对于我们来说只需要进行简单的设置即可。

2 整合基本JDBC和数据源

2.1 导入相关jar包的maven坐标

  1. <dependency>
  2.     <groupId>org.springframework.boot</groupId>
  3.     <artifactId>spring-boot-starter-web</artifactId>
  4. </dependency>
  5. <dependency>
  6.     <groupId>org.springframework.boot</groupId>
  7.     <artifactId>spring-boot-starter-jdbc</artifactId>
  8. </dependency>
  9. <dependency>
  10.     <groupId>mysql</groupId>
  11.     <artifactId>mysql-connector-java</artifactId>
  12. </dependency>
  13. <dependency>
  14.     <groupId>org.springframework.boot</groupId>
  15.     <artifactId>spring-boot-starter-thymeleaf</artifactId>
  16. </dependency>
  17. <dependency>
  18.     <groupId>org.springframework.boot</groupId>
  19.     <artifactId>spring-boot-starter-test</artifactId>
  20.     <scope>test</scope>
  21. </dependency>

2.2 配置application.yml

  1. spring:
  2.   datasource:
  3.     username: root
  4.     password: 123456
  5.     url: jdbc:mysql://192.168.179.100:3306/test?useUnicode=true&characterEncoding=UTF-8&autoReconnect=true&useSSL=false&serverTimezone=GMT%2B8&allowPublicKeyRetrieval=true&nullCatalogMeansCurrent=true
  6.     driver-class-name: com.mysql.jdbc.Driver
  • 默认使用的数据源是org.apache.tomcat.jdbc.pool.DataSource。
  • 数据源的相关配置都在DataSourceProperties里面。
  • 自动配置原理。
  • SpringBoot和数据访问 - 图1参考org.springframework.boot.autoconfigure.jdbc.DataSourceConfiguration，根据配置创建数据源，默认使用Tomcat连接池，可以使用spring.datasource.type指定自定义的数据源类型。
  1. abstract class DataSourceConfiguration {
  2.     /**
  3.      * Tomcat Pool DataSource configuration.
  4.      */
  5.     @ConditionalOnClass(org.apache.tomcat.jdbc.pool.DataSource.class)
  6.     @ConditionalOnProperty(name = "spring.datasource.type", havingValue = "org.apache.tomcat.jdbc.pool.DataSource", matchIfMissing = true)
  7.     static class Tomcat extends DataSourceConfiguration {
  9.         @Bean
  10.         @ConfigurationProperties(prefix = "spring.datasource.tomcat")
  11.         public org.apache.tomcat.jdbc.pool.DataSource dataSource(
  12.                 DataSourceProperties properties) {
  13.             org.apache.tomcat.jdbc.pool.DataSource dataSource = createDataSource(
  14.                     properties, org.apache.tomcat.jdbc.pool.DataSource.class);
  15.             DatabaseDriver databaseDriver = DatabaseDriver
  16.                     .fromJdbcUrl(properties.determineUrl());
  17.             String validationQuery = databaseDriver.getValidationQuery();
  18.             if (validationQuery != null) {
  19.                 dataSource.setTestOnBorrow(true);
  20.                 dataSource.setValidationQuery(validationQuery);
  21.             }
  22.             return dataSource;
  23.         }
  25.     }
  27.         /**
  28.      * Hikari DataSource configuration.
  29.      */
  30.     @ConditionalOnClass(HikariDataSource.class)
  31.     @ConditionalOnProperty(name = "spring.datasource.type", havingValue = "com.zaxxer.hikari.HikariDataSource", matchIfMissing = true)
  32.     static class Hikari extends DataSourceConfiguration {
  34.         @Bean
  35.         @ConfigurationProperties(prefix = "spring.datasource.hikari")
  36.         public HikariDataSource dataSource(DataSourceProperties properties) {
  37.             return createDataSource(properties, HikariDataSource.class);
  38.         }
  40.     }
  42.     /**
  43.      * DBCP DataSource configuration.
  44.      *
  45.      * @deprecated as of 1.5 in favor of DBCP2
  46.      */
  47.     @ConditionalOnClass(org.apache.commons.dbcp.BasicDataSource.class)
  48.     @ConditionalOnProperty(name = "spring.datasource.type", havingValue = "org.apache.commons.dbcp.BasicDataSource", matchIfMissing = true)
  49.     @Deprecated
  50.     static class Dbcp extends DataSourceConfiguration {
  52.         @Bean
  53.         @ConfigurationProperties(prefix = "spring.datasource.dbcp")
  54.         public org.apache.commons.dbcp.BasicDataSource dataSource(
  55.                 DataSourceProperties properties) {
  56.             org.apache.commons.dbcp.BasicDataSource dataSource = createDataSource(
  57.                     properties, org.apache.commons.dbcp.BasicDataSource.class);
  58.             DatabaseDriver databaseDriver = DatabaseDriver
  59.                     .fromJdbcUrl(properties.determineUrl());
  60.             String validationQuery = databaseDriver.getValidationQuery();
  61.             if (validationQuery != null) {
  62.                 dataSource.setTestOnBorrow(true);
  63.                 dataSource.setValidationQuery(validationQuery);
  64.             }
  65.             return dataSource;
  66.         }
  68.     }
  70.     /**
  71.      * DBCP DataSource configuration.
  72.      */
  73.     @ConditionalOnClass(org.apache.commons.dbcp2.BasicDataSource.class)
  74.     @ConditionalOnProperty(name = "spring.datasource.type", havingValue = "org.apache.commons.dbcp2.BasicDataSource", matchIfMissing = true)
  75.     static class Dbcp2 extends DataSourceConfiguration {
  77.         @Bean
  78.         @ConfigurationProperties(prefix = "spring.datasource.dbcp2")
  79.         public org.apache.commons.dbcp2.BasicDataSource dataSource(
  80.                 DataSourceProperties properties) {
  81.             return createDataSource(properties,
  82.                     org.apache.commons.dbcp2.BasicDataSource.class);
  83.         }
  85.     }
  86.     //其他略
  88. }
  • SpringBoot和数据访问 - 图2SpringBoot默认支持org.apache.tomcat.jdbc.pool.DataSource、HikariDataSource、org.apache.commons.dbcp.BasicDataSource和org.apache.commons.dbcp2.BasicDataSource数据源。
  • SpringBoot和数据访问 - 图3自定义数据源类型：
  1. abstract class DataSourceConfiguration {
  2.     /**
  3.      * Generic DataSource configuration.
  4.      */
  5.     @ConditionalOnMissingBean(DataSource.class)
  6.     @ConditionalOnProperty(name = "spring.datasource.type")
  7.     static class Generic {
  9.         @Bean
  10.         public DataSource dataSource(DataSourceProperties properties) {
  11.             return properties.initializeDataSourceBuilder().build();
  12.         }
  14.     }
  15.     //其他略
  16. }
  • SpringBoot和数据访问 - 图4DataSourceInitializer是ApplicationListener，其作用是：
    • ①运行建表语句，sql脚本的规则是schema-*.sql。
    • ②运行插入数据的语句，sql脚本的规则是data-*.sql。
  1. @Configuration
  2. @ConditionalOnClass({ DataSource.class, EmbeddedDatabaseType.class })
  3. @EnableConfigurationProperties(DataSourceProperties.class)
  4. @Import({ Registrar.class, DataSourcePoolMetadataProvidersConfiguration.class })
  5. public class DataSourceAutoConfiguration {
  7.      @Bean
  8.     @ConditionalOnMissingBean
  9.     public DataSourceInitializer dataSourceInitializer(DataSourceProperties properties,
  10.             ApplicationContext applicationContext) {
  11.         return new DataSourceInitializer(properties, applicationContext);
  12.     }   
  13.     //其他略   
  14. }
  • SpringBoot和数据访问 - 图5自动配置了JdbcTemplate和NamedParameterJdbcTemplate，用来操作数据库。
  1. package org.springframework.boot.autoconfigure.jdbc;
  3. import javax.sql.DataSource;
  5. import org.springframework.boot.autoconfigure.AutoConfigureAfter;
  6. import org.springframework.boot.autoconfigure.EnableAutoConfiguration;
  7. import org.springframework.boot.autoconfigure.condition.ConditionalOnClass;
  8. import org.springframework.boot.autoconfigure.condition.ConditionalOnMissingBean;
  9. import org.springframework.boot.autoconfigure.condition.ConditionalOnSingleCandidate;
  10. import org.springframework.context.annotation.Bean;
  11. import org.springframework.context.annotation.Configuration;
  12. import org.springframework.context.annotation.Primary;
  13. import org.springframework.jdbc.core.JdbcOperations;
  14. import org.springframework.jdbc.core.JdbcTemplate;
  15. import org.springframework.jdbc.core.namedparam.NamedParameterJdbcOperations;
  16. import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
  18. @Configuration
  19. @ConditionalOnClass({ DataSource.class, JdbcTemplate.class })
  20. @ConditionalOnSingleCandidate(DataSource.class)
  21. @AutoConfigureAfter(DataSourceAutoConfiguration.class)
  22. public class JdbcTemplateAutoConfiguration {
  24.     private final DataSource dataSource;
  26.     public JdbcTemplateAutoConfiguration(DataSource dataSource) {
  27.         this.dataSource = dataSource;
  28.     }
  30.     @Bean
  31.     @Primary
  32.     @ConditionalOnMissingBean(JdbcOperations.class)
  33.     public JdbcTemplate jdbcTemplate() {
  34.         return new JdbcTemplate(this.dataSource);
  35.     }
  37.     @Bean
  38.     @Primary
  39.     @ConditionalOnMissingBean(NamedParameterJdbcOperations.class)
  40.     public NamedParameterJdbcTemplate namedParameterJdbcTemplate() {
  41.         return new NamedParameterJdbcTemplate(this.dataSource);
  42.     }
  43. }

2.3 整合数据源Druid

2.3.1 引入druid

  1. <dependency>
  2.     <groupId>com.alibaba</groupId>
  3.     <artifactId>druid-spring-boot-starter</artifactId>
  4.     <version>1.1.23</version>
  5. </dependency>
  1. spring:
  2.   datasource:
  3.     username: root
  4.     password: 123456
  5.     url: jdbc:mysql://192.168.64.101:3306/test?useUnicode=true&characterEncoding=UTF-8&autoReconnect=true&useSSL=false&serverTimezone=GMT%2B8&allowPublicKeyRetrieval=true&nullCatalogMeansCurrent=true
  6.     driver-class-name: com.mysql.jdbc.Driver
  7.     type: com.alibaba.druid.pool.DruidDataSource
  8.     # 数据源的其他配置
  9.     druid:
  10.       # 初始化
  11.       initial-size: 5
  12.       # 最小
  13.       min-idle: 5
  14.       # 最大
  15.       max-active: 20
  16.       # 连接等待超时时间
  17.       max-wait: 60000
  18.       # 配置间隔多久才进行一次检测，检测需要关闭的空闲连接，单位是毫秒
  19.       time-between-eviction-runs-millis: 60000
  20.       # 配置一个连接在池中最小生存的时间，单位是毫秒
  21.       min-evictable-idle-time-millis: 300000
  22.       validation-query: SELECT 1 FROM DUAL
  23.       test-on-borrow: false
  24.       test-while-idle: true
  25.       test-on-return: false
  26.       # 打开PSCache，并且指定每个连接上PSCache的大小
  27.       pool-prepared-statements: true
  28.       max-pool-prepared-statement-per-connection-size: 20
  29.       #   配置监控统计拦截的filters，去掉后监控界面sql无法统计，'wall'用于防火墙
  30.       filters: stat,wall,log4j
  31.       #合并多个DruidDataSource的监控数据
  32.       use-global-data-source-stat: true
  33.       # 通过connectProperties属性来打开mergeSql功能；慢SQL记录
  34.       connection-properties: druid.stat.mergeSql=true;druid.stat.slowSqlMillis=5000
  35.       # 配置DruidStatFilter
  36.       web-stat-filter:
  37.         enabled: true
  38.         url-pattern: "/*"
  39.         exclusions: "*.js,*.gif,*.jpg,*.bmp,*.png,*.css,*.ico,/druid/*"
  40.       # 配置DruidStatViewServlet
  41.       stat-view-servlet:
  42.         enabled: true
  43.         url-pattern: "/druid/*"
  44.         # IP白名单(没有配置或者为空，则允许所有访问)
  45. #        allow:
  46.         # IP黑名单 (存在共同时，deny优先于allow)
  47. #        deny:
  48.         #  禁用HTML页面上的“Reset All”功能
  49.         reset-enable: false
  50.         # 登录名
  51.         login-username: admin
  52.         # 登录密码
  53.         login-password: 123456

3 整合Mybatis

3.1 导入相关jar包的Maven坐标

  1. <dependency>
  2.     <groupId>org.springframework.boot</groupId>
  3.     <artifactId>spring-boot-configuration-processor</artifactId>
  4.     <optional>true</optional>
  5. </dependency>
  6. <dependency>
  7.     <groupId>mysql</groupId>
  8.     <artifactId>mysql-connector-java</artifactId>
  9. </dependency>
  10. <dependency>
  11.     <groupId>org.springframework.boot</groupId>
  12.     <artifactId>spring-boot-starter-data-jpa</artifactId>
  13. </dependency>
  14. <dependency>
  15.     <groupId>org.springframework.boot</groupId>
  16.     <artifactId>spring-boot-starter-jdbc</artifactId>
  17. </dependency>
  18. <dependency>
  19.     <groupId>org.springframework.boot</groupId>
  20.     <artifactId>spring-boot-starter-test</artifactId>
  21.     <scope>test</scope>
  22. </dependency>
  23. <dependency>
  24.     <groupId>org.springframework.boot</groupId>
  25.     <artifactId>spring-boot-starter-web</artifactId>
  26. </dependency>
  27. <dependency>
  28.     <groupId>com.alibaba</groupId>
  29.     <artifactId>druid-spring-boot-starter</artifactId>
  30.     <version>1.1.23</version>
  31. </dependency>
  32. <dependency>
  33.     <groupId>org.springframework.boot</groupId>
  34.     <artifactId>spring-boot-starter-thymeleaf</artifactId>
  35. </dependency>
  36. <dependency>
  37.     <groupId>org.mybatis.spring.boot</groupId>
  38.     <artifactId>mybatis-spring-boot-starter</artifactId>
  39.     <version>2.1.3</version>
  40. </dependency>

3.2 sql脚本

  1. DROP TABLE IF EXISTS `employee`;
  2. CREATE TABLE `employee`  (
  3.   `id` int(11) NOT NULL AUTO_INCREMENT,
  4.   `name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  5.   `gender` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  6.   PRIMARY KEY (`id`) USING BTREE
  7. ) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;

3.3 创建JavaBean

  1. package com.sunxiaping.springboot.domain;
  3. public class Employee {
  5.     private Integer id;
  7.     private String name;
  9.     private String gender;
  11.     public Integer getId() {
  12.         return id;
  13.     }
  15.     public void setId(Integer id) {
  16.         this.id = id;
  17.     }
  19.     public String getName() {
  20.         return name;
  21.     }
  23.     public void setName(String name) {
  24.         this.name = name;
  25.     }
  27.     public String getGender() {
  28.         return gender;
  29.     }
  31.     public void setGender(String gender) {
  32.         this.gender = gender;
  33.     }
  35.     @Override
  36.     public String toString() {
  37.         return "Employee{" +
  38.                 "id=" + id +
  39.                 ", name='" + name + '\'' +
  40.                 ", gender='" + gender + '\'' +
  41.                 '}';
  42.     }
  43. }

3.4 注解版

  • 创建Mapper接口：
  1. package com.sunxiaping.springboot.mapper;
  3. import com.sunxiaping.springboot.domain.Employee;
  4. import org.apache.ibatis.annotations.Insert;
  5. import org.apache.ibatis.annotations.Mapper;
  6. import org.apache.ibatis.annotations.Options;
  8. //指定这是一个操作数据库的Mapper
  9. @Mapper
  10. public interface EmployeeMapper {
  12.     @Options(useGeneratedKeys = true,keyProperty = "id")
  13.     @Insert("INSERT into `employee` (`name`,`gender`) values (#{name},#{gender})")
  14.     void save(Employee employee);
  16. }
  • 在SpringBoot的启动类上加上@MapperScan注解，指定Mapper接口所在的包名
  1. package com.sunxiaping.springboot;
  3. import org.mybatis.spring.annotation.MapperScan;
  4. import org.springframework.boot.SpringApplication;
  5. import org.springframework.boot.autoconfigure.SpringBootApplication;
  6. import org.springframework.boot.web.servlet.ServletComponentScan;
  8. /**
  9.  * 主程序，用来启动SpringBoot应用
  10.  */
  11. @SpringBootApplication
  12. @ServletComponentScan
  13. //指定扫描的Mapper接口的包名
  14. @MapperScan(basePackages = "com.sunxiaping.springboot.mapper")
  15. public class WebApplication {
  17.     public static void main(String[] args) {
  18.         SpringApplication.run(WebApplication.class, args);
  19.     }
  21. }
  • 自定义Mybatis规则：
  1. package com.sunxiaping.springboot.config;
  4. import org.mybatis.spring.boot.autoconfigure.ConfigurationCustomizer;
  5. import org.springframework.context.annotation.Bean;
  6. import org.springframework.context.annotation.Configuration;
  8. @Configuration
  9. public class MybatisConfig {
  11.     @Bean
  12.     public ConfigurationCustomizer configurationCustomizer() {
  13.         return (configuration)->{
  14.             //开启驼峰命名
  15.             configuration.setMapUnderscoreToCamelCase(true);
  16.         };
  17.     }
  18. }
  • 测试：
  1. package com.sunxiaping.springboot;
  3. import com.sunxiaping.springboot.domain.Employee;
  4. import com.sunxiaping.springboot.mapper.EmployeeMapper;
  5. import org.junit.Test;
  6. import org.junit.runner.RunWith;
  7. import org.springframework.beans.factory.annotation.Autowired;
  8. import org.springframework.boot.test.context.SpringBootTest;
  9. import org.springframework.test.context.junit4.SpringRunner;
  11. @RunWith(SpringRunner.class)
  12. @SpringBootTest
  13. public class SpringbootApplicationTests {
  15.     @Autowired
  16.     private EmployeeMapper employeeMapper;
  18.     @Test
  19.     public void test() {
  20.         Employee employee = new Employee();
  21.         employee.setName("张三");
  22.         employee.setGender("男");
  24.         employeeMapper.save(employee);
  26.         System.out.println("employee = " + employee);
  27.     }
  28. }

3.5 配置版

  • 编写EmployeeMapper接口：
  1. package com.sunxiaping.springboot.mapper;
  3. import com.sunxiaping.springboot.domain.Employee;
  4. import org.apache.ibatis.annotations.Mapper;
  6. //指定这是一个操作数据库的Mapper
  7. @Mapper
  8. public interface EmployeeMapper {
  10.     void save( Employee employee);
  12. }
  • 编写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>
  7.     <settings>
  8.         <setting name="mapUnderscoreToCamelCase" value="true"/>
  9.     </settings>
  10. </configuration>
  • 编写EmployeeMapper.xml
  1. <?xml version="1.0" encoding="UTF-8" ?>
  2. <!DOCTYPE mapper
  3.         PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
  4.         "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
  5. <mapper namespace="com.sunxiaping.springboot.mapper.EmployeeMapper">
  7.     <insert id="save" parameterType="com.sunxiaping.springboot.domain.Employee" useGeneratedKeys="true" keyProperty="id">
  8.         INSERT INTO `employee`(`name`,`gender`)
  9.         VALUES (#{name},#{gender})
  10.     </insert>
  11. </mapper>
  • application.yml
  1. spring:
  2.   # 数据源
  3.   datasource:
  4.     username: root
  5.     password: 123456
  6.     url: jdbc:mysql://192.168.64.101:3306/test?useUnicode=true&characterEncoding=UTF-8&autoReconnect=true&useSSL=false&serverTimezone=GMT%2B8&allowPublicKeyRetrieval=true&nullCatalogMeansCurrent=true
  7.     driver-class-name: com.mysql.jdbc.Driver
  8.     type: com.alibaba.druid.pool.DruidDataSource
  9.     # 数据源的其他配置
  10.     druid:
  11.       # 初始化
  12.       initial-size: 5
  13.       # 最小
  14.       min-idle: 5
  15.       # 最大
  16.       max-active: 20
  17.       # 连接等待超时时间
  18.       max-wait: 60000
  19.       # 配置间隔多久才进行一次检测，检测需要关闭的空闲连接，单位是毫秒
  20.       time-between-eviction-runs-millis: 60000
  21.       # 配置一个连接在池中最小生存的时间，单位是毫秒
  22.       min-evictable-idle-time-millis: 300000
  23.       validation-query: SELECT 1 FROM DUAL
  24.       test-on-borrow: false
  25.       test-while-idle: true
  26.       test-on-return: false
  27.       # 打开PSCache，并且指定每个连接上PSCache的大小
  28.       pool-prepared-statements: true
  29.       max-pool-prepared-statement-per-connection-size: 20
  30.       #   配置监控统计拦截的filters，去掉后监控界面sql无法统计，'wall'用于防火墙
  31.       filters: stat,wall,log4j
  32.       #合并多个DruidDataSource的监控数据
  33.       use-global-data-source-stat: true
  34.       # 通过connectProperties属性来打开mergeSql功能；慢SQL记录
  35.       connection-properties: druid.stat.mergeSql=true;druid.stat.slowSqlMillis=5000
  36.       # 配置DruidStatFilter
  37.       web-stat-filter:
  38.         enabled: true
  39.         url-pattern: "/*"
  40.         exclusions: "*.js,*.gif,*.jpg,*.bmp,*.png,*.css,*.ico,/druid/*"
  41.       # 配置DruidStatViewServlet
  42.       stat-view-servlet:
  43.         enabled: true
  44.         url-pattern: "/druid/*"
  45.         # IP白名单(没有配置或者为空，则允许所有访问)
  46.         #        allow:
  47.         # IP黑名单 (存在共同时，deny优先于allow)
  48.         #        deny:
  49.         #  禁用HTML页面上的“Reset All”功能
  50.         reset-enable: false
  51.         # 登录名
  52.         login-username: admin
  53.         # 登录密码
  54.         login-password: 123456
  55. mybatis:
  56.   mapper-locations: classpath:com/sunxiaping/springboot/mapper/*.xml
  57.   config-location: classpath:mybatis-config.xml
  • 测试：
  1. package com.sunxiaping.springboot;
  3. import com.sunxiaping.springboot.domain.Employee;
  4. import com.sunxiaping.springboot.mapper.EmployeeMapper;
  5. import org.junit.Test;
  6. import org.junit.runner.RunWith;
  7. import org.springframework.beans.factory.annotation.Autowired;
  8. import org.springframework.boot.test.context.SpringBootTest;
  9. import org.springframework.test.context.junit4.SpringRunner;
  11. @RunWith(SpringRunner.class)
  12. @SpringBootTest
  13. public class SpringbootApplicationTests {
  15.     @Autowired
  16.     private EmployeeMapper employeeMapper;
  18.     @Test
  19.     public void test() {
  20.         Employee employee = new Employee();
  21.         employee.setName("张三");
  22.         employee.setGender("男");
  24.         employeeMapper.save(employee);
  26.         System.out.println("employee = " + employee);
  27.     }
  28. }

4 整合JPA

4.1 导入相关jar包的Maven坐标

  1. <dependency>
  2.     <groupId>org.springframework.boot</groupId>
  3.     <artifactId>spring-boot-configuration-processor</artifactId>
  4.     <optional>true</optional>
  5. </dependency>
  6. <dependency>
  7.     <groupId>mysql</groupId>
  8.     <artifactId>mysql-connector-java</artifactId>
  9. </dependency>
  10. <dependency>
  11.     <groupId>org.springframework.boot</groupId>
  12.     <artifactId>spring-boot-starter-data-jpa</artifactId>
  13. </dependency>
  14. <dependency>
  15.     <groupId>org.springframework.boot</groupId>
  16.     <artifactId>spring-boot-starter-jdbc</artifactId>
  17. </dependency>
  18. <dependency>
  19.     <groupId>org.springframework.boot</groupId>
  20.     <artifactId>spring-boot-starter-test</artifactId>
  21.     <scope>test</scope>
  22. </dependency>
  23. <dependency>
  24.     <groupId>org.springframework.boot</groupId>
  25.     <artifactId>spring-boot-starter-web</artifactId>
  26. </dependency>
  27. <dependency>
  28.     <groupId>com.alibaba</groupId>
  29.     <artifactId>druid-spring-boot-starter</artifactId>
  30.     <version>1.1.23</version>
  31. </dependency>
  32. <dependency>
  33.     <groupId>org.springframework.boot</groupId>
  34.     <artifactId>spring-boot-starter-data-jpa</artifactId>
  35. </dependency>

4.2 编写实体类

  1. package com.sunxiaping.springboot.domain;
  3. import javax.persistence.*;
  5. @Entity
  6. @Table(name = "`employee`")
  7. public class Employee {
  9.     @Id
  10.     @GeneratedValue(strategy = GenerationType.IDENTITY)
  11.     private Integer id;
  13.     @Column(name = "`name`")
  14.     private String name;
  16.     @Column(name = "`gender`")
  17.     private String gender;
  19.     public Integer getId() {
  20.         return id;
  21.     }
  23.     public void setId(Integer id) {
  24.         this.id = id;
  25.     }
  27.     public String getName() {
  28.         return name;
  29.     }
  31.     public void setName(String name) {
  32.         this.name = name;
  33.     }
  35.     public String getGender() {
  36.         return gender;
  37.     }
  39.     public void setGender(String gender) {
  40.         this.gender = gender;
  41.     }
  43.     @Override
  44.     public String toString() {
  45.         return "Employee{" +
  46.                 "id=" + id +
  47.                 ", name='" + name + '\'' +
  48.                 ", gender='" + gender + '\'' +
  49.                 '}';
  50.     }
  51. }

4.3 编写application.yml

  1. spring:
  2.   # 数据源
  3.   datasource:
  4.     username: root
  5.     password: 123456
  6.     url: jdbc:mysql://192.168.64.101:3306/test?useUnicode=true&characterEncoding=UTF-8&autoReconnect=true&useSSL=false&serverTimezone=GMT%2B8&allowPublicKeyRetrieval=true&nullCatalogMeansCurrent=true
  7.     driver-class-name: com.mysql.jdbc.Driver
  8.     type: com.alibaba.druid.pool.DruidDataSource
  9.     # 数据源的其他配置
  10.     druid:
  11.       # 初始化
  12.       initial-size: 5
  13.       # 最小
  14.       min-idle: 5
  15.       # 最大
  16.       max-active: 20
  17.       # 连接等待超时时间
  18.       max-wait: 60000
  19.       # 配置间隔多久才进行一次检测，检测需要关闭的空闲连接，单位是毫秒
  20.       time-between-eviction-runs-millis: 60000
  21.       # 配置一个连接在池中最小生存的时间，单位是毫秒
  22.       min-evictable-idle-time-millis: 300000
  23.       validation-query: SELECT 1 FROM DUAL
  24.       test-on-borrow: false
  25.       test-while-idle: true
  26.       test-on-return: false
  27.       # 打开PSCache，并且指定每个连接上PSCache的大小
  28.       pool-prepared-statements: true
  29.       max-pool-prepared-statement-per-connection-size: 20
  30.       #   配置监控统计拦截的filters，去掉后监控界面sql无法统计，'wall'用于防火墙
  31.       filters: stat,wall,log4j
  32.       #合并多个DruidDataSource的监控数据
  33.       use-global-data-source-stat: true
  34.       # 通过connectProperties属性来打开mergeSql功能；慢SQL记录
  35.       connection-properties: druid.stat.mergeSql=true;druid.stat.slowSqlMillis=5000
  36.       # 配置DruidStatFilter
  37.       web-stat-filter:
  38.         enabled: true
  39.         url-pattern: "/*"
  40.         exclusions: "*.js,*.gif,*.jpg,*.bmp,*.png,*.css,*.ico,/druid/*"
  41.       # 配置DruidStatViewServlet
  42.       stat-view-servlet:
  43.         enabled: true
  44.         url-pattern: "/druid/*"
  45.         # IP白名单(没有配置或者为空，则允许所有访问)
  46.         #        allow:
  47.         # IP黑名单 (存在共同时，deny优先于allow)
  48.         #        deny:
  49.         #  禁用HTML页面上的“Reset All”功能
  50.         reset-enable: false
  51.         # 登录名
  52.         login-username: admin
  53.         # 登录密码
  54.         login-password: 123456
  55.   jpa:
  56.     show-sql: true
  57.     open-in-view: true
  58.     hibernate:
  59.       ddl-auto: update

4.3 编写Repository

package com.sunxiaping.springboot.repository;

import com.sunxiaping.springboot.domain.Employee;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;

public interface EmployeeRepository extends JpaRepository<Employee,Integer>, JpaSpecificationExecutor<Employee> {

}

4.4 测试

package com.sunxiaping.springboot;

import com.sunxiaping.springboot.domain.Employee;
import com.sunxiaping.springboot.repository.EmployeeRepository;
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 java.util.List;

@RunWith(SpringRunner.class)
@SpringBootTest
public class SpringbootApplicationTests {

    @Autowired
    private EmployeeRepository employeeRepository;


    @Test
    public void test() {
        System.out.println("employeeRepository = " + employeeRepository);

        List<Employee> employeeList = employeeRepository.findAll();

        System.out.println("employeeList = " + employeeList);

    }
}