Java JDBC: An example to connect Oracle database

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

We will divide this article into 2 parts

  • Oracle database
  • Java JDBC application

 

Part 1: 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);

 

All done with Oracle database part, except querying to fetch all rows

Select * from PLAYER;

MySQL_select_query

 

Part 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 the database from Java application using JDBC API

 

Pre-requisite:

  • Download ojdbc14 jar to be included in the project classpath
  • Loading Oracle driver class (com.jdbc.driver.OracleDriver)
  • Database URL formation (server IP address, port number, database name)
  • Username
  • Password

Note: Steps to include required jars into classpath in Eclipse IDE

 

Once we are ready with above listed things, then we can go ahead and code an example to connect Oracle database and query it

 

Let us see an example on Oracle 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 & 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

 

Download:

 

References:

https://dev.mysql.com/doc/connector-j/5.1/en/connector-j-usagenotes-connect-drivermanager.html
http://www.benchresources.net/spring-jdbc-introduction-and-jdbc-example-without-spring/
https://docs.oracle.com/javase/tutorial/jdbc/basics/gettingstarted.html
https://docs.oracle.com/cd/E11882_01/java.112/e16548/overvw.htm#JJDBC28025
http://docs.oracle.com/javase/tutorial/jdbc/basics/index.html
https://en.wikipedia.org/wiki/JDBC_driver
http://www.devx.com/tips/Tip/28818
https://docs.oracle.com/javase/7/docs/api/java/lang/Class.html#forName(java.lang.String)

 

Read Also:

 

Happy Coding !!
Happy Learning !!

Java JDBC: An example to connect MS Access database
Java: Steps to include jars into classpath in Eclipse IDE