How to avoid quotes around values in jOOQ

Question

I am using jOOQ as a SQL builder and I try to build a simple update statement as the following:

DSL.using(SQLDialect.POSTGRES_9_5)
.update(table("Location"))
.set(field("speed"), 50)
.set(field("location"), "ST_PointFromText('POINT(-73 40)', 4326)")
.where(field("id").equal(1))
.getSQL(ParamType.INLINED);

As you can see, I am using Postgres and PostGIS, so I need to insert the location using the PostGIS method ST_PointFromText().

The generated SQL looks like this, and fails because the ST_PointFromText() is wrapped around single quotes:

update Location
set speed = 50, location = 'ST_PointFromText(''POINT(-73 40)'', 4326)' 
where id = 1

The query should be:

update Location
set speed = 50, location = ST_PointFromText('POINT(-73 40)', 4326)
where id = 1

Is there any way to remove the quotes around the ST_PointFromText()?

score 3 · Accepted Answer · answered May 29 '18 at 10:41

When you write something like this in jOOQ:

.set(field("location"), "ST_PointFromText('POINT(-73 40)', 4326)")

Then, the value put on the right side will be considered a bind value. In case you generate the SQL with bind values being inlined, it turns into an escaped string literal. In general, you will want that, because it is the correct and expected behaviour given that you're passing a value to the statement (imagine otherwise, the risk of SQL injection, etc.)

If you do want to add some "plain SQL" string to your query, just use DSL.field(String), e.g.

.set(field("location", String.class), 
     field("ST_PointFromText('POINT(-73 40)', 4326)", String.class))

Note that the data types are required because of this issue. I've just put String.class as a dummy type. You should probably implement an actual converter / binding for your POINT data type to be clean.

For more information about plain SQL and plain SQL templating, see these sections of the manual:

How to avoid quotes around values in jOOQ

1 Answers1