오라클 데이터베이스
sQL 6일차(0717)
피아마수
2023. 7. 17. 17:09
sequence 특징
자동으로 번호를 생성하는 객체
제약정보보기( 테이블명 대문자로 써야함)
select * from user_constraints where table_name='BOARD';시퀀스 생성
공유객체: 전체 테이블에서 공유해서 사용한다.
-->게시판마다 따로따로 사용하려면 시퀀스 객체를 각각 생성해주면 된다.
create sequence board_num;사용자계정에 시퀀스에 생성된것을 확인할수 있다.
시퀀스 번호 추출 ( 번호를 계속 올림)
select board_num.nextval from dual;시퀀스 현재 번호 추출
select board_num.currval from dual;MS-SQL의 테이블 종속적인 시퀀스
identity(1,1) ->1부터 1씩 증가
create table board(boardnum int identity(1,1),,,,title)
insert into board(title) values('방가'); -- 자동으로 시퀀스 컬럼을 통해 데이터가 추가됨My-sql의 테이블 종속적인 시퀀스
auto_increment - > my-Sql의 테이블 종속적인 시퀀스
create table board(boardnum int auto_increment,,,,title)
insert into board(title) values('방가');oracle에서 시퀀스를 시작 숫자와 증가분을 지정하기
10부터 시작 2씩 증가
create sequence seq_num
start with 10
increment by 2;
rownum
의사 컬럼
실제로 테이블에 존재하지 않지만 내부적으로 행번호를 부여하는 컬럼
create table 에서는 사용하지 못한다.
rownum을 사용한 테이블을 order by 하고 싶으면 서브쿼리안에서 정렬후에 바깥쪽 select 구문에서 rownum을 써주자
--순번이 뒤죽박죽 된다.
select rownum, empno, ename, sal
from emp
order by sal; -- 이미 select 된것을 order by 를 통해서 sal을 기준으로 정렬하니까 rownum값이이렇게 됌
--실행순서 >> from >> select >> select 한 데이터 순번(rownum)>> order by
--서브쿼리를 사용하면 rownum의 순서가 제대로 나온다.
select rownum, e.*
from (
select empno, ename, sal
from emp
order by sal -- 이미 정럴된 데이터를 기반으로 rownum을 붙이면 해결
) e;ms-sql에서는 Top n쿼리가 있는데 오라클에서는 존재 하지 않는다 -->이를 rownum으로 해결("기준"이 되는 데이터 순으로 "정렬"시키고 상위 n 개의 데이터 가져오기)
rownum 사용 ( 순번부여...상위 n)
1.정렬의 기준 설정하기(선행)
2. 정렬의 기준에 rownum 붙이고 데이터 추출
--급여를 많이 받는 사원 5명을 뽑아라
select *
from (
select rownum as num, e.empno, e.ename, e.sal
from (
select empno, ename, sal
from emp
order by sal desc
) e
) n where num <=5;
게시판 페이징하기
둘다알아야 쿼리 해석 가능
1) rownum 만 사용
select *
from (
select rownum as num , e.*
from (
select *
from employees
order by employee_id asc
) e where rownum <=50
)n where num >= 41;
--where num >= ?;2) between을 사용
select *
from (
select rownum as num , e.*
from (
select *
from employees
order by employee_id asc
) e
)n where num between 41 and 50;행을 열로 전환하는 pivot
deptno , cnt
10 3
20 5
30 6
---------------------------------
deptno_10 , deptno_20 , deptno_30
3 5 6
select deptno, count(*) as cnt
from emp
group by deptno
order by deptno asc;
select sum(case when deptno=10 then 1 else 0 end) as dept_10,
sum(case when deptno=20 then 1 else 0 end) as dept_20,
sum(case when deptno=30 then 1 else 0 end) as dept_30
from emp;
월을 to_char()로 변환하기
select to_char(sysdate,'FMMM-DD') from dual; --0 없음
select to_char(sysdate,'MM-DD') from dual; -- 0 존재
pivot
select *
from (
select deptno, job, sal from emp
)
pivot(sum(sal) for job in('PRESIDENT' as p, 'ANALYST' as a, 'MANAGER' as m,
'SALESMAN' as s, 'CLERK' as c));
rollup : 직종별 합도 구하고 모든 직종의 총합도 구하고
cube : rollup은 여러 컬럼들의 총합을 구할 수없다. 그래서 여러개의 컬럼의 총합을 구할때는 cube를 사용한다.
물론 이 두개 다 union all을 통해 구성가능하다
0717 SQL 수업
/*
1.1 SEQUENCE 특징
1) 자동적으로 유일 번호를 생성합니다.
2) 공유 가능한 객체
3) 주로 기본 키 값을 생성하기 위해 사용됩니다.
4) 어플리케이션 코드를 대체합니다.
5) 메모리에 CACHE 되면 SEQUENCE 값을 액세스 하는 효율성을 향상시킵니다
CREATE SEQUENCE sequence_name
[INCREMENT BY n] -- 증가분
[START WITH n]
[{MAXVALUE n | NOMAXVALUE}] --999라면 그다음 값은 1이 된다 cycle로 지정 되어있을때
[{MINVALUE n | NOMINVALUE}]
[{CYCLE | NOCYCLE}] --nocycle은 순서번호 정지! maxvalue다음으로 증가 불가
[{CACHE | NOCACHE}]; --cache에 시퀀스 값을 미리 할당한다, nocache 시퀀스 값으 메모리에 미리할당하고 있지 않아
*/
drop table board;
create table board(
boardid number constraint pk_board_boardid primary key, --자동 index 생성
title nvarchar2(50)
);
desc board;
select * from user_constraints where table_name='BOARD';
--게시판 글쓰기 작업
--1번이라는 숫자를 자동으로 생성되길 원함
insert into board(boardid, title) values(1,'처음글');
insert into board(boardid, title) values(2,'두번째');
select * from board;
rollback;
--처음 글을 쓰면 글번호가 1... 그 다음글은 2 증가값... 100
--어떤 논리....
insert into board(boardid, title) values(1,'처음글');
--1을 만들기
select count(boardid)+1 from board;
--insert 문에 대입
insert into board(boardid, title) values((select count(boardid)+1 from board),'처음글');
insert into board(boardid, title) values((select count(boardid)+1 from board),'2번글');
insert into board(boardid, title) values((select count(boardid)+1 from board),'3번글');
delete from board;
delete from board where boardid=1;
--글을 삭제 했을시 그 다음 글번호가 중복이 될 수 있다는 문제 점이 있다.
commit;
insert into board(boardid, title) values((select count(boardid)+1 from board),'4번글');
--중복값이 나와서 논리 실패(글이 삭제 되었을 때 순번을 생성하면 중복값 발생)
delete from board;
commit;
--글이 삭제 되었을때 순번을 생성하면 중복값 발생 문제를 해결
select nvl(max(boardid),0) +1 from board;
insert into board(boardid,title) values((select nvl(max(boardid),0) +1 from board),'새글');
--max는 행이 없을때 null을 반환 그래서 nvl함수를 사용한다.
--게시판의 글을 최신 것부터 나열한다.
select * from board order by boardid desc;
commit;
delete from board where boardid=3;
select *from board;
insert into board(boardid,title) values((select nvl(max(boardid),0) +1 from board),'새글');
--만약 2번글을 지우게 되면 문제가 생긴다 다음글은 3번인데 이미 3번이 있기 때문에
--시퀀스 생성하기 (순번 만들기, 채번기) 객체(create...)
--순차적인 번호를 생성하는 객체
create sequence board_num;
--번호 추출
--다음 번호를 알수 있는 필드명 --번호를 뽑아내고 기억함
select board_num.nextval from dual;
--현재 번호 확인(마지막 번호 확인)
select board_num.currval from dual;
insert into board(boardid,title) values(board_num.nextval,'새글');
--시퀀스 객체는 공유객체 이다.
--공유객체(static) : 하나의 테이블에 종속되지 않고 여러개의 테이블에서 사용가능하다.
--게시판마다 따로따로 쓰려면
--게시판 테이블
--자유게시판 board_num.nextval 3
--공지사항 board_num.nextval 1, 2
--판매게시판 board_num.nextval
----------------------------------------------
--자유게시판 board_1.nextval 1
--공지사항 board_2.nextval 1, 2
--판매게시판 board_3.nextval 1
-------------------------------------------------
--TIP)
--sequence 모든 DB에 있나?
--1. 오라클 (O)
--2. MS-SQL(2012버전 부터 있음)
--3. MY-SQL (X) mariadb와 엔진 같음
--4. Mariadb 무료 (https://mariadb.com/kb/ko/sequence-overview/)
--CREATE SEQUENCE s START WITH 100 INCREMENT BY 10;
--5. postgresql DB >> https://www.postgresql.org/docs/current/sql-createsequence.html
--------------------------------------------------------------------
--시퀀스를 만드는 전통적인 방법
--테이블에 종속적인 방법
/*
MS-SQL
create table board(boardnum int identity(1,1),,,,title)
insert into board(title) values('방가');
자동으로 컬럼삽입 boardnum > 1..2..3
테이블에 종속
My-sql
create table board(boardnum int auto_increment,,,,title)
insert into board(title) values('방가');
자동으로 컬럼삽입 boardnum > 1..2..3
이것도 테이블에 종속
*/
create sequence seq_num
start with 10
increment by 2;
select seq_num.nextval from dual;
select seq_num.currval from dual;
--가장 나중에 쓴글 (최신글)
select * from board order by boardid desc;
---------------------------------------------------------------
--rownum : 의사컬럼 : 실제 물리적으로 존재하는 컬럼이 아니고 논리적 존재
--create table 에서 사용 못해요...
--rownum: 실제로 테이블에 컬럼으로 존재하지 않지만 내부적으로 행번호를 부여하는 컬럼
select * from emp;
select rownum, empno, ename, sal from emp;
--select 한 데이터에 순번을 붙이는 것이다
--순번이 뒤죽박죽 된다.
select rownum, empno, ename, sal
from emp
order by sal; -- 이미 select 된것을 order by 를 통해서 sal을 기준으로 정렬하니까 rownum값이이렇게 됌
--실행순서 >> from >> select >> select 한 데이터 순번(rownum)>> order by
--서브쿼리를 사용하면 rownum의 순서가 제대로 나온다.
select rownum, e.*
from (
select empno, ename, sal
from emp
order by sal -- 이미 정럴된 데이터를 기반으로 rownum을 붙이면 해결
) e;
--Top n 쿼리 ("기준"이 되는 데이터 순으로 "정렬"시키고 상위 n 개의 데이터 가져오기)
--ms-sql : select top 10 , * from emp order by sal desc;
--오라클은 Top n (X)
--rownum 사용 ( 순번부여...상위 n)
--1.정렬의 기준 설정하기(선행)
--2. 정렬의 기준에 rownum 붙이고 데이터 추출
--급여를 많이 받는 순으로 정렬된 데이터를 만든다
select rownum, e.*
from (
select empno, ename, sal
from emp
order by sal desc
) e;
--급여를 많이 받는 사원 5명을 뽑아라
select *
from (
select rownum as num, e.empno, e.ename, e.sal
from (
select empno, ename, sal
from emp
order by sal desc
) e
) n where num <=5; -- 대용량 데이터 페이징처리 (Today point)
--between A and B 페이징 쿼리
----------------------------------------------------------------------
--기업 환경 ( 10만건 ~ 1억건)
-- 게시판 10만건
--10만건을 나누어서 조회(10건씩, 15건 씩)
/*
totaldata = 102건
pagesize = 10 (100건의 데이터를 10개씩 묶어서 만들겠다)
page 개수 >> 11개
[1][2][3][4][5][6][7][8][9][10]
<a href="page.jsp?page=1">1</a>
1page 클릭 > 1~10번 글 : select ... where num between 1 and 10
2page 클릭 >> 11~20번글 :select ... where num between 11 and 20
밑에 두개를 사용하면된다.
1. rownum
2. between
*/
-------------------------------------------------------------------------------------
--HR계정
show user;
select * from employees;
/*
총데이터 건수 : 107건
pagesize=10
[1] 1 ~ 10
[2] 11 ~ 20
....
[5] 41 ~ 50 클릭
*/
-- 1단계 (기준 데이터 생성하기 : 정렬)
-- 사번이 낮은 순으로 정렬
select * from employees order by employee_id asc;
--2단계 (기준 데이터에 순번 부여하기)
select rownum as num , e.*
from (
select *
from employees
order by employee_id asc
) e where rownum <=50;
--3단계 (추출)
select *
from (
select rownum as num , e.*
from (
select *
from employees
order by employee_id asc
) e where rownum <=50
)n where num >= 41;
--where num >= ?;
--이래서 서브쿼리안에 서브쿼리로 쓰라는 거구나 편안하네
--게시판 만들기의 페이징 처리
--활용 : servlet / jsp/ spring
-------------------------------------------------------------------------------
select *
from (
select rownum as num , e.*
from (
select *
from employees
order by employee_id asc
) e
)n where num between 41 and 50;
-- rownum도 편하지만 between도 편하다 둘다알아야 쿼리 해석 가능
-------------------------------------------------------------------------------
--분석함수(통계함수)
--OLTP - OLAP
-- 기본 (decode, case, union)
-- 피벗 (집계된 행 데이터 >> 열 데이터로 바꾸기)
-- 피벗 (집계된 열 데이터 >> 행데이터로 바꾸기)
/*
행을 열로 전환하기
집계함수...
deptno , cnt
10 3
20 5
30 6
---------------------------------
deptno_10 , deptno_20 , deptno_30
3 5 6
*/
select deptno, count(*) as cnt
from emp
group by deptno
order by deptno asc;
/*
한 건 (row)
@Data
class Data{private int deptno, private int cnt}
Data data = new Data(10,3);
여러 건(row)
List<Data> list = new ArrayList<>();
list.add(new Data(10,3));
list.add(new Data(20,5));
list.add(new Data(30,6));
--for문 돌면서 뿌리면 됌
*/
select deptno , case when deptno=10 then 1 else 0 end as dept_10,
case when deptno=20 then 1 else 0 end as dept_20,
case when deptno=30 then 1 else 0 end as dept_30
from emp
order by deptno asc;
--위에 컬럼명에서 나온 값들ㅇ르 더해주고 싶다
select deptno , sum(case when deptno=10 then 1 else 0 end) as dept_10,
sum(case when deptno=20 then 1 else 0 end) as dept_20,
sum(case when deptno=30 then 1 else 0 end) as dept_30
from emp
group by deptno
order by deptno asc;
--deptno 의미 필요 없어요
--컬럼명으로 부서를 구분 :dept_10,dept_20,dept_30
select sum(case when deptno=10 then 1 else 0 end) as dept_10,
sum(case when deptno=20 then 1 else 0 end) as dept_20,
sum(case when deptno=30 then 1 else 0 end) as dept_30
from emp;
select *
from (
select deptno, count(*) as ecount
from emp
group by deptno
) x;
select case when deptno=10 then ecount else null end as dept_10,
case when deptno=20 then ecount else null end as dept_20,
case when deptno=30 then ecount else null end as dept_30
from (
select deptno, count(*) as ecount
from emp
group by deptno
) x;
--ex) 월별 매출 통계를 낼때 어떤 상품을 구매했는지 보여줄때
select max(case when deptno=10 then ecount else null end) as dept_10,
max(case when deptno=20 then ecount else null end) as dept_20,
max(case when deptno=30 then ecount else null end) as dept_30
from (
select deptno, count(*) as ecount
from emp
group by deptno
) x;
--row데이터를 열데이터로 보여주고 싶을때 방법은 여러가지이다
--통계 , 차트
--교재 p208
/*
select *
from (피벗 대상 쿼리문) --서브 쿼리
pivot(그룹함수(집계함수) for 피벗 컬럼 in(피벗컬럼값 as 별칭....)
오라클 11g부터 pivot 지원...
기존 이하 버전에는 decode를 row데이터를 컬럼으로 변경
통계, 차트 데이터 만들때 사용
*/
--직종별 월별 입사 건수
select * from emp;
select to_char(sysdate,'FMMM-DD') from dual; --0 없음
select to_char(sysdate,'MM-DD') from dual; -- 0 존재
select empno, job , to_char(hiredate,'FMMM')||'월' as hire_month
from emp
order by job;
/*
1월 , 2월 , 3월, 4월, ......12월
CLERK 2 0 0 0
MANAGER 0 0 0 1
*/
--분석통계는 쿼리를 따로 빼두는게 낫다
--pivot은 샘플을 몇개 만들어서 변경만 해서 사용한다.
select *
from (
select job, to_char(hiredate,'FMMM')||'월' as hire_month
from emp
)pivot(
count(*) for hire_month in ('1월' as "1월",
'2월'as "2월",
'3월','4월','5월','6월','7월','8월','9월','10월','11월','12월')
);
--pivot에 서 컬럼명지정가능
--decode 구현한다면
select job,
sum(decode(to_char(hiredate, 'FMMM'),'1',1,0)) as "1월",
sum(decode(to_char(hiredate, 'FMMM'),'2',1,0)) as "2월",
sum(decode(to_char(hiredate, 'FMMM'),'3',1,0)) as "3월",
sum(decode(to_char(hiredate, 'FMMM'),'4',1,0)) as "4월",
sum(decode(to_char(hiredate, 'FMMM'),'5',1,0)) as "5월",
sum(decode(to_char(hiredate, 'FMMM'),'6',1,0)) as "6월",
sum(decode(to_char(hiredate, 'FMMM'),'7',1,0)) as "7월",
sum(decode(to_char(hiredate, 'FMMM'),'8',1,0)) as "8월",
sum(decode(to_char(hiredate, 'FMMM'),'9',1,0)) as "9월",
sum(decode(to_char(hiredate, 'FMMM'),'10',1,0)) as "10월",
sum(decode(to_char(hiredate, 'FMMM'),'11',1,0)) as "11월",
sum(decode(to_char(hiredate, 'FMMM'),'12',1,0)) as "12월"
from emp
group by job;
--직종별, 부서별 급여 합계를 구하세요
select job, deptno, sum(sal) , count(sal)
from emp
group by job, deptno
order by job;
select *
from (
select job, deptno, sal from emp
)
pivot(--피벗안의 비교하는 애들은 다 문자열 처리가 되어있다.
sum(sal) for deptno in ('10' as d10,'20' as d20,'30' as d30)
);
-----------------------------------------------------------------
select *
from (
select deptno, job, sal from emp
)
pivot(sum(sal) for job in('PRESIDENT' as p, 'ANALYST' as a, 'MANAGER' as m,
'SALESMAN' as s, 'CLERK' as c));
--이런 결과가 나오도록 decode를 사용해서 만들어보세요
select deptno, sum(decode(job,'PRESIDENT',sal,0)) p,
sum(decode(job,'ANALYST',sal,0)) a,
sum(decode(job,'MANAGER',sal,0)) m,
sum(decode(job,'SALESMAN',sal,0)) s,
sum(decode(job,'CLERK',sal,0)) c
from emp
group by deptno;
--case를 이용해서 만들어보세요
SELECT DEPTNO, SUM(CASE WHEN JOB='PRESIDENT' THEN SAL END) AS E_PRESIDENT
, SUM(CASE WHEN JOB='ANALYST' THEN SAL END) AS E_ANALYST
, SUM(CASE WHEN JOB='MANAGER' THEN SAL END) AS E_MANAGER
, SUM(CASE WHEN JOB='SALESMAN' THEN SAL END) AS E_SALESMAN
, SUM(CASE WHEN JOB='CLERK' THEN SAL END) AS E_CLERK
FROM EMP
GROUP BY DEPTNO
ORDER BY DEPTNO DESC;
------------------------------------------------------------------------------
--사원테이블에서 부서별 급여합계와 전체 급여합을 출력하세요
select deptno, sum(sal)
from emp
group by deptno
union all
select null, sum(sal) sumsal from emp;
------------------------------------------------------------------------------
--다차원 분석 함수(레포팅 : OLAP)
--rollup, cube
select job , sum(sal)
from mep
group by rollup(job); --나는 직종별 급여의 합도 구하고.. 모든 직종의 급여의 합도 구할 거야
--union을 사용해도 할 수 있다.
select job, deptno, sum(sal), count(sal)
from emp
group by job, deptno
order by job, deptno;
select job,deptno, sum(sal)
from emp
group by rollup(job,deptno);
/*
CLERK 10 1300
CLERK 20 1900
CLERK 30 950
CLERK 4150--소계
ANALYST 20 6000
ANALYST 6000--소계
MANAGER 10 2450
MANAGER 20 2975
MANAGER 30 2850
MANAGER 8275--소계
SALESMAN 30 5600
SALESMAN 5600--소계
PRESIDENT 10 5000
PRESIDENT 5000--소계
29025--전체합
*/
--직종별 총합, 전체합
--우측 끝 컬럼부터 연산에서 제외 하므로 컬럼의 순서가 중요
select job,deptno, sum(sal)
from emp
group by rollup(deptno,job);--deptno를 기준으로 소계
--기준소계: deptno 소계, job소계 , 전체합계
-->아주 일반적으로 union하는 방법이 잇다.
select deptno , job, sum(sal)
from emp
group by deptno, job
union all
select deptno, null, sum(sal)
from emp
group by deptno
union all
select null, job, sum(sal)
from emp
group by job
union all
select null, null, sum(sal)
from emp;
--rollup의 단점은 모든 컬럼의 집계가 안된다.
--cube 모든 컬럼의 소계
select deptno, job, sum(sal)
from emp
group by cube(deptno, job)
order by deptno, job;
------------------------------------------------------------------------
--순위함수...JDBC연결하기
오라클은 설치하면 jdbc를 기본으로 제공한다.
C:\oraclexe\app\oracle\product\11.2.0\server
C:\oraclexe\app\oracle\product\11.2.0\server\jdbc\lib
C:\DataBase\ConnectionUtils\Oracle
프로젝트에 jdbc사용을 위한 jar를 파일을 추가한다.
클래스 로딩해서 사용하기
자바를 통해서 sql 검색후 출력하기
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
/*
JDBC
1. Java 언어(APP)를 통해서 Oracle(소프트웨어) 연결해서 CRUD작업
2. Java App : Oracle , My-sql , MS-sql 등등 연결하고 작업(CRUD)
2.1 각각의 제품에 맞는 드라이버를 가지고 있어야 합니다
CASE 1: 삼성 노트북 >> HP 프린터 연결 >> HP프린터 사이트에서 드라이버 다운 >> 삼성 설치
CASE 2: HP프린터 제조 회사는 ... 삼성, LG 회사마다 적용할 수 있는 드라이버를 별도 제작
각 언어에 맞는 드라이버를 다운로드 해서 제품에 맞게 설치 .... 접속 ...
Oracle (C:\oraclexe\app\oracle\product\11.2.0\server\jdbc\lib)
Mysql (https://dev.mysql.com/downloads/connector/j/)
강사PC : C:\KOSA_IT\DataBase\JDBC\ConnectionUtils\Oracle >> ojdbc6.jar
3. 드라이버를 참조 (현재 프로젝트에서 사용) >> Java Project -> 속성 -> build path ->
jar 파일 추가
3.1 드라이버 사용 준비 완료 >> 메모리에 load 사용 ....
3.2 Class.forName("oracle.jdbc.OracleDriver")..... new 객체 생성과 동일 한 원리
4. JAVA CODE (CRUD) >> JDBC API 제공 받는다
4.1 import java.sql.* >> interface , class 제공
4.2 개발자는 interface 를 통해서 표준화된 DB 작업 수행
POINT) why interface형태로 제공 >> JDBC API >> Oracle, Mysql , ....)
//OracleConnection >> Connection 구현
//MysqlConnection >> Connection 구현
//다형성 Connection 부모타입 : 유연한 프로그래밍 작성
>>다형성을 구현 (인터페이스 활용)
import java.sql.Connection
import java.sql.ResultSet
import java.sql.Statement 등등
5. 작업순서
5.1 DB연결 -> 명령생성 -> 명령실행 -> 처리 -> 자원해제
5.1 명령 : DDL (create , alter , drop)
CRUD (insert , select , update , delete)
실행 : 쿼리문을 DB서버에게 전달
처리 : 결과를 받아서 화면 출력 , 또는 다른 프로그램에 전달 등등
자원해제 : 연결해제
*/
public class Ex01_Oracle_Connection {
public static void main(String[] args) throws ClassNotFoundException, SQLException {
//Db 연결 - > 명령 생성(CRUD) -> 실행 -> 처리
Class.forName("oracle.jdbc.OracleDriver");//OracleDriver oracle = new OracleDriver();
System.out.println("오라클 드라이버 로딩...");
//loading된 드라이버를 통해서 oracle 서버 연결 //연결하려면 IP,포트, 사용자패스워드 필요
//연결 객체의 주소값을 리턴 이걸 인터페이스로 받는다
//주소가 connection클래스를 상속받은 형태로 존재함
//class Oracleaddr implements Connection{}
//new addr();
//class Mysqladdr implments Connection{}
Connection conn = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:xe","KOSA","1004");
// 연결주소, 포트번호, 전역데이터베이스명, 사용자, 사용자 패스워드
System.out.println(conn.isClosed()+"정상 false");//연결 닫혀있냐? 아니 안닫혔어 true: 닫힘
//명령 객체 얻어오기
Statement stmt = conn.createStatement();//명령객체의 주소를 받아온다
//CRUD 가능
String sql = "select empno, ename, sal, comm from emp";
//DB에서 쿼리를 수행한후 결과 값을 resultset에 저장
ResultSet rs = stmt.executeQuery(sql);
//
ResultSetMetaData rsmd =rs.getMetaData();
//구조적인 정보를 볼 수 있다.
System.out.println("Total columns : "+rsmd.getColumnCount());
System.out.println("column name : "+rsmd.getColumnName(1));
System.out.println("column type : "+rsmd.getColumnTypeName(1));//오라클타입이 나온다.NUMBER
//가공
while(rs.next()) {//데이터 건수 만큼 반복된다.
System.out.println(rs.getInt("empno")+" / "+rs.getString("ename")+" / "
+ rs.getInt("sal")+" / "+rs.getInt("comm"));
}
//
stmt.close();
rs.close();
conn.close();
System.out.println("DB연결 "+ conn.isClosed());//어 연결 끊어 졌어 결과: true
}
}반복적으로 사용되는 코드는 밖으로 뺀다
package kr.or.kosa.utils;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
/*
JDBC 작업 ( 5가지 함수 : 전체 조회, 조건, 삽입, 삭제, 수정 : class EmpDao{5개 함수})
5개의 함수 공통적으로 사용하는게 있다
1. 드라이버 로딩
2. 연결객체 생성, 명령, 자원해제
3. 반복적인 코드 제거.....
반복적인 코드를 가지고 있는 별도의 클래스
>>ConnectionHelper
DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:xe","KOSA","1004");
ConnectionHelper 설계
함수 ... 자주사용 > new하지 않고 static 으로 설계> 함수 종류 ....>>overloading을 사용한다.>>다형성
* */
public class ConnectionHelper {
public static Connection getConnection(String dsn) { //oracle , mysql 도 연결
Connection conn = null;
try {
if(dsn.equals("oracle")) {
conn = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:xe","KOSA","1004");
}else if(dsn.equals("mysql")) {
conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/sampledb?characterEncoding=UTF-8&serverTimezone=UTC&useSSL=true","KOSA","1004");
}
} catch (Exception e) {
System.out.println(e.getMessage());
}
return conn;
}
public static Connection getConnection(String dsn,String id, String pwd) {//oracle, mysql도 연결
Connection conn = null;
try {
if(dsn.equals("oracle")) {
conn = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:xe",id,pwd);
}
else if(dsn.equals("mysql")) {
conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/sampledb?characterEncoding=UTF-8&serverTimezone=UTC&useSSL=true",id,pwd);
}
}catch(Exception e) {
System.out.println(e.getMessage()); //에러메세지만e;.printstack();에러메세지와 몇번째줄에서 에러났는지 까지
}
return conn;
}
public static void close(Connection conn) {
if(conn!= null) {
try {
conn.close();
} catch (Exception e) {
System.out.println(e.getMessage());
}
}
}
public static void close(Statement stmt) {
if(stmt!=null) {
try {
stmt.close();
}catch(Exception e) {
System.out.println(e.getMessage());
}
}
}
public static void close(ResultSet rs) {
if(rs!=null) {
try {
rs.close();
}catch(Exception e) {
System.out.println(e.getMessage());
}
}
}
}그러면 아까 그 파일을 이렇게 쓸 수있다.
import java.sql.Connection;
import java.sql.SQLException;
import kr.or.kosa.utils.ConnectionHelper;
public class Ex02_Oracle_Helper_Connection {
public static void main(String[] args) throws SQLException {
Connection conn = ConnectionHelper.getConnection("oracle");
System.out.println(conn.isClosed());
ConnectionHelper.close(conn);
System.out.println(conn.isClosed());
}
}
패키지이름을 왜 도메인으로 할까?
패키지끼리 충돌 나지 말라고
mysql의 JDBC는다운 받아 사용해야한다.
https://dev.mysql.com/downloads/
MySQL :: MySQL Community Downloads
The world's most popular open source database Contact MySQL | Login | Register
dev.mysql.com
HTML대신에 데이터베이스 테이블에 대한 리포트쓰는 툴
오즈 이게 더 깔끔
https://www.forcs.com/kr/oz-report/
크리스탈 리포트 도 있다
DB파악할때 시퀀스 있나 확인 해야겠다
https://mariadb.com/kb/ko/sequence-overview/
Sequence Overview
Object that generates a sequence of numeric values.
mariadb.com
신규 프로젝트에서 사용되는 요새트렌드 시퀀스 갖고 있음
PostgreSQL
The world's most advanced open source database.
www.postgresql.org
공공기관 개발 시에는 인터넷 막기 때문에 java document를 읽을 줄 알아야한다.