JDBC - Select records example

Posted on February 12, 2017


Technologies used:   JDK 1.8.0_121 | MySQL 5.7.12 | Eclipse Mars.2 (4.5.2)

In this section, we will show you how to select the records from a table using the JDBC API. There are five steps for selecting a record from a table using JDBC API.

  • Open connection to the database.
  • Create a statement object to perform a select query.
  • Execute the executeQuery() method of statement object to submit a SQL query to database.
  • Process the result object.
  • Close connection to the database.

JDBC Statement + Select records example

Here is an example of using  the Statement object to select records from a table.

SelectRecordExample1.java

package com.boraji.tutorial.jdbc;

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

/**
 * @author imssbora
 */
public class SelectRecordExample1 {

  public static void main(String[] args) {
    String jdbcUrl = "jdbc:mysql://localhost:3306/BORAJI";
    String username = "root";
    String password = "admin";
    String sql = "select * from employee";
    
    try (Connection conn = DriverManager.getConnection(jdbcUrl, username, password); 
        Statement stmt = conn.createStatement(); 
        ResultSet rs = stmt.executeQuery(sql);) {
      
      int row = 0;
      while (rs.next()) {
        System.out.println("----------Row " + (++row) + " ------------");
        System.out.println("EMP_ID=" + rs.getString(1));
        System.out.println("NAME=" + rs.getString(2));
        System.out.println("DOB=" + rs.getString(3));
        System.out.println("EMAIL=" + rs.getString(4));
        System.out.println("DEPT=" + rs.getString(5));
        System.out.println();
      }
    } catch (SQLException e) {
      e.printStackTrace();
    }
  }
}

Output

----------Row 1 ------------
EMP_ID=1
NAME=Michael
DOB=1995-07-01
[email protected]
DEPT=IT Department

----------Row 2 ------------
EMP_ID=2
NAME=Henry
DOB=1990-02-21
[email protected]
DEPT=HR Department

 

JDBC PreparedStatement + Select records example

Here is an example of using the PreparedStatement object to select records from a table.

SelectRecordExample2.java

package com.boraji.tutorial.jdbc;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

/**
 * @author imssbora
 */
public class SelectRecordExample2 {

  public static void main(String[] args) {
    String jdbcUrl = "jdbc:mysql://localhost:3306/BORAJI";
    String username = "root";
    String password = "admin";
    String sql = "select * from employee where EMP_ID=?";

    try (Connection conn = DriverManager.getConnection(jdbcUrl, username, password);
        PreparedStatement stmt = conn.prepareStatement(sql);) {

      stmt.setInt(1, 2);
      try (ResultSet rs = stmt.executeQuery();) {
        while (rs.next()) {
          System.out.println("EMP_ID=" + rs.getString(1));
          System.out.println("NAME=" + rs.getString(2));
          System.out.println("DOB=" + rs.getString(3));
          System.out.println("EMAIL=" + rs.getString(4));
          System.out.println("DEPT=" + rs.getString(5));
        }
      }
    } catch (SQLException e) {
      e.printStackTrace();
    }
  }
}

Output

EMP_ID=2
NAME=Henry
DOB=1990-02-21
[email protected]
DEPT=HR Department