Java Application with Oracle Database

Java Application with Oracle Database

This is a step by step Java tutorial on creating the first JDBC application with the Oracle database. If you have not enough idea about the basics of JDBC(Java database connectivity) you can visit this link. You can also see this link to understand the basic steps to connect with the RDBMS. Oracle is a large, popular, and one of the commonly used database management systems around the world. This is a client-server architecture based DBMS, it means the user can communicate with the database with some client like Sqlplus. We can create the  Java application with JDBC(Java Database connectivity) Thin driver or type-4 driver. We can programmatically interact with the Oracle database to create a table, insert data into the table and fetch the data stored in the tables using JDBC OR you can do anything with the database that you can do with database client.

To create the application follow these steps.

1. Install Oracle Database

You need to install Oracle Database to your machine, you can download the suitable version of the database for educational purpose and install it to your machine.

2. Download the Database driver

Once you have done the installation you need to find the database driver for the application.

3. You can either download suitable the driver from this link or you can also find it from your Oracle installation directory in your machine. For example, this path will be something like C:\Oracle_installation_dir\oraclexe\app\oracle\product\11.2.0\server\jdbc\lib

just copy the ojdbc_version.jar(ojdbc6.jar, ojdbc8.jar, etc) file and past it to your desktop.

4. Start Eclipse

5. Create Eclipse Project

Go to file->new->Java project, provide some name and click ok,

6. Add the driver to the project

Right-click on your project name in Eclipse,
Go to build-path->add external archives

Select the driver (ojdbc_version.jar) from desktop or downloads. It will be added to your project and will be displayed in referenced libraries.

7.  Create packages

Create four packages and with four class files,

8. The specification of the project structure is as following

package Name	Class Name	Purpose
com.pack.util	DBConnection	Small utility class with a static method getConnection that will return connection for the database from the DriverManager Class
com.pack.beans	Employee	A Simple Java Bean class or data model for the table employee in the database, with few fields like name, id, dept, and salary(NO Bussiness logic here)
com.pack.dao	EmployeeDao	Data Access Object class with methods to create the table, update and display table data. (All Bussiness logic to communicate with the database will reside in this class)
com.pack.main	Demo	Class with a main() method, for the user to interact with the database

9. Create DBConnection class, in com.pack.util package

package com.pack.util;

import java.sql.Connection;

import java.sql.DriverManager;

import java.sql.SQLException;

public class DBConnection {//Utility Class to open the connection

public static Connection getConnection()

{

      Connection con=null;

      try {

            Class.forName("oracle.jdbc.driver.OracleDriver");

      con=DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:xe",

                                "scott","tigernew");

      } catch (ClassNotFoundException | SQLException e) {

            e.printStackTrace();

      }

      return con;

}

}

10. Create an Employee class in com.pack.bean package,

package com.pack.bean;

//Fully encapsulated

public class Employee { //Employee Java bean

private int eid;        //all private fields

private String ename;

private String edept;

private int esal;

//Constructor

public Employee(int eid, String ename, String edept, int esal) {

      super();

      this.eid = eid;

      this.ename = ename;

      this.edept = edept;

      this.esal = esal;

}

//getter and settet methods

public int getEid() {

      return eid;

}

public void setEid(int eid) {

      this.eid = eid;

}

public String getEname() {

      return ename;

}

public void setEname(String ename) {

      this.ename = ename;

}

public String getEdept() {

      return edept;

}

public void setEdept(String edept) {

      this.edept = edept;

}

public int getEsal() {

      return esal;

}

public void setEsal(int esal) {

      this.esal = esal;

}

}d

11. Create EmployeeDao class, in com.pack.dao package

package com.pack.dao;

import java.sql.Connection;

import java.sql.ResultSet;

import java.sql.SQLException;

import java.sql.Statement;

import com.pack.util.DBConnection;

public class EmployeeDao { //DAO Class

public void createTable()//Create the table in database

{

      Connection con=DBConnection.getConnection();

      try {

            Statement stmt=con.createStatement();

            String sql="CREATE TABLE MYEMPLOYEE1("

                        + "ID NUMBER, NAME VARCHAR2(20),DEPT          VARCHAR2(20),"

                        + "SAL NUMBER, PRIMARY KEY(ID))";

            boolean b=stmt.execute(sql);

            System.out.println("Table create successfully");

            con.close();

      } catch (SQLException e) {

            e.printStackTrace();

      }

}

      public void insertData()//Insert data into the table

      {

            Connection con=DBConnection.getConnection();

            try {

                  Statement stmt=con.createStatement();

                  String sql1="INSERT INTO MYEMPLOYEE1 VALUES(10,'AJAY','CSE',45000)";

                  String sql2="INSERT INTO MYEMPLOYEE1 VALUES(12,'AJAY','CSE',45000)";

                  String sql3="INSERT INTO MYEMPLOYEE1 VALUES(15,'AJAY','CSE',45000)";

                  stmt.execute(sql1);

                  stmt.execute(sql2);

                  stmt.execute(sql3);

                  System.out.println("Table UPADTED successfully");

                  con.close();

            } catch (SQLException e) {

                  System.out.println("SOME EXCEPTION");

                  e.printStackTrace();

            }

           

            }

      public void showData() //display the data from

      {               //the table

            Connection con=DBConnection.getConnection();

            try {

                  Statement stmt=con.createStatement();

                  String sql1="SELECT * FROM MYEMPLOYEE1";

                  ResultSet res=stmt.executeQuery(sql1);

                  while(res.next())

                  {

                        System.out.println(res.getInt(1)+"\t"+res.getString(2)+"\t"+res.getString(3));

                  }

                  con.close();

            } catch (SQLException e) {

                  System.out.println("SOME EXCEPTION");

                  e.printStackTrace();

            }

           

            }

}d

12. Create Demo class, in the package com.pack.main, and run these three steps one by one NOT Together, 

package com.pack.main;

import com.pack.dao.EmployeeDao;

public class Demo {

      public static void main(String[] args) {

            System.out.println("Welcome user");

            EmployeeDao edao=new EmployeeDao();

            edao.createTable(); //1. STEP Never run it again

            //edao.insertData(); // 2. STEP insert data

            //edao.showData();  //3. STEP show data

      }

}

 d

Video Tutorial