• 【JDBC核心】DAO 相关


    DAO 相关

    概念

    DAO:Data Access Object 访问数据信息的类和接口,包括了对数据的 CRUD(Create、Retrival、Update、Delete),而不包含任何业务相关的信息。有时也称作 BaseDAO。

    作用:为了实现功能的模块化,更有利于代码的维护和升级。

    使用

    表结构

    表结构

    DAO使用

    DAO使用


    package cn.parzulpan.jdbc.ch07.util;
    
    import com.alibaba.druid.pool.DruidDataSourceFactory;
    import org.apache.commons.dbutils.DbUtils;
    
    import javax.sql.DataSource;
    import java.io.InputStream;
    import java.sql.*;
    import java.text.ParseException;
    import java.text.SimpleDateFormat;
    import java.util.Properties;
    
    /**
     * @Author : parzulpan
     * @Time : 2020-12-02
     * @Desc : 操作数据库的工具类,最终版
     */
    
    public class JDBCUtils {
        private static DataSource dataSource = null;
    
        static {
            try {
                Properties properties = new Properties();
                InputStream is = ClassLoader.getSystemClassLoader().getResourceAsStream("druid.properties");
                properties.load(is);
                dataSource = DruidDataSourceFactory.createDataSource(properties);
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
    
        /**
         * 获取数据库连接,使用 Druid 数据库连接池
         * @return 数据库连接
         */
        public static Connection getDruidConnection() throws SQLException {
            return dataSource.getConnection();
        }
    
        /**
         * 使用DbUtils,静默关闭数据库资源
         * @param connection 数据库连接
         * @param statement 声明
         * @param resultSet 结果集
         */
        public static void closeResourceQuietly(Connection connection, Statement statement, ResultSet resultSet) {
            DbUtils.closeQuietly(connection);
            DbUtils.closeQuietly(statement);
            DbUtils.closeQuietly(resultSet);
        }
    
        public static Date getSqlDate(String dateStr) {
            java.sql.Date date = null;
            try {
                java.util.Date parse = new SimpleDateFormat("yyyy-MM-dd").parse(dateStr);
                date = new java.sql.Date(parse.getTime());
            } catch (ParseException e) {
                e.printStackTrace();
            }
            return date;
        }
    }
    

    package cn.parzulpan.jdbc.ch07.dao;
    
    import org.apache.commons.dbutils.QueryRunner;
    import org.apache.commons.dbutils.handlers.BeanHandler;
    import org.apache.commons.dbutils.handlers.BeanListHandler;
    import org.apache.commons.dbutils.handlers.ScalarHandler;
    
    import java.lang.reflect.ParameterizedType;
    import java.lang.reflect.Type;
    import java.sql.Connection;
    import java.sql.SQLException;
    import java.util.List;
    
    /**
     * @Author : parzulpan
     * @Time : 2020-12-01
     * @Desc : 对数据表的通用操作
     */
    
    public abstract class BaseDAO<T> {
        // 泛型的类型
        private Class<T> type;
        private QueryRunner queryRunner = new QueryRunner();
    
        // 获取 T 类对象,获取泛型的类型,泛型是在被子类继承时才确定的
        public BaseDAO() {
            // 获取子类的类型
            Class<? extends BaseDAO> clazz = this.getClass();
    
            // 获取父类的类型
            ParameterizedType parameterizedType = (ParameterizedType) clazz.getGenericSuperclass();
    
            // 获取具体的泛型
            Type[] types = parameterizedType.getActualTypeArguments();
    
            this.type = (Class<T>)types[0];
        }
    
        /**
         * 通用的增删改操作
         * @param connection 数据库连接
         * @param sql   sql 语句
         * @param args 参数
         * @return 更新的条数
         */
        public int update(Connection connection, String sql, Object ... args) {
            int update = 0;
            try {
                update = queryRunner.update(connection, sql, args);
            } catch (SQLException e) {
                e.printStackTrace();
            }
            return update;
        }
    
        /**
         * 通用的查询操作
         * @param connection 数据库连接
         * @param sql   sql 语句
         * @param args 参数
         * @return 返回一个对象
         */
        public T getBean(Connection connection, String sql, Object ... args) {
            T t = null;
            BeanHandler<T> beanHandler = new BeanHandler<>(type);
            try {
                t = queryRunner.query(connection, sql, beanHandler, args);
            } catch (SQLException e) {
                e.printStackTrace();
            }
            return t;
        }
    
        /**
         * 通用的查询操作
         * @param connection 数据库连接
         * @param sql   sql 语句
         * @param args 参数
         * @return 返回一个对象列表
         */
        public List<T> getBeanList(Connection connection, String sql, Object ... args) {
            List<T> list = null;
            BeanListHandler<T> beanListHandler = new BeanListHandler<>(type);
            try {
                list = queryRunner.query(connection, sql, beanListHandler, args);
            } catch (SQLException e) {
                e.printStackTrace();
            }
            return list;
        }
    
        /**
         * 查询特殊值,类似于最大的,最小的,平均的,总和,个数相关的操作
         * @param connection 数据库连接
         * @param sql   sql 语句
         * @param args 参数
         * @return 特殊值
         */
        public Object getValue(Connection connection, String sql, Object ... args) {
            Object obj = null;
            ScalarHandler scalarHandler = new ScalarHandler();
            try {
                obj = queryRunner.query(connection, sql, scalarHandler, args);
            } catch (SQLException e) {
                e.printStackTrace();
            }
            return obj;
        }
    }
    

    package cn.parzulpan.jdbc.ch07.dao;
    
    import cn.parzulpan.jdbc.ch07.bean.Customer;
    
    import java.sql.Connection;
    import java.sql.Date;
    import java.util.List;
    
    /**
     * @Author : parzulpan
     * @Time : 2020-12-01
     * @Desc : 此接口用于规范 customers 表的常用操作
     */
    
    public interface CustomerDAO {
    
        /**
         * 将 customer 对象添加到数据库中
         * @param connection 数据库连接
         * @param customer customer 对象
         */
        void insert(Connection connection, Customer customer);
    
        /**
         * 用 customer 对象修改数据库对应的数据中
         * @param connection 数据库连接
         * @param customer customer 对象
         */
        void update(Connection connection, Customer customer);
    
        /**
         * 根据 Id 删除一条记录
         * @param connection 数据库连接
         * @param id
         */
        void deleteById(Connection connection, int id);
    
        /**
         * 根据 Id 查询一条记录
         * @param connection 数据库连接
         * @param id
         * @return
         */
        Customer getCustomerById(Connection connection, int id);
    
        /**
         * 查询多条记录
         * @param connection 数据库连接
         * @return
         */
        List<Customer> getCustomerList(Connection connection);
    
        /**
         * 查询记录条数
         * @param connection 数据库连接
         * @return
         */
        Long getCount(Connection connection);
    
        /**
         * 查询最大的生日记录
         * @param connection 数据库连接
         * @return
         */
        Date getMaxBirth(Connection connection);
    
    }
    

    package cn.parzulpan.jdbc.ch07.impl;
    
    import cn.parzulpan.jdbc.ch07.bean.Customer;
    import cn.parzulpan.jdbc.ch07.dao.BaseDAO;
    import cn.parzulpan.jdbc.ch07.dao.CustomerDAO;
    
    import java.sql.Connection;
    import java.sql.Date;
    import java.util.List;
    
    /**
     * @Author : parzulpan
     * @Time : 2020-12-01
     * @Desc :
     */
    
    public class CustomerDAOImpl extends BaseDAO<Customer> implements CustomerDAO {
        /**
         * 将 customer 对象添加到数据库中
         *
         * @param connection 数据库连接
         * @param customer   customer 对象
         */
        @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());
        }
    
        /**
         * 用 customer 对象修改数据库对应的数据中
         *
         * @param connection 数据库连接
         * @param customer   customer 对象
         */
        @Override
        public void update(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());
        }
    
        /**
         * 根据 Id 删除一条记录
         *
         * @param connection 数据库连接
         * @param id
         */
        @Override
        public void deleteById(Connection connection, int id) {
            String sql = "delete from customers where id = ?";
            update(connection, sql, id);
        }
    
        /**
         * 根据 Id 查询一条记录
         *
         * @param connection 数据库连接
         * @param id
         * @return
         */
        public Customer getCustomerById(Connection connection,  int id) {
            String sql = "select id, name, email, birth from customers where id = ?";
            return getBean(connection, sql, id);
        }
    
        /**
         * 查询多条记录
         *
         * @param connection 数据库连接
         * @return
         */
        @Override
        public List<Customer> getCustomerList(Connection connection) {
            String sql = "select id, name, email, birth from customers";
            return getBeanList(connection, sql);
        }
    
        /**
         * 查询记录条数
         *
         * @param connection 数据库连接
         * @return
         */
        @Override
        public Long getCount(Connection connection) {
            String sql = "select count(*) from customers";
            return (Long) getValue(connection, sql);
        }
    
        /**
         * 查询最大的生日记录
         *
         * @param connection 数据库连接
         * @return
         */
        @Override
        public Date getMaxBirth(Connection connection) {
            String sql = "select max(birth) from customers";
            return (Date) getValue(connection, sql);
        }
    }
    

    package cn.parzulpan.jdbc.ch07.junit;
    
    import cn.parzulpan.jdbc.ch07.bean.Customer;
    import cn.parzulpan.jdbc.ch07.impl.CustomerDAOImpl;
    import cn.parzulpan.jdbc.ch07.util.JDBCUtils;
    import org.junit.After;
    import org.junit.Before;
    import org.junit.Test;
    
    import java.sql.Connection;
    import java.sql.Date;
    import java.sql.SQLException;
    import java.util.List;
    
    import static org.junit.Assert.*;
    
    /**
     * @Author : parzulpan
     * @Time : 2020-12-02
     * @Desc :
     */
    
    public class CustomerDAOImplTest {
    
    
        @Before
        public void setUp() throws Exception {
            System.out.println("CustomerDAOImplTest Before");
        }
    
        @After
        public void tearDown() throws Exception {
            System.out.println("CustomerDAOImplTest Down");
        }
    
        private CustomerDAOImpl customerDAO = new CustomerDAOImpl();
    
        @Test
        public void insert() {
            Connection connection = null;
            try {
                connection = JDBCUtils.getDruidConnection();
                Customer customer = new Customer(30, "DAO", "dao@163.com", JDBCUtils.getSqlDate("1995-1-1"));
                customerDAO.insert(connection, customer);
            } catch (SQLException e) {
                e.printStackTrace();
            } finally {
                JDBCUtils.closeResourceQuietly(connection, null, null);
            }
        }
    
        @Test
        public void update() {
            Connection connection = null;
            try {
                connection = JDBCUtils.getDruidConnection();
                Customer customer = new Customer(24, "DAOUpdate", "dao@163.com", JDBCUtils.getSqlDate("1995-1-1"));
                customerDAO.update(connection, customer);
            } catch (SQLException e) {
                e.printStackTrace();
            } finally {
                JDBCUtils.closeResourceQuietly(connection, null, null);
            }
        }
    
        @Test
        public void deleteById() {
            Connection connection = null;
            try {
                connection = JDBCUtils.getDruidConnection();
                customerDAO.deleteById(connection, 23);
            } catch (SQLException e) {
                e.printStackTrace();
            } finally {
                JDBCUtils.closeResourceQuietly(connection, null, null);
            }
        }
    
        @Test
        public void getCustomerById() {
            Connection connection = null;
            try {
                connection = JDBCUtils.getDruidConnection();
                Customer customer = customerDAO.getCustomerById(connection, 10);
                System.out.println(customer);
            } catch (SQLException e) {
                e.printStackTrace();
            } finally {
                JDBCUtils.closeResourceQuietly(connection, null, null);
            }
        }
    
        @Test
        public void getCustomerList() {
            Connection connection = null;
            try {
                connection = JDBCUtils.getDruidConnection();
                List<Customer> customers = customerDAO.getCustomerList(connection);
                customers.forEach(System.out::println);
            } catch (SQLException e) {
                e.printStackTrace();
            } finally {
                JDBCUtils.closeResourceQuietly(connection, null, null);
            }
        }
    
        @Test
        public void getCount() {
            Connection connection = null;
            try {
                connection = JDBCUtils.getDruidConnection();
                Long count = customerDAO.getCount(connection);
                System.out.println(count);
            } catch (SQLException e) {
                e.printStackTrace();
            } finally {
                JDBCUtils.closeResourceQuietly(connection, null, null);
            }
        }
    
        @Test
        public void getMaxBirth() {
            Connection connection = null;
            try {
                connection = JDBCUtils.getDruidConnection();
                Date maxBirth = customerDAO.getMaxBirth(connection);
                System.out.println(maxBirth);
            } catch (SQLException e) {
                e.printStackTrace();
            } finally {
                JDBCUtils.closeResourceQuietly(connection, null, null);
            }
        }
    }
    

    练习和总结

  • 相关阅读:
    日期帮助类
    校验帮助类
    缓存帮助类
    数据转换帮助类
    枚举帮助类
    sql 不常用的知识点记录
    反射实例化不同类型的实例
    xml读取
    读取字段别名
    动态类型赋值处理
  • 原文地址:https://www.cnblogs.com/parzulpan/p/14130004.html
Copyright © 2020-2023  润新知