本文详细介绍了如何通过JDBC使用C3P0、DBCP和Druid数据库连接池进行数据库操作。分别展示了创建配置文件、导入依赖、创建JavaBean和数据库访问类的步骤，并对比了三种连接池的优缺点。Druid以其高效性能、监控能力和扩展性脱颖而出。
通过JDBC使用DBCP与C3P0连接池操作
1.通过JDBC使用C3P0连接池
C3P0是一个开源的JDBC连接池，它实现了数据源和JNDI绑定，支持JDBC3规范和JDBC2的标准扩展。目前使用它的开源项目有Hibernate、Spring等。
例如：
create table t_user(
user_id int primary key auto_increment,
user_name varchar(20),
user_age int,
user_sex bit,
user_address varchar(30),
user_day datetime
);
2.创建Java项目
3.导入依赖包
c3p0-0.9.5.2.jar
mchange-commons-java-0.2.15.jar
mysql-connector-java-5.1.38.jar
4.在ser下创建c3p0的配置文件【c3p0-config.xml】
<?xml version="1.0" encoding="UTF-8"?>
<c3p0-config>
<!-- c3p0的默认配置项 -->
<default-config>
<property name="driverClass">com.mysql.jdbc.Driver</property>
<property name="jdbcUrl">jdbc:mysql://localhost:3306/test</property>
<property name="user">root</property>
<property name="password">123456</property>
<property name="initialPoolSize">5</property>
<property name="maxPoolSize">20</property>
</default-config>
<!-- 定义的数据库配置 -->
<named-config name="test">
<property name="driverClass">com.mysql.jdbc.Driver</property>
<property name="jdbcUrl">jdbc:mysql://localhost:3306/test</property>
<property name="user">root</property>
<property name="password">123456</property>
<property name="initialPoolSize">25</property>
<property name="maxPoolSize">200</property>
</named-config>
</c3p0-config>
5.创建javabean
package com.wangxing.bean;
/**
* 保存用户信息的java实体类
* @author Administrator
*
*/
import java.sql.Date;
public class UserBean {
private int userid;
private String username;
private int userage;
private boolean usersex;
private String useraddress;
private Date userday;
public int getUserid() {
return userid;
}
public void setUserid(int userid) {
this.userid = userid;
}
public String getUsername() {
return username;
}
public void setUsername(String username) {
this.username = username;
}
public int getUserage() {
return userage;
}
public void setUserage(int userage) {
this.userage = userage;
}
public boolean isUsersex() {
return usersex;
}
public void setUsersex(boolean usersex) {
this.usersex = usersex;
}
public String getUseraddress() {
return useraddress;
}
public void setUseraddress(String useraddress) {
this.useraddress = useraddress;
}
public Date getUserday() {
return userday;
}
public void setUserday(Date userday) {
this.userday = userday;
}
}
6.创建数据库访问类
package com.wamgxing.dbacsess;
import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import com.mchange.v2.c3p0.ComboPooledDataSource;
import com.wangxing.bean.UserBean;
public class UserAcsess {
//定义保存c3p0数据源对象
private ComboPooledDataSource dataSource=null;
//定义数据库连接对象
private Connection conn=null;
//定义PreparedStatement
private PreparedStatement ps=null;
public UserAcsess() {
//加载c3p0-config.xml文件中默认的config
//初始化数据源
//dataSource =new ComboPooledDataSource();
//初始化数据源,加载的是自定义的数据库表配置项
dataSource =new ComboPooledDataSource("test");
//初始化数据源
try {
conn=dataSource.getConnection();
} catch (SQLException e) {
e.printStackTrace();
}
}
public void insertUser(UserBean userbean) {
try {
String insertsql="insert into t_user values(null,?,?,?,?,?);";
ps=conn.prepareStatement(insertsql);
ps.setString(1,userbean.getUsername());
ps.setInt(2,userbean.getUserage());
ps.setBoolean(3,userbean.isUsersex());
ps.setString(4,userbean.getUseraddress());
ps.setDate(5,new Date(System.currentTimeMillis()));
int temp=ps.executeUpdate();
if(temp>0) System.out.println("添加成功");
}catch(Exception e) {
e.printStackTrace();
}finally {
try {
ps.close();
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
public void updateUser(UserBean userbean) {
try {
String insertsql="update t_user set user_name=?,"
+ "user_age=?,user_sex=?,user_address=?,user_day=?"
+ " where user_id=?";
ps=conn.prepareStatement(insertsql);
ps.setString(1,userbean.getUsername());
ps.setInt(2,userbean.getUserage());
ps.setBoolean(3,userbean.isUsersex());
ps.setString(4,userbean.getUseraddress());
ps.setDate(5,new Date(System.currentTimeMillis()));
ps.setInt(6,userbean.getUserid());
int temp=ps.executeUpdate();
if(temp>0) System.out.println("修改成功");
}catch(Exception e) {
e.printStackTrace();
}finally {
try {
ps.close();
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
public void deleteUser(int userid) {
try {
String insertsql="delete from t_user where user_id=?";
ps=conn.prepareStatement(insertsql);
ps.setInt(1,userid);
int temp=ps.executeUpdate();
if(temp>0) System.out.println("删除成功");
}catch(Exception e) {
e.printStackTrace();
}finally {
try {
ps.close();
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
public void selectUser() {
try {
String selectsql="select* from t_user;";
ps=conn.prepareStatement(selectsql);
ResultSet temp=ps.executeQuery();
while(temp.next()) {
int userid=temp.getInt("user_id");
String username=temp.getString("user_name");
int userage=temp.getInt("user_age");
boolean usersex=temp.getBoolean("user_sex");
String useraddress=temp.getString("user_address");
Date userday=temp.getDate("user_day");
System.out.println(userid+"\t"+username);
}
}catch(Exception e) {
e.printStackTrace();
}finally {
try {
ps.close();
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
优点:不用手动编写创建数据库连接对象的操作，因为c3p0的数据库连接池的配置中已经配置初始化成功。
提供程序的执行效率。
日后切换数据库的时候不需要修改源代码，只需要修改c3p0的数据库连接池的配置。
2.通过JDBC使用dbcp连接池
数据库连接池负责分配、管理和释放数据库连接，它允许应用程序重复使用一个现有的数据库连接，而不是再重新建立一个；释放空闲时间超过最大空闲时间的数据库连接来避免因为没有释放数据库连接而引起的数据库连接遗漏。这项技术能明显提高对数据库操作的性能。
实例:
create table t_user(
user_id int primary key auto_increment,
user_name varchar(20),
user_age int,
user_sex bit,
user_address varchar(30),
user_day datetime
);
1.创建java项目
2.导入jar包
【commons-dbcp-1.4.jar
commons-dbutils-1.6.jar
commons-pool-1.6.jar】
3.在src下创建数据库连接配置文件【xxxxxxxx.properties】
driverClassName=com.mysql.jdbc.Driver
url=jdbc:mysql://192.168.71.1:3306/my_db?useUnicode=true&characterEncoding=utf8
username=root
password=123456
maxActive=50
maxIdle=20
maxWait=60000
4创建javabean
package com.wangxing.dcbp;
import java.sql.Date;
public class UserBean {
private int userid;
private String username;
private int userage;
private boolean usersex;
private String useraddress;
private Date userday;
public int getUserid() {
return userid;
}
public void setUserid(int userid) {
this.userid = userid;
}
public String getUsername() {
return username;
}
public void setUsername(String username) {
this.username = username;
}
public int getUserage() {
return userage;
}
public void setUserage(int userage) {
this.userage = userage;
}
public boolean isUsersex() {
return usersex;
}
public void setUsersex(boolean usersex) {
this.usersex = usersex;
}
public String getUseraddress() {
return useraddress;
}
public void setUseraddress(String useraddress) {
this.useraddress = useraddress;
}
public Date getUserday() {
return userday;
}
public void setUserday(Date userday) {
this.userday = userday;
}
}
5.创建数据库访问类
package com.wangxing.dbacesess;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Properties;
import javax.sql.DataSource;
import org.apache.commons.dbcp.BasicDataSourceFactory;
import com.wangxing.dcbp.UserBean;
public class UserAscess {
private Connection conn = null;
private PreparedStatement ps = null;
public UserAscess() {
try {
Properties pro = new Properties();
InputStream inStream = this.getClass().getResourceAsStream("/dbcp.properties");
pro.load(inStream);
DataSource ds = BasicDataSourceFactory.createDataSource(pro);
conn = ds.getConnection();
} catch (Exception e) {
e.printStackTrace();
}
}
public void insertUser(UserBean userbean) {
String insertsql = "insert into t_user values(null,?,?,?,?,?);";
try {
ps = conn.prepareStatement(insertsql);
ps.setString(1, userbean.getUsername());
ps.setInt(2, userbean.getUserage());
ps.setBoolean(3, userbean.isUsersex());
ps.setString(4, userbean.getUseraddress());
ps.setDate(5, new Date(System.currentTimeMillis()));
int temp = ps.executeUpdate();
if (temp > 0)
System.out.println("添加成功");
} catch (Exception e) {
e.printStackTrace();
} finally {
try {
ps.close();
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
public void updateUser(UserBean userbean) {
try {
String updatesql = "update t_user set user_name=?," + "user_age=?,user_sex=?,user_address=?,user_day=?"
+ "where user_id=?";
ps = conn.prepareStatement(updatesql);
ps.setString(1, userbean.getUsername());
ps.setInt(2, userbean.getUserage());
ps.setBoolean(3, userbean.isUsersex());
ps.setString(4, userbean.getUseraddress());
ps.setDate(5, new Date(System.currentTimeMillis()));
ps.setInt(6, userbean.getUserid());
int temp = ps.executeUpdate();
if (temp > 0)
System.out.println("修改成功");
} catch (Exception e) {
e.printStackTrace();
} finally {
try {
ps.close();
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
public void deleteUser(int userid) {
try {
String deletesql = "delete from t_user where user_id=?";
ps = conn.prepareStatement(deletesql);
ps.setInt(1, userid);
int temp = ps.executeUpdate();
if (temp > 0)
System.out.println("删除成功");
} catch (Exception e) {
e.printStackTrace();
} finally {
try {
ps.close();
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
public void selectUser() {
try {
String selectsql = "select * from t_user;";
ps = conn.prepareStatement(selectsql);
ResultSet temp = ps.executeQuery();
while (temp.next()) {
int userid = temp.getInt("user_id");
String username = temp.getString("user_name");
int userage = temp.getInt("user_age");
boolean usersex = temp.getBoolean("user_sex");
String useraddress = temp.getString("user_address");
Date userday = temp.getDate("user_day");
System.out.println(userid + "\t" + username);
}
} catch (Exception e) {
e.printStackTrace();
} finally {
try {
ps.close();
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
/*
* 根据userid查询用户信息
*/
public UserBean selectUserByid(int id) {
UserBean userbean = null;
try {
String selectsql = "select * from t_user where user_id=?;";
ps = conn.prepareStatement(selectsql);
ps.setInt(1, id);
ResultSet temp = ps.executeQuery();
if (temp.next()) {
int idc = temp.getInt("user_id");
String username = temp.getString("user_name");
int userage = temp.getInt("user_age");
boolean usersex = temp.getBoolean("user_sex");
String useraddress = temp.getString("user_address");
Date userday = temp.getDate("user_day");
userbean = new UserBean();
userbean.setUserid(idc);
userbean.setUsername(username);
userbean.setUseraddress(useraddress);
userbean.setUserage(userage);
System.out.println(id + "\t" + username + "\t" + userage);
}
} catch (Exception e) {
e.printStackTrace();
} finally {
try {
ps.close();
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
return userbean;
}
}
主类
package com.wangxing.test;
import com.wangxing.dbacesess.UserAscess;
import com.wangxing.dcbp.UserBean;
public class Test {
public static void main(String[] args) {
UserBean userbean=new UserBean();
userbean.setUsername("lsi");
userbean.setUserage(56);
userbean.setUsersex(true);
userbean.setUseraddress("北京");
new UserAscess().insertUser(userbean);
/*UserBean userbean=new UserBean();
userbean.setUserid(1);
userbean.setUsername("zhangsan");
userbean.setUserage(23);
userbean.setUsersex(true);
userbean.setUseraddress("北京");
new UserAscess().updateUser(userbean);
*/
//new UserAscess().deleteUser(1);
//new UserAscess().selectUser();
//userbean=new UserAscess().selectUserByid(2);
}
}
3.JDBC访问Druid
Druid首先是一个数据库连接池。Druid是目前最好的数据库连接池，在功能、性能、扩展性方面，都超过其他数据库连接池，包括DBCP、C3P0、BoneCP、Proxool、JBoss DataSource。Druid已经在阿里巴巴部署了超过600个应用，经过一年多生产环境大规模部署的严苛考验。Druid是阿里巴巴开发的号称为监控而生的数据库连接池！
同时Druid不仅仅是一个数据库连接池，它包括四个部分：
Druid是一个JDBC组件，它包括三个部分：
基于Filter－Chain模式的插件体系。
DruidDataSource 高效可管理的数据库连接池。
SQLParser
Druid的功能
1、替换DBCP和C3P0。Druid提供了一个高效、功能强大、可扩展性好的数据库连接池。
2、可以监控数据库访问性能，Druid内置提供了一个功能强大的StatFilter插件，能够详细统计SQL的执行性能，这对于线上分析数据库访问性能有帮助。
3、数据库密码加密。直接把数据库密码写在配置文件中，这是不好的行为，容易导致安全问题。DruidDruiver和DruidDataSource都支持PasswordCallback。
4、SQL执行日志，Druid提供了不同的LogFilter，能够支持Common-Logging、Log4j和JdkLog，你可以按需要选择相应的LogFilter，监控你应用的数据库访问情况。
5、扩展JDBC，如果你要对JDBC层有编程的需求，可以通过Druid提供的Filter机制，很方便编写JDBC层的扩展插件。
所以Druid可以：
1、充当数据库连接池。
2、可以监控数据库访问性能
3、获得SQL执行日志
例如：
create table t_user(
user_id int primary key auto_increment,
user_name varchar(20),
user_age int,
user_sex bit,
user_address varchar(30),
user_day datetime
);
1.创建java项目
2.导入jar包【druid-1.1.10.jar】
3.在src下创建数据库连接配置文件【xxxxxxxx.properties】
driverClassName=com.mysql.jdbc.Driver
url=jdbc:mysql://192.168.71.1:3306/my_db?useUnicode=true&characterEncoding=UTF-8
username=root
password=123456
initialSize=100
maxActive=300
maxWait=60000
Druid配置详解
https://blog.youkuaiyun.com/zhangjinwei417/article/details/92823438
**4.创建javabean
package com.wangxing.druid.bean;
import java.sql.Date;
public class UserBean {
private int user_id;
private String user_name;
private int user_age;
private boolean user_sex;
private String user_address;
private Date user_day;
public int getUser_id() {
return user_id;
}
public void setUser_id(int user_id) {
this.user_id = user_id;
}
public String getUser_name() {
return user_name;
}
public void setUser_name(String user_name) {
this.user_name = user_name;
}
public int getUser_age() {
return user_age;
}
public void setUser_age(int user_age) {
this.user_age = user_age;
}
public boolean isUser_sex() {
return user_sex;
}
public void setUser_sex(boolean user_sex) {
this.user_sex = user_sex;
}
public String getUser_address() {
return user_address;
}
public void setUser_address(String user_address) {
this.user_address = user_address;
}
public Date getUser_day() {
return user_day;
}
public void setUser_day(Date user_day) {
this.user_day = user_day;
}
}
5.创建数据库访问类**
package com.wangxing.dbacess;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Properties;
import javax.sql.DataSource;
import com.alibaba.druid.pool.DruidDataSourceFactory;
import com.wangxing.druid.bean.UserBean;
public class UserAscess {
private Connection conn = null;
private PreparedStatement ps = null;
public UserAscess() {
try {
Properties pro = new Properties();
InputStream instream = this.getClass().getResourceAsStream("/druid.properties");
pro.load(instream);
DataSource ds = DruidDataSourceFactory.createDataSource(pro);
conn = ds.getConnection();
} catch (Exception e) {
e.printStackTrace();
}
}
public void insertUser(UserBean userbean) {
try {
String insertsql = "insert into t_user values(null,?,?,?,?,?);";
ps = conn.prepareStatement(insertsql);
ps.setString(1, userbean.getUser_name());
ps.setInt(2, userbean.getUser_age());
ps.setBoolean(3, userbean.isUser_sex());
ps.setString(4, userbean.getUser_address());
ps.setDate(5, new Date(System.currentTimeMillis()));
int temp = ps.executeUpdate();
if (temp > 0)
System.out.println("添加成功");
} catch (Exception e) {
e.printStackTrace();
} finally {
try {
ps.close();
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
public void updateUser(UserBean userbean) {
try {
String update = "update t_user set user_name=?," + "user_age=?,user_sex=?,user_address=?,user_day=?"
+ "where user_id=?";
ps = conn.prepareStatement(update);
ps.setString(1, userbean.getUser_name());
ps.setInt(2, userbean.getUser_age());
ps.setBoolean(3, userbean.isUser_sex());
ps.setString(4, userbean.getUser_address());
ps.setDate(5, new Date(System.currentTimeMillis()));
ps.setInt(6, userbean.getUser_id());
int temp = ps.executeUpdate();
if (temp > 0)
System.out.println("修改成功");
} catch (Exception e) {
e.printStackTrace();
} finally {
try {
ps.close();
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
public void deleteUser(int userid) {
try {
String deletesql = "delete from t_user where user_id=?";
ps = conn.prepareStatement(deletesql);
ps.setInt(1, userid);
int temp = ps.executeUpdate();
if (temp > 0)
System.out.println("删除成功");
} catch (Exception e) {
e.printStackTrace();
} finally {
try {
ps.close();
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
public void selectUser() {
try {
String selectsql = "select*from t_user";
ps = conn.prepareStatement(selectsql);
ResultSet temp = ps.executeQuery();
while (temp.next()) {
int userid = temp.getInt("user_id");
String username = temp.getString("user_name");
int userage = temp.getInt("user_age");
boolean usersex = temp.getBoolean("user_sex");
String useraddress = temp.getString("user_address");
Date userday = temp.getDate("user_day");
System.out.println(userid + "\t" + username);
}
} catch (Exception e) {
e.printStackTrace();
} finally {
try {
ps.close();
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
public UserBean selectUserByid(int id) {
UserBean userbean = null;
try {
String selectsql = "select*from t_user where user_id=?;";
ps = conn.prepareStatement(selectsql);
ps.setInt(1, id);
ResultSet temp = ps.executeQuery();
while (temp.next()) {
int userid = temp.getInt("user_id");
String username = temp.getString("user_name");
int userage = temp.getInt("user_age");
boolean usersex = temp.getBoolean("user_sex");
String useraddress = temp.getString("user_address");
Date userday = temp.getDate("user_day");
userbean = new UserBean();
userbean.getUser_id();
userbean.getUser_name();
userbean.getUser_address();
System.out.println(id + "\t" + username + "\t" + useraddress);
}
} catch (Exception e) {
e.printStackTrace();
} finally {
try {
ps.close();
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
return userbean;
}
}
主类
package com.wangxing.test;
import com.wangxing.dbacess.UserAscess;
import com.wangxing.druid.bean.UserBean;
public class Test {
public static void main(String[] args) {
UserBean userbean=new UserBean();
/*
userbean.setUser_name("lisi");
userbean.setUser_age(23);
userbean.setUser_sex(false);
userbean.setUser_address("xian");
new UserAscess().insertUser(userbean);*/
/* userbean.setUser_id(5);
userbean.setUser_name("zhangsanfeng");
userbean.setUser_age(26);
userbean.setUser_sex(false);
userbean.setUser_address("shanxi");
new UserAscess().updateUser(userbean);
*/
// new UserAscess().deleteUser(4);
//new UserAscess().selectUser();
new UserAscess().selectUserByid(6);
}
}
扫一扫
340
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
