Java JDBC CRUD in Eclipse - SQL Insert, Select, Update, and Delete

Author:
In this tutorial, we will learn how to perform CRUD (Create, Read, Update, Delete) operations using Java JDBC in Eclipse with a MySQL database. We will cover each operation step-by-step and explain the code briefly.

Table of Contents

  1. Introduction
  2. Setting Up the Environment
  3. Creating the Database and Table
  4. Inserting Records
  5. Selecting Records
  6. Updating Records
  7. Deleting Records
  8. Main Method to Test CRUD Operations
  9. Conclusion

1. Introduction

CRUD operations are the basic operations we perform on a database. These operations are essential for interacting with database records. In this tutorial, we will use JDBC to connect to a MySQL database and perform CRUD operations.

2. Setting Up the Environment

  1. Install JDK: Ensure that the Java Development Kit (JDK) is installed on your machine.
  2. Install MySQL: Ensure that MySQL Server is installed and running.
  3. Eclipse IDE: Use Eclipse IDE for Java Developers.
  4. Add MySQL JDBC Driver: Download the MySQL JDBC driver (Connector/J) and add it to your project's build path in Eclipse.

3. Creating the Database and Table

First, create a database and table in MySQL. Use the following SQL script to create a books table in your database:

CREATE DATABASE library;
USE library;

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
);

4. Inserting Records

Let's start by inserting records into the books table.

Code Example

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;

public class InsertBookExample {
    private static final String INSERT_BOOK_SQL = "INSERT INTO books (title, author, price) VALUES (?, ?, ?)";

    public static void main(String[] args) {
        try (Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/library", "root", "root");
             PreparedStatement preparedStatement = connection.prepareStatement(INSERT_BOOK_SQL)) {

            preparedStatement.setString(1, "Effective Java");
            preparedStatement.setString(2, "Joshua Bloch");
            preparedStatement.setBigDecimal(3, new BigDecimal("45.50"));

            int rowAffected = preparedStatement.executeUpdate();
            System.out.println("Rows affected: " + rowAffected);

        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

Explanation

  • Connection: Establish a connection to the MySQL database using DriverManager.getConnection.
  • PreparedStatement: Create a PreparedStatement object to execute the SQL insert query.
  • Set Parameters: Set the values for the title, author, and price columns.
  • Execute Update: Execute the insert query using executeUpdate().

5. Selecting Records

Next, let's select records from the books table.

Code Example

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

public class SelectBooksExample {
    private static final String SELECT_ALL_BOOKS = "SELECT * FROM books";

    public static void main(String[] args) {
        try (Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/library", "root", "root");
             PreparedStatement preparedStatement = connection.prepareStatement(SELECT_ALL_BOOKS)) {

            ResultSet resultSet = preparedStatement.executeQuery();

            while (resultSet.next()) {
                int id = resultSet.getInt("id");
                String title = resultSet.getString("title");
                String author = resultSet.getString("author");
                BigDecimal price = resultSet.getBigDecimal("price");

                System.out.println(id + ", " + title + ", " + author + ", " + price);
            }

        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

Explanation

  • PreparedStatement: Create a PreparedStatement object to execute the SQL select query.
  • ResultSet: Execute the select query using executeQuery() and process the ResultSet to retrieve records.

6. Updating Records

Let's update records in the books table.

Code Example

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;

public class UpdateBookExample {
    private static final String UPDATE_BOOK_SQL = "UPDATE books SET price = ? WHERE title = ?";

    public static void main(String[] args) {
        try (Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/library", "root", "root");
             PreparedStatement preparedStatement = connection.prepareStatement(UPDATE_BOOK_SQL)) {

            preparedStatement.setBigDecimal(1, new BigDecimal("50.00"));
            preparedStatement.setString(2, "Effective Java");

            int rowAffected = preparedStatement.executeUpdate();
            System.out.println("Rows affected: " + rowAffected);

        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

Explanation

  • PreparedStatement: Create a PreparedStatement object to execute the SQL update query.
  • Set Parameters: Set the new price and title parameters.
  • Execute Update: Execute the update query using executeUpdate().

7. Deleting Records

Finally, let's delete records from the books table.

Code Example

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;

public class DeleteBookExample {
    private static final String DELETE_BOOK_SQL = "DELETE FROM books WHERE title = ?";

    public static void main(String[] args) {
        try (Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/library", "root", "root");
             PreparedStatement preparedStatement = connection.prepareStatement(DELETE_BOOK_SQL)) {

            preparedStatement.setString(1, "Effective Java");

            int rowAffected = preparedStatement.executeUpdate();
            System.out.println("Rows affected: " + rowAffected);

        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

Explanation

  • PreparedStatement: Create a PreparedStatement object to execute the SQL delete query.
  • Set Parameters: Set the title parameter.
  • Execute Update: Execute the delete query using executeUpdate().

8. Main Method to Test CRUD Operations

Let's create a main method to test all CRUD operations together.

Code Example

public class Main {
    public static void main(String[] args) {
        InsertBookExample insertBookExample = new InsertBookExample();
        insertBookExample.insertBook("Java: The Complete Reference", "Herbert Schildt", new BigDecimal("55.00"));

        SelectBooksExample selectBooksExample = new SelectBooksExample();
        selectBooksExample.selectAllBooks();

        UpdateBookExample updateBookExample = new UpdateBookExample();
        updateBookExample.updateBookPrice("Java: The Complete Reference", new BigDecimal("60.00"));

        DeleteBookExample deleteBookExample = new DeleteBookExample();
        deleteBookExample.deleteBook("Java: The Complete Reference");

        selectBooksExample.selectAllBooks();
    }
}

Explanation

  • InsertBookExample: Call the method to insert a new book.
  • SelectBooksExample: Call the method to select all books.
  • UpdateBookExample: Call the method to update the book price.
  • DeleteBookExample: Call the method to delete the book.
  • Print Output: Print the results of each operation to the console.

9. Conclusion

In this tutorial, we learned how to perform CRUD operations using Java JDBC with a MySQL database. We covered how to insert, select, update, and delete records using JDBC PreparedStatement. By following these steps, you can interact with a MySQL database using Java in Eclipse.

Comments

Post a Comment

Leave Comment