JPA如何將查詢結(jié)果轉(zhuǎn)換為DTO對(duì)象

更新時(shí)間：2022年02月23日 09:47:54 作者：itlgl

這篇文章主要介紹了JPA如何將查詢結(jié)果轉(zhuǎn)換為DTO對(duì)象，具有很好的參考價(jià)值，希望對(duì)大家有所幫助。如有錯(cuò)誤或未考慮完全的地方，望不吝賜教

前言

JPA支持使用@Query自定義查詢，查詢的結(jié)果需要字節(jié)用DTO對(duì)象接收，如果使用HQL的查詢語(yǔ)句，可以將直接將DTO對(duì)象的構(gòu)造方法傳入hql中，直接轉(zhuǎn)為DTO對(duì)象；而如果使用native sql查詢的方式，只能將返回結(jié)果用Object[]對(duì)象接收，然后DTO設(shè)置對(duì)象的構(gòu)造來(lái)接收Object[]里面的參數(shù)完成DTO對(duì)象的轉(zhuǎn)換。

例子

mysql數(shù)據(jù)庫(kù)表

用戶表

CREATE TABLE `pos_user` (
? `id` bigint(20) NOT NULL AUTO_INCREMENT,
? `user_name` varchar(50) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,
? `user_pwd` varchar(255) DEFAULT NULL,
? `user_type` int(11) DEFAULT NULL,
? `parent_id` bigint(20) DEFAULT NULL,
? `user_status` int(11) DEFAULT NULL,
? `distributor_id` bigint(20) DEFAULT NULL,
? `creator_identity_type` int(2) DEFAULT NULL,
? `creator_id` bigint(20) DEFAULT NULL,
? `create_date` varchar(50) DEFAULT NULL,
? PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=22 DEFAULT CHARSET=utf8;

設(shè)備表

CREATE TABLE `pos_device` (
? `id` bigint(20) NOT NULL AUTO_INCREMENT,
? `imei` varchar(120) NOT NULL,
? `mac` varchar(120) NOT NULL,
? `unique_code` varchar(120) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,
? `type` varchar(100) DEFAULT NULL,
? `system_version` varchar(100) DEFAULT NULL,
? `distributor_id` bigint(20) DEFAULT NULL,
? `creator_identity_type` int(2) DEFAULT NULL,
? `creator_id` bigint(20) DEFAULT NULL,
? `create_date` varchar(50) DEFAULT NULL,
? PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=31 DEFAULT CHARSET=utf8;

用戶和設(shè)備關(guān)聯(lián)表

CREATE TABLE `pos_user_device_relation` (
? `id` bigint(20) NOT NULL AUTO_INCREMENT,
? `device_id` bigint(20) DEFAULT NULL,
? `user_id` bigint(20) DEFAULT NULL,
? PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8;

可以看到用戶和設(shè)備關(guān)聯(lián)表中有用戶id和設(shè)備id

聯(lián)合查詢的需求

想列出pos_user_device_relation表中所有pos_user的distributor_id=1的所有用戶和設(shè)備，要求返回的信息包括用戶的username、type信息和設(shè)備的imei、mac等信息。

sql語(yǔ)句

SELECT
pdr.id,
pdr.device_id,
pd.imei,
pd.mac,
pd.unique_code,
pd.type,
pd.system_version,
pdr.user_id,
pu.user_name,
pu.user_type
FROM
pos_user_device_relation pdr, pos_user pu, pos_device pd
WHERE pdr.device_id = pd.id AND pdr.user_id = pu.id AND pdr.user_id in (select id from pos_user where distributor_id=1) limit 0,10

查詢可以正常得到結(jié)果，結(jié)果行是這樣的：

+----+-----------+---------------------+-------------------+--------------------------+----------+----------------+---------+---------------+-----------+
| id | device_id | imei | mac | unique_code | type | system_version | user_id | user_name | user_type |
+----+-----------+---------------------+-------------------+--------------------------+----------+----------------+---------+---------------+-----------+

如何在JPA中映射為DTO對(duì)象

DTO對(duì)象字段定義如下：

private Long posUserDeviceId;
private Long deviceId;
private String deviceImei;
private String deviceMac;
private String deviceUniqueCode;
private String deviceType;
private String deviceSystemVersion;
private Long userId;
private String username;
private PosUserEntityConstants.UserType userType;

對(duì)象中的PosUserEntityConstants.UserType是一個(gè)自定義轉(zhuǎn)換類型，通過(guò)繼承AttributeConverter將Integer轉(zhuǎn)換為UserType的枚舉。

方法一：使用HQL的方法

Repository的查詢代碼如下：

@Query(
? ? ? ? value = "SELECT\n" +
? ? ? ? ? ? ? ? "new com.hengbao.ethiopiatelecomrecharge.dao.dto.PosUserDeviceRelationDto(\n" +
? ? ? ? ? ? ? ? "pdr.id,\n" +
? ? ? ? ? ? ? ? "pdr.deviceId,\n" +
? ? ? ? ? ? ? ? "pd.imei,\n" +
? ? ? ? ? ? ? ? "pd.mac,\n" +
? ? ? ? ? ? ? ? "pd.uniqueCode,\n" +
? ? ? ? ? ? ? ? "pd.type,\n" +
? ? ? ? ? ? ? ? "pd.systemVersion,\n" +
? ? ? ? ? ? ? ? "pdr.userId,\n" +
? ? ? ? ? ? ? ? "pu.userName,\n" +
? ? ? ? ? ? ? ? "pu.userType\n" +
? ? ? ? ? ? ? ? ") \n" +
? ? ? ? ? ? ? ? "FROM \n" +
? ? ? ? ? ? ? ? "PosUserDeviceRelationEntity pdr, PosUserEntity pu, PosDeviceEntity pd \n" +
? ? ? ? ? ? ? ? "WHERE pdr.deviceId = pd.id AND pdr.userId = pu.id AND pdr.userId in (select id from PosUserEntity where distributorId=?1)",
? ? ? ? countQuery = "SELECT count(*) FROM \n" +
? ? ? ? ? ? ? ? "PosUserDeviceRelationEntity pdr, PosUserEntity pu, PosDeviceEntity pd \n" +
? ? ? ? ? ? ? ? "WHERE pdr.deviceId = pd.id AND pdr.userId = pu.id AND pdr.userId in (select id from PosUserEntity where distributorId=?1)"
)
Page<PosUserDeviceRelationDto> findUserAndDeviceInfoByDistributorId(Long distributorId, Pageable pageable);

可以看到HQL的方法將PosUserDeviceRelationDto的構(gòu)造器直接傳入到HQL語(yǔ)句中，省去了我們自行轉(zhuǎn)換的麻煩。那么PosUserDeviceRelationDto中也要重寫一個(gè)相應(yīng)的構(gòu)造器：

由于項(xiàng)目中使用了lombok，所有最終dto的代碼只是在類上面加上了一些注解，@AllArgsConstructor的注解會(huì)自動(dòng)生成一個(gè)全參數(shù)的構(gòu)造器，構(gòu)造器的順序和字段定義順序一致，類代碼如下：

@Getter
@Setter
@NoArgsConstructor
@AllArgsConstructor
@ToString
public class PosUserDeviceRelationDto implements Serializable {
? ? /**
? ? ?* 版本號(hào)
? ? ?*/
? ? private static final long serialVersionUID = 1L;?
? ? private Long posUserDeviceId;?
? ? private Long deviceId;
? ? private String deviceImei;
? ? private String deviceMac;
? ? private String deviceUniqueCode;
? ? private String deviceType;
? ? private String deviceSystemVersion;?
? ? private Long userId;
? ? private String username;
? ? private PosUserEntityConstants.UserType userType;
}

方法二：使用native query的方式查詢并轉(zhuǎn)換為dto

Repository的查詢代碼如下：

@Query(
? ? ? ? value = "SELECT\n" +
? ? ? ? ? ? ? ? "pdr.id,\n" +
? ? ? ? ? ? ? ? "pdr.device_id,\n" +
? ? ? ? ? ? ? ? "pd.imei,\n" +
? ? ? ? ? ? ? ? "pd.mac,\n" +
? ? ? ? ? ? ? ? "pd.unique_code,\n" +
? ? ? ? ? ? ? ? "pd.type,\n" +
? ? ? ? ? ? ? ? "pd.system_version,\n" +
? ? ? ? ? ? ? ? "pdr.user_id,\n" +
? ? ? ? ? ? ? ? "pu.user_name,\n" +
? ? ? ? ? ? ? ? "pu.user_type\n" +
? ? ? ? ? ? ? ? "FROM\n" +
? ? ? ? ? ? ? ? "pos_user_device_relation pdr, pos_user pu, pos_device pd\n" +
? ? ? ? ? ? ? ? "WHERE pdr.device_id = pd.id AND pdr.user_id = pu.id AND pdr.user_id in (select id from pos_user where distributor_id=?1)",
? ? ? ? countQuery = "SELECT count(*) FROM\n" +
? ? ? ? ? ? ? ? "pos_user_device_relation pdr, pos_user pu, pos_device pd\n" +
? ? ? ? ? ? ? ? "WHERE pdr.device_id = pd.id AND pdr.user_id = pu.id AND pdr.user_id in (select id from pos_user where distributor_id=?1)",
? ? ? ? nativeQuery = true
)
Page<Object[]> findUserAndDeviceInfoByDistributorId2(Long distributorId, Pageable pageable);

可以看到這樣只能用Object[]來(lái)接收結(jié)果集，而不能直接將返回參數(shù)定義為PosUserDeviceRelationDto對(duì)象，否則會(huì)報(bào)no converter的異常。

那如何將Object[]的結(jié)果集轉(zhuǎn)換為PosUserDeviceRelationDto對(duì)象呢？

首先先看一下Object[]每個(gè)對(duì)象的類型：BigInteger BigInteger String String String String String BigInteger String Integer

這是可以發(fā)現(xiàn)雖然mysql數(shù)據(jù)庫(kù)定義的是bigint(20)類型，但是結(jié)果集是BigInteger，不能直接用Long接收，所以專門定義一個(gè)dto的構(gòu)造器如下：

public PosUserDeviceRelationDto(BigInteger posUserDeviceId,
? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? BigInteger deviceId,
? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? String deviceImei,
? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? String deviceMac,
? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? String deviceUniqueCode,
? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? String deviceType,
? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? String deviceSystemVersion,
? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? BigInteger userId,
? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? String username,
? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? Integer userType) {
? ? this.posUserDeviceId = posUserDeviceId == null ? null : posUserDeviceId.longValue();
? ? this.deviceId = deviceId == null ? null : deviceId.longValue();
? ? this.deviceImei = deviceImei;
? ? this.deviceMac = deviceMac;
? ? this.deviceUniqueCode = deviceUniqueCode;
? ? this.deviceType = deviceType;
? ? this.deviceSystemVersion = deviceSystemVersion;
? ? this.userId = userId == null ? null : userId.longValue();
? ? this.username = username;
? ? // UserTypeConverter是繼承自javax.persistence.AttributeConverter的類型轉(zhuǎn)換器
? ? this.userType = new PosUserEntityConstants.UserTypeConverter().convertToEntityAttribute(userType);
}

然后直接調(diào)用構(gòu)造即可：

Page<Object[]> userAndDeviceInfoByDistributorId2 = posUserDeviceRelationRepository.findUserAndDeviceInfoByDistributorId2(1L, PageRequest.of(0, 10));
for (Object[] objects : userAndDeviceInfoByDistributorId2.getContent()) {
? ? // 轉(zhuǎn)換成dto的方法一：將objects中的所有參數(shù)強(qiáng)轉(zhuǎn)為對(duì)應(yīng)類型，傳遞到dto的構(gòu)造器中；dto對(duì)象定義好對(duì)應(yīng)的構(gòu)造器
? ? PosUserDeviceRelationDto dto1 = new PosUserDeviceRelationDto(
? ? ? ? ? ? (BigInteger) objects[0],
? ? ? ? ? ? (BigInteger) objects[1],
? ? ? ? ? ? (String ? ?) objects[2],
? ? ? ? ? ? (String ? ?) objects[3],
? ? ? ? ? ? (String ? ?) objects[4],
? ? ? ? ? ? (String ? ?) objects[5],
? ? ? ? ? ? (String ? ?) objects[6],
? ? ? ? ? ? (BigInteger) objects[7],
? ? ? ? ? ? (String ? ?) objects[8],
? ? ? ? ? ? (Integer ? ) objects[9]);
? ? System.out.println(dto1);

網(wǎng)上還能搜到另外一種解決方法，就是通過(guò)反射的方法簡(jiǎn)化dto的轉(zhuǎn)化步驟(http://www.dbjr.com.cn/article/238470.htm)，但是這個(gè)存在bug，如果返回的objects數(shù)組中有一個(gè)值為null，那么getClass()方法獲取類的類型就會(huì)報(bào)錯(cuò)，所以改為將每個(gè)參數(shù)的類型直接傳入進(jìn)去，可以這樣使用反射其實(shí)省不了多少工夫了：

Page<Object[]> userAndDeviceInfoByDistributorId2 = posUserDeviceRelationRepository.findUserAndDeviceInfoByDistributorId2(1L, PageRequest.of(0, 10));
for (Object[] objects : userAndDeviceInfoByDistributorId2.getContent()) {
? ? // 轉(zhuǎn)換成dto的方法二：反射的方法直接調(diào)用構(gòu)造
? ? PosUserDeviceRelationDto dto2 = caseDto(objects, new Class[]{BigInteger.class,
? ? ? ? ? ? ? ? ? ? BigInteger.class,
? ? ? ? ? ? ? ? ? ? String.class,
? ? ? ? ? ? ? ? ? ? String.class,
? ? ? ? ? ? ? ? ? ? String.class,
? ? ? ? ? ? ? ? ? ? String.class,
? ? ? ? ? ? ? ? ? ? String.class,
? ? ? ? ? ? ? ? ? ? BigInteger.class,
? ? ? ? ? ? ? ? ? ? String.class,
? ? ? ? ? ? ? ? ? ? Integer.class},
? ? ? ? ? ? PosUserDeviceRelationDto.class);
? ? System.out.println(dto2);
}
/**
?* 網(wǎng)頁(yè)中直接使用objectArray中獲取每一個(gè)class，但是這樣有一個(gè)問(wèn)題，就是如果獲取的objectArray中有一個(gè)空值的話，不能獲取到class，
?* 導(dǎo)致不能獲取到對(duì)象的構(gòu)造器
?* @param objectArray
?* @param objectClassArray
?* @param dtoClass
?* @param <T>
?* @return
?*/
private <T> T caseDto(Object[] objectArray, Class[] objectClassArray, Class<T> dtoClass) throws Exception {
? ? Constructor<T> constructor = dtoClass.getConstructor(objectClassArray);
? ? return constructor.newInstance(objectArray);
}

例子涉及的部分源代碼

Repository

@Query(
        value = "SELECT\n" +
                "new com.hengbao.ethiopiatelecomrecharge.dao.dto.PosUserDeviceRelationDto(\n" +
                "pdr.id,\n" +
                "pdr.deviceId,\n" +
                "pd.imei,\n" +
                "pd.mac,\n" +
                "pd.uniqueCode,\n" +
                "pd.type,\n" +
                "pd.systemVersion,\n" +
                "pdr.userId,\n" +
                "pu.userName,\n" +
                "pu.userType\n" +
                ") \n" +
                "FROM \n" +
                "PosUserDeviceRelationEntity pdr, PosUserEntity pu, PosDeviceEntity pd \n" +
                "WHERE pdr.deviceId = pd.id AND pdr.userId = pu.id AND pdr.userId in (select id from PosUserEntity where distributorId=?1)",
        countQuery = "SELECT count(*) FROM \n" +
                "PosUserDeviceRelationEntity pdr, PosUserEntity pu, PosDeviceEntity pd \n" +
                "WHERE pdr.deviceId = pd.id AND pdr.userId = pu.id AND pdr.userId in (select id from PosUserEntity where distributorId=?1)"
)
Page<PosUserDeviceRelationDto> findUserAndDeviceInfoByDistributorId(Long distributorId, Pageable pageable);
@Query(
        value = "SELECT\n" +
                "pdr.id,\n" +
                "pdr.device_id,\n" +
                "pd.imei,\n" +
                "pd.mac,\n" +
                "pd.unique_code,\n" +
                "pd.type,\n" +
                "pd.system_version,\n" +
                "pdr.user_id,\n" +
                "pu.user_name,\n" +
                "pu.user_type\n" +
                "FROM\n" +
                "pos_user_device_relation pdr, pos_user pu, pos_device pd\n" +
                "WHERE pdr.device_id = pd.id AND pdr.user_id = pu.id AND pdr.user_id in (select id from pos_user where distributor_id=?1)",
        countQuery = "SELECT count(*) FROM\n" +
                "pos_user_device_relation pdr, pos_user pu, pos_device pd\n" +
                "WHERE pdr.device_id = pd.id AND pdr.user_id = pu.id AND pdr.user_id in (select id from pos_user where distributor_id=?1)",
        nativeQuery = true
)
Page<Object[]> findUserAndDeviceInfoByDistributorId2(Long distributorId, Pageable pageable);

DTO類

@Getter
@Setter
@NoArgsConstructor
@AllArgsConstructor
@ToString
public class PosUserDeviceRelationDto implements Serializable {
    /**
     * 版本號(hào)
     */
    private static final long serialVersionUID = 1L; 
    private Long posUserDeviceId; 
    private Long deviceId;
    private String deviceImei;
    private String deviceMac;
    private String deviceUniqueCode;
    private String deviceType;
    private String deviceSystemVersion; 
    private Long userId;
    private String username;
    private PosUserEntityConstants.UserType userType; 
    public PosUserDeviceRelationDto(BigInteger posUserDeviceId,
                                    BigInteger deviceId,
                                    String deviceImei,
                                    String deviceMac,
                                    String deviceUniqueCode,
                                    String deviceType,
                                    String deviceSystemVersion,
                                    BigInteger userId,
                                    String username,
                                    Integer userType) {
        this.posUserDeviceId = posUserDeviceId == null ? null : posUserDeviceId.longValue();
        this.deviceId = deviceId == null ? null : deviceId.longValue();
        this.deviceImei = deviceImei;
        this.deviceMac = deviceMac;
        this.deviceUniqueCode = deviceUniqueCode;
        this.deviceType = deviceType;
        this.deviceSystemVersion = deviceSystemVersion;
        this.userId = userId == null ? null : userId.longValue();
        this.username = username;
        // UserTypeConverter是繼承自javax.persistence.AttributeConverter的類型轉(zhuǎn)換器
        this.userType = new PosUserEntityConstants.UserTypeConverter().convertToEntityAttribute(userType);
    }
}

test測(cè)試類：

@Test
public void testFindUserAndDeviceInfoByDistributorId() throws Exception {
    System.out.println("-----------------hql query-----------------");
    Page<PosUserDeviceRelationDto> userAndDeviceInfoByDistributorId = posUserDeviceRelationRepository.findUserAndDeviceInfoByDistributorId(1L, PageRequest.of(0, 10)); 
    System.out.println("count=" + userAndDeviceInfoByDistributorId.getTotalElements());
    if(userAndDeviceInfoByDistributorId.getContent() != null) {
        for (PosUserDeviceRelationDto dto : userAndDeviceInfoByDistributorId.getContent()) {
            System.out.println(dto);
        }
    }
 
    System.out.println("-----------------native sql query-----------------");
    Page<Object[]> userAndDeviceInfoByDistributorId2 = posUserDeviceRelationRepository.findUserAndDeviceInfoByDistributorId2(1L, PageRequest.of(0, 10));
    System.out.println("count=" + userAndDeviceInfoByDistributorId2.getTotalElements());
    if(userAndDeviceInfoByDistributorId2.getContent() != null) {
        for (Object[] objects : userAndDeviceInfoByDistributorId2.getContent()) {
            for (Object obj : objects) {
                System.out.print(obj + "(" + (obj == null ? null : obj.getClass().getSimpleName()) + ") ");
            }
            System.out.println();  
        }
 
        // 轉(zhuǎn)換為dto 方法一
        System.out.println("-----轉(zhuǎn)換dto的第一種方法-----");
        for (Object[] objects : userAndDeviceInfoByDistributorId2.getContent()) {
            // 轉(zhuǎn)換成dto的方法一：將objects中的所有參數(shù)強(qiáng)轉(zhuǎn)為對(duì)應(yīng)類型，傳遞到dto的構(gòu)造器中；dto對(duì)象定義好對(duì)應(yīng)的構(gòu)造器
            PosUserDeviceRelationDto dto1 = new PosUserDeviceRelationDto(
                    (BigInteger) objects[0],
                    (BigInteger) objects[1],
                    (String    ) objects[2],
                    (String    ) objects[3],
                    (String    ) objects[4],
                    (String    ) objects[5],
                    (String    ) objects[6],
                    (BigInteger) objects[7],
                    (String    ) objects[8],
                    (Integer   ) objects[9]);
            System.out.println(dto1);
        }
 
        // 轉(zhuǎn)換為dto 方法二
        System.out.println("-----轉(zhuǎn)換dto的第二種方法-----");
        for (Object[] objects : userAndDeviceInfoByDistributorId2.getContent()) {
            // 轉(zhuǎn)換成dto的方法二：反射的方法直接調(diào)用構(gòu)造
            PosUserDeviceRelationDto dto2 = caseDto(objects, new Class[]{BigInteger.class,
                            BigInteger.class,
                            String.class,
                            String.class,
                            String.class,
                            String.class,
                            String.class,
                            BigInteger.class,
                            String.class,
                            Integer.class},
                    PosUserDeviceRelationDto.class);
            System.out.println(dto2);
        }
    }
}
 
/**
 * 網(wǎng)頁(yè)中直接使用objectArray中獲取每一個(gè)class，但是這樣有一個(gè)問(wèn)題，就是如果獲取的objectArray中有一個(gè)空值的話，不能獲取到class，
 * 導(dǎo)致不能獲取到對(duì)象的構(gòu)造器
 * @param objectArray
 * @param objectClassArray
 * @param dtoClass
 * @param <T>
 * @return
 */
private <T> T caseDto(Object[] objectArray, Class[] objectClassArray, Class<T> dtoClass) throws Exception {
    Constructor<T> constructor = dtoClass.getConstructor(objectClassArray);
    return constructor.newInstance(objectArray);
}

以上為個(gè)人經(jīng)驗(yàn)，希望能給大家一個(gè)參考，也希望大家多多支持腳本之家。

您可能感興趣的文章: