Accessing the Oracle Database with Java Cardiff School of Computer Science & Informatics Documentation & Facilities Toggle navigation Home Documentation Software Services Contact Accessing the Oracle Database with Java Oracle database schemas can be accessed from Java application programs and applets. For information about the Oracle database management system in the School, and to learn about your Oracle account and schema on it, see Oracle in the School of Computer Science & Informatics Oracle from Java with JDBC Oracle JDBC includes two drivers that can be used to connect Java programs to the Oracle database. The thick, OCI, driver is the more scalable of the two. The OCI driver needs to use Oracle database client libraries. This means that it will work only on systems that have an Oracle package that includes the Oracle Call Interface (OCI) libraries installed on them. The Thin driver is written entirely in Java. It can be used for Java client applications run on any system – it does not require any Oracle client installation. It is particularly suited to applets which are usually run on remote systems by web browsers. It is not necessary for Oracle client software to be installed on the remote system. The drivers are in a Java jar file ojdbc6.jar. This file is installed on the School's Laboratory systems as /opt/oracle/instantclient/ojdbc6.jar on Linux C:\Program Files\instantclient_11__1\ojdbc6.jar on Windows. and you can also download it from here The Thin Driver Oracle supplies an example program which uses the Thin JDBC Driver. A modified version, ThinApplet.java, which uses our Oracle sample tables, can be downloaded here Thin Driver Connection String The format of the connection string, used to specify the connection to the Oracle server is
jdbc:oracle:thin:username/password@csoracle.cs.cf.ac.uk:1521:csora12edu.cs.cf.ac.uk
where username and password are your School Oracle database account name and password. Compiling with the Thin Driver Copy the ThinApplet.java and the ojdbc6.jar files to your working directory. Edit ThinApplet.java and put your Oracle user name and password in place of username/password in the definition of the connect_string String variable. Compile the applet with javac and include ojdbc6.jar in the classpath.
javac -cp ojdbc6.jar ThinApplet.java
Viewing with the Appletviewer You can use the appletviewer to run and view your Applet. The appletviewer needs an HTML file that it uses to invoke the Applet. Download ThinApplet.htm from here You will also need file dot.java.policy which you can get here The dot.java.policy file is needed to grant permissions on your workstation for the applet to connect to the Oracle server.
// Permissions for current directory "." to allow Applets to connect to Oracle
grant codeBase "file:./*" {
permission java.net.SocketPermission "csoracle.cs.cf.ac.uk:1024-",
"connect, accept,resolve";
permission java.util.PropertyPermission "oracle.jdbc.*", "read";
permission java.util.PropertyPermission "oracle.net.*", "read";
permission java.util.PropertyPermission "javax.net.*", "read";
permission java.util.PropertyPermission "user.name", "read";
permission javax.management.MBeanServerPermission "newMBeanServer";
permission javax.management.MBeanPermission "*", "registerMBean";
permission javax.management.MBeanTrustPermission "register";
permission java.lang.management.ManagementPermission "monitor";
permission javax.management.MBeanServerPermission "createMBeanServer";
};
Place the file in your home directory (/home/username) on Linux and rename it .java.policy. On Windows place it in the same directory as the Java applet. You can run the appletviewer from the command line. On Linux, simply appletviewer ThinApplet.htm Click on Execute Query to connect to Oracle and run the query. In a CMD window on Windows, modify the command to refer to the dot.java.policy file: appletviewer -J-Djava.security.policy=dot.java.policy ThinApplet.htm Applets can also be viewed through any web browser that allows the use of a Java plugin. But this requires further security settings that are beyond the scope of this Note. The OCI Driver Oracle has example programs which use the OCI JDBC driver. A modified version of OCIConnectionPool.java, which uses our Oracle sample tables, is available here . Compiling and Running JDBC OCI on Linux Copy OCIConnectionPool.java and download its Makefile from here . into your working directory. Edit OCIConnectionPool.java to change each occurrence of username and password to your Oracle user name and password. They are in line 42 in the OracleOCIConnectionPool command,
OracleOCIConnectionPool cpool = new OracleOCIConnectionPool
("username", "password", url, null);
and in lines 52 and 73 in the getConnection commands. The Makefile contains commands for compiling and running the application. Just type make and you will see it execute javac and java with appropriate settings for the classpath to include Oracle's JDBC Jar class file ojdbc6.jar. When the application runs, you should see output like...
-- The default configuration for the OracleOCIConnectionPool --
Min poolsize Limit: 1
Max poolsize Limit: 1
Connection Increment: 0
NoWait: false
Timeout: 0
PoolSize: 1
ActiveSize: 0
-- Use the connection from the OracleOCIConnectionPool --
Charles Dickens
Dafydd ApGwilym
Emile Zola
Thomas Mann
Robert Stevenson
-- Use another connection from the OracleOCIConnectionPool --
XXXXXXX
-- The new configuration for the OracleOCIConnectionPool --
Min poolsize Limit: 1
Max poolsize Limit: 2
Connection Increment: 1
NoWait: false
Timeout: 0
PoolSize: 1
ActiveSize: 0
Compiling and Running JDBC OCI on Windows Copy OCIConnectionPool.java to your working directory. Edit OCIConnectionPool.java to change username and password to your Oracle user name and password in line 42 in the OracleOCIConnectionPool command,
OracleOCIConnectionPool cpool = new OracleOCIConnectionPool
("username", "password", url, null);
and in lines 52 and 73 in the getConnection commands. To compile and run programs with the Oracle JDBC OCI driver, you must set up a CLASSPATH environment variable. You can do this in a Command Prompt (cmd) window as follows:
set CLASSPATH=.;%ProgramFiles%\instantclient_11_1\ojdbc6.jar
Now you can compile and run the OracleOCIConnectionPool program in the command window. To compile the program use javac OCIConnectionPool.java Running the Application The connection string for JDBC OCI is jdbc:oracle:oci:@csora12edu. The user name and password are already inside the program (we have just edited them in). To run the application, specify the connection string as a constant called JDBC_URL. The command should be
java "-DJDBC_URL=jdbc:oracle:oci:@csora12edu" OCIConnectionPool
Overview Oracle Accounts and Schemas Oracle from Java with JDBC The Thin Driver Thin Driver Connection String Compiling with the Thin Driver Viewing with the Appletviewer The OCI Driver Compiling and Running JDBC OCI on Linux Compiling and Running JDBC OCI on Windows Running the Application Back to top Tags database java jdbc oracle programming sql Tags database java jdbc oracle programming sql Cardiff University is a member of the Russell Group of Universities Cardiff University is a registered charity No. 1136855 Copyright © Cardiff University Accessibility Feedback Cookies Privacy Policy Terms of Use
