Search code examples
javahql

HQL join on substring

I have two tables that are loosely coupled. I have a SQL query that works, but I am having difficulty converting it to HQL.

Table account has a column 'name', of which a substring is the key to another table.

SELECT
a.id, 
a.name 
j.description
FROM account a 
JOIN jar j ON  j.jar_id = substr(a.name, LOCATE('jar-', a.name) + LENGTH('jar-'), LENGTH(a.name))

This join works where account.name has values 'jar-255', 'jar-756', 'jar-881', etc. It extracts the id by substring and uses it to reference the jar table.

It doesn't work in Java/HQL, as the Account object doesn't directly reference or have a child Jar object. The error is 

org.hibernate.hql.internal.ast.QuerySyntaxException: Path expected for join!

This is the join in HQL:

JOIN Jar j ON j.jarId = substring(a.name, LOCATE(a.name, 'jar-') + LENGTH('jar-'), LENGTH(a.name))

Is this acheivable?

Solution

  • Prior to Hibernate 5.1 you will need to have a reference/relation to do a join. So if it is an older version (below 5.1), then you will have to do the following:

    FROM Account a, Jar j WHERE j.jarId=substring....