4.Java数据库连接_4.JDBC 连接池封装

最新推荐文章于 2022-10-20 15:03:14 发布

转载最新推荐文章于 2022-10-20 15:03:14 发布 · 188 阅读

[java_4_数据库] 专栏收录该内容

4 篇文章

订阅专栏

本文详细介绍了一种基于Java的数据库连接池实现方法，通过SimpleDataSource类管理数据库连接，有效提高数据库访问效率。同时，提供了DBUtil工具类封装，简化SQL语句执行与结果处理流程，包括增删改查等常见数据库操作。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

https://blog.youkuaiyun.com/wtuyzh/article/details/72782603

1. jdbc.properties

driver=com.mysql.jdbc.Driver
    url=jdbc:mysql://localhost:3306/
    charset=?useUnicode=true&characterEncoding=utf8&zeroDateTimeBehavior=convertToNull&allowMultiQueries=true
    username=root
    password=root
    dbname=student

2. 连接池数据源SimpleDataSource

import java.io.PrintWriter;
import java.lang.reflect.InvocationHandler;
import java.lang.reflect.Method;
import java.lang.reflect.Proxy;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.SQLFeatureNotSupportedException;
import java.util.LinkedList;
import java.util.logging.Logger;
import javax.sql.DataSource;

public class SimpleDataSource implements DataSource
{
    private static int poolSize = 10;
    private static String driver_str = "com.mysql.jdbc.Driver";
    private LinkedList<Connection> pool = new LinkedList<Connection>();

    public SimpleDataSource(String driver, String url, String name, String pwd) {
        this(driver, url, name, pwd, poolSize);
    }

    public SimpleDataSource(String driver, String url, String name, String pwd, int poolSize) {
        try {
//            Class.forName(driver);
            // 加载数据库驱动程序
            try {
                Class.forName(driver_str);
            } catch (ClassNotFoundException e) {
                System.out.println("加载驱动错误");
                System.out.println(e.getMessage());
                e.printStackTrace();
            }
            this.poolSize = poolSize;
            if (poolSize <= 0) {
                throw new RuntimeException("初始化池大小失败: " + poolSize);
            }

            for (int i = 0; i < poolSize; i++) {
                System.out.println(url);
                System.out.println(name);
                System.out.println(pwd);
                Connection con = DriverManager.getConnection(url, name, pwd);
                con = ConnectionProxy.getProxy(con, pool);// 获取被代理的对象
                pool.add(con);// 添加被代理的对象
            }
        } catch (Exception e) {
            throw new RuntimeException(e.getMessage(), e);
        }

    }

    /** 获取池大小 */
    public int getPoolSize() {
        return poolSize;

    }

    /** 不支持日志操作 */
    public PrintWriter getLogWriter() throws SQLException {
        throw new RuntimeException("Unsupport Operation.");
    }

    public void setLogWriter(PrintWriter out) throws SQLException {
        throw new RuntimeException("Unsupport operation.");
    }

    /** 不支持超时操作 */
    public void setLoginTimeout(int seconds) throws SQLException {
        throw new RuntimeException("Unsupport operation.");
    }

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

    @SuppressWarnings("unchecked")
    public <T> T unwrap(Class<T> iface) throws SQLException {
        return (T) this;
    }

    public boolean isWrapperFor(Class<?> iface) throws SQLException {
        return DataSource.class.equals(iface);
    }

    /** 从池中取一个连接对象,使用了同步和线程调度 */
    public Connection getConnection() throws SQLException {
        synchronized (pool) {
            if (pool.size() == 0) {
                try {
                    pool.wait();
                } catch (InterruptedException e) {
                    throw new RuntimeException(e.getMessage(), e);
                }
                return getConnection();
            } else {
                return pool.removeFirst();
            }
        }
    }

    public Connection getConnection(String username, String password) throws SQLException {
        throw new RuntimeException("不支持接收用户名和密码的操作");
    }

    /** 实现对Connection的动态代理 */
    static class ConnectionProxy implements InvocationHandler {

        private Object obj;
        private LinkedList<Connection> pool;

        private ConnectionProxy(Object obj, LinkedList<Connection> pool) {
            this.obj = obj;
            this.pool = pool;
        }

        public static Connection getProxy(Object o, LinkedList<Connection> pool) {
            Object proxed = Proxy.newProxyInstance(o.getClass().getClassLoader(), new Class[] { Connection.class },
                    new ConnectionProxy(o, pool));
            return (Connection) proxed;
        }

        public Object invoke(Object proxy, Method method, Object[] args) throws Throwable {
            if (method.getName().equals("close")) {
                synchronized (pool) {
                    pool.add((Connection) proxy);
                    pool.notify();
                }
                return null;
            } else {
                return method.invoke(obj, args);
            }
        }

    }

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

3. jdbc封装DBUtil

 package dbTools;

import java.io.*;
import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.sql.*;
import java.util.*;
import javax.sql.DataSource;

public class DBUtil
{
    private static String USERNAME = null;
    private static String PASSWORD = null;
    private static String DRIVER = null;
    private static String URL = null;
    public static String DBNAME = null;
    public static String CHARSET = null;
    private static Connection conn = null;
    private PreparedStatement pst = null;
    private ResultSet rs = null;
    private static DataSource dataSource = null;
    private static final boolean AUTO_COMMIT = true;
    private String SQLTables = "show tables";

    static
    {
        // 创建一个 Properties对象
        Properties pro = new Properties();
        try
        {
            // 通过类加载器将配置文件加载进来
            pro.load(DBUtil.class.getClassLoader()
                    .getResourceAsStream("jdbc.properties"));
            // 读取配置文件得到属性值
            DBNAME=pro.getProperty("dbname");
            USERNAME = pro.getProperty("username");
            PASSWORD = pro.getProperty("password");
            DRIVER = pro.getProperty("driver");
            CHARSET=pro.getProperty("charset");
            URL = pro.getProperty("url")+DBNAME+CHARSET;
            System.out.println(URL);
            // 加载驱动
            dataSource = new SimpleDataSource(DRIVER, URL, USERNAME, PASSWORD);
            conn = dataSource.getConnection();
            if (!AUTO_COMMIT)
                conn.setAutoCommit(AUTO_COMMIT);
        } catch (IOException e)
        {
            e.printStackTrace();
        } catch (SQLException e)
        {
            e.printStackTrace();
        }
    }

/**
 *
 * @param sql 增删改sql语句
 * @return 更新成功返回true
 */
    public boolean update(String sql)
    {
        int result = -1;// 表示当用户执行添加删除和修改的时候所影响数据库的行数
        try
        {
            pst = conn.prepareStatement(sql);
            result = pst.executeUpdate();
        } catch (SQLException e)
        {
            e.printStackTrace();
        }
        return result > 0;
    }

    /**
     * @param sql 增删改sql语句
     * @param params 给sql中？传值，个数必须和sql中？匹配，
     * @return 更新成功返回true    
     */
    public boolean update(String sql, List<?> params)
    {
        // 表示当用户执行添加删除和修改的时候所影响数据库的行数
        int result = -1;
        try
        {
            pst = conn.prepareStatement(sql);
            int index = 1;
            // 填充sql语句中的占位符
            if (params != null && !params.isEmpty())
            {
                for (int i = 0; i < params.size(); i++)
                {
                    pst.setObject(index++, params.get(i));
                }
            }
            result = pst.executeUpdate();
        } catch (SQLException e)
        {

            e.printStackTrace();
        }

        return result > 0;
    }

    /**
     * @param sql 查询 sql语句
     * @return 成功返回ResultSet    
     */
    public ResultSet query(String sql)
    {
        try
        {
            pst = conn.prepareStatement(sql);
            rs = pst.executeQuery();// 返回查询结果
        } catch (SQLException e)
        {
            e.printStackTrace();
        }
        return rs;
    }

    /**
     * @param sql 查询sql语句
     * @param params 给sql中？传值，个数必须和sql中？匹配，
     * @return 成功返回ResultSet    
     */
    public ResultSet query(String sql, List<?> params)
    {
        try
        {
            int index = 1;
            pst = conn.prepareStatement(sql);
            if (params != null && !params.isEmpty())
            {
                for (int i = 0; i < params.size(); i++)
                {
                    pst.setObject(index++, params.get(i));
                }
            }
            rs = pst.executeQuery();// 返回查询结果
        } catch (SQLException e)
        {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        return rs;
    }

    /**
     * 关闭对象
     * @return
     */
    public void Close()
    {
        if (rs != null)
        {
            try
            {
                rs.close();
            } catch (SQLException e)
            {
                // TODO Auto-generated catch block
                e.printStackTrace();
            }
        }
        if (pst != null)
        {
            try
            {
                pst.close();
            } catch (SQLException e)
            {
                // TODO Auto-generated catch block
                e.printStackTrace();
            }
        }
        if (conn != null)
        {
            try
            {
                conn.close();
            } catch (SQLException e)
            {
                System.out.println("关闭数据库连接失败");
            }
        }
    }

    /**
     * 查询当前数据库所有表名称
     * @return list
     */
    public List<String> getTables()
    {
        List<String> list = new ArrayList<String>();
        try
        {
            PreparedStatement ps = conn.prepareStatement(SQLTables);
            ResultSet rs = ps.executeQuery();
            while (rs.next())
            {
                String tableName = rs.getString(1);
                list.add(tableName);
            }
            Close();
        } catch (SQLException e)
        {
            System.out.println("数据库查询表名失败了！");
        }

        return list;
    }

    /**
     * 根据表名返回字段属性
     * @param tableName 表名
     * @return
     */
    public List<String[]> getColumnDatas(String tableName)
    {
        String SQLColumns = "select column_name ,data_type,column_comment,numeric_scale,numeric_precision from information_schema.columns where table_name =  '"
                + tableName + "' " + "and table_schema =  '" + DBNAME + "'";

        List<String[]> columnList = new ArrayList<String[]>();
        try
        {
            PreparedStatement ps = conn.prepareStatement(SQLColumns);
            ResultSet rs = ps.executeQuery();

            while (rs.next())
            {
                String name = rs.getString(1);
                String type = rs.getString(2);
                String comment = rs.getString(3);
                String scale = rs.getString(4);
                String precision = rs.getString(5);
                type = getType(type, precision, scale);
                columnList.add(new String[] { name, type, comment });
            }

            Close();
        } catch (SQLException ex)
        {
            System.out.println(ex.getMessage());
        }
        return columnList;
    }

    /**
     * 根据sql字段属性转换为java数据类型
     * @param dataType
     * @param precision 精度
     * @param scale 大小
     * @return  java类型
     * @return
     */
    public String getType(String dataType, String precision, String scale)
    {
        dataType = dataType.toLowerCase();
        if (dataType.contains("char") || dataType.contains("text"))
            dataType = "String";
        else if (dataType.contains("bit"))
            dataType = "Boolean";
        else if (dataType.contains("bigint"))
            dataType = "Long";
        else if (dataType.contains("int"))
            dataType = "Integer";
        else if (dataType.contains("float"))
            dataType = "Float";
        else if (dataType.contains("double") || dataType.contains("real"))
            dataType = "Double";
        else if (dataType.contains("number"))
        {
            if ((scale.length() > 0) && (Integer.parseInt(scale) > 0))
                dataType = "Double";
            else if ((precision.length() > 0)
                    && (Integer.parseInt(precision) > 6))
                dataType = "Long";
            else
                dataType = "Integer";
        }
        else if (dataType.contains("decimal"))
            dataType = "Double";
        else if (dataType.contains("date"))
            dataType = "java.util.Date";
        else if (dataType.contains("time"))
            dataType = "java.sql.Timestamp";
        else if (dataType.contains("clob"))
            dataType = "java.sql.Clob";
        else
        {
            dataType = "Object";
        }
        return dataType;
    }

    /**
     * 根据列名生成set方法名称
     * @param colName
     * @return
     * @return
     */
   private  String createSetMethod(String colName)
   {
       return "set"+colName.substring(0, 1).toUpperCase()+ colName.substring(1);
   }

    /**
     * 把Resultset结果集转换为实体对象List
     * @方法名 ：rsToEntityList<br>
     * @方法描述 ：根据结果集（多条数据）映射 到 实体类集合<br>
     * @param <T>  泛型
     * @param clazz 实体类的Class
     * @param rs 查询的结果集
     * @return 返回类型 ：List<T>
     */
    public <T> List<T> rsToEntityList(Class<T> clazz, ResultSet rs)
    {
        ResultSetMetaData rsmd = null;
        List<T> list = new ArrayList<T>();
        String temp = "";
        Method s = null;
        T t = null;
        try
        {
            rsmd = rs.getMetaData();
            while (rs.next())
            {
                t = clazz.newInstance();
                for (int i = 1; i <= rsmd.getColumnCount(); i++)
                {
                    temp = rsmd.getColumnName(i);
                    String dataType = getType(rsmd.getColumnTypeName(i),
                            String.valueOf(rsmd.getPrecision(i)),
                            String.valueOf(rsmd.getScale(i)));
                    if (dataType.equals("Integer"))
                    {
                        s = clazz.getDeclaredMethod(createSetMethod(temp),Integer.class);
                        s.invoke(t, rs.getInt(temp));
                    }
                    else if (dataType.equals("String"))
                    {
                        s = clazz.getDeclaredMethod(createSetMethod(temp),
                                String.class);
                        s.invoke(t, rs.getString(temp));
                    }
                    else if (dataType.equals("Boolean"))
                    {
                        s = clazz.getDeclaredMethod(createSetMethod(temp),
                                Boolean.class);
                        s.invoke(t, rs.getBoolean(temp));
                    }
                    else if (dataType.equals("Long"))
                    {
                        s = clazz.getDeclaredMethod(createSetMethod(temp),
                                Long.class);
                        s.invoke(t, rs.getLong(temp));
                    }
                    else if (dataType.equals("Float"))
                    {
                        s = clazz.getDeclaredMethod(createSetMethod(temp),
                                Float.class);
                        s.invoke(t, rs.getFloat(temp));
                    }
                    else if (dataType.equals("Double"))
                    {
                        s = clazz.getDeclaredMethod(createSetMethod(temp),
                                Double.class);
                        s.invoke(t, rs.getDouble(temp));
                    }
                    else if (dataType.equals("java.util.Date"))
                    {
                        s = clazz.getDeclaredMethod(createSetMethod(temp),
                                java.util.Date.class);
                        s.invoke(t, rs.getDate(temp));
                    }
                    else if (dataType.equals("Object"))
                    {
                        s = clazz.getDeclaredMethod(createSetMethod(temp),
                                Object.class);
                        s.invoke(t, rs.getObject(temp));
                    }
                    else if (dataType.equals("java.sql.Clob"))
                    {
                        s = clazz.getDeclaredMethod(createSetMethod(temp),
                                java.sql.Clob.class);
                        s.invoke(t, rs.getClob(temp));
                    }
                }
                list.add(t);
            }
        } catch (SQLException e)
        {
            e.printStackTrace();
        } catch (IllegalArgumentException e)
        {
            e.printStackTrace();
        } catch (IllegalAccessException e)
        {
            e.printStackTrace();
        } catch (InvocationTargetException e)
        {
            e.printStackTrace();
        } catch (SecurityException e)
        {
            e.printStackTrace();
        } catch (NoSuchMethodException e)
        {
            e.printStackTrace();
        } catch (InstantiationException e)
        {
            e.printStackTrace();
        }
        return list;
    }
}

/**
     * 获取结果集，并将结果放在List中
     *
     * @param rs  结果集
     * @return List
     * 
     */
    public List<Object> rsToObjectList(ResultSet rs) {
        // 创建ResultSetMetaData对象
        ResultSetMetaData rsmd = null;

        // 结果集列数
        int columnCount = 0;
        try {
            rsmd = rs.getMetaData();

            // 获得结果集列数
            columnCount = rsmd.getColumnCount();
        } catch (SQLException e1) {
            System.out.println(e1.getMessage());
        }

        // 创建List
        List<Object> list = new ArrayList<Object>();

        try {
            // 将ResultSet的结果保存到List中
            while (rs.next()) {
                Map<String, Object> map = new HashMap<String, Object>();
                for (int i = 1; i <= columnCount; i++) {
                    map.put(rsmd.getColumnLabel(i), rs.getObject(i));
                }
                list.add(map);//每一个map代表一条记录，把所有记录存在list中
            }
        } catch (SQLException e) {
            System.out.println(e.getMessage());
        }

        return list;
    }

4. 测试

import java.util.List;

public class TestMain {
    public static void main(String[] args)
    {
        DBUtil db=new DBUtil();
        String sql="select * from epet";
        db.query(sql);
        List<Object> list=db.rsToObjectList(db.query(sql));

        System.out.println(list);

    }
}