This is my first attempt at using Hibernate w/ Panache. I am working on a serverless function to insert and update records in a database. But before I can to that I need to obtain an ID from a table. This ID will be added to each record as it goes into the database.
The database is a DB2 AS/400 table and is defined so:
CREATE TABLE PCFPIDS (
IDSCOMP CHAR(2) NOT NULL DEFAULT '' ,
IDSID NUMERIC(11, 0) NOT NULL DEFAULT 0 ,
IDSMAX NUMERIC(11, 0) NOT NULL DEFAULT 0 )
PRIMARY KEY( IDSCOMP ) );
My entity class is defined:
@Entity
public class PCFPIDS extends PanacheEntity {
public String IDSCOMP;
public String IDSID;
public int IDSMAX;
public static PCFPIDS getLatestSession() {
return find("IDSCOMP", "##").firstResult();
}
}
Yet when I run the application I receive the following error:
2021-11-22 13:14:45,108 ERROR [io.qua.ver.htt.run.QuarkusErrorHandler] (executor-thread-0) HTTP Request to /saveRecord failed, error id: d736abda-ce6e-4f86-a8ea-b96e24d52612-2: io.quarkus.funqy.runtime.ApplicationException: javax.persistence.PersistenceException: org.hibernate.exception.SQLGrammarException: could not prepare statement
at io.quarkus.funqy.runtime.FunctionInvoker.invoke(FunctionInvoker.java:131)
at io.quarkus.funqy.runtime.bindings.http.VertxRequestHandler.dispatch(VertxRequestHandler.java:141)
at io.quarkus.funqy.runtime.bindings.http.VertxRequestHandler.lambda$handle$1(VertxRequestHandler.java:116)
at io.quarkus.vertx.core.runtime.VertxCoreRecorder$13.runWith(VertxCoreRecorder.java:543)
at org.jboss.threads.EnhancedQueueExecutor$Task.run(EnhancedQueueExecutor.java:2449)
at org.jboss.threads.EnhancedQueueExecutor$ThreadBody.run(EnhancedQueueExecutor.java:1478)
at org.jboss.threads.DelegatingRunnable.run(DelegatingRunnable.java:29)
at org.jboss.threads.ThreadLocalResettingRunnable.run(ThreadLocalResettingRunnable.java:29)
at io.netty.util.concurrent.FastThreadLocalRunnable.run(FastThreadLocalRunnable.java:30)
at java.base/java.lang.Thread.run(Thread.java:834)
Caused by: javax.persistence.PersistenceException: org.hibernate.exception.SQLGrammarException: could not prepare statement
at org.hibernate.internal.ExceptionConverterImpl.convert(ExceptionConverterImpl.java:154)
at org.hibernate.query.internal.AbstractProducedQuery.list(AbstractProducedQuery.java:1626)
at org.hibernate.query.Query.getResultList(Query.java:165)
at io.quarkus.hibernate.orm.panache.common.runtime.CommonPanacheQueryImpl.firstResult(CommonPanacheQueryImpl.java:263)
at io.quarkus.hibernate.orm.panache.runtime.PanacheQueryImpl.firstResult(PanacheQueryImpl.java:159)
at com.goodyear.inventory.entities.PCFPIDS.getLatestSession(PCFPIDS.java:15)
at com.goodyear.inventory.SaveInventoryFunction.saveRecord(SaveInventoryFunction.java:13)
at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.base/java.lang.reflect.Method.invoke(Method.java:566)
at io.quarkus.funqy.runtime.FunctionInvoker.invoke(FunctionInvoker.java:120)
... 9 more
Caused by: org.hibernate.exception.SQLGrammarException: could not prepare statement
at org.hibernate.exception.internal.SQLStateConversionDelegate.convert(SQLStateConversionDelegate.java:103)
at org.hibernate.exception.internal.StandardSQLExceptionConverter.convert(StandardSQLExceptionConverter.java:37)
at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:113)
at org.hibernate.engine.jdbc.internal.StatementPreparerImpl$StatementPreparationTemplate.prepareStatement(StatementPreparerImpl.java:186)
at org.hibernate.engine.jdbc.internal.StatementPreparerImpl.prepareQueryStatement(StatementPreparerImpl.java:151)
at org.hibernate.loader.Loader.prepareQueryStatement(Loader.java:2122)
at org.hibernate.loader.Loader.executeQueryStatement(Loader.java:2059)
at org.hibernate.loader.Loader.executeQueryStatement(Loader.java:2037)
at org.hibernate.loader.Loader.doQuery(Loader.java:956)
at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:357)
at org.hibernate.loader.Loader.doList(Loader.java:2868)
at org.hibernate.loader.Loader.doList(Loader.java:2850)
at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2682)
at org.hibernate.loader.Loader.list(Loader.java:2677)
at org.hibernate.loader.hql.QueryLoader.list(QueryLoader.java:540)
at org.hibernate.hql.internal.ast.QueryTranslatorImpl.list(QueryTranslatorImpl.java:400)
at org.hibernate.engine.query.spi.HQLQueryPlan.performList(HQLQueryPlan.java:219)
at org.hibernate.internal.SessionImpl.list(SessionImpl.java:1468)
at org.hibernate.query.internal.AbstractProducedQuery.doList(AbstractProducedQuery.java:1649)
at org.hibernate.query.internal.AbstractProducedQuery.list(AbstractProducedQuery.java:1617)
... 19 more
Caused by: com.ibm.as400.access.AS400JDBCSQLSyntaxErrorException: [SQL0205] Column ID not in table PCFPIDS in PDPRD.
at com.ibm.as400.access.JDError.createSQLExceptionSubClass(JDError.java:948)
at com.ibm.as400.access.JDError.throwSQLException(JDError.java:745)
at com.ibm.as400.access.AS400JDBCStatement.commonPrepare(AS400JDBCStatement.java:1815)
at com.ibm.as400.access.AS400JDBCPreparedStatementImpl.<init>(AS400JDBCPreparedStatementImpl.java:357)
at com.ibm.as400.access.AS400JDBCConnectionImpl.prepareStatement(AS400JDBCConnectionImpl.java:2307)
at com.ibm.as400.access.AS400JDBCConnectionImpl.prepareStatement(AS400JDBCConnectionImpl.java:2084)
at com.ibm.as400.access.AS400JDBCConnectionImpl.prepareStatement(AS400JDBCConnectionImpl.java:2079)
at io.agroal.pool.wrapper.ConnectionWrapper.prepareStatement(ConnectionWrapper.java:659)
at org.hibernate.engine.jdbc.internal.StatementPreparerImpl$5.doPrepare(StatementPreparerImpl.java:149)
at org.hibernate.engine.jdbc.internal.StatementPreparerImpl$StatementPreparationTemplate.prepareStatement(StatementPreparerImpl.java:176)
... 35 more
I’m logging the hibernate SQL statements and here is the output from that:
Hibernate:
select
pcfpids0_.id as id1_0_,
pcfpids0_.IDSCOMP as idscomp2_0_,
pcfpids0_.IDSID as idsid3_0_,
pcfpids0_.IDSMAX as idsmax4_0_
from
PCFPIDS pcfpids0_
where
pcfpids0_.IDSCOMP=? fetch first 1 rows only
For some reason Hibernate/Panache is adding pcfpids0_.id as id1_0_,. Yet if I annotate field IDSCOMP with @Id then I get this error:
2021-11-22 13:22:05,464 ERROR [io.qua.dep.dev.IsolatedDevModeMain] (vert.x-worker-thread-0) Failed to start quarkus: java.lang.RuntimeException: io.quarkus.builder.BuildException: Build failure: Build failed due to errors
[error]: Build step io.quarkus.arc.deployment.ArcProcessor#generateResources threw an exception: javax.enterprise.inject.spi.DeploymentException: io.quarkus.builder.BuildException: Build failure: You provide a JPA identifier via @Id inside 'com.acme.inventory.entities.PCFPIDS' but one is already provided by PanacheEntity, your class should extend PanacheEntityBase instead, or use the id provided by PanacheEntity
at io.quarkus.arc.processor.BeanDeployment.processErrors(BeanDeployment.java:1190)
at io.quarkus.arc.processor.BeanProcessor.processValidationErrors(BeanProcessor.java:142)
at io.quarkus.arc.deployment.ArcProcessor.generateResources(ArcProcessor.java:447)
at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.base/java.lang.reflect.Method.invoke(Method.java:566)
at io.quarkus.deployment.ExtensionLoader$2.execute(ExtensionLoader.java:821)
at io.quarkus.builder.BuildContext.run(BuildContext.java:277)
at org.jboss.threads.ContextHandler$1.runWith(ContextHandler.java:18)
at org.jboss.threads.EnhancedQueueExecutor$Task.run(EnhancedQueueExecutor.java:2449)
at org.jboss.threads.EnhancedQueueExecutor$ThreadBody.run(EnhancedQueueExecutor.java:1478)
at java.base/java.lang.Thread.run(Thread.java:834)
at org.jboss.threads.JBossThread.run(JBossThread.java:501)
Caused by: io.quarkus.builder.BuildException: Build failure: You provide a JPA identifier via @Id inside 'com.acme.inventory.entities.PCFPIDS' but one is already provided by PanacheEntity, your class should extend PanacheEntityBase instead, or use the id provided by PanacheEntity
at io.quarkus.hibernate.orm.panache.deployment.PanacheHibernateResourceProcessor.validate(PanacheHibernateResourceProcessor.java:159)
... 11 more
at io.quarkus.runner.bootstrap.AugmentActionImpl.runAugment(AugmentActionImpl.java:418)
at io.quarkus.runner.bootstrap.AugmentActionImpl.reloadExistingApplication(AugmentActionImpl.java:289)
at io.quarkus.runner.bootstrap.AugmentActionImpl.reloadExistingApplication(AugmentActionImpl.java:67)
at io.quarkus.deployment.dev.IsolatedDevModeMain.restartApp(IsolatedDevModeMain.java:227)
at io.quarkus.deployment.dev.IsolatedDevModeMain.restartCallback(IsolatedDevModeMain.java:210)
at io.quarkus.deployment.dev.RuntimeUpdatesProcessor.doScan(RuntimeUpdatesProcessor.java:516)
at io.quarkus.deployment.dev.RuntimeUpdatesProcessor.doScan(RuntimeUpdatesProcessor.java:417)
at io.quarkus.vertx.http.runtime.devmode.VertxHttpHotReplacementSetup$4.handle(VertxHttpHotReplacementSetup.java:152)
at io.quarkus.vertx.http.runtime.devmode.VertxHttpHotReplacementSetup$4.handle(VertxHttpHotReplacementSetup.java:139)
at io.vertx.core.impl.ContextImpl.lambda$null$0(ContextImpl.java:159)
at io.vertx.core.impl.AbstractContext.dispatch(AbstractContext.java:100)
at io.vertx.core.impl.ContextImpl.lambda$executeBlocking$1(ContextImpl.java:157)
at org.jboss.threads.ContextHandler$1.runWith(ContextHandler.java:18)
at org.jboss.threads.EnhancedQueueExecutor$Task.run(EnhancedQueueExecutor.java:2449)
at org.jboss.threads.EnhancedQueueExecutor$ThreadBody.run(EnhancedQueueExecutor.java:1478)
at org.jboss.threads.DelegatingRunnable.run(DelegatingRunnable.java:29)
at org.jboss.threads.ThreadLocalResettingRunnable.run(ThreadLocalResettingRunnable.java:29)
at io.netty.util.concurrent.FastThreadLocalRunnable.run(FastThreadLocalRunnable.java:30)
at java.base/java.lang.Thread.run(Thread.java:834)
Caused by: io.quarkus.builder.BuildException: Build failure: Build failed due to errors
[error]: Build step io.quarkus.arc.deployment.ArcProcessor#generateResources threw an exception: javax.enterprise.inject.spi.DeploymentException: io.quarkus.builder.BuildException: Build failure: You provide a JPA identifier via @Id inside 'com.acme.inventory.entities.PCFPIDS' but one is already provided by PanacheEntity, your class should extend PanacheEntityBase instead, or use the id provided by PanacheEntity
at io.quarkus.arc.processor.BeanDeployment.processErrors(BeanDeployment.java:1190)
at io.quarkus.arc.processor.BeanProcessor.processValidationErrors(BeanProcessor.java:142)
at io.quarkus.arc.deployment.ArcProcessor.generateResources(ArcProcessor.java:447)
at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.base/java.lang.reflect.Method.invoke(Method.java:566)
at io.quarkus.deployment.ExtensionLoader$2.execute(ExtensionLoader.java:821)
at io.quarkus.builder.BuildContext.run(BuildContext.java:277)
at org.jboss.threads.ContextHandler$1.runWith(ContextHandler.java:18)
at org.jboss.threads.EnhancedQueueExecutor$Task.run(EnhancedQueueExecutor.java:2449)
at org.jboss.threads.EnhancedQueueExecutor$ThreadBody.run(EnhancedQueueExecutor.java:1478)
at java.base/java.lang.Thread.run(Thread.java:834)
at org.jboss.threads.JBossThread.run(JBossThread.java:501)
Caused by: io.quarkus.builder.BuildException: Build failure: You provide a JPA identifier via @Id inside 'com.acme.inventory.entities.PCFPIDS' but one is already provided by PanacheEntity, your class should extend PanacheEntityBase instead, or use the id provided by PanacheEntity
at io.quarkus.hibernate.orm.panache.deployment.PanacheHibernateResourceProcessor.validate(PanacheHibernateResourceProcessor.java:159)
... 11 more
at io.quarkus.builder.Execution.run(Execution.java:116)
at io.quarkus.builder.BuildExecutionBuilder.execute(BuildExecutionBuilder.java:79)
at io.quarkus.deployment.QuarkusAugmentor.run(QuarkusAugmentor.java:161)
at io.quarkus.runner.bootstrap.AugmentActionImpl.runAugment(AugmentActionImpl.java:416)
... 18 more
Caused by: javax.enterprise.inject.spi.DeploymentException: io.quarkus.builder.BuildException: Build failure: You provide a JPA identifier via @Id inside 'com.acme.inventory.entities.PCFPIDS' but one is already provided by PanacheEntity, your class should extend PanacheEntityBase instead, or use the id provided by PanacheEntity
at io.quarkus.arc.processor.BeanDeployment.processErrors(BeanDeployment.java:1190)
at io.quarkus.arc.processor.BeanProcessor.processValidationErrors(BeanProcessor.java:142)
at io.quarkus.arc.deployment.ArcProcessor.generateResources(ArcProcessor.java:447)
at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.base/java.lang.reflect.Method.invoke(Method.java:566)
at io.quarkus.deployment.ExtensionLoader$2.execute(ExtensionLoader.java:821)
at io.quarkus.builder.BuildContext.run(BuildContext.java:277)
at org.jboss.threads.ContextHandler$1.runWith(ContextHandler.java:18)
at org.jboss.threads.EnhancedQueueExecutor$Task.run(EnhancedQueueExecutor.java:2449)
at org.jboss.threads.EnhancedQueueExecutor$ThreadBody.run(EnhancedQueueExecutor.java:1478)
at java.base/java.lang.Thread.run(Thread.java:834)
at org.jboss.threads.JBossThread.run(JBossThread.java:501)
Caused by: io.quarkus.builder.BuildException: Build failure: You provide a JPA identifier via @Id inside 'com.acme.inventory.entities.PCFPIDS' but one is already provided by PanacheEntity, your class should extend PanacheEntityBase instead, or use the id provided by PanacheEntity
at io.quarkus.hibernate.orm.panache.deployment.PanacheHibernateResourceProcessor.validate(PanacheHibernateResourceProcessor.java:159)
... 11 more
I’m not sure how to resolve this. I cannot alter the AS/400 DB2 table and add the ID column. So how do I tell Hibernate/Panache to not add this Id column to my select query?
>Solution :
When our entities extends PanacheEntity, we inherit the Long id from PanacheEntity.
If we want to use a non-Long id, our entity should extends PanacheBaseEntity. The primary key field must then be annotated with @Id:
@Entity
public class PCFPIDS extends PanacheEntityBase {
public String IDSCOMP;
public String IDSID;
@Id
public int IDSMAX;
public static PCFPIDS getLatestSession() {
return find("IDSCOMP", "##").firstResult();
}
}
For more information on how to use hibernate panache, I recommend reading the official quarkus guide on hibernate panache.
A remark on the code:
In java, class names should be written in CamelCase, and field names should be written in camelCase (IDSIP -> idsId). We can align the class- and field-names with the database schema by adding the annotations @Table(name = "<tableNameGoesHere>") and @Column(name = "<columnNameGoesHere>") to the classes and fields respectively.