How to change JSON returned by query using Helidon 2.0.0-M-2

Question

I'm using Helidon 2.0.0-M2. When I run the query below I get back a list of JSON objects.

dbClient.execute(exec -> exec.createNamedQuery("select-dsitem-by-id")
                .addParam("userId", dataItemId)
                .execute())
                .thenAccept(response::send)
                .exceptionally(throwable -> sendError(throwable, response));

Returned list

[
    {
        "data": "qwerty",
        "user_id": "12345"
    },
    {
        "data": "qwerty123",
        "user_id": "22345"
    }
]

The attribute names seem to be taken directly from the database column name. e.g. one attribute name returned is "user_id". However, I want it to be "userId". I also want to create a parent wrapper for this list like:

{
    "userList": [
        {
            "data": "qwerty",
            "user_id": "12345"
        },
        {
            "data": "qwerty123",
            "user_id": "22345"
        }
    ]
}

What is the best way to do this with the dbclient?

Thanks

It's a simple select statement "SELECT user_id, type, data FROM data_store WHERE user_id = :userId" — Schaffer, May 13 '20 at 18:18

score 2 · Accepted Answer · answered May 13 '20 at 09:53

Simple approach: Change your SQL statement to return the correct name, such as: SELECT data, user_id as userId FROM mytable

Complicated approach: We are working on a better support to map to a JSON stream. Currently there is only one (a bit complicated) way to achieve this:

You can create a custom mapper from a DbRow to JsonObject. This mapper needs to be a general one (it must work for any DbRow of any query).

The built-in mapper uses metadata provided on the columns. I have prepared a simple example (that just expects to have a single type of statements):

class DbRecordMapperProvider implements DbMapperProvider {
        private static final DbMapper<JsonObject> MAPPER = new DbRecordMapper();
        @SuppressWarnings("unchecked")
        @Override
        public <T> Optional<DbMapper<T>> mapper(Class<T> aClass) {
            if (JsonObject.class.equals(aClass)) {
                return Optional.of((DbMapper<T>)MAPPER);
            }
            return Optional.empty();
        }
    }
    class DbRecordMapper implements DbMapper<JsonObject> {
        @Override
        public JsonObject read(DbRow dbRow) {
            return Json.createObjectBuilder()
                    .add("name", dbRow.column("FIRSTPART").as(String.class))
                    .add("message", dbRow.column("SECONDPART").as(String.class))
                    .build();
        }

        @Override
        public Map<String, ?> toNamedParameters(JsonObject dbRecord) {
            return dbRecord;
        }

        @Override
        public List<?> toIndexedParameters(JsonObject dbRecord) {
            throw new IllegalStateException("Cannot convert json object to indexed parameters");
        }
    }

The important method is public JsonObject read(DbRow dbRow).

Once you have such a DbMapperProvider, you register it with the DbClient:

        dbClient = DbClient.builder()
                .config(config.get("db"))
                .mapperProvider(new DbRecordMapperProvider())
                .build();

Thanks. The SQL statement is the easiest for now. It is possible to wrap the output in other JSON such as a named array? — Schaffer, May 13 '20 at 18:21

How to change JSON returned by query using Helidon 2.0.0-M-2

1 Answers1