当前位置：Gxlcms > 数据库问题 > JDBC （三）

JDBC （三）

时间：2021-07-01 10:21:17 帮助过：9人阅读

=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")){

人气教程排行

本站所有资源全部来源于网络，若本站发布的内容侵害到您的隐私或者利益，请联系我们删除！