Re: Types.OTHER

Hi All,

For my use case I was simply looking for Java String mapping to underlying
MySQL (JSON data type) and Postgres (JSON or JSONB data types).  I would
generally be rendering this JSON text directly in a REST API, and not going
through Java complex object serialization pipeline.

I think if people want to map JSON into Java objects, that is a good
exercise for the developer.

regards Malcolm

On Tue, Jun 9, 2020 at 4:59 PM Andrus Adamchik <[email protected]>
wrote:

> Hi John, Malcom --
>
> (Don't know if Malcom is on dev@, so posting it here; we can move to dev@)
>
> Looks like the need for JSON type and other non-standard (from JDBC
> standpoint) types is there. Many people need it. I am hoping the effort
> that started independently by at least 3 community members can result in
> something that we can bring to the Cayenne core. Let's discuss how this
> might work.
>
> So... We'd need to create a taxonomy of "value object" types that map to
> "java.sql.Types.OTHER" (e.g. Jsonb, Wkt, TimestampRange, etc.). Then we'd
> create ExtendedTypes for each one of them, and register them inside the
> corresponding DbAdapters. More than one adapter can support a given type.
> Also we need to add adapter-specific logic to db-import Types.OTHER as the
> right value object type using DB-specific metadata of a given column. Same
> for SQL schema generation - for Types.OTHER we can derive the true type
> based on the value object it is mapped to.
>
> An important question is the internal structure of the value object types.
> Most of these types require parsing of a String or a byte[] into some
> special Java object. And there are two problems with it:
>
> 1. Performance. You may not always need that special object in many cases.
> E.g. your JSON is written directly as String to a REST response. No need to
> parse it by default.
> 2. External dependencies - Jsonb would require Jackson, Wkt - JTS libs to
> parse it to geometry, etc.
>
> I solved both problems by structuring the types as unparsed wrappers of
> String or byte[]. If there is a third-party lib dependency, it up to the
> calling code to decide when and how to parse the data and which library to
> use. If there are no dependencies (e.g. Postgres "tsrange" can be parsed to
> a pair of LocalDateTime objects using JDK classes) a parser can be included
> in the type, but the parsing should happen lazily.
>
> Also I was able to avoid the dependency on driver-specific code (PgObject
> in John's example) by providing a custom "sql tree processor" (a 4.2
> feature), so that String or byte[] can be bound to PreparedStatement, and
> the tree processor would alter the SQL to wrap the parameter in a Postgres
> function doing the type conversion in DB.
>
> I am planning to open-source the code that implements parts of the
> solution above. Hopefully soon.
>
> Thoughts?
>
>
> Also a minor note on John's example:
>
> > public static <T> void registerWithRuntime(ServerRuntime runtime,
> Class<T> javaClass){
> >
> >       JsonbType<T> jsonbType = *new* JsonbType<T>(javaClass);
> >       Collection<DataNode> nodes =
> runtime.getDataDomain().getDataNodes();
> >       for(DataNode node : nodes) {
> >
>  node.getAdapter().getExtendedTypes().registerType(jsonbType);
> >       }
> > }
>
> Unless you have a custom DbAdapter as discussed above, an idiomatic way to
> register an ExtendedType would be to write a DI module and add it to
> runtime on startup:
>
>   Module m = b -> ServerModule.contributeUserTypes(b).add(jsonbType);
>
>
> Andrus
>
>
>
>
> > On May 26, 2020, at 5:37 PM, John Huss <[email protected]> wrote:
> >
> > For JSONB support I use this class (basically). But in addition you will
> > probably want an immutable (or optionally immutable) version of Map
> because
> > changes to the Map attribute itself won't be detected by Cayenne unless
> you
> > reset the attribute value on the entity, like
> > myEntityObject.setMyAttribute(new JsonMap(map)). In the Cayenne data
> > map/model you use the OTHER jdbc type.
> >
> > *import* java.sql.CallableStatement;
> >
> > *import* java.sql.PreparedStatement;
> >
> > *import* java.sql.ResultSet;
> >
> > *import* java.util.Collection;
> >
> >
> > *import* org.apache.cayenne.access.DataNode;
> >
> > *import* org.apache.cayenne.access.types.ExtendedType;
> >
> > *import* org.apache.cayenne.configuration.server.ServerRuntime;
> >
> > *import* org.apache.cayenne.eof.JsonList;
> >
> > *import* org.codehaus.jackson.map.ObjectMapper;
> >
> > *import* org.postgresql.util.PGobject;
> >
> >
> > *public* *class* JsonbType<T> *implements* ExtendedType<T> {
> >
> >
> > *private* *static* *final* String *DB_TYPE* = "jsonb";
> >
> >
> > /**
> >
> > * Call this at startup time to register with the Cayenne runtime.
> >
> > * *@param* javaClass with user-created properties (getters and setters)
> >
> > */
> >
> > *public* *static* <T> *void* registerWithRuntime(ServerRuntime runtime,
> > Class<T> javaClass){
> >
> > JsonbType<T> jsonbType = *new* JsonbType<T>(javaClass);
> >
> >
> > Collection<DataNode> nodes = runtime.getDataDomain().getDataNodes();
> >
> > *for* (DataNode node : nodes) {
> >
> > node.getAdapter().getExtendedTypes().registerType(jsonbType);
> >
> > }
> >
> > }
> >
> >
> > *private* *final* Class<T> clazz;
> >
> >
> > *public* JsonbType(Class<T> clazz) {
> >
> > *this*.clazz = clazz;
> >
> > *try* {
> >
> > clazz.getConstructor(*new* Class[0]);
> >
> > } *catch* (NoSuchMethodException | SecurityException e) {
> >
> > *throw* *new* RuntimeException("Json type " + clazz.getName() + " does
> not
> > have a no-args constructor");
> >
> > }
> >
> > }
> >
> >
> > @Override
> >
> > *public* String getClassName() {
> >
> > *return* clazz.getName();
> >
> > }
> >
> >
> > @Override
> >
> > *public* T materializeObject(ResultSet rs, *int* index, *int* type)
> *throws*
> > Exception {
> >
> > String json = rs.getString(index);
> >
> > *return* materialize(json);
> >
> > }
> >
> >
> > @Override
> >
> > *public* T materializeObject(CallableStatement rs, *int* index, *int*
> type)
> > *throws* Exception {
> >
> > String json = rs.getString(index);
> >
> > *return* materialize(json);
> >
> > }
> >
> >
> > *private* T materialize(String json) {
> >
> > *if* (json != *null*) {
> >
> > *try* {
> >
> > T value = *new* ObjectMapper().readValue(json, clazz);
> >
> > *if* (value *instanceof* JsonList) {
> >
> > ((JsonList<?>)value).freeze();
> >
> > } *else* *if* (value *instanceof* JsonMap) {
> >
> > ((JsonMap)value).freeze();
> >
> > }
> >
> > *return* value;
> >
> > } *catch* (Exception e) {
> >
> > *throw* *new* RuntimeException("Failed to deserialize value of type " +
> > clazz.getSimpleName() + " from " + json + ". " + e.getMessage(), e);
> >
> > }
> >
> > } *else* {
> >
> > *return* *null*;
> >
> > }
> >
> > }
> >
> >
> > @Override
> >
> > *public* *void* setJdbcObject(
> >
> > PreparedStatement statement,
> >
> > T value,
> >
> > *int* pos,
> >
> > *int* type,
> >
> > *int* scale) *throws* Exception {
> >
> >
> > *if* (value == *null*) {
> >
> > statement.setNull(pos, type);
> >
> > } *else* {
> >
> > String json;
> >
> > *try* {
> >
> > json = *new* ObjectMapper().writeValueAsString(value);
> >
> > } *catch* (Exception e) {
> >
> > *throw* *new* RuntimeException("Failed to serialize value of type " +
> > clazz.getSimpleName()
> > + " from " + value + ". " + e.getMessage());
> >
> > }
> >
> >
> > PGobject dataObject = *new* PGobject();
> >
> > dataObject.setType(*DB_TYPE*);
> >
> > dataObject.setValue(json);
> >
> > statement.setObject(pos, dataObject);
> >
> > }
> >
> > }
> >
> >
> > @Override
> >
> > *public* String toString(T value) {
> >
> > *return* value.toString();
> >
> > }
> >
> >
> > }
>