Spring data JPA只查询部分字段问题

文章目录

• • 背景
  • 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对象

字段和查询的字段对应，需要提供构造函数:

@Datapublic 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 {                public static <T> T processResult(Tuple source,Class<T> targetClass) {            Object instantiate = BeanUtils.instantiate(targetClass);            convertTupleToBean(source,instantiate,null);            return (T) instantiate;        }                public static <T> T processResult(Tuple source,Class<T> targetClass,String... ignoreProperties) {            Object instantiate = BeanUtils.instantiate(targetClass);            convertTupleToBean(source,instantiate,ignoreProperties);            return (T) instantiate;        }                public static void convertTupleToBean(Tuple source,Object target){            convertTupleToBean(source,target,null);        }                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属性是否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返回实体的部分指定字段的方法总结

来源地址：https://blog.csdn.net/jgku/article/details/128581548