本文源码: GitHub. 点这里 https://github.com/cicadasmile/java-base-parent || GitEE. 点这里 https://gitee.com/cicadasmile/java-base-parent
一, 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
来源: https://www.cnblogs.com/cicada-smile/p/12071198.html