I have a class X that has a displayNames property implemented by a map displayNames.
Code:
public class X {
/** Localized display names */
private Map<String, String> displayNames;
}
The keys of that map stand for language codes (e.g. 'en', 'de', 'fr') and can be arbitrary. The values of the map are the display names of that object.
I now can search for all X with a specific description:
Code:
@NamedQuery(name = "X.getAll", query = "from X x where 'abc' in elements(x.displayNames)")
But so far I could not find a query that would return me all X that count 'abc' as substring.
What I managed so far is to search a specific language for the substring. This seems to work with:
Code:
@NamedQuery(name = "X.getAll", query = "from X x where x.displayNames['en'] like 'abc'")
But how can I create a HQL query that searches and returns all instances of X that have a display name that includes a substring 'abc'? Is there maybe an alternative way to the LIKE operator?
The most promising candidate
Code:
@NamedQuery(name = "X.getAll", query = "from X x where any elements(x.displayNames) like 'abc'")
fails with a runtime error:
Code:
10:15:29.091 WARN org.hibernate.util.JDBCExceptionReporter - SQL Error: -5581, SQLState: 42581
10:15:29.091 ERROR org.hibernate.util.JDBCExceptionReporter - unexpected token: SELECT
javax.persistence.PersistenceException: org.hibernate.exception.SQLGrammarException: could not execute query
at org.hibernate.ejb.AbstractEntityManagerImpl.convert(AbstractEntityManagerImpl.java:1235)
at org.hibernate.ejb.AbstractEntityManagerImpl.convert(AbstractEntityManagerImpl.java:1168)
[...]
Caused by: org.hibernate.exception.SQLGrammarException: could not execute query
at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:92)
at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:66)
[...]
Caused by: java.sql.SQLException: unexpected token: SELECT
at org.hsqldb.jdbc.Util.sqlException(Unknown Source)
at org.hsqldb.jdbc.JDBCPreparedStatement.<init>(Unknown Source)
[...]
Caused by: org.hsqldb.HsqlException: unexpected token: SELECT
at org.hsqldb.error.Error.parseError(Unknown Source)
at org.hsqldb.ParserBase.unexpectedToken(Unknown Source)
at org.hsqldb.ParserDQL.XreadSimpleValueExpressionPrimary(Unknown Source)