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 <johnth...@gmail.com> 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();
>
> }
>
>
> }
