Introduction

In this seminar you will be working primarilly with the database. We will use The Employee Records only to initialize the filestystem database (embedded). We will be using the H2 database. The database features can be found here.

The H2 database supports multiple connection modes:

  • in-memory - Example JDBC URI: jdbc:h2:mem:database_name
  • embedded - Example JDBC URI: jdbc:h2:~/file/path/to/database.db
  • server

We will be using the embedded mode, so your changes are persistent. The in-memory mode can be useful for integration testing.

Note: The default schema for the H2 is PUBLIC.

Task 0: Update your repository

For this seminar, you will work with the same forked repository as previously. We assume you already have the upstream remote set from Seminar 04 (if not, please follow task 0a from that seminar).

Task 0.1: Update your main branch

First, you need to fetch new changes from the upstream repository and update your local main branch:

git switch main
git pull upstream main

Then you need to update the main branch in your forked repository:

git push origin main

Task 0.2: Create a new branch for this seminar

Again, each pair creates a new branch on a single computer as they are working together as one person during the seminar.

Pair 1 creates a new branch seminar07-pair1:

git switch -c seminar07-pair1

Pair 2 creates a new branch seminar07-pair2:

git switch -c seminar07-pair2

Task 1: Prepare Your Database

In order to create/destroy a database, there are 2 actions in The Employee Records project. They can be found here: cz.muni.fi.pv168.employees.storage.db.actions, in order to create a database run the CreateDatabase, run the main function inside the class CreateDatabase.

Expected output should look like this:

Database created...
Database connection string: jdbc:h2:/home/user/pv168/db/employee-records;DB_CLOSE_DELAY=-1;DATABASE_TO_UPPER=false

Task 1.1: Connect to your database

  • Open the IntelliJ Idea Database tool integration
  • Click on the + icon (New)
  • Select the Datasource from URL option
  • Paste there the Database connection string that has been provided to you by the CreateDatabase action.
    • Check whether the driver is H2 and continue.
  • On the next dialog called Data Sources and Drivers you might need to download drivers for H2 in order for it to work
    • Drivers installation should be just one click on the prompt at the bottom of the window
    • You might have drivers already installed, in that case you do not need to do anything
  • You should click on Test Connection in order to verify whether you are able to connect
  • After that click OK and the new DB connection should be present
  • The new database connection should be present on the right side of the Idea window
  • The console tab should be opened (if not, click on the Database on the right side of the window, and select New -> Query Console)
    • To this console you will be writing all the SQL queries and statements.
    • In order to execute the statement, click on the green arrow in the top-left corner.
📌Visual Guide

New datasource from URL:

Datasource input box:

Main DS Window:

DS Check connection:

Created database:

Console Tab:

Task 2: Create a first table

DO ALL THE FOLLOWING TASKS IN THE CONSOLE

  • Create a first table in H2 database, Department.
CREATE TABLE Department (
    `id`        BIGINT NOT NULL,
    `number`    VARCHAR(10) NOT NULL,
    `name`      VARCHAR(50) NOT NULL,
    `createdAt` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);
  • Observe the Department table definition and try to understand it.
  • Insert 3 new rows the Department table (hint: INSERT INTO)
    • Observe the console outputs for example: 1 row affected in 4 ms
  • Use SELECT to get all rows in the Department table.
  • Use UPDATE to change the name for one of the departments.

Task 3: Change your table

Our table is missing one of the integral parts - PRIMARY KEY. In order to fix it, you need to

  • drop the table first (you will also lose all the data).
  • create it again, this time with id column set as PRIMARY KEY. We would like it to be auto-generated (sequence from 1 …, Hint: IDENTITY or AUTO_INCREMENT)

  • After you are done, try to insert some new rows and use select to know whether it worked.

  • Now we would like to have the number unique per each department (there cannot be 2 departments with the same number)
    • this time use ALTER TABLE Department ADD CONSTRAINT DepartmentsNumber_UQ UNIQUE (number);
    • if this statement fails - try to understand why (hint - take a look at existing rows and whether the numbers are unique, if not UPDATE/DELETE them)
  • Test whether the number unique constraint is really enforced

Task 4: Create Employee table

  • Create a new table Employee that will be referencing a Department table.
  • Columns:
    • id - auto-generated primary key
    • departmentId - references the Department.id table, defines to which department empoyee belongs to
    • firstName - first name of the employee
    • lastName - last name of the employee
    • birthDate - date when the employee was born
    • createdAt - when the employee instance was created in the database
  • Try to add some employees using INSERT statement.

Task 5: More complex selects

  • Write a SELECT statement that will return all the employees with correspoing department name, the Select output columns would consist with only 3 columns: Employee.firstName, Employee.lastName and Department.name

  • Write a SELECT statement that will return number of employees for each department, the result would consists from 2 columns - Department.name and a named column: employeesCount.

    • For this select, have at least one department without employees.
    • You should see names of all the departments (at least one will have 0 employees)

Task 6: Delete a Department with employees

  • Delete a single department that has employees and observe the behavior.

Bonus Task: Explore the INFORMATION_SCHEMA

  • Get list of all tables for the PUBLIC schema.
  • Get list of all constraints (TABLE_CONSTRAINTS) for the PUBLIC schema.
  • Get list of all indexes for the PUBLIC schema.