Hi,
I'm trying to write a batch insert statement using jOOQ. I have two
versions, one that inserts a single piece of data, and one that does a
batch insert with a list of data. The code inserting a single item works,
and the code doing the batch insert doesn't. I'm trying to use the PostGIS
ST_GeomFromGeoJSON function to insert geojson into a Postgres database.
create.transaction(configuration -> {
DSL.using(configuration).insertInto(TABLE_NAME, TABLE.COL1, TABLE.COL2,
TABLE.GEOM, TABLE.COL4)
.values(
data.getCol1(),
data.getCol2(),
DSL.field(DSL.sql("ST_GeomFromGeoJSON(?)", new ObjectMapper().
writeValueAsString(data.getGeom()))),
data.getCol4())
.execute()
});
This code creates a log line:
19:12:44.218 [main] DEBUG org.jooq.tools.LoggerListener - Executing query
: insert into "public"."table_name" ("col1", "col2", "geom", "col4")
values (?, cast(? as timestamp), ST_GeomFromGeoJSON(?), ?::jsonb)
19:12:44.219 [main] DEBUG org.jooq.tools.LoggerListener - -> with bind
values : insert into "public"."table_name" ("col1", "col2", "geom",
"col4") values (13, timestamp 'timestamp', ST_GeomFromGeoJSON(
'{"type":"Point","coordinates":[5.0,10.0]}'), 'random json'::jsonb)
Trying to do this with a list of data elements I have:
create.transaction(configuration -> {
BatchBindStep bindStep = DSL.using(configuration).batch(
DSL.using(configuration).insertInto(TABLE_NAME, TABLE.COL1, TABLE.
COL2, TABLE.GEOM, TABLE.COL4
).values((Integer) null, null, null, null)
);
for (Data d : dataList) {
bindStep.bind(
d.getCol1(),
d.getCol2(),
DSL.field(DSL.sql("ST_GeomFromGeoJSON(?)", new ObjectMapper().
writeValueAsString(data.getGeom()))),
d.getCol4()
);
}
try {
bindStep.execute();
} catch (DataAccessException ex) {
// Log ex, and getNextException
}
});
This yields an error:
9:13:54.933 [main] ERROR t.b.p.s.Service - Error while executing SQL.
org.jooq.exception.DataAccessException: SQL [insert into
"public"."table_name" ("col1", "col2", "geom", "col4") values (?, cast(? as
timestamp), cast(? as varchar), cast(? as varchar)::jsonb)]; Batch entry 0
insert into "public"."table_name" ("col1", "col2", "geom", "col4") values
('14', cast('timestamp' as timestamp),
cast('{"type":"Point","coordinates":[0.0,0.0]}' as varchar), cast('random
json' as varchar)::jsonb) was aborted. Call getNextException to see the
cause.
Caused by: java.sql.BatchUpdateException: Batch entry 0 insert into
"public"."table_name" ("col1", "col2", "geom", "col4") values ('14',
cast('timestamp' as timestamp),
cast('{"type":"Point","coordinates":[0.0,0.0]}' as varchar), cast('random
json' as varchar)::jsonb) was aborted. Call getNextException to see the
cause.
19:13:54.935 [main] ERROR t.b.p.s.Service - Next exception:
org.postgresql.util.PSQLException: ERROR: column "geom" is of type geometry
but expression is of type character varying
Hint: You will need to rewrite or cast the expression.
When I have the statement batched, jOOQ doesn't seem to be inserting the
SQL to use ST_GeomFromGeoJSON. I don't understand why. Help would be
greatly appreciated!
--
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.
