How to map Joda Money with org.jadira.usertype.moneyandcurrency.joda.PersistentMoneyAmountAndCurrency type in Hibernate?

Question

Trying this:

@Type(type = "org.jadira.usertype.moneyandcurrency.joda.PersistentMoneyAmountAndCurrency")
private org.joda.money.Money price;

Getting this:

org.hibernate.MappingException: property mapping has wrong number of columns:domain.ClientOrderItem.price type: org.jadira.usertype.moneyandcurrency.joda.PersistentMoneyAmountAndCurrency

@Type(type = "org.jadira.usertype.moneyandcurrency.joda.PersistentMoneyAmount",
parameters = {@org.hibernate.annotations.Parameter(name = "currencyCode", value = "USD")})

Works nice, but I want to store currency in database and be able to use different currencies.

Answer 1

There's a working example from Jadira Usertype Unit Tests

    @Entity
    @Table(name = "moneyAmountAndCurrency")
    @TypeDef(name = "testjoda_MoneyAmountWithCurrencyType", typeClass = PersistentMoneyAmountAndCurrency.class)
    public class MoneyAmountAndCurrencyHolder implements Serializable {

    private static final long serialVersionUID = -1674416082110551506L;

    @Columns(columns = { @Column(name = "MY_CURRENCY"), @Column(name = "MY_AMOUNT") })
    @Type(type = "testjoda_MoneyAmountWithCurrencyType")
    private Money money;

Answer 2

Introduction

This is actually not different from the answer given by Chris Pheby. I'm just providing a version of his answer without the @TypeDefs, etc, for two scenaria:

1. Currency stored as a CHAR(3) in MySQL for better readability of data by humans using the 3-letter ISO-4217 code
2. Currency stored as a SMALLINT in MySQL for marginally increased efficiency using the 3-digit ISO-4217 number

In both cases, the amount component of the Money field is stored as a DECIMAL(9,2) which requires 5 bytes of storage for most RDBMSs. You could, of course, use any other exact datatype in order to avoid possible precision issues that come with double/float.

Scenario 1 (3-letter currency representation)

My fictional Payment @Entity looks like this for scenario (1):

package com.fictional;

import org.hibernate.annotations.Columns;
import org.hibernate.annotations.Type;
import org.joda.money.CurrencyUnit;
import org.joda.money.Money;

import javax.persistence.Column;
import javax.persistence.Entity;

/**
 * A fictional payment.
 */
@Entity
public class Payment {

    /**
     * Paid money.
     */
    @Columns(columns = {@Column(name = "paidMoneyCurrency"), @Column(name = "paidMoneyAmount")})
    @Type(type = "org.jadira.usertype.moneyandcurrency.joda.PersistentMoneyAmountAndCurrency")
    private Money paidMoney;


    /**
     * Sample construction of a money object belonging to a payment.
     */
    static public void testPayment()
    {
        Payment p = new Payment();

        p.paidMoney = Money.of(CurrencyUnit.EUR, 1234.56);

        // Hibernate persistence code to insert the new record
    }
}

The static method is just an example of instantiating such an object before submitting it to the persistence store.

The corresponding DDL for the database would be this:

CREATE TABLE Payment (
    paidMoneyCurrency CHAR(3) NOT NULL,
    paidMoneyAmount DECIMAL(9,2) NOT NULL
);

The INSERT statement generated by Hibernate looks like this:

INSERT INTO Payment (paidMoneyCurrency, paidMoneyAmount) VALUES ('EUR', 1234.56);

Scenario 2 (3-digit currency representation)

For scenario (2), you would only have to modify the @Type annotation of your paidMoney field to read PersistentMoneyAmountAndCurrencyAsInteger instead of PersistentMoneyAmountAndCurrency (the difference is the AsInteger suffix in the class name).

    // :
    // :
    @Type(type = "org.jadira.usertype.moneyandcurrency.joda.PersistentMoneyAmountAndCurrencyAsInteger")
    private Money paidMoney;
    // :
    // :

All the remaining code (even instantiation of the Money object in the static method) remains the same.

Then you would get the following DDL and corresponding INSERT statement generated by Hibernate (978 is the numeric code for EUR automatically calculated for you by joda money)

CREATE TABLE Payment (
    paidMoneyCurrency SMALLINT NOT NULL,
    paidMoneyAmount DECIMAL(9,2) NOT NULL
);

INSERT INTO Payment (paidMoneyCurrency, paidMoneyAmount) VALUES (978, 1234.56);