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

Question

I am trying to migrate with Hibernate and HSQLDB. I tried to adapt the tutorial to more modern versions, so a problem may arise. This happens when I run unit test using JUnit.

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

The error I am getting 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 SqlExceptionHelper ERROR: 146 - not a user missing privileges or object not found: DEVICES 16: 40: 16,803 ERROR SqlExceptionHelper: 146 - user does not have enough privileges or objects 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 ... Called: org.hibernate.exception.SQLGrammarException: could not prepare expression ... richinenny: java.sql.SQLSyntaxErrorException: the user does not have enough privileges or object, and found: DEVICE ... causes: org.hsqldb.HsqlException: user does not have privilege, or object not found: DEVICE ...

My persistence.xml is as follows:

<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 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 tao looks 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 finally, my test case (this sucks, I just want to run it):

 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 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 as I am really stuck right now.

Answer 1

The message you receive is a somewhat cryptic HSQLDB way to tell you that the DEVICES table you are trying to select from does not exist (or that the user you are connecting to does not have access to this table). Create it.