Retrieve a Record from an Existing Table Using JDBC API

A. You can read/fetch the contents of a record in a table using the SELECT query. This will return data in the form of a result table and, these result tables are called result-sets.

Syntax

SELECT column1, column2, columnN FROM table_name;
Or,
SELECT * FROM table_name;

To retrieve the contents of a row of a table using JDBC API you need to:

• Register the driver: Register the driver class using the registerDriver() method of the DriverManager class. Pass the driver class name to it, as parameter.

• Establish a connection: Connect ot the database using the getConnection() method of the DriverManager class. Passing URL (String), username (String), password (String) as parameters to it.

• Create Statement: Create a Statement object using the createStatement() method of the Connection interface.

• Execute the Query: Execute the query using the executeQuery() method of the Statement interface.

Example

Assume we have a table named customers in the database named mydatabase in MySQL, with 12 records as:

+----+-----------+------+---------+----------------+
| ID | NAME      | AGE  | SALARY | ADDRESS         |
+----+-----------+------+---------+----------------+
| 1  | Amit      | 25   | 3000.00 | Hyderabad      |
| 2  | Kalyan    | 27   | 4000.00 | Vishakhapatnam |
| 3  | Renuka    | 30   | 5000.00 | Delhi          |
| 4  | Archana   | 24   | 1500.00 | Mumbai         |
| 5  | Koushik   | 30   | 9000.00 | Kota           |
| 6  | Hardik    | 45   | 6400.00 | Bhopal         |
| 7  | Trupthi   | 33   | 4360.00 | Ahmedabad      |
| 8  | Mithili   | 26   | 4100.00 | Vijayawada     |
| 9  | Maneesh   | 39   | 4000.00 | Hyderabad      |
| 10 | Rajaneesh | 30   | 6400.00 | Delhi          |
| 11 | Komal     | 29   | 8000.00 | Ahmedabad      |
| 12 | Manyata   | 25   | 5000.00 | Vijayawada     |
+----+-----------+------+---------+----------------+

Following JDBC program establishes connection with MySQL and retrieves all the records from the customers table and displays them.

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class ReadRecordsExample {
   public static void main(String args[]) throws SQLException {
      //Registering the Driver
      DriverManager.registerDriver(new com.mysql.jdbc.Driver());
      //Getting the connection
      String mysqlUrl = "jdbc:mysql://localhost/mydatabase";
      Connection con = DriverManager.getConnection(mysqlUrl, "root", "password");
      System.out.println("Connection established......");
      //Creating the Statement
      Statement stmt = con.createStatement();
      //Query to retrieve records
      String query = "Select * from Customers";
      //Executing the query
      ResultSet rs = stmt.executeQuery(query);
      System.out.println("Contents of the Customers table:");
      while(rs.next()) {
         System.out.print("ID: "+rs.getInt("ID")+", ");
         System.out.print("Name: "+rs.getString("Name")+", ");
         System.out.print("Age: "+rs.getInt("Age")+", ");
         System.out.print("Salary: "+rs.getInt("Salary")+", ");
         System.out.print("Address: "+rs.getString("Address"));
         System.out.println();
      }
   }
}

Output

Connection established......
Contents of the Customers table:
ID: 1, Name: Amit, Age: 25, Salary: 3000, Address: Hyderabad
ID: 2, Name: Kalyan, Age: 27, Salary: 4000, Address: Vishakhapatnam
ID: 3, Name: Renuka, Age: 30, Salary: 5000, Address: Delhi
ID: 4, Name: Archana, Age: 24, Salary: 1500, Address: Mumbai
ID: 5, Name: Koushik, Age: 30, Salary: 9000, Address: Kota
ID: 6, Name: Hardik, Age: 45, Salary: 6400, Address: Bhopal
ID: 7, Name: Trupthi, Age: 33, Salary: 4360, Address: Ahmedabad
ID: 8, Name: Mithili, Age: 26, Salary: 4100, Address: Vijayawada
ID: 9, Name: Maneesh, Age: 39, Salary: 4000, Address: Hyderabad
ID: 10, Name: Rajaneesh, Age: 30, Salary: 6400, Address: Delhi
ID: 11, Name: Komal, Age: 29, Salary: 8000, Address: Ahmedabad
ID: 12, Name: Manyata, Age: 25, Salary: 5000, Address: Vijayawada