A Java MySQL SELECT example

Summary: This is a Java/MySQL SQL SELECT example, demonstrating how to issue a SQL SELECT command from your Java source code, while accessing a MySQL (or MariaDB) database.

While I tested this solution with MySQL, it should work with many other databases, including MariaDB, Oracle, SQL Server, Postgresql, SQLite, etc.

Solution

To demonstrate this solution, I’ve written a short example program that shows how to perform a SELECT query against a MySQL database in Java. Let’s take a look at it.

An example MySQL database table

The first thing we need for our SQL SELECT query example is a sample database table. To keep it simple — but also show several different MySQL data types — I’ve created the following example database table:

create table users (
    id int unsigned auto_increment not null,
    first_name varchar(32) not null,
    last_name varchar(32) not null,
    date_created timestamp default now(),
    is_admin boolean,
    num_points int,
    primary key (id)
);

A few of these MySQL fields are a little contrived, but I wanted to show several different data types in one table, and this is what I came up with. In particular, the field num_points is a little unusual. I made it up so I could show an int data type in this table, and I was thinking of those websites where points are awarded for giving correct answers.

I’ve populated this database table in some related articles (Java MySQL INSERT using Statement, Java MySQL INSERT using PreparedStatement), so when I run this SELECT query from the MySQL command prompt:

select * from users;

I see this output:

+----+------------+-----------+---------------------+----------+------------+
| id | first_name | last_name | date_created        | is_admin | num_points |
+----+------------+-----------+---------------------+----------+------------+
|  2 | Fred       | Flinstone | 2010-06-23 00:00:00 |        0 |       6000 | 
|  3 | Barney     | Rubble    | 2010-06-23 00:00:00 |        0 |       5000 | 
+----+------------+-----------+---------------------+----------+------------+
2 rows in set (0.00 sec)

With a little example data in my database, I’m ready to write some Java/JDBC code.

The Java source code

To perform a SQL SELECT query from Java, you just need to follow these steps:

1. Create a Java Connection to the MySQL database
2. Define the SELECT statement
3. Execute the SELECT query, getting a Java ResultSet from that query
4. Iterate over the ResultSet, getting the database fields (columns) from each row of data that is returned
5. Close the Java database connection
6. Catch any SQL exceptions that may come up during the process

I tried to document the following Java/MySQL SELECT example so you can see these steps. Note that in this example my MySQL database username is “root”, my password is blank, and the database is running on the same computer where this program is run, so the database host name is “localhost”:

import java.sql.*;

/**
 * A Java MySQL SELECT statement example.
 * Demonstrates the use of a SQL SELECT statement against a
 * MySQL database, called from a Java program.
 * 
 * Created by Alvin Alexander, https://alvinalexander.com
 */
public class JavaMysqlSelectExample
{

  public static void main(String[] args)
  {
    try
    {
      // create our mysql database connection
      String myDriver = "org.gjt.mm.mysql.Driver";
      String myUrl = "jdbc:mysql://localhost/test";
      Class.forName(myDriver);
      Connection conn = DriverManager.getConnection(myUrl, "root", "");
      
      // our SQL SELECT query. 
      // if you only need a few columns, specify them by name instead of using "*"
      String query = "SELECT * FROM users";

      // create the java statement
      Statement st = conn.createStatement();
      
      // execute the query, and get a java resultset
      ResultSet rs = st.executeQuery(query);
      
      // iterate through the java resultset
      while (rs.next())
      {
        int id = rs.getInt("id");
        String firstName = rs.getString("first_name");
        String lastName = rs.getString("last_name");
        Date dateCreated = rs.getDate("date_created");
        boolean isAdmin = rs.getBoolean("is_admin");
        int numPoints = rs.getInt("num_points");
        
        // print the results
        System.out.format("%s, %s, %s, %s, %s, %s\n", id, firstName, lastName, dateCreated, isAdmin, numPoints);
      }
      st.close();
    }
    catch (Exception e)
    {
      System.err.println("Got an exception! ");
      System.err.println(e.getMessage());
    }
  }
}

The results

Assuming that everything is set up properly on your computer system, you should see output like this when you run this example Java program:

2, Fred, Flinstone, 2010-06-23, false, 6000
3, Barney, Rubble, 2010-06-23, false, 5000

Of course your output will vary depending on the actual data in your database table.

Regarding your setup, the main things you’ll need are a Java compiler (SDK), the MySQL JDBC database driver, and a MySQL instance running on your computer (or running on another computer you can access).

In “real world” Java database programs I almost always use the Spring JDBC library to access a database, but when you're first getting started, I think it’s important to see examples like this so you can understand how things work under the covers.

Summary: My Java MySQL SELECT example

As a quick recap, this example demonstrated the following steps:

1. How to create a Java Connection to a MySQL database
2. How to create a SQL SELECT statement
3. How to execute a Java MySQL SELECT query, getting a Java ResultSet from that query
4. How to iterate over the Java ResultSet, getting the database fields (columns) from each row of data that is returned
5. How to close a Java database connection
6. How to catch any SQL exceptions that may come up during the process

In summary, I hope this Java MySQL SELECT example is helpful.