JPA query attribute with a converter-CodePudding

I have a SpringBoot 2.6.11 application with JPA 2.2. I have an entity like this:

@Data
@Entity
@Table(name = "entity")
public class Entity implements Serializable {

  ....

  @Convert(converter = ListConverter.class)
  private List<String> referenceCode;
  ....

}

I have this Converter:

@Converter(autoApply = true)
public class ListConverter implements AttributeConverter<List<String>, String> {

  @Override
  public String convertToDatabaseColumn(List<String> attribute) {
    return String.join(";", attribute);
  }

  @Override
  public List<String> convertToEntityAttribute(String dbData) {
    return new ArrayList<>(Arrays.asList(dbData.split(";")));
  }
}

And when I insert or extract this element all working fine. But now I wanna query that element and I don't know how to do it. If I do something like that:

public List<Entity> findByReferenceCode(String reference);

It doesn't work, if I do:

@Query("select e from Entity e where e.referenceCode IN ?1")
public List<Entity> findByReferenceCode(List<String> reference);

Still doesn't work..

The only way I found is by the nativeQuery but is really an extrema ratio. Ho can I solve this? Thank you

CodePudding user response：

To really do what you want here, you need to use an @ElementCollection. The reason being that there is no reliable way for JPA to query a single column and treat it as a collection. Reliably querying a collection requires a second table (which is what @ElementCollection does). You can continue to use the @Converter, but your queries will have to be customized to handle the disparity between the entity attribute type (list) and the actual database column type (string).

If you are okay with the limitations of the @Converter then it's fine (I have used them this way) but if you truly need to query the attribute like a collection (e.g. search for multiple independent items, perform counts, aggregations, etc) and you want those queries to be generated by a JPA layer, then you will have to use @ElementCollection and let it create a second table.