一、环境
1、数据库驱动jar文件
2、DBCP方法
Commons-dbcp.jar:连接池的实现
Commons-pool.jar:连接池实现的依赖库
资源文件
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;
- }
- }
- 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
模拟数据连接池 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();
- //创建对象就初始化size个数据库连接
- 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();
- //动态代理,返回一个代理对象
- 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方法就将连接返回给数据连接池
- 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
连接数据库:
- 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);
- //建立数据工厂
- 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>
连接数据库:
- 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();
- }
- }