SW 심화교육 12일차

# 중국집 DB ERD 관계도 그리기

 

# Oracle Database 

 

- 테이블 생성

  create table study10t(id NUMBER(3), data VARCHAR2(10));

- 자료 삽입

  insert into study10t values(100,'Helloworld');

 

* 오라클 숫자 : number(3) - 최대 세자리 숫자

  VARCHAR2 : 오라클에서 만든 속도가 조금 빠른 varchar

 

- 시퀀스 사용 → 일련번호 만들 때 사용

  create table study11t(id NUMBER(5), data char(5));

  create sequence seq_study11;

  insert into study11t values (seq_study11.nextval , 'apple');

 

  * 오라클과 mysql 은 일련번호 만드는 법이 틀리다

    : mysql auto_increment primary key 를 썼다.

 

- 문자에 이어붙이기  

   select id, data || '*' from study11t;

  * mysql의 concat 과 동일한 기능을 수행한다.

 

- char은 고정 길이를 갖는다.

  * char(10) 으로 선언한 필드에 'apple' 을 넣으면 'apple     '된다.

  * mysql 은 그냥 'apple'이 된다.

 

- Trim() : 좌/우의 공백문자를 제거하는 역할을 한다.

   select id, trim(data) || '*' from study11t;

   

- the_Time date

   create table study12t(the_time date);

   insert into study12t values(sysdate);

   select to_char( the_time, 'YYYY-MM-DD' ) from study12t;

* oracle 의 날짜시간은 date 자료형을 이용한다.

  현재 시간은 sysdate를 이용한다.

  보여지는 형식은 to-char 이용하여 형식을 지정하면 된다.

  select to_char( the_time, 'YYYY-MM-DD HH24:MI:SS' ) from study12t;

 

- group by

  select stid, round(avg(score), 2) as 평균 from scoret group by stid;

 

- inner join

  select * from studentt inner join scoret on studentt.stid = scoret.stid;

 

- 오라클은 이렇게 이너조인을 할 수 있다.

  select * from studentt , scoret where studentt.stid = scoret.stid;

 

- Outer JOIN

  insert into subjectt values ('PHY1', '물리');

  select * from subjectt left outer join scoret on subjectt.subid = scoret.subid;

 

- 오라클에서의 OUTER JOIN

  select * from subjectt, scoret where subjectt.subid = scoret.subid;

  select * from subjectt, scoret where subjectt.subid = scoret.subid(+);

  null 값으로 채워지는 일이 발생하는 쪽에 (+) 표시를 붙인다.

 

- INNER JOIN ON, OUTER JOIN ON, 등이 국제 표준 SQL인데,

  각 DB별로 변형 SQL 문을 만들기 시작함

 

- Oracle의 변형방법을 다른 DB 업체들이 따라하기도 하다.

- 오라클만 쓰는 사람들은 오라클의 방법만을 고집하는 경우가 많다.

 

- FROM 절 서브쿼리

  SELECT x.stid, x.avg FROM (select stid, round(avg(score), 2) as avg from scoret group by stid) x;

  SELECT 시 테이블의 펼칭을 줄때는 AS 사용하지 않는다. (MYsql에서는 계속 지원한다.

 

- AS 문법

  SELECT y.name, x.avg FROM (select stid, round(avg(score), 2) as avg from scoret group by stid) x, 

  studentt y where x.stid = y.stid;

 

* AS 문법, JOIN 의 문법이 약간 틀리지만 기본 개념은 동일하다.

 

# Constraint in Oracle

- Primary key, foreign key, check, unique, not null

  alter table studentt add constraint pk_studentt_stid primary key ( stid );

  alter table scoret add constraint fk_scoret_stid foreign key( stid ) references studentt(stid);

 

  delete from studentt where stid = '10101'; - 위배

  studentt의 10101을 지우게 되면, 참조받는 자식테이블인 scoret는 없는 기록을 참조받기 때문에

  위배

  insert into scoret values('10109', 'KOR1', 100); 는 studentt의 참조받는 10109가 없기 때문에

  자료 못 넣음

 

  alter table scoret add constraint fk_scoret_subid foreign key(subid) references subjectt(subid);

  subjectt의 subid가 Primary key로 지정되지 않았기 때문에 참조할 수 없다.

  (FK Constraint는 먼저 참조할 대상 PK Constraint가 존재해야 생성 가능하다.

 

- check 제약 조건 명시

  alter table scoret add constraint ck_scoret_score check( score>=0 and score<= 100 );

  insert into scoret values('10101', 'PHY1', 120);

 

- unique

  alter table subjectt add constraint uq_subject_subid unique(subid);

  * not null 은 보장안함. no duplicate 는 보장

  insert into subjectt values(null,'없음0');

 

  * 권장사항 : constraint는 테스트 끝나고서

    회원가입 담당자가 일을 다 안한 상황에서 게시판 담당자가 테스트 들어가려면?

    

- constraint 지우기

  alter table scoret drop constraint check ck_scoret_score;

  alter table subjectt drop constraint uq_subject_subid;

  alter table scoret drop constraint fk_scoret_stid;

  alter table studentt drop constraint pk_studentt_stid;

  이름 붙이는 원칙이 있으면 관리하기 편하다.

 

---

-

  create table bangmyung_t(

  no int,

  gul varchar2(100),

  the_time date

  );

 

  create sequence seq_bangmyungl

  insert into bangmyung_t values(seq_bangmyungl.nextval, '만나서 반갑습니다', sysdate);

  

  create table bangmyung_t(

  no int auto_increment primary key,

  gul varchar(100),

  the_time datetime

);

 - mysql 일 때

  insert into bangmyung_t values(default, '만나서 반갑습니다', now());

 import java.sql.*;

public class Test101 {
    public static void main( String[] args ) throws Exception{
		Class.forName("oracle.jdbc.driver.OracleDriver");
		Connection conn = DriverManager.getConnection("jdbc:oracle:thin:@127.0.0.1:1521/XE","HR","HR");
		System.out.println(conn);
		Statement stmt = conn.createStatement();
		
		String gul = "HelloWorld";
		String sql = "insert into bangmyung_t values(seq_bangmyungl.nextval,'" + gul + "', sysdate)";
		stmt.executeUpdate(sql);
		
		stmt.close();
		conn.close();
	}
}
/*
- java -classpath .;ojdbc14.jar Test101
*/ 

- commit

import java.sql.*;
import java.util.*;

public class Test103 {
	public static void addGul(String gul) throws Exception{
		Connection conn = null;
		Statement stmt = null;
		try{
		conn = DriverManager.getConnection(
		            "jdbc:oracle:thin:@127.0.0.1:1521/XE","HR","HR");
		stmt = conn.createStatement();
		
		
		String sql = "insert into bangmyung_t values(seq_bangmyungl.nextval,'" + gul + "', sysdate)";
		System.out.println(sql);
		stmt.executeUpdate(sql);
		
		
		} catch(Exception e){
			throw e;
		}finally{
		if(stmt!=null ){stmt.close();}
		if(conn!=null){conn.close();}
			System.out.println("all closed");
		}
	}
    public static void main( String[] args ) throws Exception{
		Class.forName("oracle.jdbc.driver.OracleDriver");
		addGul("a");
	}
}

- - executeUpdate 상황에서 에러나도 conn.close() 는 되어야 한다.
- finally 영역은 try영역에서 에러가 나건 안나건 무조건 실행한다.
- stmt.close() conn.close() 를 finally로 옮김,
- getConnection에서 에러가 나면, conn, stmt 는 null인 상태로 finally로 가게 됨.
  finally 부분의 close가 호출되는 시점을 null이 아닐 때로 조건 설정

 

 

# 게시판 클래스

 

- BangMyungVO.class

package temp;

import java.sql.*;
import java.util.*;


public class BangMyungVO{
	private Integer no = null;
	private String gul = null;
	private String the_time = null;
	
	public void setNo(int i){no = i;}
	public Integer getNo(){return no;}
	
	public void setGul(String i){gul = i;}
	
	public String getGul(){
	return gul;
	}
	
	public void setThe_time(String i){
	the_time = i;
	}
	
	public String getThe_time(){
	return the_time;
	}
}

- BangMyungDAO.class

package temp;

import java.sql.*;
import java.util.*;

public class BangMyungDAO {

	static{
		try{
		Class.forName("oracle.jdbc.driver.OracleDriver");
		
		}catch(ClassNotFoundException e){
		}
	}
	public static void addGul(String gul) throws Exception{
		Connection conn = null;
		Statement stmt = null;
		try{
		conn = DriverManager.getConnection(
		            "jdbc:oracle:thin:@127.0.0.1:1521/XE","HR","HR");
		stmt = conn.createStatement();
		
		
		String sql = "insert into bangmyung_t values(seq_bangmyungl.nextval,'" + gul + "', sysdate)";
		System.out.println(sql);
		stmt.executeUpdate(sql);
		
		
		} catch(Exception e){
			throw e;
		}finally{
		if(stmt!=null ){stmt.close();}
		if(conn!=null){conn.close();}
			System.out.println("all closed");
		}
	}
	
	public static List<BangMyungVO> findAll() throws Exception{
	List<BangMyungVO> ls = null;
	Connection conn = null;
	Statement stmt = null;
		
		try{
		conn = DriverManager.getConnection("jdbc:oracle:thin:@127.0.0.1:1521/XE","HR","HR");
		stmt = conn.createStatement();
		
		String sql = "select * from bangmyung_t";
		ResultSet rs = stmt.executeQuery(sql);
		
		ls = new ArrayList<BangMyungVO>();
		while(rs.next()){
			BangMyungVO vo = new BangMyungVO();
			vo.setNo(rs.getInt("no"));
			vo.setGul(rs.getString("gul"));
			vo.setThe_time(rs.getString("the_time"));
			ls.add(vo);
			
		}
		
		} catch(Exception e){
		throw e;
		} finally{
		if(stmt != null){stmt.close();}
		if(conn != null){conn.close();}
		}
		return ls;
	}
	
}

/*
- executeUpdate 상황에서 에러나도 conn.close() 는 되어야 한다.
- finally 영역은 try영역에서 에러가 나건 안나건 무조건 실행한다.
- stmt.close() conn.close() 를 finally로 옮김,
- getConnection에서 에러가 나면, conn, stmt 는 null인 상태로 finally로 가게 됨.
  finally 부분의 close가 호출되는 시점을 null이 아닐 때로 조건 설정
*/

- 메인 클래스

import java.sql.*;
import java.util.*;
import temp.BangMyungDAO;
import temp.BangMyungVO;

public class Test105{
    public static void main( String[] args ) throws Exception{
	BangMyungDAO.addGul("끝이 보이냐");
	List<BangMyungVO> ls = BangMyungDAO.findAll();
	
	for( BangMyungVO vo : ls ){
	System.out.println(vo.getNo() + "\t" + vo.getGul() + "\t" + vo.getThe_time());
	}
	}
}

# 트랜잭션 JDBC 반영

- 트랜잭션 : 2개 이상의 update 문을 하나처럼 묶는 개념

- 티비 가전점에서 티비를 하나 고객이 구매하면, 매상은 올라가는거랑 동시에 재고가 줄어들잖아.
  근데 매상만 올라가고, 재고가 안줄어들면 DB가 엉키니까
  매상 올리는 SQL 작업하고, 재고 내리는 SQL 작업 모두 성공해야 COMMIT 을 해준다는 개념이
  트랜잭션이야.
  그래서 2개 이상의 UPDATE문을 하나의 작업단위로 묶는 개념이지

 

- 사용자가 SQL 문장을 실행할 때, 바로 테이블에 적용시키지 않고, 

  로그에 담아둔다.

  하나의 트랜잭션이 모두 종료되면 그 때 테이블에 반영된다.

  트랜잭션 중 하나라도 작업 수행을 실패하면 테이블에 반영되지 않는다.

  동일한 conn에서 작업한 내용만 트랜잭션으로 묶을 수 있다.

 

- 트랜잭션 

import java.sql.*;
import java.util.*;


public class Test106{
    public static void main( String[] args ) throws Exception{
	Class.forName("oracle.jdbc.driver.OracleDriver");
	Connection conn = DriverManager.getConnection(
	"jdbc:oracle:thin:@127.0.0.1:1521/XE","HR","HR");
	conn.setAutoCommit(false);
	Statement stmt = conn.createStatement();
	stmt.executeUpdate("insert into test_tx values(6)");
	stmt.executeUpdate("insert into test_tx values(7)");
	stmt.executeUpdate("insert into test_tx values(8)");
	stmt.close();
	conn.commit();
	conn.rollback(); // 로그를 그냥 비운다.
	conn.close();
	}
}

- conn 을 통해서 executeUpdate 하면 LOG을 거쳐서 Table에 저장된다.
- jdbc 는 autoCommit을 지원한다.
- 즉 executeUpdate 시에 무조건 commit 이 자동으로 먹는다.
- conn.setAutoCommit(false) : commit 이 자동으로 먹지 않는다.

 

- 트랜잭션 try/catch 문으로 개선

import java.sql.*;
import java.util.*;


public class Test106_2{
    public static void main( String[] args ) throws Exception{
	Class.forName("oracle.jdbc.driver.OracleDriver");
	Connection conn = null;
	Statement stmt = null;
	try{
	
	conn = DriverManager.getConnection(
	"jdbc:oracle:thin:@127.0.0.1:1521/XE","HR","HR");
	conn.setAutoCommit(false);
	stmt = conn.createStatement();
	stmt.executeUpdate("insert into test_tx values(9)");
	stmt.executeUpdate("insert into test_tx values(10)");
	stmt.executeUpdate("insert into test_tx values(11)");
	
	conn.commit();
	//conn.rollback();
	}catch(Exception e){
		if( conn != null){conn.rollback();}
		throw e;
	}finally{
	if( stmt != null )stmt.close();
	if( conn != null )conn.close();
	}
	
	}
}