How to retrieve particular columns of a table using JDBC program?

JDBCJava 8MySQLMySQLi Database

A ResultSet interface in JDBC represents the tabular data generated by SQL queries. It has a cursor which points to the current row. Initially this cursor is positioned before first row.

You can move the cursor using the next() method and, you can retrieve the column values of a row using the getter methods of the ResultSet interface (getInt(), getString(), getDate() etc..).

To retrieve required data from a table:

  • Connect to the database.

  • Create a Statement object.

  • Execute the Statement using the executeQuery() method. To this method, pass the select query in the String format. To retrieve all the values, we use the following query:

Select * from TableName;
  • To retrieve particular columns, specify the required column names instead of * as:

select Name, DOB from Emp

Example

Assume we have a table named Emp in the database with the following description:

+----------+--------------+------+-----+---------+-------+
| Field    | Type         | Null | Key | Default | Extra |
+----------+--------------+------+-----+---------+-------+
| Name     | varchar(255) | YES  |     | NULL    |       |
| DOB      | date         | YES  |     | NULL    |       | 
| Location | varchar(255) | YES  |     | NULL    |       |
+----------+--------------+------+-----+---------+-------+

Following JDBC example retrieves Name and DOB values of the employees from the Emp table.

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
public class RetrievingParticularColumn {
      public static void main(String args[]) throws Exception {
      //Registering the Driver
      DriverManager.registerDriver(new com.mysql.jdbc.Driver());
      //Getting the connection
      String mysqlUrl = "jdbc:mysql://localhost/sampleDB";
      Connection con = DriverManager.getConnection(mysqlUrl, "root", "password");
      System.out.println("Connection established......");

      //Creating a Statement object
      Statement stmt = con.createStatement();
      //Retrieving the data
      ResultSet rs = stmt.executeQuery("select Name, DOB from Emp");

      System.out.println("Contents of the table");
      while(rs.next()) {
         System.out.print("Name of the Employee: "+rs.getString("Name")+", ");
         System.out.print("Date of Birth: "+rs.getDate("DOB"));
         System.out.println("");
      }
   }
}

Output

Connection established......
Contents of the table
Name of the Employee: Amit, Date of Birth: 1970-01-08
Name of the Employee: Sumith, Date of Birth: 1970-01-08
Name of the Employee: Sudha, Date of Birth: 1970-01-05
raja
Published on 20-Mar-2019 15:57:23
Advertisements