CODEWITHSUNDEEP
javahibernatespring-data-jpa
Faheem Sultan
Faheem Sultan

Reputation: 283

Select specific column in jpa native query and map to a pojo

I am trying to select specific columns in a spring data jpa native query and trying to get it mapped to a pojo, but hibernate throws as exception for any column that i don't mention in the query and is present in the pojo. now i only want to select specific columns and not the whole table. is there any way i can only select specific columns and get it mapped to my pojo?

following is the exception:

    Hibernate: select id, account_id, operator_id,stop_aggregator,is_free_msg_on_unsubscribe,unsubscribe_call_to_sub_engine_required,unsubscribe_by_account_notify_url,is_unsubscribe_by_account_forwarding_enabled,product_type,operator_billing_type, aggregator_username, aggregator_password from billing_products where id=?
    2020-08-19 15:17:51,798 unsubscription WARN [qtp2123460034-79] SqlExceptionHelper: SQL Error: 0, SQLState: S0022
    2020-08-19 15:17:51,798 unsubscription ERROR [qtp2123460034-79] SqlExceptionHelper: Column 'aggregator_name' not found.
    2020-08-19 15:17:51,805 unsubscription ERROR [qtp2123460034-79] Exception: Exchange[ExchangePattern: InOut, BodyType: com.globalcharge.gbs.unsubscription.model.GenericUnsubRequest, Body: com.globalcharge.gbs.unsubscription.model.GenericUnsubRequest@3d88ca3d, CaughtExceptionType: org.springframework.dao.InvalidDataAccessResourceUsageException, CaughtExceptionMessage: could not execute query; SQL [select id, account_id, operator_id,stop_aggregator,is_free_msg_on_unsubscribe,unsubscribe_call_to_sub_engine_required,unsubscribe_by_account_notify_url,is_unsubscribe_by_account_forwarding_enabled,product_type,operator_billing_type, aggregator_username, aggregator_password from billing_products where id=?]; nested exception is org.hibernate.exception.SQLGrammarException: could not execute query, StackTrace: org.springframework.dao.InvalidDataAccessResourceUsageException: could not execute query; SQL [select id, account_id, operator_id,stop_aggregator,is_free_msg_on_unsubscribe,unsubscribe_call_to_sub_engine_required,unsubscribe_by_account_notify_url,is_unsubscribe_by_account_forwarding_enabled,product_type,operator_billing_type, aggregator_username, aggregator_password from billing_products where id=?]; nested exception is org.hibernate.exception.SQLGrammarException: could not execute query
        at org.springframework.orm.jpa.vendor.HibernateJpaDialect.convertHibernateAccessException(HibernateJpaDialect.java:279)
        at org.springframework.orm.jpa.vendor.HibernateJpaDialect.translateExceptionIfPossible(HibernateJpaDialect.java:253)
        at org.springframework.orm.jpa.AbstractEntityManagerFactoryBean.translateExceptionIfPossible(AbstractEntityManagerFactoryBean.java:527)
        at org.springframework.dao.support.ChainedPersistenceExceptionTranslator.translateExceptionIfPossible(ChainedPersistenceExceptionTranslator.java:61)
        at org.springframework.dao.support.DataAccessUtils.translateIfNecessary(DataAccessUtils.java:242)
        at org.springframework.dao.support.PersistenceExceptionTranslationInterceptor.invoke(PersistenceExceptionTranslationInterceptor.java:153)
        at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186)
        at org.springframework.data.jpa.repository.support.CrudMethodMetadataPostProcessor$CrudMethodMetadataPopulatingMethodInterceptor.invoke(CrudMethodMetadataPostProcessor.java:144)
        at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186)
        at org.springframework.data.jpa.repository.support.CrudMethodMetadataPostProcessor$ExposeRepositoryInvocationInterceptor.invoke(CrudMethodMetadataPostProcessor.java:364)
        at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186)
        at org.springframework.aop.interceptor.ExposeInvocationInterceptor.invoke(ExposeInvocationInterceptor.java:93)
        at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186)
        at org.springframework.data.repository.core.support.SurroundingTransactionDetectorMethodInterceptor.invoke(SurroundingTransactionDetectorMethodInterceptor.java:61)
        at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186)
        at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:212)
        at com.sun.proxy.$Proxy127.loadStopInfo(Unknown Source)
        at com.globalcharge.gbs.unsubscription.util.DataUtil.loadStopInfo(DataUtil.java:204)
        at com.globalcharge.gbs.unsubscription.processor.UnsubscriptionProcessor.checkBillingAndStopInfoDetails(UnsubscriptionProcessor.java:272)
        at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
        at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
        at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
        at java.lang.reflect.Method.invoke(Method.java:498)
        at org.apache.camel.component.bean.MethodInfo.invoke(MethodInfo.java:481)
        at org.apache.camel.component.bean.MethodInfo$1.doProceed(MethodInfo.java:300)
        at org.apache.camel.component.bean.MethodInfo$1.proceed(MethodInfo.java:273)
        at org.apache.camel.component.bean.AbstractBeanProcessor.process(AbstractBeanProcessor.java:198)
        at org.apache.camel.component.bean.BeanProcessor.process(BeanProcessor.java:53)
        at org.apache.camel.component.bean.BeanProducer.process(BeanProducer.java:41)
        at org.apache.camel.processor.SendProcessor.process(SendProcessor.java:148)
        at org.apache.camel.management.InstrumentationProcessor.process(InstrumentationProcessor.java:76)
        at org.apache.camel.processor.RedeliveryErrorHandler.process(RedeliveryErrorHandler.java:548)
        at org.apache.camel.processor.CamelInternalProcessor.process(CamelInternalProcessor.java:201)
        at org.apache.camel.processor.Pipeline.process(Pipeline.java:138)
        at org.apache.camel.processor.Pipeline.process(Pipeline.java:101)
        at org.apache.camel.processor.FilterProcessor.process(FilterProcessor.java:57)
        at org.apache.camel.management.InstrumentationProcessor.process(InstrumentationProcessor.java:76)
        at org.apache.camel.processor.RedeliveryErrorHandler.process(RedeliveryErrorHandler.java:548)
        at org.apache.camel.processor.CamelInternalProcessor.process(CamelInternalProcessor.java:201)
        at org.apache.camel.processor.Pipeline.process(Pipeline.java:138)
        at org.apache.camel.processor.Pipeline.process(Pipeline.java:101)
        at org.apache.camel.processor.ChoiceProcessor.process(ChoiceProcessor.java:117)
        at org.apache.camel.management.InstrumentationProcessor.process(InstrumentationProcessor.java:76)
        at org.apache.camel.processor.RedeliveryErrorHandler.process(RedeliveryErrorHandler.java:548)
        at org.apache.camel.processor.CamelInternalProcessor.process(CamelInternalProcessor.java:201)
        at org.apache.camel.processor.Pipeline.process(Pipeline.java:138)
        at org.apache.camel.processor.Pipeline.process(Pipeline.java:101)
        at org.apache.camel.processor.CamelInternalProcessor.process(CamelInternalProcessor.java:201)
        at org.apache.camel.component.direct.DirectProducer.process(DirectProducer.java:76)
        at org.apache.camel.processor.SendProcessor.process(SendProcessor.java:148)
        at org.apache.camel.processor.RedeliveryErrorHandler.process(RedeliveryErrorHandler.java:548)
        at org.apache.camel.processor.CamelInternalProcessor.process(CamelInternalProcessor.java:201)
        at org.apache.camel.processor.CamelInternalProcessor.process(CamelInternalProcessor.java:201)
        at org.apache.camel.component.jetty.CamelContinuationServlet.doService(CamelContinuationServlet.java:220)
        at org.apache.camel.http.common.CamelServlet.service(CamelServlet.java:79)
        at javax.servlet.http.HttpServlet.service(HttpServlet.java:741)
        at org.eclipse.jetty.servlet.ServletHolder.handle(ServletHolder.java:876)
        at org.eclipse.jetty.servlet.ServletHandler.doHandle(ServletHandler.java:542)
        at org.eclipse.jetty.server.handler.ScopedHandler.nextHandle(ScopedHandler.java:255)
        at org.eclipse.jetty.server.handler.ContextHandler.doHandle(ContextHandler.java:1347)
        at org.eclipse.jetty.server.handler.ScopedHandler.nextScope(ScopedHandler.java:203)
        at org.eclipse.jetty.servlet.ServletHandler.doScope(ServletHandler.java:480)
        at org.eclipse.jetty.server.handler.ScopedHandler.nextScope(ScopedHandler.java:201)
        at org.eclipse.jetty.server.handler.ContextHandler.doScope(ContextHandler.java:1249)
        at org.eclipse.jetty.server.handler.ScopedHandler.handle(ScopedHandler.java:144)
        at org.eclipse.jetty.server.handler.HandlerWrapper.handle(HandlerWrapper.java:132)
        at org.eclipse.jetty.server.Server.handle(Server.java:505)
        at org.eclipse.jetty.server.HttpChannel.handle(HttpChannel.java:370)
        at org.eclipse.jetty.server.HttpConnection.onFillable(HttpConnection.java:267)
        at org.eclipse.jetty.io.AbstractConnection$ReadCallback.succeeded(AbstractConnection.java:305)
        at org.eclipse.jetty.io.FillInterest.fillable(FillInterest.java:103)
        at org.eclipse.jetty.io.ChannelEndPoint$2.run(ChannelEndPoint.java:117)
        at org.eclipse.jetty.util.thread.QueuedThreadPool.runJob(QueuedThreadPool.java:781)
        at org.eclipse.jetty.util.thread.QueuedThreadPool$Runner.run(QueuedThreadPool.java:917)
        at java.lang.Thread.run(Thread.java:748)
    Caused by: org.hibernate.exception.SQLGrammarException: could not execute query
        at org.hibernate.exception.internal.SQLStateConversionDelegate.convert(SQLStateConversionDelegate.java:106)
        at org.hibernate.exception.internal.StandardSQLExceptionConverter.convert(StandardSQLExceptionConverter.java:42)
        at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:113)
        at org.hibernate.loader.Loader.doList(Loader.java:2692)
        at org.hibernate.loader.Loader.doList(Loader.java:2672)
        at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2506)
        at org.hibernate.loader.Loader.list(Loader.java:2501)
        at org.hibernate.loader.custom.CustomLoader.list(CustomLoader.java:338)
        at org.hibernate.internal.SessionImpl.listCustomQuery(SessionImpl.java:2223)
        at org.hibernate.internal.AbstractSharedSessionContract.list(AbstractSharedSessionContract.java:1069)
        at org.hibernate.query.internal.NativeQueryImpl.doList(NativeQueryImpl.java:170)
        at org.hibernate.query.internal.AbstractProducedQuery.list(AbstractProducedQuery.java:1505)
        at org.hibernate.query.Query.getResultList(Query.java:132)
        at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
        at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
        at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
        at java.lang.reflect.Method.invoke(Method.java:498)
        at org.springframework.orm.jpa.SharedEntityManagerCreator$DeferredQueryInvocationHandler.invoke(SharedEntityManagerCreator.java:403)
        at com.sun.proxy.$Proxy149.getResultList(Unknown Source)
        at org.springframework.data.jpa.repository.query.JpaQueryExecution$CollectionExecution.doExecute(JpaQueryExecution.java:129)
        at org.springframework.data.jpa.repository.query.JpaQueryExecution.execute(JpaQueryExecution.java:91)
        at org.springframework.data.jpa.repository.query.AbstractJpaQ...]

following is my Repository

@Repository
public interface BillingProductsRepo extends JpaRepository<BillingProduct, Integer> {
    
        @Query(value = "select id, account_id, operator_id,stop_aggregator,"
                + "is_free_msg_on_unsubscribe,unsubscribe_call_to_sub_engine_required,unsubscribe_by_account_notify_url,"
                + "is_unsubscribe_by_account_forwarding_enabled,product_type,operator_billing_type, aggregator_username, "
                + "aggregator_password from billing_products where id=:id", nativeQuery = true)
        List<BillingProduct> loadStopInfo(@Param("id") String productId);
}

and following is my POJO

@Entity(name = "billing_products")
public class BillingProduct implements Serializable {
    
    
        /**
         * 
         */
        private static final long serialVersionUID = 1103975041539208371L;
        
    
        @Id
        @Column(name = "id")
        @JsonProperty("id")
        private int id;
        
        
        @JsonProperty("stopShortcode")
        @Column(name = "stop_shortcode")
        private String stopShortcode;
    
        
        @JsonProperty("stopKeyword")
        @Column(name = "stop_keyword")
        private String stopKeyword;
    
        
        @JsonProperty("privacyAndSecurityUrl")
        @Column(name = "privacy_and_security_url")
        private String privacyAndSecurityUrl;
    
        
        @JsonProperty("accountId")
        @Column(name = "account_id")
        private String accountId;
    
        
        @JsonProperty("unsubscribeByAccountNotifyUrl")
        @Column(name = "unsubscribe_by_account_notify_url")
        private String unsubscribeByAccountNotifyUrl;
    
        
        @JsonProperty("isUnsubscribeByAccountForwardingEnabled")
        @Column(name = "is_unsubscribe_by_account_forwarding_enabled")
        private boolean isUnsubscribeByAccountForwardingEnabled;
    
        
        @JsonProperty("stopAggregator")
        @Column(name = "stop_aggregator")
        private String stopAggregator;
    
        
        @JsonProperty("isStopForwardingEnabled")
        @Column(name = "is_stop_forwarding_enabled")
        private boolean isStopForwardingEnabled;
    
        
        @JsonProperty("stopNotifyUrl")
        @Column(name = "stop_notify_url")
        private String stopNotifyUrl;
    
        
        @JsonProperty("stopMtUrl")
        @Column(name = "stop_mt_url")
        private String stopMtUrl;
    
        
        @JsonProperty("isFreeMsgOnUnsubscribe")
        @Column(name = "is_free_msg_on_unsubscribe")
        private boolean isFreeMsgOnUnsubscribe;
    
        
        @JsonProperty("unsubscribeCallToSubEngineRequired")
        @Column(name = "unsubscribe_call_to_sub_engine_required")
        private boolean unsubscribeCallToSubEngineRequired;
    
        
        @JsonProperty("operatorBillingType")
        @Column(name = "operator_billing_type")
        private String operatorBillingType;
    
        
        @JsonProperty("productType")
        @Column(name = "product_type")
        private String productType;
        
        
        @JsonProperty("aggregatorUsername")
        @Column(name = "aggregator_username")
        private String aggregatorUsername;
        
        
        @JsonProperty("aggregatorPassword")
        @Column(name = "aggregator_password")
        private String aggregatorPassword;
        
        
        @JsonProperty("operatorId")
        @Column(name = "operator_id")
        private int operatorId;
        
        @JsonProperty("aggregatorName")
        
        @Column(name="aggregator_name")
        private String aggregatorName;
        
        public int getId() {
            return id;
        }
        public void setId(int id) {
            this.id = id;
        }
        
        public String getStopAggregator() {
            return stopAggregator;
        }
        public void setStopAggregator(String stopAggregator) {
            this.stopAggregator = stopAggregator;
        }
        public boolean isStopForwardingEnabled() {
            return isStopForwardingEnabled;
        }
        public void setStopForwardingEnabled(boolean isStopForwardingEnabled) {
            this.isStopForwardingEnabled = isStopForwardingEnabled;
        }
        public String getStopShortcode() {
            return stopShortcode;
        }
        public void setStopShortcode(String stopShortcode) {
            this.stopShortcode = stopShortcode;
        }
        public String getStopKeyword() {
            return stopKeyword;
        }
        public void setStopKeyword(String stopKeyword) {
            this.stopKeyword = stopKeyword;
        }
        public String getPrivacyAndSecurityUrl() {
            return privacyAndSecurityUrl;
        }
        public void setPrivacyAndSecurityUrl(String privacyAndSecurityUrl) {
            this.privacyAndSecurityUrl = privacyAndSecurityUrl;
        }
        public String getAccountId() {
            return accountId;
        }
        public void setAccountId(String accountId) {
            this.accountId = accountId;
        }
        public String getUnsubscribeByAccountNotifyUrl() {
            return unsubscribeByAccountNotifyUrl;
        }
        public void setUnsubscribeByAccountNotifyUrl(String unsubscribeByAccountNotifyUrl) {
            this.unsubscribeByAccountNotifyUrl = unsubscribeByAccountNotifyUrl;
        }
        public boolean isUnsubscribeByAccountForwardingEnabled() {
            return isUnsubscribeByAccountForwardingEnabled;
        }
        public void setUnsubscribeByAccountForwardingEnabled(boolean isUnsubscribeByAccountForwardingEnabled) {
            this.isUnsubscribeByAccountForwardingEnabled = isUnsubscribeByAccountForwardingEnabled;
        }
        public String getStopNotifyUrl() {
            return stopNotifyUrl;
        }
        public void setStopNotifyUrl(String stopNotifyUrl) {
            this.stopNotifyUrl = stopNotifyUrl;
        }
        public String getStopMtUrl() {
            return stopMtUrl;
        }
        public void setStopMtUrl(String stopMtUrl) {
            this.stopMtUrl = stopMtUrl;
        }
        public boolean isFreeMsgOnUnsubscribe() {
            return isFreeMsgOnUnsubscribe;
        }
        public void setFreeMsgOnUnsubscribe(boolean isFreeMsgOnUnsubscribe) {
            this.isFreeMsgOnUnsubscribe = isFreeMsgOnUnsubscribe;
        }
        public boolean isUnsubscribeCallToSubEngineRequired() {
            return unsubscribeCallToSubEngineRequired;
        }
        public void setUnsubscribeCallToSubEngineRequired(boolean unsubscribeCallToSubEngineRequired) {
            this.unsubscribeCallToSubEngineRequired = unsubscribeCallToSubEngineRequired;
        }
        public String getOperatorBillingType() {
            return operatorBillingType;
        }
        public void setOperatorBillingType(String operatorBillingType) {
            this.operatorBillingType = operatorBillingType;
        }
        public String getProductType() {
            return productType;
        }
        public void setProductType(String productType) {
            this.productType = productType;
        }
        public String getAggregatorUsername() {
            return aggregatorUsername;
        }
        public void setAggregatorUsername(String aggregatorUsername) {
            this.aggregatorUsername = aggregatorUsername;
        }
        public String getAggregatorPassword() {
            return aggregatorPassword;
        }
        public void setAggregatorPassword(String aggregatorPassword) {
            this.aggregatorPassword = aggregatorPassword;
        }
        public int getOperatorId() {
            return operatorId;
        }
        public void setOperatorId(int operatorId) {
            this.operatorId = operatorId;
        }
        public String getAggregatorName() {
            return aggregatorName;
        }
        public void setAggregatorName(String aggregatorName) {
            this.aggregatorName = aggregatorName;
        }
    
}

Upvotes: 0

Views: 2826

Answers (2)

Wardibald
Wardibald

Reputation: 71

A JPA entity isn't really meant to be used in this way as it represents a full row of data in a table, that is subject to CRUD operations. If you only partially query it:

  • You have a JPA managed Object in a possibly inconsistent state, which at best does nothing helpful, but can lead to the Object being (attempted to be) persisted in the inconsistent state, e.g. when the current transaction closes while the managed Object is still in scope.
  • You cannot use additional tools like Hibernate-validator effectively

The answer of SternK is not wrong in this sense (it will work), but it is better to query on a DTO Object in the same way as SternK's answer:

  • select new fully.qualified.DTOClassName(...)
  • make sure you have the appropriate constructor available in the class

Upvotes: 2

SternK
SternK

Reputation: 13111

You do not need native query for that. You can just declare an appropriate constructor in the BillingProduct entity:

@Entity(name = "billing_products")
public class BillingProduct implements Serializable {

   public BillingProduct(int id, String accountId, ...)
   {
      // ...
   }
   // ...
}

and then use it in the query:

@Repository
public interface BillingProductsRepo extends JpaRepository<BillingProduct, Integer> {


    @Query(value = "select new org.my.BillingProduct(b.id, b.accountId, ... ) from BillingProduct b where b.id=:id")
    List<BillingProduct> loadStopInfo(@Param("id") String productId);
}

See the documentation.

As an alternative you can also try to use the @SqlResultSetMapping annotation. But as for me it is more complex way.

Upvotes: 4

Related Questions