JDBCOne

Need to load database. Get your mysql root password.

Make sure you change the PASSWORD in the BookstoreApp.java file.

Then make sure to

cd init-sql
bash ./loadDB.sh
# type in your root password.

Run the main class.

Simple Java JDBC MySQL Example

This is a beginner-friendly Java application that demonstrates how to connect to a MySQL database and perform basic CRUD operations (Create, Read, Update, Delete). This example includes:

• A simple database schema for a bookstore
• Java code that connects to MySQL and performs all four basic operations
• Clear comments explaining each part of the code

Database Schema

The example uses a simple "bookstore" database with one table called "books":

CREATE TABLE books (
    id INT AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(100) NOT NULL,
    author VARCHAR(100) NOT NULL,
    price DECIMAL(10, 2) NOT NULL,
    publish_year INT
);

This schema tracks books with their title, author, price, and publication year.

The Java Application

The Java application (BookstoreApp.java) provides a simple console menu that allows users to:

• Add a new book (INSERT)
• View all books (SELECT)
• Update a book's price (UPDATE)
• Delete a book (DELETE)

Key Features of the Code:

• Database Setup: The application automatically creates the database and table if they don't exist
• PreparedStatements: Uses prepared statements for all database operations to prevent SQL injection
• Resource Management: Properly closes all database resources
• Error Handling: Includes basic exception handling
• User Interface: Simple console-based menu for interaction

How to Use the Code:

Make sure you have MySQL installed and running on your machine Update the connection details (DB_URL, DB_USER, DB_PASSWORD) with your MySQL credentials Compile and run the Java application Follow the menu prompts to perform various database operations

Important JDBC Concepts Demonstrated:

• Establishing a Connection: Using DriverManager.getConnection()
• Creating Statements: Both simple Statement and PreparedStatement
• Executing Queries: Using executeQuery() for SELECT operations
• Executing Updates: Using executeUpdate() for INSERT, UPDATE, and DELETE operations
• Processing Results: Iterating through a ResultSet to retrieve data

Learning From This Example

The code is extensively commented to help you understand each step. Pay special attention to:

• How connections are established and closed
• The difference between Statement and PreparedStatement
• How parameters are set in PreparedStatements
• How ResultSets are processed to retrieve data

This example provides a solid foundation to understand Java JDBC operations with MySQL. You can expand it by adding more features like searching for specific books, implementing transactions, or creating a more complex database schema with multiple related tables.