使用Jpa进行多表关联查询，利用java反射将查询结果封装到对象中
在使用Jpa进行数据库查询的时候，经常会遇到这样的问题：多变关联查询，查询结果不是和数据库表对应的实体类，如何封装到一个对象中？这里总结了一下自己的方法，利用java的反射来进行数据的封装。
Repository接口
package com.test.library.dao;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import javax.persistence.Tuple;
import java.util.List;
public interface BookInfoRepository extends JpaRepository<BookInfo,Integer> {
@Query(value = "SELECT bi.id,bi.name,COUNT(bb.id) COUNT FROM book_info bi " +
"LEFT JOIN book_borrow bb ON bi.id=bb.book_id GROUP BY bi.id ORDER BY COUNT DESC",nativeQuery = true)
List<Tuple> bookCount();
}
查询的结果是List<Tuple>的集合，然后自己写一个利用java反射进行数据封装的工具类
接收查询结果的实体类
package com.test.library.vo;
import lombok.Data;
import java.math.BigInteger;
//统计实体类
@Data
public class BookCountVO {
private Integer id;
private String name;
private BigInteger count;
}
封装数据到对象的工具类
package com.test.library.utils;
import org.springframework.beans.BeanUtils;
import org.springframework.beans.FatalBeanException;
import org.springframework.util.ClassUtils;
import javax.persistence.Tuple;
import java.beans.PropertyDescriptor;
import java.lang.reflect.Method;
import java.lang.reflect.Modifier;
import java.util.Arrays;
import java.util.List;
public class NativeResultProcessUtils {
/**
* tuple转实体对象
* @param source tuple对象
* @param targetClass 目标实体class
* @param <T> 目标实体类型
* @return 目标实体
*/
public static <T> T processResult(Tuple source, Class<T> targetClass) {
Object instantiate = BeanUtils.instantiateClass(targetClass);
convertTupleToBean(source,instantiate,null);
return (T) instantiate;
}
/**
* 把tuple中属性名相同的值复制到实体中
* @param source tuple对象
* @param target 目标对象实例
* @param ignoreProperties 要忽略的属性
*/
public static void convertTupleToBean(Tuple source,Object target, String... ignoreProperties){
//目标class
Class<?> actualEditable = target.getClass();
//获取目标类的属性信息
PropertyDescriptor[] targetPds = BeanUtils.getPropertyDescriptors(actualEditable);
//忽略列表
List<String> ignoreList = (ignoreProperties != null ? Arrays.asList(ignoreProperties) : null);
//遍历属性节点信息
for (PropertyDescriptor targetPd : targetPds) {
//获取set方法
Method writeMethod = targetPd.getWriteMethod();
//判断字段是否可以set
if (writeMethod != null && (ignoreList == null || !ignoreList.contains(targetPd.getName()))) {
//获取source节点对应的属性
String propertyName = targetPd.getName();
Object value = source.get(propertyName);
if(value!=null && ClassUtils.isAssignable(writeMethod.getParameterTypes()[0], value.getClass())) {
try {
//判断target属性是否private
if (!Modifier.isPublic(writeMethod.getDeclaringClass().getModifiers())) {
writeMethod.setAccessible(true);
}
//写入target
writeMethod.invoke(target, value);
}
catch (Throwable ex) {
throw new FatalBeanException(
"不能复制属性" + targetPd.getName(), ex);
}
}
}
}
}
}
在service层调用BookInfoRepository 接口的bookCount()方法查询数据，利用工具类将数据封装到BookCountVO 对象中。
@Autowired
private BookInfoRepository bookRepository;
public List<BookCountVO> bookCount() {
List<Tuple> tupleList = bookRepository.bookCount();
List<BookCountVO> bookCountVOList = new ArrayList<>();
for (int i = 0; i < tupleList.size(); i++) {
Tuple tuple = tupleList.get(i);
BookCountVO bookCountVO = NativeResultProcessUtils.processResult(tuple, BookCountVO.class);
bookCountVOList.add(bookCountVO);
}
return bookCountVOList;
}
