In this blog post we will see various ways to store list of strings in single column in database using JPA.

• Storing list as comma separated strings
• Storing list in JSON format
• Storing list as array

i) Storing List as comma separated strings

The easiest way to store list of strings in a single database column is to store them as comma separated strings.

We define column in database as char or varchar

We use JPA 2.1 Attribute Converter feature to convert list of string to comma separated string while storing into database and vice versa while reading from the database.

 What is JPA Attribute Converter?

A class that implements AttributeConverter interface can be used to convert entity attribute state into database column representation and back again

Below converter class takes care of transformation from List to String and vice versa.

public class ListToStringConverter implements AttributeConverter<List<String>, String> {
    @Override
    public String convertToDatabaseColumn(List<String> attribute) {
        return attribute == null ? null : String.join(",",attribute);
    }

    @Override
    public List<String> convertToEntityAttribute(String dbData) {
        return dbData == null ? Collections.emptyList() : Arrays.asList(dbData.split(","));
    }
}Code language: Java (java)

The convertToDatabaseColumn method is called by the JPA provider prior to executing an INSERT or UPDATE statement.

The convertToEntityAttribute method is called by the JPA provider when executing SELECT statement by fetching an entity from the database, via a find method or when executing a JPQL or Criteria API query. 

Mapping the JPA AttributeConverter

To instruct the JPA provider to use a given AttributeConverter implementation, we can use the @Convert JPA annotation on the entity attribute.

public class User {
	@Id
	@GeneratedValue(strategy = GenerationType.SEQUENCE)
	private Long id;
	
	@Column(nullable=false)
	private String firstName;

	@Column(nullable=false)
	private String lastName;
	
	@Column
	@Convert(converter = ListToStringConverter.class)
	private List<String> phoneNumbers;

       ..........
}Code language: Java (java)

ii) Storing List as JSON

You can also store List of strings in the form JSON , if the database supports JSON datatype.

Using JPA, we can store object as JSON in 2 ways.

• Using hibernate-types project
• Using JPA attribute converter

Using hibernate-types project

For using hibernate-types library , first we need to add dependency in pom.xml file

<dependency>
	<groupId>com.vladmihalcea</groupId>
	<artifactId>hibernate-types-52</artifactId>
	<version>2.14.0</version>
</dependency>Code language: Java (java)

Next, we need to register the datatype with @TypeDef annotation then declare the datatype on attribute with @Type annotation.

@TypeDefs({
		
		@TypeDef(name = "json", typeClass = JsonBinaryType.class)
})
public class User {
	@Id
	@GeneratedValue(strategy = GenerationType.SEQUENCE)
	private Long id;

	.....

	@Column(columnDefinition = "json")
	@Type(type = "json")
	private List<String> preferences;
       
        ...
}Code language: Java (java)

Using JPA Attribute Converter

In this approach, we will write attribute converter to list to json and vice versa.

public class User {
	@Id
	@GeneratedValue(strategy = GenerationType.SEQUENCE)
	private Long id;

           ...

	@Column(columnDefinition = "json")
	@Convert(converter = ListToJsonConverter.class)
	private List<String> orgPreferences;
         ...
}Code language: Java (java)

package dev.fullstackcode.jpa.mapping.entity.converters;

import com.fasterxml.jackson.core.JsonProcessingException;
import com.fasterxml.jackson.core.type.TypeReference;
import com.fasterxml.jackson.databind.ObjectMapper;

import javax.persistence.AttributeConverter;
import java.util.List;

public class ListToJsonConverter implements AttributeConverter<List<String>, String> {

    static final ObjectMapper mapper = new ObjectMapper();

    @Override
    public String convertToDatabaseColumn(List<String> list) {
        if (list == null)
            return null;
        try {
            return mapper.writeValueAsString(list);
        } catch (JsonProcessingException e) {
            throw new RuntimeException(e);
        }
    }


    @Override
    public List<String> convertToEntityAttribute(String dbJson) {
        if (dbJson == null)
            return null;
        try {
            return mapper.readValue(dbJson, new TypeReference<List<String>>() {
            });
        } catch (JsonProcessingException e) {
            throw new RuntimeException(e);
        }
    }
}
Code language: Java (java)

In PostgreSQL, you will need to set special configuration to save String as json datat type

spring.datasource.url=jdbc:postgresql://localhost:5432/<DBName>?stringtype=unspecifiedCode language: Java (java)

If you run the save operation on entity without the above configuration , you will receive the below error.

ERROR: column "org_preferences" is of type json but expression is of type character varying

Some StackOverflow posts suggested writing attribute converter like below using PGobject to store JSON. The changes did not work for me.

public class ListToJsonConverter implements AttributeConverter<List<String>, PGobject> {

    static final ObjectMapper mapper = new ObjectMapper();

    @Override
    public PGobject convertToDatabaseColumn(List<String> list) {
        if (list == null)
            return null;
        try {
            PGobject pgobject = new PGobject();
            pgobject.setType("json");
            pgobject.setValue(mapper.writeValueAsString(list));
            return pgobject;
        } catch (JsonProcessingException | SQLException e) {
            throw new RuntimeException(e);
        }
    }


    @Override
    public List<String> convertToEntityAttribute(PGobject dbJson) {
        if (dbJson == null || dbJson.getValue() == null)
            return null;
        try {
            return mapper.readValue(dbJson.getValue()  , new TypeReference<List<String>>() {
            });
        } catch (JsonProcessingException e) {
            throw new RuntimeException(e);
        }
    }
}
Code language: Java (java)

If you perform save operation with above Attribute converter ,You will receive the below error message

ERROR: column "org_preferences" is of type json but expression is of type byteaCode language: Java (java)

iii) Storing List as Array

Postgres database supports Array data type for Strings and Numbers.So you can store the list of strings as array in the database.

We are going to use hibernate-types library to store list as array in database.

First we register the type with @TypeDef annotation then declare the datatype on attribute with @Type annotation

@TypeDefs({ @TypeDef( name = "list-array", typeClass = ListArrayType.class) })
public class User {
	@Id
	@GeneratedValue(strategy = GenerationType.SEQUENCE)
	private Long id;

         ....
   
	@Type(type = "list-array")
	@Column( name = "hobbies",columnDefinition = "text[]")
	private List<String> hobbies;

Code language: Java (java)

You can download complete source code for this blog post from Github

References

• https://github.com/vladmihalcea/hibernate-types