JPA, Spring and Kotlin: store a list or an array in a column of the database

In some use cases could be useful to store a list of values in a column and not a relationship @OneToMany

This has to be considered an anti-pattern and should be applied only in specific cases.

Storing an array in a database column can have many negative consequences:

• the database cannot validate the values
• the column could be wrongly sized for the future amount of data stored

The advantages of storing a list / array of data in a column are:

• the ORM and the database require to manage one relationship less
• the query of one table should be more efficient that the query of a 1 to many relationship

Example with Kotlin

For our example we will use Kotlin, if you are a Java developer it should be easily understandable.

Converting a CSV String in a Set and the other way around

You need to use the @Converter feature of JPA:

@Converter 
class StringSetColumnConverter : AttributeConverter<Set<String>, String> { 
 
private val delimiter = "," 
 
override fun convertToDatabaseColumn(attribute: Set<String>?): String? { 
return attribute?.joinToString(delimiter) 
} 
 
override fun convertToEntityAttribute(dbData: String?): Set<String> { 
return dbData?.split(delimiter)?.toSet() ?: emptySet() 
} 

Create unit tests

The test show how easy is to use them and the expected result:

class StringSetColumConverterTest { 
private val stringSetColumnConverter = StringSetColumnConverter() 
 
@Test 
fun concertSetToColumnString() { 
assertEquals("abd,def", stringSetColumnConverter.convertToDatabaseColumn(setOf("abc","def"))) 
} 
 
@Test 
fun convertStringColumnToSet() { 
assertEquals(setOf("abc", "def"), stringSetColumnConverter.convertToEntityAttribute("abc,def")) 
} 
} 

The @Entity declaration

The @Entity that requires the conversion can be declared:

@Entity 
@Table(name = "MyTable") 
class MyTable( 
@Id 
val id: Long, 
@Convert(converter = StringSetColumnConverter::class) 
val words: Set<String>? 
) 

You table can simply use a Varchar field.