Custom Types in ebean

[Peace MICHAELS]

i have a play 2.3 java web app using ebean and postgre database, i have a colomn in my postgre databse which has a json type. representing that in my model class confuses me,
 if i represent it like so

    public JsonNode property;

ebean does not generate that colomn in the table generation it just ignores it.How do i represent json in ebean?

[Rob Bygrave]

A busy day today so I'll get back to this question hopefully tonight.

... so both postgres and Oracle have JSON data types which I think will be very significant / very useful.  We refactored the internal JSON support in Ebean to help support these db types.

... so I will come back to this question.  Where we are and what we need to do from here.

Cheers, Rob.

--

---
You received this message because you are subscribed to the Google Groups "Ebean ORM" group.
To unsubscribe from this group and stop receiving emails from it, send an email to ebean+un...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[Peace MICHAELS]

ok rob i will be waiting

> You received this message because you are subscribed to a topic in the

> Google Groups "Ebean ORM" group.

> To unsubscribe from this topic, visit
> https://groups.google.com/d/topic/ebean/tcqAvmHf_FI/unsubscribe.
> To unsubscribe from this group and all its topics, send an email to

[Rob Bygrave]

An example using JsonNode mapping to Postgres JSON db type is now at:

https://github.com/ebean-orm/avaje-ebeanorm-examples/commit/45d7e6760d1e2ce41ea5a499414214864af456e4

There are a 2 problems with this as it currently stands.

1) DDL Generation won't work correctly 

The problem here is that in say the Postgres case JsonNode can map to many DB types including JSON, JSONB, VARCHAR and TEXT/CLOB.  Right now the mechanism is not in place to choose the appropriate db column to use.  Going forward you'd probably choose between JSON or JSONB with Postgres.  

2) ObjectMapper not passed in

The internal "ScalarType" uses an ObjectMapper.  Ideally this could be passed in / injected but there is not the facility to do this at present.

I'll look to address these 2 issues in a future release of EbeanORM.

Cheers, Rob.

[Rob Bygrave]

FYI

I'm progressing the JSON storage so in a week this feature will be in.  

What this means is that in our entity beans we will be able to have properties of  type JsonNode (Jackson2) and Map<String,Object> and store these properties in the DB. For all DB's these will be storable into CLOB (default probably), BLOB and VARCHAR db columns.  

@Entity

public class MyDocument {

  ...

  // store this as Clob for most DB platforms and Postgres store in "json" 

  @DbJson

  JsonNode content; 

 // this is a Jackson 2 JsonNode ... no we won't add Jackson 2 bind as a required dependency (use provided scope)

}

or instead use a Map<String,Object> instead of jackson's JsonNode ...

@Entity

public class MyDocument {

  ...

  // store this as Clob for most DB platforms and Postgres store in "json" 

  @DbJson

  Map<String,Object> content;

}

For Postgres these can also be stored into it's json and jsonb data types ... and at this stage json will be the default storage probably.

In Postgres and Oracle we can make use of their built in json functions and indexes. Once the ElasticSearch integration is in then that provides extra/alternative indexing/search options (which will be better in many use cases where we are happy to give up a bit of read consistency - see data that might be 30 seconds out of date etc).

I realise this has bumped the priority on the documentation etc so sorry about that.

Cheers, Rob.

[Peace MICHAELS]

Cool!!

[Sean Brady]

Very cool!!

—
Sent from Mailbox

[Peace MICHAELS]

Rob any progress on this ?

[Rob Bygrave]

Good progress but not complete.  I'm happy with how it is looking/working and I've completed the support for Map<String,Object> and now need to do the same mapping support for JsonNode (and tidy up the existing HSTORE support a little bit).

To confess, I would have it completed it on the weekend but instead watched a couple of Rugby games and went looking at motorcycles. The rugby results were not too good but a new Kawasaki Z300 is coming my way next week so I'm pretty excited about that. Going out tonight so won't progress this until Wednesday night.

Cheers, Rob.

On 26 May 2015 at 03:33, Peace MICHAELS <orep...@gmail.com> wrote:

Rob any progress on this ?

[Rob Bygrave]

Ok that is in master now.

https://github.com/ebean-orm/avaje-ebeanorm/issues/297

This adds support for Map<String,Object> ... stored as JSON content to Clob, Blob, Varchar, JSON, JSONB.

https://github.com/ebean-orm/avaje-ebeanorm/issues/299

This adds support for JsonNode ... stored as JSON content to Clob, Blob, Varchar, JSON, JSONB.

Right now there is benefit in using Map<String,Object> over JsonNode in that it supports dirty checking (so you can mutate the content and Ebean knows if it has been mutated and includes the content in an update etc - with JsonNode there is no dirty checking so if the property is loaded then it is included in an update regardless).

You annotate the property with @DbJson or @DbJsonB.  Note that @DbJsonB is a 'shortcut' for @DbJson(storage = DbJsonType.JSONB).

Note that JSON on Postgres uses its JSON type and on all other DB's will use Clob.

Note that JSONB on Postgres uses its JSONB type and on all other DB's will use Clob (I think this is the most sensible fallback for non-Postgres)

You can explicitly specify to use VARCHAR, BLOB or CLOB via the storage property on @DbJson along with a length when using VARCHAR.

At the moment there isn't support for the extra Oracle DDL syntax (so if you start using this with Oracle's JSON support you may want to tweak Ebean's generated DDL as desired).

You can use Ebean's JSON support to marshall/unmarshall the entire bean from/to JSON.  Obviously we want to do this wrt the upcoming ElasticSearch integration.

Currently these properties will default to be eagerly fetched and it would be good to perhaps control that default behavior like we can with Clobs/Blobs.  At the moment use the select() clause on the where to control when there load as per normal properties.

The x-postgres-features project (https://github.com/ebean-orm/avaje-ebeanorm-examples/tree/master/x-postgres-features) has working examples of JSONB against Postgres database.

There are various tests for the combinations (Map/JsonNode properties to various db types) in com.avaje.tests.json package.

So yes, feel free to give it a whirl.  I intend to get another release this weekend (if you don't want to build from master yourself).

 

Also note that with the JSONB operators that Postgres supports some of these using '?' which is not great with JDBC. The best workaround that I know at this time is to create a database function (in order to utilise indexes etc).

Cheers, Rob.

[Peace MICHAELS]

how do i build from master?

> You received this message because you are subscribed to a topic in the

> Google Groups "Ebean ORM" group.

> To unsubscribe from this topic, visit
> https://groups.google.com/d/topic/ebean/tcqAvmHf_FI/unsubscribe.
> To unsubscribe from this group and all its topics, send an email to

[Rob Bygrave]

git clone the repo and use maven so to install a snapshot into your local .m2 repository (assumes you have maven installed) via:

mvn clean install

Then local projects on your machine can then depend on the specific snapshot version you have just installed. Is that what you mean or did I misunderstand?

Cheers, Rob.

[Carsten D.]

Rob, I just stumbled upon this since I'm using Play with Ebean ORM for convenience. Easy JSON binding with the "correct" datatype in Postgresql (json/jsonb) was something that I almost desperately wanted/needed. Thanks a lot for the quick implementation, it's the perfect solution for me (and many others I'm sure, since JSON is so important for certain data, even when using RDBMS).

And since Play 2.4 is released (I'm on 2.3.x, but will migrate to 2.4 soon) which allows for release-cycle independent Ebean 4.x integration, I think I'm all set.

[Rob Bygrave]

Thanks Carsten, it's always good to get postive feedback.  Yes, I'm pretty excited about the Postgres JSON/JSONB support - that certainly opens up some design options.  

The upcoming ElasticSearch integration also works in here. Once that is in we won't be short of really good options for mixing in some 'document orientation' and 'inverted index goodness' into our applications. 

Cheers, Rob.