Querying Data From MySQL Using JDBC

Summary: In this tutorial, you will learn how to query data from MySQL using JDBC Statement and ResultSet objects.

This tutorial picks up where the Connecting to MySQL Using JDBC Driver tutorial left off.

Step 1. Create a new file called Candidate.java.

Step 2. Define a new class called Candidate in the Candidate.java file:

import java.sql.SQLException;

public class Candidate {
    public static void getAll(){
        String sql = "SELECT first_name, last_name, email " +
                "FROM candidates";

        try (var conn = MySQLConnection.connect();
             var stmt  = conn.createStatement();
             var rs    = stmt.executeQuery(sql)) {

            // loop through the result set
            while (rs.next()) {
                System.out.println(
                    rs.getString("first_name") + "\t" +
                    rs.getString("last_name")  + "\t" +
                    rs.getString("email")
                );

            }
        } catch (SQLException ex) {
            System.out.println(ex.getMessage());
        }
    }
}
Code language: JavaScript (javascript)

How it works.

First, import the SQLException class from the java.sql package. SQLException is an exception class that handles database-related errors:

import java.sql.SQLException;Code language: CSS (css)

Second, define a class named Candidate:

public class Candidate {Code language: PHP (php)

Third, define a static method called getAll() that retrieves data from the database:

public static void getAll() {Code language: JavaScript (javascript)

Fourth, construct a query that retrieves the first name and last name from the candidates table:

String sql = "SELECT first_name, last_name, email " +
             "FROM candidates";Code language: JavaScript (javascript)

Fifth, open the database connection using the connect() method of the MySQLConnection class, create a Statement object for executing SQL queries, execute the SQL query by calling the executeQuery() method of the Statement object, and store the result in the ResultSet object (rs):

try (var conn = MySQLConnection.connect();
     var stmt  = conn.createStatement();
     var rs    = stmt.executeQuery(sql)) {Code language: JavaScript (javascript)

Note that we use the try block is a try-with-resources block to automatically close resources like database connections (conn), statements (stmt), and result sets (rs) when the block is exited.

Sixth, use a while loop to iterate through the result set. Fetch each row and print the values of first name, last name, and email to the console:

while (rs.next()) {
    System.out.println(
        rs.getString("first_name") + "\t" +
        rs.getString("last_name")  + "\t" +
        rs.getString("email")
    );
}Code language: JavaScript (javascript)

Seventh, handle the exception if it occurs:

} catch (SQLException ex) {
    System.out.println(ex.getMessage());
}Code language: JavaScript (javascript)

Step 3. Modify the Main.java file that calls the getAll() method of the Candidate class:

public class Main {
    public static void main(String[] args){
        Candidate.getAll();
    }
}Code language: JavaScript (javascript)

If you run the Main program, you’ll see the following output:

Carine 	Schmitt	carine.s@gmail.com
Jean	King	[email protected]
Peter	Ferguson	[email protected]
...Code language: CSS (css)

Summary

• Use the Statement object to execute an SQL query.
• Use the ResultSet object to store the result of a query.