DB数据源之SpringBoot+MyBatis踏坑过程（七）手动使用Tomcat连接池

 

liuyuhang原创，未经允许禁止转载 

 

系列目录连接

DB数据源之SpringBoot+Mybatis踏坑过程实录（一）

 

1.环境说明

 

　　springboot2.0以下版本，java7，myeclipse2017 C1，使用的是mySql数据库

　　

　　pom

  1 <project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
  2     xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
  3     <modelVersion>4.0.0</modelVersion>
  4     <groupId>Tit</groupId>
  5     <artifactId>FM</artifactId>
  6     <version>0.0.1-SNAPSHOT</version>
  7     <packaging>war</packaging>
  8 
  9     <parent>
 10         <groupId>org.springframework.boot</groupId>
 11         <artifactId>spring-boot-starter-parent</artifactId>
 12         <version>1.5.3.RELEASE</version>
 13         <relativePath /> <!-- lookup parent from repository -->
 14     </parent>
 15     <dependencies>
 16 
 17         <!-- spring boot web -->
 18         <dependency>
 19             <groupId>org.springframework.boot</groupId>
 20             <artifactId>spring-boot-starter-web</artifactId>
 21         </dependency>
 22 
 23         <dependency>
 24             <groupId>junit</groupId>
 25             <artifactId>junit</artifactId>
 26             <version>3.8.1</version>
 27             <scope>test</scope>
 28         </dependency>
 29 
 30         <dependency>
 31             <groupId>org.apache.tomcat.embed</groupId>
 32             <artifactId>tomcat-embed-logging-juli</artifactId>
 33             <version>7.0.62</version>
 34         </dependency>
 35 
 36         <!-- 添加MySQL依赖 -->
 37         <dependency>
 38             <groupId>mysql</groupId>
 39             <artifactId>mysql-connector-java</artifactId>
 40         </dependency>
 41         <!-- 添加JDBC依赖 -->
 42         <dependency>
 43             <groupId>org.springframework.boot</groupId>
 44             <artifactId>spring-boot-starter-jdbc</artifactId>
 45         </dependency>
 46         <!-- mybaits基础依赖 -->
 47         <dependency>
 48             <groupId>org.mybatis</groupId>
 49             <artifactId>mybatis</artifactId>
 50             <version>3.4.0</version>
 51         </dependency>
 52         <!-- mybatis插件依赖 -->
 53         <dependency>
 54             <groupId>org.mybatis.spring.boot</groupId>
 55             <artifactId>mybatis-spring-boot-starter</artifactId>
 56             <version>1.1.1</version>
 57         </dependency>
 58         <!-- mapper依赖 -->
 59         <dependency>
 60             <groupId>tk.mybatis</groupId>
 61             <artifactId>mapper</artifactId>
 62             <version>3.3.7</version>
 63         </dependency>
 64 
 65 
 66         <dependency>
 67             <groupId>org.springframework.boot</groupId>
 68             <artifactId>spring-boot-configuration-processor</artifactId>
 69             <optional>true</optional>
 70         </dependency>
 71 
 72         <!-- 热部署 -->
 73         <dependency>
 74             <groupId>org.springframework.boot</groupId>
 75             <artifactId>spring-boot-devtools</artifactId>
 76             <optional>true</optional>
 77             <scope>true</scope>
 78         </dependency>
 79         <!-- end of 热部署 -->
 80     </dependencies>
 81 
 82     <properties>
 83         <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
 84         <project.reporting.outputEncoding>UTF-8</project.reporting.outputEncoding>
 85         <java.version>1.7</java.version>
 86 
 87         <webVersion>3.0</webVersion>
 88     </properties>
 89     <build>
 90         <finalName>FM</finalName>
 91         <plugins>
 92             <plugin>
 93                 <groupId>org.springframework.boot</groupId>
 94                 <artifactId>spring-boot-maven-plugin </artifactId>
 95             </plugin>
122         </plugins>
143 
144     </build>
145 </project>

 

2.配置思路

 

  • 　　确保类单例，使用构造器实例化的sqlSessionFactory只设置一次
  • 　　提供getSqlSessionFactory获取sqlSessionFactory
  • 　　setSqlSessionFactory时初始化数据源，并设置连接池
  • 　　setSqlSessionFactory方法提供参数可对数据源进行更改，以确保数据源故障时可进行重新设置

 

3.所需类与结构

 

　　3.1.pom，略

　　3.2.DataConfig.java配置数据源获取SqlSessionFactory类

　　3.3.mapper.xml，略

　　3.4.HelloExample.java测试，略

　　3.5.AppRun.java，Springboot启动类，略

 

4.代码

 

　　DataConfig.java代码如下：

 

  1 package com.FM.config;
  2 
  3 import org.apache.ibatis.session.SqlSessionFactory;
  4 import org.apache.tomcat.jdbc.pool.DataSource;
  5 import org.apache.tomcat.jdbc.pool.PoolProperties;
  6 import org.mybatis.spring.SqlSessionFactoryBean;
  7 import org.springframework.context.annotation.Configuration;
  8 import org.springframework.core.io.DefaultResourceLoader;
  9 import org.springframework.core.io.Resource;
 10 import org.springframework.core.io.support.PathMatchingResourcePatternResolver;
 11 /**
 12  * 单例数据源配置
 13  * @author Liuyuhang
 14  *
 15  */
 16 @Configuration
 17 public class DataConfig {
 18 
 19     private static final String url = "jdbc:mysql://xxx.xxx.xxx.xxx:3306/dataBaseName?cuseUnicode=true&characterEncoding=utf-8&useSSL=false";
 20     private static final String driver = "com.mysql.jdbc.Driver";
 21     private static final String username = "root";
 22     private static final String password = "root";
 23 
 24     /**
 25      * SqlSessionFactory
 26      */
 27     private SqlSessionFactory sqlSessionFactory = null;
 28 
 29     /**
 30      * 单例的类对象
 31      */
 32     private static volatile DataConfig dataConfig;
 33     
 34     /**
 35      * 无参构造
 36      * @throws Exception
 37      */
 38     public DataConfig() throws Exception {
 39         setSqlSessionFactory();
 40         System.out.println("DataConfig init");
 41     }
 42     
 43     /**
 44      * 双验证单例模式
 45      * @return
 46      * @throws Exception
 47      */
 48     public static DataConfig getInstence() throws Exception{
 49         if(null==dataConfig){
 50             synchronized (DataConfig.class) {
 51                 if(null==dataConfig){
 52                     dataConfig = new DataConfig();
 53                     
 54                 }
 55             }
 56         }
 57         return dataConfig;
 58     }
 59 
 60     /**
 61      * tomcat pool配置
 62      * @param url
 63      * @param dirver
 64      * @param username
 65      * @param password
 66      * @return
 67      */
 68     public DataSource dataSource(String url, String dirver, String username, String password) {
 69         PoolProperties p = new PoolProperties();
 70         p.setUrl(url);
 71         p.setDriverClassName(dirver);
 72         p.setUsername(username);
 73         p.setPassword(password);
 74         p.setTestWhileIdle(false);
 75         p.setTestOnBorrow(true);
 76         p.setValidationQuery("SELECT 1");
 77         p.setTestOnReturn(false);
 78         p.setValidationInterval(30000);
 79         p.setTimeBetweenEvictionRunsMillis(30000);
 80         p.setMaxActive(100);
 81         p.setInitialSize(10);
 82         p.setMaxWait(10000);
 83         p.setRemoveAbandonedTimeout(60);
 84         p.setMinEvictableIdleTimeMillis(30000);
 85         p.setMinIdle(10);
 86         p.setLogAbandoned(true);
 87         p.setRemoveAbandoned(true);
 88         DataSource datasource = new DataSource();
 89         datasource.setPoolProperties(p);
 90         return datasource;
 91     }
 92 
 93     /**
 94      * setSqlSessionFactory,构造的时候运行一次,可实例化以后也可手动调用修改SqlSessionFactory
 95      * @throws Exception
 96      */
 97     public void setSqlSessionFactory() throws Exception {
 98         DataSource dataSource = dataSource(url, driver, username, password);
 99         // 创建sessionFactory
100         SqlSessionFactoryBean factoryBean = new SqlSessionFactoryBean();
101         factoryBean.setDataSource(dataSource);// 加载数据源
102         // 扫描mapper.xml
103         Resource[] resources = new PathMatchingResourcePatternResolver().getResources("classpath:com/FM/mapper/*.xml");
104         factoryBean.setMapperLocations(resources);
105         // 读取config
106         factoryBean.setConfigLocation(new DefaultResourceLoader().getResource("classpath:mybatis-config.xml"));
107         this.sqlSessionFactory = factoryBean.getObject();
108         System.out.println("setSqlSessionFactory init");
109     }
110 
111     /**
112      * 获取SqlSessionFactory
113      * @return
114      * @throws Exception
115      */
116     public SqlSessionFactory getSessionFactory() throws Exception {
117         if (null == sqlSessionFactory) {
118             setSqlSessionFactory();
119         }
120         return this.sqlSessionFactory;
121     }
122 
123 }

 

5.说明

  • 　　在不使用连接池情况下，直接加载数据源时，会导致mysql数据库开启连接数量持续增长到最大值，导致mysql数据库无法使用

 

6.测试

 

　　测试时应观察mysql连接数量增长情况，总数量，对数据库进行多次请求。

 

 

以上!