功能场景
需要开发一个功能,功能场景式要根据4个条件排列组合如查询数据,我是一个多年的Hibernate用户,hibernate有一个很大的缺点:不能像mybatis那样用直接写sql的方式,其实即使是用mybatis,要这么多条件也要组合很多sql语句。hibernate的criteria也可以,但是也比较麻烦,想了半天后突然从Java反射得到灵感,好像可以利用spring框架和Java反射机制,去动态构建这个sql语句,多说无益,有灵感立刻开干。
首先构造我的查询条件类
public class IpInfoFilterModel4Query implements Serializable {
@ApiModelProperty(name = "节点类型",required = true)
private String nodeName;
@ApiModelProperty(name = "代工厂",required = true)
private String foundryName;
@ApiModelProperty(name = "供应商名称",required = true)
private String providerName;
@ApiModelProperty(name = "类型名称", required = true)
private String typeName;
}
下面开始写sql构建方法
private HashMap<String, Object>buildTheSql(StringBuffer countHql,StringBuffer hql,IpInfoFilterModel4Query ipInfoFilterModel4Query)throws ChipCloudBusinessException{
HashMap<String, Object>condition = new HashMap<String, Object>();
try {
Field[] fields = ipInfoFilterModel4Query.getClass().getDeclaredFields();
Class clazz =ipInfoFilterModel4Query.getClass();
for(Field field : fields) {
/**获取属性的注解,通过required字段区分类中属性是否为必填项,只对必填项进行判空*/
ApiModelProperty anno = field.getAnnotation(ApiModelProperty.class);
field.setAccessible(true);
/**获取属性的getter setter方法*/
PropertyDescriptor pd = new PropertyDescriptor(field.getName(), clazz);
/**获取get方法*/
Method getMethod = pd.getReadMethod();
if (anno.required()) {
if(!Checkers.isBlankString(getMethod.invoke(ipInfoFilterModel4Query).toString())) {
condition.put(field.getName(), field.get(ipInfoFilterModel4Query));
}
}
}
Integer i = 0;
final List<Object> args= new ArrayList<Object>();
for(String key:condition.keySet()) {
countHql.append(" and ").append(key).append("=?").append(i.toString());
hql.append(" and ").append(key).append(" = ?").append(i.toString());
args.add(condition.get(key).toString());
i=i+1;
}
condition.put("countHql", countHql);
condition.put("hql", hql);
condition.put("args", args);
return condition;
} catch (Exception e) {
// TODO: handle exception
logger.error(e.getMessage());
throw new ChipCloudBusinessException("IPDATAMANAGE_GET_IP_LIST_CONSTRUCT_SQL_ERROR");
}
}
这样一个动态构建sql的方法就完成了,其中map类condition中,key是属性的名称,value是属性的值,这样就把非空参数动态的构建到sql语句中了,构建出的sql语句如下:
2021-05-20 21:23:50,557 ERROR (IpManageServiceImpl.java:705)- 0:b
2021-05-20 21:23:50,557 ERROR (IpManageServiceImpl.java:705)- 1:a
2021-05-20 21:23:50,557 ERROR (IpManageServiceImpl.java:705)- 2:d
2021-05-20 21:23:50,557 ERROR (IpManageServiceImpl.java:705)- 3:c
2021-05-20 21:23:50,557 ERROR (IpManageServiceImpl.java:707)- select * from IpmanageDataInfoModel where 1 = 1 and nodeName=?0 and foundryName=?1 and typeName=?2 and providerName=?3
这样我们就可以调用hibernate的query方法去查询数据了,如果用常规的方法,就要一个个判断,不为空则塞到语句中,这样4个条件就要写41个查询判断,这简直要疯了