JDBC Statement - Batch Insert Example

In this tutorial, we will explore how to use the JDBC Statement interface to perform batch inserts into a MySQL database table. Batch processing allows you to group multiple SQL statements into a batch and execute them as a single unit. This can significantly improve the performance of database operations by reducing the number of database calls.

Introduction

What is JDBC?

Java Database Connectivity (JDBC) is an API that enables Java applications to interact with databases. It provides methods to query and update data in a database, as well as to retrieve metadata about the database itself.

What is Statement?

The Statement interface is a part of the JDBC API and provides methods to execute SQL queries against the database. It is typically used for executing static SQL statements that do not require parameters.

What is Batch Processing?

Batch processing allows you to execute a group of SQL statements as a batch. This reduces the number of database calls and can significantly improve performance when executing multiple statements.

Table of Contents

1. Setting Up the MySQL Database
2. Adding MySQL JDBC Driver to Your Project
3. Establishing a Database Connection
4. Batch Inserting Records with JDBC Statement
5. Closing the Connection
6. Conclusion

1. Setting Up the MySQL Database

First, create a database named jdbc_example and a products 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 products (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100),
    description VARCHAR(255),
    price DECIMAL(10, 2)
);

2. Adding MySQL JDBC Driver to Your Project

To interact with a MySQL database, you need to 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>

3. 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();
        }
    }
}

4. Batch Inserting Records with JDBC Statement

We can use the Connection interface to create a Statement object and execute a batch of SQL insert statements.

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

public class BatchInsertExample {
    public static void main(String[] args) {
        String[] insertSQLs = {
            "INSERT INTO products (name, description, price) VALUES ('Laptop', 'Dell Inspiron', 75000.00)",
            "INSERT INTO products (name, description, price) VALUES ('Smartphone', 'Samsung Galaxy', 30000.00)",
            "INSERT INTO products (name, description, price) VALUES ('Tablet', 'Apple iPad', 50000.00)",
            "INSERT INTO products (name, description, price) VALUES ('Smartwatch', 'Apple Watch', 20000.00)",
            "INSERT INTO products (name, description, price) VALUES ('Headphones', 'Bose QuietComfort', 15000.00)"
        };

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

            // Add SQL statements to the batch
            for (String sql : insertSQLs) {
                statement.addBatch(sql);
            }

            // Execute the batch of SQL statements
            int[] result = statement.executeBatch();

            System.out.println("Inserted " + result.length + " records successfully!");

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

5. Closing the Connection

Using the try-with-resources statement ensures that the connection is closed automatically. This is important to free up database resources.

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

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

Conclusion

In this tutorial, we have covered the basics of using the JDBC Statement interface to perform batch inserts into a MySQL database table. We demonstrated how to establish a connection, execute a batch of SQL insert statements, and close the connection using the try-with-resources statement. This guide should help you get started with JDBC and understand how to use the Statement interface effectively for batch processing with MySQL.