关键字: JPA复杂查询,JPA返回自定义实体,JPA返回自定义DTO,JPA联表查询,JPA原生SQL查询,JPA踩坑
新冠疫情之下,闲来无事研究了一下JPA,发现坑还是不少的,特地记录一下,有兴趣的道友可以看看。
在灵活性上JPA比不上MyBatis,比如想联表查询返回一个自定义的实体Dto,结果发现不能直接返回自定义的实体,典型错误如下:
12020-03-31 14:52:20.306 ERROR 82562 --- [io-19880-exec-1] o.a.c.c.C.[.[.[.[dispatcherServlet] : Servlet.service() for servlet [dispatcherServlet] in context with path [/stock-data] threw exception [Request processing failed; nested exception is org.springframework.core.convert.ConverterNotFoundException: No converter found capable of converting from type [org.springframework.data.jpa.repository.query.AbstractJpaQuery$TupleConverter$TupleBackedMap] to type [com.lmt.stock.data.XxxDto]] with root cause 2 3org.springframework.core.convert.ConverterNotFoundException: No converter found capable of converting from type [org.springframework.data.jpa.repository.query.AbstractJpaQuery$TupleConverter$TupleBackedMap] to type [com.lmt.stock.data.XxxDto] 4 at org.springframework.core.convert.support.GenericConversionService.handleConverterNotFound(GenericConversionService.java:321) ~[spring-core-5.2.4.RELEASE.jar:5.2.4.RELEASE] 5 at org.springframework.core.convert.support.GenericConversionService.convert(GenericConversionService.java:194) ~[spring-core-5.2.4.RELEASE.jar:5.2.4.RELEASE] 6 at org.springframework.core.convert.support.GenericConversionService.convert(GenericConversionService.java:174) ~[spring-core-5.2.4.RELEASE.jar:5.2.4.RELEASE] 7 at org.springframework.data.repository.query.ResultProcessor$ProjectingConverter.convert(ResultProcessor.java:297) ~[spring-data-commons-2.2.5.RELEASE.jar:2.2.5.RELEASE] 8 at org.springframework.data.repository.query.ResultProcessor$ChainingConverter.lambda$and$0(ResultProcessor.java:217) ~[spring-data-commons-2.2.5.RELEASE.jar:2.2.5.RELEASE] 9 at org.springframework.data.repository.query.ResultProcessor$ChainingConverter.convert(ResultProcessor.java:228) ~[spring-data-commons-2.2.5.RELEASE.jar:2.2.5.RELEASE] 10 at org.springframework.data.repository.query.ResultProcessor.processResult(ResultProcessor.java:156) ~[spring-data-commons-2.2.5.RELEASE.jar:2.2.5.RELEASE] 11 at org.springframework.data.jpa.repository.query.AbstractJpaQuery.doExecute(AbstractJpaQuery.java:157) ~[spring-data-jpa-2.2.5.RELEASE.jar:2.2.5.RELEASE] 12 at org.springframework.data.jpa.repository.query.AbstractJpaQuery.execute(AbstractJpaQuery.java:142) ~[spring-data-jpa-2.2.5.RELEASE.jar:2.2.5.RELEASE] 13 at org.springframework.data.repository.core.support.RepositoryFactorySupport$QueryExecutorMethodInterceptor.doInvoke(RepositoryFactorySupport.java:618) ~[spring-data-commons-2.2.5.RELEASE.jar:2.2.5.RELEASE] 14 at org.springframework.data.repository.core.support.RepositoryFactorySupport$QueryExecutorMethodInterceptor.invoke(RepositoryFactorySupport.java:605) ~[spring-data-commons-2.2.5.RELEASE.jar:2.2.5.RELEASE] 15 at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186) ~[spring-aop-5.2.4.RELEASE.jar:5.2.4.RELEASE] 16 at org.springframework.data.projection.DefaultMethodInvokingMethodInterceptor.invoke(DefaultMethodInvokingMethodInterceptor.java:80) ~[spring-data-commons-2.2.5.RELEASE.jar:2.2.5.RELEASE] 17 at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186) ~[spring-aop-5.2.4.RELEASE.jar:5.2.4.RELEASE] 18 at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:366) ~[spring-tx-5.2.4.RELEASE.jar:5.2.4.RELEASE] 19 at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:99) ~[spring-tx-5.2.4.RELEASE.jar:5.2.4.RELEASE] 20 at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186) ~[spring-aop-5.2.4.RELEASE.jar:5.2.4.RELEASE] 21 at org.springframework.dao.support.PersistenceExceptionTranslationInterceptor.invoke(PersistenceExceptionTranslationInterceptor.java:139) ~[spring-tx-5.2.4.RELEASE.jar:5.2.4.RELEASE] 22 at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186) ~[spring-aop-5.2.4.RELEASE.jar:5.2.4.RELEASE] 23 at org.springframework.data.jpa.repository.support.CrudMethodMetadataPostProcessor$CrudMethodMetadataPopulatingMethodInterceptor.invoke(CrudMethodMetadataPostProcessor.java:149) ~[spring-data-jpa-2.2.5.RELEASE.jar:2.2.5.RELEASE] 24 at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186) ~[spring-aop-5.2.4.RELEASE.jar:5.2.4.RELEASE] 25 at org.springframework.aop.interceptor.ExposeInvocationInterceptor.invoke(ExposeInvocationInterceptor.java:95) ~[spring-aop-5.2.4.RELEASE.jar:5.2.4.RELEASE] 26 at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186) ~[spring-aop-5.2.4.RELEASE.jar:5.2.4.RELEASE] 27 at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:212) ~[spring-aop-5.2.4.RELEASE.jar:5.2.4.RELEASE] 28 at com.sun.proxy.$Proxy112.findXxxDtoByAccountCode(Unknown Source) ~[na:na] 29 at com.lmt.stock.controller.StockController.getXxxDto(StockController.java:43) ~[classes/:na] 30 at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) ~[na:1.8.0_181] 31 at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) ~[na:1.8.0_181] 32 at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[na:1.8.0_181] 33 at java.lang.reflect.Method.invoke(Method.java:498) ~[na:1.8.0_181] 34 at org.springframework.web.method.support.InvocableHandlerMethod.doInvoke(InvocableHandlerMethod.java:190) ~[spring-web-5.2.4.RELEASE.jar:5.2.4.RELEASE] 35 at org.springframework.web.method.support.InvocableHandlerMethod.invokeForRequest(InvocableHandlerMethod.java:138) ~[spring-web-5.2.4.RELEASE.jar:5.2.4.RELEASE] 36 at org.springframework.web.servlet.mvc.method.annotation.ServletInvocableHandlerMethod.invokeAndHandle(ServletInvocableHandlerMethod.java:106) ~[spring-webmvc-5.2.4.RELEASE.jar:5.2.4.RELEASE] 37 at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.invokeHandlerMethod(RequestMappingHandlerAdapter.java:879) ~[spring-webmvc-5.2.4.RELEASE.jar:5.2.4.RELEASE] 38 at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.handleInternal(RequestMappingHandlerAdapter.java:793) ~[spring-webmvc-5.2.4.RELEASE.jar:5.2.4.RELEASE] 39 at org.springframework.web.servlet.mvc.method.AbstractHandlerMethodAdapter.handle(AbstractHandlerMethodAdapter.java:87) ~[spring-webmvc-5.2.4.RELEASE.jar:5.2.4.RELEASE] 40 at org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:1040) ~[spring-webmvc-5.2.4.RELEASE.jar:5.2.4.RELEASE] 41 at org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:943) ~[spring-webmvc-5.2.4.RELEASE.jar:5.2.4.RELEASE] 42 at org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:1006) ~[spring-webmvc-5.2.4.RELEASE.jar:5.2.4.RELEASE] 43 at org.springframework.web.servlet.FrameworkServlet.doGet(FrameworkServlet.java:898) ~[spring-webmvc-5.2.4.RELEASE.jar:5.2.4.RELEASE] 44 at javax.servlet.http.HttpServlet.service(HttpServlet.java:634) ~[tomcat-embed-core-9.0.31.jar:9.0.31] 45 at org.springframework.web.servlet.FrameworkServlet.service(FrameworkServlet.java:883) ~[spring-webmvc-5.2.4.RELEASE.jar:5.2.4.RELEASE] 46 at javax.servlet.http.HttpServlet.service(HttpServlet.java:741) ~[tomcat-embed-core-9.0.31.jar:9.0.31] 47 at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:231) ~[tomcat-embed-core-9.0.31.jar:9.0.31] 48 at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166) ~[tomcat-embed-core-9.0.31.jar:9.0.31] 49 at org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:53) ~[tomcat-embed-websocket-9.0.31.jar:9.0.31] 50 at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193) ~[tomcat-embed-core-9.0.31.jar:9.0.31] 51 at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166) ~[tomcat-embed-core-9.0.31.jar:9.0.31] 52 at org.springframework.web.filter.RequestContextFilter.doFilterInternal(RequestContextFilter.java:100) ~[spring-web-5.2.4.RELEASE.jar:5.2.4.RELEASE] 53 at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:119) ~[spring-web-5.2.4.RELEASE.jar:5.2.4.RELEASE] 54 at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193) ~[tomcat-embed-core-9.0.31.jar:9.0.31] 55 at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166) ~[tomcat-embed-core-9.0.31.jar:9.0.31] 56 at org.springframework.web.filter.FormContentFilter.doFilterInternal(FormContentFilter.java:93) ~[spring-web-5.2.4.RELEASE.jar:5.2.4.RELEASE] 57 at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:119) ~[spring-web-5.2.4.RELEASE.jar:5.2.4.RELEASE] 58 at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193) ~[tomcat-embed-core-9.0.31.jar:9.0.31] 59 at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166) ~[tomcat-embed-core-9.0.31.jar:9.0.31] 60 at org.springframework.web.filter.CharacterEncodingFilter.doFilterInternal(CharacterEncodingFilter.java:201) ~[spring-web-5.2.4.RELEASE.jar:5.2.4.RELEASE] 61 at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:119) ~[spring-web-5.2.4.RELEASE.jar:5.2.4.RELEASE] 62 at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193) ~[tomcat-embed-core-9.0.31.jar:9.0.31] 63 at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166) ~[tomcat-embed-core-9.0.31.jar:9.0.31] 64 at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:202) ~[tomcat-embed-core-9.0.31.jar:9.0.31] 65 at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:96) [tomcat-embed-core-9.0.31.jar:9.0.31] 66 at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:541) [tomcat-embed-core-9.0.31.jar:9.0.31] 67 at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:139) [tomcat-embed-core-9.0.31.jar:9.0.31] 68 at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:92) [tomcat-embed-core-9.0.31.jar:9.0.31] 69 at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:74) [tomcat-embed-core-9.0.31.jar:9.0.31] 70 at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:343) [tomcat-embed-core-9.0.31.jar:9.0.31] 71 at org.apache.coyote.http11.Http11Processor.service(Http11Processor.java:367) [tomcat-embed-core-9.0.31.jar:9.0.31] 72 at org.apache.coyote.AbstractProcessorLight.process(AbstractProcessorLight.java:65) [tomcat-embed-core-9.0.31.jar:9.0.31] 73 at org.apache.coyote.AbstractProtocol$ConnectionHandler.process(AbstractProtocol.java:868) [tomcat-embed-core-9.0.31.jar:9.0.31] 74 at org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.doRun(NioEndpoint.java:1639) [tomcat-embed-core-9.0.31.jar:9.0.31] 75 at org.apache.tomcat.util.net.SocketProcessorBase.run(SocketProcessorBase.java:49) [tomcat-embed-core-9.0.31.jar:9.0.31] 76 at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149) [na:1.8.0_181] 77 at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624) [na:1.8.0_181] 78 at org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61) [tomcat-embed-core-9.0.31.jar:9.0.31] 79 at java.lang.Thread.run(Thread.java:748) [na:1.8.0_181]
针对这个问题,上网搜寻一番,大概发现如下4个解决方案:
方案1.HQL查询+实体全参数的构造方法
实体定义
1@Getter 2@Setter 3public class XxxDto { 4 5 BigDecimal profitPercent; 6 7 String accountCode; 8 9 public XxxDto() {} 10 11 public XxxDto(String accountCode, BigDecimal profitPercent) { 12 this.accountCode = accountCode; 13 this.profitPercent = profitPercent; 14 } 15 16}
查询接口
1import com.lmt.stock.data.XxxDto; 2import com.lmt.stock.data.entity.StockHisHq; 3import org.springframework.data.jpa.repository.JpaRepository; 4import org.springframework.data.jpa.repository.Query; 5 6import java.util.List; 7 8/** 9 * @author bazhandao 10 * @date 2020/3/23 11:08 11 * @since JDK1.8 12 */ 13public interface StockHisHqRepository extends JpaRepository<StockHisHq, Long> { 14 15 // 注意这里只能用HQL查询,不能加nativeQuery = true 16 @Query(value = "select new com.lmt.stock.data.XxxDto(a.accountCode as accountCode, b.profitPercent as profitPercent) from TradeAccount a, TradeOrder b where a.accountCode = b.accountCode and a.accountCode = ?1") 17 List<XxxDto> findXxxDtoByAccountCode(String accountCode); 18 19}
方案2.实体定义成接口的形式
实体定义
1// 只需要有get方法即可,注意命名要规范 2public interface YyyDto { 3 4 String getAccountCode(); 5 6 BigDecimal getProfitPercent(); 7 8}
查询接口
1import com.lmt.stock.data.YyyDto; 2import com.lmt.stock.data.entity.StockHisHq; 3import org.springframework.data.jpa.repository.JpaRepository; 4import org.springframework.data.jpa.repository.Query; 5 6import java.util.List; 7 8/** 9 * @author bazhandao 10 * @date 2020/3/23 11:08 11 * @since JDK1.8 12 */ 13public interface StockHisHqRepository extends JpaRepository<StockHisHq, Long> { 14 15 // 这里可以用HQL查询,也可以用原生SQL查询,YyyDto是一个接口,这里返回的是JPA生成的YyyDto的代理类 16 // 查寻出的字段命名要规范,否则与接口中的get方法对应不上 17 // @Query(value = "select a.account_code as accountCode, b.profit_percent as profitPercent from trade_account a, trade_order b where a.account_code = b.account_code and a.account_code = ?1", nativeQuery = true) 18 @Query(value = "select a.accountCode as accountCode, b.profitPercent as profitPercent from TradeAccount a, TradeOrder b where a.accountCode = b.accountCode and a.accountCode = ?1") 19 List<YyyDto> findXxxDtoByAccountCode(String accountCode); 20 21}
ps: YyyDto是一个接口,这里返回的是JPA生成的YyyDto的代理类,是可以直接json序列化成json字符串的
方案3.查寻出Map结果,利用工具类转换成需要的实体
实体定义
1@Getter 2@Setter 3public class XxxDto { 4 5 BigDecimal profitPercent; 6 7 String accountCode; 8 9}
查询接口
1import com.lmt.stock.data.entity.StockHisHq; 2import org.springframework.data.jpa.repository.JpaRepository; 3import org.springframework.data.jpa.repository.Query; 4 5import java.util.List; 6 7/** 8 * @author bazhandao 9 * @date 2020/3/23 11:08 10 * @since JDK1.8 11 */ 12public interface StockHisHqRepository extends JpaRepository<StockHisHq, Long> { 13 14 // 这里可以用HQL查询,也可以用原生SQL查询 15 // 查寻出的字段命名要规范 16 // @Query(value = "select a.account_code as accountCode, b.profit_percent as profitPercent from trade_account a, trade_order b where a.account_code = b.account_code and a.account_code = ?1", nativeQuery = true) 17 @Query(value = "select a.accountCode as accountCode, b.profitPercent as profitPercent from TradeAccount a, TradeOrder b where a.accountCode = b.accountCode and a.accountCode = ?1") 18 List<Map<String, Object>> findXxxDtoByAccountCode(String accountCode); 19 20}
工具类
1public class BeanUtils extends org.springframework.beans.BeanUtils{ 2 3 /** 4 * List<Map<String, Object>> 转换为List<T> 5 * @param mapList 6 * @param clazz 7 * @param <T> 8 * @return 9 * @throws IllegalAccessException 10 * @throws InstantiationException 11 */ 12 public static <T> List<T> toList(List<Map<String, Object>> mapList, Class<T> clazz) throws IllegalAccessException, InstantiationException { 13 if (mapList == null || clazz == null) { 14 return null; 15 } 16 List<T> list = new ArrayList<>(mapList.size()); 17 for (Map<String, Object> map : mapList) { 18 T t = clazz.newInstance(); 19 copyProperties(map, t); 20 list.add(t); 21 } 22 return list; 23 } 24 25 /** 26 * 从map中复制属性对对象 27 * @author bazhandao 28 * @date 2018-11-10 29 * @param map 30 * @param target 31 */ 32 public static void copyProperties(Map<String,Object> map, Object target) { 33 if(map == null || target == null || map.isEmpty()){ 34 return; 35 } 36 Class<?> actualEditable = target.getClass(); 37 PropertyDescriptor[] targetPds = getPropertyDescriptors(actualEditable); 38 for (PropertyDescriptor targetPd : targetPds) { 39 if(targetPd.getWriteMethod() == null) { 40 continue; 41 } 42 try { 43 String key = targetPd.getName(); 44 Object value = map.get(key); 45 // 这里判断以下value是否为空 46 setValue(target, targetPd, value); 47 } catch (Exception ex) { 48 throw new FatalBeanException("Could not copy properties from source to target", ex); 49 } 50 } 51 } 52 53 /** 54 * 设置值到目标bean 55 * @param target 56 * @param targetPd 57 * @param value 58 * @throws IllegalAccessException 59 * @throws InvocationTargetException 60 */ 61 private static void setValue(Object target, PropertyDescriptor targetPd, Object value) throws IllegalAccessException, InvocationTargetException { 62 // 这里判断以下value是否为空 63 if (value != null) { 64 Method writeMethod = targetPd.getWriteMethod(); 65 if (!Modifier.isPublic(writeMethod.getDeclaringClass().getModifiers())) { 66 writeMethod.setAccessible(true); 67 } 68 writeMethod.invoke(target, value); 69 } 70 } 71} 72
ps: 注意SQL查寻出的字段名要与实体中的字段名一致,也可扩展工具类实现查寻出数据库原始字段名再转换的驼峰命名对应到实体中.
方案4.@SqlResultSetMapping注解实现
实体定义
1import javax.persistence.EntityResult; 2import javax.persistence.FieldResult; 3import javax.persistence.SqlResultSetMapping; 4 5@Getter 6@Setter 7@SqlResultSetMapping( 8 name = "xxxDto", 9 entities = { 10 @EntityResult( 11 entityClass = XxxDto.class, // 当前类名 12 fields = { 13 @FieldResult(name = "accountCode", column = "account_code"), 14 @FieldResult(name = "profitPercent", column = "profit_percent") 15 } 16 ) 17 } 18) 19public class XxxDto { 20 21 BigDecimal profitPercent; 22 23 String accountCode; 24 25}
接口定义
1import com.lmt.stock.data.XxxDto; 2import com.lmt.stock.data.entity.StockHisHq; 3import org.springframework.data.jpa.repository.JpaRepository; 4import org.springframework.data.jpa.repository.Query; 5 6import java.util.List; 7 8/** 9 * @author bazhandao 10 * @date 2020/3/23 11:08 11 * @since JDK1.8 12 */ 13public interface StockHisHqRepository extends JpaRepository<StockHisHq, Long> { 14 15 // 这里可以用HQL查询,也可以用原生SQL查询 16 // 查寻出的字段命名要规范 17 // @Query(value = "select a.account_code as accountCode, b.profit_percent as profitPercent from trade_account a, trade_order b where a.account_code = b.account_code and a.account_code = ?1", nativeQuery = true) 18 @Query(value = "select a.accountCode as accountCode, b.profitPercent as profitPercent from TradeAccount a, TradeOrder b where a.accountCode = b.accountCode and a.accountCode = ?1") 19 List<XxxDto> findXxxDtoByAccountCode(String accountCode); 20 21}
方案5. 独创方案,自定义注解+注入convert实现
注意看错误日志ConverterNotFoundException: No converter found capable of converting from type
既然是converter没有找到那就注入对应的converter就完事儿了,
查出的原始类型为AbstractJpaQueryT u p l e C o n v e r t e r TupleConverter_T__u__p__l__e__C__o__n__v__e__r__t__e__r_TupleBackedMap,是一个Map,也就是Map转实体呗,对应的实体注入一个Map转dto的converter就行了
继续看错误日志, at org.springframework.data.repository.query.ResultProcessor.processResult(ResultProcessor.java:156) ~[spring-data-commons-2.2.5.RELEASE.jar:2.2.5.RELEASE]
在这个ResultProcessor有所发现,用到的ConversionService是通过DefaultConversionService.getSharedInstance()获取的,如下图:
那么把我们需要注入的converter注入到DefaultConversionService.getSharedInstance()应该就能实现自动转换为自定义Dto了
实现方案:
1.添加自定义注解@JpaDto
JpaDto.java
1package com.lmt.zeus.jpa.annotation; 2 3/** 4 * @description 自定义注解表示,加在类上表示是一个JpaDto类 5 * 6 * @author bazhandao 7 * @date 2020/3/26 16:39 8 * @since JDK1.8 9 */ 10 11import org.springframework.stereotype.Component; 12 13import java.lang.annotation.*; 14 15@Documented 16@Component 17@Target(value = {ElementType.TYPE}) 18@Retention(RetentionPolicy.RUNTIME) 19public @interface JpaDto { 20 21}
2.添加ZeusJpaConfiguration类实现注入加有@JpaDto注解的类对应的converter功能
ZeusJpaConfiguration.java
1package com.lmt.zeus.jpa.config; 2 3import com.lmt.zeus.jpa.annotation.JpaDto; 4import lombok.extern.slf4j.Slf4j; 5import org.springframework.beans.BeanUtils; 6import org.springframework.beans.FatalBeanException; 7import org.springframework.beans.factory.annotation.Autowired; 8import org.springframework.context.ApplicationContext; 9import org.springframework.context.annotation.Configuration; 10import org.springframework.core.convert.support.DefaultConversionService; 11import org.springframework.core.convert.support.GenericConversionService; 12 13import javax.annotation.PostConstruct; 14import java.beans.PropertyDescriptor; 15import java.lang.reflect.Method; 16import java.lang.reflect.Modifier; 17import java.util.Map; 18 19/** 20 * @description 21 * 22 * @author bazhandao 23 * @date 2020/3/26 17:51 24 * @since JDK1.8 25 */ 26@Slf4j 27@Configuration 28public class ZeusJpaConfiguration { 29 30 @Autowired 31 private ApplicationContext applicationContext; 32 33 /** 34 * 初始化注入@JpaDto对应的Converter 35 */ 36 @PostConstruct 37 public void init() { 38 Map<String, Object> map = applicationContext.getBeansWithAnnotation(JpaDto.class); 39 for (Object o : map.values()) { 40 Class c = o.getClass(); 41 log.info("Jpa添加Converter,class={}", c.getName()); 42 GenericConversionService genericConversionService = ((GenericConversionService) DefaultConversionService.getSharedInstance()); 43 genericConversionService.addConverter(Map.class, c, m -> { 44 try { 45 Object obj = c.newInstance(); 46 // 这里可以扩展,注入的converter,实现sql查寻出的结果为数据库中带下划线的字段,通过程序转为驼峰命名再设置到实体中 47 // 也可以做类型转换判断,这里未做类型判断,直接copy到dto中,类型不匹配的时候可能会出错 48 return copyMapToObj(m, obj); 49 } catch (Exception e) { 50 throw new FatalBeanException("Jpa结果转换出错,class=" + c.getName(), e); 51 } 52 }); 53 } 54 } 55 56 /** 57 * 将map中的值copy到bean中对应的字段上 58 * @author bazhandao 59 * @date 2020-03-26 60 * @param map 61 * @param target 62 * @return 63 */ 64 private Object copyMapToObj(Map<String, Object> map, Object target) { 65 if(map == null || target == null || map.isEmpty()){ 66 return target; 67 } 68 Class<?> actualEditable = target.getClass(); 69 PropertyDescriptor[] targetPds = BeanUtils.getPropertyDescriptors(actualEditable); 70 for (PropertyDescriptor targetPd : targetPds) { 71 if(targetPd.getWriteMethod() == null) { 72 continue; 73 } 74 try { 75 String key = targetPd.getName(); 76 Object value = map.get(key); 77 if (value == null) { 78 continue; 79 } 80 Method writeMethod = targetPd.getWriteMethod(); 81 if (!Modifier.isPublic(writeMethod.getDeclaringClass().getModifiers())) { 82 writeMethod.setAccessible(true); 83 } 84 writeMethod.invoke(target, value); 85 } catch (Exception ex) { 86 throw new FatalBeanException("Could not copy properties from source to target", ex); 87 } 88 } 89 return target; 90 } 91} 92
3.实体类
1package com.lmt.stock.data; 2 3import com.lmt.zeus.jpa.annotation.JpaDto; 4import lombok.Getter; 5import lombok.Setter; 6 7import java.math.BigDecimal; 8 9/** 10 * @description 小试牛刀 11 * 12 * @author bazhandao 13 * @date 2020/3/26 13:53 14 * @since JDK1.8 15 */ 16@JpaDto // 注意实体类要加上@JpaDto注解,将该类注入到容器 17@Getter 18@Setter 19public class XxxDto { 20 21 BigDecimal profitPercent; 22 23 String accountCode; 24 25} 26
4.接口实现
1import com.lmt.stock.data.XxxDto; 2import com.lmt.stock.data.entity.StockHisHq; 3import org.springframework.data.jpa.repository.JpaRepository; 4import org.springframework.data.jpa.repository.Query; 5 6import java.util.List; 7 8/** 9 * @author bazhandao 10 * @date 2020/3/23 11:08 11 * @since JDK1.8 12 */ 13public interface StockHisHqRepository extends JpaRepository<StockHisHq, Long> { 14 15 // 这里可以用HQL查询,也可以用原生SQL查询 16 // 查寻出的字段命名要规范 17 // @Query(value = "select a.account_code as accountCode, b.profit_percent as profitPercent from trade_account a, trade_order b where a.account_code = b.account_code and a.account_code = ?1", nativeQuery = true) 18 @Query(value = "select a.accountCode as accountCode, b.profitPercent as profitPercent from TradeAccount a, TradeOrder b where a.accountCode = b.accountCode and a.accountCode = ?1") 19 List<XxxDto> findXxxDtoByAccountCode(String accountCode); 20 21} 22
ps:该方案可以使用原生SQL也可以使用HQL,只要查寻出的结果字段名跟实体中的字段名保持一致即可,还可以对注入的converter做扩展,支持更多功能
这里使用了自定义注解将dto注入到容器,通过Spring Boot的Configuration功能实现注入该dto对应的converter功能
也可以不用自定义注解,通过自定义类扫描器或者通过配置加载到dto对应的class注入对应的converter,总之只要开拓思维玩法是多样的
总结
方案1、2、4是Spring Data Jpa中原生支持的方案;
方案1 要求实体有全参的构造方法,写HQL时需要把参数一一对应,当字段比较多时写起来比较累,不建议使用
方案2 通过定义一个接口,里边添加上对应字段的get方法即可查寻出对应的结果(JPA生成了该接口的代理类,支持直接序列化为json串),相对简单,建议采用
方案4 通过@SqlResultSetMapping注解实现,需要在dto类中将结果一一对应绑定,字段多时写起来比较繁锁,不建议使用
方案3 通过查寻出Map,再利用工具类转换为对应的dto实体,写业务过程会多一步操作,对业务代码有入侵,相对比较繁锁,是一个比较Low逼的方案,不建议采用
方案5 通过注解+自动注入converter实现自动转换查寻出的结果即为dto实体,对业务开发无侵入,简化开发过程,建议采用
综上所述,如果对查寻出的结果不做其他复杂操作,则直接使用JPA原生支持的方法2,定义接口dto查寻自定义结果,
如果不习惯查出的结果是一个接口类,可以采用方案5,框架低层注入converter实现查寻自定义结果。
针对JPA实现扩展代码已上传到github,欢迎访问:
https://github.com/sadness-hacker/lmt-zeus/tree/master/lmt-zeus-jpa