Oracle Database Connection With Examples

To achieve this, JDBC specifications are agreed upon within the Java community–and each database vendor is then left to implement these specification to work with their product.

Oracle Drivers

Oracle provided two types of Drives.

1. Thin
2. OCI

OCI

  The OCI (Type II) driver consists of java wrappers to the low-level Oracle Call Interface (OCI) libraries used by utilities like SQL*Plus to access the database server. The OCI driver offers potentially better performance that the thin driver. It however requires the OCI libraries to be installed on the local machine.

Thin

  The Oracle JDBC Thin driver is a 100% pure Java, Type IV driver. It is targeted for Oracle JDBC applets but can be used for applications as well.The Thin driver communicates with the server using TTC, a protocol developed by Oracle to access the Oracle Relational Database Management System (RDBMS).The JDBC Thin driver allows a direct connection to the database by providing an implementation of TCP/IP that emulates Oracle Net and TTC (the wire protocol used by OCI) on top of Java sockets. Both of these protocols are lightweight implementation versions of their counterparts on the server. The Oracle Net protocol runs over TCP/IP only.

Loading a JDBC Driver

...
try{
   Class.forName("com.mysql.jdbc.Driver");
}catch(ClassNotFoundException exc){
   exc.printStackTrace();
}

Loading a JDBC Driver

...
Connection connection_;
...
try{
   connection_ = DriverManager.getConnection("jdbc:mysql://" + host + "/" + db, username, password);
}catch(SQLException exc){
   exc.printStackTrace();
}

Creating a JDBC Statement

...
try{
   Statement statement = connection_.createStatement();
}catch(SQLException exc){
   exc.printStackTrace();
}

Inserting Data into a Relation

...
try{
   String insert_sql_stmt = "INSERT INTO " + table + " VALUES(" + values + ")";
   int row = statement.executeUpdate(insert_sql_stmt, Statement.RETURN_GENERATED_KEYS);
   ResultSet keys = statement.getGeneratedKeys();
}catch(SQLException exc){
   exc.printStackTrace();
}

Retrieving Data from a Database

...
try{
   ResultSet query_result = statement.executeQuery(query);
}catch(SQLException exc){
   exc.printStackTrace();
}

Invoking the next() Method

...
try{
   ResultSet query_result = statement.executeQuery(query);
   while(query_result.next()){
      ...
   }
}catch(SQLException exc){
   exc.printStackTrace();
}

Sample Example1:

package com.knowledgewala.database.mysql;

import java.sql.DriverManager;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.logging.Logger;
/**
 * This class helps to connect oracle database server.
 * 
 * @author dknitk
 *
 */
public class KWOracleSqlJDBC1 {


    // Instantiate Logger Object
    private static final Logger LOGGER = Logger.getLogger(KWMySqlJDBC1.class
            .getName());
    //Instantiate Oracle Database connection properties
    private static final String DB_DRIVER = "oracle.jdbc.driver.OracleDriver";
    private static final String DB_CONNECTION = "jdbc:oracle:thin:@localhost:1521:KW";
    private static final String DB_USER = "user";
    private static final String DB_PASSWORD = "password";

    /**
     * This method helps to execute java program.
     * 
     * @param argv
     */
    public static void main(String[] argv) {

        try {
            // Call method to insert records into database table
            insertRecordIntoTable();

        } catch (SQLException e) {

            LOGGER.info(e.getMessage());

        }

    }

    /**
     * This method helps to insert record into table.
     * 
     * @throws SQLException
     */
    private static void insertRecordIntoTable() throws SQLException {

        Connection dbConnection = null;
        PreparedStatement preparedStatement = null;

        String insertTableSQL = "INSERT INTO KWEMPUSER"
                + "(USER_ID, USERNAME, CREATED_BY, CREATED_DATE) VALUES"
                + "(?,?,?,?)";

        try {
            // Call method to get connection
            dbConnection = getDBConnection();
            preparedStatement = dbConnection.prepareStatement(insertTableSQL);
            // Set database record to insert into table
            preparedStatement.setInt(1, 11);
            preparedStatement.setString(2, "kw");
            preparedStatement.setString(3, "tiger");
            preparedStatement.setTimestamp(4, getCurrentTimeStamp());

            // execute insert SQL stetement
            preparedStatement.executeUpdate();

            LOGGER.info("Record is inserted into DBUSER table!");

        } catch (SQLException e) {

            System.out.println(e.getMessage());

        } finally {

            if (preparedStatement != null) {
                preparedStatement.close();
            }

            if (dbConnection != null) {
                dbConnection.close();
            }

        }

    }

    /**
     * This method helps to create oracle database connection.
     * 
     * @return Connection
     */
    private static Connection getDBConnection() {

        Connection dbConnection = null;

        try {

            Class.forName(DB_DRIVER);

        } catch (ClassNotFoundException e) {

            System.out.println(e.getMessage());

        }

        try {

            dbConnection = DriverManager.getConnection(
                            DB_CONNECTION, DB_USER,DB_PASSWORD);
            return dbConnection;

        } catch (SQLException e) {

            System.out.println(e.getMessage());

        }

        return dbConnection;

    }

    /**
     * This method helps to get current timeStamp
     * 
     * @return Timestamp
     */
    private static java.sql.Timestamp getCurrentTimeStamp() {

        java.util.Date today = new java.util.Date();
        return new java.sql.Timestamp(today.getTime());

    }

}