jooq return query with incorrect values

Question

I want to insert into database table data using following jooq based code to generate query:

Factory jf = getJooqFactory(); int surveyId = jf.nextval(SURVEY_ID_SEQ).intValue(); jf.insertInto(SURVEY) .set(SURVEY.ID, surveyId) .set(SURVEY.NAME, survey.getName()) .set(SURVEY.IDML, Factory.val(idml, SQLDataType.CLOB)) .execute();

The problem is that when I print the query using this code

System.out.println(jf.insertInto(SURVEY) .set(SURVEY.ID, null) .set(SURVEY.NAME, null) .set(SURVEY.IDML, null) .getSQL());

what I get is query with question marks instead of values.

insert into "collect"."survey" ("id", "name", "idml") values (?, ?, ?)

When I print values of variables separately they are all correct. Even if I insert String values manually in .set() getSQL() return question marks.

I don't know a lot about jOOQ, but I suspect that it's using parameterised SQL to do its work. See en.wikipedia.org/wiki/Prepared_statement . Unless you're getting question marks going into your database, it's nothing to worry about. — Luke Woodward
– Luke Woodward, Commented Feb 20, 2012 at 21:02

Lukas Eder · Accepted Answer · 2012-04-11 10:09:33Z

org.jooq.Query.getSQL() renders SQL exactly as it is rendered to the JDBC PreparedStatement. Prepared statements in Java expect bind variables to be rendered as question marks: ?. The actual bind values are then set using methods, such as in this example

PreparedStatement stmt = connection.prepareStatement( "insert into collect.survey (id, name, idml) values (?, ?, ?)"); stmt.setInt(1, surveyId); stmt.setString(2, survey.getName()); stmt.setString(3, idml); stmt.executeUpdate();

If you want to see the generated SQL with inlined bind values, you can use the factory's Factory.renderInlined() method:

String sql = jf.renderInlined( jf.insertInto(SURVEY) .set(SURVEY.ID, surveyId) .set(SURVEY.NAME, survey.getName()) .set(SURVEY.IDML, val(idml, SQLDataType.CLOB)) );

There is also a getSQL(boolean) method on the Query object, to render SQL with inlined bind variables:

jf.insertInto(SURVEY) .set(SURVEY.ID, surveyId) .set(SURVEY.NAME, survey.getName()) .set(SURVEY.IDML, val(idml, SQLDataType.CLOB)) .getSQL(true);

Learn more about JDBC's PreparedStatement here:

http://docs.oracle.com/javase/7/docs/api/java/sql/PreparedStatement.html

Collectives™ on Stack Overflow

jooq return query with incorrect values

1 Answer 1

Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Related