Advanced Programming 404

0-Overview

6 min read

🧭 Topic: Database (2 Sessions)

Quick Overview :

This topic provides a comprehensive introduction to relational databases using PostgreSQL, covering SQL fundamentals (DDL, DML, DQL, DCL, TCL), database design principles, and integrating databases with Java applications using JDBC. The workshop focuses on building a complete Restaurant Management System.

📌 Covered in This Topic

Introduction to Databases

  • From File Storage to Databases: Limitations of file-based storage
    • No structured way to query or retrieve specific records (manual parsing)
    • Difficult to maintain data integrity (avoiding duplicates)
    • Poor performance with large data
    • No built-in concurrency support; race conditions can occur
  • What is a Database?: An organized collection of structured information, typically stored electronically
  • DBMS (Database Management System): Software that manages databases
  • Why Use a Database?
    • Structured format for storing data (tables, types)
    • Powerful Structured Query Language (SQL)
    • Tools for ensuring data consistency and integrity
    • Mechanisms to handle multiple users and concurrent operations

PostgreSQL

  • A powerful, open-source Object-Relational Database System (ORDBMS)
  • Focuses on extensibility and standards compliance
  • Uses a client/server model to handle multiple concurrent connections
  • Backed by over 35 years of active development

RDBMS Concepts

  • Tables: Store data with columns (fields) and rows (records)
  • Primary Key: Uniquely identifies each row in a table (no duplicates, cannot be NULL)
  • Foreign Key: Enforces links between data in two tables
  • Relationships:
    • One-to-One (1:1): One row in Table A matches exactly one row in Table B
    • One-to-Many (1:N): One row in Table A matches many rows in Table B (most common)
    • Many-to-Many (N:N): Uses a junction (join) table

SQL Categories

  • DDL (Data Definition Language): Defines or changes structure of tables, schemas
    • CREATE, ALTER, DROP, TRUNCATE, RENAME
  • DML (Data Manipulation Language): Manipulates data stored in tables
    • INSERT, UPDATE, DELETE
  • DQL (Data Query Language): Retrieves data (read-only)
    • SELECT
  • DCL (Data Control Language): Controls permissions and access
    • GRANT, REVOKE
  • TCL (Transaction Control Language): Manages transactions as single units
    • BEGIN, COMMIT, ROLLBACK, SAVEPOINT

SQL Data Types

  • Numeric: SMALLINT, INTEGER, BIGINT, DECIMAL(p,s) / NUMERIC(p,s), REAL, DOUBLE PRECISION
  • Text: VARCHAR(n), TEXT
  • Other: UUID, BOOLEAN, DATE, TIMESTAMP

SQL Constraints

  • PRIMARY KEY: Uniquely identifies each row
  • FOREIGN KEY: Enforces links between tables (REFERENCES)
  • UNIQUE: Ensures all values in a column are different
  • NOT NULL: Ensures a column cannot have NULL values
  • CHECK: Ensures values meet a specific condition
  • DEFAULT: Sets a default value if none provided
  • ON DELETE / ON UPDATE: Controls what happens when referenced row is deleted/updated (CASCADE, etc.)

Essential SQL Queries

CREATE TABLE

CREATE TABLE person (
    id BIGSERIAL PRIMARY KEY,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    email VARCHAR(150) UNIQUE,
    gender VARCHAR(7) NOT NULL,
    date_of_birth DATE NOT NULL
);

INSERT

INSERT INTO person (first_name, last_name, gender, date_of_birth)
VALUES ('Anne', 'Smith', 'FEMALE', DATE '1988-01-09');

SELECT

SELECT * FROM person WHERE gender = 'Male' AND country_of_birth = 'Iran';
SELECT first_name, last_name FROM person ORDER BY last_name ASC;
SELECT DISTINCT country_of_birth FROM person;

Aggregate Functions

SELECT MAX(price), MIN(price), AVG(price), SUM(price) FROM car;

GROUP BY with HAVING

SELECT country_of_birth, COUNT(*) AS population
FROM person
GROUP BY country_of_birth
HAVING COUNT(*) > 100;

JOINS

  • INNER JOIN: Returns rows where there is a match in both tables
SELECT person.first_name, car.make, car.model, car.price
FROM person
JOIN car ON person.car_id = car.id;
  • LEFT JOIN, RIGHT JOIN, FULL JOIN also available

UPDATE & DELETE

UPDATE person SET email = 'Tom@gmail.com' WHERE id = 50;
DELETE FROM person WHERE id = 505;

PostgreSQL-Specific Features

  • UUID: uuid UUID DEFAULT gen_random_uuid()
  • COALESCE: Returns first non-null value
  • NULLIF: Returns NULL if expressions equal, otherwise first expression
  • Casting: '100'::INTEGER or CAST('100' AS INTEGER)
  • TIMESTAMPS: NOW(), EXTRACT(YEAR FROM NOW()), AGE(NOW(), date_of_birth)

JDBC (Java Database Connectivity)

  • Standard Java API for connecting to relational databases
  • Acts as a bridge between Java (object-oriented) and SQL

JDBC Architecture

  • Interfaces: Connection, Statement, PreparedStatement, ResultSet
  • Database Drivers: Each database has its own driver (e.g., postgresql-42.x.jar)
  • DriverManager: Manages JDBC drivers and establishes connections

Statement vs PreparedStatement

  • Statement: Sends raw SQL string each time; vulnerable to SQL Injection
  • PreparedStatement: Uses placeholders (?), pre-compiled, prevents SQL Injection

ResultSet

  • Tabular data structure from SELECT queries
  • Uses a Cursor (pointer) that sits before the first row
  • resultSet.next() moves cursor to next row
  • resultSet.getString("columnName") extracts data

CRUD Operations

  • executeQuery(): For SELECT, returns ResultSet
  • executeUpdate(): For INSERT, UPDATE, DELETE, returns int (affected rows)
  • getGeneratedKeys(): Retrieves auto-generated primary keys

Connection Leaks and Try-with-Resources

  • Connection, Statement, ResultSet use heavy system resources
  • Must close them to prevent database freeze or crash
  • Try-with-Resources automatically closes resources even if exceptions occur

ORM (Object-Relational Mapping)

  • Pain Point of JDBC: Writing raw SQL strings and manual mapping is repetitive and error-prone
  • ORM Solution: Automatically maps Java Classes to Database Tables using annotations (e.g., @Entity)
  • Reality: JDBC is the engine under the hood; ORM is the autopilot. Must understand the engine first!

📑 Slides & Materials

🛠️ Workshop & Assignments

💬 Workshop: Restaurant Database Management System

  • 📂 WS-10-Database
  • Objective: Build a complete Restaurant Management System with PostgreSQL and JDBC
  • Tech Stack: Java 23, Maven, PostgreSQL, JDBC
  • Database Entities:
    1. User (Customer): Unique ID, username (unique), password (hashed), email
    2. MenuItem: Unique ID, name, description, price (positive), category
    3. Order: Unique ID, customer reference, creation date/time, total price
    4. OrderDetail: Unique ID, order reference, menu item reference, quantity (>0), item price at purchase
  • Features:
    1. User Management: Register (unique username, hashed passwords), Login
    2. Menu Browsing: Display all available menu items from database
    3. Order Creation: Select items with quantities, calculate total, save Order and OrderDetail records
    4. Receipt Generation: Query database to print detailed receipt with item names, quantities, unit prices, subtotal, final total
    5. Order History: View all past orders with total amounts
  • Technical Requirements:
    • Use PreparedStatement to prevent SQL injection
    • Proper exception handling (SQLException)
    • Clean separation of concerns (model, database, dao, service, ui packages)
    • Database connection configuration via pom.xml with PostgreSQL dependency
  • JDBC & ORM Demo Files: Included database.sql for schema creation, JDBC and Hibernate examples with connection configuration

🔗 Repository Links

RepositoryDescription
📂 WS-10-DatabaseWorkshop: Restaurant Database Management System with PostgreSQL and JDBC

🌐 Additional Resources

⏩ Navigation

Tip :

Databases are the backbone of most real-world applications. Understanding SQL and JDBC is essential for backend development. When designing your schema, start with an Entity Relationship Diagram (ERD) to visualize the relationships between tables. Always use PreparedStatement to prevent SQL injection attacks – it’s a security best practice. Also, remember to handle database resources properly with try-with-resources to avoid connection leaks. The restaurant workshop provides excellent hands-on experience with all these concepts in one integrated project!

Graph View

Backlinks