Hibernate解析含STRAIGHT_JOIN关键字的原生查询失败求助

I've run into this exact issue before—Hibernate's formula parser doesn't handle STRAIGHT_JOIN correctly, as you saw, it incorrectly prefixes the inner table with the outer query's alias. Here are a few reliable workarounds that preserve the STRAIGHT_JOIN requirement:

1. Wrap the Inner Query in a Subquery #

By nesting your STRAIGHT_JOIN logic inside a subquery, you prevent Hibernate from trying to parse and rewrite the inner table references. Hibernate will treat the entire inner block as a black box and leave the STRAIGHT_JOIN intact.

Modify your @Formula like this:

@Formula("(SELECT MAX(sub.col1) FROM (" +
         "SELECT table1.col1 " +
         "FROM table1 STRAIGHT_JOIN table2 t ON table1.col2 = t.col2 " +
         "INNER JOIN table3 t3 ON t3.col1 = t.col1 " +
         "WHERE t3.col2 = 1) AS sub)")
private Integer code;

This forces Hibernate to only process the outer subquery, leaving your STRAIGHT_JOIN logic untouched.

2. Use a Database View #

If you prefer cleaner code in your entity, create a database view that encapsulates the STRAIGHT_JOIN logic, then query the view in your @Formula.

First, create the view in MySQL:

CREATE VIEW table2_max_code AS
SELECT t.id, MAX(table1.col1) AS code
FROM table2 t
STRAIGHT_JOIN table1 ON table1.col2 = t.col2
INNER JOIN table3 t3 ON t3.col1 = t.col1
WHERE t3.col2 = 1
GROUP BY t.id;

Then update your entity's @Formula to fetch from the view:

@Formula("(SELECT code FROM table2_max_code WHERE id = id)")
private Integer code;

Hibernate will only interact with the view, so it won't modify the underlying STRAIGHT_JOIN logic inside the view.

3. Upgrade Hibernate to a Fixed Version #

This specific parsing bug has been addressed in newer Hibernate versions (around 5.4.x and later). If you're on an older version, upgrading might resolve the issue without needing workarounds. Just make sure to test thoroughly after upgrading to avoid breaking other parts of your application.

4. Use a Named Native Query with @PostLoad #

As a last resort, you can fetch the value using a named native query and load it into the entity after it's retrieved from the database.

First, define the native query:

@NamedNativeQuery(
    name = "Table2.getMaxCode",
    query = "SELECT MAX(table1.col1) FROM table1 STRAIGHT_JOIN table2 t ON table1.col2 = t.col2 INNER JOIN table3 t3 ON t3.col1 = t.col1 WHERE t3.col2 = 1 AND t.id = ?",
    resultClass = Integer.class
)
@Table(name="table2")
public class Table2{
    // ... existing fields ...
    private Integer code;

    @PostLoad
    private void loadCode() {
        EntityManager em = Persistence.createEntityManagerFactory("yourPU").createEntityManager();
        this.code = em.createNamedQuery("Table2.getMaxCode", Integer.class)
                      .setParameter(1, this.id)
                      .getSingleResult();
        em.close();
    }
}

Note: This approach adds an extra query per entity load, so it's less efficient than using @Formula directly. Use this only if the other methods don't work for your setup.

内容的提问来源于stack exchange，提问作者Keshav sardana