JPA Entiy on synonym instead of table

Question

I have a Seam 2.2 based Java EE 5 web application with a bunch of tables mapped to JPA 1.0 Entities via Hibernate 3.3.3. During development it's running on a Tomcat 6, Oracle 10 XE and Windows 7.

Now, we had the request by operations department to split the data model into one schema being the owner of all database objects (myschema) and one schema acting as the application's database user (myschema_app). So I did the following:

• create schema myschema_app
• grant object rights on all necessary tables from myschema (both regular ones and n:m intermediate tables) and sequences depending on the usage (one or more of select, insert, update, delete) to myschema_app
• declare private synonyms in myschema_app in order to use the same names than before and hiding the other schema's name prefix
• change property hibernate.default_schema to new schema name in persistence.xml
• change user/password in Tomcat's datasource definition in context.xml

When I start the application while having hibernate.hbm2ddl.autoset to validate, I get an exception when the framework tries to create the EntityManagerFactory telling me that a table is missing. When I execute a select statement directly in an sql tool with myschema_app connected, everything works fine.

I understood that using a synonym going on another table is transparent for the application. Has anyone an idea what I may have overlooked?

Rhinoscopy

Answer 1

My guess is that hbm2ddl seaches specifically for tables, and not for synonyms, but that your application should indeed work as if the tables existed in the schema. Try to remove the hbm2ddl option and test your application.

EDIT: it seems my guess is true: https://forum.hibernate.org/viewtopic.php?p=2438033

Hudibrastic

That was some quick reply, 10 points earned. Yep, I have already seen, that omiting that parameter makes the application work, but it's a pity that Hibernate doesn't support synonyms during validation, because I think it's a good thing to validate the schema at startup to make sure it fits the application. – Crew

The problem is in hibernates org.hibernate.tool.hbm2ddl.DatabaseMetadata class where the table types are hardcoded to private static final String[] TYPES = {"TABLE", "VIEW"}; where it really should be private static final String[] TYPES = {"TABLE", "VIEW", "ALIAS", "SYNONYM"}; – Playwright

Answer 2

As of 4.3.0, It is possible to set hibernate.synonyms=true to solve your problem with synonyms.

References:

https://github.com/hibernate/hibernate-orm/commit/1df4b2ea3c98c74f3b6bbd42e266ee5c7ad60d27

https://hibernate.atlassian.net/browse/HHH-8183

https://github.com/hibernate/hibernate-orm/pull/508

Tender

Answer 3

I still had validation issues because my Oracle driver was not giving the right columns even after setting hibernate.synonyms=true, so instead of disabling schema validation entirely I filtered the synonym table out:

In properties:

hbm2ddl.schema_filter_provider=my.path.to.MyCustomSchemaFilterProvider

Define schema filter provider:

package my.path.to;
..
public class MyCustomSchemaFilterProvider implements SchemaFilterProvider {
    @Override
    public SchemaFilter getCreateFilter() {
        return MySchemaFilter.INSTANCE;
    }

    @Override
    public SchemaFilter getDropFilter() {
        return MySchemaFilter.INSTANCE;
    }

    @Override
    public SchemaFilter getMigrateFilter() {
        return MySchemaFilter.INSTANCE;
    }

    @Override
    public SchemaFilter getValidateFilter() {
        return MySchemaFilter.INSTANCE;
    }
}

SchemaFilter:

..
public class MySchemaFilter implements SchemaFilter {
    public static final MySchemaFilter INSTANCE = new MySchemaFilter();

    @Override
    public boolean includeNamespace(Namespace namespace) {
        return true;
    }

    @Override
    public boolean includeTable(Table table) {
        if (table.getName().toLowerCase().equals("synonymtabletoexclude")){
            return false;
        }
        return true;
    }

    @Override
    public boolean includeSequence(Sequence sequence) {
        return true;
    }
}

This was based off of https://medium.com/@horiaconstantin/excluding-hibernate-entities-from-auto-generation-bce86f8e6d94

Psyche