JDBC(4) : jdbc与dbcp2管理数据库工具类(轻量级版)

package cn.nordrassil.test2.util;


import org.apache.commons.dbcp2.BasicDataSourceFactory;

import javax.sql.DataSource;
import java.sql.*;
import java.util.ArrayList;
import java.util.LinkedList;
import java.util.List;
import java.util.Properties;

/**
 * @Auther: liyue
 * @Date: 2020/6/19 15:48
 * @Description:
 */
public class JdbcUtil {

    /**
     * 传递给JDBC驱动的用于建立连接的URL
     */
    private final static String url = "jdbc:mysql://127.0.0.1:3306/test?characterEncoding=UTF-8&useSSL=false";

    /**
     * 使用的JDBC驱动的完整有效的Java 类名
     */
    private final static String driverClassName = "com.mysql.jdbc.Driver";

    /**
     * 传递给JDBC驱动的用于建立连接的用户名
     */
    private final static String username = "root";

    /**
     * 传递给JDBC驱动的用于建立连接的密码
     */
    private final static String password = "123";

    /**
     * 初始化连接:连接池启动时创建的初始化连接数量,1.2版本后支持
     */
    private final static Integer initialSize = 4;

    /**
     * 最大活动连接数
     * 可以在这个池中同一时刻被分配的有效连接数的最大值，如设置为负数，则不限制
     */
    private final static Integer maxTotal = 30;

    /**
     * 最大空闲连接:连接池中容许保持空闲状态的最大连接数量,超过的空闲连接将被释放,
     * 如果设置为负数表示不限制
     */
    private final static Integer maxIdle = 10;

    /**
     * 最小空闲连接:连接池中容许保持空闲状态的最小连接数量,低于这个数量将创建新的连接,
     * 如果设置为0则不创建
     */
    private final static Integer minIdle = 5;

    /**
     * 从连接池获取一个连接时，最大的等待时间
     * 设置为-1时，如果没有可用连接，连接池会一直无限期等待，直到获取到连接为止。
     * 如果设置为N（毫秒），则连接池会等待N毫秒，等待不到，则抛出异常。
     */
    private final static Integer maxWaitMillis = 1000;

    /**
     * 是否在从池中取出连接前进行检验,如果检验失败,
     * 则从池中去除连接并尝试取出另一个.
     */
    private final static String testOnBorrow = "true";

    /**
     * SQL查询,用来验证从连接池取出的连接,在将连接返回给调用者之前.如果指定,
     * 则查询必须是一个SQL SELECT并且必须返回至少一行记录
     */
    private final static String validationQuery = "select 1";

    /**
     * 获取连接
     *
     * @return
     */
    public static Connection getConn() throws Exception {
        Connection conn = null;
        try {
            Class.forName(driverClassName);
            conn = DriverManager.getConnection(url, username, password);
        } catch (Exception e) {
            e.printStackTrace();
        }
        return conn;
    }

    /**
     * 获取连接池
     *
     * @return
     */
    public static DataSource getDataSourceInstance() throws Exception {
        Properties properties = new Properties();
        properties.put("driverClassName", driverClassName);
        properties.put("url", url);
        properties.put("username", username);
        properties.put("password", password);
        properties.put("initialSize", initialSize);
        properties.put("maxTotal", maxTotal);
        properties.put("maxIdle", maxIdle);
        properties.put("minIdle", minIdle);
        properties.put("maxWaitMillis", maxWaitMillis);
        properties.put("testOnBorrow", testOnBorrow);
        properties.put("validationQuery", validationQuery);
        //创建dataSource对象
        DataSource dataSource = BasicDataSourceFactory.createDataSource(properties);
        return dataSource;
    }


    /**
     * 获取建表语句
     *
     * @param conn
     * @param table
     * @return
     */
    public static String showCreateTableSql(Connection conn, String table) throws Exception {
        ResultSet rs = null;
        PreparedStatement ps = null;
        try {
            long t1 = System.currentTimeMillis();
            String sql = "SHOW CREATE TABLE " + table;
            ps = conn.prepareStatement(sql);
            rs = ps.executeQuery();
            String str = null;
            while (rs.next()) {
                String string = rs.getString(2);
                if (string != null)
                    str = string;
            }
            return str;
        } finally {
            if (rs != null) {
                try {
                    rs.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if (ps != null) {
                try {
                    ps.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if (conn != null) {
                try {
                    conn.close();
                } catch (Exception e) {
                    e.printStackTrace();
                }
            }
        }
    }

    /**
     * 通用查询
     *
     * @param sql
     * @param conn
     * @return
     * @throws Exception
     */
    public static List<List<String>> query(String sql, Connection conn, Object... params) throws Exception {
        List<List<String>> lists = new LinkedList<>();
        ResultSet rs = null;
        PreparedStatement ps = null;
        try {
            ps = conn.prepareStatement(sql);
            for (int i = 0; i < params.length; i++) {
                ps.setObject(i, params[i]);
            }
            rs = ps.executeQuery();
            List<String> titles = new LinkedList<>();
            boolean b = Boolean.TRUE;
            while (rs.next()) {
                ResultSetMetaData rsmd = rs.getMetaData();
                int columnCount = rsmd.getColumnCount();
                List<String> list = new LinkedList<>();
                for (int i = 1; i <= columnCount; i++) {
                    if (b) {
                        titles.add(rsmd.getColumnName(i));
                    }
                    list.add(rs.getString(i));
                }
                b = Boolean.FALSE;
                lists.add(list);
            }
            List<List<String>> result = new ArrayList<>();
            result.add(titles);
            result.addAll(lists);
            return result;
        } finally {
            if (rs != null) {
                try {
                    rs.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if (ps != null) {
                try {
                    ps.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if (conn != null) {
                try {
                    conn.close();
                } catch (Exception e) {
                    e.printStackTrace();
                }
            }
        }
    }


    /**
     * 查询返回第一行第一列数值
     *
     * @param conn
     * @param sql
     * @param params
     * @return
     */
    public static String selectOneField(Connection conn, String sql, Object... params) throws Exception {
        ResultSet rs = null;
        PreparedStatement ps = null;
        try {
            ps = conn.prepareStatement(sql);
            for (int i = 0; i < params.length; i++) {
                ps.setObject(i, params[i]);
            }
            rs = ps.executeQuery();
            String result = null;
            while (rs.next()) {
                result = rs.getString(1);
                break;
            }
            return result;
        } catch (SQLException e) {
            e.printStackTrace();
            return null;
        } finally {
            if (rs != null) {
                try {
                    rs.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if (ps != null) {
                try {
                    ps.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if (conn != null) {
                try {
                    conn.close();
                } catch (Exception e) {
                    e.printStackTrace();
                }
            }
        }
    }


    /**
     * 执行SQL,返回影响行数
     *
     * @param conn
     * @param sql
     * @param params
     * @return
     * @throws SQLException
     */
    public static int execute(Connection conn, String sql, Object... params) throws SQLException {
        PreparedStatement ps = null;
        try {
            ps = conn.prepareStatement(sql);
            for (int i = 0; i < params.length; i++) {
                ps.setObject(i + 1, params[i]);
            }
            return ps.executeUpdate();
        } finally {
            if (ps != null) {
                try {
                    ps.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if (conn != null) {
                try {
                    conn.close();
                } catch (Exception e) {
                    e.printStackTrace();
                }
            }
        }
    }


    /**
     * 统计数量
     *
     * @param conn
     * @param sql
     * @param params
     * @return
     */
    public static int count(Connection conn, String sql, Object... params) throws Exception {
        ResultSet rs = null;
        PreparedStatement ps = null;
        try {
            ps = conn.prepareStatement(sql);
            for (int i = 0; i < params.length; i++) {
                ps.setObject(i, params[i]);
            }
            rs = ps.executeQuery();
            int result = 0;
            while (rs.next()) {
                result = rs.getInt(1);
            }
            return result;
        } finally {
            if (rs != null) {
                try {
                    rs.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if (ps != null) {
                try {
                    ps.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if (conn != null) {
                try {
                    conn.close();
                } catch (Exception e) {
                    e.printStackTrace();
                }
            }
        }
    }


    /**
     * 返回查询结果第一列
     *
     * @param conn
     * @param sql
     * @param params
     * @return
     */
    public static List<String> selectListOneField(Connection conn, String sql, Object... params) throws Exception {
        ResultSet rs = null;
        PreparedStatement ps = null;
        try {
            ps = conn.prepareStatement(sql);
            for (int i = 0; i < params.length; i++) {
                ps.setObject(i, params[i]);
            }
            rs = ps.executeQuery();
            List<String> result = new LinkedList<>();
            while (rs.next()) {
                result.add(rs.getString(1));
            }
            return result;
        } finally {
            if (rs != null) {
                try {
                    rs.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if (ps != null) {
                try {
                    ps.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if (conn != null) {
                try {
                    conn.close();
                } catch (Exception e) {
                    e.printStackTrace();
                }
            }
        }
    }

}

 

依赖

<dependency>
            <groupId>org.apache.commons</groupId>
            <artifactId>commons-dbcp2</artifactId>
            <version>2.6.0</version>
        </dependency>
        <dependency>
            <groupId>org.apache.commons</groupId>
            <artifactId>commons-lang3</artifactId>
            <version>3.3.1</version>
        </dependency>

 

 

END。