Java MySQL – SELECT Row(s) using JDBC – Examples

Java MySQL – SELECT Row(s)

In this tutorial, you will learn how to select rows from a MySQL table using Java. We’ll cover examples such as selecting all rows, selecting specific rows based on a condition, and selecting specific columns. Each example includes detailed steps and code snippets.

Prerequisites

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

Step 1: Set Up MySQL Database and Table

Before running the Java code, create a sample table in MySQL. In this tutorial, 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');

This setup creates the school database and a students table with sample data.

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 SELECT Rows

Let’s go through different examples of selecting rows from the students table.

Example 1: Select All Rows

This example retrieves all rows from the students table.

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

public class SelectAllRowsExample {
    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);

            // Create a statement to execute the query
            Statement statement = connection.createStatement();

            // SQL command to select all rows
            String sql = "SELECT * FROM students";

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

            // Process the result set
            while (resultSet.next()) {
                int id = resultSet.getInt("id");
                String name = resultSet.getString("name");
                int age = resultSet.getInt("age");
                String studentClass = resultSet.getString("class");

                System.out.println("ID: " + id + ", Name: " + name + ", Age: " + age + ", Class: " + studentClass);
            }

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

This code retrieves and displays all rows from the students table.

After running each Java example, you can verify the data in MySQL Workbench by running:

SELECT * FROM students;

Example 2: Select Specific Rows Based on a Condition

This example retrieves rows where the class is 10A.

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

public class SelectConditionalRowsExample {
    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 rows based on condition
            String sql = "SELECT * FROM students WHERE class = ?";

            // Prepare the statement
            PreparedStatement preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setString(1, "10A");  // Class condition

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

            // Process the result set
            while (resultSet.next()) {
                int id = resultSet.getInt("id");
                String name = resultSet.getString("name");
                int age = resultSet.getInt("age");
                String studentClass = resultSet.getString("class");

                System.out.println("ID: " + id + ", Name: " + name + ", Age: " + age + ", Class: " + studentClass);
            }

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

This code retrieves and displays all rows from the students table where class is 10A.

After running each Java example, you can verify the data in MySQL Workbench by running:

SELECT * FROM students
WHERE class='10A';

Example 3: Select Specific Columns

This example retrieves only the name and age columns for all students.

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

public class SelectSpecificColumnsExample {
    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 specific columns
            String sql = "SELECT name, age FROM students";

            // Create a statement
            Statement statement = connection.createStatement();

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

            // Process the result set
            while (resultSet.next()) {
                String name = resultSet.getString("name");
                int age = resultSet.getInt("age");

                System.out.println("Name: " + name + ", Age: " + age);
            }

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

This code retrieves and displays only the name and age columns for each student.

After running each Java example, you can verify the data in MySQL Workbench by running:

SELECT name, age FROM students;

Conclusion

This tutorial covered various ways to retrieve rows from a MySQL table using Java, including selecting all rows, filtering by conditions, and selecting specific columns. With JDBC, you can easily customize queries to meet different data retrieval requirements for your applications.