前言
项目中虽然有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 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 ignoreList1 = Arrays.asList(ignoreProperties); //默认忽略分页参数 List 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 valuesList = (List ) 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 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);
为了优化SQL，一般我们不建议select * from，而是需要查询那些字段就拼出那些字段，例如：select log_id from
但是如果数据表有一百个字段呢？一个个手动拼接就太傻了，因此写了一个自动拼接字段的方法，支持配置忽略拼接的字段
/** * * @param entity 实体对象 * @param ignoreProperties 动态参数 忽略拼接的字段 * @return sql */ public static StringBuilder appendFields(Object entity, String... ignoreProperties) { StringBuilder sql = new StringBuilder(); List ignoreList = Arrays.asList(ignoreProperties); try { sql.append("select "); for (Field field : entity.getClass().getDeclaredFields()) { //获取授权 field.setAccessible(true); String fieldName = field.getName();//属性名称 Object fieldValue = field.get(entity);//属性的值 //非临时字段、非忽略字段 if (!field.isAnnotationPresent(Transient.class) && !ignoreList.contains(fieldName)) { //拼接查询字段 驼峰属性转下划线 sql.append(new PropertyNamingStrategy.SnakeCaseStrategy().translate(fieldName).toLowerCase()).append(" ").append(","); } } //处理逗号（删除最后一个字符） sql.deleteCharAt(sql.length() - 1); String tableName = entity.getClass().getAnnotation(Table.class).name(); sql.append("from ").append(tableName).append(" where '1' = '1' "); } catch (IllegalAccessException e) { e.printStackTrace(); } return sql; } 接着上面的main测试
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 list = new ArrayList<>(); list.add("type1"); list.add("type2"); resLog.setOperationTypeList(list);//in查询 //动态拼接查询字段 StringBuilder sql = appendFields(resLog,"remark","operator"); appendQueryColumns(resLog,sql); System.out.println(sql.toString());} 结果
select log_id, resource_type, resource_id, resource_name, resource_code, operation_type, operation_time, operator_id 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-12-13 10:34:33', 'yyyy-MM-dd hh24:mi:ss') and operation_time < to_date('2018-12-13 10:34:33', 'yyyy-MM-dd hh24:mi:ss')