Spring JPA :: No converter found capable of converting from type

Question

I am using spring with JPA and trying to execute the query using @query with SQL query and trying to map the result to an object. I have different entity classes and mapping to another DTO as I do not want all the columns. Getting below error

No converter found capable of converting from type [org.springframework.data.jpa.repository.query.AbstractJpaQuery$TupleConverter$TupleBackedMap] to type [webapi.dto.StatsDTO]",

Below is my Repository interface:

public interface UserRepository extends CrudRepository<UserEntity, Long> {
    @Query(value = "select count(type_id) userCount, type_id, modified_at from "
                + "user_campaign_objective where camp_id = ?1 group by objective_type_id,modified_at", nativeQuery = true)
    
        List<StatsDTO> getStatsDTO(Long camp_id);
}

Below is StatsDTO:

@Data
@NoArgsConstructor
@AllArgsConstructor
public class StatsDTO {
    private Integer userCount;
    private Byte typeId;
    private Instant modifiedAt;

}

how to fix the above issue.
if the issue is not fixable then can we do it without @query using spring data JPA? How?

Edit:: Using JPQL ::

@Query("select new webapi.campaign.dto.StatsDTO(count(u.objective_type_id),u.objective_type_id,u.modified_at) "
        + "from user_campaign_objective u where u.campaign_id = ?1 group by u.objective_type_id,u.modified_at")

getting below error

    Caused by: org.hibernate.hql.internal.ast.QuerySyntaxException: user_campaign_objective is not mapped [select new webapi.campaign.dto.StatsDTO(count(u.objective_type_id),u.objective_type_id,u.modified_at) from user_campaign_objective u where u.campaign_id = ?1 group by u.objective_type_id,u.modified_at]
    at org.hibernate.hql.internal.ast.QuerySyntaxException.generateQueryException(QuerySyntaxException.java:79) ~[hibernate-core-5.2.17.Final.jar:5.2.17.Final]     
at org.hibernate.QueryException.wrapWithQueryString(QueryException.java:103) ~[hibernate-core-5.2.17.Final.jar:5.2.17.Final]    
at org.hibernate.hql.internal.ast.QueryTranslatorImpl.doCompile(QueryTranslatorImpl.java:217) ~[hibernate-core-5.2.17.Final.jar:5.2.17.Final]   at org.hibernate.hql.internal.ast.QueryTranslatorImpl.compile(QueryTranslatorImpl.java:141) ~[hibernate-core-5.2.17.Final.jar:5.2.17.Final]     at org.hibernate.engine.query.spi.HQLQueryPlan.<init>(HQLQueryPlan.java:115) ~[hibernate-core-5.2.17.Final.jar:5.2.17.Final]    
at org.hibernate.engine.query.spi.HQLQueryPlan.<init>(HQLQueryPlan.java:77) ~[hibernate-core-5.2.17.Final.jar:5.2.17.Final]     at org.hibernate.engine.query.spi.QueryPlanCache.getHQLQueryPlan(QueryPlanCache.java:153) ~[hibernate-core-5.2.17.Final.jar:5.2.17.Final]   
at org.hibernate.internal.AbstractSharedSessionContract.getQueryPlan(AbstractSharedSessionContract.java:553) ~[hibernate-core-5.2.17.Final.jar:5.2.17.Final]    
at org.hibernate.internal.AbstractSharedSessionContract.createQuery(AbstractSharedSessionContract.java:662) ~[hibernate-core-5.2.17.Final.jar:5.2.17.Final]     ... 79 common frames omitted Caused by: org.hibernate.hql.internal.ast.QuerySyntaxException: user_campaign_objective is not mapped  
at org.hibernate.hql.internal.ast.util.SessionFactoryHelper.requireClassPersister(SessionFactoryHelper.java:169) ~[hibernate-core-5.2.17.Final.jar:5.2.17.Final] 
    at org.hibernate.hql.internal.ast.tree.FromElementFactory.addFromElement(FromElementFactory.java:91) ~[hibernate-core-5.2.17.Final.jar:5.2.17.Final]    at org.hibernate.hql.internal.ast.tree.FromClause.addFromElement(FromClause.java:79) ~[hibernate-core-5.2.17.Final.jar:5.2.17.Final]    
at org.hibernate.hql.internal.ast.HqlSqlWalker.createFromElement(HqlSqlWalker.java:326) ~[hibernate-core-5.2.17.Final.jar:5.2.17.Final] 
    at org.hibernate.hql.internal.antlr.HqlSqlBaseWalker.fromElement(HqlSqlBaseWalker.java:3706) ~[hibernate-core-5.2.17.Final.jar:5.2.17.Final]    
at org.hibernate.hql.internal.antlr.HqlSqlBaseWalker.fromElementList(HqlSqlBaseWalker.java:3595) ~[hibernate-core-5.2.17.Final.jar:5.2.17.Final]    
at org.hibernate.hql.internal.antlr.HqlSqlBaseWalker.fromClause(HqlSqlBaseWalker.java:720) ~[hibernate-core-5.2.17.Final.jar:5.2.17.Final]  
at org.hibernate.hql.internal.antlr.HqlSqlBaseWalker.query(HqlSqlBaseWalker.java:576) ~[hibernate-core-5.2.17.Final.jar:5.2.17.Final]   
at org.hibernate.hql.internal.antlr.HqlSqlBaseWalker.selectStatement(HqlSqlBaseWalker.java:313) ~[hibernate-core-5.2.17.Final.jar:5.2.17.Final]     
at org.hibernate.hql.internal.antlr.HqlSqlBaseWalker.statement(HqlSqlBaseWalker.java:261) ~[hibernate-core-5.2.17.Final.jar:5.2.17.Final]   
at org.hibernate.hql.internal.ast.QueryTranslatorImpl.analyze(QueryTranslatorImpl.java:266) ~[hibernate-core-5.2.17.Final.jar:5.2.17.Final] 
    at org.hibernate.hql.internal.ast.QueryTranslatorImpl.doCompile(QueryTranslatorImpl.java:189) ~[hibernate-core-5.2.17.Final.jar:5.2.17.Final]   ... 85 common frames omitted

If using nativequery using sql get below error

@Query(value = "select count(objective_type_id), objective_type_id, modified_at from "
        + "user_campaign_objective where campaign_id = ?1 group by objective_type_id,modified_at", nativeQuery = true)

Stacktrace

org.springframework.core.convert.ConverterNotFoundException: No converter found capable of converting from type [org.springframework.data.jpa.repository.query.AbstractJpaQuery$TupleConverter$TupleBackedMap] to type [webapi.campaign.dto.StatsDTO]   
at org.springframework.core.convert.support.GenericConversionService.handleConverterNotFound(GenericConversionService.java:321) ~[spring-core-5.0.7.RELEASE.jar:5.0.7.RELEASE]  
at org.springframework.core.convert.support.GenericConversionService.convert(GenericConversionService.java:194) ~[spring-core-5.0.7.RELEASE.jar:5.0.7.RELEASE]  
at org.springframework.core.convert.support.GenericConversionService.convert(GenericConversionService.java:174) ~[spring-core-5.0.7.RELEASE.jar:5.0.7.RELEASE]  
at org.springframework.data.repository.query.ResultProcessor$ProjectingConverter.convert(ResultProcessor.java:293) ~[spring-data-commons-2.0.8.RELEASE.jar:2.0.8.RELEASE]   
at org.springframework.data.repository.query.ResultProcessor$ChainingConverter.lambda$and$0(ResultProcessor.java:213) ~[spring-data-commons-2.0.8.RELEASE.jar:2.0.8.RELEASE]    
at org.springframework.data.repository.query.ResultProcessor$ChainingConverter.convert(ResultProcessor.java:224) ~[spring-data-commons-2.0.8.RELEASE.jar:2.0.8.RELEASE]     
at org.springframework.data.repository.query.ResultProcessor.processResult(ResultProcessor.java:152) ~[spring-data-commons-2.0.8.RELEASE.jar:2.0.8.RELEASE]     

at org.springframework.data.jpa.repository.query.AbstractJpaQuery.doExecute(AbstractJpaQuery.java:141) ~[spring-data-jpa-2.0.8.RELEASE.jar:2.0.8.RELEASE]   
at org.springframework.data.jpa.repository.query.AbstractJpaQuery.execute(AbstractJpaQuery.java:125) ~[spring-data-jpa-2.0.8.RELEASE.jar:2.0.8.RELEASE]     at org.springframework.data.repository.core.support.RepositoryFactorySupport$QueryExecutorMethodInterceptor.doInvoke(RepositoryFactorySupport.java:590) ~[spring-data-commons-2.0.8.RELEASE.jar:2.0.8.RELEASE]  
at org.springframework.data.repository.core.support.RepositoryFactorySupport$QueryExecutorMethodInterceptor.invoke(RepositoryFactorySupport.java:578) ~[spring-data-commons-2.0.8.RELEASE.jar:2.0.8.RELEASE]    
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:185) ~[spring-aop-5.0.7.RELEASE.jar:5.0.7.RELEASE]  
at org.springframework.data.projection.DefaultMethodInvokingMethodInterceptor.invoke(DefaultMethodInvokingMethodInterceptor.java:59) ~[spring-data-commons-2.0.8.RELEASE.jar:2.0.8.RELEASE]     
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:185) ~[spring-aop-5.0.7.RELEASE.jar:5.0.7.RELEASE]  
at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:294) ~[spring-tx-5.0.7.RELEASE.jar:5.0.7.RELEASE]     
at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:98) ~[spring-tx-5.0.7.RELEASE.jar:5.0.7.RELEASE]   
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:185) ~[spring-aop-5.0.7.RELEASE.jar:5.0.7.RELEASE]  
at org.springframework.dao.support.PersistenceExceptionTranslationInterceptor.invoke(PersistenceExceptionTranslationInterceptor.java:139) ~[spring-tx-5.0.7.RELEASE.jar:5.0.7.RELEASE]  
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:185) ~[spring-aop-5.0.7.RELEASE.jar:5.0.7.RELEASE]  
at org.springframework.data.jpa.repository.support.CrudMethodMetadataPostProcessor$CrudMethodMetadataPopulatingMethodInterceptor.invoke(CrudMethodMetadataPostProcessor.java:135) ~[spring-data-jpa-2.0.8.RELEASE.jar:2.0.8.RELEASE]    
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:185) ~[spring-aop-5.0.7.RELEASE.jar:5.0.7.RELEASE]  
at org.springframework.aop.interceptor.ExposeInvocationInterceptor.invoke(ExposeInvocationInterceptor.java:92) ~[spring-aop-5.0.7.RELEASE.jar:5.0.7.RELEASE]    
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:185) ~[spring-aop-5.0.7.RELEASE.jar:5.0.7.RELEASE]  
at org.springframework.data.repository.core.support.SurroundingTransactionDetectorMethodInterceptor.invoke(SurroundingTransactionDetectorMethodInterceptor.java:61) ~[spring-data-commons-2.0.8.RELEASE.jar:2.0.8.RELEASE]  
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:185) ~[spring-aop-5.0.7.RELEASE.jar:5.0.7.RELEASE]  
at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:212) ~[spring-aop-5.0.7.RELEASE.jar:5.0.7.RELEASE]   
at com.sun.proxy.$Proxy138.findByCampaignID(Unknown Source) ~[na:na]    
at waypedia.webapi.campaign.fullymanaged.CampaignCreatorService.campaignStats(CampaignCreatorService.java:477) ~[main/:na]  
at waypedia.webapi.campaign.fullymanaged.CampaignCreatorController.campaignStats(CampaignCreatorController.java:251) ~[main/:na]    
at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method) ~[na:na]  
at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) ~[na:na]    
at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[na:na]    
at java.base/java.lang.reflect.Method.invoke(Method.java:564) ~[na:na]  at org.springframework.web.method.support.InvocableHandlerMethod.doInvoke(InvocableHandlerMethod.java:209) ~[spring-web-5.0.7.RELEASE.jar:5.0.7.RELEASE]    
at org.springframework.web.method.support.InvocableHandlerMethod.invokeForRequest(InvocableHandlerMethod.java:136) ~[spring-web-5.0.7.RELEASE.jar:5.0.7.RELEASE]    
at org.springframework.web.servlet.mvc.method.annotation.ServletInvocableHandlerMethod.invokeAndHandle(ServletInvocableHandlerMethod.java:102) ~[spring-webmvc-5.0.7.RELEASE.jar:5.0.7.RELEASE]     
at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.invokeHandlerMethod(RequestMappingHandlerAdapter.java:877) ~[spring-webmvc-5.0.7.RELEASE.jar:5.0.7.RELEASE]   
at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.handleInternal(RequestMappingHandlerAdapter.java:783) ~[spring-webmvc-5.0.7.RELEASE.jar:5.0.7.RELEASE]    
at org.springframework.web.servlet.mvc.method.AbstractHandlerMethodAdapter.handle(AbstractHandlerMethodAdapter.java:87) ~[spring-webmvc-5.0.7.RELEASE.jar:5.0.7.RELEASE]    
at org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:991) ~[spring-webmvc-5.0.7.RELEASE.jar:5.0.7.RELEASE]    
at org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:925) ~[spring-webmvc-5.0.7.RELEASE.jar:5.0.7.RELEASE]     
at org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:974) ~[spring-webmvc-5.0.7.RELEASE.jar:5.0.7.RELEASE]  
at org.springframework.web.servlet.FrameworkServlet.doPost(FrameworkServlet.java:877) ~[spring-webmvc-5.0.7.RELEASE.jar:5.0.7.RELEASE]  
at javax.servlet.http.HttpServlet.service(HttpServlet.java:661) ~[tomcat-embed-core-8.5.31.jar:8.5.31]  at org.springframework.web.servlet.FrameworkServlet.service(FrameworkServlet.java:851) ~[spring-webmvc-5.0.7.RELEASE.jar:5.0.7.RELEASE]     
at javax.servlet.http.HttpServlet.service(HttpServlet.java:742) ~[tomcat-embed-core-8.5.31.jar:8.5.31]  
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:231) ~[tomcat-embed-core-8.5.31.jar:8.5.31]     
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166) ~[tomcat-embed-core-8.5.31.jar:8.5.31]     
at org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:52) ~[tomcat-embed-websocket-8.5.31.jar:8.5.31]   
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193) ~[tomcat-embed-core-8.5.31.jar:8.5.31]     
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166) ~[tomcat-embed-core-8.5.31.jar:8.5.31]     
at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:320) ~[spring-security-web-5.0.6.RELEASE.jar:5.0.6.RELEASE]  
at org.springframework.security.web.access.intercept.FilterSecurityInterceptor.invoke(FilterSecurityInterceptor.java:127) ~[spring-security-web-5.0.6.RELEASE.jar:5.0.6.RELEASE]    
at org.springframework.security.web.access.intercept.FilterSecurityInterceptor.doFilter(FilterSecurityInterceptor.java:91) ~[spring-security-web-5.0.6.RELEASE.jar:5.0.6.RELEASE]   
at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:334) ~[spring-security-web-5.0.6.RELEASE.jar:5.0.6.RELEASE]  
at org.springframework.security.web.access.ExceptionTranslationFilter.doFilter(ExceptionTranslationFilter.java:119) ~[spring-security-web-5.0.6.RELEASE.jar:5.0.6.RELEASE]  
at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:334) ~[spring-security-web-5.0.6.RELEASE.jar:5.0.6.RELEASE]  
at org.springframework.security.web.session.SessionManagementFilter.doFilter(SessionManagementFilter.java:137) ~[spring-security-web-5.0.6.RELEASE.jar:5.0.6.RELEASE]   
at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:334) ~[spring-security-web-5.0.6.RELEASE.jar:5.0.6.RELEASE]  
at org.springframework.security.web.authentication.AnonymousAuthenticationFilter.doFilter(AnonymousAuthenticationFilter.java:111) ~[spring-security-web-5.0.6.RELEASE.jar:5.0.6.RELEASE]    
at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:334) ~[spring-security-web-5.0.6.RELEASE.jar:5.0.6.RELEASE]  
at org.springframework.security.web.servletapi.SecurityContextHolderAwareRequestFilter.doFilter(SecurityContextHolderAwareRequestFilter.java:170) ~[spring-security-web-5.0.6.RELEASE.jar:5.0.6.RELEASE]    
at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:334) ~[spring-security-web-5.0.6.RELEASE.jar:5.0.6.RELEASE]  
at org.springframework.security.web.savedrequest.RequestCacheAwareFilter.doFilter(RequestCacheAwareFilter.java:63) ~[spring-security-web-5.0.6.RELEASE.jar:5.0.6.RELEASE]   
at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:334) ~[spring-security-web-5.0.6.RELEASE.jar:5.0.6.RELEASE]  
at org.springframework.security.web.authentication.www.BasicAuthenticationFilter.doFilterInternal(BasicAuthenticationFilter.java:158) ~[spring-security-web-5.0.6.RELEASE.jar:5.0.6.RELEASE]    
at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107) ~[spring-web-5.0.7.RELEASE.jar:5.0.7.RELEASE]    
at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:334) ~[spring-security-web-5.0.6.RELEASE.jar:5.0.6.RELEASE]  
at org.springframework.security.web.authentication.logout.LogoutFilter.doFilter(LogoutFilter.java:116) ~[spring-security-web-5.0.6.RELEASE.jar:5.0.6.RELEASE]   
at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:334) ~[spring-security-web-5.0.6.RELEASE.jar:5.0.6.RELEASE]  
at org.springframework.security.web.header.HeaderWriterFilter.doFilterInternal(HeaderWriterFilter.java:66) ~[spring-security-web-5.0.6.RELEASE.jar:5.0.6.RELEASE]   
at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107) ~[spring-web-5.0.7.RELEASE.jar:5.0.7.RELEASE]    at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:334) ~[spring-security-web-5.0.6.RELEASE.jar:5.0.6.RELEASE]  
at org.springframework.security.web.context.SecurityContextPersistenceFilter.doFilter(SecurityContextPersistenceFilter.java:105) ~[spring-security-web-5.0.6.RELEASE.jar:5.0.6.RELEASE]     
at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:334) ~[spring-security-web-5.0.6.RELEASE.jar:5.0.6.RELEASE]  
at org.springframework.security.web.context.request.async.WebAsyncManagerIntegrationFilter.doFilterInternal(WebAsyncManagerIntegrationFilter.java:56) ~[spring-security-web-5.0.6.RELEASE.jar:5.0.6.RELEASE]    
at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107) ~[spring-web-5.0.7.RELEASE.jar:5.0.7.RELEASE]    
at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:334) ~[spring-security-web-5.0.6.RELEASE.jar:5.0.6.RELEASE]  
at org.springframework.security.web.FilterChainProxy.doFilterInternal(FilterChainProxy.java:215) ~[spring-security-web-5.0.6.RELEASE.jar:5.0.6.RELEASE]     at org.springframework.security.web.FilterChainProxy.doFilter(FilterChainProxy.java:178) ~[spring-security-web-5.0.6.RELEASE.jar:5.0.6.RELEASE]     
at org.springframework.web.filter.DelegatingFilterProxy.invokeDelegate(DelegatingFilterProxy.java:357) ~[spring-web-5.0.7.RELEASE.jar:5.0.7.RELEASE]    at org.springframework.web.filter.DelegatingFilterProxy.doFilter(DelegatingFilterProxy.java:270) ~[spring-web-5.0.7.RELEASE.jar:5.0.7.RELEASE]  
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193) ~[tomcat-embed-core-8.5.31.jar:8.5.31]     
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166) ~[tomcat-embed-core-8.5.31.jar:8.5.31]     
at org.springframework.web.filter.RequestContextFilter.doFilterInternal(RequestContextFilter.java:99) ~[spring-web-5.0.7.RELEASE.jar:5.0.7.RELEASE]     
at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107) ~[spring-web-5.0.7.RELEASE.jar:5.0.7.RELEASE]    
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193) ~[tomcat-embed-core-8.5.31.jar:8.5.31]     
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166) ~[tomcat-embed-core-8.5.31.jar:8.5.31]     
at org.springframework.web.filter.HttpPutFormContentFilter.doFilterInternal(HttpPutFormContentFilter.java:109) ~[spring-web-5.0.7.RELEASE.jar:5.0.7.RELEASE]    
at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107) ~[spring-web-5.0.7.RELEASE.jar:5.0.7.RELEASE]    
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193) ~[tomcat-embed-core-8.5.31.jar:8.5.31]     
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166) ~[tomcat-embed-core-8.5.31.jar:8.5.31]     
at org.springframework.web.filter.HiddenHttpMethodFilter.doFilterInternal(HiddenHttpMethodFilter.java:93) ~[spring-web-5.0.7.RELEASE.jar:5.0.7.RELEASE]     at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107) ~[spring-web-5.0.7.RELEASE.jar:5.0.7.RELEASE]    
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193) ~[tomcat-embed-core-8.5.31.jar:8.5.31]     at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166) ~[tomcat-embed-core-8.5.31.jar:8.5.31]     
at org.springframework.session.web.http.SessionRepositoryFilter.doFilterInternal(SessionRepositoryFilter.java:147) ~[spring-session-core-2.0.4.RELEASE.jar:2.0.4.RELEASE]   
at org.springframework.session.web.http.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:81) ~[spring-session-core-2.0.4.RELEASE.jar:2.0.4.RELEASE]  
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193) ~[tomcat-embed-core-8.5.31.jar:8.5.31]     
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166) ~[tomcat-embed-core-8.5.31.jar:8.5.31]     
at org.springframework.web.filter.CharacterEncodingFilter.doFilterInternal(CharacterEncodingFilter.java:200) ~[spring-web-5.0.7.RELEASE.jar:5.0.7.RELEASE]  
at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107) ~[spring-web-5.0.7.RELEASE.jar:5.0.7.RELEASE]    
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193) ~[tomcat-embed-core-8.5.31.jar:8.5.31]     
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166) ~[tomcat-embed-core-8.5.31.jar:8.5.31]     
at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:198) ~[tomcat-embed-core-8.5.31.jar:8.5.31]   
at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:96) [tomcat-embed-core-8.5.31.jar:8.5.31]     
at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:496) [tomcat-embed-core-8.5.31.jar:8.5.31]     
at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:140) [tomcat-embed-core-8.5.31.jar:8.5.31]  
at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:81) [tomcat-embed-core-8.5.31.jar:8.5.31]   
at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:87) [tomcat-embed-core-8.5.31.jar:8.5.31]   
at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:342) [tomcat-embed-core-8.5.31.jar:8.5.31]    
at org.apache.coyote.http11.Http11Processor.service(Http11Processor.java:803) [tomcat-embed-core-8.5.31.jar:8.5.31]     
at org.apache.coyote.AbstractProcessorLight.process(AbstractProcessorLight.java:66) [tomcat-embed-core-8.5.31.jar:8.5.31]   
at org.apache.coyote.AbstractProtocol$ConnectionHandler.process(AbstractProtocol.java:790) [tomcat-embed-core-8.5.31.jar:8.5.31]    
at org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.doRun(NioEndpoint.java:1468) [tomcat-embed-core-8.5.31.jar:8.5.31]    
at org.apache.tomcat.util.net.SocketProcessorBase.run(SocketProcessorBase.java:49) [tomcat-embed-core-8.5.31.jar:8.5.31]    
at java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1135) [na:na]    
at java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:635) [na:na]    
at org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61) [tomcat-embed-core-8.5.31.jar:8.5.31]     
at java.base/java.lang.Thread.run(Thread.java:844) [na:na]

You can use `Constructor Expression` if you want only some property from the object. — soorapadman, Oct 01 '18 at 13:28
@mrkernelpanic my entity is different. I want only some of the columns with count. — Anjali, Oct 02 '18 at 04:01
@JensSchauder updated stacktrace for both the ways. using JPA query and nativequery — Anjali, Oct 02 '18 at 05:13
Please, format stack traces as code, not as a quote. Makes them much easier to read. — Jens Schauder, Oct 02 '18 at 05:14
Possible duplicate of [How to return a custom object from a Spring Data JPA GROUP BY query](https://stackoverflow.com/questions/36328063/how-to-return-a-custom-object-from-a-spring-data-jpa-group-by-query) — manish, Apr 03 '19 at 07:13

Thanthu · Answer 1 · 2020-12-19T11:53:16.743

53

You can solve this issue and achieve the result by using projections by making your DTO an interface with getters for columns returned by the query. That's what I did when I faced the same issue.

For example in your case change StatsDTO to interface like shown below, make sure to give alias for your columns if you are using '.' operator:

public interface StatsDTO {
    Integer getUserCount();
    Byte getTypeId();
    Instant getModifiedAt();
}

Also in your query give alias for your columns like userCount, typeId, modifiedAt respectively so that it is mapped correctly.

edited Dec 19 '20 at 11:53

answered Apr 03 '19 at 05:26

Thanthu

4,399
34
43

9

Is there no way to make it work using a class here? I would like to have a class with variables `userCount`, `typeId`, `modifiedAt` and map them using same conventions. I am ready to take on new dependency installations but use class and make SQL result Objects map to instances of this class directly to have `List` Yes, I have a working `SqlResultSetMapping` setup but that is too much for my use case – krozaine Jan 29 '20 at 19:02
3

Finally! this is the only solution i've got as of today to directly map the result of a nativeQuery to a DTO. – Mario Codes Nov 29 '21 at 17:02
2

Thanks, it works fine to me, just I can't understand how this works, so when the values return from the database They are gonna to look the get methods (with their name) and the interface create automatically the properties where to save the return values? Am I right or I don't understand something? Thanks for your consideration – Robs Mar 04 '22 at 17:08
The concept underneath making it work is class proxies. In a nutshell you define your contract with an interface matching the query column / alias names and hibernate provides a proxy implementation that is able to match both sides. If you debug the result of the query you can see the internals of the proxy class – Miguel Suarez Peleteiro Oct 21 '22 at 09:38
This worked fine for me to directly map the DB results to a DTO. – Ripudaman Singh Jul 20 '23 at 10:55

David Pham · Accepted Answer · 2018-10-02T05:46:54.123

32

You can use the constructor expression JPQL query, your query looks like this:

select new StatsDTO(count(u),u.typeId,u.modifiedAt) from UserCampaignObjective u where campId = ? group by objectiveTypeId,modifiedAt

But you make sure the constructor StatsDTO existed already.

edited Oct 02 '18 at 05:46

answered Oct 01 '18 at 14:27

David Pham

1,673
19
17

I tried as you said but QuerySyntaxException: user_campaign_objective is not mapped error is coming. I have defined constructor as well. – Anjali Oct 02 '18 at 03:53
@Anjali i just standardized the query for you, look back answer. Remember that you have a bean class userCampaignObjective which maps to user_campaign_objective table. – David Pham Oct 02 '18 at 04:11
my entity class is different which maps to user_campaign_objective table. I want count of one column and other 2 fields, so for this I have created StatsDTO class which contains count, typeId,modifiedAt. – Anjali Oct 02 '18 at 04:32
I just update answer. Your StatsDTO is just java oject is not entity. Entity class will map to one table. Spring JPA will working on that entity – David Pham Oct 02 '18 at 04:36
I tried your update and I got QuerySyntaxException: userCampaignObjective is not mapped. Even I have given same fields name in statsdto as entity. – Anjali Oct 02 '18 at 04:40
Did you create entity class userCampaignObjective with @Entity annotation? – David Pham Oct 02 '18 at 05:17
Yes userCampaignObjective has @Entity annotation – Anjali Oct 02 '18 at 05:40
Sorry, You have UserCampaignObjective entity already. So you replace userCampaignObjective by UserCampaignObjective in the query, then try it again – David Pham Oct 02 '18 at 05:49
Thanks David for help and patience. Its working now. You saved lot of time of mine. thanks a lot – Anjali Oct 02 '18 at 06:06
How can i use a distinct clause with such a query – Malkeith Singh Dec 18 '19 at 18:26
In my case had cast() on the constructor call : cast(ds.demoId as java.lang.String) – Alferd Nobel Nov 30 '20 at 20:58
5

When using a query like this, make sure you are giving the full path of the DTO class.e.g. `com.example.task.dto.StatsDTO(arg1, arg2, ...)`. Reference: https://stackoverflow.com/a/36329166/4539906 – Asad Shakeel Feb 14 '21 at 19:36
package name should be included for JPA to recognize it. – Smart Coder Aug 16 '21 at 17:25
3

This is using JPQL and won't work for the OP as he is using "native query". Need to use "projections" as per @Thanthu's answer by making the DTO an interface. – Glenster Nov 12 '21 at 09:38

score 6 · Answer 3 · answered Oct 01 '18 at 14:03

6

You have a mismatch between the column names:

userCount
type_id
modified_at

And your property names:

userCount
typeId
modifiedAt

Since this is a native query the JPA naming strategy doesn't apply and the column names should match the property names. So if you change the query to the following, it should work:

select count(type_id) userCount, type_id as typeId, modified_at as modifiedAt from ...

answered Oct 01 '18 at 14:03

Jens Schauder

77,657
34
181
348

2

@Jeans tried your suggestions but still same issue. did not help. – Anjali Oct 02 '18 at 03:47
I tried with JPQL and I got QuerySyntaxException: userCampaignObjective is not mapped. Even I have given same fields name in statsdto as entity. – Anjali Oct 02 '18 at 04:44

score 6 · Answer 4 · answered Jan 23 '21 at 04:28

6

If you are still looking for an answer, here is how I did

@Data
@NoArgsConstructor
@AllArgsConstructor
public class StatsDTO {
    private Integer userCount;
    private Byte typeId;
    private Instant modifiedAt;
}

and your query should be like this

public interface UserRepository extends CrudRepository<UserEntity, Long> {
    @Query(value = "select new com.example.package.StatsDTO(count(type_id) userCount, typeId, modifiedAt from "
                + "UserCampaignObjective where campId = ?1 group by objectiveTypeId,modifiedAt")

        List<StatsDTO> getStatsDTO(Long camp_id);
}

make sure you have a constructor present in StatsDTO to map all fields.

answered Jan 23 '21 at 04:28

Sandeep Choudhary

252
3
14

the opening and closing braces don't match, there are 3 opening brackets, and only 2 closing brackets in line: @Query(value = "select new com.example.package.StatsDTO(count(type_id) userCount, typeId, modifiedAt from " + "UserCampaignObjective where campId = ?1 group by objectiveTypeId,modifiedAt") – troy_achilies Feb 19 '22 at 13:04
Nevertheless, this answer helped me and I would like to up vote it but I am unable to do so because stackoverflow won't allow me to up vote till you edit answer, just put that closing bracket and I will upvote, thanks! – troy_achilies Feb 19 '22 at 13:25

score 2 · Answer 5 · answered Mar 23 '22 at 13:19

A sureshot way to solve it is using NamedNativeQuery.

UserEntity.java

@AllArgsConstructor
@NoArgsConstructor
@Data
@Entity
@SqlResultSetMapping(
        name = "userEntityMapping",
        classes = {
                @ConstructorResult(
                        targetClass = StatsDTO.class,
                        columns = {
                                @ColumnResult(name = "campId", type = Integer.class),
                                @ColumnResult(name = "userCount", type = Byte.class),
                                @ColumnResult(name = "modifiedAt", type = Instant.class)
                        }
                )
        }
)
@NamedNativeQuery(
        name = "UserEntity.getStatsDTO",
        query = "YOUR_QUERY",
        resultSetMapping = "userEntityMapping"
)
@Table(name = "user_campaign_objective")
public class UserEntity implements Serializable {
    private static final long serialVersionUID = 1224483473794225719L;

    @Id
    @Column(name = "campaign_id")
    private BigInteger campId;
}

UserRepository

public interface UserRepository extends CrudRepository<UserEntity, Long> {
    @Query(nativeQuery = true)
    List<StatsDTO> getStatsDTO(Long campId);
}

Just make sure the column names in query matches exactly with your field names in Dto.

score 0 · Answer 6 · answered Nov 12 '21 at 06:17

I also faced the same issue, I tried all solution mentioned here but nothing worked. After some reserch I resolved this by using constructor expression JPQL query

Repository :

public interface SessionsStoreRepository extends 
JpaRepository<SessionsStore,Integer> {

SessionsStore findBySessionId(String sessionId);

//constructor expression JPQL query
@Query(value = "SELECT new "
        + "com.rahul.employeeportal.model.ActiveSession(s.username, e.firstName, e.lastName, MAX(s.loginTime), s.logoutTime, s.isActive) from SessionsStore s, Employee e "
        + " where e.username = s.username GROUP BY s.username")
List<ActiveSession> findUsers();

}

Custom Object POJO :

@AllArgsConstructor
@Data
public class ActiveSession {

    private String username;
    private String firstName;
    private String lastName;
    private LocalDateTime loginTime;
    private LocalDateTime logoutTime;
    private int isActive;
}

Note : Make sure you don't have @NoArgsConstructor(lombok) and query column names should match with Class parameters.

score -1 · Answer 7 · answered Sep 17 '19 at 16:26

-1

Also you could try changing the UserRepository Interface declaration to below,

public interface UserRepository extends CrudRepository<StatsDTO, Long> {
    @Query(value = "select count(type_id) userCount, type_id, modified_at from "
                + "user_campaign_objective where camp_id = ?1 group by objective_type_id,modified_at", nativeQuery = true)

        List<StatsDTO> getStatsDTO(Long camp_id);
}

The change is : CrudRepository <StatsDTO, Long> from CrudRepository < UserEntity, Long>.

I faced the same issue while using a different data object inside a repository declared with another data object.

answered Sep 17 '19 at 16:26

arun sebasatian

1
1

1

HI, getting,Caused by: java.lang.IllegalArgumentException: Not a managed type: exception – Arvind Singh Dec 11 '19 at 02:47
Did you annotate your entity class with @Entity? – arun sebasatian Jul 08 '20 at 21:07

score -3 · Answer 8 · edited Apr 25 '21 at 12:17

-3

Tried a lot of resources online but ultimately this dependency in the POM file worked.

<dependency>
    <groupId>com.alibaba</groupId>
    <artifactId>druid-spring-boot-starter</artifactId>
    <version>1.1.9</version>
</dependency>

edited Apr 25 '21 at 12:17

Brian Tompsett - 汤莱恩

5,753
72
57
129

answered Apr 25 '21 at 11:36

Apoorve Bhargava

1

can you please explain what this dependency does? – Alexandros Kourtis Jan 23 '23 at 08:28

Spring JPA :: No converter found capable of converting from type

8 Answers8

Linked