VIPJAVA/gn_oa_vip27/src/main/java/com/sf/dao/BaseDao.java

package com.sf.dao;
import java.lang.reflect.Field;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

public class BaseDao<T> {


    /**
     * 通用增删改
     * @throws SQLException
     */
    public void update(Connection connection,String sql,Object...args) throws SQLException {
        //获取PreparedStatement并且预编译sql语句
        PreparedStatement ps = connection.prepareStatement(sql);
        //给SQL语句中的占位符赋值
        if(args!=null &&args.length>0) {
            for (int i = 0; i < args.length; i++) {
                ps.setObject(i+1, args[i]);
            }
        }
        //执行
        ps.executeUpdate();
    }


    /**
     * 查询列表
     * @throws SQLException
     * @throws IllegalAccessException
     * @throws InstantiationException
     * @throws SecurityException
     * @throws NoSuchFieldException
     */
    public List<T> getList(Connection connection,Class<T> clazz,String sql,Object...args) throws SQLException, InstantiationException, IllegalAccessException, NoSuchFieldException, SecurityException{
        List<T> list = new ArrayList<>();
        //获取PreparedStatement并且预编译sql语句
        PreparedStatement ps = connection.prepareStatement(sql);
        //给SQL语句中的占位符赋值
        if(args!=null &&args.length>0) {
            for (int i = 0; i < args.length; i++) {
                ps.setObject(i+1, args[i]);
            }
        }
        //执行
        ResultSet rs = ps.executeQuery();
        //获取数据库表的元数据信息
        ResultSetMetaData rsmd = rs.getMetaData();
        //获取表的列的总数
        int columnCount = rsmd.getColumnCount();

        //从结果集中获取每一个数据
        while(rs.next()) {
            //创建T对象
            T t = clazz.newInstance();
            //获取列名,jdbc提供了使用列的下标获取列名的方式
            for (int i = 0; i < columnCount; i++) {
                String columnLabel = rsmd.getColumnLabel(i+1);
                //通过列名获取列值
                Object columnValue = rs.getObject(columnLabel);

                //获取T对象中属性的对象
                Field field = clazz.getDeclaredField(columnLabel);//获取当前类中的属性列表
                //开启权限
                field.setAccessible(true);
                //给当前属性赋值
                field.set(t, columnValue);
            }
            list.add(t);
        }
        return list;
    }

    /**
     * 查询单个对象
     * @throws SQLException
     * @throws IllegalAccessException
     * @throws IllegalArgumentException
     * @throws SecurityException
     * @throws NoSuchFieldException
     * @throws InstantiationException
     */
    public T get(Connection connection,Class<T> clazz,String sql,Object...args) throws SQLException, IllegalArgumentException, IllegalAccessException, NoSuchFieldException, SecurityException, InstantiationException {
        T t = null;

        //获取PreparedStatement并且预编译sql语句
        PreparedStatement ps = connection.prepareStatement(sql);
        //给SQL语句中的占位符赋值
        if(args!=null &&args.length>0) {
            for (int i = 0; i < args.length; i++) {
                ps.setObject(i+1, args[i]);
            }
        }
        //执行
        ResultSet rs = ps.executeQuery();
        //获取数据库表的元数据信息
        ResultSetMetaData rsmd = rs.getMetaData();
        //获取表的列的总数
        int columnCount = rsmd.getColumnCount();

        //从结果集中获取每一个数据
        if(rs.next()) {
            //创建T对象
            t = clazz.newInstance();
            //获取列名,jdbc提供了使用列的下标获取列名的方式
            for (int i = 0; i < columnCount; i++) {
                String columnLabel = rsmd.getColumnLabel(i+1);
                //通过列名获取列值
                Object columnValue = rs.getObject(columnLabel);

                //获取T对象中属性的对象
                Field field = clazz.getDeclaredField(columnLabel);//获取当前类中的属性列表
                //开启权限
                field.setAccessible(true);
                //给当前属性赋值
                field.set(t, columnValue);
            }
        }
        return t;
    }


    /**
     * 获取数据库表的总记录数
     * @throws SQLException
     * SELECT COUNT(*) FROM employee;
     */
    public int getCount(Connection connection,String sql) throws SQLException {
        Statement statement = connection.createStatement();
        ResultSet rs = statement.executeQuery(sql);
        if(rs.next()) {
            int count = rs.getInt("count");
            return count;
        }
        return 0;
    }
}