Oncelikle JDBC i kullanmak icin
https://www.oracle.com/technetwork/apps-tech/jdbc-112010-090769.html linkinden ojdbc6.jar i indiriyoruz.
Ve eclipse de yeni bir java projesi olusturup icinde libs adli bir folder olusturuyoruz. Indirdigimiz jar i oraya yapistiriyoruz. Ama bu sekilde yapistirmamiz o jar i kullanmamiza yetmez o yuzden jar a sag tiklayip Build Path -> Add to Build Path secenegine tikliyoruz. Bu sekilde referenced library e jar imiz eklenmis oldu. Artik kullanabiliriz.
JDBC i kullanmak icin temel olarak yapmamiz gerekenler
Connection olustur => Statement olustur => Execute Et => Resultset ile cek.
Statement
Statement icin temelde 2 tip var.
ResultSet executeQuery(String sql) => gordugumuz gibi result set dondurur. select islemleri icin bunu kullaniriz.
int executeUpdate(String sql) => bunu ise insert, update, delete gibi islemlerde kullaniriz ve sonuc olarak islemlerden etkilenen row countu dondurur.
* Ayrica yazdigimiz tum connection, statement ve resultset in kapatilmasi gerekir. bunu tek tek kapatmak yerine try icinde hepsini tamamlarsak otomatik olarak kullanmadiginda kapatilir.
ResultSet Types
3 tip vardir.
Type_Forward_Only : ornegin bir islemde ilk basta 1. rowdayiz ve bunu kullanarak 5. row a gecebiliriz. Ama 5. rowdan 3 e gecemeyiz.Sadece ileri dogru gidebiliriz.
Type_Scroll_Insensitive : bununla hem forward hem backward ilerleyebiliriz. Ama Insensitive oldg icin result olustuktan sonraki degisikliklere duyarsizdir.
Type_Scroll_Sensitive : bununla hem forward hem backward ilerleyebiliriz. Sensitive oldg icin result olustuktan sonraki degisikliklere duyarlidir.
* hic birini yazmazsak defaultu Type_Forward_Only dir.
ResultSet Concur Types
2 tip vardir.
Concur_Read_Only
Concur_Updatable
* defaultu Concur_Read_Only dir.
Asagida hem update hem insert yaptigimiz bir ornek yer almaktadir.
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class UpdatableRecords {
public static void main(String[] args) {
try (Connection conn = DBUtil.getConnection(DBType.ORADB);
Statement stmt = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE);
ResultSet rs = stmt.executeQuery("select department_id, department_name, manager_id, location_id from departments");
) {
rs.absolute(6);
rs.updateString("department_name", "Information Technology");
rs.updateRow();
System.out.println("Record updated successfuly");
rs.moveToInsertRow();
rs.updateInt("department_id", 999);
rs.updateString("department_name", "Training");
rs.updateInt("manager_id", 200);
rs.updateInt("location_id", 2000);
rs.insertRow();
System.out.println("Record inserted succesfully");
} catch(SQLException e){
DBUtil.showErrorMsg(e);
}
}
}
Degisiklikleri sqlplus tan goruntuleyebiliriz.
Simdi de PreparedStatement kullanimina bakalim. Statement in bir alt sinifidir ve sorguya parametre girilecegi zaman kullanilir.
Asagida ornek bir yer almaktadir.
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
public class PreparedStatementEx01 {
public static void main(String[] args) {
Connection conn = null;
PreparedStatement pstmt = null;
ResultSet rs = null;
try {
String sql = "select * from Employees where SALARY < ? and Department_Id=?";
conn = DBUtil.getConnection(DBType.ORADB);
pstmt = conn.prepareStatement(sql, ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
preparedStatement(pstmt, 10000, 50);
System.out.println("---------------------------");
preparedStatement(pstmt, 3000, 50);
} catch(SQLException e){
DBUtil.showErrorMsg(e);
}
}
private static void preparedStatement(PreparedStatement pstmt, double salary, int department_id) throws SQLException {
ResultSet rs;
pstmt.setDouble(1, salary);
pstmt.setInt(2, department_id);
rs = pstmt.executeQuery();
String format = "%-4s%-20s%-25s%-10f\n";
while(rs.next()){
System.out.format(format, rs.getString("Employee_ID"), rs.getString("First_Name"),
rs.getString("Last_Name"), rs.getFloat("Salary"));
}
rs.last();
System.out.println("Total num of rows: "+ rs.getRow());
}
}
INSERT RECORD
import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.Scanner;
public class InsertRecord {
public static void main(String[] args) throws SQLException {
Connection conn = DBUtil.getConnection(DBType.ORADB);
PreparedStatement pstmt = null;
String empname;
int salary, empid;
Date hiredate;
Scanner scan = new Scanner(System.in);
System.out.print("Enter emp id: ");
empid = Integer.parseInt(scan.nextLine());
System.out.print("Enter emp name: ");
empname = scan.nextLine();
System.out.println("Enter salary: ");
salary = Integer.parseInt(scan.nextLine());
System.out.println("Enter hire date: ");
hiredate = java.sql.Date.valueOf(scan.nextLine());
String sql = "insert into newemp values(?,?,?,?)";
pstmt = conn.prepareStatement(sql);
pstmt.setInt(1, empid);
pstmt.setString(2, empname);
pstmt.setInt(3, salary);
pstmt.setDate(4, hiredate);
int result = pstmt.executeUpdate();
if(result == 1){
System.out.println("Record inserted");
} else{
System.err.println("Error occured when insert.");
}
scan.close();
pstmt.close();
conn.close();
}
}
UPDATE RECORD
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.Scanner;
public class UpdateRecord01 {
public static void main(String[] args) throws SQLException{
Connection conn = DBUtil.getConnection(DBType.ORADB);
String sql = "update newemp set empsalary = ? where empid=20";
Scanner scan = new Scanner(System.in);
System.out.println("Enter new salary : ");
int salary = scan.nextInt();
PreparedStatement pstmt = conn.prepareStatement(sql);
pstmt.setInt(1, salary);
int result = pstmt.executeUpdate();
if(result == 1){
System.out.println("Succesfully updated");
} else{
System.err.println("Error occured");
}
scan.close();
pstmt.close();
conn.close();
}
}
DELETE RECORD
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.Scanner;
public class DeleteRecord01 {
public static void main(String[] args) throws SQLException {
Connection conn = DBUtil.getConnection(DBType.ORADB);
String sql = "Delete from newemp where empid=?";
Scanner scan = new Scanner(System.in);
System.out.println("Enter id of emp to delete:");
int empid = scan.nextInt();
PreparedStatement pstmt = conn.prepareStatement(sql);
pstmt.setInt(1, empid);
int result = pstmt.executeUpdate();
if(result == 1){
System.out.println("Succesfully deleted");
} else{
System.err.println("Error occured.");
}
scan.close();
pstmt.close();
conn.close();
}
}
CALLABLE STATEMENT
Callable Statement ile Stored Procedure e parametre gonderebiliriz.
Bunun icin oncelikle bir stored procedure olusturalim.
SqlPlus i acalim. Ve daha once elimizdeki newemp tablosuna kayit eklememizi saglayacak bir procedure yazalim.
create or replace Procedure AddEmployee
(
eid in NEWEMP.EMPID%TYPE,
ename in NEWEMP.EMPNAME%TYPE,
sal in NEWEMP.EMPSALARY%TYPE,
hdate in NEWEMP.HIREDATE%TYPE
)
is
begin
insert into NEWEMP values (eid, ename, sal, hdate);
commit;
end;
/
Procedure yukaridaki gibi olmalidir.
Simdi eclipse e gecip kodumuzu yazalim.
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.Date;
import java.sql.SQLException;
import java.util.Scanner;
public class CallableStatementEx01 {
public static void main(String[] args) {
try (Connection conn = DBUtil.getConnection(DBType.ORADB);
CallableStatement cstmt = conn.prepareCall("{call AddEmployee (?,?,?,?)}");
Scanner scan = new Scanner(System.in); ) {
System.out.println("Enter empid: ");
int empid = Integer.parseInt(scan.nextLine());
System.out.println("Enter empname: ");
String empname = scan.nextLine();
System.out.println("Enter empsalary: ");
int salary = Integer.parseInt(scan.nextLine());
System.out.println("Enter hiredate: ");
Date hdate = java.sql.Date.valueOf(scan.nextLine());
cstmt.setInt(1, empid);
cstmt.setString(2, empname);
cstmt.setInt(3, salary);
cstmt.setDate(4, hdate);
cstmt.execute();
System.out.println("Added succesfully");
} catch(SQLException ex){
DBUtil.showErrorMsg(ex);
}
}
}
INSERTING MULTIPLE RECORDS
Yukarida yazdigimiz kod ile bir kez insert islemi yapabiliyoruz. Eger 10 kez insert yapmak istesek her bir insert isleminde database e baglanip islem yapmamiz gerekecek ve bu da network trafigini arttiracak. Bu yuzden 10 kez insert yapip bir kez database e baglanmamiz iyi bir yontemdir.
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.Date;
import java.sql.SQLException;
import java.util.Scanner;
public class InsertMultipleRecords01 {
public static void main(String[] args) {
try (Connection conn = DBUtil.getConnection(DBType.ORADB);
CallableStatement cstmt = conn.prepareCall("{call AddEmployee(?,?,?,?)}");
Scanner scan = new Scanner(System.in); ) {
String option;
do {
System.out.println("Enter eid: ");
int eid = Integer.parseInt(scan.nextLine());
System.out.println("Enter ename: ");
String ename = scan.nextLine();
System.out.println("Enter salary: ");
int sal = Integer.parseInt(scan.nextLine());
System.out.println("Enter hire date: ");
Date hdate = java.sql.Date.valueOf(scan.nextLine());
cstmt.setInt(1, eid);
cstmt.setString(2, ename);
cstmt.setInt(3, sal);
cstmt.setDate(4, hdate);
cstmt.addBatch();
System.out.println("Do you want to add one more record ? yes/no:");
option = scan.nextLine();
} while (option.equals("yes"));
int[] updatedRecords = cstmt.executeBatch();
System.out.println("Total num of updated records: " + updatedRecords.length);
} catch(SQLException e){
DBUtil.showErrorMsg(e);
}
}
}
Kayitlarin eklenip eklenmedigini kontrol etmek icin SqlPlus ta su query i calistirabiliriz.
select * from newemp where empid in (33, 44, 55);
Bu sekilde eklemis oldugumuz kayitlari gorebiliriz.
STORED PROCEDURE
Stored procedure lar ayni zamanda bir deger de dondurebilir. Bu durumu gorebilecegimiz bir ornek yapalim.
Kullanicidan alacagimiz department id deki calisan sayisni dondurmek isteyelim. Oncelikle bunun icin gerekli stored procedure i yazalim.
create or replace procedure shownum (
dept_id in Employees.Department_Id%Type,
empCount out number
)
as
begin
select count(*) into empCount from Employees
where Department_Id = dept_id;
end;
/
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.SQLException;
import java.sql.Types;
import java.util.Scanner;
public class OutSpEx01 {
public static void main(String[] args) {
try (Connection conn = DBUtil.getConnection(DBType.ORADB);
CallableStatement cstmt = conn.prepareCall("{call shownum (?,?)}");
Scanner scan = new Scanner(System.in); ) {
System.err.println("Enter department id: ");
int deptid = scan.nextInt();
cstmt.setInt(1, deptid);
cstmt.registerOutParameter(2, Types.INTEGER);
cstmt.execute();
int totalCount = cstmt.getInt(2);
System.out.println("Total number of employees work in department "+ deptid + " is "+ totalCount);
} catch(SQLException e){
DBUtil.showErrorMsg(e);
}
}
}
RETURN MULTIPLE ROWS FROM STORED PROCEDURE
Stored procedure dan multiple row dondurmek istedigimizde data type olarak SYS_REFCURSOR kullanmaliyiz.
Verilen department id de calisanlarin bilgilerini dondurecek bir sp yazalim.
Create or replace procedure GETEMPLOYEES
(
p_deptid in employees.department_id%type,
p_persons out SYS_REFCURSOR
)
as
begin
open p_persons for
select employee_id, first_name || last_name as ename,
email, salary from employees
where department_id = p_deptid
order by employee_id;
end;
/
Simdi ise gerekli kodumuzu yazalim.
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Scanner;
import oracle.jdbc.internal.OracleTypes;
public class ReturnMultipleRowsSp01 {
public static void main(String[] args){
try (Connection conn = DBUtil.getConnection(DBType.ORADB);
CallableStatement cstmt = conn.prepareCall("{call getemployees(?,?)}");
Scanner scan = new Scanner(System.in); ) {
System.out.println("Enter deparment id :");
int deptid = scan.nextInt();
cstmt.setInt(1, deptid);
cstmt.registerOutParameter(2, OracleTypes.CURSOR);
cstmt.execute();
ResultSet rs = ((oracle.jdbc.internal.OracleCallableStatement)cstmt).getCursor(2);
String format = "%-4s%-20s%-25s%-10f\n";
while(rs.next()){
System.out.format(format, rs.getInt("employee_id"), rs.getString("ename"), rs.getString("email"), rs.getDouble("salary"));
}
} catch(SQLException e){
DBUtil.showErrorMsg(e);
}
}
}
