JDBC PreparedStatement CRUD Operations - Select, Insert, Update and Delete Example

🎓 Top 15 Udemy Courses (80-90% Discount): My Udemy Courses - Ramesh Fadatare — All my Udemy courses are real-time and project oriented courses.

▶️ Subscribe to My YouTube Channel (178K+ subscribers): Java Guides on YouTube

▶️ For AI, ChatGPT, Web, Tech, and Generative AI, subscribe to another channel: Ramesh Fadatare on YouTube

In this tutorial, we'll cover how to perform basic CRUD (Create, Read, Update, Delete) operations using JDBC PreparedStatement with a books table in a MySQL database. This tutorial will guide you through each step, providing code examples and explanations.

Introduction
Setting Up the Environment
Creating the Books Table
CRUD Operations Using JDBC PreparedStatement
- Insert a Record
- Select Records
- Update a Record
- Delete a Record
Conclusion

1. Introduction

JDBC (Java Database Connectivity) is an API for connecting and executing queries on a database. PreparedStatement is a feature in JDBC that helps execute parameterized SQL queries. Using PreparedStatement improves performance and security by precompiling SQL queries and preventing SQL injection attacks.

2. Setting Up the Environment

Before we start, ensure you have the following set up:

Java Development Kit (JDK) installed on your machine.
MySQL Server installed and running.
MySQL JDBC driver (Connector/J) added to your project's classpath.

3. Creating the Books Table

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

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. CRUD Operations Using JDBC PreparedStatement

4.1. Insert a Record

To insert a record into the books table, we'll use a PreparedStatement.

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

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

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

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

            int rowsAffected = preparedStatement.executeUpdate();
            System.out.println(rowsAffected + " row(s) inserted.");
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

4.2. Select Records

To select records from the books table, we'll use a PreparedStatement.

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_BOOKS_SQL = "SELECT * FROM books;";

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

            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: " + id + ", Title: " + title + ", Author: " + author + ", Price: " + price);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

4.3. Update a Record

To update a record in the books table, we'll use a PreparedStatement.

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

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

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

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

            int rowsAffected = preparedStatement.executeUpdate();
            System.out.println(rowsAffected + " row(s) updated.");
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

4.4. Delete a Record

To delete a record from the books table, we'll use a PreparedStatement.

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

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

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

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

            int rowsAffected = preparedStatement.executeUpdate();
            System.out.println(rowsAffected + " row(s) deleted.");
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

5. Conclusion

In this tutorial, we covered how to perform CRUD operations using JDBC PreparedStatement with a books table in a MySQL database. We demonstrated how to insert, select, update, and delete records. By following these steps, you can effectively manage data in your database using Java and JDBC.

My Top and Bestseller Udemy Courses. The sale is going on with a 70 - 80% discount. The discount coupon has been added to each course below:

Spring Boot + RabbitMQ Course - The Practical Guide

Java Guides

Search This Blog