Views

A view in an RDBMS is avirtual table that is defined by a query. It can combine data from two or more tables, or just contain a subset of information from a single table. A view does not store data, but rather displays the data that is stored in the base tables. You can query a view like you can a table, but you cannot insert, update, or delete data from a view unless certain conditions (not discussed here) are met!

i.e store complex queries under a name (it’s almost like a variable for a query)!

Let’s assume we have the following tables: (We are usingPostgres in this example)

The Department table

The Employee table

The Employee-Department table

Now, individually these tables do not mean much. I’ll have to run the following query to get some meaningful data:

select e.id, e.name, e.email, d.name, ed.start_time,  ed.end_timefrom employee e join employee_department ed on e.id = ed.employee_idjoin department d on d.id = ed.department_id

Let’s say you want to group by department name and count the number of employees:

select d.name, count(e.id) from employee e join employee_department ed on e.id = ed.employee_idjoin department d on d.id = ed.department_idgroup by d.name;

This join query “from employee e join employee_department ed on e.id = ed.employee_id join department d on d.id = ed.department_id” is duplicated every time you want to work with employees and departments!

I can instead, create a view off of this query and give it a name and treat it as if it were a table!

-- create view <view-name> AS <your query>create view employee_with_department_details ASselect e.id as employee_id, e.name as employee_name, e.email, d.name as department_name, ed.start_time, ed.end_timefrom employee e join employee_department ed on e.id = ed.employee_idjoin department d on d.id = ed.department_id;

Now, I can directly query this view as if it were a table

select * from employee_with_department_details;select department_name, count(employee_id)from employee_with_department_detailsgroup by department_name;

Important — Whenever you execute a view, the query behind the view is executed every-time!

Query views directly in a Spring Data Jpa application using Interface Projections

interface EmployeeWithDepartmentDetails {    @Value("#{target.employee_id}")    Long getEmployeeId();    @Value("#{target.employee_name}")    String getEmployeeName();    String getEmail();    @Value("#{target.department_name}")    String getDepartmentName();    @Value("#{target.start_time}")    Long getStartTime();    @Value("#{target.end_time}")    Long getEndTime();}@Repositorypublic interface DepartmentJpaRepository extends JpaRepository<Department, Long> {    @Query(            value = "select * from employee_with_department_details;",            nativeQuery = true    )    List<EmployeeWithDepartmentDetails> getEmployeeWithDepartmentDetails();}

Query the view:

Materialized Views

The main difference between a view and a materialized view in an RDBMS is that a view does not store any data, while a materialized view does. A view is a virtual table that is defined by a query, and it displays the data that is stored in the base tables. A materialized view is a view that stores the result set of the query in a physical table, and it can be queried like a regular table. However, a materialized view needs to be refreshed periodically to reflect the changes in the base tables, while a view is always up to date.

Therefore, a materialised view does 2 things:

1. Store the query that is used to create this materialised view (also done by a view)
2. Store the data returned by this query!

Unlike a normal view, a materialised view does not execute the query every time you fetch it, but rather returns the data stored by the view thusimproving performance. That is why the data in a materialised view can become stale and require a refresh.

-- create materialized view <view-name> AS <your query>create materialized view employee_with_department_details  ASselect e.id as employee_id, e.name as employee_name, e.email, d.name as department_name, ed.start_time, ed.end_timefrom employee e join employee_department ed on e.id = ed.employee_idjoin department d on d.id = ed.department_id;select * from employee_with_department_details;

To refresh a materialised view:

refresh materialized view employee_with_department_details;

Query and update materialized views in a Spring Data Jpa application

Example use case: We want to refresh the materialized view whenever a new employee is added to a department so that the data returned by the materialized view is always current

@Transactionalpublic AddEmployeeToDepartment addEmployeeToDepartment(Long employeeId, Long departmentId) {    final Department department = departmentJpaRepository.findById(departmentId).orElseThrow();    final Employee employee = employeeJpaRepository.findById(employeeId).orElseThrow();    final EmployeeDepartment employeeDepartment = new EmployeeDepartment(employeeId, departmentId);    employeeDepartmentJpaRepository.save(        employeeDepartment    );    //Refresh the materialised view since it will contain stale data otherwise!    employeeDepartmentJpaRepository        .refreshEmployeeWithDepartmentDetails();    return new AddEmployeeToDepartment(true, null);}

@Repositorypublic interface EmployeeDepartmentJpaRepository extends JpaRepository<EmployeeDepartment, Long> {    @Query(            value = "select * from employee_with_department_details;",            nativeQuery = true    )    List<EmployeeWithDepartmentDetails> getEmployeeWithDepartmentDetails();    /*    The refresh materialized view statement does not return any result set,    but Spring Data JPA expects one. To fix this, you need to annotate your method with @Modifying,    which tells Spring Data JPA that the query is a DML statement and does not return any result.    You can also use the clearAutomatically attribute to clear the persistence context after the query execution,    which might be useful if you want to query the refreshed view afterwards.    */    @Modifying(clearAutomatically = true)    @Transactional    //use @Async instead!    @Query(            value = "refresh materialized view employee_with_department_details;",            nativeQuery = true    )    void refreshEmployeeWithDepartmentDetails();}

Query the materialised view:

Let’s try to add a new employee (Mr Robot) and tag them to the IT department and see if the materialized view is refreshed or not:

Ideally, you should not use materialized views for data that is updated very frequently. The data is basically static until you refresh it, at which time it’s flushed and the data is replaced by the result of the query at the new run time. They’re particulary good when the performance to run the query is poor but the data doesn’t have to be exact or up to the last second. For example, if you wanted to run a query that generates a report for the previous day, you could create a materialized view to get the data from yesterday and run it on a schedule after midnight. Then the user can query the materialized view with a select * in the morning and get quick results without waiting on the query to execute against the base data. Sometimes, it makes sense to have the materialized view to contain most of the result set and then some optimized query to just pull data from the current day, hour, etc. and union the results together.