文章目录
- 背景
- JPA 2.1以上的解决办法
- 实体中增加named query和result map
- 定义一个新的DTO对象
- repository中定义查询接口
- 其它方案
- 查询中构造新对象
- 自己写convertor
- 使用entityManager的Transformers.aliasToBean
- 使用entityManager的Transforms.ALIAS_TO_ENTITY_MAP
- 参考链接
背景
在JPA查询中,有时只需要查部分字段,这时jpa repository查出的是map,无法映射到Entity类。会提示错误:
org.springframework.core.convert.ConverterNotFoundException:
No converter found capable of converting from type
[org.springframework.data.jpa.repository.query.AbstractJpaQuery$TupleConverter$TupleBackedMap] to type
网上搜索有多种解决方案。这里列举一下。经过验证,本人采取了第一种方案,证明是可行的。
JPA 2.1以上的解决办法
实体中增加named query和result map
@SqlResultSetMapping(name = "EBookInfo",classes = @ConstructorResult(targetClass = EBookInfo.class,columns = {@ColumnResult(name = "book_id", type = Long.class),@ColumnResult(name = "book_name", type = String.class),@ColumnResult(name = "file_type", type = String.class)}))
@NamedNativeQuery(name = "listExpressEbooks",query = "select book_id, book_name, file_type from ebook order by update_date desc",resultSetMapping = "EBookInfo")
@Entity
@Table(name = "ebook")
public class Ebook {private Long bookId;private Integer authorId;private String authorName;private Integer categoryId;private String bookName;private String subTitle;private String tags;private String isbn;private String edition;private Byte bookType;private Integer star;private Integer downloadCount;private Byte status;private String fileType;private String outline;private String introduction;private String preface;private String cover;private Float price;private String publisher;private String bgColor;private String foreColor;private String titleColor;private String coverBackgroundId;private String coverPictureId;private Integer coverTemplateId;private String coverPictureMode;private Integer pageMode;private Timestamp requestDate;private Timestamp publishDate;private Timestamp updateDate;
定义一个新的DTO对象
字段和查询的字段对应,需要提供构造函数:
@Data
public class EBookInfo {private Long bookId;private String bookName; private String fileType;public EBookInfo(Long bookId, String bookName, String fileType) {this.bookId = bookId;this.bookName = bookName;this.fileType = fileType;}}
repository中定义查询接口
@Query(name = "listExpressEbooks", nativeQuery = true)public List<EBookInfo> listExpressEbooks();
其它方案
查询中构造新对象
public List<Blog> selectByYearMonth(String year, String month, int status) {String sql = String.format("select new Blog(blog.id, blog.title, blog.abs, blog.createtime) from Blog blog where blog.status = %d and YEAR(createtime) = %s and MONTH(createtime) = %s order by blog.createtime desc", status, year, month);//Query query = this.em.createNativeQuery(sql, "ExpressedResult");Query query = this.em.createQuery(sql);List results = query.getResultList();return results;
}
上述方法是之前我项目中代码库里的写法,Blog需要提供相应的构造函数。
自己写convertor
repository 返回 Tuple 对象,自己写代码手动转换为指定对象,repository层使用native查询。
这里要借助辅助类:
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.instantiate(targetClass);convertTupleToBean(source,instantiate,null);return (T) instantiate;}/**** tuple转实体对象* @param source tuple对象* @param targetClass 目标实体class* @param <T> 目标实体类型* @param ignoreProperties 要忽略的属性* @return 目标实体*/public static <T> T processResult(Tuple source,Class<T> targetClass,String... ignoreProperties) {Object instantiate = BeanUtils.instantiate(targetClass);convertTupleToBean(source,instantiate,ignoreProperties);return (T) instantiate;}/*** 把tuple中属性名相同的值复制到实体中* @param source tuple对象* @param target 目标对象实例*/public static void convertTupleToBean(Tuple source,Object target){convertTupleToBean(source,target,null);}/*** 把tuple中属性名相同的值复制到实体中* @param source tuple对象* @param target 目标对象实例* @param ignoreProperties 要忽略的属性*/public static void convertTupleToBean(Tuple source,Object target, String... ignoreProperties){//目标classClass<?> 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();//判断字段是否可以setif (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属性是否privateif (!Modifier.isPublic(writeMethod.getDeclaringClass().getModifiers())) {writeMethod.setAccessible(true);}//写入targetwriteMethod.invoke(target, value);}catch (Throwable ex) {throw new FatalBeanException("Could not copy property '" + targetPd.getName() + "' from source to target", ex);}}}}}}
使用entityManager的Transformers.aliasToBean
未验证,Spring data jpa未必支持
使用entityManager的Transforms.ALIAS_TO_ENTITY_MAP
未验证
参考链接
- 解决JPA Native 查询不能使用投影(Projection)的问题
- spring data jpa返回实体的部分指定字段的方法总结