Oracle JDBC Test

Adding Oracle JDBC driver to your classpath

Java JDBC programming requires a JDBC driver for a 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. Save the test file as GetEmp.java in the C:\Users\java\jdbc 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:\Users\java\jdbc>set classpath=.;C:/oraclexe/app/oracle/product/11.2.0/server/jdbc/lib/ojdbc6.jar

C:\Users\java\jdbc>javac GetEmp.java

C:\Users\java\jdbc>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 exists.
XE: It is the SID name. SID identifies Oracle database objects.

Test Failure Checklist

  1. Exception in thread "main" java.lang.NoClassDefFoundError: GetEmp
    • The message says that the class loader cannot find the GetEmp.
    • Confirm that you ran it where the GetEmp.class file is located, and the classpath contains the current directory (.).
  2. java.lang.ClassNotFoundException: oracle.jdbc.driver.OracleDriver
    • The message says that the class loader cannot the startup class of the Oracle JDBC driver.
    • 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
    • The message says that 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 the Listener service.
  4. java.sql.SQLException: ORA-01017: invalid username/password; logon denied
    • The message says that 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

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

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

C:\Users\java\jdbc>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 test it by creating the following batch file.

JDBCTEST.bat
@echo off

java -classpath C:/Users/java/jdbc;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 in Eclipse, Eclipse manages your code on a project-by-project basis, so you need to create a project once. Create the project 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 like 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