JDBC通用的增、删、改、查方法

package cn.it58.util;

import cn.it58.pojo.Student;
import org.junit.jupiter.api.Test;
import java.lang.reflect.Field;
import java.sql.*;
import java.io.IOException;
import java.io.InputStream;
import java.util.Arrays;
import java.util.List;
import java.util.Map;
import java.util.Properties;
import java.util.concurrent.ConcurrentHashMap;
import java.util.concurrent.CopyOnWriteArrayList;

/**
 * @author geQiang
 * @version 1.0
 * @date 2019/5/23
 * @description cn.it58.util
 */
public class DBUtil {

    private String url;
    private String username;
    private String password;

    //执行数据库配置文件读取
    {
        Properties properties = new Properties();
        InputStream in = DBUtil.class.getClassLoader().getResourceAsStream("jdbc.properties");
        try {
            properties.load(in);
            url = properties.getProperty("url");
            //System.out.println(url);
            username = properties.getProperty("username");
            password = properties.getProperty("password");
        } catch (IOException e) {
            e.printStackTrace();
        }
    }


    /**
     * 获得数据库连接
     *
     * @return
     */
    public Connection getConnection() {
        try {
            Connection ct = DriverManager.getConnection(url, username, password);
            //关闭事务的自动提交
            ct.setAutoCommit(false);
            //设数据库事务的隔离级别 8 SERIALIZABLE
            ct.setTransactionIsolation(Connection.TRANSACTION_SERIALIZABLE);
            return ct;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }


    /**
     * 关闭数据库连接，有顺序要求
     */
    public void close(Connection ct, PreparedStatement st, ResultSet rt) {
        if (rt != null) {
            try {
                rt.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if (st != null) {
            try {
                st.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if (ct != null) {
            try {
                ct.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }


    /**
     * 获得数据库的表名称
     *
     * @return
     */
    public List<String> getAllTableNames() {
        List<String> list = new CopyOnWriteArrayList<>();
        String sql = " show tables ";
        Connection ct = getConnection();
        PreparedStatement st = null;
        ResultSet rt = null;
        try {
            st = ct.prepareStatement(sql);
            rt = st.executeQuery();
            while (rt.next()) {
                String tableName = rt.getString(1);
                list.add(tableName);
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            close(ct, st, rt);
        }
        return list;
    }

    @Test
    public void testGetAllTableNames() {
        for (String tableName : getAllTableNames()) {
            System.out.println(tableName);
        }
    }

    /**
     * 获得指定表名的字段名称和数据类型
     *
     * @param tableName
     * @return
     */
    public Map<String, String> getTableMetaData(String tableName) {
        Connection ct = getConnection();
        Map<String, String> map = new ConcurrentHashMap<>();
        ResultSet rt = null;
        try {
            DatabaseMetaData metaData = ct.getMetaData();
            rt = metaData.getColumns(ct.getCatalog(), "%", tableName, "%");
            System.out.println(ct.getCatalog());
            String columnName = "";
            String columnType = "";
            //map是无序的，所以设置游标顺序无用
            rt.setFetchDirection(ResultSet.FETCH_REVERSE);
            while (rt.next()) {
                map.put(rt.getString("COLUMN_NAME"), rt.getString("TYPE_NAME"));
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            close(ct, null, rt);
        }
        return map;
    }

    @Test
    public void testGetTableMetaData() {
        System.out.println(getTableMetaData("account"));
    }

    /**
     * 通用的查询对象集合的方法
     *
     * @param tClass 何种数据类型
     * @param sql    sql语句
     * @param args   sql参数集合
     * @return 存放根据sql语句查询出对象的集合
     */
    public List query(Class tClass, String sql, List args) {
        //存放查询记录的集合
        List list = new CopyOnWriteArrayList<>();
        //如果sql是null，直接返回空集合
        if (sql == null) {
            return list;
        }
        //打印sql语句
        System.out.println(sql);
        //获得连接对象
        Connection ct = getConnection();
        //定义sql预处理对象
        PreparedStatement st = null;
        //定义结果集对象
        ResultSet rt = null;
        try {
            //预处理sql语句
            st = ct.prepareStatement(sql);
            //添加参数，先判断，避免空指针
            if (args != null) {
                for (int i = 0; i < args.size(); i++) {
                    //变量sql参数集合，添加sql参数
                    st.setObject((i + 1), args.get(i));
                }
            }
            //执行查询操作
            rt = st.executeQuery();
            //获得结果集数据描述对象metaData
            ResultSetMetaData metaData = rt.getMetaData();
            //定义存放字段和字段值的map
            Map<String, Object> map = new ConcurrentHashMap<>();
            //获得当前类的所有属性集合
            Field[] fields = tClass.getDeclaredFields();
            //遍历结果集
            while (rt.next()) {
                //当前类的对象
                Object object = tClass.newInstance();
                //遍历结果集数据描述对象
                for (int i = 0; i < metaData.getColumnCount(); i++) {
                    //获得列名
                    String cloumnName = metaData.getColumnName(i + 1);
                    //获得列名对应的值
                    Object value = rt.getObject(cloumnName);
                    //把列名和列的值存放到map中
                    map.put(cloumnName, value);
                }
                //遍历map中的Entry实例
                for (Map.Entry<String, Object> entry : map.entrySet()) {
                    //获得实例的key值
                    String fieldName = entry.getKey();
                    //获得实例的value值
                    Object value = entry.getValue();
                    //遍历当前类的属性，属性和字段名必须一致，否则无法把字段和属性进行匹配
                    for (Field field : fields) {
                        //获得属性名
                        String name = field.getName();
                        //把属性名和字段名转小写，进行比对，如果相同，就把字段值赋值给属性
                        if (fieldName.toLowerCase().equals(name.toLowerCase())) {
                            //修改属性的可见性private-->public
                            field.setAccessible(true);
                            //当前类的对象object的name属性赋值为value
                            field.set(object, value);
                        }
                    }
                }
                list.add(object);
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            close(ct, st, rt);
        }
        return list;
    }

    @Test
    public void testQuery() {
        String sql = " select s.*,c.caption from student s,clazz c where s.classId = c.id and  s.id < ? ";
        List params = new CopyOnWriteArrayList<>();
        params.add(100);
        List list = query(Student.class, sql, params);
        for (Object obj : list) {
            System.out.println(obj);
        }
    }

    /**
     * 通用的查询总数的方法
     *
     * @param sql
     * @param args
     * @return
     */
    public int querySum(String sql, List args) {
        //如果sql是null，直接返回空集合
        if (sql == null) {
            return 0;
        }
        //打印sql语句
        System.out.println(sql);
        //获得连接对象
        Connection ct = getConnection();
        //定义sql预处理对象
        PreparedStatement st = null;
        //定义结果集对象
        ResultSet rt = null;
        try {
            st = ct.prepareStatement(sql);
            if (args != null) {
                for (int i = 0, size = args.size(); i < size; i++) {
                    st.setObject((i + 1), args.get(i));
                }
            }
            rt = st.executeQuery();
            if (rt.next()) {
                return rt.getInt(1);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            close(ct, st, rt);
        }
        return 0;
    }

    @Test
    public void testQuerySum() {
        String sql = " select count(1) from student  where studentName like ? ";
        List params = Arrays.asList("王%");
        System.out.println(querySum(sql, params));
    }

    /**
     * 通用的增、删、改操作
     *
     * @param sql
     * @param args
     * @param operation
     * @return
     */
    public Long update(String sql, List args, Operation operation) {
        if (sql == null) {
            return 0L;
        }
        //打印sql语句
        System.out.println(sql);
        //获得连接对象
        Connection ct = getConnection();
        //定义sql预处理对象
        PreparedStatement st = null;
        //定义结果集对象
        ResultSet rt = null;
        try {
            if (operation == Operation.INSERT) {
                st = ct.prepareStatement(sql, PreparedStatement.RETURN_GENERATED_KEYS);
            } else {
                st = ct.prepareStatement(sql);
            }
            if (args != null) {
                for (int i = 0, size = args.size(); i < size; i++) {
                    st.setObject((i + 1), args.get(i));
                }
            }
            long num = st.executeUpdate();
            ct.commit();
            if (operation == Operation.INSERT) {
                rt = st.getGeneratedKeys();
                if (rt.next()) {
                    long id = rt.getLong(1);
                    return id;
                }
            } else {
                return num;
            }
        } catch (SQLException e) {
            e.printStackTrace();
            try {
                ct.rollback();
            } catch (SQLException e1) {
                e1.printStackTrace();
            }
        } finally {
            close(ct, st, rt);
        }
        return 0L;
    }

    @Test
    public void add() {
        String sql = " insert into student (studentName,gender,classId,birthday) values(?,?,?,?) ; ";
        List args = Arrays.asList("root123", "男", 3, "1998-12-12 00:00:00");
        //49727
        System.out.println(update(sql, args, Operation.INSERT));
        /*
        mysql> select * from student where id = 49727;
        +-------+-------------+--------+---------+---------------------+
        | id    | studentName | gender | classId | birthday            |
        +-------+-------------+--------+---------+---------------------+
        | 49727 | root123     | 男     |       3 | 1998-12-12 00:00:00 |
        +-------+-------------+--------+---------+---------------------+
        */
    }

    @Test
    public void up() {
        String sql = " update  student set studentName=?,birthday=? where id =?  ";
        List args = Arrays.asList("root1234", "1997-10-12 00:00:00", 49727);
        //1
        System.out.println(update(sql, args, Operation.UPDATE));
        /*
        mysql> select * from student where id = 49727;
        +-------+-------------+--------+---------+----------------------+
        | id    | studentName | gender | classId | birthday             |
        +-------+-------------+--------+---------+----------------------+
        | 49727 | root1234     | 男     |       3 | 1997-10-12 00:00:00 |
        +-------+-------------+--------+---------+----------------------+
        */
    }

}