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.