CODEWITHSUNDEEP
javasqlhibernate
Niamath
Niamath

Reputation: 309

what is the equelent of sql query in hibernate

In SQL Server i am using this query

select * 
from Unit c 
ORDER BY CONVERT(INT, LEFT(name, PATINDEX('%[^0-9]%', name + 'z')-1)) desc;

I want this query to use in Hibernate. when I use this in Hibernate I got error

java.lang.IllegalArgumentException: org.hibernate.hql.ast.QuerySyntaxException:unexpected token: LEFT near line 1, column 122 
    [SELECT c FROM models.entities.UnitEntity c WHERE c.expSetId = :expSetId AND isWaitArea=:isWaitArea ORDER BY CONVERT(INT, LEFT(name, PATINDEX('%[^0-9]%', name + 'z')-1)) asc]

Upvotes: 0

Views: 288

Answers (2)

adjiandov
adjiandov

Reputation: 47

I am pretty sure that you can use SQL query with hibernate as well. When you create your hibernate session, you can use something like 

session.createSQLQuery("Your Query Here")

Hope this helps.

Upvotes: 0

Angga
Angga

Reputation: 2323

some of that is not in the hibernate dialect, you can change left with substring, convert with cast. and as for patindex i couldn't find the substitution. you either can add pathindex to the constructor of the dialect that you use

registerFunction( "patindex", new StandardSQLFunction("patindex") );

or create patindex() to a stored procedure.

then you can use something like this: 

from Unit c order by cast(substring(name, 0, PATINDEX('%[^0-9]%', name + 'z')-1) as integer);

or you can use locate() instead of patindex(), but i think it doesn't support regular expression.

Upvotes: 1

Related Questions