Springboot集成Mybatis+MySQL+Druid连接池，集成logback+slf4j日志案例

在上一篇已经介绍过如何用idea创建springboot工程，这里就不多介绍了，现在我们来详细讲解一下springboot是如何集成Mybatis和logback日志的！

一、首先我们要引入Mybatis、MySQL、Druid、slf4j、logback所需要的jar包，pom文件的配置如下：

<dependencies>
    <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-web</artifactId>
    </dependency>
    <dependency>
        <groupId>org.mybatis.spring.boot</groupId>
        <artifactId>mybatis-spring-boot-starter</artifactId>
        <version>1.3.2</version>
    </dependency>
    <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-autoconfigure</artifactId>
    </dependency>
    <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-test</artifactId>
        <scope>test</scope>
    </dependency>
    <!-- spring AOP切面 -->
    <dependency>
        <groupId>org.aspectj</groupId>
        <artifactId>aspectjrt</artifactId>
        <version>1.9.4</version>
    </dependency>
    <dependency>
        <groupId>org.aspectj</groupId>
        <artifactId>aspectjweaver</artifactId>
        <version>1.9.4</version>
    </dependency>
    <!-- fastjson -->
    <dependency>
        <groupId>com.alibaba</groupId>
        <artifactId>fastjson</artifactId>
        <version>1.2.58</version>
    </dependency>
    <!-- druid -->
    <dependency>
        <groupId>com.alibaba</groupId>
        <artifactId>druid</artifactId>
        <version>1.1.19</version>
    </dependency>
    <!-- mysql -->
    <dependency>
        <groupId>mysql</groupId>
        <artifactId>mysql-connector-java</artifactId>
        <version>5.1.40</version>
        <scope>runtime</scope>
    </dependency>
    <!-- mybatis -->
    <dependency>
        <groupId>org.mybatis</groupId>
        <artifactId>mybatis</artifactId>
        <version>3.5.2</version>
    </dependency>
    <dependency>
        <groupId>org.mybatis</groupId>
        <artifactId>mybatis-spring</artifactId>
        <version>2.0.2</version>
    </dependency>
    <!-- 日志集成 -->
    <dependency>
        <groupId>ch.qos.logback</groupId>
        <artifactId>logback-classic</artifactId>
    </dependency>
    <dependency>
        <groupId>org.slf4j</groupId>
        <artifactId>slf4j-api</artifactId>
    </dependency>
</dependencies>

build标签里配置资源文件地址：

<!-- 配置文件加载地址 -->
<resources>
    <resource>
        <directory>src/main/resources</directory>
        <includes>
            <include>**/*.xml</include>
            <include>**/*.yml</include>
            <include>**/*.properties</include>
        </includes>
    </resource>
</resources>

接下来我们配置application-xxx.xml文件：

server.port=80
spring.application.name=demo
spring.profiles.active=dev

通过spring.profiles.active定义开发（dev）、测试（test）、生产（prod）不同环境！

再是配置MySQL的连接属性：

## MySQL数据库信息配置
## mysql-connector-java 8.x的版本：com.mysql.cj.jdbc.Driver
jdbc.driver=com.mysql.jdbc.Driver
jdbc.url=jdbc:mysql://127.0.0.1:3306/test?characterEncoding=UTF-8&autoReconnect=true
jdbc.username=root
jdbc.password=123456

然后继续添加Druid连接池的属性：

############################### druid连接池配置 ###############################
## 配置初始化大小
druid.initialSize=5
## 最小连接数
druid.minIdle=10
## 最大连接数
druid.maxActive=30
## 配置获取连接等待超时的时间，单位毫秒
druid.maxWait=60000
## 配置间隔多久才进行一次检测，检测需要关闭的空闲连接，单位是毫秒
druid.timeBetweenEvictionRunsMillis=60000
## 配置一个连接在池中最小生存的时间，单位是毫秒
druid.minEvictableIdleTimeMillis=300000
## 配置一个连接在池中最大生存的时间，单位是毫秒
druid.maxEvictableIdleTimeMillis=900000
## 申请连接的时候检测，建议配置为true，不影响性能，并且保证安全性
druid.testWhileIdle=true
## 获取连接时执行检测，建议关闭，影响性能
druid.testOnBorrow=false
## 归还连接时执行检测，建议关闭，影响性能
druid.testOnReturn=false
## #是否开启PSCache，PSCache对支持游标的数据库性能提升巨大，oracle建议开启，mysql下建议关闭
druid.poolPreparedStatements=false
## 开启poolPreparedStatements后生效
druid.maxOpenPreparedStatements=20
## 是1.1.4中新增加的配置，如果有initialSize数量较多时，打开会加快应用启动时间
druid.asyncInit=true
## 使用Druid连接池连接阿里云AnalyticDB 建议配置keepAlive=true ，并使用1.1.16 之后的版本
druid.keepAlive=false
## 配置监控统计拦截的filters，配置扩展插件，常用的插件有=>stat:监控统计 log4j:日志，wall:防御sql注入
druid.filters=stat,wall,slf4j
## 测试连接
druid.validationQuery=SELECT 1 FROM DUAL            
#通过connectProperties属性来打开mergeSql功能;慢SQL记录
druid.connectionProperties='druid.stat.mergeSql=true;druid.stat.slowSqlMillis=5000'

最后再配置Mybatis的资源文件存放地址：

## 配置mybatis的mapper文件地址
mybatis.mapper-locations=classpath*:mapper/*.xml

我这里是存放在resources目录下的mapper文件夹下，如图：

好了，配置文件都搞定，接下来我们就需要通过代码来实现Druid连接我们的MySQL数据库：

1.新建一个实体类来加载application-xxx.xml文件中MySQL和Druid的属性：

import org.springframework.beans.factory.annotation.Value;
import org.springframework.stereotype.Component;

@Component //加入到spring容器中，其他类可以通过过@Autowired注解获取
public class DruidInfoDto {

    @Value("${jdbc.driver}")
    private String driver;

    @Value("${jdbc.url}")
    private String url;

    @Value("${jdbc.username}")
    private String username;

    @Value("${jdbc.password}")
    private String password;

    @Value("${druid.initialSize}")
    private int initialSize;

    @Value("${druid.minIdle}")
    private int minIdle;

    @Value("${druid.maxActive}")
    private int maxActive;

    @Value("${druid.maxWait}")
    private int maxWait;

    @Value("${druid.timeBetweenEvictionRunsMillis}")
    private int timeBetweenEvictionRunsMillis;

    @Value("${druid.minEvictableIdleTimeMillis}")
    private int minEvictableIdleTimeMillis;

    @Value("${druid.validationQuery}")
    private String validationQuery;

    @Value("${druid.testWhileIdle}")
    private boolean testWhileIdle;

    @Value("${druid.testOnBorrow}")
    private boolean testOnBorrow;

    @Value("${druid.testOnReturn}")
    private boolean testOnReturn;

    @Value("${druid.poolPreparedStatements}")
    private boolean poolPreparedStatements;

    @Value("${druid.maxOpenPreparedStatements}")
    private int maxOpenPreparedStatements;

    @Value("${druid.filters}")
    private String filters;

    @Value("${druid.connectionProperties}")
    private String connectionProperties;

    public String getDriver() {
        return driver;
    }

    public String getUrl() {
        return url;
    }

    public String getUsername() {
        return username;
    }

    public String getPassword() {
        return password;
    }

    public int getInitialSize() {
        return initialSize;
    }

    public int getMinIdle() {
        return minIdle;
    }

    public int getMaxActive() {
        return maxActive;
    }

    public int getMaxWait() {
        return maxWait;
    }

    public int getTimeBetweenEvictionRunsMillis() {
        return timeBetweenEvictionRunsMillis;
    }

    public int getMinEvictableIdleTimeMillis() {
        return minEvictableIdleTimeMillis;
    }

    public String getValidationQuery() {
        return validationQuery;
    }

    public boolean isTestWhileIdle() {
        return testWhileIdle;
    }

    public boolean isTestOnBorrow() {
        return testOnBorrow;
    }

    public boolean isTestOnReturn() {
        return testOnReturn;
    }

    public boolean isPoolPreparedStatements() {
        return poolPreparedStatements;
    }

    public int getMaxOpenPreparedStatements() {
        return maxOpenPreparedStatements;
    }

    public String getFilters() {
        return filters;
    }

    public String getConnectionProperties() {
        return connectionProperties;
    }
}

2.初始化DruidDataSource，创建连接：

import com.alibaba.druid.pool.DruidDataSource;
import com.example.demo.model.dto.DruidInfoDto;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.Primary;

import javax.sql.DataSource;
import java.sql.SQLException;

@Configuration
public class DruidDataSourceConfig {

    @Autowired
    private DruidInfoDto druidInfoDto;

    @Bean
    @Primary    //@Primary可以不加
    public DataSource dataSource() {
        DruidDataSource datasource = new DruidDataSource();
        datasource.setDriverClassName(druidInfoDto.getDriver());
        datasource.setUrl(druidInfoDto.getUrl());
        datasource.setUsername(druidInfoDto.getUsername());
        datasource.setPassword(druidInfoDto.getPassword());   //这里可以做加密处理
        //configuration
        datasource.setInitialSize(druidInfoDto.getInitialSize());
        datasource.setMinIdle(druidInfoDto.getMinIdle());
        datasource.setMaxActive(druidInfoDto.getMaxActive());
        datasource.setMaxWait(druidInfoDto.getMaxWait());
        
datasource.setTimeBetweenEvictionRunsMillis(druidInfoDto.getTimeBetweenEvictionRunsMillis());
        datasource.setMinEvictableIdleTimeMillis(druidInfoDto.getMinEvictableIdleTimeMillis());
        datasource.setValidationQuery(druidInfoDto.getValidationQuery());
        datasource.setTestWhileIdle(druidInfoDto.isTestWhileIdle());
        datasource.setTestOnBorrow(druidInfoDto.isTestOnBorrow());
        datasource.setTestOnReturn(druidInfoDto.isTestOnReturn());
        datasource.setPoolPreparedStatements(druidInfoDto.isPoolPreparedStatements());
        datasource.setMaxPoolPreparedStatementPerConnectionSize(druidInfoDto.getMaxOpenPreparedStatements());
        try {
            datasource.setFilters(druidInfoDto.getFilters());
        } catch (SQLException e) {

        }
        datasource.setConnectionProperties(druidInfoDto.getConnectionProperties());

        return datasource;
    }
}

再来看看我们的application启动类：

import org.mybatis.spring.annotation.MapperScan;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.EnableAspectJAutoProxy;
import org.springframework.web.filter.ShallowEtagHeaderFilter;

@MapperScan("com.example.demo.dao") // 配置扫描dao的目录
@EnableAspectJAutoProxy    //没用到AOP切面的可以删除
@SpringBootApplication
public class DemoApplication {

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

}

到这里我们已经完成了Springboot与Mybatis、Druid的集成，接着我们再来集成日志（slf4j和logback）。

jar包我们已经在pom文件引入，现在我们只需要在resources目录下创建logback.xml文件，内容如下：

<?xml version="1.0" encoding="UTF-8"?>
<configuration debug="false">

    <!--定义日志文件的存储地址 勿在 LogBack 的配置中使用相对路径-->
    <property name="LOG_HOME" value="/home" />
    <property name="PATTERN" value="%d{yyyy-MM-dd HH:mm:ss.SSS} [%thread] %-5level %logger{50} - %msg%n" />

    <!--控制台日志， 控制台输出 -->
    <appender name="CONSOLE" class="ch.qos.logback.core.ConsoleAppender">
        <encoder class="ch.qos.logback.classic.encoder.PatternLayoutEncoder">
            <!--格式化输出：%d表示日期，%thread表示线程名，%-5level：级别从左显示5个字符宽度,%msg：日志消息，%n是换行符-->
            <pattern>${PATTERN}</pattern>
            <charset>UTF-8</charset>
        </encoder>
    </appender>

    <!--文件日志， 按照每天生成日志文件 -->
    <appender name="FILE" class="ch.qos.logback.core.rolling.RollingFileAppender">
        <rollingPolicy class="ch.qos.logback.core.rolling.TimeBasedRollingPolicy">
            <!--日志文件输出的文件名-->
            <FileNamePattern>${LOG_HOME}/demo.log.%d{yyyy-MM-dd}.log</FileNamePattern>
            <!--日志文件保留天数-->
            <MaxHistory>30</MaxHistory>
        </rollingPolicy>
        <encoder class="ch.qos.logback.classic.encoder.PatternLayoutEncoder">
            <!--格式化输出：%d表示日期，%thread表示线程名，%-5level：级别从左显示5个字符宽度%msg：日志消息，%n是换行符-->
            <pattern>${PATTERN}</pattern>
            <charset>UTF-8</charset>
        </encoder>
        <!--日志文件最大的大小-->
        <triggeringPolicy class="ch.qos.logback.core.rolling.SizeBasedTriggeringPolicy">
            <MaxFileSize>10MB</MaxFileSize>
        </triggeringPolicy>
    </appender>

    <!--
        在log4j jdbc中定义了以下五个日志对象：
        　　jdbc.sqlonly: 记录系统执行过的sql语句
        　　jdbc.sqltiming: 记录sql执行的时间，可以分析耗时的sql语句
        　　jdbc.audit: 记录除了ResultSet外的所有JDBC调用情况。一般不需要。
        　　jdbc.resultset: 记录返回结果集信息
        　　jdbc.connection: 记录数据库连接和释放信息，可记录当前的数据库连接数，便于诊断连接是否释放。
    -->
    <logger name="org.hibernate" level="WARN" />
    <logger name="org.aspectj" level="WARN" />
    <logger name="org.springframework" level="WARN" />
    <logger name="jdbc.sqltiming" level="DEBUG" />
    <!--myibatis log configure-->
    <logger name="com.apache.ibatis" level="TRACE"/>
    <logger name="java.sql.Connection" level="DEBUG"/>
    <logger name="java.sql.Statement" level="DEBUG"/>
    <!-- java.sql.PreparedStatement这一句至关重要如果没有，就无法输出sql语句 -->
    <logger name="java.sql.PreparedStatement" level="DEBUG"/>
    <logger name="java.sql.ResultSet" level="DEBUG" />

    <springProfile name="dev">
        <!-- 日志输出级别 -->
        <root level="DEBUG">
            <appender-ref ref="FILE" />
            <appender-ref ref="CONSOLE"/>
        </root>
    </springProfile>

    <springProfile name="test">
        <!-- 日志输出级别 -->
        <root level="DEBUG">
            <appender-ref ref="FILE" />
            <appender-ref ref="CONSOLE"/>
        </root>
    </springProfile>

    <springProfile name="pro">
        <!-- 日志输出级别 -->
        <root level="INFO">
            <appender-ref ref="FILE" />
            <appender-ref ref="CONSOLE"/>
        </root>
    </springProfile>
</configuration>

完成了所有的准备工作，我们来创建dao、service、controller层进行测试一下，下面贴出代码：

StudentEntity.java

import java.io.Serializable;

public class StudentEntity implements Serializable {

    // 序列化
    private static final long serialVersionUID = 1L;

    private Integer id;

    private String name;

    private Integer age;

    public Integer getId() {
        return id;
    }

    public void setId(Integer id) {
        this.id = id;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public Integer getAge() {
        return age;
    }

    public void setAge(Integer age) {
        this.age = age;
    }

    @Override
    public String toString() {
        return "StudentEntity{" +
                "id=" + id +
                ", name='" + name + '\'' +
                ", age=" + age +
                '}';
    }
}

StudentDao.java

import com.example.demo.model.entity.StudentEntity;
import org.springframework.stereotype.Repository;

import java.util.List;

@Repository
public interface StudentDao {

    List<StudentEntity> queryStudent();

}

mapper文件：

<?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.example.demo.dao.StudentDao">

    <resultMap id="studentMap" type="com.example.demo.model.entity.StudentEntity">
        <result property="id" column="id" />
        <result property="name" column="name" />
        <result property="age" column="age" />
    </resultMap>

    <select id="queryStudent" resultMap="studentMap">
        SELECT id,name,age
        FROM student
    </select>

</mapper>

StudentService.java

import com.example.demo.model.entity.StudentEntity;

import java.util.List;

public interface StudentService {

    List<StudentEntity> queryStudent();

}

StudentServiceImpl.java

import com.example.demo.dao.StudentDao;
import com.example.demo.model.entity.StudentEntity;
import com.example.demo.service.StudentService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import java.util.List;

@Service
public class StudentServiceImpl implements StudentService {

    @Autowired
    private StudentDao studentDao;


    @Override
    public List<StudentEntity> queryStudent() {
        return studentDao.queryStudent();
    }
}

StudentController.java

import com.example.demo.model.entity.StudentEntity;
import com.example.demo.service.StudentService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;

import java.util.List;

@RestController
@RequestMapping("/student")
public class StudentController {

    @Autowired
    private StudentService studentService;

    @PostMapping("/queryStudent")
    public List<StudentEntity> queryStudent() {
        return studentService.queryStudent();
    }
}

debug启动工程：

我们在VM options配置一下启动参数（开发环境DEV）：-Dspring.profiles.active=dev

步骤如下：

选择Edit Configurations:

弹出界面，点开Environment，如图：

运行工程：

启动成功，端口80

test数据库的student表的数据如下：

我们用postman请求一下接口，看看是否能够正确的返回结果：

返回的数据（屏幕太小，截图不全，贴出返回结果）：

[
    {
        "id": 1,
        "name": " 张三",
        "age": 18
    },
    {
        "id": 2,
        "name": "李五",
        "age": 21
    },
    {
        "id": 3,
        "name": "王二",
        "age": 33
    },
    {
        "id": 4,
        "name": "张无忌",
        "age": 60
    },
    {
        "id": 5,
        "name": "杨过",
        "age": 80
    },
    {
        "id": 6,
        "name": "二蛋",
        "age": 13
    }
]

我们看到数据已经正确的查询出来。

划重点：

    上面集成druid我们并没有真正的发挥springboot的自动化配置；

    springboot的EnableAutoConfiguration注解非常强大，可以自动为我们完成上面DruidDataSourceConfig的配置，无需再自己创建DruidDataSourceConfig类，配置信息如下：

    1.POM文件修改：

<!-- druid -->
<!--<dependency>
	<groupId>com.alibaba</groupId>
	<artifactId>druid</artifactId>
	<version>1.1.19</version>
</dependency>-->

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

    仅仅是把druid换成了druid-spring-boot-starter，其他不变；

    2.application配置文件修改：

​
## springboot 自动加载集成druid连接池：druid-spring-boot-starter
spring.datasource.driver-class-name=com.mysql.jdbc.Driver
spring.datasource.url=jdbc:mysql://127.0.0.1:3306/test
spring.datasource.username=root
spring.datasource.password=123456
## spring集成druid连接池配置
spring.datasource.druid.initial-size=5
spring.datasource.druid.min-idle=10
spring.datasource.druid.on-fatal-error-max-active=30
spring.datasource.druid.test-while-idle=true
spring.datasource.druid.max-active=60000
spring.datasource.druid.async-init=true
spring.datasource.druid.filters=stat,wall,slf4j

​

    3.删除上面创建的DruidInfoDto和DruidDataSourceConfig类。

打完收工！

喜欢的朋友帮忙点个赞、加关注，非常感谢！