JavaEE基础(06):Servlet整合C3P0数据库连接池
一、C3P0连接池1、C3P0简介本文源码:GitHub·点这里 || GitEE·点这里
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-parentGitEE·地址https://gitee.com/cicadasmile/java-base-parent
声明:本站所有文章资源内容,如无特殊说明或标注,均为采集网络资源。如若本站内容侵犯了原著者的合法权益,可联系本站删除。