Recent Posts

Tuesday, 13 November 2018

JDBC - Select records from a table

     This tutorial provides an example on how to select/ fetch records from a table using JDBC application.

Note
     Before going into the program, it is advised to go through JDBC Programming Basic Steps where the meaning of Connection, Statement etc. are discussed. 

Example 1: Select all Rows from the Table
import java.sql.*;
/**
 * 
 * @author ashok.mariyala
 *
 */
public class SelectAllRowsDemo { 
   public static void main(String[] args) throws Exception {
      String sqlQuery = "select * from emp"; 
      Class.forName("oracle.jdbc.OracleDriver"); 
      Connection con = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:XE","scott","tiger"); 
      Statement st = con.createStatement();
      boolean flag = false;
      ResultSet rs = st.executeQuery(sqlQuery);
      System.out.println("Eno\tEName\tESalary\tEAddress");
      while(rs.next()) {
         System.out.println(rs.getInt(1)+"\t"+rs.getString(2)+"\t"+rs.getDouble(3)+"\t"+rs.getString(4));
      }
      if(flag==false) {
         System.out.println("No Records found");
      }
      con.close();
   }
}
Selecting particular Columns from the Database
     While retrieving Data from Database, we have to consider Order of Columns in the ResultSet but not in the Database Table.
     Database Table Columns Order and ResultSet Columns Order need not be same. We have to give Importance only for ResultSet Columns Order.
E.g 1: select * from employees;
     In this case Database Table contains 4 Columns and ResultSet also contains 4 Columns and Order is also same.
DataBase : (eno,ename,esal,eaddr)
ResultSet : (eno,ename,esal,eaddr)
while(rs.next()) { 
   System.out.println(rs.getInt(1)+"..."+rs.getString(2)+"..."+rs.getDouble(3)+".."+rs.getString(4)); 
}
E.g 2: select esal, eno, eaddr, ename from employees;
     In this case Database Table contains 4 Columns and ResultSet also contains 4 Columns, but Order is not same.
DataBase : (eno,ename,esal,eaddr)
ResultSet : (esal,eno,eaddr,ename)
while(rs.next()) { 
   System.out.println(rs.getDouble(1)+"..."+rs.getInt(2)+"..."+rs.getString(3)+".."+rs.getString(4)); 
}
E.g 3: select ename,eaddr from employees;
     In this case Database Table contains 4 Columns, but ResultSet contains 2 Columns.
DataBase : (eno,ename,esal,eaddr)
ResultSet : (ename,eaddr)
while(rs.next()) { 
   System.out.println(rs.getString(1)+"..."+rs.getString(2)); 
}

Next Tutorial : Aggregate Functions

Previous Tutorial : JDBC - Delete a record in the table

No comments:

Post a Comment