JPA- How To Handle JSON in PostgreSQL

Hibernate Types is a library of extra types not supported by Hibernate Core by default. This is a Spring Boot application that uses this library to persist JSON data (JSON Java Object) in a PostgreSQL json column and for querying JSON data from the PostgreSQL json column to JSON Java Object. Updates are supported as well.

Key points:

• for Maven, add Hibernate Types as a dependency in pom.xml
• in entity use @TypeDef to map typeClass to JsonBinaryType

Book.java

@Data
public class Book implements Serializable {

    private static final long serialVersionUID = 1L;  

    private String title;
    private String isbn; 
    private int price;
}

Author.java

@Data
@Entity
@TypeDef(name = "jsonb", typeClass = JsonBinaryType.class)
public class Author implements Serializable {

    private static final long serialVersionUID = 1L;

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;

    private String name;
    private String genre;
    private int age;

    @Type(type = "jsonb")
    @Column(columnDefinition = "jsonb") // or, json
    private Book book;
}

AuthorRepository.java

@Repository
public interface AuthorRepository extends JpaRepository<Author, Long> {        
    
    public Author findByName(String name);
    
    @Query(value = "SELECT a.* FROM author a "
            + "WHERE CAST(a.book ->> 'price' AS INTEGER) = ?1",
            nativeQuery = true)
    public Author findByBookPriceNativeQueryCast(int price);
    
    @Query(value = "SELECT a.* FROM author a "
            + "WHERE a.book ->> 'isbn' = ?1",
            nativeQuery = true)
    public Author findByBookIsbnNativeQuery(String isbn);
}

BookStoreService.java

@RequiredArgsConstructor
@Service
public class BookstoreService {

    private final AuthorRepository authorRepository;

    public void newAuthor() {
        Book book = new Book();
        book.setIsbn("001-JN");
        book.setTitle("A History of Ancient Prague");
        book.setPrice(45);

        Author author = new Author();
        author.setName("Joana Nimar");
        author.setAge(34);
        author.setGenre("History");
        author.setBook(book);

        authorRepository.save(author);
    }

    public void byName() {
        Author author = authorRepository.findByName("Joana Nimar");
        System.out.println(author);
    }
    
    public void byBookIsbnNativeQueryCast() {
        Author author = authorRepository.findByBookPriceNativeQueryCast(45);
        System.out.println(author);
    }
    
    public void byBookIsbnNativeQuery() {
        Author author = authorRepository.findByBookIsbnNativeQuery("001-JN");
        System.out.println(author);
    }
}

application.properties

spring.datasource.url=jdbc:postgresql://localhost:5432/postgres
spring.datasource.username=postgres
spring.datasource.password=postgres

spring.jpa.hibernate.ddl-auto=create
spring.jpa.show-sql=true

spring.jpa.properties.hibernate.dialect=org.hibernate.dialect.PostgreSQL95Dialect

spring.datasource.initialization-mode=always
spring.datasource.platform=postgresql

spring.jpa.properties.hibernate.jdbc.lob.non_contextual_creation=true

spring.jpa.open-in-view=false

MainApp.java

@RequiredArgsConstructor
@SpringBootApplication
public class MainApplication {

    private final BookstoreService bookstoreService;

    public static void main(String[] args) {
        SpringApplication.run(MainApplication.class, args);
    }

    @Bean
    public ApplicationRunner init() {
        return args -> {
            System.out.println("\n\nPersist a new author:");
            bookstoreService.newAuthor();
            
            System.out.println("\n\nFind author by name:");
            bookstoreService.byName();
            
            System.out.println("\n\nFind author by the isbn of his book via a native query and cast:");
            bookstoreService.byBookIsbnNativeQueryCast();
            
            System.out.println("\n\nFind author by the isbn of his book via a native query:");
            bookstoreService.byBookIsbnNativeQuery();
        };
    }
}

Console Output —

Persist a new author:
    Hibernate: insert into author (age, book, genre, name) values (?, ?, ?, ?)

Find author by name:
2021-06-25 20:08:04.172  INFO 17560 --- [           main] o.h.h.i.QueryTranslatorFactoryInitiator  : HHH000397: Using ASTQueryTranslatorFactory
Hibernate: select author0_.id as id1_0_, author0_.age as age2_0_, author0_.book as book3_0_, author0_.genre as genre4_0_, author0_.name as name5_0_ from author author0_ where author0_.name=?
    Author(id=1, name=Joana Nimar, genre=History, age=34, book=Book(title=A History of Ancient Prague, isbn=001-JN, price=45))


Find author by the isbn of his book via a native query and cast:
    Hibernate: SELECT a.* FROM author a WHERE CAST(a.book ->> 'price' AS INTEGER) = ?
    Author(id=1, name=Joana Nimar, genre=History, age=34, book=Book(title=A History of Ancient Prague, isbn=001-JN, price=45))


Find author by the isbn of his book via a native query:
    Hibernate: SELECT a.* FROM author a WHERE a.book ->> 'isbn' = ?
    Author(id=1, name=Joana Nimar, genre=History, age=34, book=Book(title=A History of Ancient Prague, isbn=001-JN, price=45))