六、SpringBoot与数据访问

一、JDBC

1. 导入依赖

 <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>

2. 配置数据源连接

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

3. 效果：
   • 默认使用com.zaxxer.hikari.HikariDataSource数据源；
   • 数据源相关配置都在DataSourceProperties里面；
4. 自动配置原理：
   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操作数据库；

二、整合Druid数据源

1. 导入依赖

<!-- https://mvnrepository.com/artifact/com.alibaba/druid -->
<!-- 阿里官方的druid数据源jar包，适合在非springboot项目中使用，具体配置方法不做介绍-->
<!--
<dependency>
    <groupId>com.alibaba</groupId>
    <artifactId>druid</artifactId>
    <version>1.2.8</version>
</dependency>
-->
 <!-- https://mvnrepository.com/artifact/com.alibaba/druid-spring-boot-starter -->
 <dependency>
     <groupId>com.alibaba</groupId>
     <artifactId>druid-spring-boot-starter</artifactId>
     <version>1.2.8</version>
 </dependency>

2. 编写配置文件

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/*'

3. 登录效果：
   
   

三、整合MyBatis

1.引入mybatis-starter依赖

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

依赖关系图：

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

}

4、配置文件版

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

四、整合SpringData JPA

1、SpringData简介

2、整合SpringData JPA

JPA：ORM(object relational mapping)

1. 编写一个实体类（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
}

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

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

3. 基本的配置(JpaProperties)

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