Build a JPQL on a BLOB with Spring Data JPA

Question

Im working with an external service which POSTs to an endpoint in my application where i have a @RequestBody CalloutRequest calloutRequest where i map the request body to the Class CalloutRequest.

As part of that CalloutRequest is a field called userAttributes which looks like this in my class:

@Lob    
private HashMap<String, List<String>> userAttributes;

I have it annotated wit @Lob because i have no class that i can map it to due to it's rather complex nature. Therefore this HashMap. An example of what userAttributes looks like this when in JSON format:

  "userAttributes": {
    "lastName": [
      "Guy"
    ],
    "ExternalId": [
      "d9a59407-2bca-46aa-8641-3350fd95bcd1"
    ],
    "distinguishedName": [
      "CN=t2-contractor9,DC=company,DC=com"
    ],
    "employeeID": [],
    "userName": [
      "t2-contractor9@company.com"
    ],
    "groupNames": [
      "ALL USERS",
      "T2-Contractor@company.com"
    ],
    "firstName": [
      "T2-Contractor"
    ],
    "UserStore": [],
    "phone": [],
    "domain": [
      "company.com"
    ],
    "disabled": [
      "false"
    ],
    "email": [
      "t2-contractor9@company.com"
    ]
  }

My challenge is that i would like to build a query for a particular username - userAttributes.userName - but given the fact that this is stored as a BLOB i cannot figure out how - or if at all possible - to write such a query.
Right now i am simply using Spring Data JPA and it's repository implementation but im wondering if this is something i can achieve with a custom @Query or perhaps using querydsl?

Am i correct in understanding that if i could figure out a way to map the above HashMap into an actual class i could use to serialise/de-serialise and have a mapping between a CalloutRequest and this new class i could build a JPQL query that would use a join to achieve what i am after?

Answer 1

What about leveraging an AttributeConverter implementation here:

public class YourEntity {
  // ...
  @ElementCollection
  @Convert(name = "value", converter = StringListToStringConverter.class)
  private Map<String, List<String>> userAttributes;
}

A simple implementation of StringListToStringConverter might be:

public class StringListToStringConverter 
       implements AttributeConverter<List<String>, String> {
    private static final String DELIMITER = "|";

    @Override
    public String converToDatabaseColumn(List<String> attributes) {
      if ( attributes == null || attributes.isEmpty() ) {
        return null;
      }

      StringBuilder sb = new StringBuilder();
      for ( Iterator<String> i = attribtues.iterator(); i.hasNext(); ) {
        sb.append( i.next() );
        if ( i.hasNext() ) {
          sb.append( DELIMITER );
        }
      }
      return sb.toString();
    }

    @Override 
    public List<String> convertToEntityAttribute(String data) {
      if ( data == null ) {
        return new ArrayList<String>();
      }

      List<String> values = Arrays.asList( StringHelper.split( DELIMITER, data ) );         
      return new ArrayList<String>( values );
    }
}

You would obviously have to handle situations where the input stream contained your DELIMITER value of sorts, but it might work depending on your use case.

I believe you can then use HQL to write your query like:

SELECT e FROM YourEntity e JOIN e.userAttributes a 
WHERE VALUE(a) = :mapValue