紧接上一篇数据库连接池的学习,点连接直达~ 数据库连接池的简单理解与使用
数据库连接池DBCP代码连接与配置文件:
1.先导入使用的jar文件,分别是dbcp.jar与pool.jar文件
2.分别使用两种方式实现,使用配置文件(dbcpconfig.properties)的方式与不使用配置文件的方式。
TestDbcp.java
package dbcp;
import java.io.FileInputStream;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.Properties;
import javax.sql.DataSource;
import org.apache.commons.dbcp.BasicDataSource;
import org.apache.commons.dbcp.BasicDataSourceFactory;
import org.junit.Test;
import util.JDBCUtil;
public class TestDbcp {
@Test
public void Test1(){
Connection conn=null;
PreparedStatement prepareStatement = null;
try {
//1.构建数据源对象
BasicDataSource bds=new BasicDataSource();
//连接的数据库,数据库类型,用户名,密码
bds.setDriverClassName("com.mysql.jdbc.Driver");
bds.setUrl("jdbc:mysql://localhost/stum");
bds.setUsername("root");
bds.setPassword("123");
//2.得到连接对象
conn=bds.getConnection();
String sql="insert into account values(null,?,?)";
prepareStatement = conn.prepareStatement(sql);
prepareStatement.setString(1, "zzz");
prepareStatement.setInt(2, 5000);
prepareStatement.executeUpdate();
} catch (SQLException e) {
e.printStackTrace();
}finally{
JDBCUtil.release(conn, prepareStatement);
}
}
@Test
public void test2(){
Connection conn=null;
PreparedStatement prepareStatement = null;
try {
//1.构建数据源对象
BasicDataSourceFactory bdsf=new BasicDataSourceFactory();
Properties properties=new Properties();
InputStream is=new FileInputStream("src//dbcpconfig.properties");
properties.load(is);
DataSource ds=bdsf.createDataSource(properties);
//2.得到连接对象
conn=ds.getConnection();
String sql="insert into account values(null,?,?)";
prepareStatement = conn.prepareStatement(sql);
prepareStatement.setString(1, "hhh");
prepareStatement.setInt(2, 2000);
prepareStatement.executeUpdate();
} catch (Exception e) {
e.printStackTrace();
}finally{
JDBCUtil.release(conn, prepareStatement);
}
}
}
配置文件:dbcpconfig.properties
#连接设置
driverClassName=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/stum
username=root
password=123
#<!-- 初始化连接 -->
initialSize=10
#最大连接数量
maxActive=50
#<!-- 最大空闲连接 -->
maxIdle=20
#<!-- 最小空闲连接 -->
minIdle=5
#<!-- 超时等待时间以毫秒为单位 6000毫秒/1000等于60秒 -->
maxWait=60000
#JDBC驱动建立连接时附带的连接属性属性的格式必须为这样:[属性名=property;]
#注意:"user" 与 "password" 两个属性会被明确地传递,因此这里不需要包含他们。
connectionProperties=useUnicode=true;characterEncoding=gbk
#指定由连接池所创建的连接的自动提交(auto-commit)状态。
defaultAutoCommit=true
#driver default 指定由连接池所创建的连接的事务级别(TransactionIsolation)。
#可用值为下列之一:(详情可见javadoc。)NONE,READ_UNCOMMITTED, READ_COMMITTED, REPEATABLE_READ, SERIALIZABLE
defaultTransactionIsolation=READ_UNCOMMITTED
数据库连接池C3P0代码连接与配置文件:
1.先导入使用的jar文件,分别是c3p0.jar文件
2.分别使用两种方式实现,使用配置文件(c3p0-config.xml)的方式与不使用配置文件的方式。
TestC3p0.java
package c3p0;
import java.sql.Connection;
import java.sql.PreparedStatement;
import org.junit.Test;
import util.JDBCUtil;
import com.mchange.v2.c3p0.ComboPooledDataSource;
public class TestC3p0 {
@Test
public void test1(){
Connection conn=null;
PreparedStatement prepareStatement = null;
try {
//1.创建datasource
ComboPooledDataSource cpds=new ComboPooledDataSource();
//2.设置链接数据的信息
cpds.setDriverClass("com.mysql.jdbc.Driver");
cpds.setJdbcUrl("jdbc:mysql://localhost/stum");
cpds.setUser("root");
cpds.setPassword("123");
//2.得到连接对象
conn=cpds.getConnection();
String sql="insert into account values(null,?,?)";
prepareStatement = conn.prepareStatement(sql);
prepareStatement.setString(1, "abc");
prepareStatement.setInt(2, 2345);
prepareStatement.executeUpdate();
} catch (Exception e) {
e.printStackTrace();
}finally{
JDBCUtil.release(conn, prepareStatement);
}
}
@Test
public void test2(){
Connection conn=null;
PreparedStatement prepareStatement = null;
try {
//1.创建datasource
ComboPooledDataSource cpds=new ComboPooledDataSource(); //默认会找default
//ComboPooledDataSource cpds=new ComboPooledDataSource("oracle"); //使用的是oracle数据库
//2.得到连接对象
conn=cpds.getConnection();
String sql="insert into account values(null,?,?)";
prepareStatement = conn.prepareStatement(sql);
prepareStatement.setString(1, "fgh");
prepareStatement.setInt(2, 3567);
prepareStatement.executeUpdate();
} catch (Exception e) {
e.printStackTrace();
}finally{
JDBCUtil.release(conn, prepareStatement);
}
}
}
配置文件:c3p0-config.xml
<?xml version="1.0" encoding="UTF-8"?>
<c3p0-config>
<!-- default-config 默认的配置, -->
<default-config>
<property name="driverClass">com.mysql.jdbc.Driver</property>
<property name="jdbcUrl">jdbc:mysql://localhost/stum</property>
<property name="user">root</property>
<property name="password">123</property>
<property name="initialPoolSize">10</property>
<property name="maxIdleTime">30</property>
<property name="maxPoolSize">100</property>
<property name="minPoolSize">10</property>
<property name="maxStatements">200</property>
</default-config>
<!-- This app is massive! -->
<named-config name="oracle">
<property name="acquireIncrement">50</property>
<property name="initialPoolSize">100</property>
<property name="minPoolSize">50</property>
<property name="maxPoolSize">1000</property>
<!-- intergalactoApp adopts a different approach to configuring statement caching -->
<property name="maxStatements">0</property>
<property name="maxStatementsPerConnection">5</property>
<!-- he's important, but there's only one of him -->
<user-overrides user="master-of-the-universe">
<property name="acquireIncrement">1</property>
<property name="initialPoolSize">1</property>
<property name="minPoolSize">1</property>
<property name="maxPoolSize">5</property>
<property name="maxStatementsPerConnection">50</property>
</user-overrides>
</named-config>
</c3p0-config>