SpringBoot - 数据访问

1. 概述

对于数据访问层，无论是SQL还是NOSQL，SpringBoot默认采用整合Spring Data的方式进行统一处理，添加大量自动配置，屏蔽了很多设置，引入各种xxxTemplate，xxxRepository来简化我们对数据访问层的操作

2. 整合基本的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>

在yml文件中配置

spring:
  datasource:
    username: root
    password: 123456
    url: jdbc:mysql://localhost:3306/myemployees?useUnicode=true&useJDBCCompliantTimezoneShift=true&serverTimezone=UTC&characterEncoding=utf8

测试

@SpringBootTest
class SpringBootDataaccessApplicationTests {
    @Autowired
    DataSource dataSource;
    @Test
    void contextLoads() throws SQLException {
        System.out.println(dataSource.getClass());
        Connection connection = dataSource.getConnection();
        System.out.println(connection);
        connection.close();
    }
}

可以看到使用的是hikari.HikariDataSource数据源

springboot的数据源的所有配置都是在DataSourceProperties中参考，自动配置原理都在org.springframework.boot.autoconfigure.jdbc下

可以使用spring.datasource.type指定自定义的数据源类型；

SpringBoot默认可以支持

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

当然也可以自定义数据源

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

}

可以把建表语句，插入数据语句放到指定位置在项目启动的时候可以自动执行
默认只需要将文件命名为

建表sql: schema-*.sql
插入数据sql: data-*.sql

默认规则：schema.sql，schema-all.sql；
可以使用   
schema:
	- classpath:department.sql
指定位置

3. 整合mybatis

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

yml配置文件

spring:
  datasource:
    driver-class-name: com.mysql.cj.jdbc.Driver
    url: jdbc:mysql://localhost:3306/springboot?useUnicode=true&useJDBCCompliantTimezoneShift=true&serverTimezone=UTC&characterEncoding=utf8
    username: root
    password: 123456

建表

SET FOREIGN_KEY_CHECKS=0;

-- ----------------------------
-- Table structure for employee
-- ----------------------------
DROP TABLE IF EXISTS `employee`;
CREATE TABLE `employee` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `lastName` varchar(255) DEFAULT NULL,
  `email` varchar(255) DEFAULT NULL,
  `gender` int(2) DEFAULT NULL,
  `d_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
SET FOREIGN_KEY_CHECKS=0;

-- ----------------------------
-- Table structure for department
-- ----------------------------
DROP TABLE IF EXISTS `department`;
CREATE TABLE `department` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `departmentName` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

javabean

public class Department {

    private Integer id;
    private String departmentName;
	//....getter setter
}

public class Employee {
    private Integer id;
    private String lastName;
    private Integer gender;
    private String email;
    private Integer dId;
	/....getter setter
}

① 注解版

//指定这是一个操作数据库的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) values(#{departmentName})")
    public int insertDept(Department department);
	//改
    @Update("update department set departmentName=#{departmentName} where id=#{id}")
    public 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);
            }
        };
    }
}

做到批量扫描所有的Mapper接口，而不在每个类上加@mapper

//使用MapperScan批量扫描所有的Mapper接口；
@MapperScan(value = "com.minifull.mapper")
@SpringBootApplication
public class SpringBoot06DataMybatisApplication {

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

② 配置文件版

mybatis:
#  mapper文件所在的位置在resources下的mapper文件夹
  mapper-locations: mapper/*.xml
#  别名
  type-aliases-package: pojo
  configuration:
#    开启驼峰命名
    map-underscore-to-camel-case: true

在Application启动类添加注解扫描接口mapper自动注入到spring

@SpringBootApplication
@MapperScan("com.javaer.mapper")
public class Application {
    public static void main(String[] args) {
        SpringApplication.run(Application.class,args);
    }
}