Oracle JDBC Test

How to test by adding the Oracle JDBC driver to your classpath.

Java JDBC programming requires a JDBC driver for the database. You can find the Oracle JDBC driver in C:\oraclexe\app\oracle\product\11.2.0\server\jdbc\lib. Among the files in the directory, ojdbc6.jar is the JDBC driver we will use. The following JDBC test file is a program that retrieves all the records in the scott account's EMP table and prints them. Save the test file as GetEmp.java in the C:\jdbcTest directory.

GetEmp.java
import java.sql.*; 

public class GetEmp {
	public static void main(String[] args) {
		String DB_URL = "jdbc:oracle:thin:@127.0.0.1:1521:XE";
		String DB_USER = "scott";
		String DB_PASSWORD = "tiger";

		Connection conn = null;
		Statement stmt = null;
		ResultSet rs = null;

		String query = "SELECT * FROM emp";
		
		try {
			Class.forName("oracle.jdbc.driver.OracleDriver");
		} catch (ClassNotFoundException e ) {
			e.printStackTrace();
		}

		try {
			conn = DriverManager.getConnection(DB_URL, DB_USER, DB_PASSWORD);
			stmt = conn.createStatement();
			rs = stmt.executeQuery(query);

			while (rs.next()) { 
				String empno = rs.getString(1);
				String ename = rs.getString(2);
				String job = rs.getString(3);
				String mgr = rs.getString(4);
				String hiredate = rs.getString(5);
				String sal = rs.getString(6);
				String comm = rs.getString(7);
				String depno = rs.getString(8);

				System.out.println(empno + " : " + ename + " : " + job + " : " + mgr + " : " + hiredate + " : " + sal + " : " + comm + " : " + depno); 
			}
			
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				rs.close();
				stmt.close();
				conn.close();
			} catch (SQLException e) {}
		}
	}
}

Compile and run.

C:\ Command Prompt
C:\jdbcTest>set classpath=.;C:/oraclexe/app/oracle/product/11.2.0/server/jdbc/lib/ojdbc6.jar

C:\jdbcTest>javac GetEmp.java

C:\jdbcTest>java GetEmp
7369 : SMITH : CLERK : 7902 : 1980-12-17 00:00:00.0 : 800 : null : 20
7499 : ALLEN : SALESMAN : 7698 : 1981-02-20 00:00:00.0 : 1600 : 300 : 30
7521 : WARD : SALESMAN : 7698 : 1981-02-22 00:00:00.0 : 1250 : 500 : 30
7566 : JONES : MANAGER : 7839 : 1981-04-02 00:00:00.0 : 2975 : null : 20
7654 : MARTIN : SALESMAN : 7698 : 1981-09-28 00:00:00.0 : 1250 : 1400 : 30
7698 : BLAKE : MANAGER : 7839 : 1981-05-01 00:00:00.0 : 2850 : null : 30
7782 : CLARK : MANAGER : 7839 : 1981-06-09 00:00:00.0 : 2450 : null : 10
7839 : KING : PRESIDENT : null : 1981-11-17 00:00:00.0 : 5000 : null : 10
7844 : TURNER : SALESMAN : 7698 : 1981-09-08 00:00:00.0 : 1500 : 0 : 30
7900 : JAMES : CLERK : 7698 : 1981-12-03 00:00:00.0 : 950 : null : 30
7902 : FORD : ANALYST : 7566 : 1981-12-03 00:00:00.0 : 3000 : null : 20
7934 : MILLER : CLERK : 7782 : 1982-01-23 00:00:00.0 : 1300 : null : 10
String DB_URL = "jdbc:oracle:thin:@127.0.0.1:1521:XE"
jdbc:oracle:thin: Thin type JDBC driver
1521: The port number of the Oracle listener
The listener bridges Oracle with external applications.
127.0.0.1: It is the IP of the server where the database is installed.
XE: It is the SID name. SID identifies Oracle database objects.

Test Failure Checklist

  1. Exception in thread "main" java.lang.NoClassDefFoundError: GetEmp
    • It is an exception that the GetEmp class can not be found. Verify that you ran it where the GetEmp.class file is located.
    • Make sure the classpath contains the current directory (.).
  2. java.lang.ClassNotFoundException: oracle.jdbc.driver.OracleDriver
    • It is an exception that the startup class of the Oracle JDBC driver can not be found. Make sure you have added the Oracle JDBC driver to your CLASSPATH.
  3. Java.sql.SQLException: IO Exception: The Network Adapter could not establish the connection
    • There is a possibility that Oracle's Listener is not in service.
    • At the command prompt, run lsnrctl status command to check the status of your service.
  4. java.sql.SQLException: ORA-01017: invalid username/password; logon denied
    • The password for the scott account is wrong. If scott's password is not tiger, modify the GetEmp.java source and test again.

How to test with the -classpath option of javac and java

Here's how to use the -classpath option when compiling and running.

C:\ Command Prompt
C:\jdbcTest>javac -classpath C:/oraclexe/app/oracle/product/11.2.0/server/jdbc/lib/ojdbc6.jar GetEmp.java

C:\jdbcTest>java -classpath .;C:/oraclexe/app/oracle/product/11.2.0/server/jdbc/lib/ojdbc6.jar GetEmp
7369 : SMITH : CLERK : 7902 : 1980-12-17 00:00:00.0 : 800 : null : 20
7499 : ALLEN : SALESMAN : 7698 : 1981-02-20 00:00:00.0 : 1600 : 300 : 30
7521 : WARD : SALESMAN : 7698 : 1981-02-22 00:00:00.0 : 1250 : 500 : 30
7566 : JONES : MANAGER : 7839 : 1981-04-02 00:00:00.0 : 2975 : null : 20
7654 : MARTIN : SALESMAN : 7698 : 1981-09-28 00:00:00.0 : 1250 : 1400 : 30
7698 : BLAKE : MANAGER : 7839 : 1981-05-01 00:00:00.0 : 2850 : null : 30
7782 : CLARK : MANAGER : 7839 : 1981-06-09 00:00:00.0 : 2450 : null : 10
7839 : KING : PRESIDENT : null : 1981-11-17 00:00:00.0 : 5000 : null : 10
7844 : TURNER : SALESMAN : 7698 : 1981-09-08 00:00:00.0 : 1500 : 0 : 30
7900 : JAMES : CLERK : 7698 : 1981-12-03 00:00:00.0 : 950 : null : 30
7902 : FORD : ANALYST : 7566 : 1981-12-03 00:00:00.0 : 3000 : null : 20
7934 : MILLER : CLERK : 7782 : 1982-01-23 00:00:00.0 : 1300 : null : 10

You can easily test it by creating the following batch file.

JDBCTEST.bat
@echo off

java -classpath C:/jdbcTest;C:/oraclexe/app/oracle/product/11.2.0/server/jdbc/lib/ojdbc6.jar GetEmp

pause

Test GetEmp.java in Eclipse

Eclipse does not refer to the classpath environment variable. To run GetEmp.java code in Eclipse, Eclipse manages your code on a project-by-project basis, so you need to create a project once. Create a project named jdbc as shown below, and create GetEmp.java to belong to a package called net.java_school.jdbc. Select the project with the mouse.
GetEmp.java on jdbc project
Click the right button and select the menu as shown below.
jdbc project selection - mouse right click - Build Path - Configure Build Path selection
Select the Libraries tab and click the Add External JARs .. button.
Libraries tab - Add External Lib...
Add the Oracle JDBC driver file.
Add ojdbc6.jar
Confirm ojdbc6.jar
In the Package Explorer view, select GetEmp.java with the mouse. Click the right button and execute as below. How to run GetEmp.java in Eclipse