JAVA-Oracle JDBC

JDBC

JDBC환경구성

윈도우-프리퍼런스-자바-빌드패스-클래스패스 배리어블-JRE_LIB

JRE_LIB의 위치를 잘 볼 것

oraclexe폴더 - app - oracle - product - 11.2.0 - server - jdbc - lib의 ojdbc6.jar를 복사

 

JRE_LIB이 있는 폴더 'lib' - ext 폴더에 ojdbc6.jar를 붙여넣음

 

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

를 테스트하여 try~catch문을 통해 try에 sysout("할말")을 적어 성공여부를 확인함

 

 

 

자바 프로그램	Statement	JDBC드라이버	→	DBMS(오라클)	→	DB(xe)
	→ ​커넥션 connection
	PreparedStatement
String url = "jdbc:oracle:thin:@localhost:1521:xe"; String user = "joeun"; String pw = "joeun";    Connection conn = null;    try {      Class.forName("oracle.jdbc.driver.OracleDriver");      System.out.println("드라이버 로딩 성공");          conn = DriverManager.getConnection(url, user, pw);      System.out.println("커넥션 연결 성공"); } catch (Exception e) {      // TODO Auto-generated catch block      e.printStackTrace(); } finally {      try {           if(conn != null) conn.close();      } catch (Exception e2) {           // TODO: handle exception           e2.printStackTrace();      } } //상기 코드는 커넥션 연결 성공을 실험하는 코드이다.

 

DAO(Data Access Object)클래스

- 일반적으로 table당 1개씩 작성함

이하, StringBuffer를 통하여 사용. 실사용 안함.

public void insert(String ssn, String name, int age) {   Connection conn = null;   Statement stmt = null;   String sql = "";      StringBuffer sb = new StringBuffer();   sb.append("insert into member (ssn, name, age) values ");   sb.append("('");   sb.append(ssn);   sb.append("', '");   sb.append(name);   sb.append("', ");   sb.append(age);   sb.append(")");   sql = sb.toString();   try {    conn = DriverManager.getConnection(URL, USER, PW);    stmt = conn.createStatement();    int i = stmt.executeUpdate(sql);    if(i > 0) System.out.println("입력성공");   } catch (Exception e) {    // TODO: handle exception    e.printStackTrace();   } finally {    try {     if(stmt != null) stmt.close();     if(conn != null) conn.close();    } catch (Exception e2) {     // TODO: handle exception     e2.printStackTrace();    }   }  }

 

이하 실제로 사용되는 인서트 코드(PreparedStatement)

public void insert(MemberDTO dto) {      Connection conn = null;      PreparedStatement pstmt = null;      String sql = "insert into member (ssn, name, age) values (?, ?, ?)";         try {           conn = DriverManager.getConnection(URL, USER, PW);           pstmt = conn.prepareStatement(sql);           pstmt.setString(1, dto.getSsn());           pstmt.setString(2, dto.getName());           pstmt.setInt(3, dto.getAge());               int i = pstmt.executeUpdate();           if(i > 0) System.out.println("입력성공");      } catch (Exception e) {           // TODO: handle exception           e.printStackTrace();      } finally {           try {                if(pstmt != null) pstmt.cancel();                if(conn != null) conn.close();           } catch (Exception e2) {                // TODO: handle exception                e2.printStackTrace();           }      } }

 

select계통 JDBC코드

public ArrayList<MemberDTO> selectAll() {      ArrayList<MemberDTO> list = new ArrayList<MemberDTO>();      Connection conn = null;      PreparedStatement pstmt = null;      String sql = "select * from member";      ResultSet rs = null;           try {           conn = DriverManager.getConnection(URL, USER, PW);           pstmt = conn.prepareStatement(sql);           rs = pstmt.executeQuery();                     while(rs.next()) {               String ssn = rs.getString("ssn");               String name = rs.getString("name");               int age = rs.getInt("age");                             MemberDTO dto = new MemberDTO(ssn, name, age);               list.add(dto);           }                } catch (Exception e) {           // TODO: handle exception           e.printStackTrace();      } finally {           closeAll(conn, pstmt, rs);      }      return list; }

상기 코드에서 resultSet은 hashSet과 동등한 방식으로 작동함

트랜젝션

- ACID

 

ACID처리의 예시

A의 계좌에서 B에게 1억을 송금 A의 계좌에서 -1억 B의 계좌에서 +1억

A의 계좌에서 1억 차감 지연 B의 계좌에 1억 증가 성공 A의 계좌에서 1억 차감 확정

 

commit

rollback

savepoint

트랜젝션 컨트롤 랭귀지(TCL)

conn.setAutoCommit(false);

을 이용하여 오토커밋을 막고,

커밋과 롤백을 이용하여 처리함.

 

connection conn = null;

preparedstatement pstmt = null;

String sql1 = "update bank set balance = balance - ? where bid=?";

String sql2 = "update bank set balance = balance + ? where bid=?";

boolean ok = false;

 

try{

conn = DriverManager.getConnection(URL, USER, PW);

pstmt = conn.prepareStatement(sql);

pstmt.executeupdate();

ok = true;

try{

if(ok == true) conn.commit();

else conn.rollback();

이후 sql문 날린 후,

 

팁. BD에서 default 0 으로 기본값을 0으로 할 수 있음.

 

이클립스에서 오라클DB실행법 데이터 소스 익스플로러 - 데이터베이스 커넥션 - 오라클 - 뉴 드라이버 디피니션 name/type : 오라클 신 드라이버 11버전 JAR List : ojdbc14 삭제 - add JAR/Zip : java\jre1.8.0_141\lib\ext\ojdbc6.jar Properties : connection URL을 jdbc:oracle:thin:@localhost:1521:xe로 변경 database Name 을 xe로 변경 아이디/패스워드 입력 ok후 테스트커넥션 이후 oracle에 우클릭 후 커넥션 해주면 됨.   이후 작업을 위해서 우클릭 - SQL File - 파일네임입력 - 데이터베이스 서버 타입(oracle_11) - 커넥션 프로필 네임(new Oracle) - database name(xe)   이후 원하는 명령어를 입력하고 블럭지정 후 alt+x로 실행 세미콜론을 찍어서는 안됨.

 

테이블에서의 기본키

- 테이블에서 특정 레코드 하나를 식별할 수 있게 하는 컬럼

- 유니크 제약조건, not null제약조건

 

외래키

- 특정 테이블A 에서 기본키로 지정되어 있어야 함

- 다른 테이블에서 컬럼으로 사용

- 서로 다른 테이블간에 종속관계를 생성시켜주는 키

- A테이블에 있는 데이터이거나, null값만 들어갈 수 있음.

 

외래키와 본 테이블간의 연결은 join

 

테이블 생성 - 외래키테이블 생성 - 연결

CREATE TABLE employee( eid varchar2(6) PRIMARY KEY, did varchar2(6), eName varchar2(18) )   CREATE TABLE depart( did varchar2(6) PRIMARY KEY, dName varchar2(21) )   alter table employee add constraint fk_employee foreign key(did) references depart(did)   insert into employee values ('e001', null, '홍길동') insert into employee values ('e002', 'd002', '김유신') insert into employee values ('e003', 'd003', '김연아') insert into employee values ('e004', 'd004', '이승만') insert into depart values ('d001', '인사부') insert into depart values ('d002', '비서실') insert into depart values ('d003', '홍보실') insert into depart values ('d004', '총무부') insert into depart values ('d005', '관리부') insert into depart values ('d006', '영업부') insert into depart values ('d007', '연구실')   commit   select * from employee, depart select * from employee e, depart d where e.did = d.did ㄴ 상기 코드는 임플로이와 디파트의 같은 did값을 가진 값의 추출   select e.eid, e.ename, d.dname from employee e, depart d where e.did = d.did ㄴ (e키조인) 상기 코드는 임플로이와 디파트의 같은 did값을 가진 값을 추출하되, e.eid, e.ename, d.did만을 추출   select * from depart select * from employee

 

inner join/ outer join 사용

select e.eid, e.ename, d.dname from employee e inner join depart d on e.did = d.did ㄴ 상기 코드는 위의 e키조인과 같음 select e.eid, e.ename, d.dname from employee e inner join depart d on e.did = d.did ㄴ 상기 코드는 innerjoin에 '왼쪽'인 employee 값을 전부 출력함 select e.eid, e.ename, d.dname from employee e right outer join depart d on e.did = d.did ㄴ 상기 코드는 innerjoin에 '오른쪽'인 depart값을 전부 출력함

 

생년월일 입력,조회하는 함수

create table agetest( birth date ) insert into agetest values ('2010-12-17') select * from agetest select floor(months_between(sysdate, birth)/12) age from agetest

select floor(months_between(sysdate, '1991-08-30')/12) age from dual;

months_between = 두 요소값의 사이 시간을 개월수로 환산

sysdate = 현재시간 호출

floor = 소숫점 버리기

 

한국나이 출력하는 코드

select (cyear-birth)+1 한국나이 from (select to_char(birth, 'yyyy') birth, to_char(sysdate, 'yyyy') cyear from agetest)

 

트리거

- 특정 기능이 실행되었을 때 또 다른 기능을 추가적으로 실행할 필요가 있을 때.

create or replace trigger afterinsertmember after insert on member for each row beclare begin insert into member_back (ssn, name, age) values (:new.ssn, :new.name, :new.age); end; /

sfterinsertmember = 이름

after = 작업 후에 트리거를 발동하겠다. insert = insert작업 후에 member = 멤버 테이블에서 for each row = 매번 실행할 것

오라클 DB의 데이터 백업 방법

exp userid=아이디/비밀번호 @xe file=c:\파일명.확장자

exp userid=아이디/비밀번호 @xe file=c:\dump.dmp

tables = member(member, employee, depart)

멤버, 임플로이, 디파트 테이블만 백업받으려면 마지막줄을 추가사용

를 사용하여 백업을 하며, 일반 커맨드창에서 하여야 함.(db에 연결해서는 안됨)

덧. 비밀번호 후 @xe는 붙여써야 하나 메일주소로 입력되기에...

 

백업된 데이터 사용 방법

imp system/sys1234 @xe

fromuser=joeun

touser=system

file=c:\dump.dmp

상기 코드를 통하여 system계정을 이용하여 joeun의 자료 dump.dmp를 system계정에 업로드할 수 있음.

 

view = 가상 테이블

view의 작성법

create or replace view view_emp as select e.eid, e.ename, d.dname from employee e inner join depart d on e.did = d.did

 

view에는 새로운 자료의 추가는 불가능하나,

자료의 변경 및 삭제는 가능함.

그 방법은 통상의 방법으로 가능 하며

view의 삭제는

drop view를 통하여 진행하게 됨.

 

left outer join의 이용

create or replace view view_empleft as select e.eid, e.ename, d.dname from employee e left outer join depart d on e.eid=d.did

 

create or replace view view_empleft as select e.eid, e.ename, d.dname from employee e left outer join depart d on e.eid=d.did where e.did is null with check option;

 

with에 view용 옵션을 부여할 수 있음.

create or replace view view_member2 as select * from member2 with read only;

상기는 읽기전용

 

index

ㄴ 테이블 내의 자료를 빠르게 찾아내기 위한 방식. 조건을 만들어 부여하는 것.

ㄴ 인덱스의 제작

create index idx_member on member (name, age)

 

stored procedure, 저장 프로시저

ㄴ특정 기능을 축약하여 저장하는 기능

create or replace procedure insertmember ( myssn in varchar2, myname in varchar2, myage in number ) as begin insert into member (ssn, name, age) values (myssn, myname, myage); end; /

를 사용하여 저장 프로시저 insertmember를 작성할 수 있음.

사용은 아래와 같음

exec insertmember (ssn값, name값, age값);

 

상기 저장 프로시저를 이용하여 이클립스에서 사용하는 법은

Connection conn = null; CallableStatement cstmt = null; String sql = "{call insertmember(?,?,?)}"; try {      conn = DriverManager.getConnection(URL, USER, PW);      cstmt = conn.prepareCall(sql);      cstmt.setString(1, dto.getSsn());      cstmt.setString(2, dto.getName());      cstmt.setInt(3, dto.getAge());

 

사용자 함수 제작

0.9를 곱하고 반환	create or replace function fn_haha (price in number) return number is begin return price*0.9; end; /	select fn_haha(100000) from dual;
판매금액*할인율 = 할인가격 반환	create or replace function fn_sale (price in number, sale in number) return number is begin return price*sale; end; /	select fn_sale(35000, 0.9) from dual;

 

자신이 가진 오브젝트 정보

select object_name, object_type, timestamp

from user_objects;

로 확인가능

 

현재 접속중인 사용자의 테이블을 확인하는 법(카탈로그 정보 조회)

select * from user_catalog;

 

데이터 사전 = data dictionary

ㄴ system catalog

ㄴ 데이터에 대한 데이터

ㄴ meta data