CODEWITHSUNDEEP
javadatabasehibernateunit-testinghsqldb
Edwardr
Edwardr

Reputation: 2956

Configuring HSQL Server for Hibernate tests

I'm just starting with Hibernate and getting my head around things.

Currently I'm trying to setup a testing environment where I can use a HSQL in-memory instance to test my project.

The error I'm running into is:

javax.persistence.PersistenceException: org.hibernate.exception.GenericJDBCException: invalid schema name: TSG

Here are the relevant parts of my project:

persistence.xml

<?xml version="1.0" encoding="UTF-8"?>

org.hibernate.ejb.HibernatePersistence com.foo.api.models.tsg.AlgPpcAlgorithmOutputEntity 

<persistence-unit name="TestingPersistenceUnit" transaction-type="RESOURCE_LOCAL">
    <provider>org.hibernate.ejb.HibernatePersistence</provider>
    <class>com.foo.api.models.tsg.AlgPpcAlgorithmOutputEntity
    </class>
    <properties>
        <property name="dialect" value="org.hibernate.dialect.HSQLDialect"/>
        <property name="hibernate.connection.driver_class" value="org.hsqldb.jdbcDriver"/>
        <property name="hibernate.connection.url" value="jdbc:hsqldb:mem:tsg"/>
        <property name="hbm2ddl.auto" value="create-drop"/>
        <property name="hibernate.connection.autocommit" value="true"/>
        <property name="hibernate.connection.username" value="sa"/>
        <property name="hibernate.connection.password" value=""/>
        <property name="hibernate.show_sql" value="true"/>
    </properties>
</persistence-unit>

As you can see, I have one peristence-unit for production (which works fine) and one in-memory HSQL one for testing (which I can't get to work).

An example Hibernate entity:

package com.foo.api.models.tsg;

import javax.persistence.*;
import java.math.BigDecimal;

@IdClass(AlgPpcAlgorithmOutputEntityPK.class)
@Table(name = "alg_ppc_algorithm_output", schema = "", catalog = "tsg")
@Entity
public class AlgPpcAlgorithmOutputEntity {
    private int parameterId;

    @Column(name = "parameter_id")
    @Id
    public int getParameterId() {
        return parameterId;
    }

    public void setParameterId(int parameterId) {
        this.parameterId = parameterId;
    }

    private String matchType;

    @Column(name = "matchType")
    @Basic
    public String getMatchType() {
        return matchType;
    }

    // for brevity I have removed the rest of the implementation
    // It was auto-generated by Hibernate and works fine in production.
}

Finally, a simple TestCase class:

package tests.integration;


import com.foo.api.models.tsg.AlgPpcAlgorithmOutputEntity;
import com.foo.api.util.HibernateUtil;
import org.hsqldb.Server;
import org.hsqldb.persist.HsqlProperties;
import org.hsqldb.server.ServerConfiguration;
import org.junit.After;
import org.junit.Before;
import org.junit.Test;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import tests.util.HSQLServerUtil;

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

import com.foo.api.KeywordManager;

import java.sql.Date;
import java.util.HashSet;

public class KeywordManagerTestCase {

    private static final Logger LOG =   LoggerFactory.getLogger(KeywordManagerTestCase.class);
    private EntityManagerFactory eMF;
    protected EntityManager eM;

    @Before
    public void setUp() throws Exception {

        HsqlProperties props = new HsqlProperties();
        props.setProperty("server.database.0", "mem:tsg");
        props.setProperty("server.dbname.0", "tsg");

        ServerConfiguration.translateDefaultDatabaseProperty(props);

        Server hsqlServer = new Server();
        hsqlServer.setRestartOnShutdown(false);
        hsqlServer.setNoSystemExit(true);
        hsqlServer.setProperties(props);
        hsqlServer.setTrace(true);

        LOG.info("Configured the HSQLDB server...");
        hsqlServer.start();
        LOG.info("HSQLDB server started on port " + hsqlServer.getPort() + "...");

        LOG.info("Loading hibernate...");
        if (eMF == null) {
            eMF = Persistence.createEntityManagerFactory("TestingPersistenceUnit");
        }

        eM = eMF.createEntityManager();
    }

    /**
     * shutdown the server.
     * @throws Exception in case of errors.
     */
    @After
    public void tearDown() throws Exception {
        eM.close();
        HSQLServerUtil.getInstance().stop();
    }

    /**
     * Demo test to see that the number of user records in the database corresponds the flat file inserts.
     */
    @Test
    public void testDemo1() {
        AlgPpcAlgorithmOutputEntity entity = new AlgPpcAlgorithmOutputEntity();
        entity.setParameterId(200);
        entity.setMatchType("aa");
        KeywordManager km;
        eM.persist(entity);

        HashSet<Integer> params = new HashSet<Integer>();
        params.add(200);
        km = new KeywordManager(eM, params, new Date[2]);

        HashSet<AlgPpcAlgorithmOutputEntity> res = km.pullKeywords(params);
        for (AlgPpcAlgorithmOutputEntity s : res) {
            System.out.println(s.getMatchType());
        }

    }


}

I'm sure I have set something up in a strange way, but as I say - this is my very first stab.

Here is what I'm trying to do:

I just can't get past this PersistenceException!

UPDATE

OK, so I realised that I didn't need to setup an explicit HSQL server in the setup of my test cases, because that's exactly what the persistence-unit entry in my persistence.xml is for. Also I have attempted to ALTER the CATALOG so that it matches the catalog used by my mapping classes. The setup of my test case now looks like:

private EntityManagerFactory eMF;
protected EntityManager eM;

@Before
public void setUp() throws Exception {
    LOG.info("Loading hibernate...");
    if (eMF == null) {
        eMF = Persistence.createEntityManagerFactory("TestingPersistenceUnit");
    }

    eM = eMF.createEntityManager();

    EntityTransaction eT = null;
    eT = eM.getTransaction();
    eT.begin();
    Query q = eM.createNativeQuery("ALTER CATALOG PUBLIC RENAME TO TSG");
    q.executeUpdate();
    eT.commit();

    // And also it seems I need to create the schema
    eT = eM.getTransaction();
    eT.begin();
    q = eM.createNativeQuery("CREATE SCHEMA TSG AUTHORIZATION DBA");
    q.executeUpdate();
    eT.commit();
}

However, I just end up with a new error now, specifically: 

javax.persistence.PersistenceException: org.hibernate.exception.SQLGrammarException: user lacks privilege or object not found: ALG_PPC_ALGORITHM_OUTPUT

So I'm getting somewhere but it seems the tables are not being created. I wonder if there is an issue with my persistence.xml?

Upvotes: 3

Views: 15797

Answers (4)

pharsfalvi
pharsfalvi

Reputation: 807

You can override the entity mapping for your unit test by dropping in an orm.xml file to your src/test/resources/META-INF (in case of using the maven layout). In this you can override the JPA annotation mappings. For your needs you just need to override the table location, like this:

<?xml version="1.0" encoding="UTF-8"?>
<entity-mappings xmlns="http://java.sun.com/xml/ns/persistence/orm" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
  xsi:schemaLocation="http://java.sun.com/xml/ns/persistence/orm http://java.sun.com/xml/ns/persistence/orm_1_0.xsd" version="1.0">

  <entity class="com.foo.api.models.tsg.AlgPpcAlgorithmOutputEntity">
    <table name="alg_ppc_algorithm_output"/>
  </entity>

</entity-mappings>

This will place your table into the default public catalog of the hsqldb database, so you don't have to alter the hsqldb scheme. It may even work when your unit test uses multiple catalogs containing tables with the same name, as you just have to give different names in the table name attribute.

Upvotes: 1

Yogendra Singh
Yogendra Singh

Reputation: 34387

You are specifying server name in the URL but trying to use memory database, which is causing the issue.

Try using the DB URL as:

           jdbc:hsqldb:mem:tsg

i.e.

  <property name="hibernate.connection.url" value="jdbc:hsqldb:mem:tsg"/>

Also use ALTER CATALOG RENAME TO tsg to change the default catalog name(PUBLIC).

EDIT: To auto create the schema, update below in persistence.xml (hibernate.hbm2ddl.auto in place of hbm2ddl.auto)

    <property name="hibernate.hbm2ddl.auto" value="create-drop"/>

Upvotes: 2

Steve Ebersole
Steve Ebersole

Reputation: 9443

As far as I know (and I am not a HSQL expert), the TSG you specify in the connection url is the database name which is not the same as the catalog. See http://hsqldb.org/doc/2.0/guide/databaseobjects-chapt.html#dbc_schemas_schema_objects

In HyperSQL, there is only one catalog per database. The name of the catalog is PUBLIC. You can rename the catalog with the ALTER CATALOG RENAME TO statement. All schemas belong the this catalog. The catalog name has no relation to the file name of the database.

As I read that, when HSQL creates a database it creates a catalog named PUBLIC within that database and a schema named PUBLIC within that catalog. There can only ever be one catalog per HSQL database. There can be multiple schemas within that single catalog.

The error you are getting comes actually from the attempt to specify catalog = "tsg" in your mapping. That catalog does not exist. Since the HSQL database can contain only one catalog, you will have to rename that PUBLIC catalog to TSG (or change up your mapping).

Upvotes: 3

fredt
fredt

Reputation: 24372

There is an obvious error in the setup. The connection URL must point to the Server:

<property name="hibernate.connection.url" value="jdbc:hsqldb:hsql://localhost/tsg"/>

Upvotes: 0

Related Questions