Getting the SQLGrammarException when trying to insert a record in Hibernate

Question

I am getting the following exception -
org.hibernate.exception.SQLGrammarException: could not insert: [Employee]
Here are the details:

hibernate.cfg.xml

    <hibernate-configuration>
    <session-factory>
    <property name="hibernate.dialect">org.hibernate.dialect.SQLServerDialect</property>
            <property name="hibernate.connection.driver_class">sun.jdbc.odbc.JdbcOdbcDriver</property>
            <property name="hibernate.connection.url">jdbc:odbc:Driver={Microsoft Access Driver (*.mdb, *.accdb)};DBQ=D:/Hibernate.accdb</property>
            <property name="connection.username"></property>
            <property name="connection.password"></property>
            <property name="connection.pool_size">1</property>
            <property name="current_session_context_class">thread</property>
            <property name="cache.provider_class">org.hibernate.cache.NoCacheProvider</property>
            <property name="show_sql">true</property>
            <property name="hbm2ddl.auto">update</property>
            <mapping resource="Employee.hbm.xml" />
    </session-factory>
    </hibernate-conf

Employee.hbm.xml

<hibernate-mapping>
   <class name="Employee" table="EMPLOYEE">
      <meta attribute="class-description">
         This class contains the employee detail. 
      </meta>
      <id name="id" type="int" column="id">
         <generator class="native"/>
      </id>
      <property name="firstName" column="first_name" type="string"/>
      <property name="lastName" column="last_name" type="string"/>
      <property name="salary" column="salary" type="int"/>
   </class>
</hibernate-mapping>

Employee.java

public class Employee {
   private int id;
   private String firstName; 
   private String lastName;   
   private int salary;  

   public Employee() {}
   public Employee(String fname, String lname, int salary) {
      this.firstName = fname;
      this.lastName = lname;
      this.salary = salary;
   }
   public int getId() {
      return id;
   }
   public void setId( int id ) {
      this.id = id;
   }
   public String getFirstName() {
      return firstName;
   }
   public void setFirstName( String first_name ) {
      this.firstName = first_name;
   }
   public String getLastName() {
      return lastName;
   }
   public void setLastName( String last_name ) {
      this.lastName = last_name;
   }
   public int getSalary() {
      return salary;
   }
   public void setSalary( int salary ) {
      this.salary = salary;
   }
}

ManageEmployee.java

import java.util.List; 
import java.util.Date;
import java.util.Iterator; 

import org.hibernate.HibernateException; 
import org.hibernate.Session; 
import org.hibernate.Transaction;
import org.hibernate.SessionFactory;
import org.hibernate.cfg.Configuration;

public class ManageEmployee {
   private static SessionFactory factory; 
   public static void main(String[] args) {
      try{
         factory = new Configuration().configure().buildSessionFactory();
      }catch (Throwable ex) { 
         System.err.println("Failed to create sessionFactory object." + ex);
         throw new ExceptionInInitializerError(ex); 
      }
      ManageEmployee ME = new ManageEmployee();

      /* Add few employee records in database */
      Integer empID1 = ME.addEmployee("Zara", "Ali", 1000);
      Integer empID2 = ME.addEmployee("Daisy", "Das", 5000);
      Integer empID3 = ME.addEmployee("John", "Paul", 10000);
}
 public Integer addEmployee(String fname, String lname, int salary){
      Session session = factory.openSession();
      Transaction tx = null;
      Integer employeeID = null;
      try{
         tx = session.beginTransaction();
         Employee employee = new Employee(fname, lname, salary);
         employeeID = (Integer) session.save(employee); 
         tx.commit();
      }catch (HibernateException e) {
         if (tx!=null) tx.rollback();
         e.printStackTrace(); 
      }finally {
         session.close(); 
      }
      return employeeID;
   }

}

Here is the full exception:

Output in console: Hibernate: insert into EMPLOYEE (first_name, last_name, salary) values (?, ?, ?) select scope_identity() org.hibernate.exception.SQLGrammarException: could not insert: [Employee] Hibernate: insert into EMPLOYEE (first_name, last_name, salary) values (?, ?, ?) select scope_identity() at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:92) at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:66) at org.hibernate.id.insert.AbstractReturningDelegate.performInsert(AbstractReturningDelegate.java:64) at org.hibernate.persister.entity.AbstractEntityPersister.insert(AbstractEntityPersister.java:2327) at org.hibernate.persister.entity.AbstractEntityPersister.insert(AbstractEntityPersister.java:2834) at org.hibernate.action.EntityIdentityInsertAction.execute(EntityIdentityInsertAction.java:71) at org.hibernate.engine.ActionQueue.execute(ActionQueue.java:273) at org.hibernate.event.def.AbstractSaveEventListener.performSaveOrReplicate(AbstractSaveEventListener.java:320) at org.hibernate.event.def.AbstractSaveEventListener.performSave(AbstractSaveEventListener.java:203) at org.hibernate.event.def.AbstractSaveEventListener.saveWithGeneratedId(AbstractSaveEventListener.java:129) at org.hibernate.event.def.DefaultSaveOrUpdateEventListener.saveWithGeneratedOrRequestedId(DefaultSaveOrUpdateEventListener.java:210) at org.hibernate.event.def.DefaultSaveEventListener.saveWithGeneratedOrRequestedId(DefaultSaveEventListener.java:56) at org.hibernate.event.def.DefaultSaveOrUpdateEventListener.entityIsTransient(DefaultSaveOrUpdateEventListener.java:195) at org.hibernate.event.def.DefaultSaveEventListener.performSaveOrUpdate(DefaultSaveEventListener.java:50) at org.hibernate.event.def.DefaultSaveOrUpdateEventListener.onSaveOrUpdate(DefaultSaveOrUpdateEventListener.java:93) at org.hibernate.impl.SessionImpl.fireSave(SessionImpl.java:713) at org.hibernate.impl.SessionImpl.save(SessionImpl.java:701) at org.hibernate.impl.SessionImpl.save(SessionImpl.java:697) at ManageEmployee.addEmployee(ManageEmployee.java:47) at ManageEmployee.main(ManageEmployee.java:23) Caused by: java.sql.SQLException: [Microsoft][ODBC Microsoft Access Driver] Missing semicolon (;) at end of SQL statement. at sun.jdbc.odbc.JdbcOdbc.createSQLException(Unknown Source) at sun.jdbc.odbc.JdbcOdbc.standardError(Unknown Source) at sun.jdbc.odbc.JdbcOdbc.SQLExecute(Unknown Source) at sun.jdbc.odbc.JdbcOdbcPreparedStatement.execute(Unknown Source) at org.hibernate.id.IdentityGenerator$InsertSelectDelegate.executeAndExtract(IdentityGenerator.java:138) at org.hibernate.id.insert.AbstractReturningDelegate.performInsert(AbstractReturningDelegate.java:57) ... 17 more org.hibernate.exception.SQLGrammarException: could not insert: [Employee] at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:92) at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:66) at org.hibernate.id.insert.AbstractReturningDelegate.performInsert(AbstractReturningDelegate.java:64) at org.hibernate.persister.entity.AbstractEntityPersister.insert(AbstractEntityPersister.java:2327) at org.hibernate.persister.entity.AbstractEntityPersister.insert(AbstractEntityPersister.java:2834) at org.hibernate.action.EntityIdentityInsertAction.execute(EntityIdentityInsertAction.java:71) at org.hibernate.engine.ActionQueue.execute(ActionQueue.java:273) at org.hibernate.event.def.AbstractSaveEventListener.performSaveOrReplicate(AbstractSaveEventListener.java:320) at org.hibernate.event.def.AbstractSaveEventListener.performSave(AbstractSaveEventListener.java:203) at org.hibernate.event.def.AbstractSaveEventListener.saveWithGeneratedId(AbstractSaveEventListener.java:129) at org.hibernate.event.def.DefaultSaveOrUpdateEventListener.saveWithGeneratedOrRequestedId(DefaultSaveOrUpdateEventListener.java:210) at org.hibernate.event.def.DefaultSaveEventListener.saveWithGeneratedOrRequestedId(DefaultSaveEventListener.java:56) at org.hibernate.event.def.DefaultSaveOrUpdateEventListener.entityIsTransient(DefaultSaveOrUpdateEventListener.java:195) at org.hibernate.event.def.DefaultSaveEventListener.performSaveOrUpdate(DefaultSaveEventListener.java:50) at org.hibernate.event.def.DefaultSaveOrUpdateEventListener.onSaveOrUpdate(DefaultSaveOrUpdateEventListener.java:93) at org.hibernate.impl.SessionImpl.fireSave(SessionImpl.java:713) at org.hibernate.impl.SessionImpl.save(SessionImpl.java:701) at org.hibernate.impl.SessionImpl.save(SessionImpl.java:697) at ManageEmployee.addEmployee(ManageEmployee.java:47) at ManageEmployee.main(ManageEmployee.java:24) Caused by: java.sql.SQLException: [Microsoft][ODBC Microsoft Access Driver] Missing semicolon (;) at end of SQL statement. at sun.jdbc.odbc.JdbcOdbc.createSQLException(Unknown Source) at sun.jdbc.odbc.JdbcOdbc.standardError(Unknown Source) at sun.jdbc.odbc.JdbcOdbc.SQLExecute(Unknown Source) at sun.jdbc.odbc.JdbcOdbcPreparedStatement.execute(Unknown Source) at org.hibernate.id.IdentityGenerator$InsertSelectDelegate.executeAndExtract(IdentityGenerator.java:138) at org.hibernate.id.insert.AbstractReturningDelegate.performInsert(AbstractReturningDelegate.java:57) ... 17 more org.hibernate.exception.SQLGrammarException: could not insert: [Employee] at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:92) at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:66) at org.hibernate.id.insert.AbstractReturningDelegate.performInsert(AbstractReturningDelegate.java:64) at org.hibernate.persister.entity.AbstractEntityPersister.insert(AbstractEntityPersister.java:2327) at org.hibernate.persister.entity.AbstractEntityPersister.insert(AbstractEntityPersister.java:2834) at org.hibernate.action.EntityIdentityInsertAction.execute(EntityIdentityInsertAction.java:71) at org.hibernate.engine.ActionQueue.execute(ActionQueue.java:273) at org.hibernate.event.def.AbstractSaveEventListener.performSaveOrReplicate(AbstractSaveEventListener.java:320) at org.hibernate.event.def.AbstractSaveEventListener.performSave(AbstractSaveEventListener.java:203) at org.hibernate.event.def.AbstractSaveEventListener.saveWithGeneratedId(AbstractSaveEventListener.java:129) at org.hibernate.event.def.DefaultSaveOrUpdateEventListener.saveWithGeneratedOrRequestedId(DefaultSaveOrUpdateEventListener.java:210) at org.hibernate.event.def.DefaultSaveEventListener.saveWithGeneratedOrRequestedId(DefaultSaveEventListener.java:56) at org.hibernate.event.def.DefaultSaveOrUpdateEventListener.entityIsTransient(DefaultSaveOrUpdateEventListener.java:195) at org.hibernate.event.def.DefaultSaveEventListener.performSaveOrUpdate(DefaultSaveEventListener.java:50) at org.hibernate.event.def.DefaultSaveOrUpdateEventListener.onSaveOrUpdate(DefaultSaveOrUpdateEventListener.java:93) at org.hibernate.impl.SessionImpl.fireSave(SessionImpl.java:713) at org.hibernate.impl.SessionImpl.save(SessionImpl.java:701) at org.hibernate.impl.SessionImpl.save(SessionImpl.java:697) at ManageEmployee.addEmployee(ManageEmployee.java:47) at ManageEmployee.main(ManageEmployee.java:25) Caused by: java.sql.SQLException: [Microsoft][ODBC Microsoft Access Driver] Missing semicolon (;) at end of SQL statement. at sun.jdbc.odbc.JdbcOdbc.createSQLException(Unknown Source) at sun.jdbc.odbc.JdbcOdbc.standardError(Unknown Source) at sun.jdbc.odbc.JdbcOdbc.SQLExecute(Unknown Source) at sun.jdbc.odbc.JdbcOdbcPreparedStatement.execute(Unknown Source) at org.hibernate.id.IdentityGenerator$InsertSelectDelegate.executeAndExtract(IdentityGenerator.java:138) at org.hibernate.id.insert.AbstractReturningDelegate.performInsert(AbstractReturningDelegate.java:57) ... 17 more Hibernate: insert into EMPLOYEE (first_name, last_name, salary) values (?, ?, ?) select scope_identity()

Please help to resolve the exceptions. Thanks in advance!

Answer 1

The MS Access dialect does not exists in the Hibernate library. This causes that Hibernate is not capable to detect the identity column and act as required for MS Access.

org.hibernate.exception.SQLGrammarException: could not insert: [Employee] Hibernate: insert into EMPLOYEE (first_name, last_name, salary) values (?, ?, ?) select scope_identity()

select scope_identity() is a TRANSACT-SQL instance not supported by MS Access.

You could do one of this options:

• Don't use autogenerated id columns or no SQL standard fields.
• Use a supported hibernate database insted of MS Access Hibernate doc.
• Create your own hibernate dialect to support MS Access or use a thirdparty one to provide the special characteristics of MS Access.

Related to How can I use hibernate with MS Access?