HSQLDB and Hibernate: Unit Test raises org.hsqldb.HsqlException: user lacks privilege or object not found

Question

I'm trying to get going with Hibernate and HSQLDB. I tried to adapt a tutorial to more current versions, so there might be the problem. This happens when I run a unit test with JUnit.

I am using HSQLDB 2.3.2 with Hibernate (JPA) 4.3.5.

The error i get is this:

16:40:16,802 DEBUG SQL:109 - select device0_.DEVICE_ID as DEVICE_I1_0_, device0_.DEVICE_FIRMWARE_VERSION as DEVICE_F2_0_, device0_.DEVICE_IMAGE as DEVICE_I3_0_ from DEVICES device0_ limit ? 16:40:16,802 DEBUG SQL:109 - select device0_.DEVICE_ID as DEVICE_I1_0_, device0_.DEVICE_FIRMWARE_VERSION as DEVICE_F2_0_, device0_.DEVICE_IMAGE as DEVICE_I3_0_ from DEVICES device0_ limit ? 16:40:16,802 DEBUG SQL:109 - select device0_.DEVICE_ID as DEVICE_I1_0_, device0_.DEVICE_FIRMWARE_VERSION as DEVICE_F2_0_, device0_.DEVICE_IMAGE as DEVICE_I3_0_ from DEVICES device0_ limit ? 16:40:16,803 WARN SqlExceptionHelper:144 - SQL Error: -5501, SQLState: 42501 16:40:16,803 WARN SqlExceptionHelper:144 - SQL Error: -5501, SQLState: 42501 16:40:16,803 ERROR SqlExceptionHelper:146 - user lacks privilege or object not found: DEVICES 16:40:16,803 ERROR SqlExceptionHelper:146 - user lacks privilege or object not found: DEVICES 16:40:16,804 ERROR gwt-log:81 - e: javax.persistence.PersistenceException: org.hibernate.exception.SQLGrammarException: could not prepare statement javax.persistence.PersistenceException: org.hibernate.exception.SQLGrammarException: could not prepare statement ... Caused by: org.hibernate.exception.SQLGrammarException: could not prepare statement ... Caused by: java.sql.SQLSyntaxErrorException: user lacks privilege or object not found: DEVICES ... Caused by: org.hsqldb.HsqlException: user lacks privilege or object not found: DEVICES ...

My persistence.xml looks like this:

<persistence 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"
             version="2.0">

  <persistence-unit name="testproject" transaction-type="RESOURCE_LOCAL">
    <provider>org.hibernate.jpa.HibernatePersistenceProvider</provider>


    <class>testproject.server.domain.Device</class>

    <exclude-unlisted-classes/>

    <properties>

      <!-- Database connection settings -->
      <property name="hibernate.connection.driver_class" value="org.hsqldb.jdbcDriver" />
      <property name="hibernate.connection.url" value="jdbc:hsqldb:file:/db/testdb" />
      <property name="hibernate.connection.username" value="sa" />
      <property name="hibernate.connection.password" value="" />

      <!-- JDBC connection pool (use the built-in) -->
      <property name="connection.pool_size" value="1" />

      <!-- SQL dialect -->
      <property name="hibernate.dialect" value="org.hibernate.dialect.HSQLDialect" />

      <!-- Enable Hibernate's automatic session context management -->
      <property name="current_session_context_class" value="thread" />

      <!-- Disable the second-level cache  -->
      <property name="cache.provider_class" value="org.hibernate.cache.NoCacheProvider" />

      <!-- Echo all executed SQL to stdout -->
      <!-- <property name="hibernate.show_sql" value="true" /> -->
      <!-- <property name="hibernate.format_sql" value="true" /> -->

      <!-- Drop and re-create the database schema on startup -->
      <!-- This property should not be set in PRODUCTION environments -->
      <!-- <property name="hibernate.hbm2ddl.auto" value="create" /> -->

      <property name="hibernate.c3p0.min_size" value="5" />
      <property name="hibernate.c3p0.max_size" value="20" />
      <property name="hibernate.c3p0.timeout" value="300" />
      <property name="hibernate.c3p0.max_statements" value="50" />
      <property name="hibernate.c3p0.idle_test_period" value="3000" />
    </properties>

  </persistence-unit>
</persistence> 

My Device.java looks like this:

package testproject.server.domain;

import java.io.Serializable;

import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;
import javax.persistence.Table;

import testproject.shared.dto.DeviceDto;

@Entity
@Table(name = "DEVICES")
public class Device implements Serializable {

    private static final long serialVersionUID = 8161681740825989132L;

    @Id
    @Column(name = "DEVICE_ID")
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    protected Long deviceId;

    @Column(name = "DEVICE_IMAGE")
    protected String image;

    @Column(name = "DEVICE_FIRMWARE_VERSION")
    protected String firmwareVersion;

    public Device() {
    }

    public Device(Long deviceId) {
        setDeviceId(deviceId);
    }

    public Device(DeviceDto device) {
        setDeviceId(device.getDeviceId());
        setImage(device.getImage());
        setFirmwareVersion(device.getFirmwareVersion());
    }

    public Long getDeviceId() {
        return deviceId;
    }

    public void setDeviceId(Long deviceId) {
        this.deviceId = deviceId;
    }

    public String getImage() {
        return image;
    }

    public void setImage(String image) {
        this.image = image;
    }

    public String getFirmwareVersion() {
        return firmwareVersion;
    }

    public void setFirmwareVersion(String firmwareVersion) {
        this.firmwareVersion = firmwareVersion;
    }

    @Override
    public String toString() {
        StringBuilder sb = new StringBuilder();
        sb.append("Device Id: ").append(getDeviceId()).append(", ");
        sb.append("Image: ").append(getImage()).append(", ");
        sb.append("Firmware Version: ").append(getFirmwareVersion());

        return sb.toString();
    }

    //TODO: Do we need that?
    @Override
    public int hashCode() {
        final int prime = 31;
        int result = 1;
        result = prime * result
                + ((deviceId == null) ? 0 : deviceId.hashCode());
        return result;
    }

    @Override
    public boolean equals(Object obj) {
        if (this == obj)
            return true;
        if (obj == null)
            return false;
        if (!(obj instanceof Device))
            return false;
        Device other = (Device) obj;
        if (deviceId == null) {
            if (other.deviceId != null)
                return false;
        } else if (!deviceId.equals(other.deviceId))
            return false;
        return true;
    }
}

and my Dao is like this:

package testproject.server.dao;

import java.util.List;

import javax.persistence.EntityManager;
import javax.persistence.EntityManagerFactory;
import javax.persistence.EntityTransaction;
import javax.persistence.Persistence;
import javax.persistence.TypedQuery;

import testproject.server.domain.Device;

public class DeviceDao extends BaseDao {

      private static final EntityManagerFactory entityManagerFactory =
          Persistence.createEntityManagerFactory("testproject");

      public static EntityManager createEntityManager() {
        return entityManagerFactory.createEntityManager();
      }

    public Long createObject(Object object) {
        return createDevice((Device) object);
    }

    public List<Object> retrieveObjects(int maxResults, int firstResult) {

        EntityManager em = createEntityManager();
        List<Object> list = null;

        try {
            TypedQuery<Object> query = em.createQuery(
                    "select a from Device a", Object.class);
            query.setMaxResults(maxResults);
            query.setFirstResult(firstResult);
            list = query.getResultList();
        } finally {
            em.close();
        }

        return list;
    }

    public Long createDevice(Device Device) {

        // For an application-managed entity manager its best practice to create
        // a
        // new entity manager inside a method and close it before the method is
        // finished.

        EntityManager em = createEntityManager();
        EntityTransaction tx = em.getTransaction();
        Long DeviceId = -1L;

        try {
            tx.begin();
            em.persist(Device);
            DeviceId = Device.getDeviceId();
            tx.commit();
        } catch (Throwable t) {
            t.printStackTrace();
            tx.rollback();
        } finally {
            em.close();
        }

        return DeviceId;
    }

    public Device retrieveDevice(Long DeviceId) {

        EntityManager em = createEntityManager();
        Device Device = null;

        try {
            TypedQuery<Device> query = em.createQuery(
                    "select a from Device a where a.DeviceId = ?1",
                    Device.class);
            query.setParameter(1, DeviceId);
            Device = query.getSingleResult();
        } finally {
            em.close();
        }

        return Device;
    }

    public List<Device> retrieveDevices(int maxResults, int firstResult) {

        EntityManager em = createEntityManager();
        List<Device> list = null;

        try {
            TypedQuery<Device> query = em.createQuery(
                    "select a from Device a", Device.class);
            query.setMaxResults(maxResults);
            query.setFirstResult(firstResult);
            list = query.getResultList();
        } finally {
            em.close();
        }

        return list;
    }

    public Device updateDevice(Device Device) {

        EntityManager em = createEntityManager();
        EntityTransaction tx = em.getTransaction();
        Device Device2 = null;

        try {
            tx.begin();
            Device2 = em.merge(Device);
            tx.commit();
        } catch (Throwable t) {
            t.printStackTrace();
            tx.rollback();
        } finally {
            em.close();
        }

        return Device2;
    }

    public void deleteDevice(Device Device) {

        EntityManager em = createEntityManager();
        EntityTransaction tx = em.getTransaction();

        try {
            tx.begin();
            em.remove(em.merge(Device));
            tx.commit();
        } catch (Throwable t) {
            t.printStackTrace();
            tx.rollback();
        } finally {
            em.close();
        }
    }
}

And last, my test case (it sucks, i just want to see it running):

package testproject.server;

import java.util.List;

import org.apache.log4j.xml.DOMConfigurator;
import org.junit.After;
import org.junit.Before;
import org.junit.Test;

import com.allen_sauer.gwt.log.client.Log;

import testproject.server.dao.DeviceDao;
import testproject.server.domain.Device;

public class DeviceTestCase {

  private long startTimeMillis;

  @Before
  public void setUp() {

    // The log4j configuration file must be in the Project's root directory
    DOMConfigurator.configure("log4j.xml");

    startTimeMillis = System.currentTimeMillis();
  }

  @Test
  public void testDeviceDao() {
    try {
      Log.debug("testDeviceDao()");

      createDevices();

    }
    catch (Exception e) {
      Log.error("e: " + e);
      e.printStackTrace();
    }
  }

  public void createDevice() {

    Device d1 = new Device();
    d1.setFirmwareVersion("Firmware1");
    d1.setImage("Image1");

    DeviceDao deviceDao = new DeviceDao();
    deviceDao.createDevice(d1);
  }

  public void createDevices() {

    Device d1 = new Device();
    d1.setFirmwareVersion("Firmware1");
    d1.setImage("Image1");

    Device d2 = new Device();
    d1.setFirmwareVersion("Firmware2");
    d1.setImage("Image2");

    Device d3 = new Device();
    d1.setFirmwareVersion("Firmware3");
    d1.setImage("Image3");

    DeviceDao deviceDao = new DeviceDao();

    deviceDao.createDevice(d1);
    deviceDao.createDevice(d2);
    deviceDao.createDevice(d3);

    List<Device> devices = deviceDao.retrieveDevices(100, 0);

    for (Device device : devices) {
      Log.debug(device.toString());
    }
    //TODO: Btw, this is not a really good test case ;)
  }

  @After
  public void tearDown() {
    long endTimeMillis = System.currentTimeMillis();
    float durationSeconds = (endTimeMillis - startTimeMillis) / 1000F;
    Log.debug("Duration: " + durationSeconds + " seconds");
  }
}

If anyone has a suggestion on how to fix this, I would be really happy since I'm really stuck right now.

Answer 1

The message you get is the somewhat cryptic way of HSQLDB to tell you that the DEVICES table, from which you're trying to select, doesn't exist (or that the user you're connecting with doesn't have access to this table). Create it.