enxt/hibernate-native-json

Hibernate user-json type for mysql and postgresql

Download

Step 1. Add the JitPack repository to your build file

Add it in your root settings.gradle at the end of repositories:

	dependencyResolutionManagement {
		repositoriesMode.set(RepositoriesMode.FAIL_ON_PROJECT_REPOS)
		repositories {
			mavenCentral()
			maven { url 'https://jitpack.io' }
		}
	}

Add it in your settings.gradle.kts at the end of repositories:

	dependencyResolutionManagement {
		repositoriesMode.set(RepositoriesMode.FAIL_ON_PROJECT_REPOS)
		repositories {
			mavenCentral()
			maven { url = uri("https://jitpack.io") }
		}
	}

Add to pom.xml

	<repositories>
		<repository>
		    <id>jitpack.io</id>
		    <url>https://jitpack.io</url>
		</repository>
	</repositories>

Add it in your build.sbt at the end of resolvers:

 
    resolvers += "jitpack" at "https://jitpack.io"

Add it in your project.clj at the end of repositories:

 
    :repositories [["jitpack" "https://jitpack.io"]]

Step 2. Add the dependency

	dependencies {
		implementation 'com.github.enxt:hibernate-native-json:2.1'
	}

	dependencies {
		implementation("com.github.enxt:hibernate-native-json:2.1")
	}

	<dependency>
	    <groupId>com.github.enxt</groupId>
	    <artifactId>hibernate-native-json</artifactId>
	    <version>2.1</version>
	</dependency>


                            
    libraryDependencies += "com.github.enxt" % "hibernate-native-json" % "2.1"


                            
    :dependencies [[com.github.enxt/hibernate-native-json "2.1"]]

Readme

hibernate-native-json

Read/Write an object to JSON / JSON to object into a database table field (declared as a string column). This also allow to query json.

Currently supported databases:

postgis
mariadb

This project provided a hibernate UserType and a dialect with json support.

The UserType uses jackson object mappper to do a fast serialize/deserialize of a json string representation. More information how to implements a user type

Check the src/test folder to see a full example.

Example

You can serialize either a class or a Map<String, Object> (in any cases a more dynamic field is necessary).

@TypeDef(name = "json", typeClass = org.hibernate.type.json.JsonStringType.class)
@Entity
public class MyClass {
    @Type(type = "json")
    private Map<String, Object> extra;
}

Now you can persist your object as a json using your hibernate session / jpa repository.

Querying

In order to use the new json_text function you need register this in you hibernate with the propertie (only abailable from 5.3 version of hibernate):

hibernate.metadata_builder_contributor=org.hibernate.type.json.MariaDBSqlJsonFunctionsBuilder

hibernate.metadata_builder_contributor=org.hibernate.type.json.PostgreSqlJsonFunctionsBuilder

ensure that it's registered

json_text: is equivalent to postgres ->> get JSON object field as text http://www.postgresql.org/docs/9.5/static/functions-json.html

json_text: is equivalent to mariadb JSON_UNQUOTE(JSON_EXTRACT()) get JSON object field as text https://mariadb.com/kb/en/library/json-functions/

This allow a HQL query like this:

    select
        json_text(i.label, 'value')
    from
        Item i
    where
        json_text(i.label, 'lang') = :lang

Witch will produce the following SQL in PostgreSQL:

    select
        item0_.label ->> 'value' as col_0_0_ 
    from
        items item0_ 
    where
        item0_.label ->> 'lang'=?

and will product the following SQL in MariaDB:

    select
        JSON_UNQUOTE(JSON_EXTRACT(item0_.label, '$.value')) as col_0_0_ 
    from
        items item0_ 
    where
        JSON_UNQUOTE(JSON_EXTRACT(item0_.label, '$.lang')) = ?