I am working with Java + Spring + MyBatis + Postgresql. I have a table with a JSONB
column in which there are a series of BIGINT
(which are mapped to Long
in Java), that for performance reason I want to update using a query inside of the mapper.xml
relative to the entity the attribute belongs to.
Supposing that I the following elements:
table_a
: a database tablecolumn_1
: column of table_a
of type JSONB
key_alpha
: field of column_1
associated to a BIGINT
valueIdeally in pure psql
syntax I would do the following to initialize such value:
UPDATE table_a
SET column_1 = jsonb_set(column_1, '{key_alpha}', to_jsonb(0));
And the following to increment it:
UPDATE table_a
SET column_1 = jsonb_set(column_1, '{key_alpha}', to_jsonb((column_1->>'key_alpha')::bigint + 1));
The problem is that it looks like that this syntax is not compatible with MyBatis xml
mapper syntax. How can I achieve this result using MyBatis, or by using an alternative method, while keeping in mind that I need a performance-critical approach?
More specifically, if I try to use this syntax in the mapper.xml
:
<if test="incrementCountAlpha != null">column_1 = jsonb_set(column_1, '{key_alpha}', to_jsonb((column_1->>'key_alpha')::bigint + #{incrementCount})),</if>
<if test="incrementCountBeta != null">column_1 = jsonb_set(column_1, '{key_beta}', to_jsonb((column_1->>'key_beta')::bigint + #{incrementCount})),</if>
I get the following error; ### Error updating database. Cause: org.postgresql.util.PSQLException: ERROR: multiple assignments to same column "column_1"
The following statement should achieve your goal.
<update id="updateColumn1">
update table_a set column_1 = column_1
<if test="incrementCountAlpha != null">
|| jsonb_build_object(
'key_alpha',
coalesce((column_1->>'key_alpha')::bigint, 0) + #{incrementCountAlpha})
</if>
<if test="incrementCountBeta != null">
|| jsonb_build_object(
'key_beta',
coalesce((column_1->>'key_beta')::bigint, 0) + #{incrementCountBeta})
</if>
</update>
COALESCE
might be unnecessary if the target field is guaranteed to be initialized beforehand.
Here is an executable demo project:
https://github.com/harawata/mybatis-issues/tree/master/so-77490757