JPA — How To Handle JSON in MySQL

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)