Java Database Connectivity (JDBC) is a Java-based data access technology that defines how a client may access a database. It provides methods for querying and updating data in a database. The JDBC classes are contained in the Java package java.sql and javax.sql.
In this JDBC Oracle connectivity example we will see how to setup a JDBC development environment and create a simple Java database application to connect to Oracle Database Express Edition using JDBC API. We will also see the following important things which are required for connecting to any database using JDBC.
Install Java
Make sure you have Java SE installed in you computer. Refer the links to install Java in Windows, Ubuntu.
Download and install Eclipse IDE
You can code and run a Java program using a simple text editor (such as Notepad) and use command prompt to run the program. Alternatively, you can use any Integrated Development Environment (IDE) (such as Eclipse, NetBeans, etc). We use Eclipse IDE.
Refer this link to install Eclipse IDE.
Download and Install Oracle Database Express Edition
This Oracle JDBC example requires Oracle Database XE (Express edition) which can be downloaded from the following link.
http://www.oracle.com/technetwork/products/express-edition/downloads/index.html.
Accept the license agreement and download the version for your platform. This tutorial assumes the platform as Windows.
Oracle XE Installation Steps
The Oracle XE installation is very simple. Just unzip the file and run the setup.exe file. Follow the step-by-step installation guide from this link.
Setup a Database
Create Database User
To create database objects, we must create at least one database user. A user is associated with a database schema, you connect to the database as a database user, and the database user is the owner of any database objects (tables, views etc) that you create in the schema associated with the user.
For example, to create a database user named ‘testuser’. Follow these steps, using the command line:
1. Open the SQL command prompt window. For example, on Windows, click Start, then Programs (or All Programs), then Oracle Database Express Edition, and then “Run SQL Command Line”.
2. Connect as the SYSTEM user:
Type:
connect
Enter user-name: system
Enter password:
The password is the one you entered during installation.
3. Create the user. For example, enter a statement in the following form:
SQL> create user testuser identified by <password-for-testuser>;
4. Grant the user the necessary privileges. For example:
SQL> grant connect, resource to testuser;
5. exit
Create Table
Now let us login to the database with the newly created user ‘testuser’ and create a simple ‘Person’ table.
1. Open SQL Command Line.
2. Type “connect”
3. Enter username as “testuser”
4. Enter password as “testpass” (or the password you entered in the previous step while creating the user)
5. Create a person table.
SQL> create table person(pid integer primary key, name varchar2(50));
6. Enter a few data into the table.
SQL> insert into person values(1, ‘Ram’);
SQL> insert into person values(2, ‘Sam’);
SQL> insert into person values(3, ‘John’);
SQL> select * from person;
SQL> commit;
SQL> exit;
Now the database and table setup is done. Let us connect to this database table and retrieve the data using JDBC API.
Java Oracle Connector
JDBC API mostly consists of interfaces which work independently of any database. A database specific driver is required for each database which implements the JDBC API.
The JDBC database Connector provides access to the database. To reach the database using JDBC we need a JDBC driver from the database provider in our case – Oracle. This connector is typically delivered with the product in a jar or zip file or available in the provider’s website. These files must be in our classpath (which is explained later under Configure JDBC Driver in Eclipse) otherwise we will get some class-not-found-exceptions indicating that the driver was not found on the classpath.
Oracle provides the JDBC connector jar with the product and is available in the following location in Windows,
C:\oraclexe\app\oracle\product\11.2.0\server\jdbc\lib
(if you followed the default installation procedure)
There will be multiple jars like,
ojdbc5.jar – Classes for use with JDK 1.5.
ojdbc6.jar – Classes for use with JDK 1.6.
We will be using “ojdbc6.jar”
Create a Java project in Eclipse IDE
- Open Eclipse IDE.
- Create a new Java Project and name it as JDBCOracle. If you are a newbie, refer this link on getting started with Java and Eclipse.
JDBC Oracle Connectivity
In order to establish a connection to the database using JDBC we need to perform the following steps,
- Import the required interfaces/classes from java.sql package.
- Load the JDBC Oracle Driver class
- Establish the connection by providing the jdbc oracle connection string url
Load Oracle Java driver
We need to know and specify which of the classes in the connector jar implements the JDBC driver so as to load the class in memory. For Oracle the class oracle.jdbc.driver.OracleDriver is the jdbc driver class. The statement
Class.forName (“oracle.jdbc.driver.OracleDriver”)
loads the driver class in memory.
JDBC Oracle Connection URL
We connect to Oracle database from Java using DriverManager class by calling DriverManager.getConnection() method. This method requires JDBC Oracle connection URL string, Oracle database username and password.
The Java database connection string URL is of the following format:
jdbc:oracle:
:[username/password]@[//]host_name[:port][/XE]
In this URL:
// is optional.
:port is optional.Specify this only if the default Oracle Net listener port (1521) is not used.
/XE, or the service name, is not required.
The connection adapter for the Oracle Database XE Client connects to the default service on the host.
Default service is a new feature of Oracle Database XE. If you use any other Oracle Database client to connect to Oracle Database XE, then you must specify the service name.
For example, if you connect to a local database using default port number, then the Oracle database connection URL is:
jdbc:oracle:thin:testuser/testpass@localhost
Java Application code
An application involving Java with database to process any SQL statement must follow these steps:
- Establish a connection. (This is done by DriverManager class)
- Create a Statement object. (Line 17)
- Execute the query. (Line 18)
- Process the ResultSet object. This is required only for SELECT SQL query. (Line 19-22)
- Close the connection. (Line 29-31)
To do the above steps create a package “com.theopentutorials.jdb.oracle”. Then create a class “TestOracleJDBC” with main method and copy the following code.
package com.theopentutorials.jdb.oracle;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class TestOracleJDBC {
public static void main(String[] args) {
Connection con = null;
Statement stmt = null;
ResultSet rs = null;
try {
Class.forName("oracle.jdbc.driver.OracleDriver");
con = DriverManager.getConnection("jdbc:oracle:thin:testuser/testpass@localhost");
stmt = con.createStatement();
rs = stmt.executeQuery("SELECT * FROM person");
while(rs.next()) {
System.out.print(rs.getInt(1) + "\t");
System.out.println(rs.getString(2));
}
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
} finally {
try {
rs.close();
stmt.close();
con.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
Configure JDBC driver in Eclipse IDE
If you run the above class you will get a runtime exception mentioning Driver class not found as shown below
java.lang.ClassNotFoundException: oracle.jdbc.driver.OracleDriver
Because we need to add the Java Oracle Connector JAR in project’s classpath. To do this, right click on your Java Project -> Properties -> Buildpath -> Libraries -> Add External JAR and select the odbc6.jar file.
Output
Run the above program to get the following output.
JDBC Oracle Application folder structure
