Java MySQL – SELECT DISTINCT using JDBC

Java MySQL – SELECT DISTINCT

In this tutorial, you will learn how to use the SELECT DISTINCT statement in MySQL with Java to retrieve unique records from a table. We’ll cover connecting Java to MySQL, writing Java code to execute the SELECT DISTINCT query, and verifying the output.

This tutorial includes examples of selecting distinct values from a single column and multiple columns.

---

Prerequisites

• MySQL installed and running
• Java Development Kit (JDK) installed
• MySQL Connector/J (JDBC Driver) downloaded
• MySQL Workbench to verify the query results

---

Step 1: Set Up MySQL Database and Table

Before running the Java code, create a sample table in MySQL. We’ll use a table named students with columns for ID, name, age, and class. Run the following SQL commands in MySQL Workbench:

CREATE DATABASE IF NOT EXISTS school;
USE school;

CREATE TABLE IF NOT EXISTS students (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(50),
    age INT,
    class VARCHAR(10)
);

-- Insert sample data
INSERT INTO students (name, age, class) VALUES ('Alice', 19, '10A');
INSERT INTO students (name, age, class) VALUES ('Bob', 20, '10B');
INSERT INTO students (name, age, class) VALUES ('Charlie', 21, '10A');
INSERT INTO students (name, age, class) VALUES ('Daisy', 22, '10B');
INSERT INTO students (name, age, class) VALUES ('Alice', 19, '10A');  -- Duplicate entry for testing DISTINCT

This setup creates the school database and a students table with sample data, including duplicate entries for testing the SELECT DISTINCT query.

Step 2: Set Up MySQL Connection in Java

To connect to MySQL from Java, you need the MySQL Connector/J library. Add this library to your Java project to establish the connection.

1. Download MySQL Connector/J

Go to the official MySQL Connector/J download page, download the connector, and add the JAR file to your Java project’s classpath.

Click on the Download button.

You may click No thanks, just start my download.
In the downloaded package, there should be a jar file.

2. Add the Connector to Your Java Project

For the sake of convenience, let us copy the jar file to libs folder in the Java Project.

Right-click on your Java project and go to Properties.

Select Java Build Path > Libraries.

Add the MySQL Connector/J JAR file you downloaded.

Click OK.

Click Apply and Close.

Step 3: Java Code to Use SELECT DISTINCT

Let’s go through examples of using the SELECT DISTINCT statement in Java to retrieve unique records from the students table.

---

Example 1: Select Distinct Values from a Single Column

This example retrieves distinct values from the name column in the students table.

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;

public class SelectDistinctExample {
    public static void main(String[] args) {
        String jdbcUrl = "jdbc:mysql://localhost:3306/school";
        String user = "root";
        String password = "your_password";

        try {
            // Establish a connection to MySQL
            Connection connection = DriverManager.getConnection(jdbcUrl, user, password);

            // SQL command to select distinct names
            String sql = "SELECT DISTINCT name FROM students";

            // Create a statement and execute the query
            Statement statement = connection.createStatement();
            ResultSet resultSet = statement.executeQuery(sql);

            // Process the result set
            System.out.println("Distinct Names:");
            while (resultSet.next()) {
                String name = resultSet.getString("name");
                System.out.println(name);
            }

            // Close resources
            resultSet.close();
            statement.close();
            connection.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
}

This code retrieves distinct names from the students table. If there are duplicate names, only unique names will be displayed.

After running the Java program, open MySQL Workbench and execute the following query to verify the results:

SELECT DISTINCT name FROM students;

---

Example 2: Select Distinct Values from Multiple Columns

This example retrieves distinct combinations of name and class from the students table.

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;

public class SelectDistinctMultipleColumnsExample {
    public static void main(String[] args) {
        String jdbcUrl = "jdbc:mysql://localhost:3306/school";
        String user = "root";
        String password = "your_password";

        try {
            // Establish a connection to MySQL
            Connection connection = DriverManager.getConnection(jdbcUrl, user, password);

            // SQL command to select distinct name and class
            String sql = "SELECT DISTINCT name, class FROM students";

            // Create a statement and execute the query
            Statement statement = connection.createStatement();
            ResultSet resultSet = statement.executeQuery(sql);

            // Process the result set
            System.out.println("Distinct Name and Class:");
            while (resultSet.next()) {
                String name = resultSet.getString("name");
                String studentClass = resultSet.getString("class");
                System.out.println("Name: " + name + ", Class: " + studentClass);
            }

            // Close resources
            resultSet.close();
            statement.close();
            connection.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
}

This code retrieves distinct combinations of name and class, ensuring that only unique pairs are displayed.

After running the Java program, open MySQL Workbench and execute the following query to verify the results:

SELECT DISTINCT name, class FROM students;

---

Example 3: Using DISTINCT with a WHERE Clause

This example retrieves distinct class values where the age is greater than 20.

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;

public class SelectDistinctWithWhereExample {
    public static void main(String[] args) {
        String jdbcUrl = "jdbc:mysql://localhost:3306/school";
        String user = "root";
        String password = "your_password";

        try {
            // Establish a connection to MySQL
            Connection connection = DriverManager.getConnection(jdbcUrl, user, password);

            // SQL command to select distinct classes where age > 20
            String sql = "SELECT DISTINCT class FROM students WHERE age > 20";

            // Create a statement and execute the query
            Statement statement = connection.createStatement();
            ResultSet resultSet = statement.executeQuery(sql);

            // Process the result set
            System.out.println("Distinct Classes (Age > 20):");
            while (resultSet.next()) {
                String studentClass = resultSet.getString("class");
                System.out.println(studentClass);
            }

            // Close resources
            resultSet.close();
            statement.close();
            connection.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
}

This code retrieves distinct class values where the age of the student is greater than 20.

After running the Java program, open MySQL Workbench and execute the following query to verify the results:

SELECT DISTINCT class FROM students WHERE age > 20;

Conclusion

Using SELECT DISTINCT in MySQL with Java is a powerful way to retrieve unique records from a table. This tutorial provided examples of selecting distinct values from a single column, multiple columns, and using conditions. These techniques can be applied to various scenarios in your Java applications to ensure data uniqueness.