JavaEE基础(06):Servlet整合C3P0数据库连接池
2019-12-20 16:03:09来源:博客园 阅读 ()
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
原文链接:https://www.cnblogs.com/cicada-smile/p/12071198.html
如有疑问请与原作者联系
标签:
版权申明:本站文章部分自网络,如有侵权,请联系:west999com@outlook.com
特别注意:本站所有转载文章言论不代表本站观点,本站所提供的摄影照片,插画,设计作品,如需使用,请与原作者联系,版权归原作者所有
下一篇:java嵌套接口
- 04.Java基础语法 2020-06-11
- 1-Java基础回顾整理_01 2020-06-10
- Java基础语法菜鸟教程笔记 2020-06-10
- Java基础复习——类和对象 2020-06-09
- 计算机基础到底是哪些基础?为什么很重要! 2020-06-08
IDC资讯: 主机资讯 注册资讯 托管资讯 vps资讯 网站建设
网站运营: 建站经验 策划盈利 搜索优化 网站推广 免费资源
网络编程: Asp.Net编程 Asp编程 Php编程 Xml编程 Access Mssql Mysql 其它
服务器技术: Web服务器 Ftp服务器 Mail服务器 Dns服务器 安全防护
软件技巧: 其它软件 Word Excel Powerpoint Ghost Vista QQ空间 QQ FlashGet 迅雷
网页制作: FrontPages Dreamweaver Javascript css photoshop fireworks Flash