spring-boot整合mybatis和druid连接池

谁借莪１个温暖的怀抱￠ 2021-05-13 16:05 534阅读 0赞

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>
        
        
        <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>
           
        <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>
    	
    	<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>
        
        
        <dependency>
            <groupId>org.mybatis.spring.boot</groupId>
            <artifactId>mybatis-spring-boot-starter</artifactId>
            <version>${mybatis-spring-boot}</version>
        </dependency>
        
        
        
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid</artifactId>
            <version>1.0.13</version>
        </dependency>
        
         
         <dependency>
             <groupId>mysql</groupId>
             <artifactId>mysql-connector-java</artifactId>
         </dependency>
        
      </dependencies>
    
      <build>
      	<finalName>Test</finalName>
      	<plugins>
            
            <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的用法一样了。整个结构：

![20180525205435946][]

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;
        }

重启服务器，然后输入：http://127.0.0.1:8080/druid

![20180525211618656][]

[20180525205435946]: /images/20210511/ae1a3f7713d440ed818929b36b158e22.png
[20180525211618656]: /images/20210511/9580d8209f394675a47a07d23f2ac584.png