Java SQL Server: Selecting Data

Summary: in this tutorial, you will learn how to query data from a table in SQL Server using JDBC API.

This tutorial begins where the Performing Batch Operations in Java tutorial left off.

The following are the steps for querying data from a table in SQL Server using JDBC:

First, connect to the SQL Server database.
Second, create a new PreparedStatement object (or Statement object) with a SELECT statement.
Third, set the values for the parameters of the statement by calling the set* methods of the PreparedStatement object.
Fourth, execute the SELECT statement in SQL Server by calling the executeQuery() method of the Statement object. The method returns a ResultSet object.
Fifth, iterate over the rows from the ResultSet by calling its next() method and process each row individually.
Finally, close the result set, statement, and database connection. If you use the try-with-resources, you don’t need to close them manually.

Selecting all rows from a table

We’ll show you how to query all rows from the Authors table and return a list of Author objects:

Step 1. Modify the AuthorDB class by adding the findAll() method that returns all rows from the Authors table as a list of Author objects:

public List < Author > findAll() throws DBException {
  var sql = "SELECT AuthorID, FirstName, LastName, BirthDate FROM Authors";

  var authors = new ArrayList<Author>();

  try (var statement = connection.createStatement();
  var resultset = statement.executeQuery(sql)) {
    //
    while (resultset.next()) {
      // create the author object
      authors.add(createAuthorFrom(resultset));
    }

    return authors;
  } catch(SQLException e) {
    throw new DBException(e.getMessage());
  }
}Code language: Java (java)

How it works.

First, construct a SELECT statement that returns all rows from the Authors table:

var sql = "SELECT AuthorID, FirstName, LastName, BirthDate FROM Authors";Code language: Java (java)

Second, create a new ArrayList of Author objects:

var authors = new ArrayList<Author>();Code language: Java (java)

Third, create a Statement object by calling the createStatement of the Connection object and execute the SELECT statement by calling the executeQuery() method of the Statement object:

try (var statement = connection.createStatement();
     var resultset = statement.executeQuery(sql)) {Code language: Java (java)

The executeQuery() method returns a ResultSet object.

Since we use the try-with-resources to manage the Statement and ResultSet objects, we don’t need to manually close them.

Fourth, iterate over the rows in the result set by calling the next() method, create an Author object from the ResultSet, and add it to the list:

while (resultset.next()) {
  authors.add(createAuthorFrom(resultset));
}Code language: Java (java)

Note that we’ll define the createAuthorFrom() method shortly.

Fifth, return the authors list from the method:

return authors;Code language: Java (java)

Finally, raise a DBException if an error occurs when querying data from the Authors table:

} catch(SQLException e) {
  throw new DBException(e.getMessage());
}Code language: Java (java)

Step 2. Define the createAuthorFrom() method that creates a new Author object from a ResultSet object:

private Author createAuthorFrom(ResultSet resultset) throws SQLException {
   return new Author(
      resultset.getInt("AuthorID"),
      resultset.getString("FirstName"),
      resultset.getString("LastName"),
      resultset.getDate("BirthDate").toLocalDate()
   );
}Code language: Java (java)

In this method, we call the get* method of the ResultSet object to retrieve AuthorID, FirstName, LastName, and BirthDate and return a new Author object created based on these values.

Step 3. Modify the main() method of the Main class to use the findAll() method:

import java.sql.SQLException;

public class Main {
    public static void main(String[] args) {
        try (var connection = SQLServerConnection.connect()) {
            var authorDB = new AuthorDB(connection);
            var authors = authorDB.findAll();
            for (var author: authors){
                System.out.println(author);
            }
        } catch (SQLException | DBException e) {
            System.err.println(e.getMessage());
        }
    }
}Code language: Java (java)

How it works.

First, connect to the SQL Server:

try (var connection = SQLServerConnection.connect()) {Code language: Java (java)

Second, create a new AuthorDB object:

var authorDB = new AuthorDB(connection);Code language: Java (java)

Third, find all authors from the Authors table by calling the findAll() method:

var authors = authorDB.findAll();Code language: Java (java)

Fourth, display all authors:

for (var author: authors) {
  System.out.println(author);
}Code language: Java (java)

Finally, display an error message if any exception occurs while selecting data:

} catch(SQLException | DBException e) {
  System.err.println(e.getMessage());
}Code language: Java (java)

Step 4. Run the Java program.

Here’s the partial output:

Author{authorId=1, firstName='John', lastName='Doe', birthDate=1990-12-31}
Author{authorId=2, firstName='Lisa', lastName='Rodriguez', birthDate=1989-04-07}
Author{authorId=3, firstName='Susan', lastName='Williams', birthDate=1941-08-16}
...Code language: Java (java)

Selecting one row from a table

We’ll select an author by Id.

Step 1. Modify the AuthorDB class and define a method findById to find the author by Id:

public Author findById(int authorId) throws DBException {

  var sql = "SELECT AuthorID, FirstName, LastName, BirthDate FROM Authors WHERE AuthorID = ?";

  try (var statement = connection.prepareStatement(sql)) {
    // Bind values to parameters
    statement.setInt(1, authorId);

    // Execute the query
    try (var resultset = statement.executeQuery()) {
      //
      if (!resultset.next()) {
        throw new DBException("The author with id " + authorId + " not found.");
      }

      // create the author object
      return createAuthorFrom(resultset);

    }

  } catch(SQLException e) {
    throw new DBException(e.getMessage());
  }
}Code language: Java (java)

How it works.

First, construct a SELECT statement that returns all rows from the Authors table:

var sql = "SELECT AuthorID, FirstName, LastName, BirthDate FROM Authors WHERE AuthorID = ?";Code language: Java (java)

Second, create a PreparedStatement object by calling the prepareStatement() method of the Connection object:

try (var statement = connection.prepareStatement(sql)) {Code language: Java (java)

In this example, we use the PreparedStatement because the query has a parameter AuthorID.

Third, bind the value to the query:

statement.setInt(1, authorId);Code language: Java (java)

Fourth, execute the query to select data from the Authors table by calling the executeQuery() method of the PreparedStatement object:

try (var resultset = statement.executeQuery()) {Code language: Java (java)

Fifth, throw an exception if no author with the specified Id exists:

if (!resultset.next()) {
  throw new DBException("The author with id " + authorId + " not found.");
}Code language: Java (java)

Sixth, return the Author object created from the result:

return createAuthorFrom(resultset);Code language: Java (java)

Seventh, throw a DBException if an error occurs while selecting the data:

} catch(SQLException e) {
  throw new DBException(e.getMessage());
}Code language: Java (java)

Step 2. Modify the main() method of the Main class to use the findById() method:

import java.sql.SQLException;

public class Main {
    public static void main(String[] args) {
        try (var connection = SQLServerConnection.connect()) {
            var authorDB = new AuthorDB(connection);

            // find author by Id
            var author = authorDB.findById(1);
            System.out.println(author);

        } catch (SQLException | DBException e) {
            System.err.println(e.getMessage());
        }
    }
}Code language: Java (java)

Step 3. Run the Java program.

Here’s the output:

Author{authorId=1, firstName='John', lastName='Doe', birthDate=1990-12-31}Code language: Java (java)

If you change the author Id to a value that does not exist, for example:

var author = authorDB.findById(-1);Code language: Java (java)

you’ll get the following message:

The author with id -1 not found.Code language: Java (java)

Download the project source code

Summary

Call the executeQuery() of the Statement or PreparedStatement object to execute a query and return a ResultSet object.
Call the next() method of the ResultSet object to iterate over the rows from the result set.
Use the get* method to retrieve data from each row in the result set.

Was this tutorial helpful?