四種連接數(shù)據(jù)庫的方法（DriverManager、DataSource子類、DBCP、c3p0） 收藏
一、環(huán)境
1、數(shù)據(jù)庫驅動jar文件
2、DBCP方法
Commons-dbcp.jar：連接池的實現(xiàn)
Commons-pool.jar：連接池實現(xiàn)的依賴庫
資源文件
3、c3p0方法：
c3p0-0.9.1.2.jar
配置文件：c3p0-config.xml
二、連接操作
1.DriverManager方法;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
public class DBConnection {
private static String driverClass = "com.mysql.jdbc.Driver";
private static String url = "jdbc:mysql://localhost:3306/test";
private static String username = "root";
private static String password = "123456";
private static Connection conn = null;
static{
try {
//注冊驅動
// 不要把conn = DriverManager.getConnection(url, username, password);
//放在這里。防止所有用戶都用一個Connection
Class.forName(driverClass);
} catch (Exception e) {
throw new RuntimeException(e);
}
}
public static Connection getConnection() throws SQLException{
conn = DriverManager.getConnection(url, username, password);
return conn;
}
}
2.使用DataSource子類方法;
資源文件DBConnection.properties
driverClass = com.mysql.jdbc.Driver
url = jdbc:mysql://localhost:3306/test
username = root
password = 123456
模擬數(shù)據(jù)連接池 DataSourcePool.java
package cn.langzi.jdbc.DataSource;
import java.io.InputStream;
import java.io.PrintWriter;
import java.lang.reflect.InvocationHandler;
import java.lang.reflect.Method;
import java.lang.reflect.Proxy;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.LinkedList;
import java.util.Properties;
import javax.sql.DataSource;
import javax.sql.DataSource;
public class DataSourcePool implements DataSource {
private static String url = null;
private static String username = null;
private static String password = null;
private static int size = 10;
private static LinkedList<Connection> list = new LinkedList<Connection>();
static{
try {
InputStream in = DataSourcePool.class.getClassLoader()
.getResourceAsStream(
"cn/langzi/jdbc/DataSource/DBConnection.properties");
Properties prop = new Properties();
prop.load(in);
Class.forName(prop.getProperty("driverClass"));
url = prop.getProperty("url");
username = prop.getProperty("username");
password = prop.getProperty("password");
} catch (Exception e) {
throw new ExceptionInInitializerError(e);
}
}
private static DataSourcePool pool = new DataSourcePool();
//創(chuàng)建對象就初始化size個數(shù)據(jù)庫連接
private DataSourcePool(){
for(int i=0;i<size;i++){
try {
Connection conn = DriverManager.getConnection(url, username, password);
System.out.println(conn);
list.add(conn);
} catch (SQLException e) {
e.printStackTrace();
}
}
}
public static DataSourcePool getInstance(){
return pool;
}
@Override
public Connection getConnection() throws SQLException {
if(list.size()>0){
//取到連接，即從list中彈出一個Connection 連接
final Connection conn = list.pop();
//動態(tài)代理，返回一個代理對象
return (Connection) Proxy.newProxyInstance(DataSourcePool.class.getClassLoade(), conn.getClass().getInterfaces(), new InvocationHandler(){
public Object invoke(Object proxy, Method method, Object[] args)
throws Throwable {
//如果Connection調用的是close方法就將連接返回給數(shù)據(jù)連接池
if(method.getName().equals("close")){
list.push(conn);
return null;
}
return method.invoke(conn, args);
}
});
}
//連接用完
throw new RuntimeException("對不起，服務器繁忙！??！");
}
@Override
public Connection getConnection(String username, String password)
throws SQLException {
// TODO Auto-generated method stub
return null;
}
@Override
public PrintWriter getLogWriter() throws SQLException {
// TODO Auto-generated method stub
return null;
}
@Override
public int getLoginTimeout() throws SQLException {
// TODO Auto-generated method stub
return 0;
}
@Override
public void setLogWriter(PrintWriter out) throws SQLException {
// TODO Auto-generated method stub
}
@Override
public void setLoginTimeout(int seconds) throws SQLException {
// TODO Auto-generated method stub
}
@Override
public boolean isWrapperFor(Class<?> iface) throws SQLException {
// TODO Auto-generated method stub
return false;
}
@Override
public <T> T unwrap(Class<T> iface) throws SQLException {
// TODO Auto-generated method stub
return null;
}
}
package cn.langzi.jdbc.DataSource;
import java.sql.Connection;
import java.sql.SQLException;
public class DBConnection {
public static Connection getConnection() throws SQLException{
Connection conn = DataSourcePool.getInstance().getConnection();
return conn;
}
}
DBCP方法：
資源文件：dbcpconfig.properties
driverClassName=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/test
username=root
password=123456
initialSize=10
maxActive=50
maxIdle=20
minIdle=5
maxWait=60000
連接數(shù)據(jù)庫：
package cn.langzi.jdbc.DBCP;
import java.io.IOException;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.Properties;
import javax.sql.DataSource;
import org.apache.commons.dbcp.BasicDataSourceFactory;
public class DBConnection {
private static DataSource dataSource = null;
static {
try {
//獲取資源文件
InputStream in = DBConnection.class.getClassLoader().getResourceAsStream("cn/langzi/jdbc/DBCP/dbcpconfig.properties");
Properties properties = new Properties();
//加載資源文件
properties.load(in);
//建立數(shù)據(jù)工廠
BasicDataSourceFactory dataSourceFactory = new BasicDataSourceFactory();
dataSource = dataSourceFactory.createDataSource(properties);
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
public static Connection getConnection() throws SQLException{
return dataSource.getConnection();
}
}
c3p0方法：
配置文件：c3p0-config.xml
<?xml version="1.0" encoding="UTF-8"?>
<c3p0-config>
<named-config name="userApp">
<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="acquireIncrement">5</property>
<property name="initialPoolSize">10</property>
<property name="minPoolSize">10</property>
<property name="maxPoolSize">20</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>
連接數(shù)據(jù)庫：
package cn.langzi.jdbc.c3p0;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import javax.sql.DataSource;
import com.mchange.v2.c3p0.ComboPooledDataSource;
public class DbConnection {
private static DataSource dataSource;
static{
dataSource = new ComboPooledDataSource("userApp");
}
public static Connection getConnectioon() throws SQLException{
return dataSource.getConnection();
}
}
