Table of Content

---

---

In the previous series of the tutorials, we have learnt about insertion of records in the table.

In this example, we are looking into the JDBC database tutorial to fetch records from the selected MySQL Database table

JDBC Select Records from Database :

package com.c4learn.jdbc;
import java.sql.*;
public class JDBCSelectRecord {
  // JDBC driver name and database URL
  static final String JDBC_DRIVER = "com.mysql.jdbc.Driver";
  static final String DB = "jdbc:mysql://localhost/student";
  // 1. Database User name & Password
  static final String USER = "admin";
  static final String PWD = "password";
  public static void main(String[] args) {
    Connection con = null;
    Statement stmt = null;
    try {
      // 2. Register JDBC driver
      Class.forName("com.mysql.jdbc.Driver");
      // 3. Open a connection
      System.out.println("Connecting to database...");
      con = DriverManager.getConnection(DB, USER, PWD);
      // 4. Execute a query
      System.out.println("Records :");
      stmt = con.createStatement();
      String sql = "select * from user";
      ResultSet rs = stmt.executeQuery(sql);
      // 5. Fetch Details
      while (rs.next()) {
        // Retrieve by column name
        int id = rs.getInt("roll");
        int age = rs.getInt("age");
        String first = rs.getString("first");
        String last = rs.getString("last");
        // Display values
        System.out.print("Roll : " + id);
        System.out.print(", Age : " + age);
        System.out.print(", First : " + first);
        System.out.println(", Last : " + last);
      }
      rs.close();
    } catch (SQLException e) {
      System.out.println(e.getMessage());
    } catch (Exception e) {
      System.out.println(e.getMessage());
    } finally {
      try {
        System.out.println("Closing Connection");
        if (stmt != null)
          stmt.close();
      } catch (SQLException e) {
      }
      try {
        if (con != null)
          con.close();
      } catch (SQLException sqlEx) {
        System.out.println(sqlEx.getMessage());
      }
    }
  }
}

Output :

Connecting to database...
Records :
Roll : 1, Age : 20, First : Ram, Last : Naik
Roll : 2, Age : 23, First : Sam, Last : George
Roll : 3, Age : 21, First : Ani, Last : Pate
Closing Connection

Explanation : JDBC Select Record

We have already inserted the records in the database, If we want to fetch the records from the DB then we can carry out following steps.

Step 1 : Importing the packages :

When we are dealing with the database connectivity then we need to include the required jar file which is necessary for the database programming.

import java.sql.*;

Step 2 : Registering JDBC driver :

After importing the packages we need to initialize a driver to open a communications channel with the database.

Class.forName("com.mysql.jdbc.Driver");

Step 3 : Open a connection .

DriverManager.getConnection() method is required to create a Connection object.

It represents a physical connection with database server. Below statement is used to create the DB URL -

static final String DB = "jdbc:mysql://localhost/student";

Using below statement we can connect to database physically.

System.out.println("Connecting to database...");
con = DriverManager.getConnection(DB_URL, USER,PWD);

Step 4 : Execute a query .

Below statement will create statement object for executing the query.

stmt = con.createStatement();

We have created the following query -

String sql = "select * from user";

To execute the query using the statement object we can write following line of code -

ResultSet rs = stmt.executeQuery(sql);

We can access the data in a ResultSet object using a cursor which is nothing but the pointer that points to one row of data in the ResultSet object.

We know that we have Database schema having roll number field as integer so we can access the field using below syntax

int id = rs.getInt("roll");
int age = rs.getInt("age");
String first = rs.getString("first");
String last = rs.getString("last");

Step 5 : Clean up the environment .

After the execution of the query we need to clean up the resources. It requires explicitly closing all database resources

if (statement != null)
       statement.close();

and

if (con != null)
          con.close();

---

---