使用Mybatis缺省实现CRUD

    Mybatis是个好东西，只是有时候，代码写起来不方便，比如一个具有10多个属性的实体对象，要写插入语句，那就要写长长的sql，很容易拼错，查询要写Results，又是老长的一段，简直忍受不了。

我来更新了，使用 SELECT COLUMN AS FIELD，在查询结果中将表中字段转换成实体对应的属性名就行了，不用写@Results注解

    所以，在下就使用注解、反射实现了实体的基本的增、删、改、查

 //插入, 会将主键一块插入
    int insert(Object o);


    //插入，不会插入主键，适合主键自动生成的情景下使用
    int add(Object o);

    /**
     * 批量插入实例
     * @param entities 实例集合
     * @param hasPrimary 是否需要插入主键值
     */
    void batchInsert(Collection<T> entities, boolean hasPrimary);

    //修改
    int update(Object o);

    /**
     * 删除实例对象
     * @param obj 可以是主键、实例对象
     * @return 被删除的数量
     */
    int delete(Object obj);


    /**
     * 通过主键批量删除实例对象
     * @param keys 主键集合
     * @return 被删除的数量
     */
    int batchDelete(Collection<?> keys);


    /**
     * 通过主键获取实例对象
     * @param key 主键值
     * @return 实例对象
     */
    T get(Object key);


    /**
     * 通过若干主键获取实例对象list
     * @param ids 若干主键值，以“,”分隔
     * @return
     */
    List<T> findByIds(String ids);

    //查找所有的实体对象
    List<T> findAll();


    /**
     * 分页查询
     * @param req 查询封装对象
     * @param pageNum 当前页码
     * @param pageSize 每页数量
     * @return list
     */
    List<T> pageSeek(SeekReq req, int pageNum, int pageSize);

    /**
     * 通用查询
     * @param req 查询封装对象
     * @return list
     */
    List<T> seek(SeekReq req);


    /**
     * 统计查询
     * @param field 统计字段，可以为空
     * @param filters 若干过滤条件
     * @return 数目
     */
    int countBy(String field, SeekFilter... filters);

    github：点击打开链接，如果各位客官心情好，请劳烦点个star; 

1、使用Table注解保存实体对象对应的数据表的表名与主键对应的实体属性名

/* 定义表的注解*/
@Retention(RetentionPolicy.RUNTIME)
@Target(ElementType.TYPE)
public @interface Table {

    String name() default "";

    String primaryKey() default "";
}

2、使用Column保存属性对应的表中字段

/* 定义字段的注解*/
@Retention(RetentionPolicy.RUNTIME)
/*该注解只能用在成员变量上*/
@Target(ElementType.FIELD)
public @interface Column {

    String name() default "";
}

3、EntityDao<T>是一个抽象类，使用EntityDao<T>通过注解获取实体的以上信息，以下是它的属性与构造器

 /**
     * 表名称
     */
    private String tableName;

    /**
     * 表格主键对应的实体属性名称
     */
    private String primaryField;

    /**
     * 众列名称，以逗号分割的字符串
     */
    private String columns;

    //众属性名称
    private List<String> fields = new ArrayList<>();

    //主键以外的所有属性名称
    private List<String> fieldsExcPrimary = new ArrayList<>();

    /**
     * 不包括主键的众列名称，同样是以逗号分割的字符串
     */
    private String columnsExcPrimary;

    /**
     * 结构[属性：字段]
     */
    private Map<String, String> fieldsAndCols = new HashMap<>();

    /**
     * [属性：字段]，不包括主键
     */
    private Map<String, String> fiesAndColsExcPrimary = new HashMap<>();

    /**
     * [属性：get方法]
     */
    private Map<String, Method> fieldAndMethod = new HashMap<>();


    /**
     * 实体的class
     */
    private Class<T> clazz;


    /**
     * 由具体的对象实现
     * @return 实体类的class
     */
    public abstract Class<T> init();


    protected EntityDao() {
        log.info("entitySql无参构造器被调用------------> " + this);
        clazz = init();
        Table table = clazz.getAnnotation(Table.class);
        this.tableName = table.name();
        this.primaryField = table.primaryKey();
        Field[] fields = clazz.getDeclaredFields();
        int size = fields.length;
        Field field;
        String colName;
        String fieldName;
        StringBuilder sb = new StringBuilder();
        StringBuilder stringBuilder = new StringBuilder();
        try {
            for (int i = 0; i < size; i++) {
                field = fields[i];
                fieldName = field.getName();

                //只针对做了com.digital.util.search.Column注解的字段
                if (fields[i].isAnnotationPresent(Column.class)) {
                    this.fields.add(fieldName);
                    fieldAndMethod.put(fieldName, clazz.getMethod("get" + upperCaseFirst(fieldName)));
                    colName = field.getAnnotation(Column.class).name();
                    if (!this.primaryField.equals(field.getName())) {//过滤有问题
                        this.fiesAndColsExcPrimary.put(fieldName, colName);
                        stringBuilder.append(colName).append(i < size - 1 ? "," : "");
                        this.fieldsExcPrimary.add(fieldName);
                    }
                    this.fieldsAndCols.put(fieldName, colName);
                    sb.append(colName).append(" AS ").append(fieldName).append(i < size - 1 ? "," : "");
                }
            }
        } catch (NoSuchMethodException ex) {
            log.error("entityDao.constructor.error-------> " + ex.getMessage());
        }

        this.columns = sb.toString();
        this.columnsExcPrimary = stringBuilder.toString();
    }

4、在SqlProvider中使用EntityDao

    public static EntityDao entityDao = new EntityDao() {
        @Override
        public Class init() {
            return Report.class;
        }
    };

5、EntityDao中生成sql的方法：

//会插入主键
    public String insert(final Object entity) {
        String sql = new SQL() {{
            INSERT_INTO(tableName);
            getFieldsAndCols().forEach((k, v) -> VALUES(v, "#{" + k + "}"));
        }}.toString();
        log.info("entityDao.insert.sql----> " + sql);
        return sql;
    }

    //不会插入主键
    public String add(final Object entity) {
        String sql = new SQL() {{
            INSERT_INTO(tableName);
            getFiesAndColsExcPrimary().forEach((k, v) -> VALUES(v, "#{" + k + "}"));
        }}.toString();
        log.info("entityDao.insert.sql----> " + sql);
        return sql;
    }


    private String batchInsert(Collection<T> entities, List<String> fields) {
        StringBuilder re = new StringBuilder(" VALUES ");
        try {
            for (T entity : entities) {
                StringBuilder sb = new StringBuilder("(");

                for (String key : fields) {
                    Method m = fieldAndMethod.get(key);
                    Object val = m.invoke(entity);
                    sb.append("'").append(val).append("',");
                }
                sb.append("),");
                re.append(sb);
            }
        } catch (Exception ex) {
            ex.printStackTrace();
            log.error("entityDao.batchInsert.error----> " + ex.getMessage());
        }


        System.out.println("----" + re.toString().replaceAll("\\),$", ")"));
        String sql = new SQL() {{
            INSERT_INTO(tableName);
            INTO_COLUMNS(columnsExcPrimary);
        }}.toString() + re.toString()
                .replaceAll(",\\)", ")")
                .replaceAll("'null'", "null")
                .replaceAll("\\),$", ")");
        log.info("entityDao.batchInsert.sql----> " + sql);
        return sql;
    }


    /**
     * 批量插入
     *
     * @param entities   实体集合
     * @param hasPrimary 插入的属性是否包括主键
     * @return
     */
    public String batchInsert(Collection<T> entities, boolean hasPrimary) {
        return batchInsert(entities, hasPrimary ? fields : fieldsExcPrimary);
    }

    /**
     * 删除实例对象
     *
     * @param obj 可以是主键、实例对象
     * @return
     */
    public String delete(Object obj) {
        String sql;
        if (obj == null) {
            log.error("操作的对象为空！");
            throw new RuntimeException("对象为空！");
        } else {
            if (isEntity(obj)) {
                sql = new SQL() {{
                    DELETE_FROM(getTableName());
                    WHERE(getColumnByField(getPrimaryField()) + "=#{" + getPrimaryField() + "}");
                }}.toString();
            } else {
                sql = new SQL() {{
                    DELETE_FROM(getTableName());
                    WHERE(getColumnByField(getPrimaryField()) + "=" + obj);
                }}.toString();
            }
            log.info("entityDao.delete.sql--------------> " + sql);
            return sql;
        }
    }

    /**
     * @param keys 以“,”分隔的主键值
     * @return sql
     */
    public String batchDelete(String keys) {
        String sql = new SQL() {{
            DELETE_FROM(getTableName());
            WHERE(getColumnByField(getPrimaryField()) + " IN (" + keys + ")");
        }}.toString();
        log.info("entityDao.batchDelete.sql----------> " + sql);
        return sql;
    }

    /**
     * 更新实例对象
     * 如果传入map，则据键值对修改对象，
     * 如果传入是一个已在的实例，则会将此实例的所有属性更新至数据库，
     * 建议使用map作为参数
     *
     * @param group 包含主键的map：[keyName: value] 或者 实例对象
     * @return sql
     */
    public String update(final Object group) {
        String sql;
        if (group == null) {
            throw new RuntimeException("传入对象不存在！");
        } else {
            if (isEntity(group)) {
                sql = new SQL() {{
                    UPDATE(tableName);
                    getFiesAndColsExcPrimary().forEach((k, v) -> SET(v + "=#{" + k + "}"));
                    WHERE(getColumnByField(primaryField) + "=#{" + primaryField + "}");
                }}.toString();
            } else if (group instanceof Map) {
                Map temp = (Map) group;
                if (temp.containsKey(primaryField)) {
                    sql = new SQL() {{
                        UPDATE(tableName);
                        temp.forEach((k, v) -> {
                            SET(getColumnByField(k.toString()) + "=#{" + k + "}");
                        });
                        WHERE(getColumnByField(primaryField) + "=#{" + primaryField + "}");
                    }}.toString();
                } else {
                    throw new RuntimeException("未传入主键值！");
                }
            } else {
                throw new IllegalArgumentException("只接收实体对象或者[属性-属性值]结构的map对象");
            }
            log.info("entityDao.update.sql-------> " + sql);
            return sql;
        }
    }

    /**
     * 批量更新实例
     *
     * @param params       需要更新的字段与值组成的键值对
     * @param seekFilters 过滤条件
     * @return sql
     */
    public String batchUpdate(Map<String, Object> params, SeekFilter... seekFilters) {
        String sql = new SQL() {{
            UPDATE(getTableName());
            if (params instanceof Map) {
                params.forEach((k, v) -> {
                    SET(getColumnByField(k.toString()) + "=#{" + k + "}");
                });
                parseFilters(this, seekFilters);
            } else {
                throw new IllegalArgumentException("只接收[属性:值]结构的map对象");
            }
        }}.toString();
        return sql;
    }


    //通过主键获取对象
    public String get(Object primaryKey) {
        String sql = new SQL() {{
            FROM(tableName);
            SELECT(columns);
            WHERE(getColumnByField(getPrimaryField()) + "=" + primaryKey);
        }}.toString();
        log.info("entityDao.get.sql------------> " + sql);
        return sql;
    }


    /**
     * 通过若干主键获取对象集合
     *
     * @param ids 以“,”分隔的id
     * @return sql
     */
    public String findByIds(String ids) {
        String sql = new SQL() {{
            FROM(tableName);
            SELECT(getColumns());
            WHERE(getColumnByField(getPrimaryField()) + "  IN (" + ids + ")");
        }}.toString();
        log.info("entityDao.findByIds.sql ---> " + sql);
        return sql;
    }

    /**
     * 统计查询
     *
     * @param field   统计的字段，默认是主键
     * @param filters 过滤条件
     * @return sql
     */
    public String countBy(String field, SeekFilter... filters) {
        String sql = new SQL() {{
            FROM(tableName);
            if (field != null && "".equals(field)) {
                if (getColumnByField(field) == null) {
                    SELECT("count(" + primaryField + ")");
                } else {
                    SELECT("count(" + field + ")");
                }
            } else {
                SELECT("count(" + primaryField + ")");
            }
            parseFilters(this, filters);
        }}.toString();
        sql = rename(sql, getFieldsAndCols());
        log.info("entityDao.countBy.sql------> " + sql);
        return sql;
    }

    /**
     * 分页查询
     *
     * @param req       查询封装对象
     * @param pageIndex 当前在页码
     * @param pageSize  每页大小
     * @return sql
     */
    public String pageSeek(SeekReq req, int pageIndex, int pageSize) {
        List<SeekFilter> filters = req.search;
        int offset = (pageIndex - 1) * pageSize;
        String sql = new SQL() {{
            FROM(getTableName());
            if (req.selectFields == null || req.selectFields.equals("")) {
                SELECT(getColumns());
            } else {
                SELECT(req.selectFields);
            }
            parseFilters(this, filters.toArray(new SeekFilter[]{}));
            if (req.sort != null) {
                ORDER_BY(req.sort);
            }
        }}.toString() + " LIMIT " + pageSize + " OFFSET " + offset;
        sql = rename(sql, getFieldsAndCols());
        log.info("entityDao.pageSeek.sql----------> " + sql);
        return sql;
    }

    /**
     * 根据指定的若干条件查询若干字段查询
     *
     * @param req 查询封装对象
     * @return sql
     */
    public String seek(SeekReq req) {
        List<SeekFilter> filters = req.search;
        String sql = new SQL() {{
            FROM(getTableName());
            if (req.selectFields == null || req.selectFields.equals("")) {
                SELECT(getColumns());
            } else {
                SELECT(req.selectFields);
            }
            parseFilters(this, filters.toArray(new SeekFilter[]{}));
            if (req.sort != null) {
                ORDER_BY(req.sort);
            }
        }}.toString();
        sql = rename(sql, fieldsAndCols);
        log.info("entityDao.seek.sql-----> " + sql);
        return sql;
    }

    private void parseFilters(SQL sql, SeekFilter... seekFilters) {
        sql.WHERE("1 = 1");
        for (SeekFilter filter : seekFilters) {
            if (filter.getLogic().equals(SeekFilter.LOGIC_AND)) {
                sql.AND();
                sql.WHERE(filter.toSqlPart());
            } else {
                sql.OR();
                sql.WHERE(filter.toSqlPart());
            }
        }
    }

    public String findAll() {
        String sql = new SQL() {{
            SELECT(columns);
            FROM(tableName);
        }}.toString();
        log.info("entityDao.findAll.sql----------> " + sql);
        return sql;
    }

例：Report实体的mapper，很简洁

@Mapper
public interface ReportMapper extends BaseMapper<Report>{
    String RESULT_MAP = "cn.guoyka.simplermybatis.entity.allOfReport";

    @Override
    @InsertProvider(type = ReportSql.class, method = BaseMapper.INSERT)
    int insert(Object o);

    @Override
    @InsertProvider(type = ReportSql.class, method = BaseMapper.ADD)
    int add(Object o);

    @Override
    @UpdateProvider(type = ReportSql.class, method = BaseMapper.UPDATE)
    int update(Object o);

    @Override
    @DeleteProvider(type = ReportSql.class, method = BaseMapper.DELETE)
    int delete(Object obj);

    //@Override
    @DeleteProvider(type = ReportSql.class, method = BaseMapper.BATCH_DELETE)
    int batchDelete(String keys);

    @Override
    @SelectProvider(type = ReportSql.class, method = BaseMapper.GET)
    @ResultMap(RESULT_MAP)
    Report get(Object key);

    @Override
    @SelectProvider(type = ReportSql.class, method = BaseMapper.FIND_BY_IDS)
    @ResultMap(RESULT_MAP)
    List<Report> findByIds(String ids);

    @Override
    @SelectProvider(type = ReportSql.class, method = BaseMapper.FIND_ALL)
    @ResultMap(RESULT_MAP)
    List<Report> findAll();

    @Override
    @SelectProvider(type = ReportSql.class, method = BaseMapper.PAGE_SEEK)
    @ResultMap(RESULT_MAP)
    List<Report> pageSeek(SeekReq req, int pageNum, int pageSize);

    @Override
    @SelectProvider(type = ReportSql.class, method = BaseMapper.SEEK)
    @ResultMap(RESULT_MAP)
    List<Report> seek(SeekReq req);

    @Override
    @SelectProvider(type = ReportSql.class, method = BaseMapper.COUNT_BY)
    int countBy(String field, SeekFilter... filters);

    @SelectProvider(type = ReportSql.class, method = "batchInsert")
    void batchInsert(Collection<Report> entities, boolean hasPrimary);
}

看看ReportSql中都是什么，有点长，都是可以做模块的代码，到处拷贝， 可以考虑写脚本生成或者用其它更漂亮的方式实现：

public class ReportSql implements BaseSql<Report> {

    public static EntityDao entityDao = new EntityDao() {
        @Override
        public Class init() {
            return Report.class;
        }
    };

    /**
     * 批量插入
     * @param entities 实例集合
     * @param hasPrimary 是否需要插入主键
     * @return sql
     */
    public String batchInsert(Collection<Report> entities, boolean hasPrimary){
        return entityDao.batchInsert(entities, hasPrimary);
    }

    @Override
    public String pageSeek(SeekReq req, int pageIndex, int pageSize) {
        return entityDao.pageSeek(req, pageIndex, pageSize);
    }

    @Override
    public String countBy(String field, SeekFilter... filters) {
        return entityDao.countBy(field, filters);
    }

    @Override
    public String findByIds(String ids) {
        return entityDao.findByIds(ids);
    }

    @Override
    public String insert(Object o) {
        return entityDao.insert(o);
    }

    @Override
    public String add(Object o) {
        return entityDao.add(0);
    }

    @Override
    public String update(Object o) {
        return entityDao.update(o);
    }

    @Override
    public String delete(Object o) {
        return entityDao.delete(o);
    }

    @Override
    public String findAll() {
        return entityDao.findAll();
    }

    @Override
    public String seek(SeekReq req) {
        return entityDao.seek(req);
    }

    @Override
    public String get(Object o) {
        return entityDao.get(o);
    }

    //@Override
    public String batchDelete(String keys) {
        return entityDao.batchDelete(keys);
    }
}

另外再附一个生成xml的脚本

@org.junit.Test
    void buildMapperXml() {
        StreamingMarkupBuilder builder = new groovy.xml.StreamingMarkupBuilder()
        builder.encoding = "UTF-8"

        /*
        准备一些输入
         */

        // 项目包名
        String packName = "cn.guoyka.simplermybatis.entity"

        // 实体类的包绝对路径
        File entityFilePath = new File("E:\\Kitty\\workspace\\simpler-mybatis\\src\\main\\java\\cn\\guoyka\\simplermybatis\\entity")


        // 保存生成的Mapper.xml的路径
        String resultPath = "E:\\Kitty\\workspace\\simpler-mybatis\\src\\main\\resources\\mybatis\\mapper\\Mapper.xml"

        String root = "E:\\Kitty\\workspace\\simpler-mybatis\\src\\main\\java\\"

        List<String> entityNames = []
        peekFile(entityFilePath, root, entityNames)
        final def d = ('<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"  "http://mybatis.org/dtd/mybatis-3-mapper.dtd">');
        def mapper = {
            mkp.xmlDeclaration()
            mkp.yieldUnescaped(d)
            mapper(namespace: packName) {
                for (String className : entityNames) {
                    try {
                        if (className != null || className != "") {
                            Class clazz = Class.forName(className)
                            if (clazz.isAnnotationPresent(Table.class)) {
                                Field[] fields = clazz.getDeclaredFields()
                                String primaryKey = clazz.getAnnotation(Table.class).primaryKey()
                                String type = (clazz.getPackage().name + "." + clazz.simpleName)
                                resultMap(id: "allOf" + className.substring(className.lastIndexOf(".") + 1), type: type) {
                                    for (int i = 0; i < fields.length; i++) {
                                        if (Modifier.isStatic(fields[i].getModifiers())) {
                                            pr(fields[i].getName() + "是静态属性，过滤过滤.")
                                        } else {
                                            if (fields[i].getName() == primaryKey) {
                                                id(property: (fields[i].name), javaType: fields[i].getType().simpleName, column: getCol(fields[i]))
                                            } else {
                                                result(property: (fields[i].name), javaType: fields[i].getType().simpleName, column: getCol(fields[i]))
                                            }
                                        }
                                    }
                                }
                            }
                        }
                    } catch (Exception ex) {
                        println("${className} ---> " + ex.getMessage())
                    }
                }

            }
        }
        def writer = new FileWriter(resultPath)
        writer << builder.bind(mapper)
    }

    /**
     * 获取目录下的java类名及完整包名，如 cn.guoyka.simplermybatis.entity.ZCCRequest
     * @param file 实体类的包绝对路径
     * @param root 项目中java文件夹的路径
     * @param fileNames 若干实体类名称
     * @return
     */
    def peekFile(File file, String root, List<String> fileNames) {
        if (file.exists()) {
            if (file.isDirectory()) {
                for (File e : file.listFiles()) {
                    if (e.isDirectory()) {
                        peekFile(e, root, fileNames)
                    } else {
                        if (e.getName().endsWith(".java")) {
                            fileNames.add(e.getPath()
                                    .replace(root, "")
                                    .replace("\\", ".").replace(".java", ""))
                        }
                    }
                }
                return fileNames
            } else {
                if (file.getName().endsWith(".java")) {
                    fileNames.add(file.getPath()
                            .replace("E:\\Kitty\\workspace\\simpler-mybatis\\src\\main\\java\\", "")
                            .replace("\\", ".").replace(".java", ""))
                }
                return fileNames
            }
        } else {
            return new RuntimeException("目录或者文件不存在！");
        }
    }

    /**
     * 获取字段上的Column注解
     * @param field
     * @return
     */
    static def getCol(Field field) {
        if (field.isAnnotationPresent(Column.class)) {
            return field.getAnnotation(Column.class).name()
        } else {
            throw new RuntimeException(field.name + "未有添加注解cn.guoyka.simplermybatis.annotation.Column，请添加！")
        }
    }