Java JDBC PreparedStatement with a List of Values in an IN Clause

In this tutorial, we will explore how to use the JDBC PreparedStatement interface to execute a SQL query with an IN clause that takes a list of values. This can be useful when you need to filter records based on multiple criteria.

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 PreparedStatement?

The PreparedStatement interface is a part of the JDBC API and provides methods to execute parameterized SQL queries. Unlike the Statement interface, PreparedStatement allows you to set parameters dynamically, making it more flexible and secure.

Table of Contents

1. Setting Up the MySQL Database
2. Adding MySQL JDBC Driver to Your Project
3. Establishing a Database Connection
4. Using JDBC PreparedStatement with an IN Clause
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)
);

INSERT INTO products (name, description, price) VALUES
('Laptop', 'Dell Inspiron', 75000.00),
('Smartphone', 'Samsung Galaxy', 30000.00),
('Tablet', 'Apple iPad', 50000.00),
('Smartwatch', 'Apple Watch', 20000.00),
('Headphones', 'Bose QuietComfort', 15000.00);

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. Using JDBC PreparedStatement with an IN Clause

We can use the Connection interface to create a PreparedStatement object and execute a SQL query with an IN clause that takes a list of values. Here is how you can do it:

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Arrays;
import java.util.List;
import java.util.stream.Collectors;
import java.util.stream.IntStream;

public class SelectWithInClauseExample {
    private static final String SELECT_PRODUCTS_SQL = "SELECT * FROM products WHERE name IN (%s)";

    public static void main(String[] args) {
        List<String> productNames = Arrays.asList("Laptop", "Tablet", "Smartwatch");

        // Create placeholders for the IN clause
        String placeholders = IntStream.range(0, productNames.size())
                .mapToObj(i -> "?")
                .collect(Collectors.joining(", "));

        // Complete SQL with placeholders
        String sql = String.format(SELECT_PRODUCTS_SQL, placeholders);

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

            // Set the parameters
            for (int i = 0; i < productNames.size(); i++) {
                preparedStatement.setString(i + 1, productNames.get(i));
            }

            // Execute the query
            ResultSet resultSet = preparedStatement.executeQuery();

            // Process the ResultSet object
            while (resultSet.next()) {
                int id = resultSet.getInt("id");
                String name = resultSet.getString("name");
                String description = resultSet.getString("description");
                double price = resultSet.getDouble("price");

                System.out.println("ID: " + id + ", Name: " + name + ", Description: " + description + ", Price: " + price);
            }
        } 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 PreparedStatement interface to execute a SQL query with an IN clause that takes a list of values. We demonstrated how to establish a connection, execute a SQL query to retrieve data, 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 PreparedStatement interface effectively with MySQL.