阅读 74

Hibernate中基于JAVA反射机制动态构建sql语句

功能场景

需要开发一个功能,功能场景式要根据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个查询判断,这简直要疯了

文章分类
后端
文章标签