JDBC Statement: Insert, Select, Update, Delete, Batch Operations with MySQL

🎓 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 will explore how to use the JDBC Statement interface to interact with a MySQL database.

We'll cover the following key points:

Setting Up the MySQL Database
Establishing a Database Connection
Creating a Table
Inserting Records
Selecting Records
Updating Records
Deleting Records
Batch Processing

Let's dive into each step with detailed explanations and code examples.

Prerequisites

Ensure you have MySQL installed and running on your machine.
Create a database named jdbc_example.
Add the MySQL JDBC driver to your project. If you are using Maven, add the following dependency to your pom.xml file:

<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <version>8.0.30</version>
</dependency>

1. Setting Up the MySQL Database

First, create a database named jdbc_example and a users table within it. Open your MySQL command line or any MySQL client and execute the following commands:

CREATE DATABASE jdbc_example;

USE jdbc_example;

CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100),
    email VARCHAR(100),
    country VARCHAR(100),
    password VARCHAR(100)
);

2. Establishing a Database Connection

We will start by establishing a connection to the MySQL database using JDBC.

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

public class JDBCExample {
    private static final String URL = "jdbc:mysql://localhost:3306/jdbc_example";
    private static final String USER = "root";
    private static final String PASSWORD = "password";

    public static Connection getConnection() throws SQLException {
        return DriverManager.getConnection(URL, USER, PASSWORD);
    }

    public static void main(String[] args) {
        try (Connection connection = getConnection()) {
            if (connection != null) {
                System.out.println("Connected to the database!");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

3. Creating a Table

Next, we will create a users table if it doesn't already exist. We use the execute method of the Statement interface.

import java.sql.Connection;
import java.sql.SQLException;
import java.sql.Statement;

public class CreateTableExample {
    public static void main(String[] args) {
        String createTableSQL = "CREATE TABLE IF NOT EXISTS users ("
                + "id INT AUTO_INCREMENT PRIMARY KEY, "
                + "name VARCHAR(100), "
                + "email VARCHAR(100), "
                + "country VARCHAR(100), "
                + "password VARCHAR(100)"
                + ")";

        try (Connection connection = JDBCExample.getConnection();
             Statement statement = connection.createStatement()) {
            statement.execute(createTableSQL);
            System.out.println("Table created successfully!");
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

4. Inserting Records

We can insert records into the users table using the executeUpdate method.

import java.sql.Connection;
import java.sql.SQLException;
import java.sql.Statement;

public class InsertRecordsExample {
    public static void main(String[] args) {
        String insertSQL = "INSERT INTO users (name, email, country, password) VALUES "
                + "('John Doe', 'john.doe@example.com', 'USA', 'secret'), "
                + "('Jane Doe', 'jane.doe@example.com', 'UK', 'secret123')";

        try (Connection connection = JDBCExample.getConnection();
             Statement statement = connection.createStatement()) {
            int rowsInserted = statement.executeUpdate(insertSQL);
            System.out.println(rowsInserted + " rows inserted!");
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

5. Selecting Records

To retrieve records, we use the executeQuery method which returns a ResultSet.

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class SelectRecordsExample {
    public static void main(String[] args) {
        String selectSQL = "SELECT * FROM users";

        try (Connection connection = JDBCExample.getConnection();
             Statement statement = connection.createStatement();
             ResultSet resultSet = statement.executeQuery(selectSQL)) {

            while (resultSet.next()) {
                int id = resultSet.getInt("id");
                String name = resultSet.getString("name");
                String email = resultSet.getString("email");
                String country = resultSet.getString("country");
                String password = resultSet.getString("password");
                System.out.println(id + ", " + name + ", " + email + ", " + country + ", " + password);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

6. Updating Records

To update records, we use the executeUpdate method again.

import java.sql.Connection;
import java.sql.SQLException;
import java.sql.Statement;

public class UpdateRecordExample {
    public static void main(String[] args) {
        String updateSQL = "UPDATE users SET country = 'Canada' WHERE name = 'John Doe'";

        try (Connection connection = JDBCExample.getConnection();
             Statement statement = connection.createStatement()) {
            int rowsUpdated = statement.executeUpdate(updateSQL);
            System.out.println(rowsUpdated + " rows updated!");
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

7. Deleting Records

To delete records, we use the executeUpdate method.

import java.sql.Connection;
import java.sql.SQLException;
import java.sql.Statement;

public class DeleteRecordExample {
    public static void main(String[] args) {
        String deleteSQL = "DELETE FROM users WHERE name = 'Jane Doe'";

        try (Connection connection = JDBCExample.getConnection();
             Statement statement = connection.createStatement()) {
            int rowsDeleted = statement.executeUpdate(deleteSQL);
            System.out.println(rowsDeleted + " rows deleted!");
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

8. Batch Processing

We can execute multiple SQL commands in a batch using the addBatch and executeBatch methods.

import java.sql.Connection;
import java.sql.SQLException;
import java.sql.Statement;

public class BatchProcessingExample {
    public static void main(String[] args) {
        String insertSQL1 = "INSERT INTO users (name, email, country, password) VALUES ('Alice', 'alice@example.com', 'France', 'alice123')";
        String insertSQL2 = "INSERT INTO users (name, email, country, password) VALUES ('Bob', 'bob@example.com', 'Germany', 'bob123')";

        try (Connection connection = JDBCExample.getConnection();
             Statement statement = connection.createStatement()) {
            statement.addBatch(insertSQL1);
            statement.addBatch(insertSQL2);

            int[] updateCounts = statement.executeBatch();
            System.out.println("Batch executed with " + updateCounts.length + " statements!");
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

Conclusion

In this tutorial, we have covered the basics of using the JDBC Statement interface to interact with a MySQL database. We demonstrated how to create a table, insert, select, update, delete records, and perform batch processing. This guide should help you get started with JDBC and the Statement interface.

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