数据库连接池（二）c3p0

最新推荐文章于 2020-06-03 18:40:39 发布

w_t_y_y

最新推荐文章于 2020-06-03 18:40:39 发布

阅读量217

点赞数

分类专栏： ORM框架Mybatis

本文链接：https://blog.csdn.net/w_t_y_y/article/details/105865962

版权

ORM框架Mybatis 专栏收录该内容

35 篇文章 6 订阅

订阅专栏

一、demo：

1、pom：

<?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>

    <groupId>com.demo</groupId>
    <artifactId>c3p0-demo</artifactId>
    <version>1.0-SNAPSHOT</version>

    <!-- springBoot -->
    <parent>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-parent</artifactId>
        <version>1.4.1.RELEASE</version>
    </parent>
    <dependencies>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>

        <!-- mybatis -->
        <dependency>
            <groupId>org.mybatis.spring.boot</groupId>
            <artifactId>mybatis-spring-boot-starter</artifactId>
            <version>1.1.1</version>
        </dependency>
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>5.1.21</version>
        </dependency>

        <!--c3p0-->
        <dependency>
            <groupId>c3p0</groupId>
            <artifactId>c3p0</artifactId>
            <version>0.9.1.2</version>
        </dependency>

    </dependencies>

    
</project>

2、application.properties：基本上涵盖了c3p0常用的所有配置，按需可删减这些配置

#配置c3p0的数据池
#配置数据库名
my.pool.jdbcUrl=jdbc:mysql://localhost:3306/pool?useUnicode=true&characterEncoding=utf8&autoReconnect=true&failOverReadOnly=false
#配置username
my.pool.user=root
#配置password
my.pool.password=wtyy
#配置driver
my.pool.driverClass=com.mysql.jdbc.Driver
#配置最小连接池数据
my.pool.minPoolSize=2
#连接池中保留的最大连接数。Default: 15
my.pool.maxPoolSize=5
#最大空闲时间,60秒内未使用则连接被丢弃。若为0则永不丢弃。Default: 0
my.pool.maxIdleTime=0
#当连接池中的连接耗尽的时候c3p0一次同时获取的连接数,每次增加3个连接
my.pool.acquireIncrement=3
#JDBC的标准参数，用以控制数据源内加载的PreparedStatements数量。但由于预缓存的statements,属于单个connection而不是整个连接池。所以设置这个参数需要考虑到多方面的因素。如果maxStatements与maxStatementsPerConnection均为0，则缓存被关闭。Default: 0-
my.pool.maxStatements=1000
#连接池内单个连接所拥有的最大缓存statements数。Default: 0
my.pool.maxStatementsPerConnection=1000
#c3p0是异步操作的，缓慢的JDBC操作通过帮助进程完成。扩展这些操作可以有效的提升性能,通过多线程实现多个操作同时被执行。Default: 3
my.pool.numHelperThreads=3
#初始化连接池数,初始化时获取三个连接，取值应在minPoolSize与maxPoolSize之间。Default: 3
my.pool.initialPoolSize=3
#每60秒检查所有连接池中的空闲连接。Default: 0
my.pool.idleConnectionTestPeriod=60
#定义在从数据库获取新连接失败后重复尝试的次数。Default: 30
my.pool.acquireRetryAttempts=30
#两次连接中间隔时间，单位毫秒。Default: 1000
my.pool.acquireRetryDelay=1000
#因性能消耗大请只在需要的时候使用它。如果设为true那么在每个connection提交的,时候都将校验其有效性。建议使用idleConnectionTestPeriod或automaticTestTable等方法来提升连接测试的性能。Default: false
my.pool.testConnectionOnCheckout=false
#如果设为true那么在取得连接的同时将校验连接的有效性。Default: false
my.pool.testConnectionOnCheckin=true
#连接关闭时默认将所有未提交的操作回滚，Default: false
my.pool.autoCommitOnClose = false
#-c3p0将建一张名为Test的空表，并使用其自带的查询语句进行测试。如果定义了这个参数那么,属性preferredTestQuery将被忽略。你不能在这张Test表上进行任何操作，它将只供c3p0测试使用。Default: null
my.pool.automaticTestTable = c3p0_test
#获取连接失败将会引起所有等待连接池来获取连接的线程抛出异常。但是数据源仍有效保留，并在下次调用getConnection()的时候继续尝试获取连接。如果设为true，那么在尝试获取连接失败后该数据源将申明已断开并永久关闭。Default: false
my.pool.breakAfterAcquireFailure = false
#当连接池用完时客户端调用getConnection()后等待获取新连接的时间，超时后将抛出SQLException,如设为0则无限期等待。单位毫秒。Default: 0
my.pool.checkoutTimeout = 100


#mybatis  SQL目录
mybatis.mapper-locations=classpath*:Mapper/*Mapper.xml

server.port=7777

3、c3p0数据库配置类：

package com.demo.config;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.boot.SpringBootConfiguration;
import org.springframework.boot.autoconfigure.jdbc.DataSourceBuilder;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.Primary;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;

import javax.sql.DataSource;

@Configuration
public class DataSourceConfig {
    //指定当前对象作为bean
    @Bean(name = "dataSource")
    //指定dataSource来DI
    //@Qualifier(value = "dataSource")
    //primary将当前数据库连接池作为默认数据库连接池
   // @Primary
    //在application.properties文件中增加前缀c3p0
    @ConfigurationProperties(prefix = "my.pool")
    public DataSource dataSource(){
        return DataSourceBuilder.create().type(com.mchange.v2.c3p0.ComboPooledDataSource.class).build();
    }

}

4、cotroller：

package com.demo.controller;

import com.demo.dto.PoolDTO;
import com.demo.service.PoolService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;

import java.util.List;

@RestController
@RequestMapping("/pool")
public class PoolController {

    @Autowired
    private PoolService poolService;

    /**
     * 回滚测试
     * @param a
     * @return
     */
    @RequestMapping("/getAll")
    public List<PoolDTO> getAll(Integer a){
        return poolService.getAll(a);
    }


    /**
     * 返回自增长的id
     * @return
     */
    @RequestMapping("/getId")
    public Integer getId(){
        return poolService.getId();
    }

    /**
     * 返回自增长的id集合
     * @return
     */
    @RequestMapping("/getIds")
    public List<PoolDTO> getIds() {
        return poolService.getIds();
    }
}

5、service：

package com.demo.service.impl;

import com.demo.dao.PoolDTOMapper;
import com.demo.dto.PoolDTO;
import com.demo.service.PoolService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import java.util.ArrayList;
import java.util.List;

@Service("poolService")
public class PoolServiceImpl implements PoolService {

    @Autowired
    private PoolDTOMapper poolDTOMapper;

    @Override
    @Transactional
    public List<PoolDTO> getAll(Integer a) {
        PoolDTO poolDTO = new PoolDTO();
        poolDTO.setPoolName("前");
        poolDTOMapper.insert(poolDTO);
        int b = 1/a;
        poolDTO.setPoolName("后");
        poolDTOMapper.insert(poolDTO);
        return poolDTOMapper.getAll();
    }

    @Override
    public Integer getId() {
        PoolDTO poolDTO = new PoolDTO();
        poolDTO.setPoolName("获取id");
        poolDTOMapper.insertAndGetId(poolDTO);
        return poolDTO.getId();
    }

    @Override
    public List<PoolDTO> getIds() {
        List<PoolDTO> list = new ArrayList<>();
        PoolDTO poolDTO = new PoolDTO();
        poolDTO.setPoolName("获取id1");
        list.add(poolDTO);
        PoolDTO poolDTO1 = new PoolDTO();
        poolDTO1.setPoolName("获取id2");
        list.add(poolDTO1);
        poolDTOMapper.batchInsert(list);
        for(PoolDTO pool : list){
            pool.setId(pool.getId());
        }
        return list;
    }
}

6、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.demo.dao.PoolDTOMapper">
  <resultMap id="BaseResultMap" type="com.demo.dto.PoolDTO">
    <id column="id" jdbcType="INTEGER" property="id" />
    <result column="pool_name" jdbcType="VARCHAR" property="poolName" />
  </resultMap>
  <sql id="Base_Column_List">
    id, pool_name
  </sql>
  <select id="getAll"  resultMap="BaseResultMap">
    select 
    <include refid="Base_Column_List" />
    from t_pool
  </select>
  <delete id="deleteByPrimaryKey" parameterType="java.lang.Integer">
    delete from t_pool
    where id = #{id,jdbcType=INTEGER}
  </delete>
  <insert id="insert" parameterType="com.demo.dto.PoolDTO">
    insert into t_pool (pool_name)
    values ( #{poolName,jdbcType=VARCHAR})
  </insert>
 <insert id="insertAndGetId" parameterType="com.demo.dto.PoolDTO" useGeneratedKeys="true" keyProperty="id">
   insert into t_pool (pool_name)
   values ( #{poolName,jdbcType=VARCHAR})
 </insert>

  <insert id="batchInsert" useGeneratedKeys="true" keyProperty="id">
    insert into t_pool (pool_name)
    values
    <foreach collection="list" item="item"  separator=",">
     ( #{item.poolName })
    </foreach>
  </insert>
</mapper>

7、启动类：

package com.demo;

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

@SpringBootApplication
@MapperScan("com.demo.dao")
public class PoolApplication {
    public static void main(String args[]){
        SpringApplication.run(PoolApplication.class,args);
    }
}