JPA — How To Calculate Non-Persistent Property via Hibernate @Formula

If you prefer to read it as a blog-post containing the relevant snippets of code then check this post

Description: This application is an example of calculating a non-persistent property of an entity based on the persistent entity attributes. In this case, we will use Hibernate, @Formula.

Key points:

• annotate the non-persistent property with @Transient
• annotate the non-persistent field with @Formula
• as the value of @Formula add the SQL query expression that calculates this non-persistent property based on the persistent entity attributes

Book.java

@AllArgsConstructor
@NoArgsConstructor
@Data
@Entity
public class Book implements Serializable {

    private static final long serialVersionUID = 1L;
 
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    protected Long id;

    private String title;
    private String isbn;
    private double price;

    @Formula("price - price * 0.25")
    private double discounted;
}

BookRepository.java

@Repository
public interface BookRepository extends JpaRepository<Book, Long> {      
}

BookStoreService.java

@Service
@RequiredArgsConstructor
public class BookstoreService {
         
    private final BookRepository bookRepository;

    public void fetchBooks() {
        List<Book> books = bookRepository.findAll();
        books.forEach(System.out::println);
    }
}

MainApp.java

@SpringBootApplication
@RequiredArgsConstructor
public class MainApplication {

    private final BookstoreService bookstoreService;

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

    @Bean
    public ApplicationRunner init() {
        return args -> {
            bookstoreService.fetchBooks();
        };
    }
}

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.MySQL5Dialect

spring.jpa.open-in-view=false

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

data-mysql.sql

INSERT INTO `book` (`id`, `isbn`, `title`, `price`) VALUES (1,'Isbn_1','Title_1',10.99);
INSERT INTO `book` (`id`, `isbn`, `title`, `price`) VALUES (2,'Isbn_2','Title_2',23.05);
INSERT INTO `book` (`id`, `isbn`, `title`, `price`) VALUES (3,'Isbn_3','Title_3',41.99);
INSERT INTO `book` (`id`, `isbn`, `title`, `price`) VALUES (4,'Isbn_4','Title_4',15.15);
INSERT INTO `book` (`id`, `isbn`, `title`, `price`) VALUES (5,'Isbn_5','Title_5',29.80);

Console Output —

Hibernate: select book0_.id as id1_0_, book0_.isbn as isbn2_0_, book0_.price as price3_0_, book0_.title as title4_0_, book0_.price — book0_.price * 0.25 as formula0_ from book book0_
Book(id=1, title=Title_1, isbn=Isbn_1, price=10.99, discounted=8.2425)
Book(id=2, title=Title_2, isbn=Isbn_2, price=23.05, discounted=17.2875)
Book(id=3, title=Title_3, isbn=Isbn_3, price=41.99, discounted=31.4925)
Book(id=4, title=Title_4, isbn=Isbn_4, price=15.15, discounted=11.3625)
Book(id=5, title=Title_5, isbn=Isbn_5, price=29.8, discounted=22.35)

— — — — — — — — — — — — — — — — — — — — — — — —

How To Calculate Non-Persistent Property via JPA @PostLoad

Description: This application is an example of calculating a non-persistent property of an entity based on the persistent entity attributes. In this case, we will use JPA, @PostLoad.

Key points:

• annotate the non-persistent field and property with @Transient
• define a method annotated with @PostLoad that calculates this non-persistent property based on the persistent entity attributes

Book.java

@AllArgsConstructor
@NoArgsConstructor
@Data
@Builder
@Entity
public class Book implements Serializable {

    private static final long serialVersionUID = 1L;

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

    private String title;
    private String isbn;
    private double price;

    @Transient
    private double discounted;

    @PostLoad
    private void postLoad() {
        this.discounted = this.price - this.price * 0.25; 
    }
}

— — — — — — — — — — — — — — — — — — -

How To Calculate Entity Persistent Property Via Hibernate @Generated

Description: This application is an example of calculating an entity persistent property at INSERT and/or UPDATE time via Hibernate, @Generated.

Key points:

Calculate at INSERT time:

• annotate the corresponding persistent field with @Generated(value = GenerationTime.INSERT)
• annotate the corresponding persistent field with @Column(insertable = false)

Calculate at INSERT and UPDATE time:

• annotate the corresponding persistent field with @Generated(value = GenerationTime.ALWAYS)
• annotate the corresponding persistent field with @Column(insertable = false, updatable = false)

Further, apply:

Method 1:

• if the database schema is generated via JPA annotations (not recommended) then use columnDefinition element of @Column to specify as an SQL query expression the formula for calculating the persistent property

Method 2:

• if the database schema is not generated via JPA annotations (recommended way) then add the formula as part of schema in CREATE TABLE

Note: In production, you should not rely on columnDefinition. You should disable hibernate.ddl-auto (by omitting it) or set it to validate, and add the SQL query expression in CREATE TABLE (in this application, check the discount column in CREATE TABLE, file schema-sql.sql). Nevertheless, not even schema-sql.sql is ok in production. The best way is to rely on Flyway or Liquibase.

Book.java

@AllArgsConstructor
@NoArgsConstructor
@Data
@Entity
public class Book implements Serializable {

    private static final long serialVersionUID = 1L;

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

    private String title;
    private String isbn;
    private double price;

    @Generated(value = GenerationTime.ALWAYS)
    @Column(insertable = false, updatable = false /*, columnDefinition = "double AS (price - price * 0.25)"*/)
    private double discounted;
}

BookStoreService.java

@Service
@RequiredArgsConstructor
public class BookstoreService {

    private final BookRepository bookRepository;

    public void insertBook() {
        Book book = new Book();
        book.setTitle("Ancient History");
        book.setIsbn("001-AH");
        book.setPrice(13.99);

        bookRepository.save(book);
        System.out.println("Discounted price after insert: " + book.getDiscounted());
    }

    @Transactional
    public void updateBook() {
        Book book = bookRepository.findByTitle("Ancient History");
        book.setPrice(9.99);
        
        bookRepository.flush();
        System.out.println("Discounted price after update: " + book.getDiscounted());
    }
}