Spring hibernate: org.hibernate.exception.SQLGrammarException

Question

My DAO layer code is as follows:

package com.app.dao;
import javax.annotation.Resource;
import org.hibernate.Session;
import org.hibernate.SessionFactory;
import org.hibernate.Transaction;
import org.springframework.orm.hibernate3.HibernateTemplate;
import org.springframework.orm.hibernate3.support.HibernateDaoSupport;
import org.springframework.stereotype.Repository;
import com.app.pojo.InterviewerPojo;

@Repository("interviewer_details_dao")
public class InterviewerDetailsDaoImpl implements InterviewerDetailsDao {

    @Resource(name="sessionFactory")
    private SessionFactory factory;
    /*private HibernateTemplate template;
    public void setSessionFactory(){
        template = new HibernateTemplate(factory);
    }*/
    /*private HibernateTemplate template = new HibernateTemplate(sessionFactory);*/
    @Override
    public void saveInterviewDetails(InterviewerPojo interviewerPojo) {

/*      Session sess = factory.getCurrentSession();
        try{
            sess.getTransaction().wasCommitted();
        Transaction trans = sess.beginTransaction();
        System.out.println("a");
        sess.saveOrUpdate(interviewerPojo);
        sess.getTransaction().commit();}
        catch(Exception e){
            throw e;
        }*/
        System.out.println("b");
        factory.getCurrentSession().save(interviewerPojo);
    /*  System.out.println(interviewerPojo);
        template.saveOrUpdate(interviewerPojo);
        System.out.println("b");*/

        System.out.println("done");

    }

}

the same code as above was running if i am getting the values from database as follows sessionFactory.getCurrentSession().createQuery("select m from emp m).list; But as I writing another page to insert values then it is having problem *As you can see i used same concept, I populated MODEL(POJO) class by another MODEL(pojo) which i have taken from database by above query* and now debugging goes upto "template.saveOrUpdate" and throws exception as follows

org.hibernate.exception.SQLGrammarException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'value for hibernate_sequence' at line 1 at org.hibernate.exception.internal.SQLStateConverter.convert(SQLStateConverter.java:100) at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:125) at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:110) at org.hibernate.engine.jdbc.internal.proxy.AbstractStatementProxyHandler.continueInvocation(AbstractStatementProxyHandler.java:129) at org.hibernate.engine.jdbc.internal.proxy.AbstractProxyHandler.invoke(AbstractProxyHandler.java:81) at $Proxy24.executeQuery(Unknown Source) at org.hibernate.id.SequenceGenerator.generateHolder(SequenceGenerator.java:112) at org.hibernate.id.SequenceHiLoGenerator$1.getNextValue(SequenceHiLoGenerator.java:85) at org.hibernate.id.enhanced.OptimizerFactory$LegacyHiLoAlgorithmOptimizer.generate(OptimizerFactory.java:350) at org.hibernate.id.SequenceHiLoGenerator.generate(SequenceHiLoGenerator.java:82) at org.hibernate.event.internal.AbstractSaveEventListener.saveWithGeneratedId(AbstractSaveEventListener.java:120) at org.hibernate.event.internal.DefaultSaveOrUpdateEventListener.saveWithGeneratedOrRequestedId(DefaultSaveOrUpdateEventListener.java:204) at org.hibernate.event.internal.DefaultSaveEventListener.saveWithGeneratedOrRequestedId(DefaultSaveEventListener.java:55) at org.hibernate.event.internal.DefaultSaveOrUpdateEventListener.entityIsTransient(DefaultSaveOrUpdateEventListener.java:189) at org.hibernate.event.internal.DefaultSaveEventListener.performSaveOrUpdate(DefaultSaveEventListener.java:49) at org.hibernate.event.internal.DefaultSaveOrUpdateEventListener.onSaveOrUpdate(DefaultSaveOrUpdateEventListener.java:90) at org.hibernate.internal.SessionImpl.fireSave(SessionImpl.java:642) at org.hibernate.internal.SessionImpl.save(SessionImpl.java:635) at org.hibernate.internal.SessionImpl.save(SessionImpl.java:631) at com.app.dao.InterviewerDetailsDaoImpl.saveInterviewDetails(InterviewerDetailsDaoImpl.java:40) at com.app.service.SaveInterviewerDetailsServiceImpl.saveDetails(SaveInterviewerDetailsServiceImpl.java:25) 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.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:318) at org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:183) at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:150) at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:110) at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:172) at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:202) at $Proxy19.saveDetails(Unknown Source) at com.app.controllers.EmployeePromoteController.validateDetails(EmployeePromoteController.java:67) 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.invoke(InvocableHandlerMethod.java:213) at org.springframework.web.method.support.InvocableHandlerMethod.invokeForRequest(InvocableHandlerMethod.java:126) at org.springframework.web.servlet.mvc.method.annotation.ServletInvocableHandlerMethod.invokeAndHandle(ServletInvocableHandlerMethod.java:96) at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.invokeHandlerMethod(RequestMappingHandlerAdapter.java:617) at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.handleInternal(RequestMappingHandlerAdapter.java:578) at org.springframework.web.servlet.mvc.method.AbstractHandlerMethodAdapter.handle(AbstractHandlerMethodAdapter.java:80) at org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:923) at org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:852) at org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:882) at org.springframework.web.servlet.FrameworkServlet.doPost(FrameworkServlet.java:789) at javax.servlet.http.HttpServlet.service(HttpServlet.java:647) at javax.servlet.http.HttpServlet.service(HttpServlet.java:728) at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:305) at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:210) at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:222) at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:123) at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:472) at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:171) at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:99) at org.apache.catalina.valves.AccessLogValve.invoke(AccessLogValve.java:931) at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:118) at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:407) at org.apache.coyote.http11.AbstractHttp11Processor.process(AbstractHttp11Processor.java:1004) at org.apache.coyote.AbstractProtocol$AbstractConnectionHandler.process(AbstractProtocol.java:589) at org.apache.tomcat.util.net.JIoEndpoint$SocketProcessor.run(JIoEndpoint.java:312) at java.util.concurrent.ThreadPoolExecutor.runWorker(Unknown Source) at java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source) at java.lang.Thread.run(Unknown Source) Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'value for hibernate_sequence' at line 1 at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method) at sun.reflect.NativeConstructorAccessorImpl.newInstance(Unknown Source) at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(Unknown Source) at java.lang.reflect.Constructor.newInstance(Unknown Source) at com.mysql.jdbc.Util.handleNewInstance(Util.java:407) at com.mysql.jdbc.Util.getInstance(Util.java:382) at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1052) at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3593) at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3525) at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1986) at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2140) at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2626) at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:2111) at com.mysql.jdbc.PreparedStatement.executeQuery(PreparedStatement.java:2273) at org.apache.commons.dbcp.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:96) at org.apache.commons.dbcp.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:96) 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.hibernate.engine.jdbc.internal.proxy.AbstractStatementProxyHandler.continueInvocation(AbstractStatementProxyHandler.java:122) ... 61 more

---

POJO source code:

package com.app.pojo; 
import java.io.Serializable; 
import javax.persistence.*; 

@SuppressWarnings("serial") 
@Entity @Table(name="interviewer") 
public class InterviewerPojo implements Serializable {
    private int eid;

    private int vid; 

    @Id @GeneratedValue(strategy=GenerationType.SEQUENCE) 
    private int i_eid; 

    private String i_name; 

    private String password; 

    public int getEid() { 
        return eid; 
    } 

    public void setEid(int eid) { 
        this.eid = eid; 
    } 
}

Hibernate config:

<property name="hibernateProperties"> 
    <props>
        <prop key="hibernate.dialect">org.hibernate.dialect.HSQLDialect</prop>
        <prop key="hibernate.formatsql">true</prop>
        <prop key="hibernate.showsql">true</prop>
        <prop key="hibernate.hbm2ddl.auto">validate</prop>
    </props> 
</property>   

Answer 1

In your error log, it showed that you're using MySQL. However, you configured Hibernate dialect was HSQLDialect.

org.hibernate.dialect.HSQLDialect

You should remove this dialect property, Hibernate can automatically detect the right dialect through JDBC driver. Or you modify to the correct dialect on your own.