SpringBoot | 访问数据库

一、简介

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

二、整合JDBC与数据源

1. JDBC

<dependencies>
    <!-- JDBC -->
    <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-jdbc</artifactId>
    </dependency>
	<!-- MySQL 驱动 -->
    <dependency>
        <groupId>mysql</groupId>
        <artifactId>mysql-connector-java</artifactId>
        <scope>runtime</scope>
    </dependency>
</dependencies>

导入 application.yml 配置文件，由于使用的 mysql 驱动的版本是 8，版本较高，因此需要作如下配置，尤其是 url 和 driver-class-name，否则会报错

spring:
  datasource:
    username: root
    password: root
    url: jdbc:mysql://localhost:3306/mybatis?useSSL=true&serverTimezone=GMT
    driver-class-name: com.mysql.cj.jdbc.Driver

数据源的相关配置都是在 DataSourceProperties 中进行配置的

三、整合Druid数据源

1. 导入Druid数据源的pom文件

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

2. 配置Druid的配置文件

application.yml

spring:
  datasource:
    # 数据源的基本配置
    username: root
    password: root
    url: jdbc:mysql://localhost:3306/mybatis?useSSL=true&serverTimezone=GMT
    driver-class-name: com.mysql.cj.jdbc.Driver
    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

3. 配置Druid数据源的监控

创建一个 DruidConfig 类，在里面分别配置 Config、Servlet 和 Filter

Config

将 Druid 的配置文件引入进来

// 从配置文件加载 druid 的配置信息
@ConfigurationProperties(prefix = "spring.datasource")
@Bean
public DataSource druid() {
    return new DruidDataSource();
}

Servlet：配置一个管理后台的 Servlet

需要返回一个 ServletRegistrationBean 的对象，并设置该对象的属性，包括监控登陆的用户名和密码等

@Bean
public ServletRegistrationBean statViewServlet() {
    ServletRegistrationBean bean
        = new ServletRegistrationBean(new StatViewServlet(), "/druid/*");
    Map<String, String> initParam = new HashMap<>();
    initParam.put("loginUsername", "admin");
    initParam.put("loginPassword", "123456");
    initParam.put("allow", ""); // 默认允许所有访问
    bean.setInitParameters(initParam);
    return bean;
}

Filter：配置一个管理数据源监控的 Filter

需要返回一个 FilterRegistrationBean 的对象

@Bean
public FilterRegistrationBean webStatFilter() {
    FilterRegistrationBean bean
        = new FilterRegistrationBean();
    // 设置 Filter
    bean.setFilter(new WebStatFilter());
    Map<String, String> initParam = new HashMap<>();
	// 不过滤 *.js、*.css ...
    initParam.put("exclusions", "*.js,*.css,/druid/*");
    bean.setInitParameters(initParam);
    bean.setUrlPatterns(Arrays.asList("/*"));
    return bean;
}

4. 启动

在浏览器的地址栏中输入 http://localhost:8080/druid/index.html，会出现以下的页面

四、整合MyBatis

需要的 pom 文件

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

依然需要配置好 druid 数据源，创建 JavaBean

4.1 注解版

首先创建一个 Mapper 接口，需要在接口名上添加 @Mapper 注解，然后编写抽象方法，需要注意的是，此时增删改查语句直接写在方法名上，如果需要传递参数，则和 MyBatis 一样，使用 #{xxx} 传递参数

@Mapper
public interface EmployeeMapper {

    @Select("select id, last_name lastName, gender, email, dept_id deptId from employee")
    List<Employee> getAllEmps();

    @Select("select id, last_name lastName, gender, email, dept_id deptId from employee where id = #{empId}")
    Employee getAllEmp(Integer empId);

    @Delete("delete from employee where id = #{empId}")
    int deleteEmplById(Integer empId);

    @Delete("delete from employee")
    int deleteEmps();

    @Update("update employee set last_name = #{lastName} where id = #{id}")
    int updateEmpById(Employee employee);

    @Insert("insert into employee(id, last_name, gender, email, dept_id) values(#{id}, #{lastName}, #{gender}, #{email}, #{deptId})")
    int insertEmp(Employee employee);

}

然后将 xxxMapper 类自动装配进测试类中，对抽象方法进行测试

@RunWith(SpringRunner.class)
@SpringBootTest
public class Springboot07DataMybatisApplicationTests {

    @Autowired
    EmployeeMapper employeeMapper;

    @Test
    public void contextLoads() {
        System.out.println(employeeMapper.getAllEmp(1));
    }

    @Test
    public void getAllEmps() {
        List<Employee> allEmps = employeeMapper.getAllEmps();
        System.out.println(allEmps);
    }

    @Test
    public void insertEmp() {
        Employee employee = new Employee();
        employee.setId(1);
        employee.setLastName("luwenhe");
        employee.setEmail("luwenhe@123.com");
        employee.setGender(1);
        employee.setDeptId(1);
        int i = employeeMapper.insertEmp(employee);
        System.out.println(i);
    }

    @Test
    public void updateEmployee() {
        Employee employee = new Employee();
        employee.setId(1);
        employee.setLastName("xixixi");
        int i = employeeMapper.updateEmpById(employee);
        System.out.println(i);
    }

    @Test
    public void deleteAll() {
        int i = employeeMapper.deleteEmps();
        System.out.println(i);
    }

}

需要注意的是，这里如果不再 xxxMapper 接口名上面添加 @Mapper 注解，那么会出现以下的错误，表示 SpringBoot 无法找到对应的 xxxMapper 接口

Caused by: org.springframework.beans.factory.NoSuchBeanDefinitionException: No qualifying bean of type 'edu.just.springboot.mapper.EmployeeMapper' available: expected at least 1 bean which qualifies as autowire candidate. Dependency annotations: {@org.springframework.beans.factory.annotation.Autowired(required=true)}

解决的方法有2个：

• 第一种：直接在 xxxMapper 接口上面添加 @Mapper 注解

• 第二种：在主方法种添加 @MapperScan(包名) 注解，即扫描 mapper 层下的所有接口

  @SpringBootApplication
  // 批量扫描所以 mapper 下的接口
  @MapperScan("edu.just.springboot.mapper")
  public class Springboot07DataMybatisApplication {
  
      public static void main(String[] args) {
          SpringApplication.run(Springboot07DataMybatisApplication.class, args);
      }
  
  }
  

可以在配置类中设置 MyBatis 的自动驼峰命名转换，即将类似 user_name 转化成 userName 这种格式

在容器中添加一个 ConfigurationCustomizer 组件，重写其中的 customer 方法，并且设置驼峰命名法

@org.springframework.context.annotation.Configuration
public class MyBatisConfig {
    
    @Bean
    public ConfigurationCustomizer configurationCustomizer() {
        return new ConfigurationCustomizer() {
            @Override
            public void customize(Configuration configuration) {
                // 配置驼峰命名法
                configuration.setMapUnderscoreToCamelCase(true);
            }
        };
    }

}

4.2 配置版

如果不是直接在 Mapper 接口的抽象方法上面直接编写增删改查语句，也可以通过外部配置文件 xxxMapper.xml 来配置，类似于 SpringMVC 这种

首先在 mapper 包下创建一个名为 DepartmentMapper 的接口文件

@Mapper
public interface DepartmentMapper {

    List<Department> getDepts();

    Department getDeptById(Integer id);

    Integer addDept(Department department);

    Integer deleteDept(Integer id);

    Integer updateDept(Department department);

}

在 resources 下的 mapper 文件下创建一个名为 DepartmentMapper.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="edu.just.springboot.mapper.DepartmentMapper">
    <select id="getDeptById" resultType="edu.just.springboot.bean.Department">
        select id, depart_name departName from department where id = #{id}
    </select>

    <select id="getDepts" resultType="edu.just.springboot.bean.Department">
        select id, depart_name departName from department
    </select>

    <insert id="addDept">
        insert into department values(#{id}, #{departName})
    </insert>

    <delete id="deleteDept">
        delete from department where id = #{id}
    </delete>

    <update id="updateDept">
        update department set depart_name = #{departName} where id = #{id}
    </update>
</mapper>

最后，需要在主配置文件中对上面的 mybatis 的配置文件进行映射

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

然后在测试类中进行测试：

@RunWith(SpringRunner.class)
@SpringBootTest
public class DepartmentMapperTest {

    @Autowired
    DepartmentMapper departmentMapper;

    @Test
    public void test1() {
        Department deptById = departmentMapper.getDeptById(1);
        System.out.println(deptById);
    }

    @Test
    public void test2() {
        List<Department> a = departmentMapper.getDepts();
        System.out.println(a);
    }

    @Test
    public void test3() {
        Department department = new Department();
        department.setId(100);
        department.setDepartName("xixixi");
        departmentMapper.addDept(department);
    }

    @Test
    public void test4() {
        Integer integer = departmentMapper.deleteDept(100);
        System.out.println(integer);
    }

    @Test
    public void test5() {
        Department department = new Department();
        department.setId(1);
        department.setDepartName("qwer");
        Integer integer = departmentMapper.updateDept(department);
        System.out.println(integer);
    }

}