org.hibernate.exception.SQLGrammarException: could not prepare statement - Table “USER_DETAIL” not found; SQL statement:

Hibernate ORM
1

I’m getting error mentioned in title while calling User login() method. UserDetail Entity looks like this:

@Entity
@Table(name = "user_detail")
@NamedQueries({ @NamedQuery(name = NamedQueryConstants.USER_FIND_ALL, query = "SELECT u FROM UserDetail u"),
    @NamedQuery(name = NamedQueryConstants.USER_FIND_BY_USER_NAME, query = "SELECT u FROM UserDetail u WHERE u.userName = ?1") })
public class UserDetail implements Serializable {
private static final long serialVersionUID = 1L;

@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
@Column(name = "user_id")
private Long userId;

private String password;

@Column(name = "user_email_id")
private String userEmailId;

@Column(name = "user_name")
private String userName;

Below is error StackTrace :

2019-01-21 02:12:37 INFO stdout:71 - Hibernate: select userdetail0_.user_id as user_id1_21_, userdetail0_.last_activity_date as last_act2_21_, userdetail0_.online_status as online_s3_21_, userdetail0_.password_change_date as password4_21_, userdetail0_.password as password5_21_, userdetail0_.user_email_id as user_ema6_21_, userdetail0_.user_name as user_nam7_21_ from user_detail userdetail0_ where userdetail0_.user_name=?

02:12:38,036 WARN [org.hibernate.engine.jdbc.spi.SqlExceptionHelper] (default task-4) SQL Error: 42102, SQLState: 42S02

02:12:38,037 ERROR [org.hibernate.engine.jdbc.spi.SqlExceptionHelper] (default task-4) Table “USER_DETAIL” not found; SQL statement:

select userdetail0_.user_id as user_id1_21_, userdetail0_.last_activity_date as last_act2_21_, userdetail0_.online_status as online_s3_21_, userdetail0_.password_change_date as password4_21_, userdetail0_.password as password5_21_, userdetail0_.user_email_id as user_ema6_21_, userdetail0_.user_name as user_nam7_21_ from user_detail userdetail0_ where userdetail0_.user_name=? [42102-193]

2019-01-21 02:12:38 INFO stdout:71 - Persistence exception# occured - org.hibernate.exception.SQLGrammarException: could not prepare statement

2019-01-21 02:12:38 ERROR stderr:71 - javax.persistence.PersistenceException: org.hibernate.exception.SQLGrammarException: could not prepare statement

2019-01-21 02:12:38 ERROR stderr:71 - at org.hibernate.jpa.spi.AbstractEntityManagerImpl.convert(AbstractEntityManagerImpl.java:1692)

2019-01-21 02:12:38 ERROR stderr:71 - at org.hibernate.jpa.spi.AbstractEntityManagerImpl.convert(AbstractEntityManagerImpl.java:1602)

2019-01-21 02:12:38 ERROR stderr:71 - at org.hibernate.jpa.internal.QueryImpl.getSingleResult(QueryImpl.java:560)

2019-01-21 02:12:38 ERROR stderr:71 - at com.itsys.erp.server.dal.impl.GenericJpaDAO.findSingleByProperty(GenericJpaDAO.java:251)

2019-01-21 02:12:38 ERROR stderr:71 - at com.itsys.erp.server.commonservices.usermgmt.impl.UserManagementServiceImpl.checkUserCredentials(UserManagementServiceImpl.java:29)

2019-01-21 02:12:38 ERROR stderr:71 - at com.itsys.erp.server.commonservices.usermgmt.slsbadapter.UserManagementServiceSlsb.checkUserCredentials(UserManagementServiceSlsb.java:39)

2019-01-21 02:12:38 ERROR stderr:71 - at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)

2019-01-21 02:12:38 ERROR stderr:71 - at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)

2019-01-21 02:12:38 ERROR stderr:71 - at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)

2019-01-21 02:12:38 ERROR stderr:71 - at java.lang.reflect.Method.invoke(Method.java:498)

2019-01-21 02:12:38 ERROR stderr:71 - at org.jboss.as.ee.component.ManagedReferenceMethodInterceptor.processInvocation(ManagedReferenceMethodInterceptor.java:52)

2019-01-21 02:12:38 ERROR stderr:71 - at org.jboss.invocation.InterceptorContext.proceed(InterceptorContext.java:422)

2019-01-21 02:12:38 ERROR stderr:71 - at org.jboss.invocation.InterceptorContext$Invocation.proceed(InterceptorContext.java:509)

2019-01-21 02:12:38 ERROR stderr:71 - at org.jboss.as.weld.interceptors.Jsr299BindingsInterceptor.doMethodInterception(Jsr299BindingsInterceptor.java:90)

… …

2019-01-21 02:12:39 ERROR stderr:71 - Caused by: org.hibernate.exception.SQLGrammarException: could not prepare statement

2019-01-21 02:12:39 ERROR stderr:71 - at org.hibernate.exception.internal.SQLStateConversionDelegate.convert(SQLStateConversionDelegate.java:106)

2019-01-21 02:12:39 ERROR stderr:71 - at org.hibernate.exception.internal.StandardSQLExceptionConverter.convert(StandardSQLExceptionConverter.java:42)

2019-01-21 02:12:39 ERROR stderr:71 - at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:111)

2019-01-21 02:12:39 ERROR stderr:71 - at org.hibernate.engine.jdbc.internal.StatementPreparerImpl$StatementPreparationTemplate.prepareStatement(StatementPreparerImpl.java:182)

2019-01-21 02:12:39 ERROR stderr:71 - at org.hibernate.engine.jdbc.internal.StatementPreparerImpl.prepareQueryStatement(StatementPreparerImpl.java:148)

2019-01-21 02:12:39 ERROR stderr:71 - … 158 more

While I’m able to execute Junit tests successfully for above User login() JPQL query, still I’m getting above error when calling login() method from Web App. I’m injecting SLSB EJB service in JSF and using Wildfly 11 App Server. What could be the reason causing error ?

2

While I’m able to execute Junit tests successfully

The Junit tests might auto-generate the DB schema using hbm2ddl.

still I’m getting above error when calling login() method from Web App.

And the production application might not use hbm2ddl, in which case you need to use automated migration scripts and a tool like FlywayDB.

3

Hi Vlad,
I’m not generating schema through JPA/Hibernate. I don’t think internally JUnit would be using hbm2ddl, unless it is specified in persistence.xml. I tried to execute login() method without JUnit and it’s working. The Database schema is already in place, using MySql. If I understand correctly hbm2ddl is used to validate or exports schema DDL to database using SessionFactory. Further I’m following JPA specifications, so using EntityManager. Here is persistence.xml :

        <persistence-unit name="WebAppPU">
		<provider>org.hibernate.jpa.HibernatePersistenceProvider</provider>
		<class>com.itsys.erp.server.dal.entities.City</class>
		<class>com.itsys.erp.server.dal.entities.Country</class>
		<class>com.itsys.erp.server.dal.entities.Customer</class>
		<class>com.itsys.erp.server.dal.entities.FpPackageMaterial</class>
		<class>com.itsys.erp.server.dal.entities.FpRawMaterial</class>
		<class>com.itsys.erp.server.dal.entities.Machine</class>
		<class>com.itsys.erp.server.dal.entities.Product</class>
		<class>com.itsys.erp.server.dal.entities.ProductType</class>
		<class>com.itsys.erp.server.dal.entities.ProductionLog</class>
		<class>com.itsys.erp.server.dal.entities.ProductionSchedule</class>
		<class>com.itsys.erp.server.dal.entities.Purchase</class>
		<class>com.itsys.erp.server.dal.entities.PurchaseDtl</class>
		<class>com.itsys.erp.server.dal.entities.PurchaseOrder</class>
                 ....

		<properties>
			<property name="javax.persistence.jdbc.driver" value="com.mysql.jdbc.Driver" />
			<property name="javax.persistence.jdbc.url" value="jdbc:mysql://localhost:3306/erp_mini" />
			<property name="javax.persistence.jdbc.user" value="root" />
			<property name="javax.persistence.jdbc.password" value="root" />
			<property name="hibernate.show_sql" value="true" />
		</properties>
	</persistence-unit>

Saw some solutions on on other sites for similar exceptions / problems, where some were using DB reserved keywords for table like user. Here I’m not able to trace any flaw, any idea what could be the reason? Pelase help.

4

Preciously, you said:

While I’m able to execute Junit tests successfully

Now, you are saying that:

So the problem is solved?

5

I mean calling login() method from main method without @Test annotation. : ). Problem still persist, not able to find trace any flaw / error in code / configuration.

6

Then you need to run a comparison debug and check why it works when testing and does not work otherwise.

Probably it’s related to the DB config parameters when testing vs production.

7

I got the production DB dump and am able to reproduce same error with WildFly 11 and MySql installed on my machine : Error is reproduced when login() method is called from JSF Managed bean.

org.hibernate.exception.SQLGrammarException: could not prepare statement.

Debugged the Web App and here are some observations with sysouts →

2019-01-22 19:21:09 INFO client:51 - JBoss EJB Client version 4.0.7.Final
2019-01-22 19:21:09 INFO stdout:71 - HOTSWAP AGENT: 19:21:09.225 INFO (org.hotswap.agent.plugin.wildfly.el.WildFlyELResolverPlugin) - Patched org.jboss.el.cache.BeanPropertiesCache

2019-01-22 19:21:09 INFO stdout:71 - HOTSWAP AGENT: 19:21:09.245 INFO (org.hotswap.agent.plugin.wildfly.el.WildFlyELResolverPlugin) - Patched org.jboss.el.cache.BeanPropertiesCache$SoftConcurrentHashMap

2019-01-22 19:21:32 INFO stdout:71 - in intialize() method of UserManagementServiceSlsb

Here UserManagementServiceSlsb stateless session bean has been created

2019-01-22 19:21:32 INFO stdout:71 - entityManager injected →

Next EntityManager is injected by Container.

2019-01-22 19:21:32 INFO stdout:71 - entitymanager → org.jboss.as.jpa.container.TransactionScopedEntityManager@413acfaf

2019-01-22 19:21:32 INFO stdout:71 - this.entityManager.getFlushMode()AUTO

Default flush mode : Auto

2019-01-22 19:21:32 INFO stdout:71 - daoFactory-> com.itsys.erp.server.dal.JPADAOFactory@3a1aee46

2019-01-22 19:21:32 INFO stdout:71 - in UserManagementServiceImpl(DAOFactory daoFactory) constructor

2019-01-22 19:21:33 INFO stdout:71 - executing query by calling getSingleResult()

JPQL executed by Query.getSingleResult() method;

2019-01-22 19:21:33 INFO stdout:71 - Hibernate: select userdetail0_.user_id as user_id1_21_, userdetail0_.last_activity_date as last_act2_21_, userdetail0_.online_status as online_s3_21_, userdetail0_.password_change_date as password4_21_, userdetail0_.password as password5_21_, userdetail0_.user_email_id as user_ema6_21_, userdetail0_.user_name as user_nam7_21_ from user_detail userdetail0_ where userdetail0_.user_name=?

Above sql query generated by Hibernate is okay and can be executed in MySql.
Next getting SQLGrammarException

19:21:33,662 WARN [org.hibernate.engine.jdbc.spi.SqlExceptionHelper] (default task-5) SQL Error: 42102, SQLState: 42S02
19:21:33,662 ERROR [org.hibernate.engine.jdbc.spi.SqlExceptionHelper] (default task-5) Table “USER_DETAIL” not found; SQL statement:

2019-01-22 19:21:33 ERROR stderr:71 - javax.persistence.PersistenceException: org.hibernate.exception.SQLGrammarException: could not prepare statement

The difference between running the Junit Tests and Web App is that EJBs are not in the picture when runing Junit Tests, instead am creating EntityManagerFactory and getting EntityManager from it. And JPQL is returning results.
Do I need to test EJB? I tried with Arquillian, but it’s a bit complex and time consuming to implement. But I don’t think testing EJBs would solve the problem. Any other framework do you know to test EJBs?
What other configuration should I check ?
Thanks.

8

If you cannot replicate it with this test case template with Hibernate alone, then it’s an application server issue, and you need to ask the question on the Wildfly forum.