JavaEE基礎(06)：Servlet整合C3P0資料庫連接配接池

本文源碼：GitHub·點這裡 || GitEE·點這裡

一、C3P0連接配接池

1、C3P0簡介

C3P0是一個開源的JDBC連接配接池，應用程式根據C3P0配置來初始化資料庫連接配接，可以自動回收空閑連接配接的功能。

2、核心依賴

<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <version>${mysql.version}</version>
</dependency>
<dependency>
    <groupId>com.mchange</groupId>
    <artifactId>c3p0</artifactId>
    <version>${c3p0.version}</version>
</dependency>
           

3、配置檔案

配置檔案位置：放在

resources

目錄下，這樣C3P0元件會自動加載該配置。

<?xml version="1.0" encoding="UTF-8"?>
<c3p0-config>
    <default-config>
        <!-- 核心參數配置 -->
        <property name="jdbcUrl">jdbc:mysql://localhost:3306/servlet-jdbc</property>
        <property name="driverClass">com.mysql.jdbc.Driver</property>
        <property name="user">root</property>
        <property name="password">123</property>
        <!-- 池參數配置 -->
        <property name="acquireIncrement">3</property>
        <property name="initialPoolSize">10</property>
        <property name="minPoolSize">2</property>
        <property name="maxPoolSize">10</property>
    </default-config>
</c3p0-config>
           

4、編寫工具類

該工具類用來擷取資料庫連接配接，和釋放相關連接配接。

public class C3P0Pool {
    private static DataSource dataSource = new ComboPooledDataSource();
    public static DataSource getDataSource() {
        return dataSource ;
    }
    /**
     * 擷取連接配接
     */
    public static Connection getConnection() throws SQLException {
        return dataSource.getConnection();
    }
    /**
     * 釋放連接配接
     */
    public static void close(ResultSet resultSet, PreparedStatement pst, Connection connection) {
        if (resultSet != null) {
            try {
                resultSet.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if (pst != null) {
            try {
                pst.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if (connection != null) {
            try {
                connection.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
}
           

二、資料操作封裝

1、新增資料

public class UserJdbcInsert {
    public static void insertUser (UserInfo userInfo){
        try {
            Connection connection = C3P0Pool.getConnection();
            String sql = "INSERT INTO user_info (user_name,user_age) VALUES (?,?)" ;
            PreparedStatement statement = connection.prepareStatement(sql);
            statement.setString(1,userInfo.getUserName());
            statement.setString(2,userInfo.getUserAge().toString());
            statement.execute() ;
            C3P0Pool.close(null, statement, connection);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
    public static void batchInsertUser (List<UserInfo> userInfoList){
        try {
            Connection connection = C3P0Pool.getConnection();
            String sql = "INSERT INTO user_info (user_name,user_age) VALUES (?,?)" ;
            PreparedStatement statement = connection.prepareStatement(sql);
            for (UserInfo userInfo:userInfoList){
                statement.setString(1,userInfo.getUserName());
                statement.setString(2,userInfo.getUserAge().toString());
                statement.addBatch();
            }
            statement.executeBatch() ;
            C3P0Pool.close(null, statement, connection);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
}
           

2、查詢資料

public class UserJdbcQuery {
    public static UserInfo queryUser (String userName){
        UserInfo userInfo = null ;
        try {
            Connection connection = C3P0Pool.getConnection();
            String sql = "SELECT * FROM user_info WHERE user_name=?" ;
            PreparedStatement statement = connection.prepareStatement(sql);
            statement.setString(1,userName);
            ResultSet resultSet = statement.executeQuery() ;
            while (resultSet.next()){
                int id = resultSet.getInt("id");
                String name = resultSet.getString("user_name");
                int age = resultSet.getInt("user_age");
                System.out.println("ID:"+id+";name:"+name+";age:"+age);
                userInfo = new UserInfo(name,age) ;
            }
            C3P0Pool.close(resultSet, statement, connection);
        } catch (Exception e) {
            e.printStackTrace();
        }
        return userInfo ;
    }
}
           

3、更新資料

public class UserJdbcUpdate {
    public static void updateUser (String name,Integer age,Integer id){
        try {
            Connection connection = C3P0Pool.getConnection();
            String sql = "UPDATE user_info SET user_name=?,user_age=? WHERE id=?" ;
            PreparedStatement statement = connection.prepareStatement(sql);
            statement.setString(1,name);
            statement.setInt(2,age);
            statement.setInt(3,id);
            statement.executeUpdate() ;
            C3P0Pool.close(null, statement, connection);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
}
           

4、删除資料

public class UserJdbcDelete {
    public static void deleteUser (Integer id){
        try {
            Connection connection = C3P0Pool.getConnection();
            String sql = "DELETE FROM user_info WHERE id=?" ;
            PreparedStatement statement = connection.prepareStatement(sql);
            statement.setInt(1,id);
            statement.executeUpdate() ;
            C3P0Pool.close(null, statement, connection);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
}
           

三、Servlet接口

public class JdbcServletImpl extends HttpServlet {
    @Override
    protected void doGet(HttpServletRequest request, HttpServletResponse response)
            throws ServletException, IOException {
        String userName = request.getParameter("userName") ;
        UserInfo userInfo = UserJdbcQuery.queryUser(userName) ;
        response.setContentType("text/html;charset=utf-8");
        response.getWriter().print("使用者資訊:"+userInfo);
    }
}
           

測試通路：

http://localhost:6003/jdbcServletImpl?userName=LiSi

頁面列印：

使用者資訊:

UserInfo{userName='LiSi', userAge=22}

四、源代碼位址

GitHub·位址
https://github.com/cicadasmile/java-base-parent
GitEE·位址
https://gitee.com/cicadasmile/java-base-parent