PostgreSQL Money type :: how to map it to Hibernate JPA?

3
votes

I seem to be having problems mapping the PostgreSQL money type to the java.util.Currency type using the following JPA snippet:

@Basic(optional = true)                                 
@Column(name = "cafl_bubgeted_amount")
private Currency bubgetedAmount;

column cafl_budgeted_amount is of type money in PostgreSQL.

When the code is deployed in JBoss AS 7.1.1 I get the trace shown at the end of the post. Has anybody handled that mapping ?

trace when deployed in JBoss AS 7.1.3 

Caused by: java.lang.IllegalArgumentException
    at java.util.Currency.getInstance(Currency.java:303) [rt.jar:1.7.0_07]
    at java.util.Currency.getInstance(Currency.java:284) [rt.jar:1.7.0_07]
    at org.hibernate.type.descriptor.java.CurrencyTypeDescriptor.wrap(CurrencyTypeDescriptor.java:66) [hibernate-core-4.0.1.Final.jar:4.0.1.Final]
    at org.hibernate.type.descriptor.java.CurrencyTypeDescriptor.wrap(CurrencyTypeDescriptor.java:35) [hibernate-core-4.0.1.Final.jar:4.0.1.Final]
    at org.hibernate.type.descriptor.sql.VarcharTypeDescriptor$2.doExtract(VarcharTypeDescriptor.java:66) [hibernate-core-4.0.1.Final.jar:4.0.1.Final]
    at org.hibernate.type.descriptor.sql.BasicExtractor.extract(BasicExtractor.java:65) [hibernate-core-4.0.1.Final.jar:4.0.1.Final]
    at org.hibernate.type.AbstractStandardBasicType.nullSafeGet(AbstractStandardBasicType.java:269) [hibernate-core-4.0.1.Final.jar:4.0.1.Final]
    at org.hibernate.type.AbstractStandardBasicType.nullSafeGet(AbstractStandardBasicType.java:265) [hibernate-core-4.0.1.Final.jar:4.0.1.Final]
    at org.hibernate.type.AbstractStandardBasicType.nullSafeGet(AbstractStandardBasicType.java:238) [hibernate-core-4.0.1.Final.jar:4.0.1.Final]
    at org.hibernate.type.AbstractStandardBasicType.hydrate(AbstractStandardBasicType.java:357) [hibernate-core-4.0.1.Final.jar:4.0.1.Final]
    at org.hibernate.persister.entity.AbstractEntityPersister.hydrate(AbstractEntityPersister.java:2695) [hibernate-core-4.0.1.Final.jar:4.0.1.Final]
    at org.hibernate.loader.Loader.loadFromResultSet(Loader.java:1552) [hibernate-core-4.0.1.Final.jar:4.0.1.Final]
    at org.hibernate.loader.Loader.instanceNotYetLoaded(Loader.java:1484) [hibernate-core-4.0.1.Final.jar:4.0.1.Final]
    at org.hibernate.loader.Loader.getRow(Loader.java:1384) [hibernate-core-4.0.1.Final.jar:4.0.1.Final]
    at org.hibernate.loader.Loader.getRowFromResultSet(Loader.java:640) [hibernate-core-4.0.1.Final.jar:4.0.1.Final]
    at org.hibernate.loader.Loader.doQuery(Loader.java:856) [hibernate-core-4.0.1.Final.jar:4.0.1.Final]
    at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:289) [hibernate-core-4.0.1.Final.jar:4.0.1.Final]
    at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:259) [hibernate-core-4.0.1.Final.jar:4.0.1.Final]
    at org.hibernate.loader.Loader.loadEntity(Loader.java:2058) [hibernate-core-4.0.1.Final.jar:4.0.1.Final]
    at org.hibernate.loader.entity.AbstractEntityLoader.load(AbstractEntityLoader.java:82) [hibernate-core-4.0.1.Final.jar:4.0.1.Final]
    at org.hibernate.loader.entity.AbstractEntityLoader.load(AbstractEntityLoader.java:72) [hibernate-core-4.0.1.Final.jar:4.0.1.Final]
    at org.hibernate.persister.entity.AbstractEntityPersister.load(AbstractEntityPersister.java:3697) [hibernate-core-4.0.1.Final.jar:4.0.1.Final]
    at org.hibernate.event.internal.DefaultLoadEventListener.loadFromDatasource(DefaultLoadEventListener.java:439) [hibernate-core-4.0.1.Final.jar:4.0.1.Final]
    at org.hibernate.event.internal.DefaultLoadEventListener.doLoad(DefaultLoadEventListener.java:420) [hibernate-core-4.0.1.Final.jar:4.0.1.Final]
    at org.hibernate.event.internal.DefaultLoadEventListener.load(DefaultLoadEventListener.java:204) [hibernate-core-4.0.1.Final.jar:4.0.1.Final]
    at org.hibernate.event.internal.DefaultLoadEventListener.proxyOrLoad(DefaultLoadEventListener.java:251) [hibernate-core-4.0.1.Final.jar:4.0.1.Final]
    at org.hibernate.event.internal.DefaultLoadEventListener.onLoad(DefaultLoadEventListener.java:148) [hibernate-core-4.0.1.Final.jar:4.0.1.Final]
    at org.hibernate.internal.SessionImpl.fireLoad(SessionImpl.java:954) [hibernate-core-4.0.1.Final.jar:4.0.1.Final]
    at org.hibernate.internal.SessionImpl.internalLoad(SessionImpl.java:903) [hibernate-core-4.0.1.Final.jar:4.0.1.Final]
    at org.hibernate.type.EntityType.resolveIdentifier(EntityType.java:610) [hibernate-core-4.0.1.Final.jar:4.0.1.Final]
    at org.hibernate.type.EntityType.resolve(EntityType.java:438) [hibernate-core-4.0.1.Final.jar:4.0.1.Final]
    at org.hibernate.engine.internal.TwoPhaseLoad.initializeEntity(TwoPhaseLoad.java:150) [hibernate-core-4.0.1.Final.jar:4.0.1.Final]
    at org.hibernate.loader.Loader.initializeEntitiesAndCollections(Loader.java:1006) [hibernate-core-4.0.1.Final.jar:4.0.1.Final]
    at org.hibernate.loader.Loader.doQuery(Loader.java:883) [hibernate-core-4.0.1.Final.jar:4.0.1.Final]
    at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:289) [hibernate-core-4.0.1.Final.jar:4.0.1.Final]
    at org.hibernate.loader.Loader.doList(Loader.java:2463) [hibernate-core-4.0.1.Final.jar:4.0.1.Final]
    at org.hibernate.loader.Loader.doList(Loader.java:2449) [hibernate-core-4.0.1.Final.jar:4.0.1.Final]
    at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2279) [hibernate-core-4.0.1.Final.jar:4.0.1.Final]
    at org.hibernate.loader.Loader.list(Loader.java:2274) [hibernate-core-4.0.1.Final.jar:4.0.1.Final]
    at org.hibernate.loader.hql.QueryLoader.list(QueryLoader.java:470) [hibernate-core-4.0.1.Final.jar:4.0.1.Final]
    at org.hibernate.hql.internal.ast.QueryTranslatorImpl.list(QueryTranslatorImpl.java:355) [hibernate-core-4.0.1.Final.jar:4.0.1.Final]
    at org.hibernate.engine.query.spi.HQLQueryPlan.performList(HQLQueryPlan.java:196) [hibernate-core-4.0.1.Final.jar:4.0.1.Final]
    at org.hibernate.internal.SessionImpl.list(SessionImpl.java:1115) [hibernate-core-4.0.1.Final.jar:4.0.1.Final]
    at org.hibernate.internal.QueryImpl.list(QueryImpl.java:101) [hibernate-core-4.0.1.Final.jar:4.0.1.Final]
    at org.hibernate.ejb.QueryImpl.getResultList(QueryImpl.java:252) [hibernate-entitymanager-4.0.1.Final.jar:4.0.1.Final]
    at gr.neuropublic.gaia.cashflow.services.TransactionGroupService.copyTransactionGroups(TransactionGroupService.java:29) [cashflow-ejb.jar:]
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) [rt.jar:1.7.0_07]
    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57) [rt.jar:1.7.0_07]
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) [rt.jar:1.7.0_07]
    at java.lang.reflect.Method.invoke(Method.java:601) [rt.jar:1.7.0_07]
    at org.jboss.as.ee.component.ManagedReferenceMethodInterceptorFactory$ManagedReferenceMethodInterceptor.processInvocation(ManagedReferenceMethodInterceptorFactory.java:72) [jboss-as-ee-7.1.1.Final.jar:7.1.1.Final]
    at org.jboss.invocation.InterceptorContext.proceed(InterceptorContext.java:288) [jboss-invocation-1.1.1.Final.jar:1.1.1.Final]
    at org.jboss.invocation.WeavedInterceptor.processInvocation(WeavedInterceptor.java:53) [jboss-invocation-1.1.1.Final.jar:1.1.1.Final]
    at org.jboss.as.ee.component.interceptors.UserInterceptorFactory$1.processInvocation(UserInterceptorFactory.java:36) [jboss-as-ee-7.1.1.Final.jar:7.1.1.Final]
    at org.jboss.invocation.InterceptorContext.proceed(InterceptorContext.java:288) [jboss-invocation-1.1.1.Final.jar:1.1.1.Final]
    at org.jboss.as.jpa.interceptor.SBInvocationInterceptor.processInvocation(SBInvocationInterceptor.java:47) [jboss-as-jpa-7.1.1.Final.jar:7.1.1.Final]
    at org.jboss.invocation.InterceptorContext.proceed(InterceptorContext.java:288) [jboss-invocation-1.1.1.Final.jar:1.1.1.Final]
    at org.jboss.invocation.InitialInterceptor.processInvocation(InitialInterceptor.java:21) [jboss-invocation-1.1.1.Final.jar:1.1.1.Final]
    at org.jboss.invocation.InterceptorContext.proceed(InterceptorContext.java:288) [jboss-invocation-1.1.1.Final.jar:1.1.1.Final]
    at org.jboss.invocation.ChainedInterceptor.processInvocation(ChainedInterceptor.java:61) [jboss-invocation-1.1.1.Final.jar:1.1.1.Final]
    at org.jboss.as.ee.component.interceptors.ComponentDispatcherInterceptor.processInvocation(ComponentDispatcherInterceptor.java:53) [jboss-as-ee-7.1.1.Final.jar:7.1.1.Final]
    at org.jboss.invocation.InterceptorContext.proceed(InterceptorContext.java:288) [jboss-invocation-1.1.1.Final.jar:1.1.1.Final]
    at org.jboss.as.ejb3.component.pool.PooledInstanceInterceptor.processInvocation(PooledInstanceInterceptor.java:51) [jboss-as-ejb3-7.1.1.Final.jar:7.1.1.Final]
    at org.jboss.invocation.InterceptorContext.proceed(InterceptorContext.java:288) [jboss-invocation-1.1.1.Final.jar:1.1.1.Final]
    at org.jboss.as.ejb3.tx.CMTTxInterceptor.invokeInOurTx(CMTTxInterceptor.java:228) [jboss-as-ejb3-7.1.1.Final.jar:7.1.1.Final]
    ... 54 more
2
hibernatepostgresqljpajboss7.x
MONEY is really painful to work with, especially with tools designed only for generic tools. I strongly recommend switching to NUMERIC. – Craig Ringer
Er "tools designed to work with the lowest common denominator of feature support in SQL databases". 1st effort is kind of gibberish, sorry. – Craig Ringer
BTW, if you need to you can probably write a custom mapping. These are provider specific, so see the Hibernate docs for how to implement an appropriate converter. – Craig Ringer

2 Answers

5
votes

A java.util.Currency represents a currency, like USD or EUR, not an actual amount like 456.34 from Postgres MONEY. The exception is because it's trying to parse the number as a currency code.

4
votes

I highly recommend avoiding MONEY currently and going with NUMERIC. There are a number of specific issues. It doesn't perform as well for arithmetic as NUMERIC does and the currency it represents is dependent on your localization settings, which means that as soon as you need multiple currencies, bad things happen. Worse still, two different users with different localization settings will see the same amount but a different currency specified.....

Additionally DECIMAL and NUMERIC are better supported cross-db than is MONEY (but DECIMAL is just a one-way alias for NUMERIC in PostgreSQL) so going lowest common denominator suggests going with a more generic, losslessly base 10 floating point number rather than a specialized monetary amount that has interesting corner cases and gotchas.