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 jooq-user+unsubscr...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.
