JDBC(3) : jdbc和dbcp封装数据库管理工具类(封装对象及自动装载配置版)

package com.aliyun.task.monitor.util;

import org.apache.commons.dbcp2.BasicDataSourceFactory;
import org.apache.commons.lang3.StringUtils;
import org.springframework.core.io.support.PropertiesLoaderUtils;

import javax.sql.DataSource;
import java.lang.reflect.Field;
import java.sql.*;
import java.util.LinkedList;
import java.util.List;
import java.util.Properties;

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

    /**
     * 获取连接
     *
     * @param propertiesPath 为resources下相对路径
     * @return
     */
    public static Connection getConn(String propertiesPath) {
        Connection conn = null;
        try {
            Properties properties = PropertiesLoaderUtils.loadAllProperties(propertiesPath);
            Class.forName("com.mysql.jdbc.Driver");
            conn = DriverManager.getConnection(properties.getProperty("url"), properties.getProperty("username"), properties.getProperty("password"));
        } catch (Exception e) {
            e.printStackTrace();
        }
        return conn;
    }

    /**
     * 获取连接池
     *
     * @param propertiesPath 为resources下相对路径
     * @return
     */
    public static DataSource getDataSourceInstance(String propertiesPath) {
        DataSource dataSource = null;
        try {
            Properties properties = PropertiesLoaderUtils.loadAllProperties(propertiesPath);
            //创建dataSource对象
            dataSource = BasicDataSourceFactory.createDataSource(properties);
        } catch (Exception e) {
            e.printStackTrace();
        }
        return dataSource;
    }


    /**
     * 获取建表语句
     *
     * @param conn
     * @param table
     * @return
     */
    public static String showCreateTableSql(Connection conn, String table) {
        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;
        } 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();
                }
            }
        }
    }


    /**
     * 查询返回封装对象列表
     *
     * @param conn
     * @param sql
     * @param T
     * @param params
     * @param <T>
     * @return
     */
    public static <T> List<T> select(Connection conn, String sql, Class<T> T, Object... params) {
        ResultSet rs = null;
        PreparedStatement ps = null;
        try {
            ps = conn.prepareStatement(sql);
            for (int i = 0; i < params.length; i++) {
                ps.setObject(i, params[i]);
            }
            List<T> list = new LinkedList<>();
            rs = ps.executeQuery();
            while (rs.next()) {
                T t = rsToObject(rs, T);
                list.add(t);
            }
            return list;
        } 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();
                }
            }
        }
    }


    /**
     * 查询返回一个对象
     *
     * @param conn
     * @param sql
     * @param T
     * @param params
     * @param <T>
     * @return
     */
    public static <T> T selectOne(Connection conn, String sql, Class<T> T, Object... params) {
        ResultSet rs = null;
        PreparedStatement ps = null;
        try {
            long t1 = System.currentTimeMillis();
            ps = conn.prepareStatement(sql);
            for (int i = 0; i < params.length; i++) {
                ps.setObject(i, params[i]);
            }
            rs = ps.executeQuery();
            T t = null;
            while (rs.next()) {
                t = rsToObject(rs, T);
                break;
            }
            long t2 = System.currentTimeMillis();

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


    /**
     * 查询返回第一行第一列数值
     *
     * @param conn
     * @param sql
     * @param params
     * @return
     */
    public static String selectOneField(Connection conn, String sql, Object... params) {
        ResultSet rs = null;
        PreparedStatement ps = null;
        try {
            long t1 = System.currentTimeMillis();
            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) {
        ResultSet rs = null;
        PreparedStatement ps = null;
        try {
            long t1 = System.currentTimeMillis();
            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);
            }
            long t2 = System.currentTimeMillis();

            return result;
        } catch (SQLException e) {
            e.printStackTrace();
            return 0;
        } 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) {
        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;
        } 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();
                }
            }
        }
    }


    /**
     * 封装对象
     *
     * @param rs
     * @param clazz
     * @param <T>
     * @return
     */
    private static <T> T rsToObject(ResultSet rs, Class<T> clazz) {
        try {
            if (null == rs) {
                return null;
            }
            List<String> names = getObjectAttributeNames(clazz);
            StringBuffer json = new StringBuffer();
            json.append("{");
            for (String name : names) {
                json.append("\"").append(name);
                String value = null;
                if (StringUtils.isBlank(value)) {
                    String newName = bigLetterToSmallAndBeforeAdd_(name);
                    try {
                        value = rs.getString(newName);
                    } catch (Exception e) {
                        value = "";
                    }
                }
                if (StringUtils.isBlank(value)) {
                    try {
                        value = rs.getString(name);
                    } catch (Exception e) {
                    }
                }
                json.append("\":\"").append(value).append("\"").append(",");
            }
            json.delete(json.length() - 1, json.length());
            json.append("}");
            T t = JsonUtils.jsonToPojo(json.toString().replaceAll("null", "").replaceAll("\\\r\\\n", ""), clazz);
            return t;
        } catch (Exception e) {
            e.printStackTrace();
            throw new RuntimeException();
        }

    }

    /**
     * 大写字母转小写并在该字母前面加_
     *
     * @param name
     * @return
     */
    private static String bigLetterToSmallAndBeforeAdd_(String name) {
        StringBuffer stringBuffer = new StringBuffer();
        char[] chars = name.toCharArray();
        for (char ch : chars) {
            boolean digit = Character.isUpperCase(ch);
            if (digit) {
                String str = String.valueOf(ch);
                String lstr = str.toLowerCase();
                stringBuffer.append("_");
                stringBuffer.append(lstr);
            } else {
                stringBuffer.append(ch);
            }
        }
        return stringBuffer.toString();
    }


    /**
     * 获取对象的所有属性值
     *
     * @param clazzT
     * @param <T>
     * @return
     */
    public static <T> List<String> getObjectAttributeNames(Class<T> clazzT) {
        List<String> list = new LinkedList<>();
        try {
            //根据类名获得其对应的Class对象 写上你想要的类名就是了 注意是全名 如果有包的话要加上 比如java.Lang.String
            Class clazz = Class.forName(clazzT.getName());
            //根据Class对象获得属性 私有的也可以获得
            Field[] fields = clazz.getDeclaredFields();
            for (Field f : fields) {
                list.add(f.getName());
                // 类型
                // f.getType();
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return list;
    }


}

 

依赖

org.apache.commons.lang3

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

org.springframework.core

org.apache.commons.dbcp2 

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

JsonUtils

https://blog.csdn.net/Lxinccode/article/details/84881943