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 MySQL json column and for querying JSON data from the MySQL 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 JsonStringType

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 = "json", typeClass = JsonStringType.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 = "json")
@Column(columnDefinition = "json")
private Book book;
}

AuthorRepository.java

@Repository
public interface AuthorRepository extends JpaRepository<Author, Long> {

public Author findByName(String name);

@Query("SELECT a FROM Author a "
+ "WHERE function('JSON_EXTRACT', a.book, '$.isbn') = ?1")

public Author findByBookIsbn(String isbn);

@Query(value = "SELECT a.* FROM author a WHERE JSON_EXTRACT(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 byNameIsbn() {
Author author = authorRepository.findByBookIsbn("001-JN");
System.out.println(author);
}

public void byBookIsbnNativeQuery() {
Author author = authorRepository.findByBookIsbnNativeQuery("001-JN");
System.out.println(author);
}
}

application.properties

spring.datasource.url=jdbc:mysql://localhost:3306/bookstoredb?createDatabaseIfNotExist=true&useSSL=false
spring.datasource.username=root
spring.datasource.password=root

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

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

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:");
bookstoreService.byNameIsbn();

System.out.println("\n\nFind author by the isbn of his book via a native query:");
bookstoreService.byBookIsbnNativeQuery();
};
}
}

Console Output —

Find author by name:
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:
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 JSON_EXTRACT(author0_.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))


Find author by the isbn of his book via a native query:
Hibernate: SELECT a.* FROM author a WHERE JSON_EXTRACT(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))

Database

mysql> show tables;
+-----------------------+
| Tables_in_bookstoredb |
+-----------------------+
| author |
+-----------------------+
1 row in set (0.00 sec)

mysql> select * from author;
+----+-----+-------------------------------------------------------------------------+---------+-------------+
| id | age | book | genre | name |
+----+-----+-------------------------------------------------------------------------+---------+-------------+
| 1 | 34 | {"isbn": "001-JN", "price": 45, "title": "A History of Ancient Prague"} | History | Joana Nimar |
+----+-----+-------------------------------------------------------------------------+---------+-------------+
1 row in set (0.00 sec)

--

--

--

Java Developer and enthusiast

Love podcasts or audiobooks? Learn on the go with our new app.

Recommended from Medium

Linux Server for AI: The Step-by-Step Guide

Report your AWS Costs programmatically using the Cost Explorer API

Fork 🍴 the F*ing Ethereum Blockchain! Transfer tokens from Vitalik’s Account ;)

Monitor the Performance of your Heroku Rails App with New Relic

You can start learning Python from level 0

The SOLID design principles: Explained

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store
PA

PA

Java Developer and enthusiast

More from Medium

Exception Handling in Spring webflux

Creating your first Spring Boot application

Spring Boot featured image

JPA Key Notes

Java Spring Boot RestAPI CRUD with MySQL