PostgreSQL JDBC CRUD Project

Esse README também esta disponível em Português Brasileiro.

This project is a backend Java application that demonstrates how to implement a full CRUD system using PostgreSQL, JDBC, and JUnit 5 for testing. It applies clean code practices and design patterns such as Generics for reusable DAO layers.

🚀 Key Features

• ✅ Database connection through PostgreSQL using JDBC;
• ✅ Generic DAO implementation to reduce repetitive code;
• ✅ Entity models like Client, Product, and Inventory with custom fields and behaviors;
• ✅ Full CRUD operations with PostgreSQL for all entities;
• ✅ Test-driven development (TDD) approach using JUnit 5;
• ✅ Javadoc documentation for all main classes and interfaces;
• ✅ Usage of environment variables for secure database connection configuration;
• ✅ Schema creation script (schema.sql) included for easy database setup.

🔐 Environment Configuration

To connect to the PostgreSQL database, you must configure the following environment variables:

• DB_URL — The JDBC URL of your PostgreSQL database (e.g., jdbc:postgresql://localhost:5432/online_selling);
• DB_USERNAME — Your PostgreSQL username;
• DB_PASSWORD — Your PostgreSQL password.

These variables are securely loaded at runtime by the application to establish the database connection.

🗂️ Project Structure

• br.com.eaugusto.domain: Entity classes like Client, Product, and Inventory, plus the IPersistable interface;
• br.com.eaugusto.dao: DAO interfaces and implementations for all entities;
• br.com.eaugusto.dao.generics: Generic DAO base implementations using annotations and reflection;
• br.com.eaugusto (tests): JUnit test classes for DAO and connection testing;
• schema.sql: SQL script to create all tables and sequences for the database.

🧪 Testing Approach

• ✅ Tests written using JUnit 5;
• ✅ TDD methodology used during development;
• ✅ Full integration tests for CRUD and database operations;
• ✅ DAOException, MappingException, and connection error testing included;
• ✅ Extensive assertion usage and rollback logic after each test.

📋 Technologies Used

• Java 17+;
• PostgreSQL;
• JDBC;
• JUnit 5;
• Environment Variables;
• Reflection and Annotations in Java.

📑 Learning Goals

• Build reusable DAOs using generics and annotations;
• Apply TDD in backend development;
• Securely configure environment variables for DB access;
• Structure a professional-grade backend Java application;
• Use Java Reflection for dynamic SQL generation.

📂 Database Schema (schema.sql)

📄 Link to schema.sql (SQL Script)

The provided schema.sql script creates the full structure needed for the application:

• Database: online_selling
• Tables: tb_client, tb_product, and tb_inventory
• Sequences: sq_client, sq_product, sq_inventory

Script Highlights:

  
-- Creates the database for the project
CREATE DATABASE online_selling;
-- Remember to switch to the new database before running the below script;
-- Creates the client table
CREATE TABLE IF NOT EXISTS tb_client (
id BIGINT PRIMARY KEY,
code VARCHAR(50) NOT NULL,
name VARCHAR(50) NOT NULL,
cpf VARCHAR(20),
phone VARCHAR(20),
address VARCHAR(50),
address_number VARCHAR(10),
city VARCHAR(50),
state VARCHAR(50),
birth_date VARCHAR(20)
);
-- Creates the sequence for client IDs
CREATE SEQUENCE IF NOT EXISTS sq_client
START WITH 1
INCREMENT BY 1
OWNED BY tb_client.id;
-- Creates the product table
CREATE TABLE IF NOT EXISTS tb_product (
id BIGINT PRIMARY KEY,
code VARCHAR(10) NOT NULL,
name VARCHAR(50) NOT NULL,
description VARCHAR(100),
price NUMERIC(10, 2),
stock_quantity INTEGER,
category VARCHAR(30)
);
-- Creates the sequence for product IDs
CREATE SEQUENCE IF NOT EXISTS sq_product
START WITH 1
INCREMENT BY 1
OWNED BY tb_product.id;
-- Creates the inventory table
CREATE TABLE IF NOT EXISTS tb_inventory (
id BIGINT PRIMARY KEY,
client_id BIGINT NOT NULL,
product_id BIGINT NOT NULL,
quantity_sold INTEGER NOT NULL,
sale_date TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY (client_id) REFERENCES tb_client (id),
FOREIGN KEY (product_id) REFERENCES tb_product (id)
);
-- Creates the sequence for inventory IDs
CREATE SEQUENCE IF NOT EXISTS sq_inventory
START WITH 1
INCREMENT BY 1
OWNED BY tb_inventory.id;