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>

其中boot2.4.2中mysql官方仲裁默认的版本号是8.0.22，所以可以省略不写version
想要修改版本：
直接以来引入的具体版本（maven的就近依赖原则）
重新声明版本，在当期那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注解