本文源碼：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 {@Overrideprotected 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

總結

以上是生活随笔為你收集整理的JavaEE基础(06)：Servlet整合C3P0数据库连接池的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。