【精品】仿JPA实现 基于DBUtils的通用Dao

本博客代码下载地址：源代码

简介

现代软件编程中约定>配置>编码，一般来说有以下约定：

• 表名：以tb_开始，后面各单词之间以_分隔
• 表主键：每个表都有一个简单类型（比如：int long string）的主键
• 实体类名：表名去除tb_前缀，去掉各单词之间的_，每个单词首字母大写
• Dao接口名：实体类类名加上Dao后缀
• Dao实现类类名：实体类类名加上DaoImpl后缀
• Service接口名：实体类类名加上Service后缀
• Service实现类类名：实体类类名加上ServiceImpl后缀

基于上面约定，本博客中我们采用DBUtils实现一个通用的Dao程序

封装数据库表字段和是否是主键的JavaBean

@Getter
@Setter
@NoArgsConstructor
@AllArgsConstructor
@Builder
@ToString
public class Item {
    private String field;
    private String pk;
}

说明：

通用Dao

public class BaseDaoImpl {
    private String table;
    private Class clazz;
    private List columonItemList;

    public BaseDaoImpl(Class clazz) {
        this.clazz = clazz;
        // 获取类名
        String className = clazz.getSimpleName();
        // 根据类名获取对应的表名
        this.table = className2tableName(className);
        try {
            String sql = "select column_name as field,column_key pk from information_schema.COLUMNS   where table_schema= ? and table_name= ? ";
            QueryRunner queryRunner = new QueryRunner(TransactionManager.getDataSource());
            columonItemList = queryRunner.query(sql, new BeanListHandler(Item.class), CreditsConst.DATABASE, table);
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    /**
     * 将类名转换成对应的表名，比如：CreditsHistory--tb_credits_history
     *
     * @param str
     * @return
     */
    private String className2tableName(String str) {
        String result = "";
        for (int i = 0; i  clazz = Class.forName("com.wego.");
        List res = (List) queryRunner.query(sql, new BeanListHandler(clazz), (pageNum - 1) * pageSize, pageSize);
//        System.out.println(res);
        return res;
    }

    public T selectByPrimaryKey(Object key) throws SQLException {
        String columns = "";
        for (Item item : columonItemList) {
            String field = item.getField();
            if (field.contains("_")) {
                String[] split = field.split("_");
                String alias = split[0] + StringUtil.first2UpperCase(split[1]);
                columns += field + " " + alias + ", ";
            } else {
                columns += field + ", ";
            }
        }
        String sql = "select " + columns.substring(0, columns.length() - 2) + " from " + table + " where ";

        for (Item item : columonItemList) {
            String field = item.getField();
            String pk = item.getPk();
            if (pk.equals("PRI")) {
                sql += field + "=? ";
                break;
            }
        }
//        System.out.println(sql);
        QueryRunner queryRunner = new QueryRunner(TransactionManager.getDataSource());
        T res = (T) queryRunner.query(sql, new BeanHandler(clazz), key);
//        System.out.println(res);
        return res;
    }

}

测试代码

public class DeptDaoImplTest {
    private DeptDaoImpl deptDao = new DeptDaoImpl();

    @Test
    void insert() throws Exception {
        Dept dept = new Dept( 22,"fdas","fdsa");
        int res = deptDao.insert(dept);
        System.out.println(res);
    }

    @Test
    void update() throws Exception {
        Dept dept = new Dept(22,"aaaaa","aaaaa");
        int res = deptDao.update(dept);
        System.out.println(res);
    }

    @Test
    void deleteByPrimaryKey() throws Exception {
        int res = deptDao.deleteByPrimaryKey(22);
        System.out.println(res);
    }

    @Test
    void selectCount() throws SQLException {
        int res = deptDao.selectCount();
        System.out.println(res);
    }

    @Test
    void selectPage() throws SQLException {
        List list = deptDao.selectPage(2, 2);
        list.forEach(System.out::println);
    }

    @Test
    void selectByPrimaryKey() throws SQLException {
        System.out.println(deptDao.selectByPrimaryKey( 20));
    }
}