SQL Abstraction Java Application

Project Overview

A Java-based SQL command executor that provides an interactive interface for executing both predefined and custom SQL queries against a MySQL database. Features formatted table output for SELECT queries and automatic handling of INSERT, UPDATE, and DELETE operations.

Features

Core Functionality

Interactive Menu System: User-friendly console interface with clear navigation
Predefined SQL Commands: 5 ready-to-use SQL queries for common operations
Custom SQL Execution: Run any valid SQL query directly
Formatted Output: Automatically adjusts column widths for clean table display
Error Handling: Detailed SQL exception reporting with error codes

Database Operations Support

SELECT Queries: Beautifully formatted table output with dynamic column sizing
Data Manipulation: INSERT, UPDATE, DELETE operations with row count feedback
Transaction Support: Basic SQL transaction execution
Scrollable ResultSets: Allows multiple passes through query results

Technical Features

JDBC Connectivity: Standard database connectivity
Resource Management: Automatic resource closing with try-with-resources
Security Features: Basic error handling and user input validation
Cross-Platform: Runs on any system with Java and MySQL

Project Structure

sql-abstraction-project/
│
├── SqlAbstraction.java          # Main Java application
├── group.sql          # Database creation script
└── mysql-connector-java-9.0.27.jar  # MySQL JDBC driver

Prerequisites

1. Java Development Kit (JDK)

Version: JDK 8 or higher
Verification: Run java -version and javac -version in terminal
Download: Oracle JDK or OpenJDK

2. MySQL Database

Version: MySQL 5.7 or higher (MySQL 8.x recommended)
Installation Required: MySQL Server must be installed and running
Default Port: 3306 (standard MySQL port)
Download: MySQL Community Server

3. MySQL JDBC Driver

File: mysql-connector-java-9.0.27.jar
Version: 8.0.x or higher recommended
Download: MySQL Connector/J
- Select "Platform Independent"
- Download the ZIP file
- Extract mysql-connector-java-9.0.27.jar

Installation & Setup

Step 1: Download and Extract JDBC Driver

Download MySQL Connector/J from the official website
Extract the ZIP file
Locate mysql-connector-java-9.0.27.jar file

Step 2: Setup Database

-- 1. Start MySQL service
-- On Windows: Services → Start MySQL
-- On Linux/Mac: sudo systemctl start mysql

-- 2. Access MySQL as root
mysql -u root -p

-- 3. Execute the database schema file
mysql> SOURCE /path/to/group.sql;
-- OR create database manually:
mysql> CREATE DATABASE group_db;
mysql> USE group_db;
-- Then copy and paste the CREATE TABLE statements from group.sql

Step 3: Update Database Credentials

In SqlAbstraction.java, update these lines (around line 133):

String jdbcUrl = "jdbc:mysql://localhost:3306/group_db";
String username = "root";  // Your MySQL username
String password = "your_password_here";  // Your MySQL password

How to Run the Application

Method 1: Using Command Line (Recommended)

# 1.# Clone the project
git clone https://github.com/daniel-oyoo/SqlAbstraction.java.git
Navigate to project directory
cd sql-abstraction-java
Compile the Java program with JDBC driver in classpath
javac -cp "mysql-connector-java-9.0.27.jar" SqlAbstraction.java

# 2. Run the program with JDBC driver in classpath
java -cp ".;mysql-connector-java-9.0.27.jar" SqlAbstraction
# On Linux/Mac use : instead of ;
java -cp ".:mysql-connector-java-9.0.27.jar" SqlAbstraction

# 3. Alternative: Compile and run with wildcard (if multiple JARs)
javac -cp "*.jar" SqlAbstraction.java
java -cp ".:*" SqlAbstraction

Method 2: Using an IDE (Eclipse/IntelliJ/NetBeans)

Create a new Java project
Add the JDBC JAR to build path:
- Eclipse: Right-click project → Build Path → Configure Build Path → Add External JARs
- IntelliJ: File → Project Structure → Libraries → Add (+) → Java → Select JAR
- NetBeans: Right-click project → Properties → Libraries → Add JAR/Folder
Copy the Java file into the src folder
Run the program from the IDE

Method 3: Create a Shell Script (Linux/Mac)

Create run.sh:

#!/bin/bash
# Compile
javac -cp "mysql-connector-java-9.0.27.jar" SqlAbstraction.java

# Run
java -cp ".:mysql-connector-java-9.0.27.jar" SqlAbstraction

Make executable and run:

chmod +x run.sh
./run.sh

Method 4: Create a Batch File (Windows)

Create run.bat:

@echo off
REM Compile
javac -cp "mysql-connector-java-9.0.27.jar" SqlAbstraction.java

REM Run
java -cp ".;mysql-connector-java-9.0.27.jar" SqlAbstraction
pause

Double-click run.bat to execute.

Sample Output

Program Startup:

Welcome to the SQL Command Executor!
You can execute predefined commands or enter custom SQL queries.

Available Predefined SQL Commands:
1: SELECT * FROM users;
2: SELECT * FROM products WHERE price > 50.00;
3: INSERT INTO orders (user_id, product_id, quantity) VALUES (1, 2, 3);
4: UPDATE users SET active = 1 WHERE user_id = 1;
5: DELETE FROM products WHERE product_id = 10;

Options:
1. Execute a predefined SQL command
2. Enter a custom SQL query
3. Exit
Enter your choice:

Executing Predefined Command (SELECT):

Enter your choice: 1
Enter the number of the SQL command to execute (or 0 to go back): 1
Executing: SELECT * FROM users;
Executing SQL: SELECT * FROM users;

user_id  username  password                        email              active  registration_date    
1        testuser  $2b$10$EXAMPLEHASHEDPASSWORD    test@example.com   true    2024-01-12 10:30:45

Executing DML Command (INSERT/UPDATE/DELETE):

Enter your choice: 1
Enter the number of the SQL command to execute (or 0 to go back): 3
Executing: INSERT INTO orders (user_id, product_id, quantity) VALUES (1, 2, 3);
Executing SQL: INSERT INTO orders (user_id, product_id, quantity) VALUES (1, 2, 3);
1 rows affected.

Custom SQL Query:

Enter your choice: 2
Enter your custom SQL query (or type 'back' to go back): SELECT product_name, price FROM products WHERE stock_quantity > 0;
Executing: SELECT product_name, price FROM products WHERE stock_quantity > 0;
Executing SQL: SELECT product_name, price FROM products WHERE stock_quantity > 0;

product_name       price    
Example Product 1  99.99    
Example Product 2  49.99

Error Handling:

Enter your choice: 2
Enter your custom SQL query (or type 'back' to go back): SELECT * FROM non_existent_table;
Executing: SELECT * FROM non_existent_table;
Executing SQL: SELECT * FROM non_existent_table;
SQL Exception: Table 'group_db.non_existent_table' doesn't exist
SQL State: 42S02
Vendor Error Code: 1146

Troubleshooting

Common Issues and Solutions:

"Class not found" or "No suitable driver found" error

Error: Could not find or load main class SqlAbstraction
# OR
java.sql.SQLException: No suitable driver found

Solution: Ensure JDBC JAR is in classpath:

# Verify JAR file exists
ls mysql-connector-java-*.jar

# Use absolute path if needed
java -cp "/full/path/to/mysql-connector-java-9.0.27.jar:." SqlAbstraction

Connection refused error
```
SQL Exception: Communications link failure
```
Solution:
- Verify MySQL service is running
- Check MySQL port (default 3306)
- Verify username/password in Java code
- Check if MySQL allows remote connections
Access denied for user error
```
SQL Exception: Access denied for user 'root'@'localhost'
```
Solution:
- Verify MySQL credentials
- Grant privileges: GRANT ALL PRIVILEGES ON group_db.* TO 'root'@'localhost';
- Use correct username/password combination

Unknown database error

SQL Exception: Unknown database 'group_db'

Solution: Create the database first:

CREATE DATABASE group_db;
USE group_db;
-- Run CREATE TABLE statements from group.sql

Testing the Application

Quick Test Sequence:

Run predefined command 1: SELECT * FROM users; - Should show sample user
Run predefined command 2: SELECT * FROM products WHERE price > 50.00; - Should show product over $50
Run custom query: SELECT COUNT(*) FROM products; - Should return "2"
Test error handling: SELECT * FROM fake_table; - Should show SQL error

Verify Database Connection:

Create a simple test file TestConnection.java:

import java.sql.*;
public class TestConnection {
    public static void main(String[] args) {
        try {
            Connection conn = DriverManager.getConnection(
                "jdbc:mysql://localhost:3306/group_db", 
                "root", 
                "your_password"
            );
            System.out.println("Connection successful!");
            conn.close();
        } catch (SQLException e) {
            System.out.println("Connection failed: " + e.getMessage());
        }
    }
}

Compile and run with the same classpath.

Notes & Best Practices

Security Considerations:

Never hardcode passwords in production code

Use environment variables or configuration files
Example: String password = System.getenv("DB_PASSWORD");

SQL Injection Protection
- This demo accepts raw SQL input (for educational purposes)
- In production, use PreparedStatement for parameterized queries
Credential Management
- Store database credentials securely
- Use different credentials for development/production
- Consider using connection pools in production

Performance Tips:

Connection Management: The current implementation creates new connections for each query. For production, consider connection pooling.
ResultSet Handling: Scrollable ResultSets are memory-intensive for large datasets.
Indexing: Add indexes to frequently queried columns in your database.

Extending the Application:

Add More Predefined Commands: Add to the sqlCommands HashMap
Add Transaction Support: Use connection.setAutoCommit(false) and commit()/rollback()
Add Logging: Implement proper logging with SLF4J or Log4J
Add Export Features: Export query results to CSV/Excel

Additional Resources

Useful Links:

Suggested Improvements:

Add a configuration file for database settings
Implement password input masking
Add query history feature
Support for multiple database types (PostgreSQL, SQLite)
Add GUI interface using Swing or JavaFX

License

This project is for educational purposes. Feel free to modify and extend for personal or academic use.

Contributing

For educational demonstrations. Not actively maintained for production use.

Happy Coding! If you encounter any issues, review the troubleshooting section or check that all prerequisites are properly installed.

Name		Name	Last commit message	Last commit date
Latest commit History 7 Commits
README.md		README.md
SqlAbstraction.java		SqlAbstraction.java
group.sql		group.sql
mysql-connector-j-9.2.0.zip		mysql-connector-j-9.2.0.zip

daniel-oyoo/SqlAbstraction

Folders and files

Latest commit

History

Repository files navigation