一、关于@query的原生sql语句
@query中写sql语句必须按照Hibernate的标准去写,下面写个例子
如果要写原生的sql语句,需要增加nativeQuery = true(默认是false)
@Query("select * from user where email_address = ?1",nativeQuery=true)
User findByEmailAddress(String emailAddress)
二、更新部分字段问题
Hibernate只提供了save方法来进行更新操作,这里有个弊端,就是如果要更新的实体类中的部分属性的值为null,它也会把null值覆盖掉数据库中的数据。这里有两个解决方案:
1、用@Query和@Modifying
@Modifying
@Transactional
@Query("update Test a set " +
"a.name = CASE WHEN :#{#testAre.name} IS NULL THEN a.name ELSE :#{#testAre.name} END ," +
"a.age = CASE WHEN :#{#testAre.age} IS NULL THEN a.age ELSE :#{#testAre.age} END ," +
"a.insertTime = CASE WHEN :#{#testAre.insertTime} IS NULL THEN a.insertTime ELSE :#{#testAre.insertTime} END ," +
"a.spare = CASE WHEN :#{#testAre.spare} IS NULL THEN a.spare ELSE :#{#testAre.spare} END " +
"where a.id = :#{#testAre.id}")
int update(@Param("testAre") TestAre testAre);
注意:这里的sql语句是按Hibernate中的形式的。
2、
public Result editCtrlEquipment(Ctrlequipment ctrlEquipment) {
Ctrlequipment oldCtrlEquipment = ctrlEquipmentDao.findById(ctrlEquipment.getId()).get();
BeanUtils.copyProperties(ctrlEquipment, oldCtrlEquipment, NullAttributeUtils.getNullPropertyNames(ctrlEquipment));
ctrlEquipmentDao.save(oldCtrlEquipment);
return Result.success();
}
public class NullAttributeUtils {
public static String[] getNullPropertyNames (Object source) {
final BeanWrapper src = new BeanWrapperImpl(source);
java.beans.PropertyDescriptor[] pds = src.getPropertyDescriptors();
Set<String> emptyNames = new HashSet<String>();
for(java.beans.PropertyDescriptor pd : pds) {
Object srcValue = src.getPropertyValue(pd.getName());
if (srcValue == null) emptyNames.add(pd.getName());
}
String[] result = new String[emptyNames.size()];
return emptyNames.toArray(result);
}
}
这里的思路:先去数据库根据id查到这条要修改的数据oldCtrlEquipment ,然后把ctrlEquipment中不为空的属性值赋给oldCtrlEquipment ,再把oldCtrlEquipment 更新到数据库中。
NullAttributeUtils 这个工具类就是拿到ctrlEquipment中值为null的属性。
BeanUtils.copyProperties()这个自己去查阅是什么功能吧。