spring-boot整合mybatis和druid连接池

原创

赶路人儿 2022-06-19 22:02:29 博主文章分类：java ©著作权

©著作权归作者所有：来自51CTO博客作者赶路人儿的原创作品，请联系作者获取转载授权，否则将追究法律责任

这篇文章介绍了spring-boot整合mybatis，文章主要介绍了如何使用spring-boot和mybatis连接mysql数据裤，没有涉及到连接池，但现实中我们都会使用一款数据库连接池作为缓冲，提高性能。接下来我们重点介绍druid连接池。

Spring Boot中默认支持的连接池有dbcp,dbcp2, hikari三种连接池，Druid来自于阿里系的一个开源连接池（地址： https://github.com/alibaba/druid），在连接池之外，还提供了非常优秀的监控功能，它包括三部分：

DruidDriver 代理Driver，能够提供基于Filter－Chain模式的插件体系。
DruidDataSource 高效可管理的数据库连接池。
SQLParser

1、pom.xml文件：

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

<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>
  
  <packaging>war</packaging> 

  <groupId>nc.edu.nuc</groupId>
  <artifactId>Test</artifactId>
  <version>0.0.1-SNAPSHOT</version>

  <name>Test</name>

  <properties>
    <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
    <maven.compiler.source>1.7</maven.compiler.source>
    <maven.compiler.target>1.7</maven.compiler.target>
    
    <mybatis-spring-boot>1.2.0</mybatis-spring-boot>
    <mysql-connector>5.1.39</mysql-connector>
  </properties>

  <parent>
      <groupId>org.springframework.boot</groupId>
      <artifactId>spring-boot-starter-parent</artifactId>
      <version>1.5.5.RELEASE</version>
  </parent>
  
  <dependencies>
    <dependency>
      <groupId>junit</groupId>
      <artifactId>junit</artifactId>
      <scope>test</scope>
    </dependency>
    
    <!-- spring boot -->
    <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-web</artifactId>
        <exclusions>  
            <exclusion>  
                <groupId>org.springframework.boot</groupId>  
                <artifactId>spring-boot-starter-logging</artifactId>  
            </exclusion>  
        </exclusions>  
    </dependency>
     <!-- Spring Boot log4j依赖 -->  
    <dependency>  
        <groupId>org.springframework.boot</groupId>  
        <artifactId>spring-boot-starter-log4j</artifactId>
        <version>1.3.8.RELEASE</version>
    </dependency> 
    <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-test</artifactId>
        <scope>test</scope>
    </dependency>
    <dependency>
      <groupId>org.springframework.boot</groupId>
      <artifactId>spring-boot-starter-tomcat</artifactId>
      <scope>provided</scope>
  </dependency>
  <!-- jsp -->
  <dependency>
     <groupId>org.apache.tomcat.embed</groupId>
      <artifactId>tomcat-embed-jasper</artifactId>
      <scope>provided</scope>
  </dependency>
  <dependency>
      <groupId>javax.servlet</groupId>
      <artifactId>jstl</artifactId>
  </dependency>
    
    <!-- Spring Boot Mybatis 依赖 -->
    <dependency>
        <groupId>org.mybatis.spring.boot</groupId>
        <artifactId>mybatis-spring-boot-starter</artifactId>
        <version>${mybatis-spring-boot}</version>
    </dependency>
    
    <!-- 连接池 -->
    <!-- Druid 数据连接池依赖 -->
    <dependency>
        <groupId>com.alibaba</groupId>
        <artifactId>druid</artifactId>
        <version>1.0.13</version>
    </dependency>
    
     <!-- MySQL 连接驱动依赖 -->
     <dependency>
         <groupId>mysql</groupId>
         <artifactId>mysql-connector-java</artifactId>
     </dependency>
    
  </dependencies>

  <build>
    <finalName>Test</finalName>
    <plugins>
        <!-- <plugin>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-maven-plugin</artifactId>
        </plugin> -->
        <plugin>
        <groupId>org.apache.maven.plugins</groupId>
        <artifactId>maven-war-plugin</artifactId>
        <configuration>
            <failOnMissingWebXml>false</failOnMissingWebXml>
        </configuration>
    </plugin>
    </plugins>
  </build>
</project>

2、配置连接池：

1）application.properties:

##mysql配置
#spring.datasource.url=jdbc:mysql://jy.ttengine.w.abc.db:1883/ttengine?useUnicode=true&characterEncoding=utf8
#spring.datasource.username=ttengine
#spring.datasource.password=TTengine123
#spring.datasource.driver-class-name=com.mysql.jdbc.Driver
## Mybatis配置
#mybatis.typeAliasesPackage=nc.edu.nuc.Test.entity  
#mybatis.mapperLocations=classpath:mapper/*.xml


#---------------------使用durid连接池
ds1.datasource.url=jdbc:mysql://jy.ttengine.w.abc.db:1883/ttengine?useUnicode=true&characterEncoding=utf8
ds1.datasource.username=ttengine
ds1.datasource.password=TTengine123
ds1.datasource.driverClassName=com.mysql.jdbc.Driver

ds1.datasource.initialSize=20
ds1.datasource.minIdle=20
ds1.datasource.maxActive=200
ds1.datasource.maxWait=60000
ds1.datasource.timeBetweenEvictionRunsMillis=60000
ds1.datasource.minEvictableIdleTimeMillis=300000
ds1.datasource.testWhileIdle=true
ds1.datasource.testOnBorrow=false
ds1.datasource.testOnReturn=false
ds1.datasource.poolPreparedStatements=true
ds1.datasource.maxPoolPreparedStatementPerConnectionSize=20

# 页面默认前缀目录
spring.mvc.view.prefix=/WEB-INF/page/
spring.mvc.view.suffix=.jsp

在application.properties中驱动配置信息没有问题，但是spring-boot不支持自动配druid连接池（即无法通过配置项直接支持相应的连接池连接池）；这里列出的这些配置项可以通过定制化DataSource来实现，也可以在定制化的DataSource中写死。

2）定置化DataSource类：

package cn.edu.nuc.Test1.dao.mysql.config;

import java.sql.SQLException;
import javax.sql.DataSource;
import org.apache.ibatis.session.SqlSessionFactory;
import org.mybatis.spring.SqlSessionFactoryBean;
import org.mybatis.spring.annotation.MapperScan;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.Primary;
import org.springframework.core.io.support.PathMatchingResourcePatternResolver;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;
import com.alibaba.druid.pool.DruidDataSource;

@Configuration
//扫描 Mapper 接口并容器管理
@MapperScan(basePackages = DatasourceConfig.PACKAGE, sqlSessionFactoryRef = "sqlSessionFactory")
public class DatasourceConfig {
    // 精确到 master 目录，以便跟其他数据源隔离
    static final String PACKAGE = "cn.edu.nuc.Test1.dao.mysql";
    static final String MAPPER_LOCATION = "classpath:mapper/*.xml";

    @Value("${ds1.datasource.url}")
    private String url;
    @Value("${ds1.datasource.username}")
    private String user;
    @Value("${ds1.datasource.password}")
    private String password;
    @Value("${ds1.datasource.driverClassName}")
    private String driverClass;
    
    @Value("${ds1.datasource.maxActive}")
    private Integer maxActive;
    @Value("${ds1.datasource.minIdle}")
    private Integer minIdle;
    @Value("${ds1.datasource.initialSize}")
    private Integer initialSize;
    @Value("${ds1.datasource.maxWait}")
    private Long maxWait;
    @Value("${ds1.datasource.timeBetweenEvictionRunsMillis}")
    private Long timeBetweenEvictionRunsMillis;
    @Value("${ds1.datasource.minEvictableIdleTimeMillis}")
    private Long minEvictableIdleTimeMillis;
    @Value("${ds1.datasource.testWhileIdle}")
    private Boolean testWhileIdle;
    @Value("${ds1.datasource.testWhileIdle}")
    private Boolean testOnBorrow;
    @Value("${ds1.datasource.testOnBorrow}")
    private Boolean testOnReturn;

    @Bean(name = "dataSource")
    @Primary
    public DataSource dataSource() {
        //jdbc配置
        DruidDataSource dataSource = new DruidDataSource();
        dataSource.setDriverClassName(driverClass);
        dataSource.setUrl(url);
        dataSource.setUsername(user);
        dataSource.setPassword(password);
        
        //连接池配置
        dataSource.setMaxActive(maxActive);
        dataSource.setMinIdle(minIdle);
        dataSource.setInitialSize(initialSize);
        dataSource.setMaxWait(maxWait);
        dataSource.setTimeBetweenEvictionRunsMillis(timeBetweenEvictionRunsMillis);
        dataSource.setMinEvictableIdleTimeMillis(minEvictableIdleTimeMillis);
        dataSource.setTestWhileIdle(testWhileIdle);
        dataSource.setTestOnBorrow(testOnBorrow);
        dataSource.setTestOnReturn(testOnReturn);
        dataSource.setValidationQuery("SELECT 'x'");
        
        dataSource.setPoolPreparedStatements(true);
        dataSource.setMaxPoolPreparedStatementPerConnectionSize(20);
        
        try {
            dataSource.setFilters("stat");
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return dataSource;
    }

    @Bean(name = "transactionManager")
    @Primary
    public DataSourceTransactionManager transactionManager() {
        return new DataSourceTransactionManager(dataSource());
    }

    @Bean(name = "sqlSessionFactory")
    @Primary
    public SqlSessionFactory ds1SqlSessionFactory(@Qualifier("dataSource") DataSource dataSource)
            throws Exception {
        final SqlSessionFactoryBean sessionFactory = new SqlSessionFactoryBean();
        sessionFactory.setDataSource(dataSource);
        sessionFactory.setTypeAliasesPackage("cn.edu.nuc.Test1.entity");
        sessionFactory.setMapperLocations(new PathMatchingResourcePatternResolver()
                .getResources(DatasourceConfig.MAPPER_LOCATION));
        return sessionFactory.getObject();
    }
}

DruidDBConfig类被@Configuration标注，用作配置信息； DataSource对象被@Bean声明，为Spring容器所管理， @Primary表示这里定义的DataSource将覆盖其他来源的DataSource。

3、其他：

上面配置好连接池后，接下来就和正常spring-boot的用法一样了。整个结构：

spring-boot整合mybatis和druid连接池_spring

1）controller：

package cn.edu.nuc.Test1.controller;

import javax.servlet.http.HttpServletRequest;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.ResponseBody;
import cn.edu.nuc.Test1.entity.Test;
import cn.edu.nuc.Test1.service.TestService;

@Controller
public class TestController {
    private static final Logger logger = LoggerFactory.getLogger(TestController.class); 
    
    @Autowired
    private TestService testService;
    
    @RequestMapping("/test")
    @ResponseBody
    public String test(HttpServletRequest request) {
        String name = request.getParameter("name");
        Test test = null;
        try {
            test = testService.findByName(name);
        } catch (Exception e) {
            test = new Test();
            logger.error("test error.",e);
        }
        logger.info("test....{}",name);
        return test.toString();
    }
}

2）service：

package cn.edu.nuc.Test1.service;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import cn.edu.nuc.Test1.dao.mysql.TestDao;
import cn.edu.nuc.Test1.entity.Test;

@Service
public class TestService{
  private static final Logger logger = LoggerFactory.getLogger(TestService.class); 
  
  @Autowired  
  private TestDao testDao;  
  
  public Test findByName(String name) throws Exception{
    return testDao.findByName(name);
  }
}

3）dao层：

package cn.edu.nuc.Test1.dao.mysql;

import org.apache.ibatis.annotations.Param;
import cn.edu.nuc.Test1.entity.Test;

public interface TestDao {
    Test findByName(@Param("name") String n);
}

4）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="cn.edu.nuc.Test1.dao.mysql.TestDao">  
    <resultMap id="BaseResultMap" type="cn.edu.nuc.Test1.entity.Test">  
        <result column="id" property="id" />  
        <result column="name" property="name" />  
    </resultMap>  
  
    <parameterMap id="Test" type="cn.edu.nuc.Test1.entity.Test"/>  
  
    <sql id="Base_Column_List">  
        id,name  
    </sql>  
    <select id="findByName" resultMap="BaseResultMap" parameterType="java.lang.String">  
        select  
        <include refid="Base_Column_List" />  
        from test  
        where name = #{name}  
    </select>  
</mapper>

5）app启动类：

package cn.edu.nuc.Test1;

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


/**
 * Hello world!
 * 可以统一用@MapperScan指定扫描的dao，也可以在每个dao上添加@Mapper
 */
@SpringBootApplication
//mapper 接口类扫描包配置
@MapperScan("cn.edu.nuc.Test1.dao.mysql")
public class App 
{
    public static void main( String[] args )
    {
        SpringApplication.run(App.class, args);
    }
}

总结：

和直接使用mybatis（不用druid连接池）的方式相比，引入druid连接池主要有以下几点差异：

1）由于引入的是druid连接池，spring-boot不是原生支持的，所以需要定置化一个DataSource类，在该类中把application.properties中的jdbc和连接池配置项设置好；

2）如果直接使用mybatis，那么在application.properties中需要设置一下两个信息，springboot会自动读取并加载；

mybatis.typeAliasesPackage=nc.edu.nuc.Test.entity

mybatis.mapperLocations=classpath:mapper/*.xml

由于使用了druid连接池，这两个mybatis的配置需要在Datasource中设置（在application.properties中就不需要指定），所以我们看DataSource的代码中有下面的代码：

sessionFactory.setMapperLocations(new PathMatchingResourcePatternResolver()
                .getResources(DatasourceConfig.MAPPER_LOCATION));

4、druid监控：

在上面的DatasourceConfig.java中添加如下信息

@Bean
    public ServletRegistrationBean druidServlet() {
      ServletRegistrationBean servletRegistrationBean = new ServletRegistrationBean();
      servletRegistrationBean.setServlet(new StatViewServlet());
      servletRegistrationBean.addUrlMappings("/druid/*");
      Map<String, String> initParameters = new HashMap<String, String>();
      initParameters.put("loginUsername", "admin");// 用户名
      initParameters.put("loginPassword", "admin");// 密码
      initParameters.put("resetEnable", "false");// 禁用HTML页面上的“Reset All”功能
      initParameters.put("allow", ""); // IP白名单 (没有配置或者为空，则允许所有访问)
      //initParameters.put("deny", "192.168.20.38");// IP黑名单 (存在共同时，deny优先于allow)
      servletRegistrationBean.setInitParameters(initParameters);
      return servletRegistrationBean;
    }
    
    @Bean
    public FilterRegistrationBean filterRegistrationBean() {
      FilterRegistrationBean filterRegistrationBean = new FilterRegistrationBean();
      filterRegistrationBean.setFilter(new WebStatFilter());
      filterRegistrationBean.addUrlPatterns("/*");
      filterRegistrationBean.addInitParameter("exclusions", "*.js,*.gif,*.jpg,*.png,*.css,*.ico,/druid/*");
      return filterRegistrationBean;
    }