hubspot/rosetta

Java library that leverages Jackson to take the pain out of mapping objects to/from the DB, designed to integrate seamlessly with jDBI

Rosetta

Overview

Rosetta is a Java library that leverages Jackson to take the pain out of mapping objects to/from the DB, designed to integrate seamlessly with Jdbi. Jackson is extremely fast, endlessly configurable, and already used by many Java webapps.

Usage

If you are on Jdbi 2, add the following dependency:

<dependency>
  <groupId>com.hubspot.rosetta</groupId>
  <artifactId>RosettaJdbi</artifactId>
  <version>{latest version}</version>
</dependency>

Latest versions can be seen here

Or if you are on Jdbi 3, add the following dependency:

<dependency>
  <groupId>com.hubspot.rosetta</groupId>
  <artifactId>RosettaJdbi3</artifactId>
  <version>{latest version}</version>
</dependency>

Latest versions can be seen here

Binding

You can bind JDBI arguments in your DAO using @BindWithRosetta.

public interface MyDAO {
  @SqlUpdate("UPDATE my_table SET name = :name, type = :type WHERE id = :id")
  void update(@BindWithRosetta MyRow obj);
}

@BindWithRosetta converts the object to a tree using Jackson, and then binds every property in the JSON tree on the Jdbi statement (using dot notation for nested object fields). This lets you use all the Jackson annotations you know and love to customize the representation.

Mapping

Jdbi 2

With Jdbi 2, you can register the Rosetta mapper globally by adding it your DBI like so:

dbi.registerMapper(new RosettaMapperFactory());

Or to test it out on a single DAO you would do:

@RegisterMapperFactory(RosettaMapperFactory.class)
public interface MyDAO { /* ... */ }

Or to use in combination with a Handle: (same idea to register on a Query)

handle.registerMapper(new RosettaMapperFactory());

Jdbi 3

With Jdbi 3, you can register the Rosetta mapper globally by adding it your Jdbi like so:

jdbi.registerRowMapper(new RosettaRowMapperFactory());

Or to test it out on a single DAO you would do:

@RegisterRowMapperFactory(RosettaRowMapperFactory.class)
public interface MyDAO { /* ... */ }

Or to use in combination with a Handle: (same idea to register on a Query)

handle.registerRowMapper(new RosettaRowMapperFactory());

Advanced Features

For a list of advanced features, see here

rosetta's Issues

Don't bind numeric types as strings

https://github.com/HubSpot/Rosetta/blob/master/RosettaCore/src/main/java/com/hubspot/rosetta/RosettaBinder.java#L31

@solomonty

Boolean properties always bound to null

When binding a Boolean property flag I am using the following syntax:

public interface MyDAO {
  @SqlUpdate("UPDATE my_table SET flag = :flag WHERE id = :id")
  void update(@BindWithRosetta MyRow obj);
}

But the flag property is always saved as null in database, regardless of its value.

What could be the problem? How can I troubleshoot it?

Using Rosetta version 3.11.0.

RosettaValue does not support binding an object as a scalar in queries.

@RosettaBinder is analogous to @BindBean, but there's no Rosetta equivalent to @Bind("someName") in order for queries like this to work:

@SqlQuery("SELECT someColumn FROM someTable WHERE someStringColumn = :it LIMIT 1")
findThingByValue(@RosettaBind("it") ValueType someValue);

with a value type like

public class ValueType {
  // ...

  @RosettaValue
  public String getValue() {
    return value;
  }
}

Rosetta Annotations Docs

I thought it would be a good idea to have the various Rosetta annotations documented with examples. It is especially hard for beginners to know what annotations exist because not all of them live in the annotations directory.

Support Postgres' HSTORE

Allow @StoredAsJson to customize the serialization inclusion setting on the object mapper

For objects that are sparsely populated, it might be useful to only store non null values and set the underlying ObjectMapper to set the include rules for serialization.

@StoredAsJson(include = Include.NON_NULL);

Exception when trying to @JsonSerialized a nested polymorphic field annotated with @JsonTypeInfo

I have this field that is annotated as @StoredAsJson and it's type is interface FilterValue. In FilterValue, I have @JsonTypeInfo and @JsonSubTypes configured properly, also having like classes StringValue and NumberValue implementing that interface.

The serialization works fine, but it's failing to deserialize the values back from the database. When I try to fetch the object from the database with my DAO I get the following error:

com.fasterxml.jackson.databind.JsonMappingException: Unexpected token (VALUE_STRING), expected START_ARRAY: need JSON Array to contain As.WRAPPER_ARRAY type information for class io.github.rscarvalho.rosettatest.data.FilterValue
 at [Source: {"id":0,"name":"filter1","filterValue":"{\"@type\":\"StringValue\",\"value\":\"my value\"}"}; line: 1, column: 25] (through reference chain: io.github.rscarvalho.rosettatest.data.Filter["filterValue"])

I've set up a repository with a small test to reproduce the issue. You can see the original error message into the Travis build that failed the same way as my local tests.

Support joins where the query includes "SELECT top_object.*, join_object.*"

Currently, if we want to add joins our select needs to be something like:

SELECT top_obect.id, top_object.other_field, join_object.id as 'join_object.id', join_object.some_field as 'join_object.some_field'...

it would be nice for the RosettaMapper to support handling

SELECT top_object.*, join_object.*

Exception if trying to work with jsonb (Postgres)

Hi,

I'm doing a batch update like this:

@SqlBatch("INSERT INTO something (id, name) VALUES (:id, :name)"
void batchUpdate(@BindWithRosetta List<SomeObject> someObjectList);

The (postgres) table:

id: text
name: jsonb

name is another class that needs to be transformed to json.

I get this exception:
Caused by: org.postgresql.util.PSQLException: ERROR: column "name" is of type jsonb but expression is of type character varying.

JSONB is a non standard column type for postgres, that is however broadly being used. According to some stack overflow entries, the fix should be to do something like this.

INSERT INTO table (json_field) VALUES (to_json(yourJsonString::json))

It might make sense to add this feature. Could be possible by doing something like this:
BindWithRosetta("jsonb")

Rosetta & Joda-Time

When I bind Rosetta-mapped object to JDBI with Joda-Time (DateTime) property, then JDBI throws following error:

ERROR [2016-04-19 08:08:19,296] io.dropwizard.jdbi.jersey.LoggingDBIExceptionMapper: Error handling a request: 916037380f2df243
! org.skife.jdbi.v2.exceptions.UnableToExecuteStatementException: Unable to execute, no named parameter matches "start_time" and no positional param for place 3

start_time itself seems to contain all DateTime properties (time in millis, etc). I'm using dropwizard.

When adding a new module for rosetta, Rosetta.addModule(new JodaModule());, start_time contains time in milliseconds, which means I need to have a cast expression in SQL query.

Nested `@StoredAsJson` does not work with binary serialization

See this test: https://github.com/HubSpot/Rosetta/blob/master/RosettaCore/src/test/java/com/hubspot/rosetta/annotations/StoredAsJsonTest.java#L570

Any examples for useHandle() usage?

Hi, I'm interested in adding Rosetta to our project, but the examples in this repo's wiki pertain to annotation-driven usage:

@SqlUpdate("INSERT INTO my_table (type, id, name) VALUES (:type, :inner.id, :inner.name)")
public void insert(@BindWithRosetta OuterBean bean);

Our project uses manual connection management with jdbi handle callbacks. Would something like this work with Rosetta?

public void insert(@BindWithRosetta MyBean object) {
    jdbi.useHandle(handle -> {
        handle.createUpdate("insert into my_table (a, b, c) values (:a, :b, :inner.c)")
                .registerRowMapper(new RosettaRowMapperFactory())
                .bindBean(object)
                .execute();
    });
}

////

public class MyBean {
    String a;
    int b;

    @StoredAsJson
    // Additional custom Jackson annotations here
    InnerBean c;
}

Also, I'm not sure whether the @StoredAsJson annotation is intended replace jdbi's @Json annotation, or if they are meant to be used together.

Release?

Is there a release for Rosetta? or is-it only snapshot?

java.lang.IllegalAccessError exception

Hi,

I am getting an error:

java.lang.IllegalAccessError: tried to access method com.mysql.jdbc.ResultSetMetaData.getField(I)Lcom/mysql/jdbc/Field; from class com.mysql.jdbc.MysqlTableNameExtractor
at com.mysql.jdbc.MysqlTableNameExtractor.getTableName(MysqlTableNameExtractor.java:20)
at com.hubspot.rosetta.RosettaMapper.mapRow(RosettaMapper.java:60)
at com.hubspot.rosetta.jdbi.RosettaMapperFactory$1.map(RosettaMapperFactory.java:45)

This only happens when application is deployed on Amazon (Amazon Linux, java 1.8).
On local machine (same java version) there is no exception and it works as expected.

I would be grateful if anyone can suggest what could be wrong here.

Test Issue

Rosetta and SQLite

Hi,

I've encountered an issue with Rosetta, when used with xerial's SQLite driver.

The issue is that the driver is non-standard and returns null for getMetaData (instead of "") from scalar sub-queried columns:
SELECT (a+b) c FROM table

What do you think about adding some null checks to the getTableName calls? I've raised the issue separately with the Xerial driver team.

Binding Collections as SQL Arrays

Serializing collections to a representation suitable for binding to a 'java.sql.Array' currently fails as demonstrated in the test below:

package com.hubspot.rosetta.beans;

import java.util.List;

public class CollectionBean {

    private List<Long> collectionProperty;

    public List<Long> getCollectionProperty() {
        return collectionProperty;
    }

    public void setCollectionProperty(List<Long> collectionProperty) {
        this.collectionProperty = collectionProperty;
    }
}

@Test
  public void itBindsCollectionsCorrectly() {

      CollectionBean collectionBean = new CollectionBean();

      List<Long> collectionProperty = new LinkedList<>();

      collectionProperty.add(1L);
      collectionProperty.add(2L);
      collectionProperty.add(3L);


      collectionBean.setCollectionProperty(collectionProperty);

      assertThat(bind(collectionBean)).isEqualTo(map("collectionProperty","[1,2,3]"));
  }

I was able to work around this bug by creating a serializer that wrote the collection to a Postgresql array literal as shown below

import com.fasterxml.jackson.core.JsonGenerator;
import com.fasterxml.jackson.core.JsonProcessingException;
import com.fasterxml.jackson.databind.JsonNode;
import com.fasterxml.jackson.databind.JsonSerializer;
import com.fasterxml.jackson.databind.ObjectMapper;
import com.fasterxml.jackson.databind.SerializerProvider;
import org.postgresql.jdbc4.Jdbc4Array;

import java.io.IOException;
import java.util.Collection;
import java.util.Iterator;

public class CollectionSerializer extends JsonSerializer<Collection> {
    @Override
    public void serialize(Collection collection, JsonGenerator jsonGenerator, SerializerProvider serializerProvider) throws IOException, JsonProcessingException {

        if (collection != null) {

            ObjectMapper om = (ObjectMapper) jsonGenerator.getCodec();

            jsonGenerator.writeString(createPostgresqlArrayLiteral(om, collection));

        }

        else {

            jsonGenerator.writeNull();

        }
    }

    protected static String createPostgresqlArrayLiteral(ObjectMapper om, Collection collection) throws JsonProcessingException {

        StringBuilder sb = new StringBuilder();

        sb.append("{");

        Iterator it = collection.iterator();

        while (it.hasNext()) {

            Object o = it.next();


            if (o == null) {

                sb.append("null");
            }

            else {

                JsonNode node = om.valueToTree(o);
                Jdbc4Array.escapeArrayElement(sb, node.asText());

            }

            if (it.hasNext()) {

                sb.append(',');
            }
        }

        sb.append("}");

        return sb.toString();
    }
}

Obviously though, this is an extreme database specific hack and also breaks @StoredAsJson serialization for JSON arrays, since all collections are passed through the same custom serializer as well. @JsonSerialize also isn't helpful here since the serializer needed for mapping the array is different from the one used for binding.

Rosetta mapper & MariaDB

Hi!

Don't know if this is actually Rosetta issue and if there is even something like "MariaDB support", but I have exceptions like this:

Can not deserialize instance of java.lang.String out of START_OBJECT token
at [Source: N/A; line: -1, column: -1](through reference chain: com.konfigurator.response.MaterialResponse["color"])
java.lang.IllegalArgumentException: Can not deserialize instance of java.lang.String out of START_OBJECT token
at [Source: N/A; line: -1, column: -1](through reference chain: com.konfigurator.response.MaterialResponse["color"])
at com.fasterxml.jackson.databind.ObjectMapper._convert(ObjectMapper.java:3459) ~[jackson-databind-2.6.3.jar:2.6.3]
at com.fasterxml.jackson.databind.ObjectMapper.convertValue(ObjectMapper.java:3400) ~[jackson-databind-2.6.3.jar:2.6.3]
at com.hubspot.rosetta.RosettaMapper.mapRow(RosettaMapper.java:88) ~[RosettaCore-3.11.2.jar:3.11.2]
at com.hubspot.rosetta.jdbi.RosettaMapperFactory$1.map(RosettaMapperFactory.java:40) ~[RosettaJdbi-3.11.2.jar:3.11.2]
at org.skife.jdbi.v2.RegisteredMapper.map(RegisteredMapper.java:35) ~[jdbi-2.77.jar:2.77]

With MySQL it is ok - same db structure and same data.
This "color" property is null from database.

Thanks.

Deserializing & Serializing of type Boolean

When using 1/0 in the database on a TINYINT(1). Deserializing to a boolean doesn't appear to work properly, for now I have a custom Serializer / Deserializer class which works fine but wouldn't it be better if this was support natively by the library?

Also to note standard behaviour by Jackson is Booleans should be true/false but obviously in databases we use 1 or 0 so it would make sense in the case of Rosetta.

@StoreAsJson for List of element deserializes the list incorrectly by default when used w/ JDBI

Hi, I have a model class that looks like the following :

public class Model {
   public String id;
   @StoredAsJson public List<String> aList;
}

When I use JDBI object interface like the following :

    @SqlQuery("SELECT id, aList FROM model WHERE id = :id")
    Model get(@Bind("id") String id);

    @SqlUpdate("INSERT INTO model (id, aList)")
    void create(@BindWithRosetta Model model);

Now when I run create, it seems to work fine; a native dbi handle select would print something like

{id="blah", aList=["id1","id2"]}

However, when I run it using the get, it prints it as

{id="blash", aList=[["id1","id2"]]} 

by wrapping it into another list. (it's like nested array now. )

Is the only way around here adding a custom deserializer? or am I missing something to make the this getter work as well?

Thank you

Issues with Annotations

I am trying to use RosettaProperty which is similar to JsonProperty. For example I have a field in database called fn which maps to fn in my DTO file. But if I want to display fn as "firstName" in my json structure, RosettaProperty dosen't seem to work for me. Since I have it has firstname, it dosen't map the value from database.

private String fn;
@RosettaProperty(value = "firstName")
public String getFnt() {
return fn;
}

public void setFn(String fn) {
    this.fn= fn;
}

OUTPUT: {"fn": null}

I am using Rosetta 3.11.9.

@JsonDeserialize does not function properly with @StoredAsJson

A custom deserializer specified in @JsonDeserialize is ignored when @StoredAsJson is present IF another deserializer is specified in a jackson module UNLESS the specified deserializer is also added to the module as well. However, given that Jackson would then essentially choose a random deserializer for a class by default, this behavior is highly undesirable.

@StoredAsJson breaks with Jackson 2.6.x

Stacktrace:

java.lang.NoSuchMethodError: com.fasterxml.jackson.databind.ser.std.StringSerializer.serialize(Ljava/lang/String;Lcom/fasterxml/jackson/core/JsonGenerator;Lcom/fasterxml/jackson/databind/SerializerProvider;)V
    at com.hubspot.rosetta.internal.StoredAsJsonSerializer.serialize(StoredAsJsonSerializer.java:25)
    at com.fasterxml.jackson.databind.ser.BeanPropertyWriter.serializeAsField(BeanPropertyWriter.java:656)
    at com.fasterxml.jackson.databind.ser.std.BeanSerializerBase.serializeFields(BeanSerializerBase.java:678)
    at com.fasterxml.jackson.databind.ser.BeanSerializer.serialize(BeanSerializer.java:156)
    at com.fasterxml.jackson.databind.ser.DefaultSerializerProvider.serializeValue(DefaultSerializerProvider.java:130)
    at com.fasterxml.jackson.databind.ObjectMapper.writeValue(ObjectMapper.java:2374)
    at com.fasterxml.jackson.databind.ObjectMapper.valueToTree(ObjectMapper.java:2503)
    at com.hubspot.rosetta.jdbi.RosettaJdbiBinder.bind(RosettaJdbiBinder.java:18)
    at com.hubspot.rosetta.jdbi.RosettaJdbiBinder.bind(RosettaJdbiBinder.java:11)
    at org.skife.jdbi.v2.sqlobject.Bindifier.bind(Bindifier.java:35)
    at org.skife.jdbi.v2.sqlobject.CustomizingStatementHandler.applyBinders(CustomizingStatementHandler.java:127)
    at org.skife.jdbi.v2.sqlobject.UpdateHandler.invoke(UpdateHandler.java:77)
    at org.skife.jdbi.v2.sqlobject.SqlObject.invoke(SqlObject.java:212)
    at org.skife.jdbi.v2.sqlobject.SqlObject$2.intercept(SqlObject.java:109)
    at org.skife.jdbi.v2.sqlobject.CloseInternalDoNotUseThisClass$$EnhancerByCGLIB$$a11e415e.insertReturningTestGeom(<generated>)

Support JDBI 3

Does Rosetta support JDBI 3? JDBI 2 is deprecated and the new version is not compatible with the previous one.

dropwizard-jdbi creating query with concat generate NullPointerException

this query works fine:
"SELECT cd.display_name AS name
FROM city_dct as cd
WHERE cd.display_name LIKE :partialName"

this query does not:
"SELECT cd.display_name AS name, concat('-',cd.city_cd) AS dbIDMultiKey
FROM city_dct as cd
WHERE cd.display_name LIKE :partialName"

The metadata generated has null values for tableName and the other attributes when the select concat is created

.