277°

利用反射跟自定义注解拼接实体对象的查询SQL

项目中虽然有ORM映射框架来帮我们拼写SQL,简化开发过程,降低开发难度。但难免会出现需要自己拼写SQL的情况,这里分享一个利用反射跟自定义注解拼接实体对象的查询SQL的方法。

 

自定义注解:

@Target(ElementType.FIELD)
@Retention(RetentionPolicy.RUNTIME)
public @interface Like {

}

@Target(ElementType.FIELD)
@Retention(RetentionPolicy.RUNTIME)
public @interface Between {

    /**
     * 最小值的实体属性名
     */
    String min();

    /**
     * 最大值的实体属性名
     */
    String max();
}

@Target(ElementType.FIELD)
@Retention(RetentionPolicy.RUNTIME)
public @interface In {

    /**
     * in的具体集合的属性名
     */
    String values();
}

 

实体对象:

@Data
@Entity
@Table(name = "RES_LOG")
public class ResLog {
    @Id
    private String logId;
    private String resourceType;
    private String resourceId;
    @Like //开启模糊查询
    private String resourceName;
    private String resourceCode;
    @In(values = "operationTypeList")//in查询
    private String operationType;
    @Between(min = "operationTimeStart", max = "operationTimeEnd")//开启区间查询
    private Date operationTime;
    private String operatorId;
    private String operator;

    @Transient
    private Date operationTimeStart;
    @Transient
    private Date operationTimeEnd;
    @Transient
    private List<String> operationTypeList;

}

 

拼接SQL方法:

/**
 * 自动拼接原生SQL的“and”查询条件,支持自定义注解:@Like @Between @In
 *
 * @param entity           实体对象
 * @param sql              待拼接SQL
 * @param ignoreProperties 忽略属性
 */
public static void appendQueryColumns(Object entity, StringBuilder sql, String... ignoreProperties) {

    try {
        //忽略属性
        List<String> ignoreList1 = Arrays.asList(ignoreProperties);
        //默认忽略分页参数
        List<String> ignoreList2 = Arrays.asList("class", "pageable", "page", "rows", "sidx", "sord");
   
        //反射获取Class的属性(Field表示类中的成员变量)
        for (Field field : entity.getClass().getDeclaredFields()) {
            //获取授权
            field.setAccessible(true);
            //属性名称
            String fieldName = field.getName();
            //属性的值
            Object fieldValue = field.get(entity);
            //检查Transient注解,是否忽略拼接
            if (!field.isAnnotationPresent(Transient.class)) {
                String column = new PropertyNamingStrategy.SnakeCaseStrategy().translate(fieldName).toLowerCase();
                //值是否为空
                if (!StringUtils.isEmpty(fieldValue)) {
                    //映射关系:对象属性(驼峰)->数据库字段(下划线)
                    if (!ignoreList1.contains(fieldName) && !ignoreList2.contains(fieldName)) {
                        //开启模糊查询
                        if (field.isAnnotationPresent(Like.class)) {
                            sql.append(" and " + column + " like '%" + fieldValue + "%'");
                        }
                        //开启等值查询
                        else {
                            sql.append(" and " + column + " = '" + fieldValue + "'");
                        }
                    }
                } else {
                    //开启区间查询
                    if (field.isAnnotationPresent(Between.class)) {
                        //获取最小值
                        Field minField = entity.getClass().getDeclaredField(field.getAnnotation(Between.class).min());
                        minField.setAccessible(true);
                        Object minVal = minField.get(entity);
                        //获取最大值
                        Field maxField = entity.getClass().getDeclaredField(field.getAnnotation(Between.class).max());
                        maxField.setAccessible(true);
                        Object maxVal = maxField.get(entity);
                        //开启区间查询
                        if (field.getType().getName().equals("java.util.Date")) {
                            if (!StringUtils.isEmpty(minVal)) {
                                sql.append(" and " + column + " > to_date( '" + new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format((Date) minVal) + "','yyyy-mm-dd hh24:mi:ss')");
                            }
                            if (!StringUtils.isEmpty(maxVal)) {
                                sql.append(" and " + column + " < to_date( '" + new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format((Date) maxVal) + "','yyyy-mm-dd hh24:mi:ss')");
                            }
                        }
                    }
                    
                    //开启in查询
                    if (field.isAnnotationPresent(In.class)) {
                        //获取要in的值
                        Field values = entity.getClass().getDeclaredField(field.getAnnotation(In.class).values());
                        values.setAccessible(true);
                        List<String> valuesList = (List<String>) values.get(entity);
                        if (valuesList != null && valuesList.size() > 0) {
                            String inValues = "";
                            for (String value : valuesList) {
                                inValues = inValues + "'" + value + "'";
                            }
                            sql.append(" and " + column + " in (" + inValues + ")");
                        }
                    }
                }
            }
        }
    } catch (Exception e) {
        e.printStackTrace();
    }
}

 

测试:

public static void main(String[] args) {
    ResLog resLog = new ResLog();
    resLog.setLogId("id1");//等值查询
    resLog.setResourceName("name1");//like查询
    resLog.setOperationTimeStart(new Date());//日期区间查询
    resLog.setOperationTimeEnd(new Date());
    ArrayList<String> list = new ArrayList<>();
    list.add("type1");
    list.add("type2");
    resLog.setOperationTypeList(list);//in查询
    //在外面拼写select * from 是为了多表联查时的情况
    StringBuilder sql = new StringBuilder("select * from res_log where '1' = '1'");
    appendQueryColumns(resLog,sql);
    System.out.println(sql.toString());
}

 

拼接结果:

select * from res_log where '1' = '1' and log_id = 'id1' and resource_name like '%name1%' and operation_type in ('type1''type2') and operation_time > to_date( '2018-10-08 15:00:40','yyyy-mm-dd hh24:mi:ss') and operation_time < to_date( '2018-10-08 15:00:40','yyyy-mm-dd hh24:mi:ss')

 

甚至我们可以直接获取实体对象对应的表名,直接在方法里面拼出 select * from ,这样就不需要在外面拼接这一句

//获取实体对象对应的表名
String TableName = entity.getClass().getAnnotation(Table.class).name();
System.out.println(TableName);

 

原文链接:https://www.cnblogs.com/huanzi-qch/p/9754846.html

全部评论: 0

    我有话说: