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 PromptC:\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
- 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 (.).
- 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.
- 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.
- 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 PromptC:\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 belongs to a package called net.java_school.jdbc.
Select the project with the mouse.
Click the right button and select the menu like below.
Select the Libraries tab and click the Add External JARs button.
Add the Oracle JDBC driver file.
In the Package Explorer view, select GetEmp.java with the mouse.
Click the right button and execute as below.
