JPA如何将查询结果转换为DTO对象

JPA如何将查询结果转换为DTO对象

目录前言例子mysql数据库表联合查询的需求sql语句如何在JPA中映射为DTO对象例子涉及的部分源代码

前言

JPA支持使用@Query自定义查询，查询的结果需要字节用DTO对象接收，如果使用HQL的查询语句，可以将直接将DTO对象的构造方法传入hql中，直接转为DTO对象；而如果使用native sql查询的方式，只能将返回结果用Object[]对象接收，然后DTO设置对象的构造来接收Object[]里面的参数完成DTO对象的转换。

例子

mysql数据库表

用户表

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;

设备表

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;

用户和设备关联表

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;

可以看到用户和设备关联表中有用户id和设备id

联合查询的需求

想列出pos_user_device_relation表中所有pos_user的distributor_id=1的所有用户和设备，要求返回的信息包括用户的username、type信息和设备的imei、mac等信息。

sql语句

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

查询可以正常得到结果，结果行是这样的：

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

如何在JPA中映射为DTO对象

DTO对象字段定义如下：

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;

对象中的PosUserEntityConstants.UserType是一个自定义转换类型，通过继承AttributeConverter将Integer转换为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)",

mlGEFHVk 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 findUserAndDeviceInfoByDistributorId(Long distributorId, Pageable pageable);

可以看到HQL的方法将PosUserDeviceRelationDto的构造器直接传入到HQL语句中，省去了我们自行转换的麻烦。那么PosUserDeviceRelationDto中也要重写一个相应的构造器：

由于项目中使用了lombok，所有最终dto的代码只是在类上面加上了一些注解，@AllArgsConstructor的注解会自动生成一个全参数的构造器，构造器的顺序和字段定义顺序一致，类代码如下：

@Getter

@Setter

@NoArgsConstructor

@AllArgsConstructor

@ToString

public class PosUserDeviceRelationDto implements Serializable {

/**

* 版本号

*/

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的方式查询并转换为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[]来接收结果集，而不能直接将返回参数定义为PosUserDeviceRelationDto对象，否则会报no converter的异常。

那如何将Object[]的结果集转换为PosUserDeviceRelationDto对象呢？

首先先看一下Object[]每个对象的类型：BigInteger BigInteger String String String String String BigInteger String Integer

这是可以发现虽然mysql数据库定义的是bigint(20)类型，但是结果集是BigInteger，不能直接用Long接收，所以专门定义一个dto的构造器如下：

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的类型转换器

this.userType = new PosUserEntityConstants.UserTypeConverter().convertToEntityAttribute(userType);

}

然后直接调用构造即可：

Page

for (Object[] objects : userAndDeviceInfoByDistributorId2.getContent()) {

// 转换成dto的方法一：将objects中的所有参数强转为对应类型，传递到dto的构造器中；dto对象定义好对应的构造器

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

网上还能搜到另外一种解决方法，就是通过反射的方法简化dto的转化步骤(https://jb51-/article/238470.htm)，但是这个存在bug，如果返回的objects数组中有一个值为null，那么getClass()方法获取类的类型就会报错，所以改为将每个参数的类型直接传入进去，可以这样使用反射其实省不了多少工夫了：

Page

for (Object[] objects : userAndDeviceInfoByDistributorId2.getContent()) {

// 转换成dto的方法二：反射的方法直接调用构造

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

}

/**

* 网页中直接使用objectArray中获取每一个class，但是这样有一个问题，就是如果获取的objectArray中有一个空值的话，不能获取到class，

* 导致不能获取到对象的构造器

* @param objectArray

* @param objectClassArray

* @param dtoClass

* @param

* @return

*/

private T caseDto(Object[] objectArray, Class[] objectClassArray, Class dtoClass) throws Exception {

Constructor 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 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

DTO类

@Getter

@Setter

@NoArgsConstructor

@AllArgsConstructor

@ToString

public class PosUserDeviceRelationDto implements Serializable {

/**

* 版本号

*/

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的类型转换器

this.userType = new PosUserEntityConstants.UserTypeConverter().convertToEntityAttribute(userType);

}

}

test测试类：

@Test

public void testFindUserAndDeviceInfoByDistributorId() throws Exception {

System.out.println("-----------------hql query-----------------");

Page 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

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

}

// 转换为dto 方法一

System.out.println("-----转换dto的第一种方法-----");

for (Object[] objects : userAndDeviceInfoByDistributorId2.getContent()) {

// 转换成dto的方法一：将objects中的所有参数强转为对应类型，传递到dto的构造器中；dto对象定义好对应的构造器

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

}

// 转换为dto 方法二

System.out.println("-----转换dto的第二种方法-----");

for (Object[] objects : userAndDeviceInfoByDistributorId2.getContent()) {

// 转换成dto的方法二：反射的方法直接调用构造

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

}

}

}

/**

* 网页中直接使用objectArray中获取每一个class，但是这样有一个问题，就是如果获取的objectArray中有一个空值的话，不能获取到class，

* 导致不能获取到对象的构造器

* @param objectArray

* @param objectClassArray

* @param dtoClass

* @param

* @return

*/

private T caseDto(Object[] objectArray, Class[] objectClassArray, Class dtoClass) throws Exception {

Constructor constructor = dtoClass.getConstructor(objectClassArray);

return constructor.newInstance(objectArray);

}

版权声明：本文内容由网络用户投稿，版权归原作者所有，本站不拥有其著作权，亦不承担相应法律责任。如果您发现本站中有涉嫌抄袭或描述失实的内容，请联系我们jiasou666@gmail.com 处理，核实后本网站将在24小时内删除侵权内容。