Wednesday 9 November 2022

Hibernate: @Formula: model computed value as a virtual read-only column

There are situations, where you want to calculate a value from other entity properties by applying some computations. You can do this with the help of @Formula annotation.

 

Example

@Entity
@Table(name = "account")
public class Account {

    @Id
    private Integer accountId;

    private Double principal;
    private Double timeInYears;
    private Double rateOfIntrest;

    @Formula(value = "(principal * timeInYears * rateOfIntrest ) /100")
    private Double interestToPay;


    //Getters and setters omitted for brevity

}

When loading the Account entity, Hibernate is going to calculate the interestToPay property using the configured @Formula.

 

Find the below working application.

 

Step 1: Create new maven project ‘hibernate-formula-annotation-demo’.

 

Step 2: Update pom.xml with maven dependencies.

 

pom.xml
<project xmlns="http://maven.apache.org/POM/4.0.0"
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 https://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>
    <groupId>com.sample.app</groupId>
    <artifactId>hibernate-formula-annotation-demo</artifactId>
    <version>1</version>

    <properties>
        <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>

        <java.version>15</java.version>
        <maven.compiler.source>${java.version}</maven.compiler.source>
        <maven.compiler.target>${java.version}</maven.compiler.target>

    </properties>

    <dependencies>
        <dependency>
            <groupId>org.postgresql</groupId>
            <artifactId>postgresql</artifactId>
            <version>42.4.1</version>
        </dependency>

        <dependency>
            <groupId>org.hibernate</groupId>
            <artifactId>hibernate-core</artifactId>
            <version>6.1.2.Final</version>
        </dependency>

        <dependency>
            <groupId>javax.persistence</groupId>
            <artifactId>javax.persistence-api</artifactId>
            <version>2.2</version>
        </dependency>


    </dependencies>
</project>

 

Step 3: Define Account entity class.

 

Account.java

 

package com.sample.app.entity;

import org.hibernate.annotations.Formula;

import jakarta.persistence.Entity;
import jakarta.persistence.Id;
import jakarta.persistence.Table;

@Entity
@Table(name = "account")
public class Account {

    @Id
    private Integer accountId;

    private Double principal;
    private Double timeInYears;
    private Double rateOfIntrest;

    @Formula(value = "(principal * timeInYears * rateOfIntrest ) /100")
    private Double interestToPay;

    public Account(Integer accountId, Double principal, Double timeInYears, Double rateOfIntrest) {
        this.accountId = accountId;
        this.principal = principal;
        this.timeInYears = timeInYears;
        this.rateOfIntrest = rateOfIntrest;
    }

    public Integer getAccountId() {
        return accountId;
    }

    public void setAccountId(Integer accountId) {
        this.accountId = accountId;
    }

    public Double getPrincipal() {
        return principal;
    }

    public void setPrincipal(Double principal) {
        this.principal = principal;
    }

    public Double getRateOfIntrest() {
        return rateOfIntrest;
    }

    public void setRateOfIntrest(Double rateOfInrest) {
        this.rateOfIntrest = rateOfInrest;
    }

    public Double getTimeInYears() {
        return timeInYears;
    }

    public void setTimeInYears(Double timeInYears) {
        this.timeInYears = timeInYears;
    }

    public Double getInterestToPay() {
        return interestToPay;
    }

    public void setInterestToPay(Double interestToPay) {
        this.interestToPay = interestToPay;
    }

}

 

Step 4: Create hibernate.cfg.xml file under src/main/resources folder.

 

hibernate.cfg.xml

 

<?xml version='1.0' encoding='utf-8'?>
<!DOCTYPE hibernate-configuration PUBLIC
        "-//Hibernate/Hibernate Configuration DTD 3.0//EN"
        "http://www.hibernate.org/dtd/hibernate-configuration-3.0.dtd">
<hibernate-configuration>

    <session-factory>

        <!-- Database Connection settings -->
        <property name="connection.driver_class">org.postgresql.Driver</property>
        <property name="connection.url">jdbc:postgresql://127.0.0.1:5432/test</property>
        <property name="connection.username">postgres</property>
        <property name="connection.password">postgres</property>

        <!-- Enable the logging of all the generated SQL statements to the console -->
        <property name="show_sql">true</property>

        <!-- Format the generated SQL statement to make it more readable, -->
        <property name="format_sql">true</property>

        <!-- Hibernate will put comments inside all generated SQL statements to 
            hint what’s the generated SQL trying to do -->
        <property name="use_sql_comments">false</property>

        <property name="dialect">org.hibernate.dialect.PostgreSQLDialect</property>

        <!-- Drop and re-create the database schema on startup -->
        <property name="hbm2ddl.auto">create</property>

        <!-- mappings for annotated classes -->
        <mapping class="com.sample.app.entity.Account" />

    </session-factory>

</hibernate-configuration>

 

Step 5: Define main application class.

 

App.java

 

package com.sample.app;

import org.hibernate.Session;
import org.hibernate.SessionFactory;
import org.hibernate.boot.Metadata;
import org.hibernate.boot.MetadataSources;
import org.hibernate.boot.registry.StandardServiceRegistry;
import org.hibernate.boot.registry.StandardServiceRegistryBuilder;

import com.sample.app.entity.Account;

public class App {
    private static final SessionFactory SESSION_FACTORY = buildSessionFactory();

    private static SessionFactory buildSessionFactory() {
        try {

            final StandardServiceRegistry standardRegistry = new StandardServiceRegistryBuilder()
                    .configure("hibernate.cfg.xml").build();

            final Metadata metaData = new MetadataSources(standardRegistry).getMetadataBuilder().build();

            return metaData.getSessionFactoryBuilder().build();

        } catch (Throwable ex) {
            throw new ExceptionInInitializerError(ex);
        }

    }

    public static void main(final String args[]) {

        Account account = new Account(1, 1000000.0, 2.3, 2.0);

        try (final Session session = SESSION_FACTORY.openSession()) {
            session.beginTransaction();

            session.persist(account);

            // Since entity is cached on persist, lets flush and refresh the entity to
            // refetch the data from database.
            session.flush();
            session.refresh(account);

            session.getTransaction().commit();

            Account persistedAccount = session.find(Account.class, 1);

            System.out.println("prinicpal : " + persistedAccount.getPrincipal());
            System.out.println("timeInYears : " + persistedAccount.getTimeInYears());
            System.out.println("rateOfInrest : " + persistedAccount.getRateOfIntrest());
            System.out.println("interestToPay : " + persistedAccount.getInterestToPay());

        }

    }
}

Total project structure looks like below.



Run App.java, you will see below messages in console.

Aug 09, 2022 8:54:03 PM org.hibernate.Version logVersion
INFO: HHH000412: Hibernate ORM core version 6.1.2.Final
Aug 09, 2022 8:54:03 PM org.hibernate.engine.jdbc.connections.internal.DriverManagerConnectionProviderImpl configure
WARN: HHH10001002: Using built-in connection pool (not intended for production use)
Aug 09, 2022 8:54:03 PM org.hibernate.engine.jdbc.connections.internal.DriverManagerConnectionProviderImpl buildCreator
INFO: HHH10001005: Loaded JDBC driver class: org.postgresql.Driver
Aug 09, 2022 8:54:03 PM org.hibernate.engine.jdbc.connections.internal.DriverManagerConnectionProviderImpl buildCreator
INFO: HHH10001012: Connecting with JDBC URL [jdbc:postgresql://127.0.0.1:5432/test]
Aug 09, 2022 8:54:03 PM org.hibernate.engine.jdbc.connections.internal.DriverManagerConnectionProviderImpl buildCreator
INFO: HHH10001001: Connection properties: {password=****, user=postgres}
Aug 09, 2022 8:54:03 PM org.hibernate.engine.jdbc.connections.internal.DriverManagerConnectionProviderImpl buildCreator
INFO: HHH10001003: Autocommit mode: false
Aug 09, 2022 8:54:03 PM org.hibernate.engine.jdbc.connections.internal.DriverManagerConnectionProviderImpl$PooledConnections <init>
INFO: HHH10001115: Connection pool size: 20 (min=1)
Aug 09, 2022 8:54:04 PM org.hibernate.engine.jdbc.dialect.internal.DialectFactoryImpl logSelectedDialect
INFO: HHH000400: Using dialect: org.hibernate.dialect.PostgreSQLDialect
Aug 09, 2022 8:54:04 PM org.hibernate.metamodel.internal.EntityInstantiatorPojoStandard resolveConstructor
INFO: HHH000182: No default (no-argument) constructor for class: com.sample.app.entity.Account (class must be instantiated by Interceptor)
Hibernate: 
    
    drop table if exists account cascade
Aug 09, 2022 8:54:04 PM org.hibernate.resource.transaction.backend.jdbc.internal.DdlTransactionIsolatorNonJtaImpl getIsolatedConnection
INFO: HHH10001501: Connection obtained from JdbcConnectionAccess [org.hibernate.engine.jdbc.env.internal.JdbcEnvironmentInitiator$ConnectionProviderJdbcConnectionAccess@41dc0598] for (non-JTA) DDL execution was not in auto-commit mode; the Connection 'local transaction' will be committed and the Connection will be set into auto-commit mode.
Aug 09, 2022 8:54:04 PM org.hibernate.engine.jdbc.spi.SqlExceptionHelper$StandardWarningHandler logWarning
WARN: SQL Warning Code: 0, SQLState: 00000
Aug 09, 2022 8:54:04 PM org.hibernate.engine.jdbc.spi.SqlExceptionHelper$StandardWarningHandler logWarning
WARN: table "account" does not exist, skipping
Hibernate: 
    
    create table account (
       accountId integer not null,
        principal float(53),
        rateOfInrest float(53),
        timeInYears float(53),
        primary key (accountId)
    )
Aug 09, 2022 8:54:04 PM org.hibernate.resource.transaction.backend.jdbc.internal.DdlTransactionIsolatorNonJtaImpl getIsolatedConnection
INFO: HHH10001501: Connection obtained from JdbcConnectionAccess [org.hibernate.engine.jdbc.env.internal.JdbcEnvironmentInitiator$ConnectionProviderJdbcConnectionAccess@3e4e4c1] for (non-JTA) DDL execution was not in auto-commit mode; the Connection 'local transaction' will be committed and the Connection will be set into auto-commit mode.
Aug 09, 2022 8:54:04 PM org.hibernate.engine.transaction.jta.platform.internal.JtaPlatformInitiator initiateService
INFO: HHH000490: Using JtaPlatform implementation: [org.hibernate.engine.transaction.jta.platform.internal.NoJtaPlatform]
Hibernate: 
    insert 
    into
        account
        (principal, rateOfInrest, timeInYears, accountId) 
    values
        (?, ?, ?, ?)
Hibernate: 
    select
        a1_0.accountId,
        (a1_0.principal * a1_0.timeInYears * a1_0.rateOfInrest ) /100,
        a1_0.principal,
        a1_0.rateOfInrest,
        a1_0.timeInYears 
    from
        account a1_0 
    where
        a1_0.accountId=?
prinicpal : 1000000.0
timeInYears : 2.3
rateOfInrest : 2.0
interestToPay : 46000.0

Data in PostgresSQL is given below.

test=# \d+ account;
                                                 Table "public.account"
    Column     |       Type       | Collation | Nullable | Default | Storage | Compression | Stats target | Description 
---------------+------------------+-----------+----------+---------+---------+-------------+--------------+-------------
 accountid     | integer          |           | not null |         | plain   |             |              | 
 principal     | double precision |           |          |         | plain   |             |              | 
 rateofintrest | double precision |           |          |         | plain   |             |              | 
 timeinyears   | double precision |           |          |         | plain   |             |              | 
Indexes:
    "account_pkey" PRIMARY KEY, btree (accountid)
Access method: heap

test=# 
test=# SELECT * FROM account;
 accountid | principal | rateofintrest | timeinyears 
-----------+-----------+---------------+-------------
         1 |   1000000 |             2 |         2.3
(1 row)

You can download complete working application from this link.


  

Previous                                                    Next                                                    Home

No comments:

Post a Comment