Spring Boot系列第六篇：Spring Boot与数据访问

一、简介

对于数据访问层，无论是SQL还是NOSQL，Spring Boot默认采用整合Spring Data的方式进行统一处理，添加大量自动配置，屏蔽了很多设置。引入各种xxxTemplate，xxxRepository来简化我们对数据访问层的操作。对我们来说只需要进行简单的设置即可。我们将在数据访问章节测试使用SQL相关、NOSQL在缓存、消息、检索等章节测试。

JDBC Mybatis JPA

二、整合基本JDBC与数据源

1、整合JDBC

引入Jdbc和数据库驱动

        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-jdbc</artifactId>
        </dependency>
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <scope>runtime</scope>
        </dependency>

application.yml文件

#连接数据库
spring:
  datasource:
    username: root
    password: 123456
    url: jdbc:mysql://192.168.1.4:3306/jdbc
    driver-class-name: com.mysql.jdbc.Driver
    schema:
      - classpath:department.sql

测试：

    @Test
    public void jdbcTest() throws SQLException {
        //class org.apache.tomcat.jdbc.pool.DataSource
        System.out.println("===========================dataSource:"+dataSource.getClass());
        Connection connection = dataSource.getConnection();
        System.out.println("connection==:"+connection);
        connection.close();
    }

效果：默认使用org.apache.tomcat.jdbc.pool.DataSource作为数据源

数据源的相关配置在DataSourceProperties里面

数据源的自动配置原理：org.springframework.boot.autoconfigure.jdbc

1、参考DataSourceConfiguration，根据配置创建数据源，默认使用Tomcat为连接池的，可以使用spring.datasource.type指定自定义数据类型

2、springboot默认可以支持

org.apache.tomcat.jdbc.pool.DataSource
HikariDataSource
org.apache.commons.dbcp.BasicDataSource
dbcp2

3、自定义数据源类型

	/**
	 * Generic DataSource configuration.
	 */
	@ConditionalOnMissingBean(DataSource.class)
	@ConditionalOnProperty(name = "spring.datasource.type")
	static class Generic {

		@Bean
		public DataSource dataSource(DataSourceProperties properties) {
		//使用DataSourceBuilder来创建数据源的、利用反射创建相应Type的数据源并且绑定相关属性
			return properties.initializeDataSourceBuilder().build();
		}

	}

4、DataSourceInitializer：ApplicationListener
作用：
1、runSchemaScripts()运行建表语句
2、runDataScripts() 运行插入数据的sql语句
默认只需要将文件命名为：

schema- *.sql和data-*.sql
默认规则：schema.sql、schema-all.sql

指定位置：
    schema:
      - classpath:department.sql

效果会在数据库建表

5、操作数据库：自动配置了JdbcTemplate操作数据库

    @ResponseBody
    @GetMapping("/query")
    public Map<String,Object> findEmp(){
        List<Map<String, Object>> list = jdbcTemplate.queryForList("select * from department ");
        return list.get(0);
    }

2、整合Durid数据源

        <!-- https://mvnrepository.com/artifact/com.alibaba/druid -->
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid</artifactId>
            <version>1.1.8</version>
        </dependency>

#连接数据库
spring:
  datasource:
    username: root
    password: 123456
    url: jdbc:mysql://192.168.1.4:3306/jdbc?useSSL=false
    driver-class-name: com.mysql.jdbc.Driver
    #数据源用druid
    type: com.alibaba.druid.pool.DruidDataSource
    #数据源其他配置
    initialSize: 5
    minIdle: 5
    maxActive: 20
    maxWait: 60000
    timeBetweenEvictionRunsMillis: 60000
    minEvictableIdleTimeMillis: 300000
    validationQuery: SELECT 1 FROM DUAL
    testWhileIdle: true
    testOnBorrow: false
    testOnReturn: false
    poolPreparedStatements: true
    #配置监控统计拦截的filters，去掉后监控界面sql无法统计，'wall'用于防火墙
    filters: stat,wall,log4j
    maxPoolPreparedStatementPerConnectionSize: 20
    useGlobalDataSourceStat: true
    connectionProperties: druid.stat.mergeSql=true;druid.stat.slowSqlMillis=500

package com.ming.config;

import com.alibaba.druid.pool.DruidDataSource;
import com.alibaba.druid.support.http.StatViewServlet;
import com.alibaba.druid.support.http.WebStatFilter;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.boot.web.servlet.FilterRegistrationBean;
import org.springframework.boot.web.servlet.ServletRegistrationBean;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;

import javax.sql.DataSource;
import java.util.Arrays;
import java.util.HashMap;
import java.util.Map;

/**
 * 数据源Druid
 * ConfigurationProperties注解绑定yml的initialSize等其他属性
 */
@Configuration
public class DruidConfig {

    @ConfigurationProperties(prefix = "spring.datasource")
    @Bean
    public DataSource druid(){
        return new DruidDataSource();
    }

    //Druid的监控
    //1、配置一个管理后台的servlet
    @Bean
    public ServletRegistrationBean statViewServlet(){
        ServletRegistrationBean bean = new ServletRegistrationBean(new StatViewServlet(), "/druid/*");
        Map<String,String> initParams = new HashMap<>();
        //设置初始化参数在ResourceServlet
        //    public static final String SESSION_USER_KEY    = "druid-user";
        //    public static final String PARAM_NAME_USERNAME = "loginUsername";
        //    public static final String PARAM_NAME_PASSWORD = "loginPassword";
        //    public static final String PARAM_NAME_ALLOW    = "allow";
        //    public static final String PARAM_NAME_DENY     = "deny";
        //    public static final String PARAM_REMOTE_ADDR   = "remoteAddress";
        initParams.put("loginUsername","admin");
        initParams.put("loginPassword","123456");
        initParams.put("allow","");//IP白名单,默认就是允许所有访问
        initParams.put("deny","192.168.1.4");//拦截本地访问

        bean.setInitParameters(initParams);
        return  bean;
    }

    //2.配置一个web监控的filter、WebStatFilter用于配置Web和Druid数据源之间的管理关联监控统计
    @Bean
    public FilterRegistrationBean webStatFilter(){
        FilterRegistrationBean bean = new FilterRegistrationBean();
        bean.setFilter(new WebStatFilter());

        Map<String,String> initParams = new HashMap<>();
//        初始化参数
//        public final static String PARAM_NAME_PROFILE_ENABLE         = "profileEnable";
//        public final static String PARAM_NAME_SESSION_STAT_ENABLE    = "sessionStatEnable";
//        public final static String PARAM_NAME_SESSION_STAT_MAX_COUNT = "sessionStatMaxCount";
//        public static final String PARAM_NAME_EXCLUSIONS             = "exclusions";
//        public static final String PARAM_NAME_PRINCIPAL_SESSION_NAME = "principalSessionName";
//        public static final String PARAM_NAME_PRINCIPAL_COOKIE_NAME  = "principalCookieName";
//        public static final String PARAM_NAME_REAL_IP_HEADER         = "realIpHeader";
        initParams.put("exclusions","*.js,*.css,/druid/*");//不拦截这些请求

        bean.setInitParameters(initParams);

        bean.setUrlPatterns(Arrays.asList("/*"));//拦截所有的请求
        return  bean;
    }

}

访问：http://localhost:8085/crud/druid/

3、整合MyBatis

1.引入mybatis starter场景启动器

<dependency>        
	<groupId>org.mybatis.spring.boot</groupId>            
	<artifactId>mybatis‐spring‐boot‐starter</artifactId>            
	<version>1.3.1</version>            
</dependency> 

mybatis‐spring‐boot‐starter引入mybatis、mybatis-spring整合包、自动配置包

步骤：
1、配置数据源相关属性（DruidConfig）

2、给数据库建表

# 项目启动时自动创建表
    schema:
      - classpath:/sql/department.sql
      - classpath:/sql/employee.sql

sql位置

3、创建JavaBean

4、注解版MyBatis

只需要写一个Mapper 用@Mapper注解就行

package com.ming.mapper;

import com.ming.bean.Department;
import org.apache.ibatis.annotations.*;

//指定这是一个操作数据库的mapper
@Mapper
public interface DepartmentMapper {

    @Select("select * from department where id = #{id}")
    public Department getDeptByID(Integer id);

    @Delete("delete from department where id = #{id}")
    public int deleteById(Integer id);

    @Insert("insert into department(departmentName) values(#{departmentName})")
    public int insertDept(Department department);

    @Update("update department set departmentName=#{departmentName} where id=#{id}")
    public int updateDept(Department department);
}

测试

package com.ming.controller;

import com.ming.bean.Department;
import com.ming.mapper.DepartmentMapper;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.PathVariable;
import org.springframework.web.bind.annotation.RestController;

@RestController
public class DeptController {

    @Autowired
    DepartmentMapper departmentMapper;

    @GetMapping("/dept/{id}")
    public Department getDept(@PathVariable("id") Integer id){
        return departmentMapper.getDeptByID(id);
    }

    @GetMapping("/dept")
    public Department insertDept(Department department){
        departmentMapper.insertDept(department);
        return department;
    }
}

访问：http://localhost:8085/crud/dept?departmentName=AA

问题？
数据库里面是驼峰命名：department_name JavaBean里面是：departmentName 这样是封装不了的

解决方法：在配置文件中开启驼峰命名、注解版添加ConfigurationCustomizer

自定义mybatis规则，只需要给容器中添加一个ConfigurationCustomizer组件

package com.ming.config;

import org.apache.ibatis.session.Configuration;
import org.mybatis.spring.boot.autoconfigure.ConfigurationCustomizer;
import org.springframework.context.annotation.Bean;

/**
 * 配置mybatis定制器
 */
@org.springframework.context.annotation.Configuration
public class MybatisConfig {

    //配置mybatis的定制器
    @Bean
    public ConfigurationCustomizer configurationCustomizer(){
       return new ConfigurationCustomizer(){
            @Override
            public void customize(Configuration configuration) {
                //开启驼峰命名
                configuration.setMapUnderscoreToCamelCase(true);
            }
        };
    }
}

使用MapperScan注解批量扫描所有的mapper接口

我们在Mapper接口上用@Mapper（指定这是一个操作数据库的mapper）如果mapper接口太多每一个接口都要加上这个@Mapper注解比较麻烦、我们只需要在主程序类上或者mybatisconfig配置类加上MapperScan注解

package com.ming;

import org.mybatis.spring.annotation.MapperScan;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
import org.springframework.boot.web.servlet.ServletComponentScan;

/**
 * @SpringBootApplication 来标注一个主程序类，说明是一个Springboot应用
 */
//@ImportResource(locations = "{classpath:bean.xml}")
@SpringBootApplication
//使用MapperScan扫描所有的mapper接口
@MapperScan(value = "com.ming.mapper")
public class MySpringbootApplication {

    public static void main(String[] args) {
        SpringApplication.run(MySpringbootApplication.class, args);
    }

}

5、配置文件版Mybatis

application.yml

mybatis:
  config‐location: classpath:mybatis/mybatis‐config.xml 指定全局配置文件的位置
  mapper‐locations: classpath:mybatis/mapper/*.xml  指定sql映射文件的位置

更多使用参照配置文件版使用

6、整合SpringData JPA

1、SpringData简介

2、整合SpringData JPA

        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-data-jpa</artifactId>
        </dependency>

JPA:ORM（Object Relational Mapping）

1、编写一个实体类（bean）和数据库表进行映射，并且配置好映射关系

package com.ming.bean;

import javax.persistence.*;

/**
 * 使用jpa注解配置映射关系
 */
@Entity //标注这是一个实体类、和数据库表映射类
@Table(name = "tbl_user")//指定和数据库那个表对应，;如果省略默认表名就是user
public class User {


    @Id//主键
    @GeneratedValue(strategy = GenerationType.IDENTITY)//自增主键
    private Integer id;

    @Column(name = "last_name",length = 50)//这是和数据表对应的列
    private String lastName;

    @Column//省略默认列名就是属性名
    private String email;

    public Integer getId() {
        return id;
    }

    public void setId(Integer id) {
        this.id = id;
    }

    public String getLastName() {
        return lastName;
    }

    public void setLastName(String lastName) {
        this.lastName = lastName;
    }

    public String getEmail() {
        return email;
    }

    public void setEmail(String email) {
        this.email = email;
    }
}

2、编写一个Dao接口来操作实体类对应的数据表（Repository）

package com.ming.dao;

import com.ming.bean.User;
import org.springframework.data.jpa.repository.JpaRepository;
//继承JpaRepository来完成对数据库的操作
public interface UserRepository extends JpaRepository<User,Integer> {

}

3、基本的配置JpaProperties

spring:
  jpa:
    hibernate:
      #更新或者创建数据库表结构
      ddl-auto: update
      #控制台显示sql
      show-sql: true