JPA支持使用@Query自定义查询，查询的结果需要字节用DTO对象接收，如果使用HQL的查询语句，可以将直接将DTO对象的构造方法传入hql中，直接转为DTO对象；而如果使用native sql查询的方式，只能将返回结果用Object[]对象接收，然后DTO设置对象的构造来接收Object[]里面的参数完成DTO对象的转换。
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等信息。
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查询可以正常得到结果，结果行是这样的：
+----+-----------+---------------------+-------------------+--------------------------+----------+----------------+---------+---------------+-----------+| id | device_id | imei | mac | unique_code | type | system_version | user_id | user_name | user_type |+----+-----------+---------------------+-------------------+--------------------------+----------+----------------+---------+---------------+-----------+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的枚举。
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的构造器直接传入到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;}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[]来接收结果集，而不能直接将返回参数定义为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<Object[]> 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://blog.csdn.net/qq_36144258/article/details/80296512)，但是csdn上这个存在bug，如果返回的objects数组中有一个值为null，那么getClass()方法获取类的类型就会报错，所以改为将每个参数的类型直接传入进去，可以这样使用反射其实省不了多少工夫了：
Page<Object[]> 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);}/** * https://blog.csdn.net/qq_36144258/article/details/80296512 * 网页中直接使用objectArray中获取每一个class，但是这样有一个问题，就是如果获取的objectArray中有一个空值的话，不能获取到class， * 导致不能获取到对象的构造器 * @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@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<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(); } // 转换为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); } }}/** * https://blog.csdn.net/qq_36144258/article/details/80296512 * 网页中直接使用objectArray中获取每一个class，但是这样有一个问题，就是如果获取的objectArray中有一个空值的话，不能获取到class， * 导致不能获取到对象的构造器 * @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);}联系客服
