Hibernate JPA @Subselect Example

@Subselect

The @Subselect annotation is used to specify an immutable and read-only entity using a custom SQL SELECT statement.

Subselect Map an immutable and read-only entity to a given SQL select expression.

Mapping the entity to a SQL query
You can map an entity to a SQL query using the @Subselect annotation.

Example : @Subselect entity mapping

@Entity(name = "Client")
@Table(name = "client")
public static class Client {

@Id
private Long id;

@Column(name = "first_name")
private String firstName;

@Column(name = "last_name")
private String lastName;

//Getters and setters omitted for brevity

}

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

@Id
private Long id;

@ManyToOne
private Client client;

private String description;

//Getters and setters omitted for brevity

}

@Entity(name = "AccountTransaction")
@Table(name = "account_transaction")
public static class AccountTransaction {

@Id
@GeneratedValue
private Long id;

@ManyToOne
private Account account;

private Integer cents;

private String description;

//Getters and setters omitted for brevity

}

@Entity(name = "AccountSummary")
@Subselect(
"select " +
" a.id as id, " +
" concat(concat(c.first_name, ' '), c.last_name) as clientName, " +
" sum(atr.cents) as balance " +
"from account a " +
"join client c on c.id = a.client_id " +
"join account_transaction atr on a.id = atr.account_id " +
"group by a.id, concat(concat(c.first_name, ' '), c.last_name)"
)
@Synchronize( {"client", "account", "account_transaction"} )
public static class AccountSummary {

@Id
private Long id;

private String clientName;

private int balance;

//Getters and setters omitted for brevity

}
In the example above, the Account entity does not retain any balance since every account operation is registered as an AccountTransaction. To find the Account balance, we need to query the AccountSummary which shares the same identifier with the Account entity.

However, the AccountSummary is not mapped to a physical table, but to an SQL query.

So, if we have the following AccountTransaction record, the AccountSummary balance will match the proper amount of money in this Account.