你懂Jpa自定義原生sql語句的查詢結(jié)果如何轉(zhuǎn)化為對象嗎？

在使用Spring Data Jpa框架時，根據(jù)業(yè)務(wù)需求我們通常需要進(jìn)行復(fù)雜的數(shù)據(jù)庫查詢，并返回我們自定義的實體類，而在該框架下，目前僅僅支持返回與數(shù)據(jù)庫映射進(jìn)行持久化的POJO實體。雖然在框架上我們可以使用@Query注解執(zhí)行我們自定義的sql語句，但是其返回值為List<Object[]> 類型，即多個Object數(shù)組的List集合。

下面我們介紹一下關(guān)于在Spring Data Jpa框架下使用自定義查詢語句返回自定義實體的解決方案。

解決方案一：

例如我們有如下相關(guān)聯(lián)實體：

User實體
@Entity
@Getter
@Setter
@Table(name="tab_user")
public class User extends BaseEntity implements Serializable {
 
    @Id
    @NotNull(groups = Update.class)
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column(name = "user_id")
    @ApiModelProperty(value = "主鍵")
    private Long userId;
 
    @Column(name = "username",nullable = false)
    @NotBlank
    @ApiModelProperty(value = "姓名")
    private String username;
  
 
    @Column(name = "branch_id",nullable = false)
    @NotNull
    @ApiModelProperty(value = "機(jī)構(gòu)id")
    private Long branchId;
 
    @Column(name = "job_id",nullable = false)
    @NotNull
    @ApiModelProperty(value = "崗位id")
    private Long jobId;
 
 
    @Override
    public int hashCode() {
        return Objects.hash(id, username);
    }
}
Job實體
package com.sgcc.modules.system.domain;
 
import com.sgcc.base.BaseEntity;
import io.swagger.annotations.ApiModelProperty;
import lombok.Getter;
import lombok.Setter;
 
import javax.persistence.*;
import javax.validation.constraints.NotBlank;
import javax.validation.constraints.NotNull;
import java.io.Serializable;
import java.util.Objects;
 
/**
 *  @author: liman
 *  @Date: 2020/7/17 17:37
 *  @Description: 員工表
 */
 
@Entity
@Getter
@Setter
@Table(name="tab_job")
public class JobTab extends BaseEntity implements Serializable {
 
    @Id
    @Column(name = "job_id")
    @NotNull(groups = Update.class)
    @ApiModelProperty(value = "ID", hidden = true)
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long jobId;
 
    @Column(name = "job_name", nullable = false)
    @NotBlank
    @ApiModelProperty(value = "崗位名稱")
    private String jobName;
 
    @Column(name = "branch_id", nullable = false)
    @NotBlank
    @ApiModelProperty(value = "崗位機(jī)構(gòu)")
    private String branchId;
 
    @Column(name = "job_type", nullable = false)
    @NotBlank
    @ApiModelProperty(value = "崗位類別")
    private String jobType;
 
 
  
}
Branch實體
package com.sgcc.modules.system.domain;
 
import cn.hutool.core.bean.BeanUtil;
import cn.hutool.core.bean.copier.CopyOptions;
import com.sgcc.base.BaseEntity;
import io.swagger.annotations.ApiModelProperty;
import lombok.Getter;
import lombok.Setter;
 
import javax.persistence.*;
import javax.validation.constraints.NotBlank;
import javax.validation.constraints.NotNull;
import java.io.Serializable;
 
/**
 * @website https://el-admin.vip
 * @description /
 * @author liman
 * @date 2020-07-07
 **/
@Entity
@Getter
@Setter
@Table(name="tab_branch")
public class Branch extends BaseEntity implements Serializable {
 
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column(name = "id")
    @ApiModelProperty(value = "id")
    private Long id;
 
    @Column(name = "branch_id",nullable = false)
    @NotNull
    @ApiModelProperty(value = "機(jī)構(gòu)id")
    private Long branchId;
 
    @Column(name = "branch_name",nullable = false)
    @NotBlank
    @ApiModelProperty(value = "機(jī)構(gòu)名稱")
    private String branchName;
 
    @Column(name = "level",nullable = false)
    @NotNull
    @ApiModelProperty(value = "級別")
    private Integer level;
 
    @Column(name = "p_id",nullable = false)
    @NotNull
    @ApiModelProperty(value = "父級機(jī)構(gòu)id")
    private Long pId;
 
    @Column(name = "dept_sort",nullable = false)
    @NotNull
    @ApiModelProperty(value = "部門排序級別")
    private Integer deptSort;
 
 
    }
}

要轉(zhuǎn)化成的實體Dto如下：

package com.sgcc.modules.system.domain.resp;
 
import lombok.Getter;
import lombok.Setter;
 
/**
 *  @author: liman
 *  @Date: 2020/7/17 16:42
 *  @Description: 測評對象返回類
 */
 
@Getter
@Setter
 
public class EvaUserResp {
    /** 所在部門*/
    private String branchName;
    /** 測評對象*/
    private String userName;
    /** 職位*/
    private String jobName;
 
    /** 職位類別*/
    private String jobType;
 
    public EvaUserResp() {
    }
 
    public EvaUserResp(String branchName, String userName, String jobName, String jobType) {
        this.branchName = branchName;
        this.userName = userName;
        this.jobName = jobName;
        this.jobType = jobType;
    }
}

下面我們來看下DAO層的JPA處理接口類

public interface BookInfoRepository extends JpaRepository<BookInfo, BigDecimal> {
 
        @Query(value = "SELECT b.branch_name as branchName," +
            "a.username as userName," +
            "c.job_name as jobName," +
            "c.job_type as jobType FROM tab_user a " +
            "INNER JOIN tab_branch b ON b.branch_id = a.branch_id " +
            "INNER JOIN tab_job c on c.job_id = a.job_id " +
            "WHERE  user_id = :userId ", nativeQuery = true)
    List<Object[]>findByUserId(@Param("userId") Long userId);
}

我們來解釋一下上面這個處理接口類中的要點：

①nativeQuery=true，屬性的設(shè)置，是表明該方法中的sql以數(shù)據(jù)庫的sql語句格式對待。

②返回值為List<Object[]>，由于我們之前說過Jpa無法自動完成查詢結(jié)果到自定義實體的映射，所以我們要使用該對象接收。

最后我們看下將該List<Object[]>對象轉(zhuǎn)換為我們自定義實體的工具類：

public class EntityUtils {
    private static Logger logger = LoggerFactory.getLogger(EntityUtils.class);
 
    /**
     * 將數(shù)組數(shù)據(jù)轉(zhuǎn)換為實體類
     * 此處數(shù)組元素的順序必須與實體類構(gòu)造函數(shù)中的屬性順序一致
     *
     * @param list           數(shù)組對象集合
     * @param clazz          實體類
     * @param <T>            實體類
     * @param model          實例化的實體類
     * @return 實體類集合
     */
    public static <T> List<T> castEntity(List<Object[]> list, Class<T> clazz, Object model) {
        List<T> returnList = new ArrayList<T>();
        if (list.isEmpty()) {
            return returnList;
        }
        //獲取每個數(shù)組集合的元素個數(shù)
        Object[] co = list.get(0);
 
        //獲取當(dāng)前實體類的屬性名、屬性值、屬性類別
        List<Map> attributeInfoList = getFiledsInfo(model);
        //創(chuàng)建屬性類別數(shù)組
        Class[] c2 = new Class[attributeInfoList.size()];
        //如果數(shù)組集合元素個數(shù)與實體類屬性個數(shù)不一致則發(fā)生錯誤
        if (attributeInfoList.size() != co.length) {
            return returnList;
        }
        //確定構(gòu)造方法
        for (int i = 0; i < attributeInfoList.size(); i++) {
            c2[i] = (Class) attributeInfoList.get(i).get("type");
        }
        try {
            for (Object[] o : list) {
                Constructor<T> constructor = clazz.getConstructor(c2);
                returnList.add(constructor.newInstance(o));
            }
        } catch (Exception ex) {
            logger.error("實體數(shù)據(jù)轉(zhuǎn)化為實體類發(fā)生異常：異常信息：{}", ex.getMessage());
            return returnList;
        }
        return returnList;
    }
 
    /**
     * 根據(jù)屬性名獲取屬性值
     *
     * @param fieldName 屬性名
     * @param modle     實體類
     * @return 屬性值
     */
    private static Object getFieldValueByName(String fieldName, Object modle) {
        try {
            String firstLetter = fieldName.substring(0, 1).toUpperCase();
            String getter = "get" + firstLetter + fieldName.substring(1);
            Method method = modle.getClass().getMethod(getter, new Class[]{});
            Object value = method.invoke(modle, new Object[]{});
            return value;
        } catch (Exception e) {
            return null;
        }
    }
 
    /**
     * 獲取屬性類型(type)，屬性名(name)，屬性值(value)的map組成的list
     *
     * @param model 實體類
     * @return list集合
     */
    private static List<Map> getFiledsInfo(Object model) {
        Field[] fields = model.getClass().getDeclaredFields();
        List<Map> list = new ArrayList(fields.length);
        Map infoMap = null;
        for (int i = 0; i < fields.length; i++) {
            infoMap = new HashMap(3);
            infoMap.put("type", fields[i].getType());
            infoMap.put("name", fields[i].getName());
            infoMap.put("value", getFieldValueByName(fields[i].getName(), model));
            list.add(infoMap);
        }
        return list;
    }
}

在執(zhí)行DAO層方法，獲得相應(yīng)的List<Object[]>對象調(diào)用工具類中的靜態(tài)方法castEntity，即可將數(shù)據(jù)轉(zhuǎn)換為自定義實體。

在使用該解決方案時，需注意以下幾點要求：

①自定義查詢語句中的查詢字段的順序一定要和自定義實體的構(gòu)造方法中的屬性順序一致。

②此種方案在解決特別復(fù)雜的查詢語句時很高效，因為只需自定義查詢語句，與數(shù)據(jù)庫進(jìn)行一次交互即可，效率可觀。但對程序的規(guī)范性要求比較高。

③此方案在解決當(dāng)前項目數(shù)據(jù)庫中數(shù)據(jù)表在業(yè)務(wù)需求下創(chuàng)建而不符合使用JPA框架創(chuàng)建持久化實體之間的關(guān)聯(lián)關(guān)系（即因為業(yè)務(wù)需求，所建立庫表不符合數(shù)據(jù)庫建庫規(guī)范），而又需要進(jìn)行多表關(guān)聯(lián)進(jìn)行復(fù)雜查詢時，很實用。

特別說明：上面所舉的例子只是單純?yōu)榱搜菔敬朔桨?，因為上面表關(guān)聯(lián)之簡單要獲得如上的結(jié)果使用JPA框架也可輕松實現(xiàn)。

具體轉(zhuǎn)化demo如下：

 /**object對象轉(zhuǎn)化EvaUserResp對象*/
 List<Object[]> evaUserResp = userRepository.findByUserId(x.getUserId());
 List<EvaUserResp> evaUserResps = EntityUtils.castEntity(evaUserResp, EvaUserResp.class, new EvaUserResp());

解決方案二：

修改DAO層的JPA處理接口類：

@Query(value = "SELECT new EvaUserResp(b.branch_name as branchName,a.username as userName,c.job_name as jobName,c.job_type as jobType ) FROM tab_user a INNER JOIN tab_branch b ON b.branch_id = a.branch_id INNER JOIN tab_job c on c.job_id = a.job_id WHERE user_id = 4 ", nativeQuery = true)
List<EvaUserResp> findByUserId(@Param("userId") Long userId);

注意：這次的EvaUserResp最好寫全路徑，程序有可能無法定位到該類

解決方案三：

我要解決這樣一條sql查詢出來的結(jié)果：

SELECT b.branch_name as branchName," +
            "a.username as userName," +
            "c.job_name as jobName," +
            "c.job_type as jobType FROM tab_user a " +
            "INNER JOIN tab_branch b ON b.branch_id = a.branch_id " +
            "INNER JOIN tab_job c on c.job_id = a.job_id " +
            "WHERE  user_id = :userId

第一步，首先要引入阿里的fastJson

<dependency>

<groupId>com.alibaba</groupId>

<artifactId>fastjson</artifactId>

<version>1.2.56</version>

</dependency>

第二步：Repository層用List<Map<String,String>>接收返回的數(shù)據(jù)

 @Query(value = "SELECT b.branch_name as branchName," +
            "a.username as userName," +
            "c.job_name as jobName," +
            "c.job_type as jobType FROM tab_user a " +
            "INNER JOIN tab_branch b ON b.branch_id = a.branch_id " +
            "INNER JOIN tab_job c on c.job_id = a.job_id " +
            "WHERE  user_id = :userId ", nativeQuery = true)
    List<Map<String,String>>  findByUserId(@Param("userId") Long userId);

第三步進(jìn)行轉(zhuǎn)化：

    List<Map<String,String>> evaUserResp = userRepository.findByUserId(x.getUserId());
    String irsStr = JSON.toJSONString(evaUserResp);
    List<EvaUserResp> evaUserResps = JSON.parseArray(irsStr,EvaUserResp.class);

感謝你看到這里，我是程序員麥冬，一個java開發(fā)從業(yè)者，深耕行業(yè)六年了，每天都會分享java相關(guān)技術(shù)文章或行業(yè)資訊

歡迎大家關(guān)注和轉(zhuǎn)發(fā)文章，后期還有福利贈送！

?著作權(quán)歸作者所有,轉(zhuǎn)載或內(nèi)容合作請聯(lián)系作者
【社區(qū)內(nèi)容提示】社區(qū)部分內(nèi)容疑似由AI輔助生成，瀏覽時請結(jié)合常識與多方信息審慎甄別。
平臺聲明：文章內(nèi)容（如有圖片或視頻亦包括在內(nèi)）由作者上傳并發(fā)布，文章內(nèi)容僅代表作者本人觀點，簡書系信息發(fā)布平臺，僅提供信息存儲服務(wù)。

友情鏈接更多精彩內(nèi)容