title: Hibernate grammar exception using JPA with hsqldb
body: <p>I am trying to write a persistence test. I have a Maven project and am using Arquillian with an embedded glassfish container, Hibernate (as my JPA provider) and HSQLDB in memory as my store.</p>
  
  <p>When I run my test I get a grammar exception (I can post the full stack trace if necessary:</p>
  
  <pre><code>javax.persistence.PersistenceException: org.hibernate.exception.SQLGrammarException: Syntax error: Encountered "identity" at line 1, column 6.
  </code></pre>
  
  <p>The logs seem to show the table created properly:</p>
  
  <pre><code>Hibernate: 
      create table Game (
          id bigint generated by default as identity (start with 1),
          title varchar(50) not null,
          primary key (id)
      )
  Dumping old records...
  Hibernate: 
      delete 
      from
          Game
  Inserting records...
  Hibernate: 
      insert 
      into
          Game
          (id, title) 
      values
          (default, ?)
  Hibernate: 
      drop table Game if exists
  Hibernate: 
      drop table Member if exists
  </code></pre>
  
  <p>I've included what I think are the relevant files</p>
  
  <ul>
  <li>persistence.xml</li>
  <li>pom.xml</li>
  <li>Entity class</li>
  <li>Test class</li>
  </ul>
  
  <p><strong>persistence.xml:</strong></p>
  
  <pre><code>&lt;?xml version="1.0" encoding="UTF-8"?&gt;
  &lt;persistence version="2.0" xmlns="http://java.sun.com/xml/ns/persistence"     xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://java.sun.com/xml/ns/persistence http://java.sun.com/xml/ns/persistence/persistence_2_0.xsd"&gt;
    &lt;persistence-unit name="testPU" transaction-type="JTA"&gt;
      &lt;provider&gt;org.hibernate.ejb.HibernatePersistence&lt;/provider&gt;
      &lt;class&gt;Member&lt;/class&gt;
      &lt;class&gt;Game&lt;/class&gt;
      &lt;exclude-unlisted-classes&gt;true&lt;/exclude-unlisted-classes&gt;
      &lt;properties&gt;
  
           &lt;property name="javax.persistence.jdbc.driver" value="org.hsqldb.jdbcDriver"     /&gt;
           &lt;property name="javax.persistence.jdbc.url"     value="jdbc:hsqldb:mem:demodb" /&gt;
           &lt;property name="javax.persistence.jdbc.user" value="sa" /&gt;
           &lt;property name="javax.persistence.jdbc.password" value="" /&gt;
  
  
           &lt;property name="hibernate.dialect" value="org.hibernate.dialect.HSQLDialect"     /&gt;
           &lt;property name="hibernate.hbm2ddl.auto" value="create-drop" /&gt;
           &lt;property name="hibernate.show_sql" value="true" /&gt;
           &lt;property name="hibernate.format_sql" value="true" /&gt;
  
      &lt;/properties&gt;
  &lt;/persistence-unit&gt;
  &lt;/persistence&gt;
  </code></pre>
  
  <p><strong>pom.xml</strong></p>
  
  <pre><code>&lt;project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
       xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd"&gt;
      &lt;modelVersion&gt;4.0.0&lt;/modelVersion&gt;
  
      &lt;groupId&gt;....&lt;/groupId&gt;
      &lt;artifactId&gt;....&lt;/artifactId&gt;
      &lt;version&gt;1.0-SNAPSHOT&lt;/version&gt;
      &lt;packaging&gt;war&lt;/packaging&gt;
  
      &lt;name&gt;Web App&lt;/name&gt;
  
      &lt;properties&gt;
          &lt;endorsed.dir&gt;${project.build.directory}/endorsed&lt;/endorsed.dir&gt;
          &lt;project.build.sourceEncoding&gt;UTF-8&lt;/project.build.sourceEncoding&gt;
          &lt;netbeans.hint.deploy.server&gt;gfv3ee6&lt;/netbeans.hint.deploy.server&gt;
          &lt;arquillian.version&gt;1.0.0.Alpha2&lt;/arquillian.version&gt;
      &lt;/properties&gt;
  
      &lt;dependencies&gt;
  
          &lt;dependency&gt;
              &lt;groupId&gt;org.hsqldb&lt;/groupId&gt;
              &lt;artifactId&gt;hsqldb&lt;/artifactId&gt;
              &lt;version&gt;2.2.9&lt;/version&gt;
              &lt;scope&gt;test&lt;/scope&gt;
          &lt;/dependency&gt;
          &lt;dependency&gt;
              &lt;groupId&gt;junit&lt;/groupId&gt;
              &lt;artifactId&gt;junit&lt;/artifactId&gt;
              &lt;version&gt;4.10&lt;/version&gt;
              &lt;scope&gt;test&lt;/scope&gt;
          &lt;/dependency&gt;
  
          &lt;dependency&gt;
              &lt;groupId&gt;org.jboss.arquillian.junit&lt;/groupId&gt;
              &lt;artifactId&gt;arquillian-junit-container&lt;/artifactId&gt;
              &lt;scope&gt;test&lt;/scope&gt;
          &lt;/dependency&gt;
  
          &lt;dependency&gt;
              &lt;groupId&gt;org.hibernate&lt;/groupId&gt;
              &lt;artifactId&gt;hibernate-entitymanager&lt;/artifactId&gt;
              &lt;version&gt;4.1.9.Final&lt;/version&gt;
          &lt;/dependency&gt;
  
      &lt;/dependencies&gt;
  
      &lt;dependencyManagement&gt;
          &lt;dependencies&gt;
              &lt;dependency&gt;
                  &lt;groupId&gt;org.jboss.arquillian&lt;/groupId&gt;
                  &lt;artifactId&gt;arquillian-bom&lt;/artifactId&gt;
                  &lt;version&gt;1.0.3.Final&lt;/version&gt;
                  &lt;scope&gt;import&lt;/scope&gt;
                  &lt;type&gt;pom&lt;/type&gt;
              &lt;/dependency&gt;
          &lt;/dependencies&gt;
      &lt;/dependencyManagement&gt;
  
      &lt;profiles&gt;
          &lt;profile&gt;
              &lt;id&gt;arquillian-glassfish-embedded&lt;/id&gt;
              &lt;dependencies&gt;
                  &lt;dependency&gt;
                      &lt;groupId&gt;org.jboss.arquillian.container&lt;/groupId&gt;
                      &lt;artifactId&gt;arquillian-glassfish-embedded-3.1&lt;/artifactId&gt;
                      &lt;version&gt;1.0.0.CR3&lt;/version&gt;
                      &lt;scope&gt;test&lt;/scope&gt;
                  &lt;/dependency&gt;
                  &lt;dependency&gt;
                      &lt;groupId&gt;org.glassfish.main.extras&lt;/groupId&gt;
                      &lt;artifactId&gt;glassfish-embedded-all&lt;/artifactId&gt;
                      &lt;version&gt;3.1.2&lt;/version&gt;
                      &lt;scope&gt;provided&lt;/scope&gt;
                  &lt;/dependency&gt;
              &lt;/dependencies&gt;
          &lt;/profile&gt;
      &lt;/profiles&gt;
  
      &lt;build&gt;
          &lt;plugins&gt;
              &lt;plugin&gt;
                  &lt;artifactId&gt;maven-surefire-plugin&lt;/artifactId&gt;
                  &lt;version&gt;2.13&lt;/version&gt;
              &lt;/plugin&gt;
              &lt;plugin&gt;
                  &lt;groupId&gt;org.apache.maven.plugins&lt;/groupId&gt;
                  &lt;artifactId&gt;maven-compiler-plugin&lt;/artifactId&gt;
                  &lt;version&gt;2.3.2&lt;/version&gt;
                  &lt;configuration&gt;
                      &lt;source&gt;1.6&lt;/source&gt;
                      &lt;target&gt;1.6&lt;/target&gt;
                      &lt;compilerArguments&gt;
                          &lt;endorseddirs&gt;${endorsed.dir}&lt;/endorseddirs&gt;
                      &lt;/compilerArguments&gt;
                  &lt;/configuration&gt;
              &lt;/plugin&gt;
              &lt;plugin&gt;
                  &lt;groupId&gt;org.apache.maven.plugins&lt;/groupId&gt;
                  &lt;artifactId&gt;maven-jar-plugin&lt;/artifactId&gt;
                  &lt;version&gt;2.4&lt;/version&gt;
                  &lt;configuration&gt;
                      &lt;source&gt;1.6&lt;/source&gt;
                      &lt;target&gt;1.6&lt;/target&gt;
                      &lt;compilerArguments&gt;
                          &lt;endorseddirs&gt;${endorsed.dir}&lt;/endorseddirs&gt;
                      &lt;/compilerArguments&gt;
                  &lt;/configuration&gt;
              &lt;/plugin&gt;
              &lt;plugin&gt;
                  &lt;groupId&gt;org.apache.maven.plugins&lt;/groupId&gt;
                  &lt;artifactId&gt;maven-war-plugin&lt;/artifactId&gt;
                  &lt;version&gt;2.1&lt;/version&gt;
                  &lt;configuration&gt;
                      &lt;failOnMissingWebXml&gt;false&lt;/failOnMissingWebXml&gt;
                  &lt;/configuration&gt;
              &lt;/plugin&gt;
              &lt;plugin&gt;
                  &lt;groupId&gt;org.apache.maven.plugins&lt;/groupId&gt;
                  &lt;artifactId&gt;maven-dependency-plugin&lt;/artifactId&gt;
                  &lt;version&gt;2.1&lt;/version&gt;
                  &lt;executions&gt;
                      &lt;execution&gt;
                          &lt;phase&gt;validate&lt;/phase&gt;
                          &lt;goals&gt;
                              &lt;goal&gt;copy&lt;/goal&gt;
                          &lt;/goals&gt;
                          &lt;configuration&gt;
                              &lt;outputDirectory&gt;${endorsed.dir}&lt;/outputDirectory&gt;
                              &lt;silent&gt;true&lt;/silent&gt;
                              &lt;artifactItems&gt;
                                  &lt;artifactItem&gt;
                                      &lt;groupId&gt;javax&lt;/groupId&gt;
                                      &lt;artifactId&gt;javaee-endorsed-api&lt;/artifactId&gt;
                                      &lt;version&gt;6.0&lt;/version&gt;
                                      &lt;type&gt;jar&lt;/type&gt;
                                  &lt;/artifactItem&gt;
                              &lt;/artifactItems&gt;
                          &lt;/configuration&gt;
                      &lt;/execution&gt;
                  &lt;/executions&gt;
              &lt;/plugin&gt;
          &lt;/plugins&gt;
          &lt;finalName&gt;....&lt;/finalName&gt;
      &lt;/build&gt;
  
      &lt;repositories&gt;
          &lt;repository&gt;
              &lt;url&gt;http://download.java.net/maven/2/&lt;/url&gt;
              &lt;id&gt;hibernate-support&lt;/id&gt;
              &lt;layout&gt;default&lt;/layout&gt;
              &lt;name&gt;Repository for library Library[hibernate-support]&lt;/name&gt;
          &lt;/repository&gt;
          &lt;repository&gt;
              &lt;url&gt;http://ftp.ing.umu.se/mirror/eclipse/rt/eclipselink/maven.repo&lt;/url&gt;
              &lt;id&gt;eclipselink&lt;/id&gt;
              &lt;layout&gt;default&lt;/layout&gt;
              &lt;name&gt;Repository for library Library[eclipselink]&lt;/name&gt;
          &lt;/repository&gt;
      &lt;/repositories&gt;
  &lt;/project&gt;
  </code></pre>
  
  <p><strong>Entity</strong></p>
  
  <pre><code>//imports removed
  
  @Entity
  public class Game implements Serializable {
      @Id @GeneratedValue(strategy = GenerationType.AUTO)
      private Long id;
      private String title;
  
      public Game() {}
  
      public Game(String title) {
          this.title = title;
      }
  
  
      public Long getId() {
          return id;
      }
  
      public void setId(Long id) {
          this.id = id;
      }
  
      @NotNull
      @Size(min = 3, max = 50)
      public String getTitle() {
          return title;
      }
  
      public void setTitle(String title) {
          this.title = title;
      }
  
      @Override
      public String toString() {
          return "Game@" + hashCode() + "[id = " + id + "; title = " + title + "]";
      }
  }
  </code></pre>
  
  <p>*<em>Test class</em></p>
  
  <pre><code>//imports removed
  
  @RunWith(Arquillian.class)
  public class GamePersistenceTest {
  
      @Deployment
      public static Archive&lt;?&gt; createDeployment() {
          return ShrinkWrap.create(WebArchive.class, "test.war")
                  .addPackage(Game.class.getPackage())
                  .addAsResource("META-INF/persistence.xml")
                  .addAsWebInfResource(EmptyAsset.INSTANCE, "beans.xml");
      }
      private static final String[] GAME_TITLES = {
          "Super Mario Brothers",
          "Mario Kart",
          "F-Zero"
      };
      @PersistenceContext
      EntityManager em;
  
      @Inject
      UserTransaction utx;
  
      // tests go here
      @Test
      public void shouldFindAllGamesUsingJpqlQuery() throws Exception {
          // given
          String fetchingAllGamesInJpql = "select g from Game g order by g.id";
  
          // when
          System.out.println("Selecting (using JPQL)...");
          List&lt;Game&gt; games = em.createQuery(fetchingAllGamesInJpql, Game.class).getResultList();
  
          // then
          System.out.println("Found " + games.size() + " games (using JPQL):");
          assertContainsAllGames(games);
      }
  
      private static void assertContainsAllGames(Collection&lt;Game&gt; retrievedGames) {
          Assert.assertEquals(GAME_TITLES.length, retrievedGames.size());
          final Set&lt;String&gt; retrievedGameTitles = new HashSet&lt;String&gt;();
          for (Game game : retrievedGames) {
              System.out.println("* " + game);
              retrievedGameTitles.add(game.getTitle());
          }
          Assert.assertTrue(retrievedGameTitles.containsAll(Arrays.asList(GAME_TITLES)));
      }
  
      @Before
      public void preparePersistenceTest() throws Exception {
          clearData();
          insertData();
          startTransaction();
      }
  
      private void clearData() throws Exception {
          utx.begin();
          em.joinTransaction();
          System.out.println("Dumping old records...");
          em.createQuery("delete from Game").executeUpdate();
          utx.commit();
      }
  
      private void insertData() throws Exception {
          utx.begin();
          em.joinTransaction();
          System.out.println("Inserting records...");
          for (String title : GAME_TITLES) {
              Game game = new Game(title);
              em.persist(game);
          }
          utx.commit();
          // clear the persistence context (first-level cache)
          em.clear();
      }
  
      private void startTransaction() throws Exception {
          utx.begin();
          em.joinTransaction();
      }
  
      @After
      public void commitTransaction() throws Exception {
          utx.commit();
      }
  }
  </code></pre>