JDBC Statement – Select Records Example

A JDBC statement object is used to send SQL command(s) to your relational database management system (RDBMS). It is associated with an open connection to the database and may not be created without one. In this example, we will select all records in the employee table using executeQuery() method.

Common Methods used with Statements

Creating a Statement Object

statement = connection.createStatement();
statement.executeUpdate(sql);

Full Program Listing

package com.avaldes.tutorials;

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

public class JDBCStatementSelectRecordsExample {

  public static void main(String[] args) throws SQLException {
    String database_url   = "jdbc:sqlserver://localhost:1433;databaseName=tutorial";
    String username     = "webuser";
    String password     = "webuser123";
    Connection connection   = null;
    Statement statement   = null;
    ResultSet result      = null;
    String sql        = null;
    
    try {
      Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
    } catch (ClassNotFoundException e) {
      System.out.println("ERROR: Unable to load SQLServer JDBC Driver");
      e.printStackTrace();
      return;
    }
   
    try {
      connection = DriverManager.getConnection(database_url, username, password);
    } catch (SQLException e) {
      System.out.println("ERROR:  Unable to establish a connection with the database!");
      e.printStackTrace();
      return;
    }
   
    System.out.println("Trying to select all records using statement...");
    try {
      if (connection != null) {
        statement = connection.createStatement();
        sql = "SELECT employee_id, first_name, last_name, pay_type, pay_rate, start_date, end_date "
            + "FROM dbo.employee "
            + "WHERE end_date is null";
        result = statement.executeQuery(sql);
        System.out.println(sql);

        System.out.println("nList of Employees...n");
        System.out.format("%-8s  %-18s %-16s %-10s %-10s %-10sn", "EMPLOYEE_ID", "FIRST_NAME", "LAST_NAME", 
            "PAY_TYPE", "PAY_RATE", "START_DATE");

        while (result.next()) {
          int employee_id = result.getInt("employee_id");
          String first_name = result.getString("first_name");
          String last_name = result.getString("last_name");
          String pay_type = result.getString("pay_type");
          float pay_rate = result.getFloat("pay_rate"); 
          java.sql.Date start_date = result.getDate("start_date");
          System.out.format("    %-10d %-18s %-18s %-7s %-9.0f %-10sn",  employee_id, first_name,
              last_name, pay_type, pay_rate, start_date.toString());
        }
      } else {
        System.out.println("ERROR: Unable to make a database connection!");
      }
    } catch (SQLException e) {
      e.printStackTrace();
      return;
    } finally {
      System.out.println("Closing connection...");
        if (connection != null) connection.close();
    }
  }
}

Output

Trying to select all records using statement...
SELECT employee_id, first_name, last_name, pay_type, pay_rate, start_date FROM dbo.employee WHERE end_date is null

List of Employees...

EMPLOYEE_ID  FIRST_NAME    LAST_NAME      PAY_TYPE   PAY_RATE   START_DATE
    1          John          Smith            S       85000     2001-03-15
    2          David         Harvey           S       53000     2000-02-11
    4          James         Young            S       103500    2012-05-14
    5          Danielle      Gray             S       94250     2009-04-09
    6          Jeff          Wang             S       76500     2000-03-11
    8          Karen         Ly               S       109100    2011-06-14
    9          Chris         Canning          S       150000    2013-02-19
    11         Mary          Anderson         S       104100    2009-05-24
    12         Tina          Mayer            S       143700    2012-04-08
    100        Babe          Ruth             S       125700    2013-02-14
    101        Derek         Jeter            S       53500     2006-04-08
Closing connection...

Please Share Us on Social Media