How Java JPA Specification works with bit operations

Question

I'm trying to run some bit operations in the specification but there is few documentations.

For instance, I have a table tb_user with a bit(32) field roles. I'm trying to search for records that has specific flipped bits (bit = 1). Let's say if I search for b'1011' and the result should return all the records that have bit0 or bit1 or bit3 flipped. If I search for b'0011' and the result should return records that have bit0, bit1 flipped.

How can I do that in JPA Specification?

    public static Specification<TbUser> role(Integer roles) {
    return ObjectUtils.isEmpty(roles) ?
            (root, query, builder) -> builder.conjunction() :
            (root, query, builder) -> {
            // How do I run the bit operations?
           
    }
}

Answer 1

On my end

builder.function("&", Integer.class, root.get("role"), 4);

would trigger an error

java.sql.SQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '&(tb_user0_.role, 524290)>0 or &(tb_user0_.role, 524290)<0)' at line 1

So I can't directly use "&" in the specification.

The other method that works is below:

// customize a MySQL5Dialect with new registered function
public class ExtendedMySQL5Dialect extends MySQL5Dialect {
    public ExtendedMySQL5Dialect() {
        super();
        // added bitwise operators
        registerFunction("bit_and", new SQLFunctionTemplate(IntegerType.INSTANCE, "(?1 & ?2)"));
        registerFunction("bit_or", new SQLFunctionTemplate(IntegerType.INSTANCE, "(?1 | ?2)"));
        registerFunction("bit_xor", new SQLFunctionTemplate(IntegerType.INSTANCE, "(?1 ^ ?2)"));
    }
}

// change your yaml or property file
spring:
  application:
  name: user-service
  jpa:
    hibernate:
      ddl-auto: update
    database-platform: com.package.path.ExtendedMySQL5Dialect

//implementation of specification
public static Specification<TbUser> role(Integer roles) {
    return ObjectUtils.isEmpty(roles) ?
        (root, query, builder) -> builder.conjunction() :
        (root, query, builder) -> {
            Expression<Integer> bitwiseAnd = builder.function("bit_and", Integer.class, root.get("role"), 4); 
            return builder.greaterThan(bitwiseAnd, 0);
       
        }
}