This project demonstrates how to connect and integrate a Java application with an Oracle Database to perform essential CRUD operations (Create, Read, Update, Delete). It follows a modular, layered design that separates logic, data, and presentation.
- Design a relational schema for managing employees, departments, and projects.
- Implement Java code to connect with Oracle DB using JDBC.
- Demonstrate queries, transactions, and data flow.
- Apply security, performance, and maintainability principles.
The project follows a 3-tier architecture for clarity and scalability.
| Layer | Description | Technology Used |
|---|---|---|
| Presentation | Interface where users or APIs interact | JavaFX / Spring MVC / REST API |
| Business Logic | Validation, rules, and data processing | Core Java / Spring Boot |
| Data Access | Database operations and persistence | JDBC / Hibernate / MyBatis |
[ User Interface ]
↓
[ Java Service Layer ]
↓
[ DAO Layer (JDBC/ORM) ]
↓
[ Oracle Database ]
Each layer is independent but communicates through well-defined interfaces, ensuring loose coupling and easy maintenance.
The project uses a single schema named HR_SYSTEM, containing three interconnected tables:
- DEPARTMENTS
- EMPLOYEES
- PROJECTS
DEPARTMENTS (1) ───< EMPLOYEES (M)
EMPLOYEES (1) ───< PROJECTS (M)
This structure enforces referential integrity:
- One department can have many employees.
- One employee can manage multiple projects.
Purpose:
Stores information about company departments.
| Column | Type | Constraint | Description |
|---|---|---|---|
| DEPT_ID | NUMBER(5) | PRIMARY KEY | Unique Department ID |
| DEPT_NAME | VARCHAR2(50) | NOT NULL | Department Name |
| LOCATION | VARCHAR2(50) | Department Location |
SQL Script:
CREATE TABLE DEPARTMENTS (
DEPT_ID NUMBER(5) PRIMARY KEY,
DEPT_NAME VARCHAR2(50) NOT NULL,
LOCATION VARCHAR2(50)
);Purpose:
Holds all employee details and their department association.
| Column | Type | Constraint | Description |
|---|---|---|---|
| EMP_ID | NUMBER(6) | PRIMARY KEY | Employee ID |
| NAME | VARCHAR2(100) | NOT NULL | Employee Name |
| VARCHAR2(100) | UNIQUE | Unique Email | |
| SALARY | NUMBER(10,2) | CHECK (SALARY > 0) | Monthly Salary |
| DEPT_ID | NUMBER(5) | FOREIGN KEY | Links to DEPARTMENTS(DEPT_ID) |
SQL Script:
CREATE TABLE EMPLOYEES (
EMP_ID NUMBER(6) PRIMARY KEY,
NAME VARCHAR2(100) NOT NULL,
EMAIL VARCHAR2(100) UNIQUE,
SALARY NUMBER(10,2) CHECK (SALARY > 0),
DEPT_ID NUMBER(5),
FOREIGN KEY (DEPT_ID) REFERENCES DEPARTMENTS(DEPT_ID)
);Purpose:
Contains project information and the employee responsible.
| Column | Type | Constraint | Description |
|---|---|---|---|
| PROJECT_ID | NUMBER(6) | PRIMARY KEY | Project ID |
| PROJECT_NAME | VARCHAR2(100) | NOT NULL | Project Name |
| START_DATE | DATE | Start Date | |
| END_DATE | DATE | Completion Date | |
| EMP_ID | NUMBER(6) | FOREIGN KEY | References EMPLOYEES(EMP_ID) |
SQL Script:
CREATE TABLE PROJECTS (
PROJECT_ID NUMBER(6) PRIMARY KEY,
PROJECT_NAME VARCHAR2(100) NOT NULL,
START_DATE DATE,
END_DATE DATE,
EMP_ID NUMBER(6),
FOREIGN KEY (EMP_ID) REFERENCES EMPLOYEES(EMP_ID)
);
Java interacts with Oracle through JDBC (Java Database Connectivity), enabling direct SQL execution within applications.
String url = "jdbc:oracle:thin:@localhost:1521:ORCL";
String username = "hr_system";
String password = "hr_pass";
Connection conn = DriverManager.getConnection(url, username, password);public void insertEmployee(int id, String name, String email, double salary, int deptId) {
String sql = "INSERT INTO EMPLOYEES VALUES (?, ?, ?, ?, ?)";
try (Connection conn = DriverManager.getConnection(URL, USER, PASS);
PreparedStatement ps = conn.prepareStatement(sql)) {
ps.setInt(1, id);
ps.setString(2, name);
ps.setString(3, email);
ps.setDouble(4, salary);
ps.setInt(5, deptId);
ps.executeUpdate();
} catch (SQLException e) {
e.printStackTrace();
}
}Explanation:
- Uses a PreparedStatement to avoid SQL injection.
- Automatically closes resources using try-with-resources.
- Keeps SQL logic separate in DAO layer for maintainability.
+-------------+ +-------------+ +---------------+
| User UI | ---> | Java App | ---> | Oracle DB |
| (Form/API) | | (DAO Layer) | | (Tables/SQL) |
+-------------+ +-------------+ +---------------+
Explanation:
- Input flows from UI → Java logic → Database.
- Results flow back for display or reporting.
- Ensures a clean, predictable data lifecycle.
| Concern | Description | Best Practice |
|---|---|---|
| SQL Injection | Malicious SQL from user input | Always use PreparedStatement |
| Connection Overhead | Frequent connection creation | Use Connection Pooling (HikariCP, UCP) |
| Data Security | Sensitive credentials or data | Use SSL and environment configs |
| Query Performance | Slow queries | Apply indexes and use EXPLAIN PLAN |
SELECT e.EMP_ID, e.NAME, d.DEPT_NAME
FROM EMPLOYEES e
JOIN DEPARTMENTS d ON e.DEPT_ID = d.DEPT_ID;Explanation:
This query demonstrates a JOIN between tables to retrieve related data.
SELECT p.PROJECT_NAME, e.NAME AS ASSIGNED_TO
FROM PROJECTS p
JOIN EMPLOYEES e ON p.EMP_ID = e.EMP_ID;Explanation:
Shows how foreign key relationships connect projects and employees.
| Feature | Purpose |
|---|---|
| Stored Procedures | Move complex logic into Oracle for performance |
| Triggers | Automate data consistency and audit trails |
| Java Stored Procedures | Run Java inside Oracle DB |
| REST APIs | Expose data via web endpoints using Spring Boot |
| Reporting | Generate PDF or Excel reports with tools like JasperReports |
This Oracle–Java project provides a complete example of enterprise data integration, covering:
- Logical data modeling with relational schemas
- Secure database access through JDBC
- Structured, layered design for scalability
- Extensible features for web and API development
| Component | Description |
|---|---|
| SQL Scripts | Create schema and tables |
| Java Source | DAO, models, service classes |
| Config Files | Database connection details |
| Documentation | Project report (this file) |
| Optional | REST endpoints or GUI interface |