I am using Java 17 with spring boot version 3.1.0, Hibernate 6.2.2.Final & postgresql 12. Running into "Could not determine recommended JdbcType for" error when inserting the data into jsonb column's using native query. We have specific req to batch insert using native query.
error : enter image description here
jdbc type error
Tried using hibernate provided annotations with @JdbcTypeCode(SqlTypes.JSON)
and hypersistence-utils-hibernate-62 annotations with @Type(JsonType.class) on both jsonb
properties expn & expnScratch
@Type(JsonType.class)
@Column(columnDefinition="jsonb")
private EnrichExpn expn;
@Type(JsonType.class)
@Column(columnDefinition="jsonb")
private EnrichExpnScratch expnScratch;
Native query :
String query = "INSERT INTO cust_enrich(cust_id, correlation_id, expn, expn_scratch)
values(:cust_id, :correlation_id, :expn, :expn_scratch) ON CONFLICT DO NOTHING "
var queryRun =
entityManager
.createNativeQuery(query)
.unwrap(Query.class)
.setParameter("cust_id", t.getCustId())
.setParameter("correlation_id", t.getCorrelationId())
.setParameter("expn", t.getExpn(), JsonType.INSTANCE)
.setParameter("expn_scratch", t.getExpnScratch(), JsonType.INSTANCE);
queryRun.executeUpdate();
@JdbcTypeCode(SqlTypes.JSON)
@Column(columnDefinition="jsonb")
private EnrichExpn expn;
@JdbcTypeCode(SqlTypes.JSON)
@Column(columnDefinition="jsonb")
private EnrichExpnScratch expnScratch;
Native query :
String query = "INSERT INTO cust_enrich(cust_id, correlation_id, expn, expn_scratch) values(:cust_id, :correlation_id, :expn, :expn_scratch) ON CONFLICT DO NOTHING "
var queryRun =
entityManager
.createNativeQuery(query)
.unwrap(Query.class)
.setParameter("cust_id", t.getCustId())
.setParameter("correlation_id", t.getCorrelationId())
.setParameter("expn", t.getExpn(), SqlType.class)
.setParameter("expn_scratch", t.getExpnScratch(), SqlType.class);
queryRun.executeUpdate();
Using hypersistence-utils @Type(JsonType.class) on properties worked. Need to call JsonType constructor with class type as shown below. Suggested by vladmihalcea himself test case
new JsonType(Expn.class) & new JsonType(ExpnScratch.class).
var queryRun =
entityManager
.createNativeQuery(query)
.unwrap(Query.class)
.setParameter("cust_id", t.getCustId())
.setParameter("correlation_id", t.getCorrelationId())
.setParameter("expn", t.getExpn(), new JsonType(Expn.class))
.setParameter("expn_scratch", t.getExpnScratch(), new JsonType(ExpnScratch.class));