1, C3P0 connection pool

1. C3P0 introduction

C3P0 is an open source JDBC connection pool. The application program initializes the database connection according to the C3P0 configuration, and can automatically reclaim the function of idle connection.

2. Core dependency

<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. Profile

Configuration file location: put it in the resources directory, so that the C3P0 component will automatically load the configuration.

<?xml version="1.0" encoding="UTF-8"?>
<c3p0-config>
    <default-config>
        <!-- Core parameter configuration -->
        <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>
        <!-- Pool parameter configuration -->
        <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. Write tool class

This utility class is used to obtain database connection and release related connection.

public class C3P0Pool {
    private static DataSource dataSource = new ComboPooledDataSource();
    public static DataSource getDataSource() {
        return dataSource ;
    }
    /**
     * Get connection
     */
    public static Connection getConnection() throws SQLException {
        return dataSource.getConnection();
    }
    /**
     * Release connection
     */
    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();
            }
        }
    }
}

2, Data operation encapsulation

1. New data

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. Query data

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. Update data

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. Delete data

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();
        }
    }
}

3, Servlet interface

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("User information:"+userInfo);
    }
}

Test access:

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

Page printing:

User information: UserInfo{userName='LiSi', userAge=22}

4, Source code address

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