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

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.

Table of Contents

1. Introduction
2. Setting Up the Environment
3. Creating the Books Table
4. CRUD Operations Using JDBC PreparedStatement
   • Insert a Record
   • Select Records
   • Update a Record
   • Delete a Record
5. 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.