How to select records from table using jdbc example

In this tutorial you will learn how you can execute the SELECT SQL statement and get the retrieved result by SELECT query in java programme using JDBC.

You can use database SELECT statement in a java program using JDBC to retrieve the data and display it for the respective Tables. JDBC returns results in a ResultSet object, So to execute the SELECT query and hold the result we require the ResultSet object.

We invoke the jdbc SELECT query using executeQuery() method, using the jdbc SELECT data statement as the parameter. The tabular results of the query are captured in the ResultSet object.

The return value for an executeQuery method is a ResultSet object containing the results of the query sent to the Database.

Once you get the ResultSet object, you can use this objects to retrieve the data using next() method. this method moves the cursor through the rows of data. The ResultSet maintains a cursor which point to current row. As initially the cursor points to first row, when we call next() method the cursor goes to next row and likewise you can retrieve all the rows from ResultSet object.

For each row of the ResultSet object you can retrieve the data of each column using getXXX, where XXX is the data type of that column like String, int, Boolean etc., by passing the number of column or name of column.

The getXXX() has the following signature as below example.

// Retrive the data from ResultSet
while (resultSet.next()) {
    System.out.println("The id of person is : " + resultSet.getString(1));
    System.out.println("The name of person is : " + resultSet.getString(1));
    System.out.println("The age of person is : " + resultSet.getString(2));
}

Below is the example of code which show you how to execute the SELECT query and retrieve the data of each column of each row.

package com.java.connect.jdbc;
 
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
 
public class JDBCSelectRecordExample {
    public static void main(String[] args) {
        Connection connection = null;
        Statement statement = null;
        ResultSet resultSet = null;
 
        try {
            // Step1:Register the mysql driver
            Class.forName("com.mysql.jdbc.Driver"); // Or any other driver which database you want to connect.
            // Step2:Get the connection object
            connection = DriverManager.getConnection("jdbc:mysql://localhost/test", "root", "");
            // Step3:Get the statement object
            statement = connection.createStatement();
            // Prepare the SQL query
            String SQL = "SELECT PERSON_ID, NAME, AGE FROM PERSON";
            // Step4: Run the SQL query and get the ResultSet object.
            resultSet = statement.executeQuery(SQL);
            // Retrive the data from ResultSet
            while (resultSet.next()) {
                System.out.println("The id of person is : " + resultSet.getString(1));
                System.out.println("The name of person is : " + resultSet.getString(2));
                System.out.println("The age of person is : " + resultSet.getString(3));
            }
 
        } catch (SQLException e) {
            e.printStackTrace();
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } finally {
            // Finally we have close all the JDBC resources
            try {
                if (resultSet != null) {
                    resultSet.close();
                }
                if (statement != null) {
                    statement.close();
                }
                if (connection != null) {
                    connection.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
}

The output of above example will be as:

The id of person is : 1
The name of person is : RAJENDRA
The age of person is : 35
The id of person is : 2
The name of person is : MAHENDRA
The age of person is : 29
The id of person is : 3
The name of person is : SURENDRA
The age of person is : 22