JPA, Spring and Kotlin: store a list or an array in a column of the database
source link: https://marco.dev/java-jpa-arrays
Go to the source link to view the article. You can view the picture content, updated content and better typesetting reading experience. If the link is broken, please click the button below to view the snapshot at that time.
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.
Recommend
About Joyk
Aggregate valuable and interesting links.
Joyk means Joy of geeK