`SqlUpdate.setParameter` does not support `ArrayNode`

[PromanSEW]

Expected behavior

Model field:

@DbJsonB
@Column(nullable = false)
JsonNode progress; // actually ArrayNode

Here is example raw SQL:

update quest set progress = ? where id = 1;

-- or

update quest set progress = to_jsob(?) where id = 1;

//ArrayNode arrayNode = [1, 1, 1] // if call toString()
DB.sqlUpdate(sql).setParameter(arrayNode).execute();

Sets progress to new JSON

Actual behavior

Caused by: jakarta.persistence.PersistenceException: Error with property:0 dt:5001 data:[1,1,1] com.fasterxml.jackson.databind.node.ArrayNode
        at io.ebeaninternal.server.persist.Binder.bindSimpleData(Binder.java:341)
        at io.ebeaninternal.server.persist.Binder.bindObject(Binder.java:234)
        at io.ebeaninternal.server.persist.Binder.bindObject(Binder.java:187)
        at io.ebeaninternal.server.persist.Binder.bind(Binder.java:133)
        at io.ebeaninternal.server.persist.Binder.bind(Binder.java:91)
        at io.ebeaninternal.server.persist.Binder.bind(Binder.java:83)
        at io.ebeaninternal.server.persist.Binder.bind(Binder.java:75)
        at io.ebeaninternal.server.persist.ExeUpdateSql.bindStmt(ExeUpdateSql.java:98)
        at io.ebeaninternal.server.persist.ExeUpdateSql.execute(ExeUpdateSql.java:36)
        at io.ebeaninternal.server.persist.DefaultPersistExecute.executeSqlUpdate(DefaultPersistExecute.java:91)
Caused by: java.sql.SQLException: Unhandled data type:5001 bind number:0
        at io.ebeaninternal.server.persist.Binder.bindSimpleData(Binder.java:333)
        at io.ebeaninternal.server.persist.Binder.bindObject(Binder.java:234)
        at io.ebeaninternal.server.persist.Binder.bindObject(Binder.java:187)
        at io.ebeaninternal.server.persist.Binder.bind(Binder.java:133)
        at io.ebeaninternal.server.persist.Binder.bind(Binder.java:91)
        at io.ebeaninternal.server.persist.Binder.bind(Binder.java:83)
        at io.ebeaninternal.server.persist.Binder.bind(Binder.java:75)
        at io.ebeaninternal.server.persist.ExeUpdateSql.bindStmt(ExeUpdateSql.java:98)
        at io.ebeaninternal.server.persist.ExeUpdateSql.execute(ExeUpdateSql.java:36)
        at io.ebeaninternal.server.persist.DefaultPersistExecute.executeSqlUpdate(DefaultPersistExecute.java:91)

Maybe I does not know right syntax for set json array, which Ebean can understand? I need to replace the whole column value Please help

[PromanSEW]

Also question:

@DbJsonB
JsonNode conditions;

public ObjectNode getConditions() {
    return (ObjectNode) conditions;
}
...
Quest quest = finder.query().select("conditions").setId(id).findOne();
quest.getConditions().put("timeUntil", timeUntil);
quest.update();

Will be conditions updated?

[rob-bygrave]

update quest set progress = to_jsob(?) where id = ?

This is a good use case for stateless update.

var bean = new Quest();
bean.setId(1);
bean.setProgress(...);
bean.update(); // stateless update

[PromanSEW]

@rob-bygrave thanks, I already changed to "setProgress" Please answer my question about put and update()

[rbygrave]

Like:

    String rawJson = ...;// convert progress to String JSON
    PGobject pgo = new PGobject();
    pgo.setType("jsonb");
    pgo.setValue(rawJson);

DB.sqlUpdate(sql).setParameter(pgo).execute();

[PromanSEW]

Thanks, but question was about POJO modification of JsonNode Or, how to mark field as dirty?

[PromanSEW]

Also, I think that issue is still actual, because PGobject is actually hack, Ebean should correctly handle ArrayNode itself I found this: https://github.com/ebean-orm/ebean/blob/8ed3b765ec7b3528b9b4aed3dc49bf49daf8bee3/ebean-core-type/src/main/java/io/ebean/core/type/PostgresHelper.java#L30-L34 So why is it not used for ArrayNode?

[PromanSEW]

@rob-bygrave your solution causes exception:

Caused by: jakarta.persistence.PersistenceException: No ScalarType registered for class org.postgresql.util.PGobject
    at io.ebeaninternal.server.persist.Binder.getScalarType(Binder.java:159)
    at io.ebeaninternal.server.persist.Binder.bindObject(Binder.java:182)
    at io.ebeaninternal.server.persist.Binder.bind(Binder.java:133)
    at io.ebeaninternal.server.persist.Binder.bind(Binder.java:91)
    at io.ebeaninternal.server.persist.Binder.bind(Binder.java:83)
    at io.ebeaninternal.server.persist.Binder.bind(Binder.java:75)
    at io.ebeaninternal.server.persist.ExeUpdateSql.bindStmt(ExeUpdateSql.java:98)
    at io.ebeaninternal.server.persist.ExeUpdateSql.execute(ExeUpdateSql.java:36)
    at io.ebeaninternal.server.persist.DefaultPersistExecute.executeSqlUpdate(DefaultPersistExecute.java:91)
    at io.ebeaninternal.server.core.PersistRequestUpdateSql.executeNow(PersistRequestUpdateSql.java:83)
    at io.ebeaninternal.server.core.PersistRequest.executeStatement(PersistRequest.java:138)
    at io.ebeaninternal.server.core.PersistRequest.executeStatement(PersistRequest.java:122)
    at io.ebeaninternal.server.core.PersistRequestUpdateSql.executeOrQueue(PersistRequestUpdateSql.java:93)
    at io.ebeaninternal.server.persist.DefaultPersister.executeOrQueue(DefaultPersister.java:90)
    at io.ebeaninternal.server.persist.DefaultPersister.executeSqlUpdate(DefaultPersister.java:138)
    at io.ebeaninternal.server.core.DefaultServer.execute(DefaultServer.java:1922)
    at io.ebeaninternal.server.core.DefaultServer.execute(DefaultServer.java:1945)
    at io.ebeaninternal.server.core.DefaultSqlUpdate.execute(DefaultSqlUpdate.java:143)

Ebean version 13.23.0

[rbygrave]

With:

update quest set progress = to_jsob(?) where id = 1;
``
or using cast:
```sql
update quest set progress = ?::jsonb where id = 1;

then the bind value should be a String, so

ArrayNode arrayNode = ...; //[1, 1, 1] // if call toString() String arrayNodeAsJsonString = ...;

DB.sqlUpdate(sql).setParameter(arrayNodeAsJsonString).execute();

---

Will be conditions updated?

That depends on the MutationDetection mode. Did you debug that and see that?

[PromanSEW]

I tried setParameter(arrayNode.toString()) with to_jsonb(?) and ?::jsonb, but it writes actually string "[1, 1, 1]" instead of json array. Finally, I fixed this problem with StringBuilder of SQL explicitly inject json:

sb.append("progress = '").append(arrayNode.toString()).append("'::jsonb");

Note about single quotes around json.

What about mutation, I switched to raw SQL:

sb.append("conditions['timeUntil'] = to_jsonb(").append(node.longValue()).append(')');