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.

JDK Install

https://www.testingdocs.com/download-and-installing-java-jdk/

Eclipse Install

https://www.testingdocs.com/install-eclipse-ide-on-windows-11/

Oracle Install

https://www.testingdocs.com/install-oracle-19c-database-on-windows-10/

Scott Schema Setup

https://www.testingdocs.com/setting-up-sample-schema-in-oracle/

JDBC Driver Setup

https://www.testingdocs.com/configure-jdbc-driver-in-java-project/

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

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.

—

Java Tutorials

Java Tutorial on this website:

https://www.testingdocs.com/java-tutorial/

For more information on Java, visit the official website :

https://www.oracle.com/java/

More information about Oracle Database:

https://www.oracle.com/database/