I have Customer
table in postgres DB with id
as primary key and below columns
id -> Integer(PK)
name -> Varchar2
details-> jsonb
created_timestamp -> TIMESTAMP WITH TIME ZONE
I'm trying to update table based on primary key using dslContext.resultQuery method, i have to update the name
, details
the jsonb column and set created_timestamp
as null
String sql = "UPDATE customer SET name = :name, details = :details, created_timestamp = 'NULL' where id = :id RETURNING id";
Java Code
List<Map<String,Object>> updatedCapacityResult = dslContext.resultQuery(sql,
DSL.param("name","value"),
DSL.param("details",objectmapper.writeValueAsString(object))).fetchMaps();
I'm having error while executing the code and it simply says
org.jooq.exception.DataAccessException: SQL [UPDATE customer SET name = :name, details = :details, created_timestamp = 'NULL' where id = :id RETURNING id; ERROR: syntax error at or near "details"
Position: 60
When i removed the code to update details
, then it started erroring out on created_timestamp
field, if i just updated the name
using above query it updates and return the data as well.
Curious what i have missed here and how to updated jsonb
and timestamp
columns ?
I couldn't figure out the issue with jooq
library, but on updating values to NULL
i found alternative like using StringBuilder
and forming it as string
String sql = new StringBuilder("UPDATE customer SET" )
.append("name = '"+name+"', ")
.append("details = '"+details+"', ")
.append("created_timestamp = NULL ")
.append("where id = '"+id+"' RETURNING id").toString();
and the just using resultQuery
List<Map<String,Object>> updatedCapacityResult = dslContext.resultQuery(sql).fetchMaps();