PostgreSQL and Hibernate CLOB

postgresql-and-hibernate-clob

When you need a SQL data type that can hold more than 255 characters, you are likely to use some large character data type such as CLOB, BLOB. It is easy to find the right type if you know what exactly the database you will stick to. However, when we use Hibernate, it provides the annotations that support the cross-platform data type translation.

Story

I like to use H2 embedded database as a starting point when I start a new project, but later on, when I switch to use PostgreSQL, I was prompted org.postgresql.jdbc.PgConnection.createClob() is not yet implemented.

I have one property in my class is a String type, as I marked as @Lob and wish Hibernate will take care of the rest for me, but NO. I still get the same exception.

Solution

To solve this, I need to add the additional @Type(org.hibernate.type.TextType) annotation to the property as well, and then it works nicely with PostgreSQL in addition to H2.

Lesson Learnt

  • org.hibernate.type.TextType - A type that maps an SQL LONGVARCHAR to a Java String
  • @Type - use this to define one of the Hibernate mappings to gain cross-platform support.

Comments

Post a Comment

Popular posts from this blog

Creating a Library Project for Spring Boot

creating-a-library-project-for-spring-boot This article introduces the key points when creating a library for spring boot project. It could be common that over the years you have created a number of services that could be reused by other spring projects. It then makes sense to make the reusable modules such as a library and install it in your local repository for later use. Here are the key points to consider when building a spring boot library. release version build plugin dependency injection autoconfigurations Release Version To guarantee spring boot works the way you expect, it is recommended to keep update the release versions of the library so that it supports some versions of the spring boot minor version. A good practice is to monitor change spring boot changelog and see if there are any conflicting dependencies that are used in the library project. If there is, introduce a new version to resolve the conflict. Build Plugin If the projects refere
Read more

Spring Data Repository Query Precedence Tricks

spring-data-repository-query-precedence-tricks Spring Data repository method is very handy but it also comes with its limitation, especially when composed with condition precedence. This article shows one way to work with it and its caveats. Use Case Given the following type: @Entity public class Book { @Id private Long id ; private String title ; private String edition ; // Getters and Setters ... } 1. check if there is any book whose title or edition matches the specified. This one is easy to come up with. We could simply use the keyword Or to compose the query: boolean existsByTitleOrEdition ( String title , String edition ) ; 2. check if there is any book whose title or edition matches the specified and is not itself. This one looks just as easy as the first one, we could have just added another condition and compose with the keyword And : boolean existsByTitleOrEditionAndIdIsNot ( String title , String edi
Read more

Serialize Timestamp To EpochSecond with Jackson

serialize-timestamp-to-epochsecond-with-jackson Some REST API only takes a long as its Date field, such as esriFieldTypeDate from ArcGIS Server Rest API. But with the rich functionality, the Java 8 time API provides, it would be silly to use long as the type for our object modeling. This tip shows how to solve this problem. I started with using LocalDateTime as the type of the timestamp field I want to use`, but Jackson simply serialize it as it does with any other fields, but what I really want is to serialize it to the epoch second. Jackson does provide a new library that supports the JSR 310 which is the date and time API. to use that dependency, we need to add the following to maven: <dependency> <groupId>com.fasterxml.jackson.datatype</groupId> <artifactId>jackson-datatype-jsr310</artifactId> </dependency> At this point, the job is not done yet. The LocalDateTime is not an option because it takes a timezone
Read more