Sunday, October 2, 2022
HomeJavause PostgreSQL's JSONB information sort with Hibernate

use PostgreSQL’s JSONB information sort with Hibernate


Most databases provide numerous proprietary options along with the identified SQL normal. One instance is PostgreSQL’s JSONB information sort which lets you retailer JSON paperwork effectively in a database column.

You would, in fact, additionally retailer the JSON doc in a textual content column. That’s a part of the SQL normal and supported by Hibernate and all different JPA implementations. Nonetheless, then you definately would miss out on PostgreSQL-specific options like JSON validation and a listing of fascinating JSON Capabilities and Operators. However you might be in all probability already conscious of that in case you are studying this put up.

If you wish to use a JSONB column with Hibernate 6, I’ve nice information for you. Hibernate 6 supplies a typical mapping for entity attributes to JSON columns; you solely have to activate it. Sadly, Hibernate 4 and 5 don’t assist any JSON mappings, and you must implement a UserType. I’ll present you each choices on this put up.

Database desk and entity

Let’s have a fast have a look at the database desk and entity earlier than we get into the main points of the UserType.
As you may see within the following code snippet, the definition of the database desk could be very easy and consists of solely 2 columns: the first key column id and the column jsonproperty of sort JSONB.

CREATE TABLE myentity
(
  id bigint NOT NULL,
  jsonproperty jsonb,
  CONSTRAINT myentity_pkey PRIMARY KEY (id)
)

And you’ll see the entity that maps the desk within the following code snippet.

@Entity
public class MyEntity {
 
    @Id
    @GeneratedValue
    personal Lengthy id;
 
    personal MyJson jsonProperty;
     
    ...
}

As you may see, there may be nothing JSON particular on this entity, solely an attribute of sort MyJson. MyJson is an easy POJO with 2 properties which you’ll see within the subsequent code snippet.

public class MyJson implements Serializable {
 
    personal String stringProp;
     
    personal Lengthy longProp;
 
    public String getStringProp() {
        return stringProp;
    }
 
    public void setStringProp(String stringProp) {
        this.stringProp = stringProp;
    }
 
    public Lengthy getLongProp() {
        return longProp;
    }
 
    public void setLongProp(Lengthy longProp) {
        this.longProp = longProp;
    }
}

So what do you must do if you wish to retailer the MyJson property in a JSONB database column? The reply to that is dependent upon the Hibernate model you’re utilizing.

In Hibernate 4 and 5, you have to implement a customized sort mapping. Don’t fear. That’s not as difficult as it’d sound. You solely have to implement the UserType interface and register your sort mapping. I’ll present you the way to do this on this article.

Hibernate 6 makes all of this even simpler. It supplies a typical JSON mapping that you have to activate. Let’s check out this primary.

JSONB mapping in Hibernate 6

Due to the JSON mapping launched in Hibernate 6, you solely have to annotate your entity attribute with a @JdbcTypeCode annotation and set the sort to SqlTypes.JSON. Hibernate then detects a JSON library in your classpath and makes use of it to serialize and deserialize the attribute’s worth.

@Entity
public class MyEntity {
 
    @Id
    @GeneratedValue
    personal Lengthy id;
 
    @JdbcTypeCode(SqlTypes.JSON)
    personal MyJson jsonProperty;
     
    ...
}

The @JdbcTypeCode annotation is a brand new annotation that was launched as a part of Hibernate’s new sort mapping. Beginning with Hibernate 6, you may outline the Java and the JDBC mapping individually by annotating your entity attribute with a @JdbcTypeCode or @JavaType annotation. Utilizing these annotations, you may reference considered one of Hibernate’s normal mappings or your individual implementations of the JavaTypeDescriptor or JdbcTypeDescriptor interfaces. I’ll clarify the implementation of these interfaces in one other tutorial. We solely have to activate Hibernate’s normal mapping.

After you annotate your entity attribute to activate Hibernate’s JSON mapping, you need to use the entity and its attribute in your corporation code. I ready an instance of that on the finish of this text.

JSONB mapping in Hibernate 4 and 5

As I discussed earlier, you have to implement a customized mapping if you wish to use PostgreSQL’s JSONB sort with Hibernate 4 or 5. One of the best ways to do this is to implement Hibernate’s UserType interface and register the mapping in a customized dialect.

Implement a Hibernate UserType

You first should create a Hibernate UserType, which maps the MyJson object right into a JSON doc and defines the mapping to an SQL sort. I name the UserType MyJsonType and present solely an important strategies within the following code snippets. You possibly can take a look on the total class within the GitHub repository.

There are a number of necessary issues you must do if you wish to implement your individual UserType. Initially, you must implement the strategies sqlTypes and returnedClass, which inform Hibernate the SQL sort and the Java class it shall use for this mapping. On this case, I take advantage of the generic Kind.JAVA_OBJECT because the SQL sort and, in fact, the MyJson class because the Java class.

public class MyJsonType implements UserType {
 
    @Override
    public int[] sqlTypes() {
        return new int[]{Sorts.JAVA_OBJECT};
    }
 
    @Override
    public Class<MyJson> returnedClass() {
        return MyJson.class;
    }
     
    ...
}

Then you must implement the strategies nullSafeGet and nullSafeSet, which Hibernate will name while you learn or write the attribute

The nullSafeGet methodology will get known as to map the worth from the database into the Java class. So we now have to parse the JSON doc right into a MyJson class. I take advantage of the Jackson ObjectMapper right here, however you can even use another JSON parser. 

The nullSafeSet methodology implements the mapping of the MyJson class into the JSON doc. Utilizing the Jackson library, you are able to do that utilizing the identical ObjectMapper as within the nullSafeGet methodology.

@Override
public Object nullSafeGet(closing ResultSet rs, closing String[] names, closing SessionImplementor session,
                          closing Object proprietor) throws HibernateException, SQLException {
    closing String cellContent = rs.getString(names[0]);
    if (cellContent == null) {
        return null;
    }
    strive {
        closing ObjectMapper mapper = new ObjectMapper();
        return mapper.readValue(cellContent.getBytes("UTF-8"), returnedClass());
    } catch (closing Exception ex) {
        throw new RuntimeException("Did not convert String to Bill: " + ex.getMessage(), ex);
    }
}
 
@Override
public void nullSafeSet(closing PreparedStatement ps, closing Object worth, closing int idx,
                        closing SessionImplementor session) throws HibernateException, SQLException {
    if (worth == null) {
        ps.setNull(idx, Sorts.OTHER);
        return;
    }
    strive {
        closing ObjectMapper mapper = new ObjectMapper();
        closing StringWriter w = new StringWriter();
        mapper.writeValue(w, worth);
        w.flush();
        ps.setObject(idx, w.toString(), Sorts.OTHER);
    } catch (closing Exception ex) {
        throw new RuntimeException("Did not convert Bill to String: " + ex.getMessage(), ex);
    }
}

One other necessary methodology you have to implement is the deepCopy methodology which has to create a deep copy of a MyJson object. One of many best methods to do this is to serialize and deserialize the MyJson object. This forces the JVM to create a deep copy of the thing.

@Override
public Object deepCopy(closing Object worth) throws HibernateException {
    strive {
        // use serialization to create a deep copy
        ByteArrayOutputStream bos = new ByteArrayOutputStream();
        ObjectOutputStream oos = new ObjectOutputStream(bos);
        oos.writeObject(worth);
        oos.flush();
        oos.shut();
        bos.shut();
         
        ByteArrayInputStream bais = new ByteArrayInputStream(bos.toByteArray());
        Object obj = new ObjectInputStream(bais).readObject();
        bais.shut();
        return obj;
    } catch (ClassNotFoundException | IOException ex) {
        throw new HibernateException(ex);
    }
}

Register the UserType

Within the subsequent step, you have to register your customized UserType. You are able to do this with a @TypeDef annotation within the package-info.java file. As you may see within the following code snippet, I set the title and the typeClass property of the @TypeDef annotation.

@org.hibernate.annotations.TypeDef(title = "MyJsonType", typeClass = MyJsonType.class)
 
bundle org.ideas.on.java.mannequin;

This hyperlinks the UserType MyJsonType to the title “MyJsonType” which I can then use with a @Kind annotation within the entity mapping.

@Entity
public class MyEntity {
 
    @Id
    @GeneratedValue(technique = GenerationType.AUTO)
    @Column(title = "id", updatable = false, nullable = false)
    personal Lengthy id;
 
    @Column
    @Kind(sort = "MyJsonType")
    personal MyJson jsonProperty;
     
    ...
 
}

And we’re virtually executed. Hibernate will now use the UserType MyJsonType to persist the jsonproperty attribute within the database. However there may be nonetheless one step left.

Hibernate dialect

Hibernate’s PostgreSQL dialect doesn’t assist the JSONB datatype, and you have to register it. You try this by extending an present dialect and calling the registerColumnType methodology within the constructor. I take advantage of a PostgreSQL database on this instance and prolong Hibernate’s PostgreSQL94Dialect.

public class MyPostgreSQL94Dialect extends PostgreSQL94Dialect {
 
    public MyPostgreSQL94Dialect() {
        this.registerColumnType(Sorts.JAVA_OBJECT, "jsonb");
    }
}

Now you may lastly retailer the MyJson object in a JSONB column.

use an entity with a JSONB mapping

As you noticed on this article, the issues you have to do to map an entity attribute to a JSONB column depend upon the Hibernate model you’re utilizing. However that’s not the case for your corporation code that makes use of the entity or its attribute. You should utilize the MyEntity entity and its MyJson attribute in the identical method as another entity. That additionally lets you substitute your UserType implementation with Hibernate’s normal dealing with while you migrate your software to Hibernate 6.

The next code snippet exhibits a easy instance that makes use of the EntityManager.discover methodology to get an entity from the database after which change the attribute values of the MyJson object.

MyEntity e = em.discover(MyEntity.class, 10000L);
e.getJsonProperty().setStringProp("modified");
e.getJsonProperty().setLongProp(789L);

And if you wish to choose an entity based mostly on some property values contained in the JSON doc, you need to use PostgreSQL’s JSON capabilities and operators with a local question.

MyEntity e = (MyEntity) em.createNativeQuery("SELECT * FROM myentity e WHERE e.jsonproperty->'longProp' = '456'", MyEntity.class).getSingleResult();

Abstract

PostgreSQL provides totally different proprietary datatypes, just like the JSONB sort I used on this put up, to retailer JSON paperwork within the database. 

Hibernate 6 supplies a typical JSON mapping. You solely have to activate it by annotating your entity attribute with a @JdbcTypeCode annotation and setting the sort to SqlTypes.JSON.

Hibernate 4 and 5 don’t assist these information sorts. It’s a must to implement the mapping your self. As you’ve gotten seen on this put up, you are able to do this by implementing the UserType interface, registering it with a @TypeDef annotation, and making a Hibernate dialect that registers the column sort.

RELATED ARTICLES

LEAVE A REPLY

Please enter your comment!
Please enter your name here

Most Popular

Recent Comments