DAO及相关实现类

一、DAO

• DAO：Data Access Object访问数据信息的类和接口，包括了对数据的CRUD（Create、Retrival、Update、Delete），而不包含任何业务相关的信息。有时也称作：BaseDAO。
• 作用：为了实现功能的模块化，更有利于代码的维护和升级。

二、DAO层代码实现

/*
* 封装针对数据表的通用操作
* */
public abstract class DAO<T> {
    private Class<T> clazz = null;

    //获取当前DAO的子类继承的父类中的泛型
    {
        Type genericSuperclass = this.getClass().getGenericSuperclass();
        ParameterizedType parameterizedType = (ParameterizedType) genericSuperclass;

        Type[] typeArguments = parameterizedType.getActualTypeArguments();//获取了父类的泛型参数
        clazz = (Class<T>) typeArguments[0];
    }

    //通用的增删改操作，事务处理
    //将一次数据库连接当做参数传入进来
    public void update(Connection connection, String sql, Object ...args) {

        PreparedStatement preparedStatement = null;
        try {

            //1、预编译sql语句，返回PreparedStatement的实例
            preparedStatement = connection.prepareStatement(sql);

            //2、填充占位符
            for (int i = 0; i < args.length; i++) {
                preparedStatement.setObject(i + 1, args[i]);
            }

            //3、执行
            preparedStatement.execute();

        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            //4、资源的关闭
            JDBCUtils.closeResource(null,preparedStatement);
        }
    }



    public T getInstance(Connection connection, String sql, Object ...args){

        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        try {

            preparedStatement = connection.prepareStatement(sql);
            for (int i = 0; i < args.length; i++) {
                preparedStatement.setObject(i + 1,args[i]);
            }

            //执行，获取返回的结果集
            resultSet = preparedStatement.executeQuery();

            ResultSetMetaData metaData = resultSet.getMetaData();       //获取结果集的元数据

            int columnCount = metaData.getColumnCount();                //通过元数据获取结果集中的列数

            if (resultSet.next()){
                T t = clazz.newInstance();
                //处理结果集一行数据中的每一个列
                for (int i = 0; i < columnCount; i++) {
                    //获取每个列的的值：通过resultSet结果集
                    Object columnValue = resultSet.getObject(i + 1);

                    //获取每个列的列名：通过metaData元数据
                    //String columnName = metaData.getColumnName(i + 1);

                    //获取每个列的列名的别名：通过metaData元数据
                    String columnLabel = metaData.getColumnLabel(i + 1);

                    //通过反射，给t对象指定的columnName属性，赋值为columnValue
                    Field declaredField = clazz.getDeclaredField(columnLabel);
                    declaredField.setAccessible(true);
                    declaredField.set(t,columnValue);
                }
                return t;
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JDBCUtils.closeResource(null,preparedStatement,resultSet);
        }

        return null;
    }




    public List<T> getForList(Connection connection, String sql, Object ...args){

        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        try {

            preparedStatement = connection.prepareStatement(sql);
            for (int i = 0; i < args.length; i++) {
                preparedStatement.setObject(i + 1,args[i]);
            }

            //执行，获取返回的结果集
            resultSet = preparedStatement.executeQuery();

            ResultSetMetaData metaData = resultSet.getMetaData();       //获取结果集的元数据

            int columnCount = metaData.getColumnCount();                //通过元数据获取结果集中的列数

            ArrayList<T> list = new ArrayList<>();                      //创建集合对象

            while (resultSet.next()){
                T t = clazz.newInstance();
                //处理结果集一行数据中的每一个列：给t对象指定属性赋值
                for (int i = 0; i < columnCount; i++) {
                    //获取每个列的的值：通过resultSet结果集
                    Object columnValue = resultSet.getObject(i + 1);

                    //获取每个列的列名：通过metaData元数据
                    //String columnName = metaData.getColumnName(i + 1);

                    //获取每个列的列名的别名：通过metaData元数据
                    String columnLabel = metaData.getColumnLabel(i + 1);

                    //通过反射，给t对象指定的columnName属性，赋值为columnValue
                    Field declaredField = clazz.getDeclaredField(columnLabel);
                    declaredField.setAccessible(true);
                    declaredField.set(t,columnValue);
                }
                list.add(t);
            }
            return list;
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JDBCUtils.closeResource(null,preparedStatement,resultSet);
        }

        return null;
    }


    //用于查询特殊值的通用方法
    public <E> E getValue(Connection connection, String sql, Object ...args){

        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        try {
            preparedStatement = connection.prepareStatement(sql);
            for (int i = 0; i < args.length; i++) {
                preparedStatement.setObject(i + 1, args[i]);
            }

            resultSet = preparedStatement.executeQuery();
            if (resultSet.next()){
                return (E) resultSet.getObject(1);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JDBCUtils.closeResource(null,preparedStatement,resultSet);
        }

        return null;
    }

}

三、CustomerDAO代码实现

/*此接口用于规范针对于Customers表的常用操作*/
public interface CustomerDAO {
    //将Customer对象添加到数据库中
    public abstract void insert(Connection connection, Customer customer);


    //删除指定的id，删除表中的一条记录
    public abstract void deleteById(Connection connection, int id);


    //修改指定对象的数据表中的记录
    public abstract void updateById(Connection connection, Customer customer);


    //查询指定的id对应的Customer对象
    public abstract Customer getCustomerById(Connection connection, int id);


    //查询表中的所有记录构成的集合
    public abstract List<Customer> getAll(Connection connection);


    //返回数据表中数据的条目数
    public abstract Long getCount(Connection connection);


    //返回数据表中最大的生日
    public abstract Date getMaxBirth(Connection connection);

}

四、CustomerDAOImpl代码实现

public class CustomerDAOImpl extends DAO<Customer> implements CustomerDAO {



    @Override
    public void insert(Connection connection, Customer customer) {
        String sql = "insert into customers(name,email,birth)values(?,?,?)";
        update(connection,sql,customer.getName(),customer.getEmail(),customer.getBirth());
    }

    @Override
    public void deleteById(Connection connection, int id) {

        String sql = "delete from customers where id = ?";
        update(connection,sql,id);
    }

    @Override
    public void updateById(Connection connection, Customer customer) {
        String sql = "update customers set name = ?, email = ?, birth = ? where id = ?";
        update(connection,sql,customer.getName(),customer.getEmail(),customer.getBirth(),customer.getId());
    }

    @Override
    public Customer getCustomerById(Connection connection, int id) {
        String sql = "select id,name,email,birth from customers where id = ?";
        Customer customer = getInstance(connection, sql, id);
        return customer;

    }

    @Override
    public List<Customer> getAll(Connection connection) {
        String sql = "select id,name,email,birth from customers";
        List<Customer> customerList = getForList(connection,sql);
        return customerList;
    }

    @Override
    public Long getCount(Connection connection) {
        String sql = "select count(*) from customers";
        return getValue(connection,sql);

    }

    @Override
    public Date getMaxBirth(Connection connection) {
        String sql = "select max(birth) from customers";
        return getValue(connection,sql);
    }
}