目录
一、DBCP连接池
方式一:
public class Demo01 {
public static DataSource dataSource = null;
static {
//获取DBCP数据库连接池的实现类对象
BasicDataSource basicDataSource = new BasicDataSource();
//设置数据库连接信息
basicDataSource.setDriverClassName("com.mysql.cj.jdbc.Driver");
basicDataSource.setUrl("jdbc:mysql://localhost:3307/j23031?serverTimezone=UTC");
basicDataSource.setUsername("root");
basicDataSource.setPassword("123456");
//设置连接池的初始参数
basicDataSource.setInitialSize(6);
dataSource = basicDataSource;
}
public static void main(String[] args) {
try {
//通过数据源获取数据库连接
Connection connection = dataSource.getConnection();
//创建语句对象
PreparedStatement preparedStatement = connection.prepareStatement("select * from emp e inner join dept d on e.deptno = d.deptno");
//执行sql
ResultSet resultSet = preparedStatement.executeQuery();
while (resultSet.next()){
Emp emp = new Emp(resultSet.getString("ename"),resultSet.getString("job"),resultSet.getInt("mgr"),
resultSet.getString("hiredate"),resultSet.getString("dname"));
System.out.println(emp);
}
//关闭连接,放回数据库连接池
JdbcUtil.closeAll(resultSet,preparedStatement,connection);
} catch (SQLException e) {
e.printStackTrace();
}
}
}
方式二:
//dbcpconfig.properties配置文件
driverClassName=com.mysql.cj.jdbc.Driver
url=jdbc:mysql://localhost:3307/j23031?serverTimezone=UTC
username=root
password=123456
initialSize=5
public class Demo02 {
public static DataSource dataSource = null;
static {
Properties properties = new Properties();
//通过类加载器获取配置文件
InputStream is = new Demo02().getClass().getClassLoader().getResourceAsStream("dbcpconfig.properties");
//加载配置文件
try {
properties.load(is);
dataSource = BasicDataSourceFactory.createDataSource(properties);
} catch (IOException e) {
e.printStackTrace();
} catch (Exception e) {
e.printStackTrace();
}
}
public static void main(String[] args) {
try {
//获取数据库连接
Connection connection = dataSource.getConnection();
//创建语句对象
Statement statement = connection.createStatement();
//执行sql
ResultSet resultSet = statement.executeQuery("select * from emp");
while (resultSet.next()){
System.out.println(resultSet.getString("ename"));
}
//关闭连接,放回连接池
JdbcUtil.closeAll(resultSet,statement,connection);
} catch (SQLException e) {
e.printStackTrace();
}
}
}
二、总结DBCP所用到的类或接口
类或接口
|
描述
|
BasicDataSource
|
创建数据源
|
DataSource
接口
|
数据源
|
BasicDataSourceFactory
|
创建数据源的工厂类
|
properties
|
文件对象
|
方法名
|
所属类或接口
|
描述
|
setDriverClassName()
|
BasicDataSource
|
设置驱动
|
setUrl()
|
BasicDataSource
|
设置连接数据库的路径
|
setUserName()
|
BasicDataSource
|
设置连接数据库的用户名
|
setPassword()
|
BasicDataSource
|
设置数据库的密码
|
setInitialSize(
|
BasicDataSource
|
设置连接池的初始化数量
|
getConnection()
|
DataSource
接口
|
获取数据库连接
|
load()
|
properties
|
加载配置文件
|
createDataSource()
|
BasicDataSourceFactory
|
创建数据源
|
三、C3P0连接池
方式一:
/**
*
* 1.基于C3PQ连接池,实现删除指定学生
*/
public class Demo03 {
public static DataSource dataSource = null;
static {
ComboPooledDataSource comboPooledDataSource = new ComboPooledDataSource();
//设置连接
try {
comboPooledDataSource.setDriverClass("com.mysql.cj.jdbc.Driver");
comboPooledDataSource.setJdbcUrl("jdbc:mysql://localhost:3307/j23031?serverTimezone=UTC");
comboPooledDataSource.setUser("root");
comboPooledDataSource.setPassword("123456");
//设置最大连接数
comboPooledDataSource.setMaxPoolSize(10);
//设置初始连接数
comboPooledDataSource.setInitialPoolSize(5);
dataSource = comboPooledDataSource;
} catch (PropertyVetoException e) {
e.printStackTrace();
}
}
public static void main(String[] args) {
Connection connection = null;
PreparedStatement preparedStatement = null;
//获取数据库连接
try {
connection = dataSource.getConnection();
preparedStatement = connection.prepareStatement("delete from student where id = ?");
//给问好赋值
preparedStatement.setInt(1,5);
//执行sql
int result = preparedStatement.executeUpdate();
if (result>0){
System.out.println("删除成功");
}else {
System.out.println("删除失败");
}
} catch (SQLException e) {
e.printStackTrace();
}finally {
JdbcUtil.closeAll(null,preparedStatement,connection);
}
}
}
方式二:
注意:
xml
的配置文件名字必须为
c3p0-config
<?xml version="1.0" encoding="UTF-8"?>
<c3p0-config>
<default-config>
<property name="driverClass">com.mysql.cj.jdbc.Driver</property>
<property name="jdbcUrl">jdbc:mysql://localhost:3307/j23031?serverTimezone=UTC</property>
<property name="user">root</property>
<property name="password">123456</property>
<property name="maxPoolSize">15</property>
<property name="initialPoolSize">6</property>
</default-config>
<named-config name="test">
<property name="driverClass">com.mysql.cj.jdbc.Driver</property>
<property name="jdbcUrl">jdbc:mysql://localhost:3307/j23031?serverTimezone=UTC</property>
<property name="user">root</property>
<property name="password">123456</property>
<property name="maxPoolSize">10</property>
</named-config>
</c3p0-config>
//基于C3P0,实现数据库连接池的创建,实现修改指定员工信息
public class Demo04 {
public static DataSource dataSource = null;
static {
dataSource = new ComboPooledDataSource("test");
}
public static void main(String[] args) {
Connection connection = null;
PreparedStatement preparedStatement = null;
try {
connection = dataSource.getConnection();
preparedStatement = connection.prepareStatement("update emp set ename = ? where empno = ?");
//给问号赋值
preparedStatement.setString(1,"刘英俊");
preparedStatement.setInt(2,1221);
int result = preparedStatement.executeUpdate();
if (result>0){
System.out.println("修改成功");
}else {
System.out.println("修改失败");
}
} catch (SQLException e) {
e.printStackTrace();
}finally {
JdbcUtil.closeAll(null,preparedStatement,connection);
}
}
}
四、总结C3P0所用到的类或接口
类或接口
|
描述
|
ComboPooledDataSource
(
string configname
)
|
c3p0
的核心类
|
所用的方法
|
描述
|
setDriverClass()
|
设置驱动路径
|
setJdbcUrl()
|
设置
url
|
setUser()
|
设置用户名
|
setPassword()
|
密码
|
setMaxPoolSize()
|
最大连接数
|
setInitialPoolSize()
|
初始化连接数
|
setMinPoolSize()
|
最小连接数
|