hibernate --- hql参数绑定方式更新 --- 更新参数字段和where条件字段相同时 --- 占位符标识问题 --- 笔记

 

在使用hibernate拼sql时对参数进行绑定的方式 有很多人都习惯使用; 例其中一种:

为了方便, 会把 参数和参数值 进行封装,方便调用;

//参数封装类
public class Property {
	private String name;	
	private Object value;
}

//参数
List<Property> properties = new LinkedList<Property>();
properties.add(new Property("age", 10));
properties.add(new Property("name", "名字1"));

//条件
List<Property> properWhere = new LinkedList<Property>();
properWhere.add(new Property("userId", 1));

//更新接口
public int updateMultipleProperty(List<Property> properties, List<Property> properWhere, Class<?> clazz);


//更新实现类 hql拼接的骚操作
String  hql = " update   "+clazz.getName()+"   set  ";
for (int i = 0; i < properties.size(); i++) {
	if(i == (properties.size()-1)) {
		hql = hql + properties.get(i).getName() + "=:" + properties.get(i).getName() + "  "; 
		
	}else {
		hql = hql + properties.get(i).getName() + "=:" + properties.get(i).getName() + " , "; 
	}
}

hql = hql + " where ";

for (int i = 0; i < properWhere.size(); i++) {
	if(i == (properWhere.size()-1)) {
		hql = hql + properWhere.get(i).getName() + "=:" + properWhere.get(i).getName() + "   "; 
	}else {
		hql = hql + properWhere.get(i).getName() + "=:" + properWhere.get(i).getName() + "  and  "; 
	}
}
Query query  = session.createQuery(hql); 

for(Property tempProperty: properties){
	query.setParameter(tempProperty.getName(), tempProperty.getValue());			
}
for(Property tempWhere: properWhere){
	query.setParameter(tempWhere.getName(), tempWhere.getValue());			
}
query.executeUpdate();

这样已经适用大部分更新的sql语句, 只需要前边把需要的条件写好, 直接调用;

 

问题: 在需要更新的字段 同时也作为where条件的时候, 会出现问题;

        参数位置的age和条件位置的age, 名字相同,相当于无法找到占位符位置, 更新出现问题;

//参数
List<Property> properties = new LinkedList<Property>();
properties.add(new Property("age", 18));
properties.add(new Property("name", "成年人"));
...

//条件
List<Property> properWhere = new LinkedList<Property>();
properWhere.add(new Property("age", 17));
properWhere.add(new Property("grade", "高三"));
...


//问题: 这时 hql 的拼接为: -----------更新不执行/age字段没有更新

String  hql = " update    "+ clazz.getName()+ " set ";
hql = hql + "  age=:age , "; 
hql = hql + "  name=:name "; 
hql = hql + "  where   "; 
hql = hql + "  age=:age  and "; 
hql = hql + "  grade=:grade"; 

Query query  = session.createQuery(hql); 

query.setParameter("age", 18);	
query.setParameter("name", "成年人");	
query.setParameter("age", 17);	
query.setParameter("grade", "高三");	

query.executeUpdate();



//解: 把占位符位置的名字区分开,sql能找到位置,正确更新

String  hql = " update    "+ clazz.getName()+ " set ";
hql = hql + "  age=:age18 , "; 
hql = hql + "  name=:name "; 
hql = hql + "  where   "; 
hql = hql + "  age=:age17  and "; 
hql = hql + "  grade=:grade"; 

Query query  = session.createQuery(hql); 

query.setParameter("age18", 18);	
query.setParameter("name", "成年人");	
query.setParameter("age17", 17);	
query.setParameter("grade", "高三");	

query.executeUpdate();

 

解决: 不适用的个别hql语句, 根据具体业务逻辑单独拼写sql ;

         也可继续封装,添加封装参数 nameSign; 

         拼接hql:   name=: nameSign;

         给值:        query.setParameter(nameSign, value);   

//参数封装类
public class Property {
    private String name;    
    private String nameSign;  
    private Object value;
}

//参数
List<Property> properties = new LinkedList<Property>();
properties.add(new Property("age", "ageNew",18));
properties.add(new Property("name","name", "成年人"));
...

//条件
List<Property> properWhere = new LinkedList<Property>();
properWhere.add(new Property("age","ageOld", 17));
properWhere.add(new Property("grade", "grade","高三"));
...


//拼接
String hql = " update   " + clazz.getName() + "   set  ";

for (int i = 0; i < properties.size(); i++) {
	if (i == (properties.size() - 1)) {
		hql = hql + properties.get(i).getName() + "=:" + properties.get(i).getNameSign() + "  ";
	} else {
		hql = hql + properties.get(i).getName() + "=:" + properties.get(i).getNameSign() + " , ";
	}
}

hql = hql + " where ";
for (int i = 0; i < properWhere.size(); i++) {
	if (i == (properWhere.size() - 1)) {
		hql = hql + properWhere.get(i).getName() + "=:" + properWhere.get(i).getNameSign() + "   ";
	} else {
		hql = hql + properWhere.get(i).getName() + "=:" + properWhere.get(i).getNameSign() + "  and  ";
	}
}
Query query = session.createQuery(hql);

for (Property tempProperty : properties) {
	query.setParameter(tempProperty.getNameSign(), tempProperty.getValue());
}
for (Property tempWhere : properWhere) {
	query.setParameter(tempWhere.getNameSign(), tempWhere.getValue());
}

query.executeUpdate();

 

 

参考文献: 

hibernate之Hql ->update语句的用法  

https://blog.csdn.net/u014492098/article/details/42103089