How to use the left left function in hql

I have sql query like this

select column from table where path = left('INPUTSTRING', length(path));


and try to execute it in hql like this,

  return session.createQuery("from Table where Path = left(:input, length(Path))").
                            query.setParameter("input", inputPath).


and getting this error

Caused by: org.hibernate.hql.ast.QuerySyntaxException: unexpected token: left near line 1


how to do it? What is the corresponding string function in hql? Is there a solution for this using the apis criteria query?


source to share

2 answers

Yes, left()

not supported MySQLDialect

. See List of supported HQL functions in API docs .

You now have 2 options left.

  • Use the method session.createSQLQuery()

  • Create your own class Dialect

    by extending MySQLDialect

    and registering the function there. It is reported on the hibernate forum here , explained well in a blog post here .


I'm not sure if HQL will do this for you, but you can use IQuery / session.CreateSQLQuery () to use a raw SQL query to populate the mapped object. I've never used it for substrings, but used it for aggregate functions. Check chapter 13 of the NHibernate docs and see if it works for you. You can check out Query Replacement in Nhibernate - here



All Articles