SpringBoot数据访问+Druid+Mybatis操作步骤

一、数据访问原始的配置

访问SpringBoot的官网，找到对应的文档，可以查阅相关的starter

https://docs.spring.io/spring-boot/docs/current/reference/html/using-spring-boot.html#using-boot-starter

1、导入JDBC场景

<!--数据访问：导入JDBC场景-->
<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-data-jdbc</artifactId>
</dependency>

数据库的驱动没有导入，需要手动导入：官方不知道想要连接的是哪个数据库，所以没有自动导入

2、导入MySql的驱动

https://mvnrepository.com/search?q=mysql

<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <!--<version>8.0.22</version>-->
</dependency>

1. 其中boot2.4.2中mysql官方仲裁默认的版本号是8.0.22，所以可以省略不写version
2. 想要修改版本：
   1. 直接以来引入的具体版本（maven的就近依赖原则）
   2. 重新声明版本，在当期那pom的属性里声明版本号，优先当前项目的属性。

• 引入了jdbc，必须要配置相关的数据库属性，否则项目启动会报错

3、配置Mysql的基本属性

spring:
  datasource:
    url: jdbc:mysql://localhost:3306/test?serverTimezone=GMT%2B8&useUnicode=true&characterEncoding=utf-8
    username: root
    password: root
    driver-class-name: com.mysql.cj.jdbc.Driver

4、测试

package com.ityj.druid;

import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.jdbc.core.JdbcTemplate;

import javax.sql.DataSource;
import java.util.Map;

@SpringBootTest
class DruidApplicationTests {

    @Autowired
    private JdbcTemplate jdbcTemplate;

    @Autowired
    private DataSource dataSource;

    @Test
    void contextLoads() {
        System.out.println(dataSource.getClass());
        Map<String, Object> stringObjectMap = jdbcTemplate.queryForMap("select name from person where id = 1");
        System.out.println("stringObjectMap = " + stringObjectMap);
    }

}

结果：
class com.zaxxer.hikari.HikariDataSource
stringObjectMap = {name=Jack}

二、使用Druid数据源

https://github.com/alibaba/druid/tree/master/druid-spring-boot-starter

1、引入druid的starter

<dependency>
    <groupId>com.alibaba</groupId>
    <artifactId>druid-spring-boot-starter</artifactId>
    <version>1.1.17</version>
</dependency>

此时不用任何其他操作，结合jdbc的其他配置，即可切换为druid数据源

class com.alibaba.druid.spring.boot.autoconfigure.DruidDataSourceWrapper

2、配置并使用druid的监控功能

spring:
  datasource:
    url: jdbc:mysql://localhost:3306/test?serverTimezone=GMT%2B8&useUnicode=true&characterEncoding=utf-8
    username: root
    password: root
    driver-class-name: com.mysql.cj.jdbc.Driver

    druid:
      aop-patterns: com.atguigu.admin.*  #监控SpringBean
      filters: stat,wall     # 底层开启功能，stat（sql监控），wall（防火墙）

      stat-view-servlet: # 配置监控页功能
        enabled: true
        login-username: admin
        login-password: admin
        resetEnable: true

      web-stat-filter: # 监控web
        enabled: true
        urlPattern: /*
        exclusions: '*.js,*.gif,*.jpg,*.png,*.css,*.ico,/druid/*'

      filter:
        stat: # 对上面filters里面的stat的详细配置
          slow-sql-millis: 1000
          logSlowSql: true
          enabled: true
        wall:
          enabled: true
          config:
            drop-table-allow: false

三、整合Mybatis

https://github.com/mybatis/spring-boot-starter

1、引入starter

<!--  导入mybatis  -->
<dependency>
    <groupId>org.mybatis.spring.boot</groupId>
    <artifactId>mybatis-spring-boot-starter</artifactId>
    <version>2.1.4</version>
</dependency>

2、配置mybatis的规则

mybatis:
  # 制定mybatis全局配置文件的位置
  config-location: classpath:/mybatis/mybatis-config.xml
  mapper-locations: classpath:/mybatis/mapper/*Mapper.xml

3、编写mapper接口，并绑定对应的映射文件

1. mybatis-config.xml编写

   <?xml version="1.0" encoding="UTF-8" ?>
   <!DOCTYPE configuration
           PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
           "http://mybatis.org/dtd/mybatis-3-config.dtd">
   <configuration>
       <settings>
           <setting name="logImpl" value="STDOUT_LOGGING"/>
       </settings>
   </configuration>
   

2. 实体类Person.java

   package com.ityj.druid.entity;
   
   import io.swagger.annotations.ApiModel;
   import io.swagger.annotations.ApiModelProperty;
   import lombok.Data;
   
   import java.util.Date;
   
   @Data
   @ApiModel(value = "Person实体类")
   public class Person {
       @ApiModelProperty(value = "主键id")
       private int id;
       @ApiModelProperty(value = "姓名")
       private String name;
       @ApiModelProperty(value = "年龄")
       private int age;
       @ApiModelProperty(value = "生日(eg:1998/12/12 12:12:12)")
       private Date birthday;
       @ApiModelProperty(value = "性别")
       private String gender;
   }
   
   

3. 接口PersonMapper.java

   package com.ityj.druid.mapper;
   
   import com.ityj.druid.entity.Person;
   import org.apache.ibatis.annotations.Mapper;
   import org.apache.ibatis.annotations.Param;
   
   @Mapper
   public interface PersonMapper {
   
       Person getPersonById(@Param(value = "id") int id);
   
   }
   
   

4. 对应的mapper:PersonMapper.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.ityj.druid.mapper.PersonMapper">
       <select id="getPersonById" parameterType="int" resultType="com.ityj.druid.entity.Person">
           select * from person where id = #{id}
       </select>
   </mapper>
   

5. service接口PersonService.java

   package com.ityj.druid.service;
   
   import com.ityj.druid.entity.Person;
   
   public interface PersonService {
       Person getPersonById(int personId);
   }
   

6. service实现类PersonServiceImpl.java

   package com.ityj.druid.service.impl;
   
   import com.ityj.druid.entity.Person;
   import com.ityj.druid.mapper.PersonMapper;
   import com.ityj.druid.service.PersonService;
   import org.springframework.beans.factory.annotation.Autowired;
   import org.springframework.stereotype.Service;
   import org.springframework.util.ObjectUtils;
   
   @Service
   public class PersonServiceImpl implements PersonService {
   
       @Autowired
       private PersonMapper personMapper;
   
       @Override
       public Person getPersonById(int personId) {
           if (ObjectUtils.isEmpty(personId)) {
              return new Person();
           }
           return personMapper.getPersonById(personId);
       }
   }
   

7. controller控制器编写

   package com.ityj.druid.controller;
   
   import com.ityj.druid.service.PersonService;
   import io.swagger.annotations.ApiModel;
   import lombok.extern.slf4j.Slf4j;
   import org.springframework.beans.factory.annotation.Autowired;
   import org.springframework.util.ObjectUtils;
   import org.springframework.web.bind.annotation.RequestMapping;
   import org.springframework.web.bind.annotation.RequestMethod;
   import org.springframework.web.bind.annotation.RequestParam;
   import org.springframework.web.bind.annotation.RestController;
   
   @RestController
   @ApiModel(value = "HelloController测试的控制器入口")
   @Slf4j
   public class HelloController {
   
       @Autowired
       private PersonService personService;
   
       @RequestMapping(path = "/getPerson", method = RequestMethod.GET)
       public Object getPerson(@RequestParam(value = "personId") int personId) {
           log.info("进入getPerson()...");
           if (ObjectUtils.isEmpty(personId)) {
               return "请检查入参是否完整！";
           }
           return personService.getPersonById(personId);
       }
   
   }
   

8. 测试

9. 完全通过注解实现数据访问

package com.ityj.druid.mapper;

import com.ityj.druid.entity.Person;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import java.util.List;

@Mapper
public interface PersonMapper {

    @Select(value = "select * from person where 1 = 1")
    List<Person> getAllPerson();

}

总结：

  1. 引入mabatis-starter
  2. 配置application.yml中指定config-location和mapper-locations的位置
  3. 编写mapper接口并标注@Mapper注解
  4. 简单方法可以在接口上写@Select注解编写
  5. 复杂方法编写mapper.xml进行绑定映射
  6. @Mapper注解标注的类会被自动扫描；项目启动类上标注@MapperScan("com.ityj.druid.mapper")可以批量扫描，无需每个类上标注@Mapper注解