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();
}
}
}d12. 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