Problem with string comparison which is actually a RAW field

[Marc Walter]

Hi all,

we have a raw database field in our Oracle database which is actually mapped to a string field in our JPA entity (StringPath in the Querydsl entity).
The (raw) database field contains hexadecimal strings like "AAAFFF". Now I would like to compare the field with a string like "00000A" which should return true because F is 15 and A is 10 and the corresponding bitset has matching bits: 1111 <=> 0101.
What I need is some sort of the functionality of the Oracle function UTL_RAW.BIT_AND.

Example:
SELECT UTL_RAW.BIT_AND('AA0B1CF', '00100A0') FROM DUAL;

Is there a way to achieve this comparison with Querydsl? Or does anybody know a workaround for the problem? Thank you very much for your help!

[Timo Westkämper]

Hi Marc.

Which JPA provider are you using?

Br,
Timo

--
Timo Westkämper
Mysema Oy
+358 (0)40 591 2172
www.mysema.com

[Marc Walter]

We are using OpenJPA 1.2.3 which comes with Websphere V7.

[Timo Westkämper]

Hi.

I don't know how to use custom functions in OpenJPA. In general you can use TemplateExpressions to create generic JPQL snippets, but Querydsl doesn't provide support to declare custom SQL functions for JPA.

Br,
Timo

[Marc Walter]

I'm stuck with this one.


Is it possible to do something like this?

andBuilder.and(true)

This would help me maybe. As I said, the "bitfeld" column is a RAW type which is mapped to String in my JPA entity. Now I could convert the String to BitSet in order to use the intersects() method.

andBuilder.and(convertToBitSet(entity.bitfield.toString()).intersects(searchCriteria.getBitSet()));

Don't know this would work.

[Timo Westkämper]

Hi.

Can you give me a bit more context? How does convertToBitSet look like and what is the type of andBuilder?

Br,
Timo

[Marc Walter]

Of course I can. andBuilder is of type BooleanBuilder.

This is the method convertToBitSet(String):

    private BitSet getBitSet(final String bitfield) {
        final char[] characters = bitfield.toUpperCase().toCharArray();
        final BitSet bitSet = new BitSet(characters.length * 4);
        int pos = 0;

        for (final char character : characters) {
            int value = 0;
            if (character < 'A') {
                value = character - '0';
            } else {
                value = character - 55;
            }
            for (int i = 3; i > -1; i--) {
                final boolean bit = (value % 2) == 1;
                value /= 2;
                bitSet.set(pos + i, bit);
            }
            pos = pos + 4;
        }
        return bitSet;
    }

[Marc Walter]

Thisis nonsense what I'm doing here. I think a subselect is what I need. Have got to try it...