四种jdbc链接方式

四种连接数据库的方法（DriverManager、DataSource子类、DBCP、c3p0）

一、环境

   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;

      }



   }

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();

}

}