• 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
    

  • 相关阅读:
    男人只说三分话、留的七分打天下。
    sqlmap实例拿站
    sqlmap使用笔记
    rpm安装删除简介
    Zookeeper技术介绍
    linux下各文件夹的结构说明及用途介绍:
    每个系统管理员都要知道的 30 个 Linux 系统监控工具
    常用命令
    安装gitlab管理自己的代码
    速成Git
  • 原文地址:https://www.cnblogs.com/cicada-smile/p/12071198.html
Copyright © 2020-2023  润新知