快捷導(dǎo)航

Spring data JPA只查詢部分字段問題及解決

更新時間：2024年08月12日 09:35:03 作者：北極象

這篇文章主要介紹了Spring data JPA只查詢部分字段問題及解決,具有很好的參考價值,希望對大家有所幫助,如有錯誤或未考慮完全的地方,望不吝賜教

背景

在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

網(wǎng)上搜索有多種解決方案。這里列舉一下。

經(jīng)過驗證，本人采取了第一種方案，證明是可行的。

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對象

字段和查詢的字段對應(yīng)，需要提供構(gòu)造函數(shù)：

@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();

其它方案

查詢中構(gòu)造新對象

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需要提供相應(yīng)的構(gòu)造函數(shù)。

自己寫convertor

repository 返回 Tuple 對象，自己寫代碼手動轉(zhuǎn)換為指定對象，repository層使用native查詢。

這里要借助輔助類：

class NativeResultProcessUtils {

        /**
         * tuple轉(zhuǎn)實體對象
         * @param source tuple對象
         * @param targetClass 目標(biāo)實體class
         * @param <T> 目標(biāo)實體類型
         * @return 目標(biāo)實體
         */
        public static <T> T processResult(Tuple source,Class<T> targetClass) {
            Object instantiate = BeanUtils.instantiate(targetClass);
            convertTupleToBean(source,instantiate,null);
            return (T) instantiate;
        }

        /**
         *
         * tuple轉(zhuǎn)實體對象
         * @param source tuple對象
         * @param targetClass 目標(biāo)實體class
         * @param <T> 目標(biāo)實體類型
         * @param ignoreProperties 要忽略的屬性
         * @return 目標(biāo)實體
         */
        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中屬性名相同的值復(fù)制到實體中
         * @param source tuple對象
         * @param target 目標(biāo)對象實例
         */
        public static void convertTupleToBean(Tuple source,Object target){
            convertTupleToBean(source,target,null);
        }

        /**
         * 把tuple中屬性名相同的值復(fù)制到實體中
         * @param source tuple對象
         * @param target 目標(biāo)對象實例
         * @param ignoreProperties 要忽略的屬性
         */
        public static void convertTupleToBean(Tuple source,Object target, String... ignoreProperties){
            //目標(biāo)class
            Class<?> actualEditable = target.getClass();
            //獲取目標(biāo)類的屬性信息
            PropertyDescriptor[] targetPds = BeanUtils.getPropertyDescriptors(actualEditable);
            //忽略列表
            List<String> ignoreList = (ignoreProperties != null ? Arrays.asList(ignoreProperties) : null);

            //遍歷屬性節(jié)點信息
            for (PropertyDescriptor targetPd : targetPds) {
                //獲取set方法
                Method writeMethod = targetPd.getWriteMethod();
                //判斷字段是否可以set
                if (writeMethod != null && (ignoreList == null || !ignoreList.contains(targetPd.getName()))) {
                    //獲取source節(jié)點對應(yīng)的屬性
                    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(
                                "Could not copy property '" + targetPd.getName() + "' from source to target", ex);
                        }
                    }
                }
            }
        }

    }