JPA: Saving List of values as comma separated values

Question

I am receiving a simple list of values part of JSON request which I want to save as comma separated values. Tried using following but it did not work.

@Column(nullable = true)
@GeneratedValue(strategy = GenerationType.AUTO)
private ArrayList<String> services = new ArrayList<String>() ;

and

@Column(nullable = true)
@ElementCollection(targetClass = String.class)
private List<String> services = new ArrayList<String>() ;

@ElementCollection threw an exception saying table services does not exist.

ElementCollection still stores the collection in a separate table, with one string per row. I don't know where you read that it stored elements as comma-separated values. You'll need a custom type, or a JPA attribute converted. Also, annotating a list of strings with GeneratedValue doesn't make any sense. — JB Nizet, Nov 06 '15 at 17:05
you can try AttributeConverters as alternative: http://hantsy.blogspot.com/2013/12/jpa-21-attribute-converter.html — Ish, Nov 06 '15 at 17:50

score 10 · Accepted Answer · answered Nov 06 '15 at 17:27

10

The @ElementCollection requires a table to store multiples rows of values,

So you could define as a String column and join/explode in getters and setters, like this

private String services;

public setServices(String services[]) //Can be Array or List
{
     // this.services = Iterate services[] and create a comma separated string or Use ArrayUtils
}

public String[] getServices() //Can be Array or List
{
    // services.split(",") to get a list of Strings, then typecast/parse them to Strings before returning or use Arrays.asList(arguments.split(","));
}

answered Nov 06 '15 at 17:27

Túlio Castro

1,313
10
17

It is also possible to store values into a transient list, and convert it to and from a string value only when writing/reading to DB. See [Entity listeners](https://docs.jboss.org/hibernate/entitymanager/3.5/reference/en/html/listeners.html) - this would improve performance a biť, as the conversion does not happen always when yoe read/write value to the entity. Or you may also use [AttributeConverter](http://hantsy.blogspot.com/2013/12/jpa-21-attribute-converter.html), as suggested by @Ish – OndroMih Nov 07 '15 at 09:58

pedorro · Answer 2 · 2021-01-06T20:07:09.617

As mentioned by others in the comments an AttributeConverter works pretty well. This one uses Jackson to serialize as a JSON array. I recommend JSON since it cleanly handles delimiter escaping, nulls, quotes, etc.:

@Converter
public class StringListAttributeConverter implements AttributeConverter<List<String>, String> {

    private static final TypeReference<List<String>> TypeRef = new TypeReference<List<String>>(){};

    @Override
    public String convertToDatabaseColumn (List<String> attribute) {
        if (attribute == null) {
            return null;
        }
        try {
            return ObjectMapperFactory.getInstance().writeValueAsString(attribute);
        }
        catch (IOException ex) {
            throw new UncheckedIOException(ex);
        }
    }

    @Override
    public List<String> convertToEntityAttribute (String dbData) {
        if (dbData == null) {
            return null;
        }
        try {
            return ObjectMapperFactory.getInstance().readValue(dbData, TypeRef);
        }
        catch (IOException ex) {
            throw new UncheckedIOException(ex);
        }
    }
}

I've used this class and it works well in most cases. One caveat I've found is that using this converter can confuse some JPA criteria queries, because it expects a type List on the entity, but finds a String in the db.

score 4 · Answer 3 · answered Oct 22 '22 at 09:41

A more simple variant did the trick for me, no Jackson, but trimming strings:

public class CsvTrimmedStringsConverter implements AttributeConverter<List<String>, String> {
  @Override
  public String convertToDatabaseColumn(List<String> attribute) {
    return attribute == null
        ? null
        : attribute.stream().map(String::trim).collect(Collectors.joining(","));
  }

  @Override
  public List<String> convertToEntityAttribute(String dbData) {
    return dbData == null
        ? null
        : Arrays.stream(dbData.split(",")).map(String::trim).collect(Collectors.toList());
  }
}

JPA: Saving List of values as comma separated values

3 Answers3