英文:
Select specific column in jpa native query and map to a pojo
问题
以下是我为您翻译的内容：
我正在尝试在Spring Data JPA原生查询中选择特定的列，并尝试将其映射到一个POJO，但是当我在查询中没有提及但在POJO中存在的任何列时，Hibernate会抛出异常。现在，我只想选择特定的列，而不是整个表。有没有办法我只选择特定的列并将其映射到我的POJO中？
以下是异常信息：
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.
...
以下是我的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);
}
以下是我的POJO代码：
@Entity(name = "billing_products")
public class BillingProduct implements Serializable {
// ... (省略其他属性和方法)
}
请注意，我已经省略了部分代码，只翻译了您提供的内容。如果您有其他问题或需要更多帮助，请随时提问。
英文:
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;
}
}
答案1
得分: 4
你不需要为此使用原生查询。你可以在 BillingProduct 实体中声明一个合适的构造函数：
@Entity(name = "billing_products")
public class BillingProduct implements Serializable {
public BillingProduct(int id, String accountId, ...)
{
// ...
}
// ...
}
然后在查询中使用它：
@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);
}
请参阅文档。
作为替代方案，你也可以尝试使用 @SqlResultSetMapping 注解。但对我来说，这是一种更复杂的方法。
英文:
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.
答案2
得分: 2
一个 JPA 实体并不适合以这种方式使用，因为它表示表中的一整行数据，受到 CRUD 操作的影响。如果你只进行部分查询：
- 你会得到一个由 JPA 管理的对象，可能处于不一致的状态，最好的情况下没有任何帮助，但可能导致对象在不一致状态下被（尝试）持久化，例如当当前事务在托管对象仍在范围内时关闭。
- 你无法有效地使用像 Hibernate-validator 这样的附加工具
SternK 的回答在这个意义上并没有错（它会起作用），但更好的做法是以与 SternK 回答相同的方式在 DTO 对象上进行查询：
- 选择 new 完整的.合格的.DTO类名（...）
- 确保在类中有适当的构造函数可用。
英文:
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
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论