Course Module 4. Working with databases - Lecture: Table per class

5.1 Description

And finally, the last strategy is Table per class. It means that a separate table will be used for each class. In a sense, this is the same MappedSuperClass, only in an updated form.

First, you need to use an annotation:

@Inheritance(strategy = InheritanceType.TABLE_PER_CLASS)

Classes with this annotation will look like this:


@Inheritance(strategy = InheritanceType.TABLE_PER_CLASS)
@Entity
class User {
	int id;
	String name;
	LocalDate birthday;
}

@Entity
class Employee extends User {
 	String occupation;
 	int salary;
 	LocalDate join;
}

@Entity
class Client extends User {
	String address;
}

And separate tables for each class. For example, these:


CREATE TABLE user {
	id INT,
	name VARCHAR,
	birthday DATE
}

CREATE TABLE employee {
	id INT,
	name VARCHAR,
	birthday DATE,
	occupation VARCHAR,
	salary INT,
	join DATE
}

CREATE TABLE client {
	id INT,
	name VARCHAR,
	birthday DATE,
	address VARCHAR
}

The main difference is that a pass-through id (PRIMARY KEY) is used for all tables. You cannot have different rows with the same id, not only within the same table, but also within this group of tables. Hibernate will take care of this.

5.2 Examples

It's very interesting to see how it all works.

You can write a simple HQL query to get all users: User, Employee, Client :

List<User> accounts = session.createQuery("from User").list();

But Hibernate, in turn, will generate a very interesting query. It will make a selection from all tables, then combine it through UNION ALL into a kind of virtual table, and only then will it search and / or select

But in order to merge tables with different columns, they first need to be supplemented with fake columns. For example, the user table needs to be supplemented with columns:

occupation VARCHAR
salary INT
join DATE
address VARCHAR

An example of a SQL query to the user table before execution UNION ALL:


SELECT   id,
         name,
         birthday,
         CAST(NULL AS VARCHAR) AS occupation,
         CAST(NULL AS INT) AS salary,
         CAST(NULL AS DATE) AS join,
         CAST(NULL AS VARCHAR) AS address,
         0 AS clazz
FROM  user

An example SQL query to the employee table before execution UNION ALL:


SELECT   id,
         name,
         birthday,
         occupation,
         salary,
         join,
         CAST(NULL AS VARCHAR) AS address,
         1 AS clazz
FROM  employee

An example of a SQL query to the client table before execution UNION ALL:


SELECT  id,
        name,
        birthday,
        CAST(NULL AS VARCHAR) AS occupation,
        CAST(NULL AS INT) AS salary,
        CAST(NULL AS DATE) AS join,
        address,
        2 AS clazz
FROM client

The good news is that HQL queries will work the way you want them to.

The bad news is that they can be slow if there is a lot of data in the tables. Because first you need to select data from all tables, then combine their rows with UNION ALL, and only then filter.