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.