Tag Archives: could not execute statement

[Solved]org.hibernate.exception.SQLGrammarException: could not execute statement


“org.hibernate.exception.SQLGrammarException: could not execute statement” this exception generally occurred when there is some issue with generated query grammar or syntax. Actual reason of issue you will in exception stacktrace cause.

Problem

In this case issue is “com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Table ‘test.stock’ doesn’t exist”. This issue is because of test.stock table is not find out because here input mapping xml file having catalog as “test” that is schema or database name while in hibernate.cfg.xml mentioned schema name as facingissuesonitdb.

Input Stock.hbm.xml


<hibernate-mapping>
    <class name="com.facingissuesonit.model.Stock" table="stock" catalog="test">
    <id name="stockId" type="java.lang.Integer">
       <column name="STOCK_ID" />
       <generator class="identity" />
    </id>
    <property name="stockCode" type="string">
           <column name="STOCK_CODE" length="10" not-null="true"     unique="true" />
    </property>
    <property name="stockName" type="string">
            <column name="STOCK_NAME" length="20" not-null="true" unique="true" />
    </property>
</class>
</hibernate-mapping>

Exception StackTrace


ERROR: Table 'test.stock' doesn't exist
Exception in thread "main" org.hibernate.exception.SQLGrammarException: could not execute statement
    at org.hibernate.exception.internal.SQLExceptionTypeDelegate.convert(SQLExceptionTypeDelegate.java:63)
    at org.hibernate.exception.internal.StandardSQLExceptionConverter.convert(StandardSQLExceptionConverter.java:42)
    at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:111)
    at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:97)
    at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.executeUpdate(ResultSetReturnImpl.java:208)
    at org.hibernate.dialect.identity.GetGeneratedKeysDelegate.executeAndExtract(GetGeneratedKeysDelegate.java:57)
    at org.hibernate.id.insert.AbstractReturningDelegate.performInsert(AbstractReturningDelegate.java:42)
    at org.hibernate.persister.entity.AbstractEntityPersister.insert(AbstractEntityPersister.java:2909)
    at org.hibernate.persister.entity.AbstractEntityPersister.insert(AbstractEntityPersister.java:3480)
    at org.hibernate.action.internal.EntityIdentityInsertAction.execute(EntityIdentityInsertAction.java:81)
    at org.hibernate.engine.spi.ActionQueue.execute(ActionQueue.java:626)
    at org.hibernate.engine.spi.ActionQueue.addResolvedEntityInsertAction(ActionQueue.java:280)
    at org.hibernate.engine.spi.ActionQueue.addInsertAction(ActionQueue.java:261)
    at org.hibernate.engine.spi.ActionQueue.addAction(ActionQueue.java:306)
    at org.hibernate.event.internal.AbstractSaveEventListener.addInsertAction(AbstractSaveEventListener.java:318)
    at org.hibernate.event.internal.AbstractSaveEventListener.performSaveOrReplicate(AbstractSaveEventListener.java:275)
    at org.hibernate.event.internal.AbstractSaveEventListener.performSave(AbstractSaveEventListener.java:182)
    at org.hibernate.event.internal.AbstractSaveEventListener.saveWithGeneratedId(AbstractSaveEventListener.java:113)
    at org.hibernate.event.internal.DefaultSaveOrUpdateEventListener.saveWithGeneratedOrRequestedId(DefaultSaveOrUpdateEventListener.java:192)
    at org.hibernate.event.internal.DefaultSaveEventListener.saveWithGeneratedOrRequestedId(DefaultSaveEventListener.java:38)
    at org.hibernate.event.internal.DefaultSaveOrUpdateEventListener.entityIsTransient(DefaultSaveOrUpdateEventListener.java:177)
    at org.hibernate.event.internal.DefaultSaveEventListener.performSaveOrUpdate(DefaultSaveEventListener.java:32)
    at org.hibernate.event.internal.DefaultSaveOrUpdateEventListener.onSaveOrUpdate(DefaultSaveOrUpdateEventListener.java:73)
    at org.hibernate.internal.SessionImpl.fireSave(SessionImpl.java:689)
    at org.hibernate.internal.SessionImpl.save(SessionImpl.java:681)
    at org.hibernate.internal.SessionImpl.save(SessionImpl.java:676)
    at com.facingissuesonit.App.main(App.java:29)
Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Table 'test.stock' doesn't exist
    at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
    at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62)
    at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
    at java.lang.reflect.Constructor.newInstance(Constructor.java:422)
    at com.mysql.jdbc.Util.handleNewInstance(Util.java:406)
    at com.mysql.jdbc.Util.getInstance(Util.java:381)
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1030)
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:956)
    at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3558)
    at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3490)
    at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1959)
    at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2109)
    at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2643)
    at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:2077)
    at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2362)
    at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2280)
    at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2265)
    at com.mchange.v2.c3p0.impl.NewProxyPreparedStatement.executeUpdate(NewProxyPreparedStatement.java:384)
    at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.executeUpdate(ResultSetReturnImpl.java:205)
    ... 22 more

Solutions

  • Solution 1: Replace above xml attribute catalog value from “test” to “facingissuesonitbd” as used same in hibernate.cfg.xml.
  • Solution 2: If your application is using only one database/schema then recommendation is to mention schema name in hibernate.cfg.xml so that in future if your schema name get change only configuration file need to update not all mapping files.
    
    <property name="hibernate.default_schema">facingissuesonitdb</property>
    

[Solved] org.springframework.dao. DataIntegrityViolationException: could not execute statement


Hibernate throws ConstraintViolationException, when tries to insert a row or update particular column or delete records that violate underlying database integrity constraints.
Below is some common situation where ConstraintViolationException occurs:

  • Insert a record with primary key as null.
  • Insert/ Update primary key value is not unique.
  • Insert data value size is more than predefine column size.
  • The inserted record data type is not compatible with predefined column type.

To see a complete list of Database Integrity Constraints follow this link :

Database Integrity Constraints List

ConstraintViolationException Example

Here below exception is occurring because inserting value of primary key as null.

@Entity
public class Publication {
	@Id
	@GeneratedValue
	protected Long id;

	@NotNull
	@Size(max = 100)
	@Column(unique = true)
	private String title;

	private int year;
	//getter and setter methods
	}

Exception stacktrace


org.springframework.dao.DataIntegrityViolationException: could not execute statement; SQL [n/a]; 
constraint [\"PUBLIC.UK_C5HPK0II0AH4H4EAAGM8HQUO0_INDEX_C ON PUBLIC.PUBLICATIONS(TITLE) VALUES 2\"; 
SQL statement:\ninsert into publications (id, title, type, year) values (null, ?, ?, ?) [23505-199]]; 
nested exception is org.hibernate.exception.ConstraintViolationException: could not execute statement
    at org.springframework.orm.jpa.vendor.HibernateJpaDialect.convertHibernateAccessException(HibernateJpaDialect.java:296)
    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:185)
    at org.springframework.data.jpa.repository.support.CrudMethodMetadataPostProcessor$CrudMethodMetadataPopulatingMethodInterceptor.invoke(CrudMethodMetadataPostProcessor.java:138)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:185)
    at org.springframework.aop.interceptor.ExposeInvocationInterceptor.invoke(ExposeInvocationInterceptor.java:92)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:185)
    at org.springframework.data.repository.core.support.SurroundingTransactionDetectorMethodInterceptor.invoke(SurroundingTransactionDetectorMethodInterceptor.java:61)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:185)
    at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:212)
    at com.sun.proxy.$Proxy121.save(Unknown Source)
    at com.pub.PublicationServices.controller.AuthorsCrudController.createAuthor(AuthorsCrudController.java:69)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
    at java.lang.reflect.Method.invoke(Unknown Source)
    at org.springframework.web.method.support.InvocableHandlerMethod.doInvoke(InvocableHandlerMethod.java:190)
    at org.springframework.web.method.support.InvocableHandlerMethod.invokeForRequest(InvocableHandlerMethod.java:138)
    at org.springframework.web.servlet.mvc.method.annotation.ServletInvocableHandlerMethod.invokeAndHandle(ServletInvocableHandlerMethod.java:104)
    at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.invokeHandlerMethod(RequestMappingHandlerAdapter.java:892)
    at org.springframework.web.servlet.mvc.

Solutions

As primary key rule states that columns set as primary key not allowed Null value and only contained unique values so that row can uniquely identified base don primary key.

To resolve this issue pass primary key column (id) value as unique and not NULL.

You would like to see

Follow below link to see more Hibernate and JPA issues solutions.