0

In spring boot using JPQL how to use bitwise operator.

select * from myTable where col1&3600 = 2222

I also see this old post

JPA @NamedQuery with bitwise AND (&) as criteria

gives a way for using MOD but it gives only way to check 1 bit.

Here col1 has integer value. Example :

Col1
-----
1111111
1010010
1101110

My query is dynamic like I want to know (1,4,6) or (1,2,4,7) or any set of position bit is on or not. That's why I am thinking from set of position I will build a integer where position bit's are on like.

(1,4,6)  - 0101001
(1,2,4,7)- 1001011

and do bitwise operation with col1 lik col1 & myBitmask > 0 then I can fetch data where have atleast one position from the searching position set have bit 1.

1111111 & 0101001 = 0101001
1010010 & 0101001 = 0000000
1101110 & 0101001 = 0101000

So I will get 1st and 3rd data only.

Now their is any better way ?

Eklavya
  • 17,618
  • 4
  • 28
  • 57
  • How would you do that with SQL? – Simon Martinelli Jun 11 '20 at 06:22
  • I don't know JPQL in particular, but bitwise AND is a pretty standard operation across different languages, (it implemented in the hardware.) I'm not sure whether you are asking how bitwise AND works, or if you are asking how to perform MOD with bitwise AND or something else. – Ryan1729 Jun 11 '20 at 06:23
  • 2
    Does this answer your question? [JPA @NamedQuery with bitwise AND (&) as criteria](https://stackoverflow.com/questions/21246772/jpa-namedquery-with-bitwise-and-as-criteria) – Ryan1729 Jun 11 '20 at 06:26
  • @Ryan1729 the given solution for only check 1 bit – Eklavya Jun 11 '20 at 07:07
  • @SimonMartinelli Like `WHERE (sc.col1& 300) = 222` – Eklavya Jun 11 '20 at 07:09
  • `300` in binary is `100101100`, `222` is `11011110`. The expression you have there will never be true since `222` has bits set that are not set in `300`. I don't know which one bit you want to check. But the usual way to check that a bit is set is to AND with a power of two, (which means the number that has a single `1` in binary) and check that the answer matches the number you ANDed with. So `WHERE (sc.col1& 2) = 2` is true if the value of `sc.col1` has the second bit from the right set (like `222` does), and it is false otherwise, (like if it was `300`.) – Ryan1729 Jun 11 '20 at 09:55
  • @Ryan1729 I updated exaplanation. Be clear I give an example not exact data. I want to do `&` operation. – Eklavya Jun 11 '20 at 11:59
  • I'm still not totally sure I understand. Are you saying that `col1 & myBitmask > 0` does what you want, but you are looking for a better way? If so, better how? Do you want a way where you don't have to build the bitmask yourself? I'm not aware of a way like that. – Ryan1729 Jun 11 '20 at 19:42
  • @Ryan1729 `col1 & myBitmask > 0` currently immposible right either mod opration 8 times. I don't want bitmask I can do that. I want better way than mod operation 8 times. – Eklavya Jun 11 '20 at 19:52
  • @Ryan1729 Do you understand mod solution is not same as `col1 & myBitmask > 0` means `&` of two value that make sense – Eklavya Jun 11 '20 at 19:54
  • If JPQL doesn't allow bitwise operations, I'm not aware of any better way. – Ryan1729 Jun 12 '20 at 01:41

0 Answers0