PV168

Databases

What is a Database

A database is an organized collection of structured information, or data, typically stored electronically in a computer system.

Multiple database types:

• Relational Databases
  • Oracle DB, PostgreSQL, MySQL, MS SQL, H2, SQLite
• NoSQL Databases (non-relational)
  • MongoDB, ElasticSearch, Redis, DynamoDB
• Other

JDBC – Java Database Connectivity

• API for communication with RDBMS
• Each DB has its own driver
  • Enables Java application to interact with the DB
• Introduced in Java 1.1
  • Stable yet older API
  • With its own quirks

JDBC URI

Examples:

• MySQL: jdbc:mysql://server:port/dbname?characterEncoding=UTF-8
• Postgres: jdbc:postgresql://host:port/database

SQLite Database JDBC URI for specific modes:

• SQLite (in-memory): jdbc:sqlite::memory:?foreign_keys=true
• SQLite (embedded): jdbc:sqlite:~/file/path/database.db?PROP=value

SQL - Structured Query Language

Types:

• Data Query Language (DQL) - SELECT
• Data Manipulation Language (DML) - INSERT, UPDATE, DELETE
• Data Definition Language (DDL) - CREATE, ALTER, DROP
• Data Control Language (DCL) - GRANT, DENY (access)
• WARNING: Most of the DDL and DCL are implementation-specific
  • Depend on the RDBMS you are using

Create a new Department Table

-- create a new Department table
CREATE TABLE IF NOT EXISTS "Department"
(
    "id"        INTEGER PRIMARY KEY AUTOINCREMENT,
    "number"    TEXT NOT NULL UNIQUE,
    "name"      TEXT NOT NULL,
    "createdAt" TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP
);

Create a new Employee Table

-- create a new Emplotee table
CREATE TABLE IF NOT EXISTS "Employee"
(
    "id"           INTEGER PRIMARY KEY AUTOINCREMENT,
    "number"       TEXT NOT NULL UNIQUE,
    "firstName"    TEXT NOT NULL,
    "lastName"     TEXT NOT NULL,
    "birthDate"    TEXT NOT NULL,
    "departmentId" INTEGER,
    "createdAt"    TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY ("departmentId") REFERENCES "Department"("id")
);

Primary Key

• Identification of objects (UNIQUE, NOT NULL, immutable)
• Should be synthetic
  • Identity (since SQL:2003):
    • "id" INTEGER PRIMARY KEY AUTOINCREMENT
  • UUID

Relationships

Are used for collections or compositions:

• One-to-One
• One-to-Many
• Many-to-Many

Foreign Key

FOREIGN KEY is a field (or collection of fields) in one table, that refers to the PRIMARY KEY of another table.

• Examples:
  • "departmentId" INTEGER REFERENCES "Department"("id")
  • FOREIGN KEY ("departmentId") REFERENCES "Department"("id")
• Caveat
  • Foreign Keys are OFF by Default in SQLite
    • PRAGMA foreign_keys = ON; in every connection!

Constraints and Default Indexes

Constraints:

• UNIQUE - column values must be unique
• "number" TEXT NOT NULL UNIQUE

Index is automatically created for:

• Primary Key
• Unique Constraints

Cascades

• What would happen on DELETE a department with employees?
• What might help: ON DELETE CASCADE:
  • "departmentId" INTEGER REFERENCES "Department"("id") ON DELETE CASCADE
• Cascades have problems
  • They might cause an outage 🤣
  • Use with extra care! (never as default option)

Information Schema

• Schema: sqlite_schema
  • Provides information about all the tables, views, columns, indexes, relationships etc. in the DB
  • Very DB-specific

  • -- List all user-defined tables and views
    SELECT type, name, tbl_name, sql
    FROM sqlite_schema
    WHERE type IN ('table', 'view')
    AND name NOT LIKE 'sqlite_%';
    

Migrations

• Way to manage database changes
• Use existing tools for it
  • Java: Liquibase
  • Python: Alembic
  • GO: Migrate
• We will not be covering the migration on this course

JDBC: Architecture

JDBC API

JDBC Connection

• Transaction per statement execution
• Can be disabled by con.setAutoCommit(false)
• Without auto commit con.commit() / con.rollback() is required
• Rollback on error
• Must be closed

JDBC (Prepared) Statement

• Don’t use Statement!!!
• PreparedStatement protects against SQL Injection
• Closed when connection object is closed
• Must be closed explicitly anyway, why?

JDBC ResultSet

• Represents result of database
• Iterator-like object over rows in result relation
• Should be closed (closed with PreparedStatement)

Code Sample

// connection and statement should be both closed after we are done
try(
    Connection connection = dataSource.getConnection();
    var statement = connection.prepareStatement("SELECT id, number FROM Department WHERE id = ?")
) {
    ps.setLong(1, departmentID); // set the
    try (var rs = statement.executeQuery()) { // we should close the result set
        while (rs.next()) {
            long departmentId = rs.getLong("id");
            String number = rs.getString("number");
        }
    }
}