Unable to validate schema when view via synonym

Hibernate ORM
1

Unable to build Hibernate SessionFactory org.hibernate.tool.schema.spi.SchemaManagementException: Schema-validation: missing table [CAMPAIGN_VW] Schema-validation: missing table [CAMPAIGN_VW]

CAMPAIGN_VW is a view in another Oracle schema. A synonym is created in the schema being connected to under the same name to allow standard queries to work. This works fine in sqldeveloper, and in jdbc.

We are using META-INF/persistence.xml and the Entity is announced there:

<?xml version="1.0" encoding="UTF-8"?>
<persistence xmlns="https://jakarta.ee/xml/ns/persistence"
             xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
             xsi:schemaLocation="https://jakarta.ee/xml/ns/persistence https://jakarta.ee/xml/ns/persistence/persistence_3_1.xsd"
             version="3.1">
...
        <class>content.dataobjects.CampaignVW</class>

As soon as I move the query referencing the view into a NamedQuery the validation fails.

We are using Hibernate 6.6.2.Final and JPA 3.x annotations. Have tried to do like this and no improvement is seen:

@Entity
@Immutable
@Table(name = "CAMPAIGN_VW"). <-- this is the name of the synonym
public class CampaignVW {
...
}

The named query is using the entity names:

...(select mcv.campaignId from CampaignVW mcv where ...)

The named query appears in another Entity class, not in CampaignVW (I don’t think that should matter). The other class is also listed in the persistence.xml and has all required annotations. We have been using hibernate 6.x with JPA for about 6 months in many cases without this kind of issue. This is the only case thus far involving a synonym into a view in another schema.

Any ideas?

2

Hello @bodhi-one. Schema validation uses DatabaseMetaData#getTables to check entity tables correspond to existing objects on the db side. I imagine, in your case, the view synonym is not included in there, hence why you’re seeing this error.

You can disable validation for specific objects using a SchemaFilter, and excluding the CAMPAIGN_VW “table” (that’s really a view) to avoid this problem.

3

Ok, thanks. I looked at SchemaFilter and it seems that you have to come in through a SchemaFilterProvider. However, pointing at our CustomSchemaFilterProvider through this persistence.xml property is not picking it up.

<property name="hibernate.schema_filter_provider" value="sample.hibernate.CustomSchemaFilterProvider"/>

Are there any working examples/references ?

4

The property to use is hibernate.hbm2ddl.schema_filter_provider.

5

We found this one to use to insert a CustomSchemaFilterProvider, seems to work. hibernate.hbm2ddl.schema_filter_provider

6

Actually, because schema validation is turned off for that Entity, because it appears as part of a NamedQuery, hibernate still fails to deal with it. I think this is indicating that if you are using a NamedQuery you cannot use an Entity which comes in through a synonym. Would we consider this a design limitation of hibernate?

[PersistenceUnit: persistence] Unable to build Hibernate SessionFactory org.hibernate.query.NamedQueryValidationException: Errors in named queries: 
  [1] Error in query named 'CampaignX.loadCampaignTpByCId': Could not resolve root entity 'CampaignVW'
  [2] Error in query named 'CampaignX.loadCampaignTpBySCode': Could not resolve root entity 'CampaignVW' Errors in named queries:

This class is announced in persistence.xml

7

That’s weird, I don’t see why disabling schema validation would impact named query interpretation. There might be something else going on here, it would be great if you could try to create a reproducer with our test case template and if you are able to reproduce the issue, create a new ticket in our issue tracker and attach that reproducer.