目录

• 前言
• 例子
• 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语句

SELECTpdr.id,pdr.device_id,pd.imei,pd.mac,pd.unique_code,pd.type,pd.system_version,pdr.user_id,pu.user_name,pu.user_typeFROMpos_user_device_relation pdr, pos_user pu, pos_device pdWHERE 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

【JPA如何将查询结果转换为DTO对象】查询可以正常得到结果，结果行是这样的：

+----+-----------+---------------------+-------------------+--------------------------+----------+----------------+---------+---------------+-----------+
| 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 = "https://www.it610.com/article/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);

可以看到HQL的方法将PosUserDeviceRelationDto的构造器直接传入到HQL语句中，省去了我们自行转换的麻烦。那么PosUserDeviceRelationDto中也要重写一个相应的构造器：
由于项目中使用了lombok，所有最终dto的代码只是在类上面加上了一些注解，@AllArgsConstructor的注解会自动生成一个全参数的构造器，构造器的顺序和字段定义顺序一致，类代码如下：

@Getter@Setter@NoArgsConstructor@AllArgsConstructor@ToStringpublic 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 = "https://www.it610.com/article/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 findUserAndDeviceInfoByDistributorId2(Long distributorId, Pageable pageable);

可以看到这样只能用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 userAndDeviceInfoByDistributorId2 = posUserDeviceRelationRepository.findUserAndDeviceInfoByDistributorId2(1L, PageRequest.of(0, 10)); 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://www.jb51.net/article/238470.htm)，但是这个存在bug，如果返回的objects数组中有一个值为null，那么getClass()方法获取类的类型就会报错，所以改为将每个参数的类型直接传入进去，可以这样使用反射其实省不了多少工夫了：

Page userAndDeviceInfoByDistributorId2 = posUserDeviceRelationRepository.findUserAndDeviceInfoByDistributorId2(1L, PageRequest.of(0, 10)); 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 = "https://www.it610.com/article/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 = "https://www.it610.com/article/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 findUserAndDeviceInfoByDistributorId2(Long distributorId, Pageable pageable);

DTO类

@Getter@Setter@NoArgsConstructor@AllArgsConstructor@ToStringpublic 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测试类：

@Testpublic 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 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(); } // 转换为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); }

以上为个人经验，希望能给大家一个参考，也希望大家多多支持脚本之家。

推荐阅读

• 

  魅蓝6t怎么查看耗电应用_魅蓝6t查看耗电应用方法教程
• 

  讲一下九大行星有哪些
• 

  传说中的根治腹水的秘方偏方可不可信？
• 

  踏青的唯美句子简短精辟 踏青的唯美句子简短
• 

  神鹰帝国未删减版烧书阁 神鹰帝国翠微居
• 

  转氨酶偏高|转氨酶偏高一定是肝脏有问题吗？为你全面总结各种可能原因
• 

  m710q，魅蓝m710q手机多少钱
• 

  粗虚线代表什么意思，那图纸上虚线代表什么意思
• 

  钣金cae分析,CAe分析
• 

  床单多长时间换洗一次 洗床单哪些事项是要注意的
• 

  突发！上海突然宣布重磅政策 刚刚上海突然宣布大消息
• 

  东风橘根的功效与作用
• 

  花牛苹果变粉后苦味
• 

  怎样查询网上小额贷款 ?如何查询网上小额贷款 ?
• 

  只打一针的新冠疫苗多久产生抗体
• 

  童诗——树画圈记年
• 

  怎么防止盗号 帐号被盗怎么盗回来
• 

  天气太热不想做饭，试试这5道面食，简单好吃又开胃，省力吃得好
• 

  面对不要脸的老赖,应该怎么办？
• 

  青菜胡萝卜面条煮吃的营养

• 按排序顺序打印字符串数组，而不将一个字符串复制到另一个字符串中
• 新手机如何恢复微信里面的聊天记录
• 支付宝如何取消自动续费
• 将Quill添加到项目构建管道中 — Quill富文本编辑器快速入门中文文档
• 如何自定义或定制Quill（ — Quill富文本编辑器快速入门中文文档）
• 极客日报|苹果 iOS 16 透露 VR 头显信息；Win11 将内置网络钓鱼保护功能；谷歌将 AI 写作视为自动生成内容|极客头条
• 推荐（如何预防和避免死锁（））
• 汽车|日产公布固态电池研发近况，5年将投入1128亿元
• 页面设计（CSS如何实现复选框（checkbox）（））
• 如何在一个ng-click指令中添加许多功能（）