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

Author:
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:
  1. Setting Up the MySQL Database
  2. Establishing a Database Connection
  3. Creating a Table
  4. Inserting Records
  5. Selecting Records
  6. Updating Records
  7. Deleting Records
  8. 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.

Comments

Post a Comment

Leave Comment