Persisting key value pairs with JPA and hibernate

In previous articles, we’ve already covered how to set up databases and tables. The tables set up in that article were, however, following a very strict structure, which is generally the case.

In this article, we will cover how to set up a table that will store a Map. While it’s not directly completely unstructured data, it is a form that is not easy to scheme.

• Example description
• Code setup
• Liquibase generation
• Created tables

Example description

Usecase

The example in question will be for a currency exchange, covering over a hundred different currencies, based on a day-by-day basis. You can imagine that it doesn’t really make sense to create a class with that many columns, each covering only one currency. Instead, we will store the data in a Map, which will then be persisted.

Endpoint description

The information will be retrieved from the European Central Bank, calling it through an API layer. The endpoint in question is https://api.apilayer.com/exchangerates_data, which will require an API key, if you wish to follow along.

Of course, you can simply instantiate a HashMap with some data and store that.

Code setup

Entity

As mentioned above, the entity will be for a currency exchange. The data will be loaded once daily, so the logical ID could be the date for example. That would result in a class as such:

@Entity
@Table(name = "currency_exchange")
data class CurrencyExchange(
    @Id val date: LocalDate,
    @ElementCollection
    @CollectionTable(name = "currency_mapping", joinColumns = [JoinColumn(name = "date")])
    @MapKeyColumn(name = "currency_conversion")
    @Column(name = "value")
    val currencyRates: Map<String, Double>
)

Okay, now, what do all those annotations on the Map actually mean?

@ElementCollection

This is an easy one. It simply notifies JPA that the type of the variable is a Collection. It will use the following annotations to gather information about how to map the table.

@CollectionTable

This annotation defines how the data is to be stored in the actual table. In the example above, we state the following items:

• The table name is currency_mapping
• It joins the currency_exchange on the data field

So now we already know that JPA will expect two tables.

@MapKeyColumn

This annotation defines what the column containing the key of the Map will be called. We can consider this to be the second element of the Composite Key, along with the date, that will be used to look up the data.

@Column

This is the value of the Map for the key.

Repository

The repository that the entities will be stored in is a fairly simple one, as it defines the entity and the ID type.

@Repository
interface CurrencyRepository : JpaRepository<CurrencyExchange, LocalDate> {
}

Data retrieval

Of course, we also need a way to populate the table. For this, we need a DTO, a Gateway to retrieve the data, and a Service to handle it.

data class CurrencyLookupDto(val base: String, val date: LocalDate, val rates: Map<String, Double>)

@Component
class CurrencyGateway @Inject constructor(private val currencyConversionWebClient: WebClient) {

    fun getRates(): Flux<CurrencyExchange> {
        return currencyConversionWebClient.get().uri { uriBuilder ->
            uriBuilder
                .path("/latest")
                .queryParam("base", "usd")
                .build()
        }
            .retrieve()
            .bodyToFlux(CurrencyLookupDto::class.java)
            .map { it.toDomain() }
            .share()
    }
}

@Component
class CurrencyService @Inject constructor(private val currencyRepository: CurrencyRepository, private val currencyGateway: CurrencyGateway) {

    @Transactional
    fun storeConversionRates(): CurrencyExchange {
        val rates = currencyGateway.getRates().collectList().block()!![0]
        currencyRepository.save(rates)
        return rates
    }

    fun getConversionRate(currency: Currency): Double {
        return currencyRepository.getById(LocalDate.now()).currencyRates[currency.currencyCode]!!
    }
}

More information on how to set up Gateways can be found here.

Liquibase generation

That’s pretty much already everything set up, code-wise! Of course, the tables do not magically get generated, and will require some nudging to be set up. Since we’ve used liquibase for that purpose before, we will do so again.

The tables that are described above will be generated as so with liquibase:

<?xml version="1.0" encoding="UTF-8"?>
<databaseChangeLog
        xmlns="http://www.liquibase.org/xml/ns/dbchangelog"
        xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
        xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog
                      http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-4.5.xsd"
        objectQuotingStrategy="QUOTE_ONLY_RESERVED_WORDS">
    <changeSet id="1660482661374-7" author="cedric (generated)">
        <createTable tableName="currency_exchange">
            <column name="date" type="DATE">
                <constraints nullable="false" primaryKey="true" primaryKeyName="pk_currency_exchange"/>
            </column>
        </createTable>
    </changeSet>
    <changeSet id="1660482661374-8" author="cedric (generated)">
        <createTable tableName="currency_mapping">
            <column name="date" type="DATE">
                <constraints nullable="false" primaryKey="true" primaryKeyName="pk_currency_mapping"/>
            </column>
            <column name="value" type="DOUBLE"/>
            <column name="currency_conversion" type="VARCHAR(255)">
                <constraints nullable="false" primaryKey="true" primaryKeyName="pk_currency_mapping"/>
            </column>
        </createTable>
    </changeSet>
    <changeSet id="1660482661374-17" author="cedric (generated)">
        <addForeignKeyConstraint baseColumnNames="date" baseTableName="currency_mapping"
                                 constraintName="fk_currency_mapping_on_currency_exchange" referencedColumnNames="date"
                                 referencedTableName="currency_exchange"/>
    </changeSet>
</databaseChangeLog>

We can see the following items in the changelog:

• Creates a table currency_exchange containing only the date
• Creates a table currency_mapping with a primary key of (date, currency_conversion)
• Creates a foreign key of the date in the first table for the second table

Created tables

After running the liquibase scripts, and filling the table once, we can see the following information.

Now, to look at the actual data:

For the data in the other table, there’s actually already too many entries for a sensible picture. In fact, there are already 334 entries after only two days. However, a small sample is below (the statement may be a little wonky, but I didn’t want the image to become too large). The base for the conversion is USD, so that value will always be 1.

Interestingly, we can see that the value of the Euro has dropped quite sharply in this short span of time. Of course, you may be pleased or disappointed with that development, but the main point is that we are now able to simply store the currency exchange values with a Map! If our interface adds or removes additional currencies, the functionality will not break, but instead simply add that currency starting that day!