SpringBoot学习笔记（五）SpringBoot与数据访问[JDBC、Druid、Jpa]

六、SpringBoot与数据访问

6.1JDBC

<dependency>
	<groupId>org.springframework.boot</groupId>
	<artifactId>spring-boot-starter-jdbc</artifactId>
</dependency>
<dependency>
	<groupId>org.springframework.boot</groupId>
	<artifactId>spring-boot-starter-test</artifactId>
	<scope>test</scope>
</dependency>

spring:
  datasource:
    url: jdbc:mysql://124.70.0.204:3306/jdbc?serverTimezone=Asia/Shanghai&characterEncoding=utf8&useSSL=false
    password: *******
    username: root
    driver-class-name: com.mysql.cj.jdbc.Driver

效果：
默认是用org.apache.tomcat.jdbc.pool.DataSource作为数据源
数据源的相关配置都在DataSourceProperties里面

6.1.1自动配置原理：

org.springframework.boot.autoconfigure.jdbc
1、参考DataSourceConfiguration,根据配置数据源，默认使用Tomcat连接池；可以使用spring.datasource.type指定自定义的数据源类型；
2、SpringBoot默认可以支持

org.apache.tomcat.jdbc.pool.DataSource、HikariDataSource、BasicDataSource

3、自定义数据源类型

	@Configuration(proxyBeanMethods = false)
    @ConditionalOnMissingBean({DataSource.class})
    @ConditionalOnProperty(name = {"spring.datasource.type"})
    static class Generic {
        Generic() {}
        @Bean
        DataSource dataSource(DataSourceProperties properties) {
        	//使用DataSourceBuilder创建数据源，利用反射创建响应式type的数据源，并且绑定相关属性
            return properties.initializeDataSourceBuilder().build();
        }
    }

6.2整合Druid数据源

6.2.1引入Druid

<!--引入数据源-->
<dependency>
	<groupId>com.alibaba</groupId>
	<artifactId>druid</artifactId>
	<version>1.1.8</version>
</dependency>

6.2.2配置文件引入

   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
#    schema:
#      - classpath:department.sql

6.2.3DruidConfig.class

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

    //配置Druid的监控
    //1、配置一个管理后台的Servlet
    @Bean
    public ServletRegistrationBean statViewServlet(){
        //处理druid下的所有请求
        ServletRegistrationBean bean = new ServletRegistrationBean(new StatViewServlet(), "/druid/*");
        HashMap<Object, Object> initParams = new HashMap<>();
        initParams.put("loginUsername","admin");
        initParams.put("loginPassword","123456");
        initParams.put("allow","");//默认就是允许所有访问
        initParams.put("deny","192.169.141.1");//本机ip
        bean.setInitParameters(initParams);
        return bean;
    }

    //2、配置一个web监控的filter
    @Bean
    public FilterRegistrationBean webStatFilter(){
        FilterRegistrationBean bean = new FilterRegistrationBean();
        bean.setFilter(new WebStatFilter());

        Map<String,String> initParams = new HashMap<>();
        initParams.put("exclusions","*.js,*.css,/druid/*");
        bean.setInitParameters(initParams);
        //拦截所有请求
        bean.setUrlPatterns(Arrays.asList("/*"));
        return bean;
    }
}

6.2.4遇到的问题

启动时候，报一下错误

根据报错提示在配置文件的24行，查看配置文件，该行代码是 filters: stat,wall,log4j

看报错原因Reason: org.apache.log4j.Logger，于是猜想少了log4j的相关依赖，在pom中引入相关依赖

<dependency>
	<groupId>log4j</groupId>
	<artifactId>log4j</artifactId>
	<version>1.2.17</version>
</dependency>

启动后成功

6.2.5访问http://localhost:8087/druid

输入配置好的用户名、密码

此时sql监控空空如也，web应用请求次数为0
此时访问一个查询接口

在回到druid页面，查看sql监控

web应用

6.3整合MyBatis

<dependency>
	<groupId>org.mybatis.spring.boot</groupId>
	<artifactId>mybatis-spring-boot-starter</artifactId>
	<version>2.2.0</version>
</dependency>

6.3.1步骤

1）、配置数据源相关属性（见上一节Druid）
2）、给数据库建表

3）、创建javaBean

4）、注解版

//指定这是一个操作数据库的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 deleteDeptById(Integer id);

    @Options(useGeneratedKeys = true,keyProperty = "id")
    @Insert("insert into department(departmentName) value(#{departmentName})")
    public int insertDept(Department department);

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

5）、自定义配置规则、批量扫描接口

6）、配置文件版

yml中：

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

EmployeeMapper.java中

public interface EmployeeMapper {

    public Employee getEmpById(Integer id);

    public void insertEmp(Employee employee);
}

EmployeeMapper.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.sguigu.springboot06.mapper.EmployeeMapper">

    <select id="getEmpById" resultType="com.sguigu.springboot06.bean.Employee">
        SELECT * FROM employee WHERE id = #{id}
    </select>

    <insert id="insertEmp">
        INSERT INTO employee(lastName,email,gender,d_id) VALUES (#{lastName},#{email},#{gender},#{dId})
    </insert>

</mapper>

接口

	@GetMapping("/emp/{id}")
    public Employee getEmp(@PathVariable("id") Integer id){
        return employeeMapper.getEmpById(id);
    }

6.3、整合SpringData JPA

6.3.1简介

统一API对数据访问层进行操作

6.3.2整合SpringData JPA

JPA:ORM(Object Relational MApping);
1）、编写一个实体类（bean）和数据表进行映射，并且配置好映射关系；

//使用JPA注解配置映射关系
@Entity//告诉JPA这是一个实体类(和数据表映射的类)
@Table(name = "tb1_user")//指定和哪个表对应;如果省略默认表名就是user
@Data
public class User {

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

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

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

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

//继承JpaRepository来完成对数据库的操作
public interface userRepository extends JpaRepository<User,Integer> {
}

3）、基本的配置

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

4）、Controller