개발자의 오르막
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();
}
}
}
'교육과정 ( SW 개발자 심화과정 ) > Java' 카테고리의 다른 글
SW 심화과정 16일차 (0) | 2019.07.30 |
---|---|
SW 심화교육 15일차 (0) | 2019.07.29 |
SW 심화교육 11일차 (0) | 2019.07.25 |
SW 심화과정 D-9 (0) | 2019.07.24 |
SW 심화교육 9일차 (0) | 2019.07.23 |