Java – An example to connect Oracle database

May 30, 2016 SJ JDBC 0

In this article, we will learn and list down the steps to connect Oracle database and finally executing a simple query to test whether connected database works as expected

Connect Oracle database :

We will divide this article into 2 parts –

  1. Oracle database
  2. Java JDBC application

Check MS Access database interaction through Java JDBC API for Java 1.8 version

1. Set-up Oracle Database :

Before working with JDBC API to interact with database (to be specific Oracle database for this example), we need to set up Oracle database and create required things like

  • Create database
  • Create table (inside newly created database)
  • Insert few sample records (inside newly created table)
  • For this example, we will create database called “benchresources”

1.1 Create database :

CREATE DATABASE benchresources;

Next step is to create new table and insert couple of records

1.2 Create table command :

CREATE TABLE `PLAYER` (
  `PLAYER_ID` INT(6) NOT NULL AUTO_INCREMENT,
  `NAME` VARCHAR(50) NOT NULL,
  `AGE` INT(3) NOT NULL,
  `MATCHES` INT(3) NOT NULL,
  PRIMARY KEY (`PLAYER_ID`)
);

1.3 Insert new records :

INSERT INTO `PLAYER`(`NAME`, `AGE`, `MATCHES`) VALUES ("Sachin Tendulkar",41,200);
INSERT INTO `PLAYER`(`NAME`, `AGE`, `MATCHES`) VALUES ("Shane Warne",44,145);
INSERT INTO `PLAYER`(`NAME`, `AGE`, `MATCHES`) VALUES ("Kevin Pietersen",34,104);
INSERT INTO `PLAYER`(`NAME`, `AGE`, `MATCHES`) VALUES ("Shahid Afridi",35,27);
INSERT INTO `PLAYER`(`NAME`, `AGE`, `MATCHES`) VALUES ("Brian Lara",45,131);
INSERT INTO `PLAYER`(`NAME`, `AGE`, `MATCHES`) VALUES ("Graeme Smith",34,117);
INSERT INTO `PLAYER`(`NAME`, `AGE`, `MATCHES`) VALUES ("Mahela Jayawardene",37,145);

1.4 Query table to view inserted records

  • All done with Oracle database part, except querying to fetch all rows 
Select * from PLAYER;
  • See below screen-capture to see new records
MySQL_select_query

 

2. Java JDBC application :

  • As we are completed with set up and ready with Oracle database
  • next step is to figure out essential things required to query database
  • from Java application using JDBC API

2.1 Pre-requisite :

2.2 JDBC program to connect and query Oracle database/table :

  • Once we are ready with above listed things
  • Then we can go ahead and code an example to connect Oracle database
  • Finally querying database

MyOracleDatabaseConnectionExample.java

package in.bench.resources.oracle.db.example;

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

public class MyOracleDatabaseConnectionExample {

	public static void main(String[] args) {

		// variables
		Connection connection = null;
		Statement statement = null;
		ResultSet resultSet = null;

		// Step 1: Loading or
		// registering Oracle JDBC driver class
		try {
			Class.forName("oracle.jdbc.driver.OracleDriver");
		}
		catch(ClassNotFoundException cnfex) {
			System.out.println("Problem in"
					+ " loading Oracle JDBC driver");
			cnfex.printStackTrace();
		}

		// Step 2: Opening database connection
		try {

			// Step 2.A: Create and
			// get connection using DriverManager class
			connection = DriverManager
					.getConnection("jdbc:oracle:thin:@localhost:1521:xe",
							"scott",
							"tiger"); 

			// Step 2.B: Creating JDBC Statement
			statement = connection.createStatement();

			// Step 2.C: Executing SQL and
			// retrieve data into ResultSet
			resultSet = statement.executeQuery("SELECT * FROM PLAYER");

			System.out.println("ID\tName\t\t\tAge\tMatches");
			System.out.println("==\t================\t===\t=======");

			// processing returned data and printing into console
			while(resultSet.next()) {
				System.out.println(resultSet.getInt(1) + "\t" +
						resultSet.getString(2) + "\t" +
						resultSet.getInt(3) + "\t" +
						resultSet.getInt(4));
			}

		}
		catch(SQLException sqlex){
			sqlex.printStackTrace();
		}
		finally {
			// Step 3: Closing database connection
			try {
				if(null != connection) {
					// cleanup resources, once after processing
					resultSet.close();
					statement.close();

					// and then finally close connection
					connection.close();
				}
			}
			catch (SQLException sqlex) {
				sqlex.printStackTrace();
			}
		}
	}
}

Output:

ID	Name			Age	Matches
==	================	===	=======
1	Sachin Tendulkar	41	200
2	Shane Warne		44	145
3	Kevin Pietersen	        34	104
4	Shahid Afridi		35	27
5	Brian Lara		45	131
6	Graeme Smith		34	117
7	Mahela Jayawardene	37	145

2.3 Download :

Related Articles :

References :

Happy Coding !!
Happy Learning !!

Java – An example to connect MS Access database
Java - Steps to include jars in classpath in Eclipse IDE

Related posts:

  1. Java – An example to connect MS Access database
  2. Java – Updating a record using JDBC Statement interface
  3. Java – Getting single record using JDBC PreparedStatement interface
  4. Java – Like clause example using JDBC Statement interface