JDBC PreparedStatement Tutorial: Insert, Select, Update, Delete, Batch Operations with MySQL

Author:
In this tutorial, we will explore how to use the JDBC PreparedStatement interface to interact with a MySQL database. 

Introduction

The PreparedStatement interface is a subinterface of Statement. It is used to execute a parameterized query. An SQL statement is precompiled and stored in a PreparedStatement object, which can then be used to efficiently execute it multiple times.

PreparedStatement features:

Easy to insert parameters into the SQL statement. 
Easy to reuse the PreparedStatement with new parameters. 
May increase the performance of executed statements. 
Enables easier batch updates. 

Why use PreparedStatement? 

The performance of the application will be faster if you use the PreparedStatement interface because a query is compiled only once. 

PreparedStatement interface takes parameters, and the advantage of using SQL statements that take parameters is that you can use the same statement and supply it with different values each time you execute it.

Table of contents

In this tutorial, we will cover the following topics:
  1. Setting Up the MySQL Database
  2. Establishing a Database Connection
  3. Inserting Records
  4. Selecting Records
  5. Updating Records
  6. Deleting Records
  7. Batch Processing

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. Inserting Records

We can insert records into the users table using the PreparedStatement interface. 

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

public class InsertRecordsExample {
    public static void main(String[] args) {
        String insertSQL = "INSERT INTO users (name, email, country, password) VALUES (?, ?, ?, ?)";

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

            // Insert first record
            preparedStatement.setString(1, "Ramesh Kumar");
            preparedStatement.setString(2, "ramesh.kumar@example.com");
            preparedStatement.setString(3, "India");
            preparedStatement.setString(4, "password123");
            int rowsInserted = preparedStatement.executeUpdate();

            // Insert second record
            preparedStatement.setString(1, "Sita Sharma");
            preparedStatement.setString(2, "sita.sharma@example.com");
            preparedStatement.setString(3, "India");
            preparedStatement.setString(4, "password456");
            rowsInserted += preparedStatement.executeUpdate();

            System.out.println(rowsInserted + " rows inserted!");
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

4. Selecting Records

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

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

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

        try (Connection connection = JDBCExample.getConnection();
             PreparedStatement preparedStatement = connection.prepareStatement(selectSQL);
             ResultSet resultSet = preparedStatement.executeQuery()) {

            // Process the ResultSet object
            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();
        }
    }
}

5. Updating Records

To update records, we use the executeUpdate method of the PreparedStatement interface.

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

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

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

            // Update the country for Ramesh Kumar
            preparedStatement.setString(1, "USA");
            preparedStatement.setString(2, "Ramesh Kumar");
            int rowsUpdated = preparedStatement.executeUpdate();
            System.out.println(rowsUpdated + " rows updated!");
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

6. Deleting Records

To delete records, we use the executeUpdate method of the PreparedStatement interface.

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

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

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

            // Delete the record of Sita Sharma
            preparedStatement.setString(1, "Sita Sharma");
            int rowsDeleted = preparedStatement.executeUpdate();
            System.out.println(rowsDeleted + " rows deleted!");
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

7. Batch Processing

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

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

public class BatchProcessingExample {
    public static void main(String[] args) {
        String insertSQL = "INSERT INTO users (name, email, country, password) VALUES (?, ?, ?, ?)";

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

            connection.setAutoCommit(false);  // Disable auto-commit

            // Add first batch
            preparedStatement.setString(1, "Lakshmi Menon");
            preparedStatement.setString(2, "lakshmi.menon@example.com");
            preparedStatement.setString(3, "India");
            preparedStatement.setString(4, "lakshmi123");
            preparedStatement.addBatch();

            // Add second batch
            preparedStatement.setString(1, "Rahul Singh");
            preparedStatement.setString(2, "rahul.singh@example.com");
            preparedStatement.setString(3, "India");
            preparedStatement.setString(4, "rahul456");
            preparedStatement.addBatch();

            // Execute batch
            int[] updateCounts = preparedStatement.executeBatch();
            connection.commit();  // Commit transaction

            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 PreparedStatement interface to interact with a MySQL database. We demonstrated how to insert, select, update, delete records, and perform batch processing. Using the PreparedStatement interface helps prevent SQL injection and improves the performance of executing repetitive SQL commands. This guide should help you get started with JDBC and the PreparedStatement interface.

Comments

Post a Comment

Leave Comment