Add entity with InheritanceType.JOINED to native query

Question

I am struggling to get native queries to work with InheritanceType.JOINED. From the Hibernate documentation I have found:

13.1.6. Handling inheritance

Native SQL queries which query for entities that are mapped as part of an inheritance must include all properties for the baseclass and all its subclasses.

Using the following two entities:

@Data
@Entity
@Table(name = "my_super")
@EqualsAndHashCode(of = {"id"})
@Inheritance(strategy = InheritanceType.JOINED)
public abstract class MySuper {

    @Id
    @Column(name = "id")
    @GeneratedValue(strategy = GenerationType.SEQUENCE)
    private long id;

}

And:

@Data
@Entity
@Table(name = "my_sub_a")
@EqualsAndHashCode(callSuper = true)
public class MySubA extends MySuper {

    @Column(name = "x")
    private int x;

}

When I try to create a native query using:

Object actual = session
    .createNativeQuery("SELECT {s.*} FROM my_super {s} LEFT JOIN my_sub_a {a} USING (id)")
    .addEntity("s", MySuper.class)
    .getSingleResult();

It translates to the query:

SELECT s.id as id1_1_0_, s_1_.x as x1_0_0_, case when s_1_.id is not null then 1 when s.id is not null then 0 end as clazz_0_ FROM my_super s LEFT JOIN my_sub_a  a  USING (id) 

And then fails with:

javax.persistence.PersistenceException: org.hibernate.exception.SQLGrammarException: could not prepare statement
Caused by: org.hibernate.exception.SQLGrammarException: could not prepare statement
Caused by: org.h2.jdbc.JdbcSQLException: Column "S_1_.X" not found; SQL statement:

So we observe the alias injection doing its work, figuring out that it may need the x column of my_sub_a as well. It is however unable to figure out the alias for my_sub_a. How should my code be modified so that this alias is connected properly as well?

My code is available at https://gist.github.com/JWGmeligMeyling/51e8a305f3c268eda473511e202f76e8 for easy reproduction of my issue.

(I am aware that this query can easily be expressed in JPQL or HQL as well, and can even be achieved using the EntityManager and Session API's. I do however want to use this in a more complex query, that I simplified to all details required for this question).

Answer 1

The issue seems related to the underlying database dialect in use, in other words to some "exotic" query parts. The behavior you described is replicable with your provided query but with some tiny tweeks it's running without errors - depending on the dialect you want to use.

In your example you use the H2 database, but I assume that's not your production dialect, right? I also tried it with PostgresSQL database (in version 9.5).

With the origin query, the behavior is the same on H2 and PostgreSQL. But if you remove the curly braces from your columns and aliases (which seem like some ODBC escape sequences) and change the USING clause to an explicit ON a.id = s.id condition the query is executable without any exception.

To verify the behavior I created some tests with different queries using either the Hibernate Session or the EntityManager, because after taking a look at your linked example code I got somehow confused by the mixed up usage of the Hibernate Session interface and EntityManager methods like createNativeQuery. In case of doubt I gave a try for both. I used the same entities and more or less the same configuration and test code like you did in your example but within a Spring Boot environment, only for convenience purposes. For switching between the databases I used Spring Boot Profiles, just activate / uncomment the @ActiveProfiles("postgres") part if you have configurations for both databases in place.

Here are the tests, I hope that does help a little:

import static org.assertj.core.api.Assertions.assertThat;

import java.util.List;

import javax.persistence.EntityManager;
import javax.persistence.PersistenceContext;

import org.hibernate.Session;
import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.test.context.ActiveProfiles;
import org.springframework.test.context.junit4.SpringRunner;
import org.springframework.transaction.annotation.Transactional;

@RunWith(SpringRunner.class)
@SpringBootTest
@Transactional
//@ActiveProfiles("postgres") // activate for PostgreSQL tests
public class InheritanceDemoApplicationTests {

    private static final String QUERY = "SELECT {s.*} FROM my_super {s} LEFT JOIN my_sub_a {a} USING (id)";
    private static final String QUERY_WITHOUT_ODBC_ESCAPES = "SELECT s.* FROM my_super s LEFT JOIN my_sub_a a USING (id)";
    private static final String QUERY_WITHOUT_USING_KEYWORD = "SELECT {s.*} FROM my_super {s} LEFT JOIN my_sub_a {a} ON a.id = s.id";
    private static final String QUERY_WITHOUT_ODBC_ESCAPES_AND_WITHOUT_USING_KEYWORD = "SELECT s.* FROM my_super s LEFT JOIN my_sub_a a ON a.id = s.id";

    @PersistenceContext
    private EntityManager entityManager;

    @Test
    public void sessionQuery() {
        validateQueryViaSession(QUERY);
    }

    @Test
    public void entityManagerQuery() {
        validateQueryViaEntityManager(QUERY);
    }

    @Test // works for PostgreSQL
    public void sessionQueryWithoutOdbc() {
        validateQueryViaSession(QUERY_WITHOUT_ODBC_ESCAPES);
    }

    @Test // works for PostgreSQL
    public void entityManagerQueryWithoutOdbc() {
        validateQueryViaEntityManager(QUERY_WITHOUT_ODBC_ESCAPES);
    }

    @Test
    public void sessionQueryWithoutUsing() {
        validateQueryViaSession(QUERY_WITHOUT_USING_KEYWORD);
    }

    @Test // works for H2
    public void entityManagerQueryWithoutUsing() {
        validateQueryViaEntityManager(QUERY_WITHOUT_USING_KEYWORD);
    }

    @Test // works for H2 & PostgreSQL
    public void sessionQueryWithoutOdbcAndWithoutUsing() {
        validateQueryViaSession(QUERY_WITHOUT_ODBC_ESCAPES_AND_WITHOUT_USING_KEYWORD);
    }

    @Test // works for H2 & PostgreSQL
    public void entityManagerQueryWithoutOdbcAndWithoutUsing() {
        validateQueryViaEntityManager(QUERY_WITHOUT_ODBC_ESCAPES_AND_WITHOUT_USING_KEYWORD);
    }

    @SuppressWarnings("rawtypes")
    private void validateQueryViaSession(final String queryString) {
        final MySubA match = persistMySubA();
        List result = entityManager.unwrap(Session.class).createSQLQuery(queryString).addEntity("s", MySuper.class)
                .list();
        assertThat(result.iterator().next()).isEqualToComparingFieldByField(match);
    }

    @SuppressWarnings("rawtypes")
    private void validateQueryViaEntityManager(final String queryString) {
        final MySubA match = persistMySubA();
        List result = entityManager.createNativeQuery(queryString, MySuper.class).getResultList();
        assertThat(result.iterator().next()).isEqualToComparingFieldByField(match);
    }

    private MySubA persistMySubA() {
        final MySubA mySubA = new MySubA();
        mySubA.setX(1);
        entityManager.persist(mySubA);
        entityManager.flush();
        return mySubA;
    }

}