必发88手机客户端 2

java数据库连接,数据库链接

数据库链接

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;



public class DBUtils {
private static final String URL="jdbc:mysql://localhost:3306/jdbc?characterEncoding=utf-8";
private static final String UER_NAME = "root";
private static final String PDW="123123";

private static DBUtils me = new DBUtils();


public static DBUtils getInstance(){
return me;
}

//为了是单例模式成为唯一可以获取该对象的方法,将该类的无参构造函数设为私有
private DBUtils(){}

/*
* 数据库连接
*/
public Connection getConn(){
Connection conn = null;
try {
Class.forName("com.mysql.jdbc.Driver");
conn = DriverManager.getConnection(URL, UER_NAME,PDW);
} catch (ClassNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return conn;

}

/*
* 释放资源
*/
public void releaseRes(Connection conn,PreparedStatement pstmt,ResultSet rs){

try {
if(conn!=null) conn.close();
if(pstmt!=null) pstmt.close();
if(rs!=null) rs.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}

}

 

import java.sql.Connection;import
java.sql.DriverManager;import java.sql.PreparedStatement;import
java.sql.ResultSet;import java.sql.SQLException; public class
DBUt…

    因为每次使用 JDBC 都要写一些重复的代码,我们可以直接抽取出一个工具类来使用。我们把主要目的是想通过工具类返回数据库连接,然后就可以直接通过连接来操作了。

1 数据库连接池的原理

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

必发88手机客户端 1

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

 

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

必发88手机客户端 2

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

 

  • 示例:模拟数据库连接池

    • 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 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);
        }
}

 

JDBCUtilsConfig工具类

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 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)对于不需要改写的方法,调用原有哦对象的对应方法。      

 

package utils;

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 pool;
      public MyConnection(Connection conn,List 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 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;
    }
}

 

import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Properties;

4 动态代理

public class JDBCUtilsConfig {

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;
    }
}

 

private static Connection conn;
private static String driverClass;
private static String url;
private static String user;
private static String password;

static {

    try {
        //1.先调用加载配置文件的方法,读取配置文件
        ReadingConfig();

        //2.加载驱动
        Class.forName(driverClass);

        //3.连接数据库
        conn = DriverManager.getConnection(url, user, password);


    } catch (Exception e) {
        // TODO Auto-generated catch block
        e.printStackTrace();
    }
}


//读取配置文件
private static void ReadingConfig() throws Exception {

    //1.使用类加载器读取配置文件
    InputStream inStream = JDBCUtilsConfig.class.getClassLoader().getResourceAsStream("database.properties");

    //2.获取Properties对象
    Properties pro = new Properties();

    //3.加载输入流
    pro.load(inStream);

    //4.解析对应的资源
    driverClass = pro.getProperty("driver");
    url = pro.getProperty("url");
    user = pro.getProperty("user");
    password = pro.getProperty("password");

}


//返回数据库连接
public static Connection Getconnection() {
    return conn;
}


//关闭连接,释放资源
public static void CloseSource(ResultSet rs,PreparedStatement ptmt,Connection conn) {
    if(rs != null) {
        try {
            rs.close();
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
    }

    if(ptmt != null) {
        try {
            ptmt.close();
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
    }

    if(conn != null) {
        try {
            conn.close();
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
    }
}

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

 

package DatabaseConnection;

5.2 C3P0

  • ①导入jar包

    c3p0-0.9.1.2.jar

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


    <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> 
    

  • 必发88手机客户端,③新建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();
        }
    }
    

    }

  

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;

6 编写自己的JDBC框架

import domain.Student;
import utils.JDBCUtilsConfig;

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

 

public class practice05 {

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

    }


}

 

public static void main(String[] args) throws Exception {
    // TODO Auto-generated method stub

    //1.读写配置文件,获取数据库连接
    Connection conn = JDBCUtilsConfig.Getconnection();
    //System.out.println(conn);

    //2.准备sql语句
    String sql = "select *from student";

    //3.预编译
    PreparedStatement ptmt = conn.prepareStatement(sql);

    //4.执行查询
    ResultSet rs = ptmt.executeQuery();

    //5.处理获取到的结果
    List<Student> liststu = new ArrayList<Student>();

    while(rs.next()) {

        Student student = new Student(rs.getString("Sno"),rs.getString("Sname"),rs.getString("Ssex"),rs.getInt("Sage"),rs.getString("Sdept"),rs.getString("S_entrance"));
        liststu.add(student);
    }


    //6.使用迭代器遍历
    Iterator<Student> iterator = liststu.iterator();
    while(iterator.hasNext()) {
        Student stu = iterator.next();
        System.out.println(stu);
    }


    //7.释放资源
    JDBCUtilsConfig.CloseSource(rs, ptmt, conn);
}

}