JDBC Dynamically Insert Multiple Rows to MySQL Example

In this tutorial, we will learn how to use JDBC to dynamically insert multiple rows into a MySQL database. This approach allows you to insert data in bulk, improving performance and 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 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. Dynamically Inserting Multiple Rows with JDBC
5. Closing the Connection
6. Conclusion

1. Setting Up the MySQL Database

First, create a database named jdbc_example and an employee 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 employee (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100),
    department VARCHAR(100),
    salary 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. Dynamically Inserting Multiple Rows with JDBC

We will use the PreparedStatement interface to insert multiple rows dynamically into the employee table.

Example: Inserting Multiple Rows

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.List;
import java.util.Arrays;

public class DynamicInsertExample {
    private static final String INSERT_EMPLOYEES_SQL = "INSERT INTO employee (name, department, salary) VALUES (?, ?, ?)";

    public static void main(String[] args) {
        List<Employee> employees = Arrays.asList(
            new Employee("Ravi Kumar", "HR", 50000.00),
            new Employee("Sunita Sharma", "Finance", 60000.00),
            new Employee("Amit Patel", "IT", 75000.00),
            new Employee("Kavita Jain", "Marketing", 65000.00)
        );

        try (Connection connection = JDBCExample.getConnection();
             PreparedStatement preparedStatement = connection.prepareStatement(INSERT_EMPLOYEES_SQL)) {

            connection.setAutoCommit(false);

            for (Employee employee : employees) {
                preparedStatement.setString(1, employee.getName());
                preparedStatement.setString(2, employee.getDepartment());
                preparedStatement.setDouble(3, employee.getSalary());
                preparedStatement.addBatch();
            }

            int[] result = preparedStatement.executeBatch();
            connection.commit();
            System.out.println("Inserted " + result.length + " records successfully!");

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

class Employee {
    private String name;
    private String department;
    private double salary;

    public Employee(String name, String department, double salary) {
        this.name = name;
        this.department = department;
        this.salary = salary;
    }

    public String getName() {
        return name;
    }

    public String getDepartment() {
        return department;
    }

    public double getSalary() {
        return salary;
    }
}

Explanation

1. Establishing a Connection: We establish a connection to the MySQL database using the DriverManager.getConnection() method.
2. Creating a PreparedStatement: We create a PreparedStatement object to execute the SQL insert statement.
3. Adding Parameters to Batch: We loop through the list of employees and add the parameters to the PreparedStatement object for each employee.
4. Executing the Batch: We execute the batch of SQL insert statements using the executeBatch() method.
5. Committing the Transaction: We commit the transaction to make the changes permanent in the database.

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 JDBC to dynamically insert multiple rows into a MySQL database table. We demonstrated how to establish a connection, create a PreparedStatement to 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 batch processing effectively with MySQL.