快捷導(dǎo)航

Spring?Data?Jpa返回自定義對象的3種方法實例

更新時間：2022年08月31日 10:03:14 作者：我是壞人哦

在使用Spring Data Jpa框架時,根據(jù)業(yè)務(wù)需求我們通常需要進(jìn)行復(fù)雜的數(shù)據(jù)庫查詢,下面這篇文章主要給大家介紹了關(guān)于Spring?Data?Jpa返回自定義對象的3種方法,文中通過實例代碼介紹的非常詳細(xì),需要的朋友可以參考下

@Entity
@NoArgsConstructor
@AllArgsConstructor
@Table(name = "tasks")
@Data
public class Tasks extends BaseEntity {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private int taskId;
    private Integer websiteId;
    private String status;
    private String lastOperator;
    private String lastOperationTime;
    private String jobId;
    private int retryTimes;
}

websites表對應(yīng)的Entity

@Entity
@Table(name = "websites")
@Data
@NoArgsConstructor
@AllArgsConstructor
public class Websites extends BaseEntity{
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private int websiteId;
    private String country;
    private String websiteName;
    private String baseUrl;
    private String smartphoneUrl;
    private String tabletUrl;
    private String smartdeviceUrl;
    private String websiteCategory;
    private String webtypeRefreshRate;
    private String urlRefreshRate;
    private String configTime;
    private String configDesc;
}

自定義對象

@Data
@NoArgsConstructor
@AllArgsConstructor
public class CustomizedDto implements Serializable {
    private static final long serialVersionUID = -7242005560621561106L;
    private String country;
    private String websiteName;
    private String baseUrl;
    private String status;
}

方法一、簡單查詢直接new對象

使用hql，將結(jié)果返回到new出來的自定義對象中，注意，自定義對象中要有對應(yīng)的構(gòu)造函數(shù)。

@Query(value = "select new com.bigdata.mrcrawler.dto.CustomizedDto(w.country,w.websiteName,w.baseUrl,t.status) " +
            "from Websites w join Tasks t on w.id=t.websiteId " +
            "where t.status=?1")
    List<CustomizedDto> getByStatus1(String status);

但是這個方法只使用于簡單的查詢語句，如果遇到復(fù)雜查詢需要使用原生SQL（即nativeQuery=true）時，此方法不適用，會拋出如下異常。

@Query(value = "select w.country as country,w.website_name as websiteName,w.base_url as baseUrl,t.status as status " +
            "from websites w join tasks t on w.website_id=t.website_id " +
            "where t.status=?1",nativeQuery = true)
    List<CustomizedDto> getByStatus2(String status);

No converter found capable of converting from type [org.springframework.data.jpa.repository.query.AbstractJpaQuery$TupleConverter$TupleBackedMap] to type [com.bigdata.mrcrawler.dto.CustomizedDto]

方法二、Service層使用EntityManager

直接在Service層使用EntityManager進(jìn)行查詢，可以自由組裝各種復(fù)雜sql。

    public List<CustomizedDto> t(String param) {
        String sql = "select w.country as country,w.website_name as websiteName,w.base_url as baseUrl,t.status as status " +
                "from websites w join tasks t on w.website_id=t.website_id " +
                "where t.status='" + param + "'";
        List<CustomizedDto> res = entityManager.createNativeQuery(sql).getResultList();
        return res;
    }

但是會有sql注入問題，例如：param傳入Running' or 1=1 --\t 上述查詢會將查詢整個數(shù)據(jù)表。解決方法如下，使用預(yù)編譯防止sql注入問題。

    public List<CustomizedDto> t(String param) {
        String sql = "select w.country as country,w.website_name as websiteName,w.base_url as baseUrl,t.status as status " +
                "from websites w join tasks t on w.website_id=t.website_id " +
                "where t.status=:param" ;
        Query nativeQuery = entityManager.createNativeQuery(sql);
        nativeQuery.setParameter("param", param);
        List<CustomizedDto> res  = nativeQuery.getResultList();
        return res;
    }

然而，個人很不喜歡這種代碼中嵌入大片大片sql的寫法，排查問題的時候看得頭疼（別問，問就是被坑過/捂臉.jpg/）。所以方法二即便可行，我私心還是不想推薦。

方法三、Dao層使用Map接收自定義對象

使用List<Map> 接收返回結(jié)果，無論是原生sql還是hql都支持，當(dāng)然也支持分頁，只需要把返回對象改為Page<Map>即可，其他操作與普通分頁沒有差別。

@Query(value = "select w.country as country,w.website_name as websiteName,w.base_url as baseUrl,t.status as status " +
            "from websites w join tasks t on w.website_id=t.website_id " +
            "where t.status=?1",nativeQuery = true)
    List<Map> getByStatus3(String status);

Service層也需要用List<Map>進(jìn)行接收。

    public List<Map> t(String param) {
        return testRepository.getByStatus3(param);
    }

如果后續(xù)還有其他操作，還是需要轉(zhuǎn)成自定義對象怎么辦，畢竟Map操作起來挺麻煩的?？梢杂萌缦陆鉀Q方案，先用Object進(jìn)行接收，然后強(qiáng)轉(zhuǎn)成自定義對象List。

    public List<CustomizedDto> t(String param) {
        Object data = testRepository.getByStatus3(param);
        return  (List<CustomizedDto>)data;
    }

強(qiáng)轉(zhuǎn)需要注意自定義對象和數(shù)據(jù)庫中字段類型的強(qiáng)一致性，如數(shù)據(jù)庫中datetime類型，自定義對象對應(yīng)的字段必須是Date，不能是String，否則轉(zhuǎn)換的時候會有問題，數(shù)據(jù)會丟失。