Hibernate Subselect Annotation

Mary ZhengMay 27th, 2024Last Updated: May 27th, 2024

0 929 13 minutes read

1. Introduction

Hibernate is an open source object relational mapping (ORM) tool that provides a framework to map object-oriented domain models to relational databases for web applications. The @org.hibernate.annotations.Subselect annotation is a Hibernate-specific feature and is used to map an immutable entity to a SQL subselect statement. Hibernate does not support insert, update, or delete operations on these immutable entities. Here is the Hibernate subselect annotation definition:

@Subselect Definition

/**
 * Maps an immutable and read-only entity to a given SQL {@code select} expression.
 * <p>
 * This is an alternative to defining a database view and mapping the entity to
 * the view using the {@link jakarta.persistence.Table @Table} annotation.
 *
 * @see Synchronize
 *
 * @author Sharath Reddy
 */
@Target(TYPE)
@Retention(RUNTIME)
public @interface Subselect {
    /**
     * The query.
     */
    String value();
}</p>

As you see from the definition. @Subselect is an alternative way to map an entity to a database view or subquery. In this example, I will create a spring boot web application which relies on two hibernate entities: Customer and Order and two immutable entities: CustomerOrder and MajorCustomer.

2. Set up Maven Spring Boot Web Application

Navigate to Spring Initializr, select Maven, Java, and “Spring Data JPA”, “H2 Database”, and “Spring Web” dependencies as the following screenshot:

Imported the generated maven spring boot project into Eclipse IDE. Verified the pom.xml as the following.

pom.xml

<?xml version="1.0" encoding="UTF-8"?>
<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>
    <parent>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-parent</artifactId>
        <version>3.3.0</version>
        <relativePath /> <!-- lookup parent from repository -->
    </parent>
    <groupId>org.zheng.demo</groupId>
    <artifactId>sebselect-demo</artifactId>
    <version>0.0.1-SNAPSHOT</version>
    <name>sebselect-demo</name>
    <description>Demo project for Spring Boot</description>
    <properties>
        <java.version>17</java.version>
    </properties>
    <dependencies>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-data-jpa</artifactId>
        </dependency>
 
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>
 
 
        <dependency>
            <groupId>com.h2database</groupId>
            <artifactId>h2</artifactId>
            <scope>runtime</scope>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
            <scope>test</scope>
        </dependency>
    </dependencies>
 
    <build>
        <plugins>
            <plugin>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-maven-plugin</artifactId>
            </plugin>
        </plugins>
    </build>
 
</project>

3. Customer and Order Entities

In this example, I will create two entities and map them to two tables.

3.1 Customer Entity

In this step, I will create a Customer class which annotates with the @Entity annotation. The Customer class has five members:

emailAddress– the customer email address.
id – the primary key of the customer.
name – the customer’s name.
type – the customer type. If the customer’s order amount exceeds 5000, then it is a major customer.
orders – a list of orders belong to this customer.

Customer.java

package org.zheng.demo.entity;
 
import java.util.List;
 
import jakarta.persistence.Entity;
import jakarta.persistence.GeneratedValue;
import jakarta.persistence.GenerationType;
import jakarta.persistence.Id;
import jakarta.persistence.OneToMany;
 
@Entity(name = "T_Customer")
public class Customer {
    private String emailAddress;
 
    @Id
    @GeneratedValue(strategy = GenerationType.AUTO)
    private Long id;
 
    private String name;
 
    @OneToMany(mappedBy = "customer")
    private List<Order> orders;
 
    private String type;
 
    public Customer() {
        super();
    }
 
    public Customer(String name, String emailAddress) {
        super();
        this.name = name;
        this.emailAddress = emailAddress;
    }
 
    public String getEmailAddress() {
        return emailAddress;
    }
 
    public Long getId() {
        return id;
    }
 
    public String getName() {
        return name;
    }
 
    public List<Order> getOrders() {
        return orders;
    }
 
    public String getType() {
        return type;
    }
 
    public void setEmailAddress(String emailAddress) {
        this.emailAddress = emailAddress;
    }
 
    public void setId(Long id) {
        this.id = id;
    }
 
    public void setName(String name) {
        this.name = name;
    }
 
    public void setOrders(List<Order> orders) {
        this.orders = orders;
    }
 
    public void setType(String type) {
        this.type = type;
    }
 
    @Override
    public String toString() {
        return "Customer [emailAddress=" + emailAddress + ", id=" + id + ", name=" + name + ", type=" + type + "]";
    }
 
}

Line 11: annotates with @Entity and maps to H2 table T_Customer.
Line 15, 16: annotates with @Id and @GeneratedValue that auto generates the primary unique id.
Line 21: annotates with @OneToMany that sets up a 1-to-many relationship between Customer and Order.

3.2 Order Entity

In this step, I will create an Order class which annotates with the @Entity annotation. The Order class has the following members:

amount– the order amount.
id – the primary key of the order.
name – the order name.
customer – the customer that the order belongs to.
quantity – the order quantity.

Order.java

package org.zheng.demo.entity;
 
import java.math.BigDecimal;
 
import jakarta.persistence.Entity;
import jakarta.persistence.GeneratedValue;
import jakarta.persistence.GenerationType;
import jakarta.persistence.Id;
import jakarta.persistence.JoinColumn;
import jakarta.persistence.ManyToOne;
 
@Entity(name = "T_Order")
public class Order {
    private BigDecimal amount;
 
    @ManyToOne
    @JoinColumn(name = "cust_id")
    private Customer customer;
 
    @Id
    @GeneratedValue(strategy = GenerationType.AUTO)
    private Long id;
    private String name;
    private int quantity;
 
    public Order() {
        super();
    }
 
    public Order(String name, int quantity, BigDecimal amount, Customer customer) {
        super();
        this.name = name;
        this.quantity = quantity;
        this.customer = customer;
        this.amount = amount;
    }
 
    public BigDecimal getAmount() {
        return amount;
    }
 
    public Customer getCustomer() {
        return customer;
    }
 
    public Long getId() {
        return id;
    }
 
    public String getName() {
        return name;
    }
 
    public int getQuantity() {
        return quantity;
    }
 
    public void setAmount(BigDecimal amount) {
        this.amount = amount;
    }
 
    public void setCustomer(Customer customer) {
        this.customer = customer;
    }
 
    public void setId(Long id) {
        this.id = id;
    }
 
    public void setName(String name) {
        this.name = name;
    }
 
    public void setQuantity(int quantity) {
        this.quantity = quantity;
    }
 
    @Override
    public String toString() {
        return "Order [customer=" + customer + ", id=" + id + ", name=" + name + ", quantity=" + quantity + ", amount="
                + amount + "]";
    }
 
}

Line 12: annotates with @Entity and map to H2 table T_Order.
Line 16, 17: annotates with @ManyToOne and @JoinColumn to define the foreign key column: cust_id.
Line 20, 21: annotates with @Id, @GeneratedValue for the primary key.

3.3 Customer Repository

In this step, I will create a CustomerRepo interface which extends from JpaRepository.

CustomerRepo.java

package org.zheng.demo.repo;
 
import org.springframework.data.jpa.repository.JpaRepository;
import org.zheng.demo.entity.Customer;
 
public interface CustomerRepo extends JpaRepository<Customer, Long> {
 
}

Line 6: extends from JpaRepository.

3.4 Order Repository

In this step, I will create an OrderRepo interfaceclass which extends from JpaRepository.

OrderRepo.java

package org.zheng.demo.repo;
 
import org.springframework.data.jpa.repository.JpaRepository;
import org.zheng.demo.entity.Order;
 
public interface OrderRepo extends JpaRepository<Order, Long> {
 
}

Line 6: extends from JpaRepository.

4. Immutable Entities

In this example, I will create two immutable entities and map to two subqueries.

4.1 CustomerOrder

In this step, I will create an immutable entity CustomerOrder class with the @Entity, @Immutable, and @Subselect annotations. Here is the sub query used with the @Subselect.

Customer Order SubQuery

SELECT t_customer.id   id,
       t_customer.NAME NAME,
       a.total         total
FROM   t_customer,
       (SELECT c.id,
               Sum(o.amount) total
        FROM   t_customer c,
               t_order o
        WHERE  c.id = o.cust_id
        GROUP  BY c.id) a
WHERE  a.id = t_customer.id

This subquery returns the customer along with its order’s total amount.

CustomerOrder.java

package org.zheng.demo.subselect;
 
import java.math.BigDecimal;
 
import org.hibernate.annotations.Immutable;
import org.hibernate.annotations.Subselect;
 
import jakarta.persistence.Column;
import jakarta.persistence.Entity;
import jakarta.persistence.Id;
 
@Entity
@Immutable
@Subselect("select t_customer.id id, t_customer.name name, a.total total " + "from t_customer ,"
        + "( select c.id, sum(o.amount) total " + "from t_customer c, t_order o " + "where  c.id = o.cust_id "
        + "group by c.id " + ") a where a.id = t_customer.id")
public class CustomerOrder {
 
    @Id
    @Column(name = "id")
    private Long id;
 
    @Column(name = "name")
    private String customerName;
 
    @Column(name = "total")
    private BigDecimal totalOrderAmount;
 
    public Long getId() {
        return id;
    }
 
    public void setId(Long id) {
        this.id = id;
    }
 
    public String getCustomerName() {
        return customerName;
    }
 
    public void setCustomerName(String customerName) {
        this.customerName = customerName;
    }
 
    public BigDecimal getTotalOrderAmount() {
        return totalOrderAmount;
    }
 
    public void setTotalOrderAmount(BigDecimal totalOrderAmount) {
        this.totalOrderAmount = totalOrderAmount;
    }
 
}

Line 12,13,14: creates an immutable entity with @Entity, @Immutable, and @Subselect. Note, the subselect query is the SQL query.
Line 20, 23, 26: maps the immutable entity to the subselect query’s columns.

4.2 MajorCustomer

In this step, I will create an immutable entity MajorCustomer class which represents a customer whose type equals “Major“.

Major Customer SubQuery

SELECT id,
       NAME,
       email_address AS email
FROM   t_customer
WHERE  type = 'Major'

This subquery returns the customer whose type = ‘Major‘.

MajorCustomer.java

package org.zheng.demo.subselect;
 
import org.hibernate.annotations.Subselect;
import org.springframework.data.annotation.Immutable;
 
import jakarta.persistence.Column;
import jakarta.persistence.Entity;
import jakarta.persistence.Id;
 
@Entity
@Immutable
@Subselect("SELECT id, name, email_Address as email FROM t_customer  where type= 'Major' ")
public class MajorCustomer {
 
    @Column(name = "email")
    private String email;
 
    @Id
    @Column(name = "id")
    private Long id;
 
    @Column(name = "name")
    private String name;
 
    public String getEmail() {
        return email;
    }
 
    public Long getId() {
        return id;
    }
 
    public String getName() {
        return name;
    }
 
    public void setEmail(String email) {
        this.email = email;
    }
 
    public void setId(Long id) {
        this.id = id;
    }
 
    public void setName(String name) {
        this.name = name;
    }
 
}

Line 10, 11, 12: creates an immutable MajorCustomer entity whose type equals “Major“.
Line 19, 22: maps the immutable entity to the subselect query’s columns.

4.3 CustomerOrderService

In this step, I will create a CustomerOrderService class which returns a list of CustomerOrder that contains customer data and its orders’ total amount.

CustomerOrderService.java

package org.zheng.demo.service;
 
import java.util.List;
 
import org.springframework.stereotype.Service;
import org.zheng.demo.subselect.CustomerOrder;
 
import jakarta.persistence.EntityManager;
import jakarta.persistence.TypedQuery;
 
@Service
public class CustomerOrderService {
 
    private EntityManager em;
 
    public CustomerOrderService(EntityManager em) {
        super();
        this.em = em;
    }
 
    public List<CustomerOrder> getCustomerOrders() {
        TypedQuery<CustomerOrder> ret = em.createQuery("select o from CustomerOrder o", CustomerOrder.class);
        return ret.getResultList();
    }
 
}

Line 16: inject EntityManager.
Line 22: create a JPQL query to access the immutable CustomerOrder entity and return the results.

4.4 MajorCustomerService

In this step, I will create a MajorCustomerService class which returns a list of major customers.

MajorCustomerService.java

package org.zheng.demo.service;
 
import java.util.List;
 
import org.springframework.stereotype.Service;
import org.zheng.demo.subselect.MajorCustomer;
 
import jakarta.persistence.EntityManager;
import jakarta.persistence.TypedQuery;
 
@Service
public class MajorCustomerService {
 
    private EntityManager entityManager;
 
    public MajorCustomerService(EntityManager entityManager) {
        this.entityManager = entityManager;
    }
 
    public List<MajorCustomer> getMajorCustomers() {
        TypedQuery<MajorCustomer> query = entityManager.createQuery("SELECT o FROM MajorCustomer o",
                MajorCustomer.class);
        return query.getResultList();
    }
}

Line 21: returns a list of MajorCustomer from the immutable entity.

5. Web Service

In this step, I will create two Restful endpoints which utilize the data and services created at step 3 and 4 to return a list of major customer and customer’s order total amounts.

5.1 ReportRest

In this step, I will create a ReportRest class which has two get endpoints.

ReportRest.java

package org.zheng.demo.rest;
 
import java.util.List;
 
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RestController;
import org.zheng.demo.service.MajorCustomerService;
import org.zheng.demo.service.CustomerOrderService;
import org.zheng.demo.subselect.MajorCustomer;
import org.zheng.demo.subselect.CustomerOrder;
 
@RestController
public class ReportRest {
 
    @Autowired
    private MajorCustomerService custService;
 
    @Autowired
    private CustomerOrderService opService;
 
    @GetMapping("/majorCustomers")
    public List getMajorCustomers() {
        return custService.getMajorCustomers();
    }
 
    @GetMapping("/customerOrders")
    public List getOrderReport() {
        return opService.getCustomerOrders();
    }
 
}

Line 22: defines a GetMapping to “/majorCustomers” to return a list of major customers.
Line 27: defines a GetMapping to “/customerOrders” to return a list of customers with its total order amount.

5.2 SebselectDemoApplication

In this step, I will update the generated SebselectDemoApplication class to insert five customers and 10 orders and calculate the customer’s order total amount and set the customer as a major customer if its total order amount exceeds 5000.

SebselectDemoApplication.java

package org.zheng.demo;
 
import java.math.BigDecimal;
import java.util.Arrays;
import java.util.Optional;
import java.util.Random;
 
import org.springframework.boot.CommandLineRunner;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
import org.springframework.context.annotation.Bean;
import org.zheng.demo.entity.Customer;
import org.zheng.demo.entity.Order;
import org.zheng.demo.repo.CustomerRepo;
import org.zheng.demo.repo.OrderRepo;
import org.zheng.demo.service.MajorCustomerService;
import org.zheng.demo.service.CustomerOrderService;
 
@SpringBootApplication
public class SebselectDemoApplication {
 
    public static void main(String[] args) {
        SpringApplication.run(SebselectDemoApplication.class, args);
 
    }
 
    @Bean
    public CommandLineRunner demo(CustomerRepo custRep, OrderRepo orderRep, CustomerOrderService orService,
            MajorCustomerService ms) {
        return (args) -> {
            // save a few customers
            String[] custs = { "mary", "zheng", "john", "bob", "terry" };
            String[] orders = { "PC", "Phone", "Laptop", "TV", "XBox" };
            BigDecimal[] amount = { new BigDecimal(1000), new BigDecimal(500), new BigDecimal(800),
                    new BigDecimal(3000), new BigDecimal(600) };
 
            Arrays.asList(custs).stream().forEach(cust -> {
                Customer customer = new Customer(cust, cust + "@test.com");
                custRep.save(customer);
 
                Random random = new Random();
                int randomNumber = random.nextInt(orders.length);
                Order order = new Order(orders[randomNumber], randomNumber, randomNumber == 0 ? amount[randomNumber]
                        : amount[randomNumber].multiply(new BigDecimal(randomNumber)), customer);
                orderRep.save(order);
 
                randomNumber = random.nextInt(orders.length);
                order = new Order(orders[randomNumber], randomNumber, randomNumber == 0 ? amount[randomNumber]
                        : amount[randomNumber].multiply(new BigDecimal(randomNumber)), customer);
                orderRep.save(order);
 
            });
 
            orderRep.findAll().forEach(order -> {
                System.out.println(order.toString());
            });
 
            orService.getCustomerOrders().stream().filter(orRe -> orRe.getTotalOrderAmount().intValue() > 5000)
                    .forEach(major -> {
 
                        Optional<Customer> majorCust = custRep.findById(major.getId());
 
                        if (majorCust.isPresent()) {
                            Customer major2 = majorCust.get();
                            major2.setType("Major");
                            custRep.save(major2);
                        }
                    });
 
            System.out.println("Major customers with total exceed 5000.");
            ms.getMajorCustomers().stream().forEach(m -> System.out.println(m.getName()));
        };
    }
 
}

Line 28: injects the CustomerRepo, OrderRepo, CustomerOrderService, and MajorCustomerService created at earlier steps.
Line 32, 33, 34: initializes five customers’ name, product and and its sale amount.
Line 39: saves the test customer.
Line 45,50: saves the test orders for each customer.
Line 55: prints out the test orders details, so we can use it to verify the data at step 6.
Line 58: finds the major customer based on its total order amount exceeding 5000.
Line 66: updates the customer’s type to “Major“.

5.3 SB Application Properties

In this step, I will configure H2 datasource in application.properties.

applicatio.properties

spring.application.name=sebselect-demo
 
spring.jpa.show-sql=true
 
spring.datasource.url=jdbc:h2:mem:testdb
spring.datasource.driverClassName=org.h2.Driver
spring.datasource.username=sa
spring.datasource.password=
spring.h2.console.enabled=true
spring.jpa.hibernate.ddl-auto=update
spring.datasource.initialization-mode=always

6. Demo

Start the spring boot application, verify the application is started and capture the server log.

Server Log

  .   ____          _            __ _ _
 /\\ / ___'_ __ _ _(_)_ __  __ _ \ \ \ \
( ( )\___ | '_ | '_| | '_ \/ _` | \ \ \ \
 \\/  ___)| |_)| | | | | || (_| |  ) ) ) )
  '  |____| .__|_| |_|_| |_\__, | / / / /
 =========|_|==============|___/=/_/_/_/
 
 :: Spring Boot ::                (v3.3.0)
 
2024-05-25T14:05:14.845-05:00  INFO 14956 --- [sebselect-demo] [           main] o.zheng.demo.SebselectDemoApplication    : Starting SebselectDemoApplication using Java 17.0.11 with PID 14956 (C:\MaryTools\workspace\sebselect-demo\target\classes started by azpm0 in C:\MaryTools\workspace\sebselect-demo)
2024-05-25T14:05:14.851-05:00  INFO 14956 --- [sebselect-demo] [           main] o.zheng.demo.SebselectDemoApplication    : No active profile set, falling back to 1 default profile: "default"
2024-05-25T14:05:15.589-05:00  INFO 14956 --- [sebselect-demo] [           main] .s.d.r.c.RepositoryConfigurationDelegate : Bootstrapping Spring Data JPA repositories in DEFAULT mode.
2024-05-25T14:05:15.650-05:00  INFO 14956 --- [sebselect-demo] [           main] .s.d.r.c.RepositoryConfigurationDelegate : Finished Spring Data repository scanning in 51 ms. Found 2 JPA repository interfaces.
2024-05-25T14:05:16.244-05:00  INFO 14956 --- [sebselect-demo] [           main] o.s.b.w.embedded.tomcat.TomcatWebServer  : Tomcat initialized with port 8080 (http)
2024-05-25T14:05:16.254-05:00  INFO 14956 --- [sebselect-demo] [           main] o.apache.catalina.core.StandardService   : Starting service [Tomcat]
2024-05-25T14:05:16.254-05:00  INFO 14956 --- [sebselect-demo] [           main] o.apache.catalina.core.StandardEngine    : Starting Servlet engine: [Apache Tomcat/10.1.24]
2024-05-25T14:05:16.322-05:00  INFO 14956 --- [sebselect-demo] [           main] o.a.c.c.C.[Tomcat].[localhost].[/]       : Initializing Spring embedded WebApplicationContext
2024-05-25T14:05:16.324-05:00  INFO 14956 --- [sebselect-demo] [           main] w.s.c.ServletWebServerApplicationContext : Root WebApplicationContext: initialization completed in 1418 ms
2024-05-25T14:05:16.369-05:00  INFO 14956 --- [sebselect-demo] [           main] com.zaxxer.hikari.HikariDataSource       : HikariPool-1 - Starting...
2024-05-25T14:05:16.553-05:00  INFO 14956 --- [sebselect-demo] [           main] com.zaxxer.hikari.pool.HikariPool        : HikariPool-1 - Added connection conn0: url=jdbc:h2:mem:testdb user=SA
2024-05-25T14:05:16.555-05:00  INFO 14956 --- [sebselect-demo] [           main] com.zaxxer.hikari.HikariDataSource       : HikariPool-1 - Start completed.
2024-05-25T14:05:16.569-05:00  INFO 14956 --- [sebselect-demo] [           main] o.s.b.a.h2.H2ConsoleAutoConfiguration    : H2 console available at '/h2-console'. Database available at 'jdbc:h2:mem:testdb'
2024-05-25T14:05:16.713-05:00  INFO 14956 --- [sebselect-demo] [           main] o.hibernate.jpa.internal.util.LogHelper  : HHH000204: Processing PersistenceUnitInfo [name: default]
2024-05-25T14:05:16.771-05:00  INFO 14956 --- [sebselect-demo] [           main] org.hibernate.Version                    : HHH000412: Hibernate ORM core version 6.5.2.Final
2024-05-25T14:05:16.809-05:00  INFO 14956 --- [sebselect-demo] [           main] o.h.c.internal.RegionFactoryInitiator    : HHH000026: Second-level cache disabled
2024-05-25T14:05:17.105-05:00  INFO 14956 --- [sebselect-demo] [           main] o.s.o.j.p.SpringPersistenceUnitInfo      : No LoadTimeWeaver setup: ignoring JPA class transformer
2024-05-25T14:05:17.994-05:00  INFO 14956 --- [sebselect-demo] [           main] o.h.e.t.j.p.i.JtaPlatformInitiator       : HHH000489: No JTA platform available (set 'hibernate.transaction.jta.platform' to enable JTA platform integration)
Hibernate: create table t_customer (id bigint not null, email_address varchar(255), name varchar(255), type varchar(255), primary key (id))
Hibernate: create table t_order (id bigint not null, amount numeric(38,2), name varchar(255), quantity integer not null, cust_id bigint, primary key (id))
Hibernate: create sequence t_customer_seq start with 1 increment by 50
Hibernate: create sequence t_order_seq start with 1 increment by 50
Hibernate: alter table if exists t_order add constraint FKgt3n29ngim2bryiw3eimwjs52 foreign key (cust_id) references t_customer
2024-05-25T14:05:18.035-05:00  INFO 14956 --- [sebselect-demo] [           main] j.LocalContainerEntityManagerFactoryBean : Initialized JPA EntityManagerFactory for persistence unit 'default'
2024-05-25T14:05:18.309-05:00  WARN 14956 --- [sebselect-demo] [           main] JpaBaseConfiguration$JpaWebConfiguration : spring.jpa.open-in-view is enabled by default. Therefore, database queries may be performed during view rendering. Explicitly configure spring.jpa.open-in-view to disable this warning
2024-05-25T14:05:18.679-05:00  INFO 14956 --- [sebselect-demo] [           main] o.s.b.w.embedded.tomcat.TomcatWebServer  : Tomcat started on port 8080 (http) with context path '/'
2024-05-25T14:05:18.690-05:00  INFO 14956 --- [sebselect-demo] [           main] o.zheng.demo.SebselectDemoApplication    : Started SebselectDemoApplication in 4.22 seconds (process running for 4.669)
Hibernate: select next value for t_customer_seq
Hibernate: insert into t_customer (email_address,name,type,id) values (?,?,?,?)
Hibernate: select next value for t_order_seq
Hibernate: insert into t_order (amount,cust_id,name,quantity,id) values (?,?,?,?,?)
Hibernate: select next value for t_order_seq
Hibernate: insert into t_order (amount,cust_id,name,quantity,id) values (?,?,?,?,?)
Hibernate: select next value for t_customer_seq
Hibernate: insert into t_customer (email_address,name,type,id) values (?,?,?,?)
Hibernate: insert into t_order (amount,cust_id,name,quantity,id) values (?,?,?,?,?)
Hibernate: insert into t_order (amount,cust_id,name,quantity,id) values (?,?,?,?,?)
Hibernate: insert into t_customer (email_address,name,type,id) values (?,?,?,?)
Hibernate: insert into t_order (amount,cust_id,name,quantity,id) values (?,?,?,?,?)
Hibernate: insert into t_order (amount,cust_id,name,quantity,id) values (?,?,?,?,?)
Hibernate: insert into t_customer (email_address,name,type,id) values (?,?,?,?)
Hibernate: insert into t_order (amount,cust_id,name,quantity,id) values (?,?,?,?,?)
Hibernate: insert into t_order (amount,cust_id,name,quantity,id) values (?,?,?,?,?)
Hibernate: insert into t_customer (email_address,name,type,id) values (?,?,?,?)
Hibernate: insert into t_order (amount,cust_id,name,quantity,id) values (?,?,?,?,?)
Hibernate: insert into t_order (amount,cust_id,name,quantity,id) values (?,?,?,?,?)
Hibernate: select o1_0.id,o1_0.amount,o1_0.cust_id,o1_0.name,o1_0.quantity from t_order o1_0
Hibernate: select c1_0.id,c1_0.email_address,c1_0.name,c1_0.type from t_customer c1_0 where c1_0.id=?
Hibernate: select c1_0.id,c1_0.email_address,c1_0.name,c1_0.type from t_customer c1_0 where c1_0.id=?
Hibernate: select c1_0.id,c1_0.email_address,c1_0.name,c1_0.type from t_customer c1_0 where c1_0.id=?
Hibernate: select c1_0.id,c1_0.email_address,c1_0.name,c1_0.type from t_customer c1_0 where c1_0.id=?
Hibernate: select c1_0.id,c1_0.email_address,c1_0.name,c1_0.type from t_customer c1_0 where c1_0.id=?
Order [customer=Customer [emailAddress=mary@test.com, id=1, name=mary, type=null], id=1, name=XBox, quantity=4, amount=2400.00]
Order [customer=Customer [emailAddress=mary@test.com, id=1, name=mary, type=null], id=2, name=PC, quantity=0, amount=1000.00]
Order [customer=Customer [emailAddress=zheng@test.com, id=2, name=zheng, type=null], id=3, name=TV, quantity=3, amount=9000.00]
Order [customer=Customer [emailAddress=zheng@test.com, id=2, name=zheng, type=null], id=4, name=PC, quantity=0, amount=1000.00]
Order [customer=Customer [emailAddress=john@test.com, id=3, name=john, type=null], id=5, name=PC, quantity=0, amount=1000.00]
Order [customer=Customer [emailAddress=john@test.com, id=3, name=john, type=null], id=6, name=Phone, quantity=1, amount=500.00]
Order [customer=Customer [emailAddress=bob@test.com, id=4, name=bob, type=null], id=7, name=PC, quantity=0, amount=1000.00]
Order [customer=Customer [emailAddress=bob@test.com, id=4, name=bob, type=null], id=8, name=PC, quantity=0, amount=1000.00]
Order [customer=Customer [emailAddress=terry@test.com, id=5, name=terry, type=null], id=9, name=XBox, quantity=4, amount=2400.00]
Order [customer=Customer [emailAddress=terry@test.com, id=5, name=terry, type=null], id=10, name=PC, quantity=0, amount=1000.00]
Hibernate: select co1_0.id,co1_0.name,co1_0.total from ( select t_customer.id id, t_customer.name name, a.total total from t_customer ,( select c.id, sum(o.amount) total from t_customer c, t_order o where  c.id = o.cust_id group by c.id ) a where a.id = t_customer.id ) co1_0
Hibernate: select c1_0.id,c1_0.email_address,c1_0.name,c1_0.type from t_customer c1_0 where c1_0.id=?
Hibernate: select c1_0.id,c1_0.email_address,c1_0.name,c1_0.type from t_customer c1_0 where c1_0.id=?
Hibernate: update t_customer set email_address=?,name=?,type=? where id=?
Major customers with total exceed 5000.
Hibernate: select mc1_0.id,mc1_0.email,mc1_0.name from ( SELECT id, name, email_Address as email FROM t_customer  where type= 'Major'  ) mc1_0
zheng

Line 28-32: H2 table T_Customer, T_Order are created.
Line 62-71: Customer order details.
Line 76, 78: print out the major customers.

Open a web browser and navigate to the http://localhost:8080/customerOrders. You should see the following response:

Customer Orders

[
  {
    "id": 1,
    "customerName": "mary",
    "totalOrderAmount": 3400
  },
  {
    "id": 2,
    "customerName": "zheng",
    "totalOrderAmount": 10000
  },
  {
    "id": 3,
    "customerName": "john",
    "totalOrderAmount": 1500
  },
  {
    "id": 4,
    "customerName": "bob",
    "totalOrderAmount": 2000
  },
  {
    "id": 5,
    "customerName": "terry",
    "totalOrderAmount": 3400
  }
]

As you see, the totalOrderAmount for customer zheng exceeds 5000. Navigate to the http://localhost:8080/majorCustomers. You should see the following response:

Major Customers

[
  {
    "email": "zheng@test.com",
    "id": 2,
    "name": "zheng"
  }
]

7. Conclusion

@Subselect is Hibernate featurue and not part of the JPA specification, so it may not be supported by JPA providers. Red Hat(Jboss) and Spring framework support @Subselect, but EclipseLink, OpenJPA, and DataNucleus don’t support @Subselect. It offers significant benefits for creating read-only views of data but comes with following constraints:

Lack of caching: the results are not cached due to complex queries. This can impact performance if the subquery is expensive.
Database dependence: The subquery is written in SQL, making it database-dependent.
Initialization order: The underlying tables referenced in the subquery must be created before the @Subselect entity is accessed. This can lead to initialization issues in some cases.
Data consistency: since @Subselect entities are read-only, any changes in the underlying tables are not immediately reflected in these entities unless the session is refreshed or the query is re-executed.
Maintenance: maintaining complex subqueries within the entity definition can become challenging, especially as the database schema evolves.
Performance trade-offs: while @Subselect can optimize read-heavy operations, it can also introduce performance bottlenecks if the subquery is not well-optimized.

8. Download

This was an example of Spring Boot web application which maps an immutable entity with @Subselect annotation.

Download
You can download the full source code of this example here: Hibernate @Subselect Annotation

Hibernate Subselect Annotation

1. Introduction

2. Set up Maven Spring Boot Web Application

3. Customer and Order Entities

3.1 Customer Entity

3.2 Order Entity

3.3 Customer Repository

3.4 Order Repository

4. Immutable Entities

4.1 CustomerOrder

4.2 MajorCustomer

4.3 CustomerOrderService

4.4 MajorCustomerService

5. Web Service

5.1 ReportRest

5.2 SebselectDemoApplication

5.3 SB Application Properties

6. Demo

7. Conclusion

8. Download

Thank you!

Mary Zheng

Thank you!

1. Introduction

2. Set up Maven Spring Boot Web Application

3. Customer and Order Entities

3.1 Customer Entity

3.2 Order Entity

3.3 Customer Repository

3.4 Order Repository

4. Immutable Entities

4.1 CustomerOrder

4.2 MajorCustomer

4.3 CustomerOrderService

4.4 MajorCustomerService

5. Web Service

5.1 ReportRest

5.2 SebselectDemoApplication

5.3 SB Application Properties

6. Demo

7. Conclusion

8. Download

Thank you!

Related Articles

Thank you!