MySql之C3P0连接池_c3p0刷新mysql连接池-CSDN博客

本文链接：https://blog.csdn.net/FloatDreamed/article/details/98540876

之前的学习使用需要操作数据库时,就会去生成一个数据库连接对象

这种方式效率很低.同数据库之间的连接是建立在TCP上的

TCP就不用了讲了,三次握手本就很慢了,当你操作数据库时再去获取连接自然效率更低

为此,连接池出现了(这里讲的是C3P0)!

连接池技术的目的：解决建立数据库连接耗费资源和时间很多的问题，提高性能 !

下面以案例演示下C3P0的操作流程.

一.案例展示

1.案例背景:

两个账户之间的转账操作如何保证转账人扣款和收款人的入账流程正常?

目标:

①不会出现转账人不扣款,收款人收款成功
②不会出现转账人已扣款,收款人未入账

***涉及到事务

2.测试准备:

①MySql数据库一枚
②database名为mysqlstudy且表名为account
③项目工程

3.代码展示

***pom.xml

<properties>
    <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
    <maven.compiler.source>1.8</maven.compiler.source>
    <maven.compiler.target>1.8</maven.compiler.target>
  </properties>

  <dependencies>
    <!--数据库驱动-->
    <dependency>
      <groupId>mysql</groupId>
      <artifactId>mysql-connector-java</artifactId>
      <version>5.1.46</version>
    </dependency>
    <!--C3P0连接池-->
    <dependency>
      <groupId>c3p0</groupId>
      <artifactId>c3p0</artifactId>
      <version>0.9.1.2</version>
    </dependency>
    <!--单元测试-->
    <dependency>
      <groupId>junit</groupId>
      <artifactId>junit</artifactId>
      <version>4.12</version>
    </dependency>
    <!--servlet-->
    <dependency>
      <groupId>javax.servlet</groupId>
      <artifactId>javax.servlet-api</artifactId>
      <version>3.1.0</version>
    </dependency>
    <!--dbutils-->
    <dependency>
      <groupId>commons-dbutils</groupId>
      <artifactId>commons-dbutils</artifactId>
        <version>1.7</version>
    </dependency>
  </dependencies>

***index.jsp

<%--
  Created by IntelliJ IDEA.
  User: WHW
  Date: 2019/8/5
  Time: 23:09
  To change this template use File | Settings | File Templates.
--%>
<%@ page contentType="text/html;charset=UTF-8" language="java" %>
<html>
<head>
    <title>转账事务测试</title>
</head>
<body>
    <fieldset>
        <form action="/as" method="post">
            转账人账户名:<input type="text" name="fromName"><br/>
            收款人账户名:<input type="text" name="toName"><br/>
            转账金额:<input type="text" name="money"><br/>
            <input type="submit" value="确认转账">
        </form>
    </fieldset>
</body>
</html>

***c3p0-config.xml

<?xml version="1.0" encoding="UTF-8"?>
<c3p0-config>
  <default-config>
	<property name="driverClass">com.mysql.jdbc.Driver</property>
	<property name="jdbcUrl">jdbc:mysql://localhost:3306/mysqlstudy?characterEncoding=utf8</property>
	<property name="user">root</property>
	<property name="password">root</property>
    <property name="initialPoolSize">10</property>
  </default-config>
</c3p0-config>

***TransferConstant.java

package com.howie.constant;

/**
 * @Author weihuanwen
 * @Date 2019/8/5 23:23
 * @Version 1.0
 */
public class TransferConstant {
    public static final String OK = "Transfer success!";
    public static final String FAIL = "Transfer failed!";
}

***AccountDao.java

package com.howie.dao;

import com.howie.pojo.Trade;
import com.howie.utils.C3P0Utils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.ScalarHandler;

import java.sql.SQLException;

/**
 * @Author weihuanwen
 * @Date 2019/8/5 23:30
 * @Version 1.0
 */
public class AccountDao {

    private QueryRunner qr = new QueryRunner();

    /**
     * 校验转账人账户余额
     * @param trade
     * @return
     * @throws SQLException
     */
    public boolean verifyMoney(Trade trade) throws SQLException {
        String sql = "SELECT balance FROM `account` WHERE `name`=?";
        Double balance = (Double) qr.query(
                C3P0Utils.getConnection(),
                sql,
                new ScalarHandler(),
                trade.getFromName());
        return balance != null && balance >= trade.getMoney();
    }

    /**
     * 出账逻辑
     * @param trade
     * @return
     * @throws SQLException
     */
    public boolean ChargeOff(Trade trade) throws SQLException {
        if (verifyMoney(trade)){
            String sql = "UPDATE `account` SET `balance`=`balance`-? WHERE `name`=?";
            int row = qr.update(
                    C3P0Utils.getConnection(),
                    sql,
                    trade.getMoney(),
                    trade.getFromName());
            return row > 0;
        }
        return false;
    }

    public boolean entry(Trade trade) throws SQLException {
        String sql = "UPDATE `account` SET `balance`=`balance`+? WHERE `name`=?";
        int row = qr.update(
                C3P0Utils.getConnection(),
                sql,
                trade.getMoney(),
                trade.getToName());
        return row > 0;
    }
}

***Trade.java

package com.howie.pojo;

/**
 * @Author weihuanwen
 * @Date 2019/8/5 23:17
 * @Version 1.0
 */
public class Trade {

    private String fromName;
    private String toName;
    private Double money;

    public String getFromName() {
        return fromName;
    }

    public void setFromName(String fromName) {
        this.fromName = fromName;
    }

    public String getToName() {
        return toName;
    }

    public void setToName(String toName) {
        this.toName = toName;
    }

    public Double getMoney() {
        return money;
    }

    public void setMoney(Double money) {
        this.money = money;
    }

    @Override
    public String toString() {
        return "Trade{" +
                "fromName='" + fromName + '\'' +
                ", toName='" + toName + '\'' +
                ", money=" + money +
                '}';
    }
}

***AccountService.java

package com.howie.service;

import com.howie.dao.AccountDao;
import com.howie.pojo.Trade;
import com.howie.utils.C3P0Utils;
import org.apache.commons.dbutils.DbUtils;

import java.sql.Connection;
import java.sql.SQLException;

/**
 * @Author weihuanwen
 * @Date 2019/8/5 22:53
 * @Version 1.0
 */
public class AccountService {

    private AccountDao accountDao = new AccountDao();

    public boolean transferAccount(Trade trade){
        Connection conn = C3P0Utils.getConnection();
        try {
            //1.关闭事务自动提交,开启事务手动提交
            conn.setAutoCommit(false);
            boolean chargeOff = accountDao.ChargeOff(trade);
            if (chargeOff){
                boolean entry = accountDao.entry(trade);
                if (entry){
                    //2.手动提交事务
                    conn.commit();
                    return true;
                }
            }
            //3.转账为执行成功,进行回滚
            conn.rollback();
        } catch (SQLException e) {
            e.printStackTrace();
            //4.转账期间抛出异常,进行回滚
            try {
                conn.rollback();
            } catch (SQLException e1) {
                e1.printStackTrace();
            }
        }finally {
            //5.将连接对象归还连接池
            DbUtils.closeQuietly(conn);
        }
        return false;
    }
}

***AccountServlet.java

package com.howie.servlet;

import com.howie.constant.TransferConstant;
import com.howie.pojo.Trade;
import com.howie.service.AccountService;

import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.util.Map;

/**
 * @Author weihuanwen
 * @Date 2019/8/5 23:10
 * @Version 1.0
 */
@WebServlet("/as")
public class AccountServlet extends HttpServlet {

    private AccountService accountService = new AccountService();

    @Override
    protected void doGet(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
        Map<String, String[]> parameterMap = req.getParameterMap();
        Trade trade = new Trade();
        for (String fieldName : parameterMap.keySet()) {
            if ("fromName".equals(fieldName)){
                trade.setFromName(parameterMap.get(fieldName)[0]);
            }
            if ("toName".equals(fieldName)){
                trade.setToName(parameterMap.get(fieldName)[0]);
            }
            if ("money".equals(fieldName)){
                double money = Double.parseDouble(parameterMap.get(fieldName)[0]);
                trade.setMoney(money);
            }
        }
        boolean result = accountService.transferAccount(trade);
        if (result){
            resp.getWriter().write(TransferConstant.OK);
        }else {
            resp.getWriter().write(TransferConstant.FAIL);
        }
    }

    @Override
    protected void doPost(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
        doGet(req,resp);
    }
}

***C3P0Utils.java

package com.howie.utils;

import com.mchange.v2.c3p0.ComboPooledDataSource;
import javax.sql.DataSource;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;


public class C3P0Utils {
	private static ComboPooledDataSource cpds = new ComboPooledDataSource();

	/*
        相比于直接向AccountDao的方法中传入connection对象,
        这里创建一个静态的ThreadLocal更加方便
        ThreadLocal的底层是一个map,存储的是当前线程的名字和当前的connection
        由于map集合是静态的,一开便加载在内存中了,想使用的话便可直接调取使用
        所以说这里可以不用ThreadLocal也行,不用的话就创建一个静态的map集合也一样能达到效果
        只不过ThreadLocal里面已经封装好了这个map,我们用它就好
     */
	private static ThreadLocal<Connection> tl = new ThreadLocal<>();
	public static Connection getConnection() {
		try {
			Connection conn = tl.get();
			if (conn == null) {
				conn = cpds.getConnection();
				tl.set(conn);
			}
			return conn;
		} catch (SQLException e) {
			throw new RuntimeException("获取数据库连接失败!");
		}
	}

	public static DataSource getDataSource() {
		return cpds;
	}

	public static void close(ResultSet rs, Statement stat, Connection conn) {
		if (rs != null) {
			try {
				rs.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		if (stat != null) {
			try {
				stat.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		if (conn != null) {
			try {
				conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}
}

4.测试

①前端页面中输入如图所示