JDBC （三）

2023-08-15 09:57:22

1 数据库连接池的原理

应用程序直接获取Connection的缺点：

缺点：用户每次请求都需要向数据库获取连接，而数据库创建连接通常需要消耗相对较大的资源，创建的时间较长。如果一个网站一天访问量是10万，数据库服务器就需要创建至少10万个连接，极大的浪费数据库的资源，并且非常容易造成数据库服务器内存溢出、宕机等。

使用数据库连接池来优化程序：

使用数据库连接池的优点：可以大大的降低对数据库的连接、关闭，提高了系统的性能。

示例：模拟数据库连接池
- jdbc.properties

jdbc.driver=com.mysql.jdbc.Driver
jdbc.url=jdbc:mysql://localhost:3306/jdbc
jdbc.user=root
jdbc.password=root

- JDBCUtil.java

package util;

import java.io.IOException;
import java.io.InputStream;
import java.sql.*;
import java.util.Properties;

/**
 * 2017/11/8
 * 说明：JDBC的工具类
 */
public class JDBCUtil {
    private static String driverClass;
    private static String url;
    private static String user;
    private static String password;
    static {
        InputStream is = JDBCUtil.class.getClassLoader().getResourceAsStream("jdbc.properties");
        Properties pro = new Properties();
        try {
            pro.load(is);
            driverClass = pro.getProperty("jdbc.driver");
            url= pro.getProperty("jdbc.url");
            user=pro.getProperty("jdbc.user");
            password=pro.getProperty("jdbc.password");
            Class.forName(driverClass);

        } catch (Exception e) {
           throw new RuntimeException(e);
        }

    }

    /**
     * 获取数据库连接
     * @return
     * @throws ClassNotFoundException
     * @throws SQLException
     */
    public static Connection getConnection() throws ClassNotFoundException, SQLException {

        //2 获取数据库的连接
        Connection conn = DriverManager.getConnection(url,user,password);
        return conn;
    }

    /**
     * 释放资源
     * @param rs
     * @param stmt
     * @param conn
     */
    public static void release(ResultSet rs, Statement stmt ,Connection conn){
        if(rs != null){
            try {
                rs.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if(stmt != null){
            try {
                stmt.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }

        if(conn != null){
            try {
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

}

- ConnectionPoolDemo.java

package util;

import java.sql.Connection;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

/**
 * 2017/11/11
 * 说明：模拟数据库连接池
 */
public class ConnectionPoolDemo {
    private static List<Connection> pool = new ArrayList<>();
    static {
        for(int x =0;x<10;x++){
            try {
                Connection conn = JDBCUtil.getConnection();
                pool.add(conn);
            } catch (ClassNotFoundException e) {
                e.printStackTrace();
            } catch (SQLException e) {
                e.printStackTrace();
            }

        }
    }

    /**
     * 从连接池中获取一个连接
     * @return
     */
    public synchronized static Connection getConnection(){
        if(pool.size() >0){
            return pool.remove(0);
        }else{
            throw new RuntimeException("服务器很忙");
        }

    }

    /**
     * 释放资源，就是将连接放到连接池里面
     * @param conn
     */
    public static void close(Connection conn){
        pool.add(conn);
    }

}

2 编写数据源

需要实现javax.sql.DataSource接口。

示例：MyDataSouce.java

package util;

import javax.sql.DataSource;
import java.io.PrintWriter;
import java.sql.Connection;
import java.sql.SQLException;
import java.sql.SQLFeatureNotSupportedException;
import java.util.ArrayList;
import java.util.Collections;
import java.util.List;
import java.util.logging.Logger;

/**
 * 2017/11/11
 * 说明：
 */
public class MyDataSource implements DataSource {
    private static List<Connection> pool = Collections.synchronizedList(new ArrayList<>());
    static {
        for(int x =0;x<10;x++){
            try {
                Connection conn = JDBCUtil.getConnection();
                pool.add(conn);
            } catch (ClassNotFoundException e) {
                e.printStackTrace();
            } catch (SQLException e) {
                e.printStackTrace();
            }

        }
    }

    @Override
    public Connection getConnection() throws SQLException {
        if(pool.size() >0){
            return pool.remove(0);
        }else{
            throw new RuntimeException("服务器很忙");
        }

    }

    @Override
    public Connection getConnection(String username, String password) throws SQLException {
        return null;
    }

    @Override
    public <T> T unwrap(Class<T> iface) throws SQLException {
        return null;
    }

    @Override
    public boolean isWrapperFor(Class<?> iface) throws SQLException {
        return false;
    }

    @Override
    public PrintWriter getLogWriter() throws SQLException {
        return null;
    }

    @Override
    public void setLogWriter(PrintWriter out) throws SQLException {

    }

    @Override
    public void setLoginTimeout(int seconds) throws SQLException {

    }

    @Override
    public int getLoginTimeout() throws SQLException {
        return 0;
    }

    @Override
    public Logger getParentLogger() throws SQLFeatureNotSupportedException {
        return null;
    }
}

示例：使用自定义数据源--MyDataSouceTest.java

package util;

import java.sql.Connection;
import java.sql.SQLException;

/**
 * 2017/11/11
 * 说明：
 */
public class MyDataSourceTest {
    private MyDataSource myDataSource;
    public MyDataSourceTest(MyDataSource myDataSource){
        this.myDataSource = myDataSource;
    }
    public void save() throws SQLException {
        Connection conn = myDataSource.getConnection();
        //操作的逻辑

        conn.close();//但是当用户调用Collection的close方法的时候，我们却发现将连接给关闭了，不好

    }

}

缺点：使用自定义数据源，当用户调用Collection的close()方法的时候，是将连接给关闭了，这样违背了我们设计数据源的初衷。换句话说，用户得到Connection的实现是：是数据库驱动对Connection接口的实现。所以，调用的close()方法都是数据库驱动的，它会把连接关闭的，恰恰相反，这却不是我们所想要的，我们想要的是当用户调用close()方法的时候，是将连接放回到连接池里。
解决方案：
- ①继承：不行
  - 原因：
    - a)如果继承，我们需要继承很多驱动的实现类，很麻烦。
    - b)数据库驱动对Connection接口的实现类不允许继承。
    - 3)丢失了原有对象的信息。　　　　　　
- ②装饰（包装、静态代理）设计模式
  - 原因：保证被保证对象的原有信息，又可以对某个/某些方法进行改写。
  - 步骤：
    - a)编写一个类，实现与被包装类同样的接口。
    - b)定义一个变量，引用被保证类的实例。
    - c)定义构造方法，传入被包装类的实例。
    - d)对于要改写的方法，编写自己所需要的代码。
    - e)对于不需要改写的方法，调用原有哦对象的对应方法。

3 装饰设计模式

示例：
- jdbc.properties

jdbc.driver=com.mysql.jdbc.Driver
jdbc.url=jdbc:mysql://localhost:3306/jdbc
jdbc.user=root
jdbc.password=root

- JDBCUtil.java

package util;

import java.io.IOException;
import java.io.InputStream;
import java.sql.*;
import java.util.Properties;

/**
 * 2017/11/8
 * 说明：JDBC的工具类
 */
public class JDBCUtil {
    private static String driverClass;
    private static String url;
    private static String user;
    private static String password;
    static {
        InputStream is = JDBCUtil.class.getClassLoader().getResourceAsStream("jdbc.properties");
        Properties pro = new Properties();
        try {
            pro.load(is);
            driverClass = pro.getProperty("jdbc.driver");
            url= pro.getProperty("jdbc.url");
            user=pro.getProperty("jdbc.user");
            password=pro.getProperty("jdbc.password");
            Class.forName(driverClass);

        } catch (Exception e) {
           throw new RuntimeException(e);
        }

    }

    /**
     * 获取数据库连接
     * @return
     * @throws ClassNotFoundException
     * @throws SQLException
     */
    public static Connection getConnection() throws ClassNotFoundException, SQLException {

        //2 获取数据库的连接
        Connection conn = DriverManager.getConnection(url,user,password);
        return conn;
    }

    /**
     * 释放资源
     * @param rs
     * @param stmt
     * @param conn
     */
    public static void release(ResultSet rs, Statement stmt ,Connection conn){
        if(rs != null){
            try {
                rs.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if(stmt != null){
            try {
                stmt.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }

        if(conn != null){
            try {
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

}

- MyConnection.java

package util;

import java.sql.*;
import java.util.List;
import java.util.Map;
import java.util.Properties;
import java.util.concurrent.Executor;

/**
 * 2017/11/11
 * 说明：对Connection的实现类进行静态代理
 */
public class MyConnection implements Connection {
    private Connection conn;
    private List<Connection> pool;
    public  MyConnection(Connection conn,List<Connection> pool){
        this.conn = conn;
        this.pool = pool;
    }

    @Override
    public void close() throws SQLException {
        pool.add(conn);

    }

    @Override
    public Statement createStatement() throws SQLException {
        return conn.createStatement();
    }

    @Override
    public PreparedStatement prepareStatement(String sql) throws SQLException {
        return conn.prepareStatement(sql);
    }

    @Override
    public CallableStatement prepareCall(String sql) throws SQLException {
        return conn.prepareCall(sql);
    }

    @Override
    public String nativeSQL(String sql) throws SQLException {
        return conn.nativeSQL(sql);
    }

    @Override
    public void setAutoCommit(boolean autoCommit) throws SQLException {
        conn.setAutoCommit(autoCommit);
    }

    @Override
    public boolean getAutoCommit() throws SQLException {
        return conn.getAutoCommit();
    }

    @Override
    public void commit() throws SQLException {
        conn.commit();
    }

    @Override
    public void rollback() throws SQLException {
        conn.rollback();
    }

    @Override
    public boolean isClosed() throws SQLException {
        return conn.isClosed();
    }

    @Override
    public DatabaseMetaData getMetaData() throws SQLException {
        return conn.getMetaData();
    }

    @Override
    public void setReadOnly(boolean readOnly) throws SQLException {
        conn.setReadOnly(readOnly);
    }

    @Override
    public boolean isReadOnly() throws SQLException {
        return conn.isReadOnly();
    }

    @Override
    public void setCatalog(String catalog) throws SQLException {
        conn.setCatalog(catalog);
    }

    @Override
    public String getCatalog() throws SQLException {
        return conn.getCatalog();
    }

    @Override
    public void setTransactionIsolation(int level) throws SQLException {
        conn.setTransactionIsolation(level);
    }

    @Override
    public int getTransactionIsolation() throws SQLException {
        return conn.getTransactionIsolation();
    }

    @Override
    public SQLWarning getWarnings() throws SQLException {
        return conn.getWarnings();
    }

    @Override
    public void clearWarnings() throws SQLException {
        conn.clearWarnings();
    }

    @Override
    public Statement createStatement(int resultSetType, int resultSetConcurrency) throws SQLException {
        return conn.createStatement(resultSetType,resultSetConcurrency);
    }

    @Override
    public PreparedStatement prepareStatement(String sql, int resultSetType, int resultSetConcurrency) throws SQLException {
        return conn.prepareStatement(sql,resultSetType,resultSetConcurrency);
    }

    @Override
    public CallableStatement prepareCall(String sql, int resultSetType, int resultSetConcurrency) throws SQLException {
        return conn.prepareCall(sql,resultSetType,resultSetConcurrency);
    }

    @Override
    public Map<String, Class<?>> getTypeMap() throws SQLException {
        return conn.getTypeMap();
    }

    @Override
    public void setTypeMap(Map<String, Class<?>> map) throws SQLException {
        conn.setTypeMap(map);
    }

    @Override
    public void setHoldability(int holdability) throws SQLException {
        conn.setHoldability(holdability);
    }

    @Override
    public int getHoldability() throws SQLException {
        return conn.getHoldability();
    }

    @Override
    public Savepoint setSavepoint() throws SQLException {
        return conn.setSavepoint();
    }

    @Override
    public Savepoint setSavepoint(String name) throws SQLException {
        return conn.setSavepoint(name);
    }

    @Override
    public void rollback(Savepoint savepoint) throws SQLException {
        conn.rollback(savepoint);
    }

    @Override
    public void releaseSavepoint(Savepoint savepoint) throws SQLException {
        conn.releaseSavepoint(savepoint);
    }

    @Override
    public Statement createStatement(int resultSetType, int resultSetConcurrency, int resultSetHoldability) throws SQLException {
        return conn.createStatement(resultSetType,resultSetConcurrency,resultSetHoldability);
    }

    @Override
    public PreparedStatement prepareStatement(String sql, int resultSetType, int resultSetConcurrency, int resultSetHoldability) throws SQLException {
        return conn.prepareStatement(sql,resultSetType,resultSetConcurrency,resultSetHoldability);
    }

    @Override
    public CallableStatement prepareCall(String sql, int resultSetType, int resultSetConcurrency, int resultSetHoldability) throws SQLException {
        return conn.prepareCall(sql,resultSetType,resultSetConcurrency,resultSetHoldability);
    }

    @Override
    public PreparedStatement prepareStatement(String sql, int autoGeneratedKeys) throws SQLException {
        return conn.prepareStatement(sql,autoGeneratedKeys);
    }

    @Override
    public PreparedStatement prepareStatement(String sql, int[] columnIndexes) throws SQLException {
        return conn.prepareStatement(sql,columnIndexes);
    }

    @Override
    public PreparedStatement prepareStatement(String sql, String[] columnNames) throws SQLException {
        return conn.prepareStatement(sql,columnNames);
    }

    @Override
    public Clob createClob() throws SQLException {
        return conn.createClob();
    }

    @Override
    public Blob createBlob() throws SQLException {
        return conn.createBlob();
    }

    @Override
    public NClob createNClob() throws SQLException {
        return conn.createNClob();
    }

    @Override
    public SQLXML createSQLXML() throws SQLException {
        return conn.createSQLXML();
    }

    @Override
    public boolean isValid(int timeout) throws SQLException {
        return conn.isValid(timeout);
    }

    @Override
    public void setClientInfo(String name, String value) throws SQLClientInfoException {
        conn.setClientInfo(name,value);
    }

    @Override
    public void setClientInfo(Properties properties) throws SQLClientInfoException {
        conn.setClientInfo(properties);
    }

    @Override
    public String getClientInfo(String name) throws SQLException {
        return conn.getClientInfo(name);
    }

    @Override
    public Properties getClientInfo() throws SQLException {
        return conn.getClientInfo();
    }

    @Override
    public Array createArrayOf(String typeName, Object[] elements) throws SQLException {
        return conn.createArrayOf(typeName,elements);
    }

    @Override
    public Struct createStruct(String typeName, Object[] attributes) throws SQLException {
        return conn.createStruct(typeName,attributes);
    }

    @Override
    public void setSchema(String schema) throws SQLException {
        conn.setSchema(schema);
    }

    @Override
    public String getSchema() throws SQLException {
        return conn.getSchema();
    }

    @Override
    public void abort(Executor executor) throws SQLException {
        conn.abort(executor);
    }

    @Override
    public void setNetworkTimeout(Executor executor, int milliseconds) throws SQLException {
        conn.setNetworkTimeout(executor,milliseconds);
    }

    @Override
    public int getNetworkTimeout() throws SQLException {
        return conn.getNetworkTimeout();
    }

    @Override
    public <T> T unwrap(Class<T> iface) throws SQLException {
        return conn.unwrap(iface);
    }

    @Override
    public boolean isWrapperFor(Class<?> iface) throws SQLException {
        return conn.isWrapperFor(iface);
    }
}

- MyDataSource.java

package util;

import javax.sql.DataSource;
import java.io.PrintWriter;
import java.sql.Connection;
import java.sql.SQLException;
import java.sql.SQLFeatureNotSupportedException;
import java.util.ArrayList;
import java.util.Collections;
import java.util.List;
import java.util.logging.Logger;

/**
 * 2017/11/11
 * 说明：
 */
public class MyDataSource implements DataSource {
    private static List<Connection> pool = Collections.synchronizedList(new ArrayList<>());
    static {
        for(int x =0;x<10;x++){
            try {
                Connection conn = JDBCUtil.getConnection();
                pool.add(conn);
            } catch (ClassNotFoundException e) {
                e.printStackTrace();
            } catch (SQLException e) {
                e.printStackTrace();
            }

        }
    }

    @Override
    public Connection getConnection() throws SQLException {
        if(pool.size() >0){
            return new MyConnection(pool.remove(0),pool);
        }else{
            throw new RuntimeException("服务器很忙");
        }

    }

    @Override
    public Connection getConnection(String username, String password) throws SQLException {
        return null;
    }

    @Override
    public <T> T unwrap(Class<T> iface) throws SQLException {
        return null;
    }

    @Override
    public boolean isWrapperFor(Class<?> iface) throws SQLException {
        return false;
    }

    @Override
    public PrintWriter getLogWriter() throws SQLException {
        return null;
    }

    @Override
    public void setLogWriter(PrintWriter out) throws SQLException {

    }

    @Override
    public void setLoginTimeout(int seconds) throws SQLException {

    }

    @Override
    public int getLoginTimeout() throws SQLException {
        return 0;
    }

    @Override
    public Logger getParentLogger() throws SQLFeatureNotSupportedException {
        return null;
    }
}

4 动态代理

4.1 基于接口的动态代理

示例：
Human.java

package com;

public interface Human {
    /**
     * 跳舞
     * @param money
     */
    public void dance(float money);

    /**
     * 唱歌
     * @param money
     */
    public void sing(float money);
}

SpringBrother.java

package com;

public class SpringBrother implements Human {
    @Override
    public void dance(float money) {
        System.out.println("拿到"+money+"元,跳舞");
    }

    @Override
    public void sing(float money) {
        System.out.println("拿到"+money+"元,唱歌");
    }
}

Client1.java

package com;

import java.lang.reflect.InvocationHandler;
import java.lang.reflect.Method;
import java.lang.reflect.Proxy;
import java.util.Arrays;

public class Client1 {
    public static void main(String[] args){

        final Human sb = new SpringBrother();
        /**
         * 通过声明和SpringBrother拥有相同的类加载器，以及实现相同的接口，那么此类就声明是代理类
         */
        Human proxy = (Human) Proxy.newProxyInstance(sb.getClass().getClassLoader(), sb.getClass().getInterfaces(), new InvocationHandler() {
            /**
             *
              * @param proxy 代理对象的引用
             * @param method 当前执行的方法
             * @param args 当前方法执行的参数
             * @return
             * @throws Throwable
             */
            @Override
            public Object invoke(Object proxy, Method method, Object[] args) throws Throwable {
                if("sing".equals(method.getName())){
                    //唱歌
                    float money = (float) args[0];
                    if(money >=10000){
                        return method.invoke(sb,money /2);
                    }
                }
                if("dance".equals(method.getName())){
                    //跳舞
                    float money = (float) args[0];
                    if(money > 20000){
                        return method.invoke(sb,args);
                    }
                }

                return null;
            }
        });
        proxy.sing(10000);
        proxy.dance(200);

    }
}

示例：使用动态代理实现自定义数据源（链接池）
jdbc.properties

jdbc.driver=com.mysql.jdbc.Driver
jdbc.url=jdbc:mysql://localhost:3306/jdbc
jdbc.user=root
jdbc.password=root

JDBCUtil.java

package com.util;

import java.io.InputStream;
import java.sql.*;
import java.util.Properties;

public class JDBCUtil {
    private static String driverClass;
    private static String url;
    private static String user;
    private static String password;
    static {
        InputStream is = JDBCUtil.class.getClassLoader().getResourceAsStream("jdbc.properties");
        Properties pro = new Properties();
        try {
            pro.load(is);
            driverClass = pro.getProperty("jdbc.driver");
            url= pro.getProperty("jdbc.url");
            user=pro.getProperty("jdbc.user");
            password=pro.getProperty("jdbc.password");
            Class.forName(driverClass);

        } catch (Exception e) {
            throw new RuntimeException(e);
        }

    }

    /**
     * 获取数据库连接
     * @return
     * @throws ClassNotFoundException
     * @throws SQLException
     */
    public static Connection getConnection() throws ClassNotFoundException, SQLException {

        //2 获取数据库的连接
        Connection conn = DriverManager.getConnection(url,user,password);
        return conn;
    }

    /**
     * 释放资源
     * @param rs
     * @param stmt
     * @param conn
     */
    public static void release(ResultSet rs, Statement stmt , Connection conn){
        if(rs != null){
            try {
                rs.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if(stmt != null){
            try {
                stmt.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }

        if(conn != null){
            try {
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

}

MyDataSource.java

package com;

import com.util.JDBCUtil;

import javax.sql.DataSource;
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.SQLException;
import java.sql.SQLFeatureNotSupportedException;
import java.util.ArrayList;
import java.util.Collections;
import java.util.List;
import java.util.logging.Logger;

public class MyDataSource implements DataSource {
    private static List<Connection> pool = Collections.synchronizedList(new ArrayList<>());
    static {
        for(int x =0;x<10;x++){
            try {
                Connection conn = JDBCUtil.getConnection();
                pool.add(conn);
            } catch (ClassNotFoundException e) {
                e.printStackTrace();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    @Override
    public Connection getConnection() throws SQLException {
        if(pool.size() >0){
            Connection conn = pool.remove(0);
            Connection proxy = (Connection) Proxy.newProxyInstance(conn.getClass().getClassLoader(), conn.getClass().getInterfaces(), new InvocationHandler() {
                @Override
                public Object invoke(Object proxy, Method method, Object[] args) throws Throwable {
                    if(method.getName().equals("close")){
                        //放到池子里
                        pool.add(conn);
                    }
                    return method.invoke(conn,args);
                }
            });
            return proxy;
        }else{
            throw new RuntimeException("服务器很忙");
        }

    }

    @Override
    public Connection getConnection(String username, String password) throws SQLException {
        return null;
    }

    @Override
    public <T> T unwrap(Class<T> iface) throws SQLException {
        return null;
    }

    @Override
    public boolean isWrapperFor(Class<?> iface) throws SQLException {
        return false;
    }

    @Override
    public PrintWriter getLogWriter() throws SQLException {
        return null;
    }

    @Override
    public void setLogWriter(PrintWriter out) throws SQLException {

    }

    @Override
    public void setLoginTimeout(int seconds) throws SQLException {

    }

    @Override
    public int getLoginTimeout() throws SQLException {
        return 0;
    }

    @Override
    public Logger getParentLogger() throws SQLFeatureNotSupportedException {
        return null;
    }
}

4.2 基于子类的动态代理（CGLIB）

示例：
SpringBrother.java

package com;

public class SpringBrother  {
    public void dance(float money) {
        System.out.println("拿到"+money+"元,跳舞");
    }

    public void sing(float money) {
        System.out.println("拿到"+money+"元,唱歌");
    }
}

Client2.java

package com;

import net.sf.cglib.proxy.Enhancer;
import net.sf.cglib.proxy.MethodInterceptor;
import net.sf.cglib.proxy.MethodProxy;

import java.lang.reflect.Method;

public class Client2 {
    public static void main(String[] args){
        SpringBrother sb = new SpringBrother();
        SpringBrother proxy = (SpringBrother) Enhancer.create(sb.getClass(), new MethodInterceptor() {
            @Override
            public Object intercept(Object o, Method method, Object[] objects, MethodProxy methodProxy) throws Throwable {
                if(method.getName().equals("dance")){
                    float money = (float) objects[0];
                    if(money >= 10000){
                        method.invoke(sb,objects);
                    }
                }
                if(method.getName().equals("sing")){
                    float money = (float) objects[0];
                    if(money >= 20000){
                        method.invoke(sb,objects);
                    }
                }

                return null;
            }
        });

        proxy.dance(10000);
        proxy.sing(25000);
    }
}

5 开源数据源的使用

5.1 DBCP

①导入jar包。

commons-dbcp-1.4.jar
commons-pool-1.5.6.jar

②导入配置文件 dbcpconfig.properties

#\u8FDE\u63A5\u8BBE\u7F6E
driverClassName=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/test
username=root
password=root

initialSize=10

maxActive=50

maxIdle=20

minIdle=5

maxWait=60000

connectionProperties=useUnicode=true;characterEncoding=utf8

defaultAutoCommit=true

defaultReadOnly=

defaultTransactionIsolation=REPEATABLE_READ

③新建DBCPUtil.java

package com.util;

import org.apache.commons.dbcp.BasicDataSourceFactory;

import javax.sql.DataSource;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.Properties;

public class DBCPUtil {
    private static DataSource dataSource;
    static{
        try {
            InputStream in = DBCPUtil.class.getClassLoader().getResourceAsStream("dbcpconfig.properties");
            Properties pro = new Properties();
            pro.load(in);
            dataSource = BasicDataSourceFactory.createDataSource(pro);
        }catch (Exception e){
            throw new RuntimeException(e);
        }

    }

    public static DataSource getDataSource(){
        return  dataSource;
    }

    public static Connection getConnection(){
        try {
            return dataSource.getConnection();
        } catch (SQLException e) {
           throw new RuntimeException(e);
        }
    }

}

5.2 C3P0

①导入jar包

c3p0-0.9.1.2.jar

②导入配置文件 c3p0-config.xml

<?xml version="1.0" encoding="UTF-8"?>
<c3p0-config>
    <default-config>
        <property name="driverClass">com.mysql.jdbc.Driver</property>
        <property name="jdbcUrl">jdbc:mysql:///test</property>
        <property name="user">root</property>
        <property name="password">root</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> 

</c3p0-config>

③新建C3P0Util.java

package com.util;

import com.mchange.v2.c3p0.ComboPooledDataSource;

import javax.sql.DataSource;
import java.sql.Connection;
import java.sql.SQLException;

public class C3P0util {
    private static ComboPooledDataSource dataSource = new ComboPooledDataSource();

    public static DataSource getDataSource(){
        return dataSource;
    }

    public static Connection getConnection(){
        try {
            return dataSource.getConnection();
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }

}

④测试：

package com.test;

import com.util.C3P0util;

import java.sql.Connection;
import java.sql.SQLException;

public class Test {
    public static void main(String[] args){
        Connection conn = C3P0util.getConnection();
        System.out.println(conn);
        try {
            conn.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

6 编写自己的JDBC框架

6.1 数据库元信息的获取

Connection中的方法：获取DatabaseMeta对象包含元数据的数据库，

DatabaseMetaData getMetaData() throws SQLException

示例：

package com;

import com.util.C3P0util;

import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.SQLException;

public class DatabaseMetaDataDemo {
    public static void main(String[] args) throws SQLException {
        Connection conn = C3P0util.getConnection();
        DatabaseMetaData databaseMetaData = conn.getMetaData();
        System.out.println("DatabaseMetaData对象"+databaseMetaData);
        System.out.println("数据库的连接:"+databaseMetaData.getURL());
        System.out.println("当前连接数据库管理系统的用户名:"+databaseMetaData.getUserName());
        System.out.println("数据库的产品名称："+databaseMetaData.getDatabaseProductName());
        System.out.println("数据库的版本号："+databaseMetaData.getDatabaseProductVersion());
        System.out.println("驱动程序的名称："+databaseMetaData.getDriverName());
        System.out.println("驱动程序的版本号："+databaseMetaData.getDriverVersion());
        System.out.println("数据库是否可读："+databaseMetaData.isReadOnly());　　　　　conn.close();　

    }
}

PreparedStatement中的方法：获取参数的的类型和属性的标记信息

ParameterMetaData getParameterMetaData() throws SQLException

示例：

package com;

import com.util.C3P0util;

import java.sql.*;

public class DatabaseMetaDataDemo {
    public static void main(String[] args) throws SQLException {
        Connection conn = C3P0util.getConnection();
        PreparedStatement psmt = conn.prepareStatement("select * from user where name = ? and password = ?");
        psmt.setString(1,"aa");
        psmt.setString(2,"bb");
        ParameterMetaData parameterMetaData = psmt.getParameterMetaData();
        System.out.println("参数的个数:"+parameterMetaData.getParameterCount());
　　　　　pstmt.close();　　　　　conn.close();　　
    }
}

PreparedStatement中的方法：获取ResultSet对象列的类型和属性信息的对象。

ResultSetMetaData getMetaData()   throws SQLException

示例：

package com;

import com.util.C3P0util;

import java.sql.*;

public class DatabaseMetaDataDemo {
    public static void main(String[] args) throws SQLException {
        Connection conn = C3P0util.getConnection();
        PreparedStatement psmt = conn.prepareStatement("select * from user ");
        ResultSet rs = psmt.executeQuery();
        ResultSetMetaData resultSetMetaData = rs.getMetaData();
        int count = resultSetMetaData.getColumnCount();
        System.out.println("结果集的列的数量："+count);
        for(int x = 0;x<count;x++){
            String columnName = resultSetMetaData.getCatalogName(x+1);
            int columnType = resultSetMetaData.getColumnType(x+1);
            System.out.println("列的名称:"+columnName+",列的类型："+columnType);
        }
        rs.close();
        psmt.close();
        conn.close();

    }
}

6.2 编写自己的JDBC框架

示例：

package com.util;

import java.sql.ResultSet;

public interface ResultSetHandler {
    public Object handle(ResultSet rs);
}

package com;

import com.util.ResultSetHandler;

import javax.sql.DataSource;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

public class MyDbutil {
    private DataSource dataSource;
    public MyDbutil(DataSource dataSource){
        this.dataSource = dataSource;
    }

    /**
     * 执行更新方法
     * @param sql 插入的sql
     * @param params 参数
     */
    public void update(String sql,Object...params){
        Connection conn = null;
        PreparedStatement pstmt = null;
        try {
            conn = dataSource.getConnection();
            pstmt = conn.prepareStatement(sql);
            //获取sql中的占位符数量
            int paramCounts = pstmt.getParameterMetaData().getParameterCount();
            if(paramCounts >0){
                if(params == null){
                    throw new RuntimeException("传入的参数为空");
                }
                if(params.length != paramCounts){
                    throw new RuntimeException("传入的参数的长度和占位符的个数不匹配");
                }
                for(int x =0;x<paramCounts;x++){
                    pstmt.setObject((x+1),params[x]);
                }
            }
            int count = pstmt.executeUpdate();
            System.out.println("更新的记录数:"+count);

        }catch (Exception e){
            throw new RuntimeException(e);
        }finally {
            close(conn,pstmt,null);
        }
    }

    public Object query(String sql,ResultSetHandler rsh,Object...params){
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        try{
            conn = dataSource.getConnection();
            pstmt= conn.prepareStatement(sql);
            //得到sql中的占位符的个数
            int paramCounts = pstmt.getParameterMetaData().getParameterCount();
            if(paramCounts > 0){
                if(params == null){
                    throw new RuntimeException("传入的参数为空");
                }
                if(params.length != paramCounts){
                    throw new RuntimeException("传入的参数的长度和占位符的个数不匹配");
                }
                for(int x =0;x<paramCounts;x++){
                    pstmt.setObject((x+1),params[x]);
                }
            }
            rs = pstmt.executeQuery();
            return rsh.handle(rs);
        }catch (Exception e){
            throw new RuntimeException(e);
        }finally {
            close(conn,pstmt,rs);
        }
    }

    /**
     * 释放资源
     * @param conn
     * @param pstmt
     * @param rs
     */
    private void close(Connection conn, PreparedStatement pstmt, ResultSet rs) {
        if(conn!=null){
            try {
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if(pstmt != null){
            try {
                pstmt.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if (rs != null){
            try {
                rs.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }

    }

}

码农公寓

1 数据库连接池的原理

2 编写数据源

3 装饰设计模式

4 动态代理

4.1 基于接口的动态代理

4.2 基于子类的动态代理 （CGLIB）

5 开源数据源的使用

5.1 DBCP

5.2 C3P0

6 编写自己的JDBC框架

6.1 数据库元信息的获取

6.2 编写自己的JDBC框架

相关文章

4.2 基于子类的动态代理（CGLIB）