六、SpringBoot与数据访问

一、JDBC二、整合Druid数据源三、整合MyBatis

1.引入mybatis-starter依赖2. 步骤：3、注解版4、配置文件版 四、整合SpringData JPA

1、SpringData简介2、整合SpringData JPA

导入依赖

 
     org.springframework.boot
     spring-boot-starter-jdbc
 
 
     mysql
     mysql-connector-java
     runtime
 

配置数据源连接

spring:
  datasource:
    username: root
    password: 199902
    url: jdbc:mysql://hadoop102:3306/test?useUnicode=true&characterEncoding=utf8&serverTimezone=UTC
    driver-class-name: com.mysql.jdbc.Driver

效果：

默认使用com.zaxxer.hikari.HikariDataSource数据源；数据源相关配置都在DataSourceProperties里面； 自动配置原理：
org.springframework.boot.autoconfigure.jdbc：

参考DataSourceConfiguration,根据配置创建数据源，默认使用Hikari连接池，可以使用spring.datasource.type指定自定义的数据源类型；SpringBoot默认支持：

org.apache.tomcat.jdbc.pool.DataSource
org.apache.commons.dbcp2.BasicDataSource
com.zaxxer.hikari.HikariDataSource
oracle.ucp.jdbc.PoolDataSource

自定义数据源

@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();
    }
}

DataSourceInitializationConfiguration
作用

ddlOnlyscriptDataSourceInitializer：运行建表语句dmlOnlyscriptDataSourceInitializer：运行插入数据的SQL语句
默认只需要将文件命名为：

#执行ddl语句
schema-*.sql、schema.sql
#执行dml语句
data-*.sql、data.sql
#可以使用：
spring:
  datasource:
    username: root
    password: 199902
    url: jdbc:mysql://hadoop102:3306/test?useUnicode=true&characterEncoding=utf8&serverTimezone=UTC
    driver-class-name: com.mysql.jdbc.Driver
  sql:
    init:
      schema-locations:
        - classpath:schema.sql
      password: 199902
      username: root
      mode: always

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

导入依赖

 
 
     com.alibaba
     druid-spring-boot-starter
     1.2.8
 

编写配置文件

spring:
  datasource:
    username: root
    password: 199902
    url: jdbc:mysql://hadoop102:3306/test?useSSL=false&useUnicode=true&characterEncoding=utf8&serverTimezone=UTC
    driver-class-name: com.mysql.jdbc.Driver
    type: com.alibaba.druid.pool.DruidDataSource
    druid:
      initial-size: 5
      min-idle: 5
      max-active: 20
      max-wait: 60000
      time-between-eviction-runs-millis: 60000
      min-evictable-idle-time-millis: 30000
      validation-query: select * from dual
      test-while-idle: true
      test-on-borrow: false
      test-on-return: false
      pool-prepared-statements: true
      #配置监控统计拦截的filters，去掉后监控界面SQL无法统计，·wall·用于防火墙
      filters: stat,wall
      max-pool-prepared-statement-per-connection-size: 20
      use-global-data-source-stat: true
      connect-properties: druid.stat.mergeSql=true;druid.stat.slowSqlMillis=500
      stat-view-servlet:
        login-password: 123456
        login-username: admin
        enabled: true
        deny: 192.168.215.165
      web-stat-filter:
        enabled: true
        url-pattern: /*
        exclusions:
          - '*.js'
          - '*.css'
          - '/druid/*'

登录效果：

 
     org.mybatis.spring.boot
     mybatis-spring-boot-starter
     2.2.2
 

依赖关系图：

配置数据源相关属性（见上一节Druid）给数据库建表创建javaBean 3、注解版

@Mapper
public interface DepartmentMapper{

    @Select("select * from test.department where id= #{id}")
    Department getDeptById(Integer id);

    @Delete("delete from test.department where id = #{id}")
    int deleteDeptById(Integer id);
 	@Options(useGeneratedKeys = true,keyProperty = "id")
    @Insert("insert into test.department (departmentName) values (#{departmentname}) ")
    int insertDept(Department department);

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

自定义mybatis的配置规则,给容器中添加ConfigurationCustomizer 组件；

@org.springframework.context.annotation.Configuration
public class MybatisConfig {

    @Bean
    public ConfigurationCustomizer configurationCustomizer(){
        return new ConfigurationCustomizer() {
            @Override
            public void customize(Configuration configuration) {
                configuration.setMapUnderscoreToCamelCase(true);
            }
        };
    }
}

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

@MapperScan(basePackages = "cn.hymll.springboot.mapper")
@SpringBootApplication
public class SpringbootDataMybatisApplication {

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

}

mybatis:
  #配置mybatis的配置文件路径
  config-location: classpath:mybatis-config.xml
  #配置mybatis的mapper映射文件路径
  mapper-locations: classpath:mapper/*

JPA：ORM(object relational mapping)

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

//配置JPA注解配置映射关系
//告诉JPA这是一个实体类
@Entity
//@Table来指定和那个数据表对应，如果省略，默认表名就是user
@Table(name = "tbl_user")
@JsonIgnoreProperties(value = {"hibernateLazyInitializer"})
public class User {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)//自增主键
    private Integer id;
    @Column(name = "last_name",length = 50)//这是和数据表对应的一个列
    private String lastName;
    @Column()//省略默认列名就是属性名
    private String email;
    
	//setter and getter
}

编写一个DAO接口开操作实体类对应的数据表（Repository）

//继承JpaRepository来完成对数据库的操作
public interface UserRepository extends JpaRepository {
}

基本的配置(JpaProperties)

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