package jdbc;
import java.sql.Timestamp;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Scanner;
public class JdbcSample {
static {
try {
Class.forName("oracle.jdbc.driver.OracleDriver");
System.out.println("Driver Loaded");
} catch (Exception e) {
e.printStackTrace();
}
}
public static Connection getConnection() throws SQLException {
Connection con = null;
try {
con = DriverManager.getConnection(
"jdbc:oracle:thin:@localhost:1521:sample", "test",
"test");
System.out.println("Connection created");
} catch (Exception e) {
e.printStackTrace();
}
return con;
}
public static void main(String[] args) throws Exception {
System.out.println("Employee Database");
System.out
.println(" 0-Create Table\n 1-Insert\n 2-Find\n 3-Update\n 4-View\n 5-Delete");
Scanner s = new Scanner(System.in);
int input = s.nextInt();
switch (input) {
case 0:
create();
break;
case 1:
insert();
break;
case 2:
find();
break;
case 3:
update();
break;
case 4:
view();
break;
case 5:
delete();
case 6:
break;
}
}
//create table
public static void create() {
PreparedStatement ps = null;
Connection con = null;
try {
final String createsql = ("create table Employee(id number(10),name varchar2(200),salary number(8,2),doj timestamp)");
con = getConnection();
ps = con.prepareStatement(createsql);
ps.executeUpdate();
System.out.println("Table created");
} catch (SQLException e) {
System.out.println("Error in creating tabls" + e);
} finally {
try {
if (ps != null)
ps.close();
if (con != null)
con.close();
System.out.println("Connection closed");
} catch (Exception e) {
e.printStackTrace();
}
}
}
//Insert record
public static void insert() {
PreparedStatement ps = null;
Connection con = null;
int rs;
try {
Scanner s = new Scanner(System.in);
System.out.println("Enter id:");
int id = s.nextInt();
System.out.println("Enter Employeename:");
String employeename = s.next();
System.out.println("Enter Salary:");
double salary = s.nextDouble();
// Long d=System.currentTimeMillis();
// Date sqlDate = new java.sql.Date(d);
java.sql.Timestamp sqlDate = new java.sql.Timestamp(
new java.util.Date().getTime());
final String insertsql = ("Insert into Employee values(?,?,?,?)");
con = getConnection();
ps = con.prepareStatement(insertsql);
ps.setInt(1, id);
ps.setString(2, employeename);
ps.setDouble(3, salary);
// ps.setDate(4,sqlDate);
ps.setTimestamp(4, sqlDate);
rs = ps.executeUpdate();
System.out.println(rs + " Record Inserted");
} catch (SQLException e) {
System.out.println("Error in inserting" + e);
} finally {
try {
if (ps != null)
ps.close();
if (con != null)
con.close();
System.out.println("Connection closed");
} catch (Exception e) {
e.printStackTrace();
}
}
}
//Search Records
public static void find() {
PreparedStatement ps = null;
Connection con = null;
ResultSet rs = null;
int count = 0;
try {
Scanner s = new Scanner(System.in);
System.out.println("Enter id to find:");
int id = s.nextInt();
final String findsql = ("select * from Employee where id=?");
con = getConnection();
ps = con.prepareStatement(findsql);
ps.setInt(1, id);
rs = ps.executeQuery();
while (rs.next()) {
count = count + 1;
String name = rs.getString("name");
double salary = rs.getDouble("salary");
// Date date=rs.getDate("doj");
Timestamp date = rs.getTimestamp("doj");
System.out.println("Employee Name:" + name + "\nSalary:"
+ salary + "\nDate:" + date);
}
System.out.println(count + " Record found");
} catch (SQLException e) {
System.out.println("Error in finding" + e);
} finally {
try {
if (ps != null)
ps.close();
if (con != null)
con.close();
System.out.println("Connection closed");
if (rs != null)
rs.close();
} catch (Exception e) {
e.printStackTrace();
}
}
}
//Update Records
public static void update() {
PreparedStatement ps = null;
Connection con = null;
int rs;
try {
Scanner s = new Scanner(System.in);
System.out.println("Enter id:");
int id = s.nextInt();
System.out.println("Enter Employeename:");
String employeename = s.next();
System.out.println("Enter Salary:");
double salary = s.nextDouble();
// Long d=System.currentTimeMillis();
// Date sqlDate = new java.sql.Date(d);
Timestamp sqlDate = new java.sql.Timestamp(
new java.util.Date().getTime());
final String updatesql = ("Update Employee set name=?,salary=?,doj=? where id=?");
con = getConnection();
ps = con.prepareStatement(updatesql);
ps.setString(1, employeename);
ps.setDouble(2, salary);
// ps.setDate(3,sqlDate);
ps.setTimestamp(3, sqlDate);
ps.setInt(4, id);
rs = ps.executeUpdate();
System.out.println(rs + " Record Updated");
} catch (SQLException e) {
System.out.println("Error in Updating" + e);
} finally {
try {
if (ps != null)
ps.close();
if (con != null)
con.close();
System.out.println("Connection closed");
} catch (Exception e) {
e.printStackTrace();
}
}
}
//View records
public static void view() {
int count = 0;
PreparedStatement ps = null;
Connection con = null;
ResultSet rs = null;
try {
final String viewsql = ("select * from Employee order by id,name");
con = getConnection();
ps = con.prepareStatement(viewsql);
rs = ps.executeQuery();
while (rs.next()) {
count = count + 1;
int id = rs.getInt("id");
String name = rs.getString("name");
double salary = rs.getDouble("salary");
Timestamp date = rs.getTimestamp("doj");
System.out.println(count + " Records ");
System.out.println("ID:" + id + "\nEmployee Name:" + name
+ "\nSalary:" + salary + "\nDate:" + date);
System.out.println();
}
System.out.println(count + " Records Displayed");
} catch (SQLException e) {
System.out.println("Error in finding value" + e);
} finally {
try {
if (ps != null)
ps.close();
if (con != null)
con.close();
System.out.println("Connection closed");
if (rs != null)
rs.close();
} catch (Exception e) {
e.printStackTrace();
}
}
}
//Delete Records
public static void delete() {
PreparedStatement ps = null;
Connection con = null;
int rs;
try {
Scanner s = new Scanner(System.in);
System.out.println("Enter id:");
int id = s.nextInt();
final String deletesql = ("delete from Employee where id=?");
con = getConnection();
ps = con.prepareStatement(deletesql);
ps.setInt(1, id);
rs = ps.executeUpdate();
System.out.println(rs + "records deleted");
} catch (SQLException e) {
System.out.println("Error in Deleting" + e);
} finally {
try {
if (ps != null)
ps.close();
if (con != null)
con.close();
System.out.println("Connection closed");
} catch (Exception e) {
e.printStackTrace();
}
}
}
}
Note: For this we need a ojdbc14.jar file.
import java.sql.Timestamp;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Scanner;
public class JdbcSample {
static {
try {
Class.forName("oracle.jdbc.driver.OracleDriver");
System.out.println("Driver Loaded");
} catch (Exception e) {
e.printStackTrace();
}
}
public static Connection getConnection() throws SQLException {
Connection con = null;
try {
con = DriverManager.getConnection(
"jdbc:oracle:thin:@localhost:1521:sample", "test",
"test");
System.out.println("Connection created");
} catch (Exception e) {
e.printStackTrace();
}
return con;
}
public static void main(String[] args) throws Exception {
System.out.println("Employee Database");
System.out
.println(" 0-Create Table\n 1-Insert\n 2-Find\n 3-Update\n 4-View\n 5-Delete");
Scanner s = new Scanner(System.in);
int input = s.nextInt();
switch (input) {
case 0:
create();
break;
case 1:
insert();
break;
case 2:
find();
break;
case 3:
update();
break;
case 4:
view();
break;
case 5:
delete();
case 6:
break;
}
}
//create table
public static void create() {
PreparedStatement ps = null;
Connection con = null;
try {
final String createsql = ("create table Employee(id number(10),name varchar2(200),salary number(8,2),doj timestamp)");
con = getConnection();
ps = con.prepareStatement(createsql);
ps.executeUpdate();
System.out.println("Table created");
} catch (SQLException e) {
System.out.println("Error in creating tabls" + e);
} finally {
try {
if (ps != null)
ps.close();
if (con != null)
con.close();
System.out.println("Connection closed");
} catch (Exception e) {
e.printStackTrace();
}
}
}
//Insert record
public static void insert() {
PreparedStatement ps = null;
Connection con = null;
int rs;
try {
Scanner s = new Scanner(System.in);
System.out.println("Enter id:");
int id = s.nextInt();
System.out.println("Enter Employeename:");
String employeename = s.next();
System.out.println("Enter Salary:");
double salary = s.nextDouble();
// Long d=System.currentTimeMillis();
// Date sqlDate = new java.sql.Date(d);
java.sql.Timestamp sqlDate = new java.sql.Timestamp(
new java.util.Date().getTime());
final String insertsql = ("Insert into Employee values(?,?,?,?)");
con = getConnection();
ps = con.prepareStatement(insertsql);
ps.setInt(1, id);
ps.setString(2, employeename);
ps.setDouble(3, salary);
// ps.setDate(4,sqlDate);
ps.setTimestamp(4, sqlDate);
rs = ps.executeUpdate();
System.out.println(rs + " Record Inserted");
} catch (SQLException e) {
System.out.println("Error in inserting" + e);
} finally {
try {
if (ps != null)
ps.close();
if (con != null)
con.close();
System.out.println("Connection closed");
} catch (Exception e) {
e.printStackTrace();
}
}
}
//Search Records
public static void find() {
PreparedStatement ps = null;
Connection con = null;
ResultSet rs = null;
int count = 0;
try {
Scanner s = new Scanner(System.in);
System.out.println("Enter id to find:");
int id = s.nextInt();
final String findsql = ("select * from Employee where id=?");
con = getConnection();
ps = con.prepareStatement(findsql);
ps.setInt(1, id);
rs = ps.executeQuery();
while (rs.next()) {
count = count + 1;
String name = rs.getString("name");
double salary = rs.getDouble("salary");
// Date date=rs.getDate("doj");
Timestamp date = rs.getTimestamp("doj");
System.out.println("Employee Name:" + name + "\nSalary:"
+ salary + "\nDate:" + date);
}
System.out.println(count + " Record found");
} catch (SQLException e) {
System.out.println("Error in finding" + e);
} finally {
try {
if (ps != null)
ps.close();
if (con != null)
con.close();
System.out.println("Connection closed");
if (rs != null)
rs.close();
} catch (Exception e) {
e.printStackTrace();
}
}
}
//Update Records
public static void update() {
PreparedStatement ps = null;
Connection con = null;
int rs;
try {
Scanner s = new Scanner(System.in);
System.out.println("Enter id:");
int id = s.nextInt();
System.out.println("Enter Employeename:");
String employeename = s.next();
System.out.println("Enter Salary:");
double salary = s.nextDouble();
// Long d=System.currentTimeMillis();
// Date sqlDate = new java.sql.Date(d);
Timestamp sqlDate = new java.sql.Timestamp(
new java.util.Date().getTime());
final String updatesql = ("Update Employee set name=?,salary=?,doj=? where id=?");
con = getConnection();
ps = con.prepareStatement(updatesql);
ps.setString(1, employeename);
ps.setDouble(2, salary);
// ps.setDate(3,sqlDate);
ps.setTimestamp(3, sqlDate);
ps.setInt(4, id);
rs = ps.executeUpdate();
System.out.println(rs + " Record Updated");
} catch (SQLException e) {
System.out.println("Error in Updating" + e);
} finally {
try {
if (ps != null)
ps.close();
if (con != null)
con.close();
System.out.println("Connection closed");
} catch (Exception e) {
e.printStackTrace();
}
}
}
//View records
public static void view() {
int count = 0;
PreparedStatement ps = null;
Connection con = null;
ResultSet rs = null;
try {
final String viewsql = ("select * from Employee order by id,name");
con = getConnection();
ps = con.prepareStatement(viewsql);
rs = ps.executeQuery();
while (rs.next()) {
count = count + 1;
int id = rs.getInt("id");
String name = rs.getString("name");
double salary = rs.getDouble("salary");
Timestamp date = rs.getTimestamp("doj");
System.out.println(count + " Records ");
System.out.println("ID:" + id + "\nEmployee Name:" + name
+ "\nSalary:" + salary + "\nDate:" + date);
System.out.println();
}
System.out.println(count + " Records Displayed");
} catch (SQLException e) {
System.out.println("Error in finding value" + e);
} finally {
try {
if (ps != null)
ps.close();
if (con != null)
con.close();
System.out.println("Connection closed");
if (rs != null)
rs.close();
} catch (Exception e) {
e.printStackTrace();
}
}
}
//Delete Records
public static void delete() {
PreparedStatement ps = null;
Connection con = null;
int rs;
try {
Scanner s = new Scanner(System.in);
System.out.println("Enter id:");
int id = s.nextInt();
final String deletesql = ("delete from Employee where id=?");
con = getConnection();
ps = con.prepareStatement(deletesql);
ps.setInt(1, id);
rs = ps.executeUpdate();
System.out.println(rs + "records deleted");
} catch (SQLException e) {
System.out.println("Error in Deleting" + e);
} finally {
try {
if (ps != null)
ps.close();
if (con != null)
con.close();
System.out.println("Connection closed");
} catch (Exception e) {
e.printStackTrace();
}
}
}
}
Note: For this we need a ojdbc14.jar file.