How to resolve the Hibernate global database schema and catalog for native SQL queries

Introduction

When your relation database system uses multiple schemas, then you can instruct Hibernate to use a global schema using the hibernate.default_schema configuration property:

<property name="hibernate.default_schema" value="forum"/>

While Hibernate can imply the default schema whenever dealing with entity queries, for native queries, you need a little trick. This post is going to demonstrate how you can imply the default schema for native SQL queries as well.

Domain Model

One we defined a global schema, there is no need to specify the schema on the entity level:

@Entity(name = "Event")
@Table(name = "event")
public class Event {

    @Id
    @GeneratedValue
    private long id;

    private String name;

    @Column(name = "created_on")
    private Timestamp createdOn;

    //Getters and setters omitted for brevity
}

Entity queries

If we want to execute an entity query:

List<Event> events = entityManager.createQuery(
    "select e " +
    "from Event e " +
    "where e.createdOn > :timestamp", Event.class)
.setParameter("timestamp", 
    Timestamp.valueOf(LocalDateTime.now().minusMonths(1)))
.getResultList();

Hibernate can inject the database schema in the underlying SQL statement:

SELECT e.id AS id1_0_,
       e.created_on AS created_2_0_,
       e.name AS name3_0_
FROM   forum.event e
WHERE  e.created_on > '2016-09-26 17:22:11.191'

Sweet!

Native queries

But now let’s define a native SQL query:

@NamedNativeQuery(
    name = "past_30_days_events",
    query = 
        "select * " +
        "from event" +
        "where age(created_on) > '30 days'",
    resultClass = Event.class
)

When executing this SQL query:

List<Event> events = entityManager.createNamedQuery(
    "past_30_days_events")
.getResultList();

Hibernate throws the following exception:

javax.persistence.PersistenceException:
Caused by: org.hibernate.exception.SQLGrammarException: could not extract ResultSet
Caused by: org.postgresql.util.PSQLException: ERROR: relation "event" does not exist

The event table does not exist in the default public schema, so we need to change all native queries to use the forum schema instead. While this works, it would be so nice if Hibernate would do that automatically on our behalf.

Therefore, we need to change the SQL query to:

@NamedNativeQuery(
    name = "past_30_days_events",
    query =
        "select * " +
        "from {h-schema}event " +
        "where age(created_on) > '30 days'",
    resultClass = Event.class
)

And now, when running the aforementioned native query, Hibernate generates the following SQL query:

SELECT *
FROM   forum.event
WHERE  age(created_on) > '30 days'

Brilliant!

If you enjoyed this article, I bet you are going to love my book as well.

Conclusion

Working with a default schema is so much easier when the schema name is defined only one, by the hibernate.default_schema configuration property. While this works just fine for JPQL queries, for native queries you need to provide the {h-schema} placeholder.

Hibernate also defines h-domain and h-catalog placeholders as well. The {h-catalog} resolve the global catalog defined by the hibernate.default_catalog configuration property. The {h-domain} resolves both the global catalog and schema, injecting them like this: catalog.schema.

If you liked this article, you might want to subscribe to my newsletter too.

Advertisements

2 thoughts on “How to resolve the Hibernate global database schema and catalog for native SQL queries

  1. Thx, nice feature.
    I remember in my project we had used
    @Table(name = “event”, schema = “forum”) for each entity
    before we found hibernate.default_schema 🙂

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s