springboot学习笔记之springboot整合mybatis与Druid操作MySQL数据库

springboot整合mybatis与Druid操作MySQL数据库

在pom文件中加入MySQL、Druid、mybatis的依赖

<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>
    <groupId>com.kino</groupId>
    <artifactId>springboot-mybatis</artifactId>
    <version>1.0.0-SNAPSHOT</version>

    <parent>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-parent</artifactId>
        <version>2.3.4.RELEASE</version>
    </parent>

    <properties>
        <java.version>1.8</java.version>
        <mybatis.version>2.1.3</mybatis.version>
        <druid.version>1.1.24</druid.version>
    </properties>

    <dependencies>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>

        <!-- MySQL数据库依赖 -->
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <scope>runtime</scope>
        </dependency>

        <!-- 数据库连接池,Druid连接数据库 -->
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid-spring-boot-starter</artifactId>
            <version>${druid.version}</version>
        </dependency>

        <!-- mybatis操作数据库 -->
        <dependency>
            <groupId>org.mybatis.spring.boot</groupId>
            <artifactId>mybatis-spring-boot-starter</artifactId>
            <version>${mybatis.version}</version>
        </dependency>


    </dependencies>

    <build>
        <plugins>
            <plugin>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-maven-plugin</artifactId>
                <configuration>
                    <!-- 指向程序的启动类 -->
                    <mainClass>com.kino.SpringBootMybatis</mainClass>
                </configuration>
            </plugin>
        </plugins>
    </build>
</project>

在application.yml中配置Druid、mybatis

server:
  port: 8090

spring:
  application:
    name: springboot-mybatis
  datasource:
    # Druid根据url的前缀来识别DriverClass,所以不用配置driver-class-name
    # useUnicode 采用Unicode字符编码
    url: jdbc:mysql://localhost:3306/db_springboot_mybatis?useUnicode=true&characterEncoding=utf-8&useSSL=true&serverTimezone=UTC
    username: root
    password: root
    # type: com.alibaba.druid.pool.DruidDataSource # 指定数据库连接池的类型,默认为hikari

mybatis:
  mapper-locations: classpath:mapper/*.xml # 配置mapperXML的位置，就是指resources->mapper目录下的xml文件
  configuration:
    map-underscore-to-camel-case: true # 配置mybatis支持驼峰命名
  type-aliases-package: com.kino.entity # 配置别名

在项目启动类中配置MapperScan

import org.mybatis.spring.annotation.MapperScan;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;

@SpringBootApplication
@MapperScan("com.kino.mapper") // 指向mapper接口
public class SpringBootMybatis {
    public static void main(String[] args) {
        SpringApplication.run(SpringBootMybatis.class, args);
    }
}

Druid

Druid GitHub

Druid GitHub 在wiki中有操作Druid的相关简介与操作文档

Druid GitHub：https://github.com/alibaba/druid

Druid官方文档

Druid官方wiki

springboot集成Druid

(1)在pom文件中添加Druid的依赖
最新版的Druid可以通过DruidGitHub或maven仓库获取到

Druid GitHub：https://github.com/alibaba/druid

maven 仓库：https://mvnrepository.com

<dependency>
     <groupId>com.alibaba</groupId>
     <artifactId>druid-spring-boot-starter</artifactId>
     <version>${druid.version}</version>
 </dependency>

在application.yml中配置Druid

spring:
  application:
    name: springboot-mybatis
  datasource:
    # Druid根据url的前缀来识别DriverClass,所以不用配置driver-class-name
    # useUnicode 采用Unicode字符编码
    url: jdbc:mysql://localhost:3306/db_springboot_mybatis?useUnicode=true&characterEncoding=utf-8&useSSL=true&serverTimezone=UTC
    username: root
    password: root

Druid的其他配置

详见Druid GitHub wiki

将数据库密码配置为密文

将数据库密码进行加密

下载Druid的release的jar版本,最好是与项目中版本相同

执行下面的命令生成相关秘钥
java -cp druid-1.1.24.jar com.alibaba.druid.filter.config.ConfigTools you_password

在springboot项目中配置数据库密码密文

将public-key与password在application.yml中进行配置
在connection-properties开启解密
开启ConfigFilter

server:
  port: 8090

# 通过Druid生成的公钥
public-key: MFwwDQYJKoZIhvcNAQEBBQADSwAwSAJBAIMEzoEun3E6mxOmhwpAOrMj5UhrovsOOoXBSM2WzAKdbuwA9+IDcbunNB5BuLmvcmg8TsgttvP435NGjZEIp7ECAwEAAQ==

spring:
  application:
    name: springboot-mybatis
  datasource:
    # Druid根据url的前缀来识别DriverClass,所以不用配置driver-class-name
    # useUnicode 采用Unicode字符编码
    url: jdbc:mysql://localhost:3306/db_springboot_mybatis?useUnicode=true&characterEncoding=utf-8&useSSL=true&serverTimezone=UTC
    username: root
    # 使用Druid生成的秘钥
    password: f19TxBHdGsr1byfEnAPvrFhPWEQ2gGV5qlHe2NZ6mk220QW8AXBNpKfr/eq2jWheNahJI2G1V4P47NS7gCWjXg==
    druid:
      # config.decrypt=true 开启解密 config.decrypt.key=${public-key}指向公钥
      connection-properties: config.decrypt=true;config.decrypt.key=${public-key}
      filter:
        config:
          enabled: true # 开启ConfigFilter


mybatis:
  mapper-locations: classpath:mapper/*.xml # 配置mapperXML的位置，就是指resources->mapper目录下的xml文件
  configuration:
    map-underscore-to-camel-case: true # 配置mybatis支持驼峰命名
  type-aliases-package: com.kino.entity # 配置别名

配置Druid的内置监控页面

在application.yml中开启Druid的监控信息配置

server:
  port: 8090

# 通过Druid生成的公钥
public-key: MFwwDQYJKoZIhvcNAQEBBQADSwAwSAJBAIMEzoEun3E6mxOmhwpAOrMj5UhrovsOOoXBSM2WzAKdbuwA9+IDcbunNB5BuLmvcmg8TsgttvP435NGjZEIp7ECAwEAAQ==

spring:
  application:
    name: springboot-mybatis
  datasource:
    # Druid根据url的前缀来识别DriverClass,所以不用配置driver-class-name
    # useUnicode 采用Unicode字符编码
    url: jdbc:mysql://localhost:3306/db_springboot_mybatis?useUnicode=true&characterEncoding=utf-8&useSSL=true&serverTimezone=UTC
    username: root
    # 使用Druid生成的秘钥
    password: f19TxBHdGsr1byfEnAPvrFhPWEQ2gGV5qlHe2NZ6mk220QW8AXBNpKfr/eq2jWheNahJI2G1V4P47NS7gCWjXg==
    druid:
      # config.decrypt=true 开启解密 config.decrypt.key=${public-key}指向公钥
      # druid.stat.mergeSql=true;druid.stat.slowSqlMillis=5000 开启SQL慢查询的配置
      connection-properties: config.decrypt=true;config.decrypt.key=${public-key};druid.stat.mergeSql=true;druid.stat.slowSqlMillis=5000
      filter:
        config:
          enabled: true # 开启ConfigFilter
        stat:
          enabled: true # 开启SQL监控信息
        wall:
          enabled: true # 开启SQL防火墙
        log4j2:
          enabled: true


mybatis:
  mapper-locations: classpath:mapper/*.xml # 配置mapperXML的位置，就是指resources->mapper目录下的xml文件
  configuration:
    map-underscore-to-camel-case: true # 配置mybatis支持驼峰命名
  type-aliases-package: com.kino.entity # 配置别名

配置servlet(内置监控页面)

import com.alibaba.druid.support.http.StatViewServlet;
import org.springframework.boot.web.servlet.ServletRegistrationBean;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;

import java.util.HashMap;

@Configuration
public class DruidMonitorInfoConfiguration {

    /**
     * Druid监控信息相关的配置
     */
    @Bean
    public ServletRegistrationBean statViewServlet(){

        // druid/* 配置访问时的路由
        ServletRegistrationBean<StatViewServlet> bean = new ServletRegistrationBean<>(new StatViewServlet(), "/druid/*");
        HashMap<String, String> initParameters = new HashMap<>();

        // 配置用户名和密码
        initParameters.put("loginUsername","admin");
        initParameters.put("loginPassword","123456");
        // allow代表可以访问，""为所有，127.0.0.1本机
        initParameters.put("allow","127.0.0.1");
        // initParameters.put("xu","127.0.0.1"); 配置谁不可以访问
        bean.setInitParameters(initParameters);
        return bean;
    }

}

访问
http://127.0.0.1:8090/druid
127.0.0.1 是allow中配置的IP
8090 是server.port

mybatis

@MapperScan

xml版

基本的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="">

</mapper>

未完待续 ~~~