0x01 介绍

C3P0是一个开源的JDBC连接池,它实现了数据源与JNDI绑定,支持JDBC3规范和实现了JDBC2的标准扩展说明的Connection和Statement池的DataSources对象

即将用于连接数据库的连接整合在一起形成一个随取随用的数据库连接池（Connection pool）

0x02 环境搭配

如果还不会搭建jsp环境的可以按照下面的文章跟着搭建

Mac版IDEA创建maven web项目-详细过程: https://www.yuque.com/pmiaowu/gpy1q8/npv0fr
JSP Servlet实例: https://www.yuque.com/pmiaowu/gpy1q8/egyodf

0x03 导入相关的包

按照上面的搭建完环境以后,就找到pom.xml找到<dependencies>标签
然后如下使用maven自动添加包

<!-- https://mvnrepository.com/artifact/mysql/mysql-connector-java -->
<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <version>5.1.41</version>
</dependency>
<!-- https://mvnrepository.com/artifact/com.mchange/c3p0 -->
<dependency>
    <groupId>com.mchange</groupId>
    <artifactId>c3p0</artifactId>
    <version>0.9.2</version>
</dependency>

0x04 C3P0连接池

# 目录结构
├── src
│   └── main
│     └── webapp
│       └── com
│         ├── Filter
│         │ └── ...
│         └── Servlet
│           ├── ...
│           ├── C3P0Demo1.java
│           ├── C3P0Demo2.java
│           └── C3P0Demo3.java
│       └── WEB-INF
│         └── web.xml
│       └── index.jsp
│   └── c3p0-config.xml

我这里就连接MySQL数据库进行演示了

0x04.1 没有配置文件的使用方法

Servlet目录创建C3P0Demo1.java

// 文件名字: C3P0Demo1.java
package com.Servlet;
import com.mchange.v2.c3p0.ComboPooledDataSource;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.beans.PropertyVetoException;
import java.io.IOException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
@WebServlet("/C3P0Demo1")
public class C3P0Demo1 extends HttpServlet {
    @Override
    protected void doGet(HttpServletRequest request, HttpServletResponse response) throws IOException {
        String DRIVER_NAME = "com.mysql.jdbc.Driver";
        String URL = "jdbc:mysql://192.168.24.145:3306/mysql";
        String USER_NAME = "root";
        String PASSWORD = "123456";
        Connection conn = null;
        PreparedStatement ps = null;
        ComboPooledDataSource dataSource = new ComboPooledDataSource();
        try {
            dataSource.setDriverClass(DRIVER_NAME);
            dataSource.setJdbcUrl(URL);
            dataSource.setUser(USER_NAME);
            dataSource.setPassword(PASSWORD);
            conn = dataSource.getConnection();
            String sql = "SELECT * FROM `user`";
            ps = conn.prepareStatement(sql);
            ResultSet rs = ps.executeQuery();
            response.getWriter().println(" ");
            response.getWriter().println("C3P0Demo1.java");
            while (rs.next()) {
                response.getWriter().println(" ");
                response.getWriter().println("用户名:" + rs.getString("User") + "   " + "密码:" + rs.getString("Password"));
            }
        } catch (PropertyVetoException | SQLException e) {
            e.printStackTrace();
        } finally {
            //释放stmt
            if (ps != null) {
                try {
                    ps.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
    }
}
// 执行该文件
// 例如: http://127.0.0.1:8081/mavenJspTest_war/C3P0Demo1

0x04.2 有配置文件的使用方法一

默认配置法,适用于单一的数据库连接使用

在src这个目录下的同级,记住一定是要在src这个目录下,创建一个c3p0-config.xml

// 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://192.168.24.145:3306/mysql</property>
        <property name="user">root</property>
        <property name="password">123456</property>
    </default-config>
</c3p0-config>

然后在Servlet目录创建C3P0Demo2.java

// 文件名字: C3P0Demo2.java
package com.Servlet;
import com.mchange.v2.c3p0.ComboPooledDataSource;
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.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
@WebServlet("/C3P0Demo2")
public class C3P0Demo2 extends HttpServlet {
    @Override
    protected void doGet(HttpServletRequest request, HttpServletResponse response) throws IOException {
        Connection conn = null;
        PreparedStatement ps = null;
        ComboPooledDataSource dataSource = new ComboPooledDataSource();
        try {
            conn = dataSource.getConnection();
            String sql = "SELECT * FROM `user`";
            ps = conn.prepareStatement(sql);
            ResultSet rs = ps.executeQuery();
            response.getWriter().println(" ");
            response.getWriter().println("C3P0Demo2.java");
            while (rs.next()) {
                response.getWriter().println(" ");
                response.getWriter().println("用户名:" + rs.getString("User") + "   " + "密码:" + rs.getString("Password"));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            //释放stmt
            if (ps != null) {
                try {
                    ps.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
    }
}
// 执行该文件
// 例如: http://127.0.0.1:8081/mavenJspTest_war/C3P0Demo2

0x04.3 有配置文件的使用方法二

可能我们需要连接多个数据库那么这个时候,该方法就可以使用上了
例如我们新增加一个名字叫c3p0-mysql-test的连接池

在src这个目录下的同级,记住一定是要在src这个目录下,创建一个c3p0-config.xml

// c3p0-config.xml的内容
<?xml version="1.0" encoding="UTF-8"?>
<c3p0-config>
    <named-config name="c3p0-mysql-test">
        <property name="driverClass">com.mysql.jdbc.Driver</property>
        <property name="jdbcUrl">jdbc:mysql://192.168.24.145:3306/mysql</property>
        <property name="user">root</property>
        <property name="password">123456</property>
    </named-config>
</c3p0-config>

然后在Servlet目录创建C3P0Demo3.java

// 文件名字: C3P0Demo3.java
// 主要是ComboPooledDataSource里面带连接池名字即可
package com.Servlet;
import com.mchange.v2.c3p0.ComboPooledDataSource;
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.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
@WebServlet("/C3P0Demo3")
public class C3P0Demo3 extends HttpServlet {
    @Override
    protected void doGet(HttpServletRequest request, HttpServletResponse response) throws IOException {
        Connection conn = null;
        PreparedStatement ps = null;
        ComboPooledDataSource dataSource = new ComboPooledDataSource("c3p0-mysql-test");
        try {
            conn = dataSource.getConnection();
            String sql = "SELECT * FROM `user`";
            ps = conn.prepareStatement(sql);
            ResultSet rs = ps.executeQuery();
            response.getWriter().println(" ");
            response.getWriter().println("C3P0Demo3.java");
            while (rs.next()) {
                response.getWriter().println(" ");
                response.getWriter().println("用户名:" + rs.getString("User") + "   " + "密码:" + rs.getString("Password"));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            //释放stmt
            if (ps != null) {
                try {
                    ps.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
    }
}
// 执行该文件
// 例如: http://127.0.0.1:8081/mavenJspTest_war/C3P0Demo3

0x05 小结

在实战中,如果对方使用了c3p0作为数据源
那么知道去src这个目录下,查找c3p0-config.xml获取数据库账号密码即可

Java 安全漫游记

2. JSP C3P0 DataSource使用