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.