-->
These old forums are deprecated now and set to read-only. We are waiting for you on our new forums!
More modern, Discourse-based and with GitHub/Google/Twitter authentication built-in.

All times are UTC - 5 hours [ DST ]



Forum locked This topic is locked, you cannot edit posts or make further replies.  [ 6 posts ] 
Author Message
 Post subject: Criteria API Query with Binary Convert and Bitwise Operator
PostPosted: Thu Mar 24, 2016 4:39 am 
Newbie

Joined: Wed Nov 13, 2013 7:03 am
Posts: 4
Hello together,

I want to create a Criteria Query with a converter (String to Binary) and a bitwise operator. Is this possible with the CriteriaApi? I couldn't find any soution.

Code:
Select * from MyTable
where CONVERT(VARBINARY(4), SUBSTRING(foo, 10, 4), 2) &10= 10


The column foo is a String (varchar in MSSQL).

Substring is no problem:
Code:
Path<String> field = root.get(MyTable_.foo);
Expression<String> expression = cb.substring(field, 10, 4);


In order to convert fields I found following statement:
Code:
ParameterExpression<?> param1 = cb.parameter(String.class, "convertParam1");
cb.function("CONVERT", Byte[].class, param1, expression);
query.setParameter("convertParam1", "VARBINARY");


But there I also get the problem that hibernate includes the parameter param1 with '' e.g. 'VARBINARY' instead VARBINARY
Source: jpa-criteria-api-function-convert-or-cast

So the query is:
Code:
CONVERT('VARBINARY', SUBSTRING(foo, 10, 4))

instead
Code:
CONVERT(VARBINARY, SUBSTRING(foo, 10, 4))


Top
 Profile  
 
 Post subject: Re: Criteria API Query with Binary Convert and Bitwise Operator
PostPosted: Thu Mar 24, 2016 4:32 pm 
Hibernate Team
Hibernate Team

Joined: Thu Sep 11, 2014 2:50 am
Posts: 1630
Location: Romania
I don't think it's possible because HQL and Criteria support functions but not operators. You might need a native query for this.


Top
 Profile  
 
 Post subject: Re: Criteria API Query with Binary Convert and Bitwise Operator
PostPosted: Tue Mar 29, 2016 12:46 am 
Newbie

Joined: Wed Nov 13, 2013 7:03 am
Posts: 4
Oh, that's bad.. :(
Yeah with native query it works fine. I think another workaround is to create an own function which can handle this internally as well. There I only have to pass the parameters which I need, but I don't know how the performance is - we will see.
Is there any plan to support it with newer hibernate versions?


Top
 Profile  
 
 Post subject: Re: Criteria API Query with Binary Convert and Bitwise Operator
PostPosted: Tue Mar 29, 2016 4:21 am 
Hibernate Team
Hibernate Team

Joined: Thu Sep 11, 2014 2:50 am
Posts: 1630
Location: Romania
Maybe the new HQM can improve this and add suport for operators too, but that will only be available in Hibernate 6.x


Top
 Profile  
 
 Post subject: Re: Criteria API Query with Binary Convert and Bitwise Operator
PostPosted: Tue Mar 29, 2016 9:31 am 
Newbie

Joined: Wed Nov 13, 2013 7:03 am
Posts: 4
I hope that Hibernate implements this feature in version 6. Shall I create a ticket?

I "solved" the problem by creating an own function and call the function with CriteriaApi:

SQL Function:
Code:
ALTER FUNCTION [dbo].[testIt](@foo varchar(50), @starting_position int, @length int, @bit_expression int)
returns int
as
begin
   return CONVERT(VARBINARY(4), SUBSTRING(@foo, @starting_position, @length), 2) &@bit_expression;
end


Java Criteria API:
Code:
public List<MyTable> test() {
   int startPos = 10;
   int length = 4;
   int bitwise = 10;
      
   // criteria sql
   CriteriaBuilder cb = em.getCriteriaBuilder();
   CriteriaQuery<MyTable> cq = cb.createQuery(MyTable.class);

   Root<MyTable> root = cq.from(MyTable.class);
   cq.select(root);
      
   Path<String> field = root.get(MyTable_.foo);
      
   Expression<?> startPosExpr = cb.literal(startPos);
   Expression<?> lengthExpr = cb.literal(length);
   Expression<?> bitwiseExpr = cb.literal(bitwise);
      
   Expression<?> functExpr= cb.function("dbo.testIt", Integer[].class, field, startPosExpr, lengthExpr, bitwiseExpr);
      
   Predicate binaryConditionPred = cb.equal(functExpr, bitwise);
      
   cq.where(binaryConditionPred);

   List<MyTable> entries;
   try {
      TypedQuery<MyTable> query = em.createQuery(cq).setMaxResults(100);
      entries = query.getResultList();
   } catch (NoResultException e) {
      entries = new ArrayList<MyTable>();
   }
   return entries;
}


Last edited by dasago on Tue Mar 29, 2016 9:36 am, edited 1 time in total.

Top
 Profile  
 
 Post subject: Re: Criteria API Query with Binary Convert and Bitwise Operator
PostPosted: Tue Mar 29, 2016 9:36 am 
Hibernate Team
Hibernate Team

Joined: Thu Sep 11, 2014 2:50 am
Posts: 1630
Location: Romania
Thanks for posting your solution.


Top
 Profile  
 
Display posts from previous:  Sort by  
Forum locked This topic is locked, you cannot edit posts or make further replies.  [ 6 posts ] 

All times are UTC - 5 hours [ DST ]


You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum

Search for:
© Copyright 2014, Red Hat Inc. All rights reserved. JBoss and Hibernate are registered trademarks and servicemarks of Red Hat, Inc.