JDBC Program to Select and Display Records Using Preparedstatement

In the previous JDBC tutorial, we learned about PreparedStatement object and later we developed a Java program to insert records into the table and another program to update records of a table. Now, in this post, we will post, we will develop a Java program to select and display the record using the PreparedStatement object.

To fetch the records of a table we need ResultSet object and using the getXxx(-) method of ResultSet we can get the details of table.

We are working with the Oracle database, but you can work with any database with the required information. In the Oracle database, we have a product table which has the details of product id, product name, price of the product, and the quantity.

SQL> SELECT * FROM product;

PID   PNAME    PRICE   QUANTITY
----- -------- ------- ----------
 102  Bed      2000     1
2051  Fan       999     2
 111  Table    1500     1

SQL> DESC product;

Name      Null?    Type
--------- -------- ----------
PID       NOT NULL NUMBER(10)
PNAME              VARCHAR2(15)
PRICE              FLOAT(126)
QUANTITY           NUMBER(10)

We want to display the records of the product table. So, no need to take any input value from the end-user, and also no need to set query parameters.

The required SQL query is,

SQL> SELECT pid, pname, price, 
     quantity FROM product;

Note:- While developing the JDBC application it is not recommended to use * in the SQL query, so SELECT * FROM product is not recommended.

---

Java program to display table records

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

public class DisplayProductTable {

   // SQL query
   private static final String SELECT_PRODUCT_QUERY = 
       "SELECT PID, PNAME, PRICE, QUANTITY FROM PRODUCT";

   public static void main(String[] args ) {

      // declare variables
      Connection con = null;
      PreparedStatement ps = null;
      ResultSet rs = null;

      try {
         // establish the connection
         con = DriverManager.getConnection(
           "jdbc:oracle:thin:@localhost:1521:knowprogram",
           "scott", "tiger");

         // compile SQL query and 
         // store it in PreparedStatemet object
         if(con != null)
         ps = con.prepareStatement(SELECT_PRODUCT_QUERY);

         // execute the query
         if(ps != null) {
            rs = ps.executeQuery();
        }

        // process the result
        if(rs != null) {
           while(rs.next()) {
              System.out.println(""
                    + rs.getInt("PID") +" "
                    + rs.getString("PNAME") +" "
                    + rs.getFloat("PRICE") +" "
                    + rs.getInt("QUANTITY"));
           } 
        } 

        System.out.println("\nRecords displayed");

     } catch(SQLException se) {
        se.printStackTrace();
     } catch(Exception e) {
        e.printStackTrace();
     } // end of try-catch block 

     finally {
        // close JDBC objects
        try {
           if(ps != null) ps.close();
        } catch(SQLException se) {
           se.printStackTrace();
        }
        try {
           if(con != null) con.close();
        } catch(SQLException se) {
           se.printStackTrace();
        }
     }

   } //end of main
} //end of class

Output:-

102 Bed 2000.0 1
2051 Fan 999.0 2
111 Table 1500.0 1

Records displayed

Select and display record based on given value using PreparedStatement

Now let us develop another JDBC program that will select the row based on the given value and display only those row details.

Required SQL query,

SQL> SELECT pname, price, quantity 
     FROM product WHERE pid = 102;

PNAME   PRICE      QUANTITY
------- ---------- ----------
Bed     2000       1

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Scanner;

public class SelectTest {

   // SQL query
   private static final String SELECT_PRODUCT_QUERY = 
        "SELECT PNAME, PRICE, QUANTITY FROM PRODUCT"+
        " WHERE PID = ?";

   public static void main(String[] args ) {

      // declare variables
      Scanner scan = null;
      int pid = 0;
      Connection con = null;
      PreparedStatement ps = null;
      ResultSet rs = null;
      boolean flag = false;

      try {
    	 // read input
    	 scan = new Scanner(System.in);
    	 if(scan != null) {
    	    System.out.print("Enter product ID: ");
    	    pid = scan.nextInt();
    	 }

         // establish the connection
         con = DriverManager.getConnection(
            "jdbc:oracle:thin:@localhost:1521:knowprogram",
            "scott", "tiger");

         // compile SQL query and 
         // store it in PreparedStatemet object
         if(con != null)
         ps = con.prepareStatement(SELECT_PRODUCT_QUERY);

         // set input value to query parameter
         if(ps != null)
            ps.setInt(1, pid);

         // execute the query
         if(ps != null) 
            rs = ps.executeQuery();

         // process the result
         if(rs != null) {
           while(rs.next()) {
              flag = true;
              System.out.println(""
                 + rs.getString("PNAME") +" "
                 + rs.getFloat("PRICE") +" "
                 + rs.getInt("QUANTITY"));
           }
        }

        if(flag)
        System.out.println("Records fetched & displayed");
        else
        System.out.println("Records not found");

     } catch(SQLException se) {
        se.printStackTrace();
     } catch(Exception e) {
        e.printStackTrace();
     } // end of try-catch block 

     finally {
        // close JDBC objects
        try {
           if(ps != null) ps.close();
        } catch(SQLException se) {
           se.printStackTrace();
        }
        try {
           if(con != null) con.close();
        } catch(SQLException se) {
           se.printStackTrace();
        }
        try {
           if(scan != null) scan.close();
        } catch(Exception e) {
           e.printStackTrace();
        }
    }

  } //end of main
} //end of class

Output:-

Enter product ID: 102
Bed 2000.0 1
Records fetched & displayed

Enter product ID: 1000
Records not found

If you enjoyed this post, share it with your friends. Do you want to share more information about the topic discussed above or do you find anything incorrect? Let us know in the comments. Thank you!

Also See:-

• Different types of statements in JDBC
• JDBC PreparedStatement interface
• Statement vs PreParedStatement
• Insert record using PreparedStatement in Java
• Update record using Java PreparedStatement
• Select record using PreparedStatement in Java
• JDBC create table using statements
• Preparedstatement Example
• JDBC program to insert with Surrogate Key

Search