JPA @NamedQuery with bitwise AND (&) as criteria

Question

does anyone know how to use bitwise AND (&) as criteria for a JPA NamedQuery without having to use a @NamedNativeQuery?

I'm storing status bits in a field.

I'm using the following defs for an entity of a view:

@NamedQueries({
    @NamedQuery(name="ViewProductsList.findAll", query="SELECT v FROM ViewProductsList v"),
    @NamedQuery(name="ViewProductsList.findFeatured", query="SELECT v FROM ViewProductsList v WHERE v.statusId & 16"),
    @NamedQuery(name="ViewProductsList.findBySubcategory", query="SELECT v ViewProductsList v WHERE v.subcatId IN (:subcatIds)"),
})

The first one works fine, but the second does not like the expression, I get the following:

18:02:13,528 DEBUG [DataNucleus.Query] - JPQL Single-String with "SELECT v FROM ViewProductsList v WHERE v.statusId & 16"
18:02:13,553 DEBUG [DataNucleus.Query] - JPQL Query : Compiling "SELECT v FROM ViewProductsList v WHERE v.statusId & 16"
>>ERROR: 'Portion of expression could not be parsed: & 16'

Using dev MySQL database and Google Cloud SQL in production, so it would have to work on both versions...

Any ideas?

UPDATED QUESTION:

When using a defined @NamedNativeQuery as follows:

@NamedNativeQuery(name = "ViewProductsList.findFeatured", query = "SELECT * FROM view_products_list v")

And calling it as follows:

Query query=em.createNativeQuery("ViewProductsList.findFeatured", ViewProductsList.class);
List<ViewProductsList> list = query.getResultList();

I get the following error:

java.lang.IllegalStateException: You cannot invoke getResultList/getSingleResult when the Query is an UPDATE/DELETE
    at org.datanucleus.api.jpa.JPAQuery.getResultList(JPAQuery.java:161)
    at cultivartehidroponia.ProductsServlet.doGet(ProductsServlet.java:81) ...

I have tried many variations of createNativeQuery examples with no success. Does anybody know of working native query examples using datanucleus?

Again, using a normal @NamedQuery with createNamedQuery works fine!

Thanks in advance!!!

score 9 · Answer 1 · edited Feb 04 '14 at 09:27

9

Bitwise AND is not part of JPQL. But...

Can be use 2 rules:

division by 2^х will shift the bits right by x (exemple 1011010/1000=1011)
z mod 2 = 1 if right bit is 1 (exemple 1011 mod 2 = 1)

try

SELECT v FROM ViewProductsList v WHERE MOD(v.statusId/16 , 2) = 1

edited Feb 04 '14 at 09:27

MathieuF

3,130
5
31
34

answered Feb 04 '14 at 09:07

zoirs

591
5
6

although you are right with your answer and it nice way to do it. i think doing bitwise on sql will be much faster than mod and division – oak Mar 03 '14 at 12:15
May be I did not get your solution, but look like it not work. This queries should return same result, but it is different: `select day from (select 62 as day /*2 4 8 16 32*/ union all select 30 /*2 4 8 16*/ union all select 32 /*32*/ ) as t where day & 32` and `select day from (select 62 as day/*2 4 8 16 32*/ union all select 30/*2 4 8 16*/ union all select 32/* 32*/ ) as t where MOD(day/32, 2) = 1` – degr Nov 18 '20 at 21:01

score 2 · Answer 2 · answered Jan 21 '14 at 07:50

2

'Bitwise AND' is not part of JPQL. Native query is the portable way to go.

answered Jan 21 '14 at 07:50

DataNucleus

15,497
3
32
37

Thanks! I figured that much, but I was trying to avoid it because it returned all kinds of errors. I have updated my original questions addressing the use of native queries. Do you have any pointers? Regards! – SergioBrito Jan 21 '14 at 18:03
I have absolutely zero problem executing a named native query (nor does the JPA TCK). If you have a problem suggest you generate a valid testcase that demonstrates such a thing and report it on the site of software being used mentioning the versions being used (and if not a current version, then address why not) – DataNucleus Jan 24 '14 at 08:28

score 0 · Answer 3 · answered Aug 27 '21 at 14:28

Very late answer but nevertheless one... You could declare a function on your SQL side that performs bitwise operations (shifting and masking). For example (PostgreSQL):

CREATE OR REPLACE FUNCTION extractBits(bitmap bigint, bitOffset integer, bitMask bigint) RETURNS bigint
    LANGUAGE plpgsql IMMUTABLE
    AS $$
BEGIN
    return (bitmap >> bitOffset) & bitMask;
END;$$ RETURNS NULL ON NULL INPUT;

Then, if/when using JPA criteria builders you can use something like:

Expression<Long> bitmap = ...; // Path or expression to raw bitmap

Expression<Long> extracted = 
    builder.function("extractBits", Long.class, bitmap, shiftCount, bitMask);

Predicate predicate = builder.equal(extracted, desiredValue);

If you wish to use in JPQL it is a bit different but there are answers as to how to do it here. For example:

Calling an oracle function from JPA

That one has an answer with EclipseLink link: http://www.eclipse.org/eclipselink/documentation/2.5/jpa/extensions/j_func.htm

With Hibernate: https://thorben-janssen.com/database-functions/