Dmitriy Sukharev. IT Blog

In this article I want to show you how to use Oracle Database via JDBC. I'm going to describe the easy way of connection to database, creation of table, and fetching, inserting, updating and deleting rows in created table (CRUD).

What do you need for this?

First of all you need an Oracle Database. I have 11g Express Edition version, which is free, so this text isn't only for very rich people paying a lot of money for their software, and so you can execute all steps from this little tutorial.

Let's launch browser and get all we need:

1. Oracle Database Express Edition you can get here
   Install it, and memorize password to have access to database, it will be used in the future.
   Here is a good manual how to install Oracle DB 11g XE in Ubuntu/Mint.
   Here is a good manual how to install Oracle DB 11g XE in CentOS.
   For Windows installation is relatively easy.
2. Also you need for special JDBC driver to link Java and Oracle DB. You can get it here
   If you use IDE include jar-archive into projects worked with DBMS.

Wikipedia says

JDBC (Java DataBase Connectivity) is an API for the Java programming language that defines how a client may access a database. It provides methods for querying and updating data in a database. JDBC is oriented towards relational databases.

A JDBC driver is a software component enabling a Java application to interact with a database.

In short, JDBC gives your applications an opportunity to make queries to database. Driver is usually a library for interaction with concrete DBMS.

First job for your fingers

Now you have all you need to write your the first program interacted with database.

First of all I propose to create an interface including methods for interaction with some DBMS. My variant is following:

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;

/**
 * Interface for interacting with database via JDBC driver.
 *
 * @author Sukharev Dmitriy
 *
 */
public interface DatabaseManager {
    /**
     * Loads JDBC driver.
     */
    public void loadDriver() throws ClassNotFoundException;

    /**
     * Connects to database.
     */
    public Connection connectToDB(String database) throws SQLException;

    /**
     * Disconnects from database.
     */
    public void disconnectFromDB(Connection connection)
            throws SQLException;

    /**
     * Executes query to database: create table.
     */
    public void createTable(Connection connection, String tablename)
            throws SQLException;

    /**
     * Executes query to database: fetch rows from database.
     */
    public ResultSet fetch(Connection connection, String tablename)
            throws SQLException;

    /**
     * Prints result of fetching to console.
     */
    public void printFetched(ResultSet resultSet) throws SQLException;

    /**
     * Executes query to database: insert row to database.
     */
    public void insert(Connection connection, String tablename) throws SQLException;

    /**
     * Executes query to database: update rows of table.
     */
    public void update(Connection connection, String tablename) throws SQLException;

    /**
     * Executes query to database: delete rows from table.
     */
    public void delete(Connection connection, String tablename) throws SQLException;
}

Now let's create class OracleDBManager which will implement previous interface. To have opportunity to compile this class before implementing all methods I propose to comment in interface all methods which are not realized and uncomment them after realizing one by one or realize empty methods.

Load Driver

First of all JDBC driver has to be loaded. There are several ways how-to do it, but here you'll see only one :). In the just created class OracleDBManager type following lines:

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;

    /**
     * Loads Oracle JDBC driver.
     * 
     * @throws ClassNotFoundException
     */
    public void loadDriver() throws ClassNotFoundException {
        Class.forName("oracle.jdbc.driver.OracleDriver");
    }

What these several lines actually do?

It's simple. Class.forName() is used for creation instance by name. This method throws exception if specified class wasn't found.

Why "oracle.jdbc.driver.OracleDriver"? It's also easy. If you open your Oracle's JDBC driver jar-archive (e.g. in 7zip or WinRAR) you'll find inside path oracle/jdbc/driver/ and class-file OracleDriver.class, so you are just loading this class :).

You need to load driver only once in application life-cycle. Actually you'll simply put launch of this method before all requests to database in application and won't remember about them after.

I hope now all is clear.

Create also class Main which consists of method main(). Realize it in the following way:

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;


/**
 * Entry point of the application which shows how-to use JDBC driver
 * for interacting with MySQL and Oracle DB databases.
 *
 * @author Sukharev Dmitriy
 *
 */
public class Main {

    /**
     * Uses entity of DatabasaManager interface for interacting with
     * MySQL or Oracle DB database using JDBC driver.
     *
     * @param args
     */
    public static void main(String[] args) {
        DatabaseManager dbManager = new OracleDBManager();

        try {
            dbManager.loadDriver();
            System.out.println("Driver was loaded.");
        } catch (ClassNotFoundException e) {
            System.err.println("Driver wasn't loaded.");
            e.printStackTrace();
        }
    }
}

Compile and run it. Don't forget about driver's jar-archive which must be included.

If all is good you'll see "Driver was loaded" message, otherwise check if you didn't misspell and really included driver's jar-archive.

I believe that everything is OK now ;)

Connect to Dababase

Let's create connection to our database. Supplement your OracleDBManager class with following:

import java.sql.DriverManager;

private String user = "system"; // It's the main user of Oracle DB who had all
                                // privileges. It's study project so we can do
                                // this. But don't use system user in the real
                                // projects.
private String pw = "pw";       // Put here password you set for main user
                                // while installation.

/**
 * Connects to OracleDB.
 */
public Connection connectToDB(String database) throws SQLException {
    String url = "jdbc:oracle:thin:@//localhost:1521/" + database;
    Connection connection = DriverManager.getConnection(url, user, pw);

    return connection;
}

You can see that you need for three strings to get connection: user, pw and url. I think you got meaning of the first two ones, but last is a little bit complex.

As far as you can see it looks like url in your browser with some little differences. The part before "//" is like "http:" in your browser. While you're using JDBC it always (constantly) starts from "jdbc" after which and before "//" is part that depends on your database and driver, you don't have to know about this more. The part after is like domain and subdomain, here you set name of host, port (but AFAIU it's not necessery for all BDMS and drivers if port is default), and name of database to which you wanna be connected.

Also pay your attention to the class DriverManager. It works with drivers, connections and tracing. Here we're using static method getConnection() returned instance of Connection class if specified url relates with a driver.

Now you can add launch of connectToDB() in your main() method surrounding it with try/catch block.

I remind that as you use Express Edition of Oracle DB you have only one database named "XE". So you don't need to think what database name to choose. Otherwise you have to declare an existing name of some database.

Disconnect from Database

After creation any connection have to be closed. For this aim there is exist method close() for any Connection instance. Just add this method to your Manager class:

/**
 * Disconnects from OracleDB.
 */
public void disconnectFromDB(Connection connection)
        throws SQLException {
    connection.close();
}

It's reasonable to put launch of this method in finally-block where connection is in try-block in main() method.

Hereinafter I'll not describe main() method, just do it in your own way. In the end of article you can find link to the full source file set of current project.

Create Table

Let's create new table inside our database. To do this you have to write real Oracle DB query, even three. It's the most complex part of this article (at least if you don't know Oracle DB syntax). Now write following code:

import java.sql.Statement;


/**
 * Executes query to OracleDB: create table. Last two queries are for
 * auto-increment of id-field.
 */
public void createTable(Connection connection, String tablename)
        throws SQLException {
    Statement statement = connection.createStatement();
    statement.executeUpdate("CREATE TABLE " + tablename + " ("
            + " id NUMBER(11) NOT NULL PRIMARY KEY,"
            + " title VARCHAR2(100) NOT NULL,"
            + " comments VARCHAR2(500) NULL," + " price FLOAT NULL" + " )");

    statement.executeUpdate("CREATE SEQUENCE " + tablename + "_ID_SEQ"
            + " START WITH 1" + " INCREMENT BY 1" + " NOMAXVALUE");

    statement.executeUpdate("CREATE OR REPLACE TRIGGER " + tablename
            + "_ID_TRIGGER" + " BEFORE INSERT ON " + tablename
            + " REFERENCING NEW AS NEW OLD AS OLD FOR EACH ROW" + " BEGIN"
            + " SELECT " + tablename
            + "_ID_SEQ.NEXTVAL INTO :NEW.ID FROM DUAL; " + " END;");
}

In the first line of the method we're getting Statement instance which is an operation system resource. Javadocs say about this: "The object used for executing a static SQL statement and returning the results it produces."

There are two types of SQL statements. The first one just gets information from database and doesn't change it. Use executeQuery() method of Statement instance for such statements. The second type used for changing database. Use executeUpdate() method for them.

So the last part of method includes three executeUpdate methods. It's reasonable because we change database by creating a new table, but why the whole three statements??? Actually we could create method without two of them, but so far as we determine id as primary key (I hope you know what these words mean) we have to provide unique id-values. There isn't auto_increment key-word in Oracle DB therefore to provide autoincrement of id-value for every new row in the table (and ensure unique id-values) we use another two statements.

We use "CREATE SEQUENCE" statement to create autoincrement per se. And we use "CREATE TRIGGER" statement to link operation of adding (inserting) new row to specified table with our sequence for id-field. As result when we insert new row we don't need to specify id-value, it'll be generated automatically.

By the way, don't duplicate my mistakes. Don't forget about semicolons in the TRIGGER statement.

Fetch

To fetch information from your table(es), type:

import java.sql.ResultSet;

/**
 * Executes query to OracleDB: fetch rows from database.
 * 
 * @param connection
 * @param tablename
 * @return
 * @throws SQLException
 */
public ResultSet fetch(Connection connection, String tablename)
        throws SQLException {
    ResultSet resultSet = null;
    Statement statement = connection.createStatement();
    resultSet = statement.executeQuery("SELECT * FROM " + tablename);
    return resultSet;
}

Here we're using executeQuery method because we don't change information, we only get it. This method returns instance of class ResultSet which includes all rows fetched by statement.

How can you get information from ResultSet instance? Very easy! See example:

/**
 * Prints fetched data to console.
 */
public void printFetched(ResultSet resultSet) throws SQLException {
    while (resultSet.next()) {
        String id = resultSet.getString("id");
        String title = resultSet.getString("title");
        double price = resultSet.getDouble("price");
        System.out.println(id + "\t" + title + "\t" + price);
    }
}

ResultSet has get-methods not only for String and doulbe values, but for many others as well. Also this methods can receive interger arguments to get the first (1), second (2), etc. column. For instance you can change first getString method to:

String id = resultSet.getString(1);

Insert

If you launched previous method I think you got nothing. It's quite reasonable 'cause there are no rows in your table. To add new row to your table launch following:

/**
 * Executes query to OracleDB: insert row to database.
 * 
 * @param connection
 * @param tablename
 * @return
 * @throws SQLException
 */
public void insert(Connection connection, String tablename)
        throws SQLException {
    Statement statement = connection.createStatement();
    statement.executeUpdate("INSERT INTO " + tablename
            + " (title, comments, price)"
            + " VALUES('title', 'comment', '2')");
}

After you executes this you'll get new record in your table. So printFetched() method won't print empty row-set :)

Update

To change specified rows you can use UPDATE statement:

/**
 * Executes query to OracleDB: update row of table.
 * 
 * @param connection
 * @param tablename
 * @return
 * @throws SQLException
 */
public void update(Connection connection, String tablename)
        throws SQLException {
    Statement statement = connection.createStatement();
    statement.executeUpdate("UPDATE " + tablename
                    + " SET title='title', comments='comment',"
                    + " price='2.7' WHERE id='3'");
}

Delete

To delete specified rows use DELETE statement:

/**
 * Executes query to OracleDB: delete row from table.
 * 
 * @param connection
 * @param tablename
 * @return
 * @throws SQLException
 */
public void delete(Connection connection, String tablename)
        throws SQLException {
    Statement statement = connection.createStatement();
    statement.executeUpdate("DELETE FROM " + tablename + " WHERE id='1'");
}

Source Code

You can get source code of this project here

In archive is also class MySqlManager therefore you can get acquaint with JDBC for MySQL usage and compare MySqlManager and OracleDBManager classes.