Display data from Oracle Database table using JDBC

Overview

In this tutorial, we will learn to retrieve and display data from an Oracle Database table using JDBC API in Java programming language.

Steps

There are several configuration steps that need to be done for the JDBC program to work.

JDK should be installed
Java Code editor – Eclipse IDE/IntelliJ/NetBeans is required for seamless Java development. The tutorial uses Eclipse IDE.
Oracle Database
SCOTT schema setup – The tutorial uses dept Oracle database table from the starter SCOTT schema.
JDBC Driver Configuration in the IDE.

Java JDBC Program

Sample JDBC program that connects to an Oracle database and retrieves all the rows in SCOTT.dept table.

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;

/*********************************************
 * Display Oracle Database Table
 * Java Tutorials - www.TestingDocs.com
 * 
 *********************************************/

public class DisplayOracleTable {
 public static void main (String args[]) {
 //Connection descriptor variables
 String driverName = "oracle.jdbc.driver.OracleDriver";
 String hostName = "localhost";
 String portNumber = "1521";
 String serviceName = "orclpdb.localdomain";
 String username="scott";
 String password="tiger";
 String url = "jdbc:oracle:thin:@//" + hostName + ":" + 
 portNumber + "/" + serviceName ;
 //Oracle SQL SELECT query
 String sqlSelectQuery = "SELECT * FROM dept";
 Connection connection =null;
 try {
 // Load the Oracle JDBC Thin driver
 Class.forName(driverName);
 
 // Connection to the Oracle Database
 connection = DriverManager.getConnection(url, username, password); 
 
 Statement stmt = connection.createStatement();
 ResultSet rs = stmt.executeQuery(sqlSelectQuery);
 //Print Table Header
 System.out.println(String.format("%-20s", "DEPTNO")
 + String.format("%-20s", "DNAME")
 + String.format("%-20s", "LOC"));
 //Print Database Table rows in while Loop
 while (rs.next()) {
 int deptno = rs.getInt(1); 
 String dname= rs.getString(2);
 String loc = rs.getString(3);
 System.out.println(String.format("%-20s", deptno) 
 + String.format("%-20s", dname) 
 + String.format("%-20s", loc));
 }
 //Close Database Connection
 stmt.close();
 } catch (Exception exp) {
 System.out.println("Exception : "
 +exp.getMessage());
 }
 }
}

Screenshot

Display Oracle Database Table JDBC

Sample Output

Run the program from the IDE. Run as ->> Java Application

Sample program output.

DEPTNO DNAME LOC 
1000 ACCOUNTING HOUSTON 
2000 RESEARCH DALLAS 
3000 SALES CHICAGO 
4000 OPERATIONS BOSTON

That’s it. We have successfully connected to the database, executed SELECT SQL query, retrieved the table and displayed the table rows on the console output window.

—