Hi Oleg,
Thanks for the heads-up. Interesting bit about parsing the money when
fetched from a JDBC ResultSet. Note, there's also
org.postgresql.util.PGmoney, which ships with the JDBC driver and contains
the following logic:
public void setValue(String s) throws SQLException
{
try
{
String s1;
boolean negative;
negative = (s.charAt(0) == '(') ;
// Remove any () (for negative) & currency symbol
s1 = PGtokenizer.removePara(s).substring(1);
// Strip out any , in currency
int pos = s1.indexOf(',');
while (pos != -1)
{
s1 = s1.substring(0, pos) + s1.substring(pos + 1);
pos = s1.indexOf(',');
}
val = Double.parseDouble(s1);
val = negative ? -val : val;
}
catch (NumberFormatException e)
{
throw new PSQLException(GT.tr("Conversion of money failed."),
PSQLState.NUMERIC_CONSTANT_OUT_OF_RANGE, e);
}
}
It looks like it doesn't take into account as many special cases (e.g.
) as your logic. On the other hand, there seems to be some
interesting logic for handling negative amounts...
Cheers,
Lukas
2015-06-08 12:11 GMT+02:00 Oleg Oleynik <[email protected]>:
> Yes, it works for me.
>
> Here are my code for PostgreSQL money type binding:
>
> <database>
> <name>org.jooq.util.postgres.PostgresDatabase</name>
> <includes>.*</includes>
> <inputSchema>public</inputSchema>
> <customTypes>
> <customType>
> <name>PgMoney</name>
> <type>java.math.BigDecimal</type>
> <binding>com.maxifier.noorm.PgMoneyBinding</binding>
> </customType>
> </customTypes>
> <forcedTypes>
> <forcedType>
> <name>PgMoney</name>
> <types>money</types>
> </forcedType>
> </forcedTypes>
> </database>
>
>
> public class PGMoneyConverter implements Converter<BigDecimal, BigDecimal> {
> @Override
> public BigDecimal from(BigDecimal databaseObject) {
> return new BigDecimal(databaseObject.toString());
> }
>
> @Override
> public BigDecimal to(BigDecimal userObject) {
> return userObject;
> }
>
> @Override
> public Class<BigDecimal> fromType() {
> return BigDecimal.class;
> }
>
> @Override
> public Class<BigDecimal> toType() {
> return BigDecimal.class;
> }
> }
>
>
>
> public class PgMoneyBinding implements Binding<BigDecimal, BigDecimal> {
> @Override
> public Converter<BigDecimal, BigDecimal> converter() {
> return new PGMoneyConverter();
> }
>
> @Override
> public void sql(BindingSQLContext<BigDecimal> ctx) throws SQLException {
>
> ctx.render().visit(DSL.val(ctx.convert(converter()).value())).sql("::money");
> }
> @Override
> public void register(BindingRegisterContext<BigDecimal> ctx) throws
> SQLException {
> ctx.statement().registerOutParameter(ctx.index(), Types.VARCHAR);
> }
>
>
> @Override
> public void set(BindingSetStatementContext<BigDecimal> ctx) throws
> SQLException {
> ctx.statement().setString(ctx.index(),
> Objects.toString(ctx.convert(converter()).value(), null));
> }
>
>
> @Override
> public void get(BindingGetResultSetContext<BigDecimal> ctx) throws
> SQLException {
> //value may be like '200 500.00 usd' so we must extract value
> String[] split = ctx.resultSet().getString(ctx.index()).split(" ");
> //whitespace is really, so we must replace \u00a0 character
> BigDecimal bigDecimal = new
> BigDecimal(split[0].replaceAll("\u00a0",""));
> ctx.convert(converter()).value(bigDecimal);
> }
>
>
> @Override
> public void get(BindingGetStatementContext<BigDecimal> ctx) throws
> SQLException {
>
> ctx.convert(converter()).value(ctx.statement().getBigDecimal(ctx.index()));
> }
>
>
> @Override
> public void set(BindingSetSQLOutputContext<BigDecimal> ctx) throws
> SQLException {
> throw new SQLFeatureNotSupportedException();
> }
>
> @Override
> public void get(BindingGetSQLInputContext<BigDecimal> ctx) throws
> SQLException {
> throw new SQLFeatureNotSupportedException();
> }
> }
>
>
> четверг, 4 июня 2015 г., 17:37:58 UTC+4 пользователь Lukas Eder написал:
>>
>> Interesting, looks like you've run into a bug / missing feature. I've
>> created an issue for this:
>> https://github.com/jOOQ/jOOQ/issues/4364
>>
>> In other databases, the money "type" is more or less just an alias for
>> decimal. It looks as though in PostgreSQL, it is more. It would make sense
>> for future versions of jOOQ to automatically bind SQL money types to JSR
>> 354 javax.money.MonetaryAmount.
>>
>> All that's missing to convert BigDecimal to PostgreSQL money is a cast.
>> You can follow the examples given in the manual showing how JSON data types
>> can be bound in PostgreSQL:
>>
>> http://www.jooq.org/doc/latest/manual/sql-building/queryparts/custom-bindings
>>
>> It should work the same way with money.
>>
>> Let me know if this works for you,
>> Lukas
>>
>> 2015-06-04 13:40 GMT+02:00 <[email protected]>:
>>
>>> Hi all!
>>>
>>> I'm trying to learn JOOQ and I get some strange behavior with PostgesSQL
>>> money type.
>>> JOOQ codegen generates BigDecimal field in class for money field in DB.
>>> But when I try to select and use that field in WHERE clause, there are
>>> exception.
>>>
>>> Here are table definition:
>>>
>>> CREATE TABLE "user"
>>> (
>>> id serial NOT NULL,
>>> username character varying NOT NULL,
>>> balance money,
>>> CONSTRAINT user_table_pkey PRIMARY KEY (id),
>>> CONSTRAINT user_table_age_fkey FOREIGN KEY (age)
>>> REFERENCES age_categories (name) MATCH SIMPLE
>>> ON UPDATE NO ACTION ON DELETE NO ACTION
>>> )
>>> WITH (
>>> OIDS=FALSE
>>> );
>>> ALTER TABLE "user"
>>> OWNER TO postgres;
>>>
>>>
>>> And here are my code throwing exception:
>>>
>>> Connection connection = DriverManager.getConnection(url, dbUser, dbPass);
>>> Settings settings = new Settings();
>>> settings.setParamType(ParamType.INLINED);
>>> DSLContext dslContext = PostgresDSL.using(connection, POSTGRES_9_4,
>>> settings);
>>> Result<UserRecord> fetch = dslContext.selectFrom(USER)
>>> .where(USER.BALANCE.gt(new BigDecimal(1000)))
>>> .limit(100)
>>> .fetch();
>>> System.out.println(fetch);
>>>
>>>
>>> Here are exception print:
>>>
>>> Exception in thread "main" org.jooq.exception.DataAccessException: SQL
>>> [select "public"."user"."id", "public"."user"."username",
>>> "public"."user"."age", "public"."user"."current_mood",
>>> "public"."user"."height", "public"."user"."friends_ids",
>>> "public"."user"."balance" from "public"."user" where
>>> "public"."user"."balance" > ? limit ?]; ERROR: operator does not exist:
>>> money > numeric
>>> Hint: No operator matches the given name and argument type(s). You
>>> might need to add explicit type casts.
>>> Position: 249
>>> at org.jooq.impl.Utils.translate(Utils.java:1644)
>>> at
>>> org.jooq.impl.DefaultExecuteContext.sqlException(DefaultExecuteContext.java:661)
>>> at org.jooq.impl.AbstractQuery.execute(AbstractQuery.java:356)
>>> at org.jooq.impl.AbstractResultQuery.fetch(AbstractResultQuery.java:290)
>>> at org.jooq.impl.SelectImpl.fetch(SelectImpl.java:2316)
>>> at com.maxifier.noorm.Test.main(Test.java:50)
>>> Caused by: org.postgresql.util.PSQLException: ERROR: operator does not
>>> exist: money > numeric
>>> Hint: No operator matches the given name and argument type(s). You
>>> might need to add explicit type casts.
>>> Position: 249
>>> at
>>> org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2270)
>>> at
>>> org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1998)
>>> at
>>> org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:255)
>>> at
>>> org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:570)
>>> at
>>> org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:420)
>>> at
>>> org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:413)
>>> at
>>> org.jooq.tools.jdbc.DefaultPreparedStatement.execute(DefaultPreparedStatement.java:194)
>>> at
>>> org.jooq.impl.AbstractResultQuery.execute(AbstractResultQuery.java:247)
>>> at org.jooq.impl.AbstractQuery.execute(AbstractQuery.java:342)
>>> ... 3 more
>>>
>>>
>>> Please tell me, I don't unserstand something and write bad code or that
>>> are a bug?
>>>
>>> --
>>> You received this message because you are subscribed to the Google
>>> Groups "jOOQ User Group" group.
>>> To unsubscribe from this group and stop receiving emails from it, send
>>> an email to [email protected].
>>> For more options, visit https://groups.google.com/d/optout.
>>>
>>
>> --
> You received this message because you are subscribed to the Google Groups
> "jOOQ User Group" group.
> To unsubscribe from this group and stop receiving emails from it, send an
> email to [email protected].
> For more options, visit https://groups.google.com/d/optout.
>
--
You received this message because you are subscribed to the Google Groups "jOOQ
User Group" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
For more options, visit https://groups.google.com/d/optout.
