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] <javascript:>>:
>
>> 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] <javascript:>.
>> 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.
