JDBC--基础的DAO类与一个表接口的实现类

最新推荐文章于 2024-09-14 19:55:29 发布

孔雀南飞梦

最新推荐文章于 2024-09-14 19:55:29 发布

阅读量141

点赞数

分类专栏： JDBC 文章标签：数据库

本文链接：https://blog.csdn.net/qq_52360788/article/details/120527026

版权

JDBC 专栏收录该内容

8 篇文章 0 订阅

订阅专栏

这篇博客展示了如何使用Java实现一个抽象的DAO基类，包含数据库的增删改查操作，并实现了针对Customer表的特定接口。内容涵盖了PreparedStatement的使用、事务处理、结果集的遍历以及反射机制来映射数据库结果到对象。此外，还给出了一个具体的CustomerDAO实现类，用于演示如何进行数据库交互。

摘要由CSDN通过智能技术生成

抽象的DAO类

public abstract class BaseDao {
    //重载的关闭资源方法
    public void releaseResources(Connection connection, Statement statement,ResultSet resultSet) throws SQLException {
        if (connection!=null){
            connection.close();
        }
        if(statement!=null){
            statement.close();
        }
        if(resultSet!=null){
            resultSet.close();
        }

    }
    //更新数据库的方法，增，删改（考虑数据库事务）
    public  int updateDataTransaction(Connection connection,String sql,Object ...args) throws SQLException, IOException, ClassNotFoundException {
        int count=0;
        //获取预编译的statement对象
        PreparedStatement preparedStatement = connection.prepareStatement(sql);
        //填充占位符
        if(args.length!=0){
            for(int i=0;i<args.length;i++){
                preparedStatement.setObject(i+1,args[i]);
            }
        }
        //执行
        count = preparedStatement.executeUpdate();//返回影响的行数
        //关闭资源,暂时 先不关闭数据库连接连接
        DatabaseConnectivity.releaseResources(null,preparedStatement);
        return count;
    }
    //考虑事务处理的查询多个表的单个对象的方法
    public <T> T selectTables_processing(Connection connection, Class<T> tClass, String sql, Object ...args) throws SQLException, IllegalAccessException, NoSuchFieldException, InvocationTargetException, InstantiationException, NoSuchMethodException {
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        //获取预编译的statement对象
        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对象
            Constructor<T> constructor = tClass.getConstructor();
            T t = constructor.newInstance();//动态创建T类型的对象
            for(int i=1;i<=columnCount;i++){
                //返回当前列属性值
                Object object = resultSet.getObject(i);
                //获取当前列的别名
                String columnLabel = metaData.getColumnLabel(i);
                //通过反射，给对应的属性赋值
                Class<?> aClass = t.getClass();//返回一个类模板
                Field declaredField = aClass.getDeclaredField(columnLabel);
                declaredField.setAccessible(true);//除去私有权限
                declaredField.set(t,object);//赋值
            }
            //释放资源但不关闭连接
            DatabaseConnectivity.releaseResources(null,preparedStatement,resultSet);
            return t;
        }

        return null;

    }
    //    查询任意表，返回多个对象的集合
    public  <T> List<T> selectListTables(Connection connection,Class<T> tClass, String sql, Object ...args){
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        try {
            //获取预编译的statement对象
            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> tList = new ArrayList<>();
            //将查询结果取出
            while (resultSet.next()){
                //创建一个空参的T对象
                Constructor<T> constructor = tClass.getConstructor();
                T t = constructor.newInstance();//动态创建T类型的对象

                for(int i=1;i<=columnCount;i++){
                    //返回当前列属性值
                    Object object = resultSet.getObject(i);
                    //获取当前列的别名
                    String columnLabel = metaData.getColumnLabel(i);
                    //通过反射，给对应的属性赋值
                    Class<?> aClass = t.getClass();//返回一个类模板
                    Field declaredField = aClass.getDeclaredField(columnLabel);
                    declaredField.setAccessible(true);//除去私有权限
                    declaredField.set(t,object);//赋值

                }
                //将当前取出的对象存入集合
                tList.add(t);
            }
            return tList;
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } catch (NoSuchFieldException e) {
            e.printStackTrace();
        } catch (IllegalAccessException e) {
            e.printStackTrace();
        } catch (NoSuchMethodException e) {
            e.printStackTrace();
        } catch (InvocationTargetException e) {
            e.printStackTrace();
        } catch (InstantiationException e) {
            e.printStackTrace();
        } finally {
            //关闭资源
            try {
                DatabaseConnectivity.releaseResources(null,preparedStatement,resultSet);
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
        return null;
    }
    //查询单行单列数据的方法
    public <T> T getValue(Connection connection,Class<T> tClass,String sql,Object... args) throws SQLException {
        //获取预编译的statement对象
        PreparedStatement preparedStatement = connection.prepareStatement(sql);
        //填充占位符
        for(int i=0;i<args.length;i++){
            preparedStatement.setObject(i+1,args[i]);
        }
        //执行并返回结果集
        ResultSet resultSet = preparedStatement.executeQuery();
        if(resultSet.next()){
            Object object = resultSet.getObject(1);
            //释放资源
            releaseResources(null,preparedStatement,resultSet);
            return (T)object;
        }
        //释放资源
        releaseResources(null,preparedStatement,resultSet);
        return null;
    }
}

对应一个表的接口

public interface CustomInterface {
    //将Customer对象添加到数据表中
    void insert(Connection connection,Customer customer) throws SQLException;
    //通过对应的id删除数据表中的数据
    void deleteById(Connection connection,int id) throws SQLException;
    //修改表中的一条记录，将对应对象相同的id数据改为对象的数据
    void updateById(Connection connection,Customer customer) throws SQLException;
    //通过指定的id返回一个对象
    Customer getCustomer(Connection connection,int id) throws SQLException, ClassNotFoundException, NoSuchMethodException, IllegalAccessException, InvocationTargetException, InstantiationException, NoSuchFieldException;
    //查询表中的所有记录
    List<Customer> getAllCustomers(Connection connection);
    //返回数据表中数据的条目数
    Long getCount(Connection connection) throws SQLException;
    //返回最大的生日
    Date getMaxBirth(Connection connection) throws SQLException;
}

及其实现类

public class CustomerDAOImpl extends BaseDao implements CustomInterface{
    @Override
    public void insert(Connection connection, Customer customer) throws SQLException {
        String sql="INSERT INTO customers(`name`,email,birth) VALUES(?,?,?);";
        //获取预编译的statement对象
        PreparedStatement preparedStatement = connection.prepareStatement(sql);
        //填充占位符
        preparedStatement.setObject(1,customer.getName());
        preparedStatement.setObject(2, customer.getEmail());
        preparedStatement.setObject(3, customer.getBirth());
        //执行
        preparedStatement.execute();
        releaseResources(null,preparedStatement, null);
    }

    @Override
    public void deleteById(Connection connection, int id) throws SQLException {
        String sql="DELETE FROM customers WHERE id=?;";
        PreparedStatement preparedStatement = connection.prepareStatement(sql);
        //填充占位符
        preparedStatement.setObject(1,id);
        //执行
        preparedStatement.execute();
    }

    @Override
    public void updateById(Connection connection, Customer customer) throws SQLException {
        String sql="UPDATE customers set `name`=?,email=?,birth=? WHERE id=?";
        PreparedStatement preparedStatement = connection.prepareStatement(sql);
        //填充
        preparedStatement.setObject(1, customer.getName());
        preparedStatement.setObject(2, customer.getEmail());
        preparedStatement.setObject(3, customer.getBirth());
        preparedStatement.setObject(4, customer.getId());
        //执行
        preparedStatement.execute();
    }

    @Override
    public Customer getCustomer(Connection connection, int id) throws SQLException, ClassNotFoundException, NoSuchMethodException, IllegalAccessException, InvocationTargetException, InstantiationException, NoSuchFieldException {
        String sql="SELECT id,email,birth,`name` FROM customers where id=?;";
        PreparedStatement preparedStatement = connection.prepareStatement(sql);
        //填充
        preparedStatement.setObject(1,id);
        //获取结果集
        ResultSet resultSet = preparedStatement.executeQuery();
        //获取元数据
        ResultSetMetaData metaData = resultSet.getMetaData();
        //获取列数
        int columnCount = metaData.getColumnCount();
        if(resultSet.next()){
            Customer customer = new Customer();
            for(int i=1;i<=columnCount;i++){
                String columnLabel = metaData.getColumnLabel(i);//获取列的别名
                Object object = resultSet.getObject(i);//获取对应的列值
                //反射
                Class<?> customer1 = customer.getClass();//获取类模板
                Field declaredField = customer1.getDeclaredField(columnLabel);//获取对应属性模板
                declaredField.setAccessible(true);
                declaredField.set(customer,object);//赋值
            }
            //释放资源
            releaseResources(null,preparedStatement, resultSet);
            return customer;
        }
        //释放资源
        releaseResources(null,preparedStatement, resultSet);
        return null;
    }

    @Override
    public List<Customer> getAllCustomers(Connection connection) {
        String sql="SELECT id,email,birth,`name` FROM customers;";
        //返回所有 对象的集合
        List<Customer> customers = selectListTables(connection, Customer.class, sql);
        return customers;
    }

    @Override
    public Long getCount(Connection connection) throws SQLException {
        //返回条目数
        String sql="SELECT COUNT(*) 'count' FROM customers;";
        Long value = getValue(connection, Long.class, sql);
        return value;
    }

    @Override
    public Date getMaxBirth(Connection connection) throws SQLException {
        //返回最大的生日
        String sql="SELECT MIN(birth) 'maxBirth' FROM customers;";
        Date value = getValue(connection, Date.class, sql);
        return value;
    }
}
class T{
    public static void main(String[] args) throws SQLException, IOException, ClassNotFoundException, InvocationTargetException, NoSuchMethodException, NoSuchFieldException, InstantiationException, IllegalAccessException {
        CustomerDAOImpl customerDAO = new CustomerDAOImpl();
        //获取数据库连接
        Connection testConnection = DatabaseConnectivity.getTestConnection();
        Customer customer = customerDAO.getCustomer(testConnection, 2);
        System.out.println(customer);
//        List<Customer> allCustomers = customerDAO.getAllCustomers(testConnection);
//        allCustomers.forEach(new Consumer<Customer>() {
//            @Override
//            public void accept(Customer customer) {
//                System.out.println(customer);
//            }
//        });
        //返回条目数
        Long count = customerDAO.getCount(testConnection);
        System.out.println(count);

        //插入操作
//        Customer hr = new Customer("HR", "16644666@qq.com", new Date(155882322));
//        customerDAO.insert(testConnection, hr);
//        //删除25号
//        customerDAO.deleteById(testConnection, 26);
        System.out.println(customerDAO.getMaxBirth(testConnection));
    }
}