[๊ตฌ๋์์นด๋ฐ๋ฏธ IT๊ตญ๋น์ง์] ์ค๋ผํด์ ์ด์ฉํ์ฌ sql์ ๋ํ ์ด๋ก ์ ๋ฐฐ์ฐ๊ณ , ๋จ๋ ์๊ฐ์๋ ์นดํ
๊ณ ๋ฆฌ๋ณ ํ์์ ์ฉ ๊ฒ์ํ ํ๋ก์ ํธ ์์
์ ํ๋ค.
-- ํ์ค ์ฃผ์
/* ์ฌ๋ฌ์ค ์ฃผ์ */
-- 1. ํ
์ด๋ธ์ ๊ตฌ์กฐ ํ์ธ
desc emp; -- empํ
์ด๋ธ์ ๊ตฌ์กฐ๋ฅผ ํ์ธํ๋ค describe emp์ ์ฝ์ํํ์
-- 2. [select] ๋ชจ๋ ์ด๊ณผ ๋ชจ๋ ํ์ ์กฐํ
select * -- 2
from emp; -- 1
-- 3. [select] ๋ชจ๋ ์ด๊ณผ ๋ชจ๋ ํ์ ์กฐํ (์ด์ ์์๋ฅผ ์ง์ )
select deptno, empno, ename, job, mgr, hiredate, sal, comm -- 2
from emp; -- 1
-- 4. [select] ์ํ๋ ์ด๊ณผ ๋ชจ๋ ํ์ ์กฐํ
select empno, ename, sal, comm -- 2
from emp; -- 1
-- 5. [select] ์กฐํ๋ ๊ฒฐ๊ณผ๋ฌผ(ResultSet=๊ฒฐ๊ณผ์
)์ ์ด ์ด๋ฆ์ ๋ณ์นญ(as) ์ฌ์ฉ
select empno as ๋ฒํธ, ename as ์ด๋ฆ -- 2
from emp; -- 1
-- as ํค์๋๋ ์๋ต๊ฐ๋ฅ
select empno ๋ฒํธ, ename ์ด๋ฆ -- 2
from emp; -- 1
-- 6. [select] ์๋ณธ ํ
์ด๋ธ์ ์กด์ฌํ์ง ์๋ ์๋ก์ด ์ด์ ์ถ๊ฐํ์ฌ ์กฐํ(์ถ๋ ฅ)
-- 6-1) ๊ฐ
-- ๋ฌธ์๊ฐ์ ('') ์ฌ์ฉ
-- ์๋ก์ด ๊ฐ์ ์ปฌ๋ผ๋ช
์ด ์์ผ๋ฏ๋ก ๋ณ์นญ์ ์ด์ฉํ๋ฉด ๋๋ค
select empno, ename, 100 ์ ์, 'ํ๊ตญ' ๋๋ผ -- 2
from emp; -- 1
-- 6-2) ๊ณ์ฐ์(ํจ์)
-- ๋จ์ ๊ณ์ฐ์ ์ธ์๋ ๋ฐ์ดํฐ ๋ฒ ์ด์ค์ ๊ฐ์ ์ด์ฉํ ๊ณ์ฐ์๋ ๊ฐ๋ฅ
-- || : ๋ฌธ์์ด์ ํฉ์น๋ ์ฐ์ฐ์
-- nvl(x,y) : x๊ฐ null์๋๋ฉด x๊ฐ์, null์ด๋ฉด y๊ฐ์ ๋ฐํ
select empno, ename, 10*5, sal, sal*2, ename||' '||job, comm, nvl(comm, 0)
from emp;
-- 6-3) ์ค์นผ๋ผ ์๋ธ์ฟผ๋ฆฌ
-- ์๋ธ์ฟผ๋ฆฌ : ํ๋์ ์ฟผ๋ฆฌ ์์ ํฌํจ๋ ๋ ํ๋์ ์ฟผ๋ฆฌ(=์์์ฟผ๋ฆฌ)
-- ์ค์นผ๋ผ : ๊ฒฐ๊ณผ๊ฐ์ด ๋จ ํ๋์ ๊ฐ(ํ)์ด์ด์ผ ํ๋ฏ๋ก select๋ฌธ์์ where์ ์ ์ฌ์ฉ
select empno, ename, (select ename from emp where empno=7839) ceo
from emp;
-- 7. ์์ฌ์ปฌ๋ผ(rowid, rownum)์ ์ถ๊ฐํ์ฌ ์ถ๋ ฅ
-- ์์ฌ์ปฌ๋ผ : ํ
์ด๋ธ์ ์ปฌ๋ผ์ฒ๋ผ ๋์ํ์ง๋ง ์ค์ ๋ก ํ
์ด๋ธ์ ์ ์ฅ๋์ง ์๋ ์ปฌ๋ผ
/* rowid : ๋
ผ๋ฆฌ์ ์ธ ๋ฐ์ดํฐ ๋จ์(=ํ)์ ๊ฐ๋ฆฌํค๋ ์ฃผ์๊ฐ,
์ค๋ณต์ ์ผ๋ก ๋ฉ๋ชจ๋ฆฌ์ ์ ์ฅ๋์ง ์์ ์ ์ผํ ๊ฐ
rownum : select์ ์ด ์คํ๋๋ ์์ ์ ์์๋๋ก ํ๋ฒํธ๋ฅผ ๋ถ์ฌํ์ฌ ๊ฒฐ๊ณผ์
์ ์ถ๋ ฅ */
select rowid, rownum, empno, ename
from emp;
/* ์ฃผ์ํ ์ ) order by์ ๊ณผ rownum์ ๊ฐ์ด ์ฌ์ฉ์, order by์ ์ ๊ฐ์ฅ ๋ง์ง๋ง์
์คํ๋๊ธฐ ๋๋ฌธ์ ์ต์ข
๊ฒฐ๊ณผ์
์ rownum์ด ํญ์ ์์ฐจ์ ์ด์ง๋ ์๋ค! */
select rowid, rownum, empno, ename
from emp
order by ename asc;
-- 8. dual ํ
์ด๋ธ์ ์ด ์ถ๊ฐํ์ฌ ๊ณ์ฐํ๊ธฐ
/* dual ํ
์ด๋ธ : 1ํ์ ๊ฒฐ๊ณผ๊ฐ์ ์ถ๊ฐํ์ฌ ์ถ๋ ฅํ๊ณ ์ ํ ๋ ์ฌ์ฉํ๋ ํ
์ด๋ธ
ํจ์(๊ณ์ฐ)์ ๊ฒฐ๊ณผ๊ฐ์ ํ์ธํ๊ธฐ ์ํด ์์๋ก ์ฌ์ฉํ๊ธฐ ์ข๋ค */
desc dual;
-- 1ํ 1์ด
select * from dual;
/* sysdate : ์ค๋ ๋ ์ง๋ฅผ ๊ตฌํ๋ ํจ์, ํน์ ํ
์ด๋ธ์ ์ปฌ๋ผ์ด ์๋๊ธฐ ๋๋ฌธ์
from์ ๋ค์ ํน์ ํ
์ด๋ธ ๋ช
์ ์
๋ ฅํด์ผํ๋ค */
-- emp ํ์ ์๋งํผ ์ค๋ ๋ ์ง๊ฐ ์ถ๋ ฅ๋จ
select sysdate from emp;
-- dual์ 1ํ๋ง ๊ฐ์ง๊ธฐ ๋๋ฌธ์ ์ค๋ ๋ ์ง 1ํ๋ง ์ถ๋ ฅ๋จ
select sysdate from dual;
-- ๋จ์ ๊ณ์ฐ
select 10+3 from dual;
select 10-3 from dual;
select 10*3 from dual;
select 10/3 from dual;
select mod(10, 3) from dual; -- mod() : ๋๋จธ์ง๋ฅผ ๊ตฌํ๋ ํจ์
select 10+3, 10-3, 10*3, 10/3 from dual; -- 1ํ์ ๋ชจ๋ ๊ฒฐ๊ณผ๊ฐ์ ์ถ๋ ฅ
/* where์
: from์ ์ ๊ฒฐ๊ณผ๋ฌผ์ ํํ ํํ์ฉ where์ ์ ๋์
์์ผ์
where์ ์ ๊ฒฐ๊ณผ๊ฐ ์ฐธ์ธ ํ๋ง ๋จ๊ธด๋ค */
select * -- 3
from emp -- 1
where deptno = 10; --2
-- deptno๊ฐ 10์ธ ํ๋ง ์ถ๋ ฅ๋จ
select * -- 3
from emp -- 1
where 1 = 1; -- 2
-- 1 = 1;์ ๋ฌด์กฐ๊ฑด ์ฐธ์ด๋ฏ๋ก ๋ชจ๋ ํ์ด ์ถ๋ ฅ๋จ
select * -- 3
from emp -- 1
where 1 = 0; -- 2
-- 1 = 0;์ ๋ฌด์กฐ๊ฑด ๊ฑฐ์ง์ด๋ฏ๋ก ์๋ฌด ํ๋ ์ถ๋ ฅ๋์ง ์์
/* where์ ์์ rownum ์ฌ์ฉ์ ์ํ๋ ๊ฒฐ๊ณผ์
์ด ์ถ๋ ฅ๋์ง ์์ ์ ์๋ค
where์ ์ด select์ ๋ณด๋ค ๋จผ์ ์คํ๋๋ค.
์ฆ, from์ ๊ฒฐ๊ณผ์
์๋ rownum์ด ํฌํจ๋์ด ์์ง ์๋ค */
select rownum, ename
from emp
where rownum < 5;
-- rownum์ 1๋ถํฐ ์งํ๋๊ธฐ ๋๋ฌธ์ where์ ์ด ์ฐธ์ด๋ฏ๋ก ์คํ๋จ
select rownum, ename
from emp
where rownum > 5;
-- rownum์ 1๋ถํฐ ์งํ๋๋๋ฐ, 1>5๋ ๊ฑฐ์ง์ด๋ฏ๋ก ์คํ์์ฒด๊ฐ ๋์ง ์์
select *
from (select rownum rnum, ename
from emp) t
where rnum > 5;
-- ์ด๋ด ๋ ์ฌ์ฉํ๋ ๊ฒ์ด Inline View(์ธ๋ผ์ธ๋ทฐ)์ด๋ค
-- ์ธ๋ผ์ธ๋ทฐ : from์ ์ ์ฌ์ฉ๋๋ ์๋ธ์ฟผ๋ฆฌ
-- ์ฐ์ฐ์
/* 1. ๋น๊ต์ฐ์ฐ์
์ซ์, ๋ฌธ์, ๋ ์ง ๋น๊ต์ ๋ค ์ฌ์ฉ ๊ฐ๋ฅํ๋ค
=, !=, <>, >, <, >=, <=
like ์ฐ์ฐ์ : ๋ฌธ์์๋ง ์ฌ์ฉ ๊ฐ๋ฅ, ๋ฌธ์์์ ํน์ ์ผ๋ถ๋ง ๊ฐ์๋ ์ฐธ์ผ๋ก ๋ฐํ */
select *
from emp
where empno > 7839; -- ์ซ์๋น๊ต
select *
from emp
where ename < 'KING'; -- ๋ฌธ์๋น๊ต
-- ๋ฌธ์๊ฐ์ ์์๋ฐ์ดํ('')๋ฅผ ์จ์ผํ๋ค, ๋์๋ฌธ์๋ฅผ ๊ตฌ๋ถํ๋ค
select *
from emp
where hiredate <= sysdate; -- ํจ์๋น๊ต (๋ ์ง)
-- like ์ฐ์ฐ์
select *
from emp
where ename like 'S'; -- ename์ด S์ธ์ง
/* S% : S๋ก ์์, %S : S๋ก ๋๋จ, %S% S๊ฐ ๋ค์ด๊ฐ๋์ง
S_ : S๋ก ์์ํ๋ฉด์ ๋๊ธ์, S____ : S๋ก ์์ํ๋ฉด์ ๋ค์ฏ๊ธ์ */
-- null๊ฐ์ ๋น๊ต์ฐ์ฐ์๋ก ๋น๊ตํ ์ ์๋ค
-- is null ๋๋ is not null ์ฌ์ฉ
select *
from emp
where comm is not null;
select *
from emp
where comm is null;
-- null๊ฐ์ ์ผ๋ฐ ์ฐ์ฐ
-- ์ซ์์ฐ์ฐ์ ๋ฌด์กฐ๊ฑด null๊ฐ์ด ๋ฐํ๋๋ค
select null+10 from dual;
select null*10 from dual;
select mod(null, 10) from dual;
-- ๋ฌธ์์ฐ์ฐ์ null์ ๊ณต๋ฐฑ์ผ๋ก ๋ณํ ํ ์ฐ์ฐ๋๋ค
select null||'๊ตฌ๋' from dual;
-- null๊ฐ์ธ ๊ฒฝ์ฐ ์ซ์์ฐ์ฐ ํ๋ ๋ฐฉ๋ฒ
-- nvl ํจ์๋ฅผ ์ฌ์ฉ : null์ธ ๊ฒฝ์ฐ 0์ผ๋ก ์นํํ๋ค
select ename, sal+nvl(comm, 0) ์ด๊ธ์ฌ
from emp;
-- where์ ์ ์๋ธ์ฟผ๋ฆฌ
-- ๊ผญ ์ค์นผ๋ผ๋ง ๋ค์ด๊ฐ์ง๋ ์๋๋ค
select ename ์ด๊ธ์ฌ
from emp
where deptno = (select deptno from dept where dname = 'SALES');
-- 2. ๋
ผ๋ฆฌ์ฐ์ฐ์
-- or ์ฐ์ฐ์
select *
from emp
where empno = 7839 or deptno = 30;
select *
from emp
where empno = 7369 or empno = 7788 or empno = 7900;
-- IN์ ์ฌ์ฉํ๋ฉด ๋ ๊ฐ๋
์ฑ ์ข๊ฒ ์์ฑํ ์ ์๋ค
select *
from emp
where empno in (7369, 7788, 7900); -- ์ ์ฟผ๋ฆฌ์ ๋์ผํ ์ฟผ๋ฆฌ
select *
from emp
where comm = 0 or comm is null;
-- ๋จ, null ๋น๊ต์์๋ IN์ ์ฌ์ฉํ ์ ์๋ค
select *
from emp
where comm in (0, null);
-- comm = 0 or comm = null ๋ก ํด์๋๋ฏ๋ก
-- and ์ฐ์ฐ์
select *
from emp
where deptno = 30 and sal < 2000;
select *
from emp
where sal >= 1500 and sal <=3000;
-- between์ ์ฌ์ฉํ๋ฉด ๋ ๊ฐ๋
์ฑ ์ข๊ฒ ์์ฑํ ์ ์๋ค
select *
from emp
where sal between 1500 and 3000; -- ์ ์ฟผ๋ฆฌ์ ๋์ผํ ์ฟผ๋ฆฌ
-- any ์ฐ์ฐ์์ all ์ฐ์ฐ์
-- any ์ฐ์ฐ์ : ํ๊ฐ๋ผ๋ ๋ง์กฑํ๋ฉด ์ฐธ์ด๋ค, or์ฐ์ฐ์์ ๋์ผํ ์ญํ
-- in๊ณผ์ ์ฐจ์ด์ ์ ๋น๊ต์ฐ์ฐ์๋ฅผ ์ฌ์ฉํ๋ค
select *
from emp
where sal > any(1000, 3000); -- sal > 1000
-- all ์ฐ์ฐ์ : ๋ชจ๋ ๋ง์กฑํ๋ฉด ์ฐธ์ด๋ค, and์ฐ์ฐ์์ ๋์ผํ ์ญํ
select *
from emp
where sal > all(1000, 3000); -- sal < 3000
-- any ์ฐ์ฐ์์ all ์ฐ์ฐ์๋ ์๋ธ์ฟผ๋ฆฌ์์ ์ฃผ๋ก ์ฌ์ฉ๋๋ค
select *
from emp
where deptno in (select deptno from dept where deptno < 30);
select *
from emp
where deptno > all (select deptno from dept where deptno < 30);
select *
from emp
where deptno > any (select deptno from dept where deptno < 30);
select * from emp; -- 1๋ฒ ๊ฒฐ๊ณผ์
select empno ๋ฒํธ, sysdate ์ค๋๋ ์ง -- 3
from emp -- 1
where 1=1; -- 2
select ename, deptno -- 3
from emp e -- 1 ํ
์ด๋ธ ๋ณ์นญ์ ์๋ต ๊ฐ๋ฅ
where e.deptno = 30; -- 2 ํ
์ด๋ธ ๋ณ์นญ์ ์๋ต ๊ฐ๋ฅ
select ename, deptno ๋ฒํธ -- 3
from emp -- 1
where ๋ฒํธ = 30 -- 2
order by ename desc; -- 4
-- select์ ์์ ์์ฑํ ๋ณ์นญ์ ์์์ where์ ์์ ์ฌ์ฉํ ์ ์์ผ๋ฏ๋ก ์ค๋ฅ ๋ฐ์
select ename, deptno ๋ฒํธ -- 3
from emp -- 1
where deptno = 30 -- 2
order by ๋ฒํธ desc; -- 4
-- order by์ ์ ๊ฐ์ฅ ๋ง์ง๋ง์ ์คํ๋๋ฏ๋ก ๋ณ์นญ ์ฌ์ฉ ๊ฐ๋ฅ
select ename, deptno, job
from emp
where deptno = 30
order by 3 desc;
-- order by์ ์ ์ซ์ 3 ์ฌ์ฉ์, select์ ์ ์ธ๋ฒ์งธ ์ปฌ๋ผ์ ๊ธฐ์ค์ผ๋ก ์ ๋ ฌํ๋ค
/* *๋ก ์์ฑํ๋๋ผ๋ select์ ์ ๊ฒฐ๊ณผ์
์ ์ด๋ฏธ ๋์จ ์ํ์ด๊ธฐ ๋๋ฌธ์ ๊ทธ ๊ฒฐ๊ณผ์
์ ๊ธฐ์ค์ผ๋ก
์ปฌ๋ผ๋ช
์ ์ซ์์ ๋ง๊ฒ ์ ํํ๋ค */
select ename, deptno
from emp
where deptno = 30
order by job desc;
-- select์ ์ ์ ํ์์ง ์๋ ์นผ๋ผ๋ช
์ด๋๋ผ๋ order by์ ์์ ์ง์ ๊ฐ๋ฅ
-- ๊ทธ ์๋จ๊ณ์ธ from์ด๋ where์ ๊น์ง ์ฌ๋ผ๊ฐ์ ๊ฐ์ ์ฐพ์์ ์ ๋ ฌํ๊ธฐ ๋๋ฌธ์ด๋ค
/* ๋จ, ์ซ์๋ก ์ง์ ํ๋ ๊ฒฝ์ฐ์๋ ๋ฐ๋ก ์๋จ๊ณ์ธ select์ ๋ง์ด ๊ธฐ์ค์ด๊ธฐ ๋๋ฌธ์
select์ ์ ์กด์ฌํ์ง ์๋ ์ซ์(์นผ๋ผ)์ผ๋ก order by๋ฅผ ์ง์ ํ ์ ์๋ค */
-- ์์ฉ
select deptno, count(*) cnt -- 5
from emp -- 1
where deptno is not null -- 2
group by deptno -- 3
having count(*) <= 5 -- 4 ์์์ ๋ณ์นญ ์ฌ์ฉํ ์ ์๋ค
order by cnt asc; -- 6
-- from 1๋ฒ ๊ฒฐ๊ณผ์
select * from emp;
-- group by 2๋ฒ ๊ฒฐ๊ณผ์
select * from emp where deptno = 10;
select * from emp where deptno = 20;
select * from emp where deptno = 30;
select * from emp where deptno is null;
select empno, ename, 15 ์ ์ฒด์ธ์, sysdate ์ค๋๋ ์ง, rowid, rownum
from emp
where rownum < 3;
-- where rownum > 3 ์ ๊ฐ๋ฅํ์ง ์์
select empno, ename, 15 ์ ์ฒด์ธ์, sysdate ์ค๋๋ ์ง, rowid, rownum
from emp
where rownum >= 1 and rownum <= 10;
-- ์๋ธ์ฟผ๋ฆฌ(=์์์ฟผ๋ฆฌ)
/* ๋ถ๋ชจ์ฟผ๋ฆฌ๋ณด๋ค ๋จผ์ ์คํ๋์ด ๊ฒฐ๊ณผ์
์ ๋จ๊ธด๋ค
์ด ๊ฒฐ๊ณผ์
์ ํ๋์ ํ
์ด๋ธ ๊ฐ์ผ๋ก ๋ณด๊ณ from์ ๋ค์์ ์ฌ์ฉ ๊ฐ๋ฅ */
-- ๋ฉํฐ ๊ฒฐ๊ณผ์
: ํ
์ด๋ธ (= ์ธ๋ผ์ธ๋ทฐ)
select ename, ์ ์ฒด์ธ์, rid, rnum, rownum -- ์ต์ข
๊ฒฐ๊ณผ์ rownum์
from
(select empno, ename, 15 ์ ์ฒด์ธ์, sysdate ์ค๋๋ ์ง, rowid rid, rownum rnum
from emp) -- 1 ํ๋์ ํ
์ด๋ธ๋ก ๋ณธ๋ค
where rnum >= 11 and rnum <= 15;
/* ๊ฐ์ ๊ฒฐ๊ณผ์
์ ๋จ๊ฒจ์
select์ ๋๋ where์ ์ ์ฌ์ฉ ๊ฐ๋ฅ */
-- ๊ฐ : ๋จ์ผํ ๊ฒฐ๊ณผ์
, select์ (=์ค์นผ๋ผ ์๋ธ์ฟผ๋ฆฌ)
select empno, ename, (select count(*) from emp) ์ ์ฒด์ธ์
from emp;
-- ๊ฐ : ๋จ์ผor์ฌ๋ฌํ ๊ฒฐ๊ณผ์
, where์ (=์๋ธ์ฟผ๋ฆฌ)
-- ๊ฐ์ด ๋จ์ผํ์ผ ๊ฒฝ์ฐ
select avg(sal) from emp;
select ename, sal
from emp
where sal > 2021.6;
-- ์ด๋ ๊ฒ ์์ฑํ ๋๊ฐ์ ์ฟผ๋ฆฌ๋ฅผ
select ename, sal
from emp
where sal > (select avg(sal) from emp);
-- ์๋ธ์ฟผ๋ฆฌ๋ฅผ ์ด์ฉํ๋ฉด ํ๋ฒ์ ์์ฑ ๊ฐ๋ฅ
-- ๊ฐ์ด ์ฌ๋ฌํ์ผ ๊ฒฝ์ฐ
select ename, deptno
from emp
where deptno = 10 or deptno = 20;
select ename, deptno
from emp
where deptno in (10, 20);
select ename, deptno
from emp
where deptno in (select deptno from dept where deptno <30);
select empno, ename, 15 ์ ์ฒด์ธ์, sysdate ์ค๋๋ ์ง, rowid, rownum
from emp
where rownum < 3;
-- where rownum > 3 ์ ๊ฐ๋ฅํ์ง ์์
where์ ์ ์ฒซ๋ฒ์งธ ํ๋ถํฐ ํ๋์ฉ, ์กฐ๊ฑด์ ๋์
ํ์ฌ true์ false๋ฅผ ๋ฐํํ๋ค. rownum์ ์ฟผ๋ฆฌ๊ฐ ์คํ๋ ๋ 1๋ถํฐ ์์ฐจ์ ์ผ๋ก ๊ฒฐ๊ณผ์
์ ์ถ๋ ฅ์ด ์ฐํ๋ ๊ฒ์ด๊ธฐ ๋๋ฌธ์ select๋ณด๋ค ๋จผ์ ์คํ๋๋ where์ ์์๋ ์ฌ์ฉ์ด ๊ฐ๋ฅํ๋ค. ๋จ, where์ ์ด true์ฌ์ผ ํํ์ฉ ์ถ๋ ฅ์ด ๋๊ธฐ ๋๋ฌธ์ rownum > 3์ด๋ผ๋ ์กฐ๊ฑด์ 1 > 3์ false๋ก ์ถ๋ ฅ์์ฒด๊ฐ ๋์ง ์์ rownum์ด ์ฐํ์ง ์๋๋ค. ๋ง์ฝ 1์ด ์๋๋ผ 4๋ถํฐ ์ถ๋ ฅํ๊ณ ์ถ๋ค๋ฉด? ์๋ธ์ฟผ๋ฆฌ๋ฅผ ์ฌ์ฉํ๋ฉด ๋๋ค!
-- rownum๊ณผ ์๋ธ์ฟผ๋ฆฌ๋ฅผ ์ด์ฉํ์ฌ ํ์ด์งํ๊ธฐ
/* ์ค๋ผํด์ limit๊ฐ ์๊ธฐ ๋๋ฌธ์ between A and B๋ฅผ ์ฌ์ฉ
๊ทธ๋์ ์๊ณ ๋ฆฌ์ฆ ์์ฒด๊ฐ ๋ฐ๋๋ค ?, ? ๊ฐ ์ค์์ ๋ง์ง๋ง ?๋ rowPerPage์ฌ์
๊ณ ์ ๋ ๊ฐ์ด์์ง๋ง, A์ B ์๋ฆฌ์ ๋ค์ด๊ฐ๋ ๋ ?๋ ๊ณ์ ๊ฐ์ด ๋ฐ๋๊ธฐ ๋๋ฌธ์ด๋ค. */
select rownum, employee_id, first_name, last_name
from
(select rownum, employee_id, first_name, last_name
from employees
order by employee_id desc)
where rownum between 11 and 20;
-- ์ด๋ ๊ฒ ํ๋ฉด ์๋ธ์ฟผ๋ฆฌ์ ํ
์ด๋ธ ์ ์ฒด์ ๋ค์ ์๋กญ๊ฒ rownum์ ๋ถ์ด๊ธฐ ๋๋ฌธ์ X
select rnum, employee_id, first_name, last_name
from
(select rownum rnum, employee_id, first_name, last_name
from employees
order by employee_id desc)
where rnum between 11 and 20;
-- ๊ทธ๋์ ์ด๋ ๊ฒ ๋ณ์นญ์ ์ฌ์ฉํด์ผํ๋ค
-- ์๊ณ ๋ฆฌ์ฆ ์ง๋ณด๊ธฐ
int rowPerPage = 10;
currentPage beginRow endRow
1 1 10
2 11 20
3 21 30
4 31 40
beginRow = (currentPage - 1) * rowPerPage + 1
endRow = beginRow + (rowPerPage - 1)
-- ์ฆ, ๊ธฐ์กด ์๊ณ ๋ฆฌ์ฆ๊ณผ ๋ค๋ฅด๊ฒ ?์ ๋ค์ด๊ฐ ๊ฐ์ beginRow์ endRow
/* ์ด์ /๋ค์ ์กฐ๊ฑด์ ๊ฑธ ๋ endRow์๊ฒ ์กฐ๊ฑด์ด ๋ค์ด๊ฐ์ผ ํ๋ค
lastPage์ผ ๋, endRow์ ๊ฐ์ totalCnt์ฌ์ผํจ */
String driver = "oracle.jdbc.driver.OracleDriver";
Class.forName(driver);
String dburl = "jdbc:oracle:thin:@localhost:1521:xe";
String dbuser = "****";
String dbpw = "****";
Connection conn = DriverManager.getConnection(dburl, dbuser, dbpw);
์ค๋ผํด๋ mariadb๋ฅผ ์ฌ์ฉํ ๋์ ๊ฐ์ด ๋๋ผ์ด๋ฒ jar ํ์ผ์ด ํ์ํ๋ค. ์ค๋ผํด์ ์ค์นํด๋ ์์ ํ์ผ์ด ๋ค์ด์๊ธฐ ๋๋ฌธ์ ๋ฐ๋ก ์ฐพ์์ ๋ค์ด๋ฐ์ ํ์ ์์ด ํด๋น ํ์ผ์ ํด๋์์ ์ฐพ์์ ์ฐ๋ฉด ๋๋ค.
์์น
C:\oraclexe\app\oracle\product\11.2.0\server\jdbc\lib
๋งค๊ฐ๋ณ์๋ ๋ง์ฐฌ๊ฐ์ง๋ก ์ฃผ์, ๊ณ์ ์ด๋ฆ, ์ํธ๊ฐ ๋ค์ด๊ฐ๋ค.
jdbc:oracle:thin:
๊ฐ ํ๋กํ ์ฝ์ด๊ณ , localhost
๋ mariadb ์ฐ๊ฒฐ์ ์์ฑํ๋ 127.0.0.1
์ ๋ณ์นญ์ด๋ค. ํฌํธ๋ฒํธ๋ ์ค์น์ ํ์ธํ ๋ฒํธ๋ฅผ ์
๋ ฅํ๋ฉด ๋๋ค. xe
๋ ์ธ์คํด์ค๋ช
์ด๋ค.
sql developer์์ ๋ชจ๋ ํ์ธ ๊ฐ๋ฅํ๋ค!
์ค๋์ ์นดํ
๊ณ ๋ฆฌ ์กฐํ/์ถ๊ฐ/์์ /์ญ์ ๊ธฐ๋ฅ์ ์ถ๊ฐํ๋ค. ์ด ๊ธฐ๋ฅ๋ค์ ํ์๋ง ์ ๊ทผ ๊ฐ๋ฅํ๋๋ก ํ๊ณ , ์นดํ
๊ณ ๋ฆฌ๋ ์ฌ์ฉ ์ค์ธ ์นดํ
๊ณ ๋ฆฌ(๊ฒ์๊ธ์ด ์กด์ฌํ๋ ์นดํ
๊ณ ๋ฆฌ)๋ ์์ /์ญ์ ๋ฅผ ํ ์ ์๋๋ก ํ๋ค.
ํ์์ด ๋ก๊ทธ์ธํ ์ํ์์๋ง ์นดํ ๊ณ ๋ฆฌ ๊ด๋ฆฌ ๋ฒํผ์ด ๋ํ๋๋๋ก ํ๊ธฐ ์ํด
// ๋ก๊ทธ์ธํ ํ์๋ง ์นดํ
๊ณ ๋ฆฌ ๊ด๋ฆฌ์ ์ ๊ทผ ๊ฐ๋ฅ
if(session.getAttribute("loginMemberId") != null) {
<a href="<%=request.getContextPath()%>/local/localList.jsp" class="btn btn-secondary">
⚙ ์นดํ
๊ณ ๋ฆฌ ๊ด๋ฆฌ
</a>
}
ํด๋น if๋ฌธ์ ์ถ๊ฐํ๋ค.
๐ ๊ฒฐ๊ณผ
๋ก๊ทธ์ธ ์
๋ก๊ทธ์ธ ํ ์นดํ
๊ณ ๋ฆฌ ๊ด๋ฆฌ ๋ฒํผ์ด ๋ํ๋๊ณ , ํด๋ฆญ์ localList.jsp๋ก ์ด๋ํ๋ค.
localList.jsp
๐ ์์ฑ
<%@ page language="java" contentType="text/html; charset=UTF-8" pageEncoding="UTF-8"%>
<%@ page import="java.sql.*" %>
<%@ page import="java.util.*" %>
<%@ page import="vo.*" %>
<%
// ์ธ์
์ ํจ์ฑ ๊ฒ์ฌ
if(session.getAttribute("loginMemberId") == null) {
response.sendRedirect(request.getContextPath() + "/home.jsp");
return;
}
String memberId = (String)session.getAttribute("loginMemberId");
System.out.println(memberId + " <- localList session loginMemberId");
// ๋ชจ๋ธ๊ฐ ๊ตฌํ๊ธฐ - localList ๋ชจ๋ธ
// ๋๋ผ์ด๋ฒ ๋ก๋ฉ ๋ฐ db ์ ์
String driver = "org.mariadb.jdbc.Driver";
String dburl = "jdbc:mariadb://127.0.0.1:3306/userboard";
String dbuser = "****";
String dbpw = "****";
Class.forName(driver);
Connection conn = DriverManager.getConnection(dburl, dbuser, dbpw);
// select ์ฟผ๋ฆฌ ์์ฑ // ๊ฐ์ฅ ์ต๊ทผ์ ์์ฑํ ์นดํ
๊ณ ๋ฆฌ๊ฐ ์๋จ์ผ๋ก ๊ฐ๋๋ก ์ ๋ ฌ
String sql = "SELECT local_name localName, createdate createdate FROM local ORDER BY createdate DESC";
PreparedStatement stmt = conn.prepareStatement(sql);
ResultSet rs = stmt.executeQuery();
System.out.println(stmt + " <- localList stmt");
// ArrayList๋ก ๋ณํ
ArrayList<Local> localList = new ArrayList<Local>();
while(rs.next()) {
Local l = new Local();
l.setLocalName(rs.getString("localName"));
l.setCreatedate(rs.getString("createdate"));
localList.add(l);
}
%>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>localList.jsp</title>
<!-- ๋ถํธ์คํธ๋ฉ5 ์ฌ์ฉ -->
<link href="https://cdn.jsdelivr.net/npm/bootstrap@5.2.3/dist/css/bootstrap.min.css" rel="stylesheet">
<script src="https://cdn.jsdelivr.net/npm/bootstrap@5.2.3/dist/js/bootstrap.bundle.min.js"></script>
</head>
<body>
<!-- include ํ์ด์ง : ๋ฉ์ธ๋ฉ๋ด(๊ฐ๋ก) -->
<div>
<!-- ์ก์
ํ๊ทธ -->
<jsp:include page="/inc/mainmenu.jsp"></jsp:include>
</div>
<!-------- include ํ์ด์ง ๋ ------->
<div class="container mt-5">
<!-------------------------------------------- localList ๋ชจ๋ธ์
์์ ------------------------------------------->
<h3 class="mt-4">์นดํ
๊ณ ๋ฆฌ ๋ชฉ๋ก</h3>
<h6>ํ์ฌ ๊ฒ์๊ธ์ด ์กด์ฌํ๋ ์นดํ
๊ณ ๋ฆฌ๋ ์์ /์ญ์ ๋ฅผ ํ ์ ์์ต๋๋ค</h6>
<!-- msg ๋ฐ์์ ๋ฉ์ธ์ง ์ถ๋ ฅ -->
<div class="text-danger">
<%
if(request.getParameter("msg") != null) {
%>
<%=request.getParameter("msg")%>
<%
}
%>
</div>
<br>
<table class="table container">
<thead class="table-success">
<tr>
<th>์นดํ
๊ณ ๋ฆฌ</th>
<th>์์ฑ์ผ์</th>
<th>์์ </th>
<th>์ญ์ </th>
</tr>
</thead>
<tbody>
<%
for(Local l : localList) {
%>
<tr>
<td><%=l.getLocalName()%></td>
<td><%=l.getCreatedate()%></td>
<td>
<a href="<%=request.getContextPath()%>/local/updateLocalForm.jsp?localName=<%=l.getLocalName()%>" class="btn">
✏
</a>
</td>
<td>
<a href="<%=request.getContextPath()%>/local/deleteLocalForm.jsp?localName=<%=l.getLocalName()%>" class="btn">
🗑
</a>
</td>
</tr>
<%
}
%>
</tbody>
</table>
<!-------------------------------------------- localList ๋ชจ๋ธ์
๋ ------------------------------------------->
<div class="text-center">
<a href="<%=request.getContextPath()%>/local/insertLocalForm.jsp" class="btn btn-success">
📃 ์๋ก์ด ์นดํ
๊ณ ๋ฆฌ ์์ฑ
</a>
</div>
</div>
<br>
<!-- include ํ์ด์ง : Copyright -->
<div>
<!-- ์ก์
ํ๊ทธ -->
<jsp:include page="/inc/copyright.jsp"></jsp:include>
</div>
<!-------- include ํ์ด์ง ๋ ------->
</body>
</html>
๐ ๊ฒฐ๊ณผ
๐ง ์๊ฐํด ๋ณด๊ธฐ
-> ์๋ฌด๋ฆฌ ์๊ฐํด๋ join๋ฌธ์ ์ฌ์ฉํด์ผ ํ ๊ฒ ๊ฐ๋ค! ์์ฑ์ผ์๋ local ํ
์ด๋ธ์ ์์ฑ์ผ์์ฌ์ผ ํ๊ณ , count๋ board ํ
์ด๋ธ์ด ๊ธฐ์ค์ด๊ธฐ ๋๋ฌธ์ด๋ค. ์์ง join๋ฌธ์ ์ ๋๋ก ๋ฐฐ์ฐ์ง ์์์ ์ด ๋ถ๋ถ์ ์๊ฐ์ด ๋ ๋ ์ฐพ์๋ณด๊ณ ๋์ ํด๋ณด๊ธฐ๋ก ํ๋ค!
LEFT JOIN๋ฌธ์ ์ด์ฉํ์ฌ ์ฟผ๋ฆฌ๋ฅผ ๋ค์ ์์ฑํด๋ณด์๋ค.
SELECT
l.local_name localName,
l.createdate createdate,
COUNT(b.local_name) cnt
FROM local l LEFT JOIN board b
ON l.local_name=b.local_name
GROUP BY localName
ORDER BY createdate DESC;
// Voํ์
์ ArrayList์ Map ์ฌ์ฉ
ArrayList<Local> localList = new ArrayList<Local>();
Map<String,Integer> cnt = new HashMap<>();
while(rs.next()) {
Local l = new Local();
l.setLocalName(rs.getString("localName"));
l.setCreatedate(rs.getString("createdate"));
cnt.put(l.getLocalName(), rs.getInt("cnt"));
// cnt.put(rs.getString("localName"), rs.getInt("cnt"));
localList.add(l);
}
Local ๊ฐ์ฒด๋ฅผ ์ต๋ํ ์ฌ์ฉํ ์ ์๊ธฐ ๋๋ฌธ์ ๋ ์ข์ ๋ฐฉ๋ฒ์ธ ๊ฒ ๊ฐ์์ ๋ง์กฑ์ค๋ฝ๋ค ๐
insertLocalForm.jsp
๐ ์์ฑ
<%@ page language="java" contentType="text/html; charset=UTF-8" pageEncoding="UTF-8"%>
<%
// ์ธ์
์ ํจ์ฑ ๊ฒ์ฌ
if(session.getAttribute("loginMemberId") == null) {
response.sendRedirect(request.getContextPath() + "/home.jsp");
return;
}
String memberId = (String)session.getAttribute("loginMemberId");
System.out.println(memberId + " <- insertLocalForm session loginMemberId");
%>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>insertLocalForm.jsp</title>
<!-- ๋ถํธ์คํธ๋ฉ5 ์ฌ์ฉ -->
<link href="https://cdn.jsdelivr.net/npm/bootstrap@5.2.3/dist/css/bootstrap.min.css" rel="stylesheet">
<script src="https://cdn.jsdelivr.net/npm/bootstrap@5.2.3/dist/js/bootstrap.bundle.min.js"></script>
</head>
<body>
<div> <!-- mainmenu include -->
<jsp:include page="/inc/mainmenu.jsp"></jsp:include>
</div>
<div class="container mt-5">
<!---------------------- ์นดํ
๊ณ ๋ฆฌ insert form ์์ ---------------------->
<h3 class="mt-4">์๋ก์ด ์นดํ
๊ณ ๋ฆฌ ์์ฑ</h3>
<h6>์ค๋ณต๋์ง ์๋ ์ด๋ฆ์ ์
๋ ฅํด์ฃผ์ธ์</h6>
<!-- ์์ฑ ์คํจ์ ์๋ฌ๋ฉ์ธ์ง ์ถ๋ ฅ -->
<div class="text-danger">
<%
if(request.getParameter("msg") != null) {
%>
<%=request.getParameter("msg")%>
<%
}
%>
</div>
<form action="<%=request.getContextPath()%>/local/insertLocalAction.jsp" method="post">
<table class="table container">
<tr>
<th class="table-success">์นดํ
๊ณ ๋ฆฌ ์ด๋ฆ</th>
<td>
<input type="text" name="localName">
</td>
</tr>
</table>
<a href="<%=request.getContextPath()%>/local/localList.jsp" class="btn btn-success">
๋ค๋ก๊ฐ๊ธฐ
</a>
<button type="submit" class="btn btn-success">์์ฑ</button>
</form>
<!---------------------- ์นดํ
๊ณ ๋ฆฌ insert form ๋ ---------------------->
</div>
<div> <!-- copyright include -->
<jsp:include page="/inc/copyright.jsp"></jsp:include>
</div>
</body>
</html>
insertLocalAction.jsp
๐ ์์ฑ
est.getContextPath() + "/local/insertLocalForm.jsp?msg=" + msg);
return;
}
String localName = request.getParameter("localName");
// ๋๋ฒ๊น
System.out.println(memberId + " <- insertLocalAction session loginMemberId");
System.out.println(localName + " <- insertLocalAction localName");
// 2. ๋ชจ๋ธ๊ฐ ๊ตฌํ๊ธฐ
// 2-1. ๋๋ผ์ด๋ฒ ๋ก๋ฉ ๋ฐ db ์ ์
String driver = "org.mariadb.jdbc.Driver";
String dburl = "jdbc:mariadb://127.0.0.1:3306/userboard";
String dbuser = "****";
String dbpw = "****";
Class.forName(driver);
Connection conn = DriverManager.getConnection(dburl, dbuser, dbpw);
// 2-2. localName ์ค๋ณต๊ฒ์ฌ
// ํด๋น localName์ด ๋ฐ์ดํฐ์ ์กด์ฌํ๋์ง(where) count ํด๋ณธ๋ค
String countSql = "SELECT count(*) FROM local WHERE local_name = ?";
PreparedStatement countStmt = conn.prepareStatement(countSql);
countStmt.setString(1, localName);
System.out.println(countStmt + " <- insertLocalAction count stmt");
ResultSet countRs = countStmt.executeQuery();
// ๊ฐฏ์ ํ์ธ
int cnt = 0;
if(countRs.next()) {
cnt = countRs.getInt("count(*)");
// ํด๋น localName์ ๊ฐฏ์๋ฅผ cnt ๋ณ์์ ์ ์ฅ
// 0์ผ ๊ฒฝ์ฐ ์ค๋ณต ์์
}
// 0๋ณด๋ค ํด ๊ฒฝ์ฐ ์ค๋ณต ์์ // form์ผ๋ก ๋ฆฌ๋ค์ด๋ ์
if(cnt > 0) {
System.out.println(cnt + " <- insertLocalAction ์ค๋ณต๋ ์นดํ
๊ณ ๋ฆฌ ๊ฐฏ์");
msg = URLEncoder.encode("์ด๋ฏธ ์กด์ฌํ๋ ์ด๋ฆ์
๋๋ค", "utf-8");
response.sendRedirect(request.getContextPath() + "/local/insertLocalForm.jsp?msg=" + msg);
return;
} else {
System.out.println("insertLocalAction ์ค๋ณต๋ ์นดํ
๊ณ ๋ฆฌ ์์");
}
// 2-3. ์ฟผ๋ฆฌ ์์ฑ
String sql = "INSERT INTO local(local_name, createdate, updatedate) VALUES(?, NOW(), NOW())";
PreparedStatement stmt = conn.prepareStatement(sql);
stmt.setString(1, localName);
System.out.println(stmt + " <- insertLocalAction stmt");
// 2-4. ์ฟผ๋ฆฌ๊ฐ ์ ์งํ๋์๋์ง ํ์ธ
int row = stmt.executeUpdate();
if(row == 1) { // ์ฑ๊ณต์ localList๋ก
System.out.println(row + " <- insertLocalAction ์ฑ๊ณต");
msg = URLEncoder.encode("์นดํ
๊ณ ๋ฆฌ๊ฐ ์์ฑ๋์์ต๋๋ค", "utf-8");
response.sendRedirect(request.getContextPath() + "/local/localList.jsp?msg=" + msg);
return;
} else { // ์คํจ์ Form์ผ๋ก
System.out.println(row + " <- insertLocalAction ์คํจ");
msg = URLEncoder.encode("์นดํ
๊ณ ๋ฆฌ๊ฐ ์์ฑ๋์ง ์์์ต๋๋ค ๋ค์ ์๋ํด์ฃผ์ธ์", "utf-8");
response.sendRedirect(request.getContextPath() + "/local/insertLocalForm.jsp?msg=" + msg);
return;
}
%>
๐ ๊ฒฐ๊ณผ
updateLocalForm.jsp
๐ ์์ฑ
String memberId = (String)session.getAttribute("loginMemberId");
String localName = request.getParameter("localName");
System.out.println(memberId + " <- updateLocalForm session loginMemberId");
System.out.println(localName + " <- updateLocalForm localName");
// 2. ๋ชจ๋ธ๊ฐ ๊ตฌํ๊ธฐ
// localCnt ๋ชจ๋ธ
String driver = "org.mariadb.jdbc.Driver";
String dburl = "jdbc:mariadb://127.0.0.1:3306/userboard";
String dbuser = "****";
String dbpw = "****";
Class.forName(driver);
Connection conn = DriverManager.getConnection(dburl, dbuser, dbpw);
// ํด๋น localName์ ๊ฒ์๊ธ ์(board์ ๊ฐฏ์)๋ฅผ ์กฐํํ๋ ์ฟผ๋ฆฌ ์์ฑ
String sql = "SELECT count(*) FROM board WHERE local_name = ?";
PreparedStatement stmt = conn.prepareStatement(sql);
stmt.setString(1, localName);
// ๋ณ์์ ๊ฐ ์ ์ฅ
ResultSet rs = stmt.executeQuery();
System.out.println(stmt + " <- updateLocalForm stmt");
int localCnt = 0;
if(rs.next()) {
localCnt = rs.getInt("count(*)");
}
%>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<!-- ๋ถํธ์คํธ๋ฉ5 ์ฌ์ฉ -->
<link href="https://cdn.jsdelivr.net/npm/bootstrap@5.2.3/dist/css/bootstrap.min.css" rel="stylesheet">
<script src="https://cdn.jsdelivr.net/npm/bootstrap@5.2.3/dist/js/bootstrap.bundle.min.js"></script>
</head>
<body>
<!-- include ํ์ด์ง : ๋ฉ์ธ๋ฉ๋ด(๊ฐ๋ก) -->
<div>
<!-- ์ก์
ํ๊ทธ -->
<jsp:include page="/inc/mainmenu.jsp"></jsp:include>
</div>
<!-------- include ํ์ด์ง ๋ ------->
<div class="container mt-5">
<!-------------------------------------------- updateLocalForm ์์ ------------------------------------------->
<!-- localCnt๊ฐ 0์ผ ๊ฒฝ์ฐ์๋ง form ํ๊ทธ ์ถ๋ ฅ -->
<%
if(localCnt == 0) {
%>
<h3 class="mt-4">์นดํ
๊ณ ๋ฆฌ ์์ </h3>
<h6>์ค๋ณต๋์ง ์๋ ์ด๋ฆ์ ์
๋ ฅํด์ฃผ์ธ์</h6>
<!-- ์์ ์คํจ์ ์๋ฌ๋ฉ์ธ์ง ์ถ๋ ฅ -->
<div class="text-danger">
<%
if(request.getParameter("msg") != null) {
%>
<%=request.getParameter("msg")%>
<%
}
%>
</div>
<form action="<%=request.getContextPath()%>/local/updateLocalAction.jsp" method="post">
<table class="table container">
<tr>
<th class="table-success">ํ์ฌ ์ด๋ฆ</th>
<td>
<input type="text" name="localName" value="<%=localName%>" readonly>
</td>
</tr>
<tr>
<th class="table-success">์์ ํ ์ด๋ฆ</th>
<td>
<input type="text" name="localNewName">
</td>
</tr>
</table>
<a href="<%=request.getContextPath()%>/local/localList.jsp" class="btn btn-success">
๋ค๋ก๊ฐ๊ธฐ
</a>
<button type="submit" class="btn btn-success">์์ </button>
</form>
<!-- localCnt๊ฐ 0์ด ์๋ ๊ฒฝ์ฐ ๋ฉ์ธ์ง ์ถ๋ ฅ -->
<%
} else {
%>
<h5> ํ์ฌ ๊ฒ์๊ธ์ด ์กด์ฌํ๋ ์นดํ
๊ณ ๋ฆฌ์
๋๋ค. ์์ ํ ์ ์์ต๋๋ค 😔 </h5>
<h5 class="text-danger"> <%=localName%> ์นดํ
๊ณ ๋ฆฌ์ ํ์ฌ ๊ฒ์๊ธ ์๋ <%=localCnt%>๊ฐ ์
๋๋ค.</h5>
<br>
<a href="<%=request.getContextPath()%>/local/localList.jsp" class="btn btn-success">
๋ค๋ก๊ฐ๊ธฐ
</a>
<%
}
%>
<!-------------------------------------------- updateLocalForm ๋ ------------------------------------------->
</div>
<br>
<!-- include ํ์ด์ง : Copyright -->
<div>
<!-- ์ก์
ํ๊ทธ -->
<jsp:include page="/inc/copyright.jsp"></jsp:include>
</div>
<!-------- include ํ์ด์ง ๋ ------->
</body>
</html>
updateLocalAction.jsp
๐ ์์ฑ
<%@ page language="java" contentType="text/html; charset=UTF-8" pageEncoding="UTF-8"%>
<%@ page import="java.sql.*" %>
<%@ page import="java.net.*" %>
<%
// 1. ์ ํจ์ฑ ๊ฒ์ฌ
// ์ธ์
๊ฐ, localName
if(session.getAttribute("loginMemberId") == null
|| request.getParameter("localName") == null
|| request.getParameter("localName").equals("")) {
response.sendRedirect(request.getContextPath() + "/home.jsp");
// ์ธ์
๊ฐ์ด๋ localName์ด ์์ผ๋ฉด home์ผ๋ก
return;
}
String memberId = (String)session.getAttribute("loginMemberId");
String localName = request.getParameter("localName");
// localNewName
String msg = null;
if(request.getParameter("localNewName") == null
|| request.getParameter("localNewName").equals("")) {
msg = URLEncoder.encode("์์ ํ ์ด๋ฆ์ด ์
๋ ฅ๋์ง ์์์ต๋๋ค", "utf-8");
localName = URLEncoder.encode(localName, "utf-8");
response.sendRedirect(request.getContextPath() + "/local/updateLocalForm.jsp?localName=" + localName + "&msg=" + msg);
// localNewName๊ฐ์ด ์์ผ๋ฉด form์ผ๋ก
return;
}
String localNewName = request.getParameter("localNewName");
// ๋๋ฒ๊น
System.out.println(memberId + " <- updateLocalAction session loginMemberId");
System.out.println(localName + " <- updateLocalAction localName");
System.out.println(localNewName + " <- updateLocalAction localNewName");
// 2. ๋ชจ๋ธ๊ฐ ๊ตฌํ๊ธฐ
// 2-1. ๋๋ผ์ด๋ฒ ๋ก๋ฉ ๋ฐ db ์ ์
String driver = "org.mariadb.jdbc.Driver";
String dburl = "jdbc:mariadb://127.0.0.1:3306/userboard";
String dbuser = "****";
String dbpw = "****";
Class.forName(driver);
Connection conn = DriverManager.getConnection(dburl, dbuser, dbpw);
// 2-2. localName ์ค๋ณต๊ฒ์ฌ
// ํด๋น localName์ด ๋ฐ์ดํฐ์ ์กด์ฌํ๋์ง(where) count ํด๋ณธ๋ค
String countSql = "SELECT count(*) FROM local WHERE local_name = ?";
PreparedStatement countStmt = conn.prepareStatement(countSql);
countStmt.setString(1, localNewName);
System.out.println(countStmt + " <- updateLocalAction count stmt");
ResultSet countRs = countStmt.executeQuery();
// ๊ฐฏ์ ํ์ธ
int cnt = 0;
if(countRs.next()) {
cnt = countRs.getInt("count(*)");
// ํด๋น localName์ ๊ฐฏ์๋ฅผ cnt ๋ณ์์ ์ ์ฅ
// 0์ผ ๊ฒฝ์ฐ ์ค๋ณต ์์
}
// 0๋ณด๋ค ํด ๊ฒฝ์ฐ ์ค๋ณต ์์ // form์ผ๋ก ๋ฆฌ๋ค์ด๋ ์
if(cnt > 0) {
System.out.println(cnt + " <- updateLocalAction ์ค๋ณต๋ ์นดํ
๊ณ ๋ฆฌ ๊ฐฏ์");
msg = URLEncoder.encode("์ด๋ฏธ ์กด์ฌํ๋ ์ด๋ฆ์
๋๋ค", "utf-8");
localName = URLEncoder.encode(localName, "utf-8");
response.sendRedirect(request.getContextPath() + "/local/updateLocalForm.jsp?localName=" + localName + "&msg=" + msg);
return;
} else {
System.out.println("updateLocalAction ์ค๋ณต๋ ์นดํ
๊ณ ๋ฆฌ ์์");
}
// 2-3. ์ฟผ๋ฆฌ ์์ฑ
String sql = "UPDATE local SET local_name = ?, updatedate = NOW() WHERE local_name = ?";
PreparedStatement stmt = conn.prepareStatement(sql);
stmt.setString(1, localNewName);
stmt.setString(2, localName);
System.out.println(stmt + " <- updateLocalAction stmt");
// 2-4. ์ฟผ๋ฆฌ๊ฐ ์ ์งํ๋์๋์ง ํ์ธ
int row = stmt.executeUpdate();
if(row == 1) { // ์ฑ๊ณต์ localList๋ก
System.out.println(row + " <- updateLocalAction ์ฑ๊ณต");
msg = URLEncoder.encode("์นดํ
๊ณ ๋ฆฌ๊ฐ ์์ ๋์์ต๋๋ค", "utf-8");
response.sendRedirect(request.getContextPath() + "/local/localList.jsp?msg=" + msg);
return;
} else { // ์คํจ์ Form์ผ๋ก
System.out.println(row + " <- updateLocalAction ์คํจ");
msg = URLEncoder.encode("์นดํ
๊ณ ๋ฆฌ๊ฐ ์์ ๋์ง ์์์ต๋๋ค ๋ค์ ์๋ํด์ฃผ์ธ์", "utf-8");
response.sendRedirect(request.getContextPath() + "/local/updateLocalForm.jsp?msg=" + msg);
return;
}
%>
๐ ๊ฒฐ๊ณผ
deleteLocalForm.jsp
๐ ์์ฑ
<%@ page language="java" contentType="text/html; charset=UTF-8" pageEncoding="UTF-8"%>
<%@ page import="java.sql.*" %>
<%
// 1. ์ ํจ์ฑ ๊ฒ์ฌ
// ์ธ์
๊ฐ, localName
if(session.getAttribute("loginMemberId") == null) {
response.sendRedirect(request.getContextPath() + "/home.jsp");
// ์ธ์
๊ฐ์ด ์์ผ๋ฉด home์ผ๋ก
return;
} else if(request.getParameter("localName") == null
|| request.getParameter("localName").equals("")) {
response.sendRedirect(request.getContextPath() + "/local/localList.jsp");
// localName๊ฐ์ด ์์ผ๋ฉด localList๋ก
return;
}
String memberId = (String)session.getAttribute("loginMemberId");
String localName = request.getParameter("localName");
System.out.println(memberId + " <- deleteLocalForm session loginMemberId");
System.out.println(localName + " <- deleteLocalForm localName");
// 2. ๋ชจ๋ธ๊ฐ ๊ตฌํ๊ธฐ
// localCnt ๋ชจ๋ธ
String driver = "org.mariadb.jdbc.Driver";
String dburl = "jdbc:mariadb://127.0.0.1:3306/userboard";
String dbuser = "****";
String dbpw = "****";
Class.forName(driver);
Connection conn = DriverManager.getConnection(dburl, dbuser, dbpw);
// ํด๋น localName์ ๊ฒ์๊ธ ์(board์ ๊ฐฏ์)๋ฅผ ์กฐํํ๋ ์ฟผ๋ฆฌ ์์ฑ
String sql = "SELECT count(*) FROM board WHERE local_name = ?";
PreparedStatement stmt = conn.prepareStatement(sql);
stmt.setString(1, localName);
// ๋ณ์์ ๊ฐ ์ ์ฅ
ResultSet rs = stmt.executeQuery();
System.out.println(stmt + " <- deleteLocalForm stmt");
int localCnt = 0;
if(rs.next()) {
localCnt = rs.getInt("count(*)");
}
%>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<!-- ๋ถํธ์คํธ๋ฉ5 ์ฌ์ฉ -->
<link href="https://cdn.jsdelivr.net/npm/bootstrap@5.2.3/dist/css/bootstrap.min.css" rel="stylesheet">
<script src="https://cdn.jsdelivr.net/npm/bootstrap@5.2.3/dist/js/bootstrap.bundle.min.js"></script>
</head>
<body>
<!-- include ํ์ด์ง : ๋ฉ์ธ๋ฉ๋ด(๊ฐ๋ก) -->
<div>
<!-- ์ก์
ํ๊ทธ -->
<jsp:include page="/inc/mainmenu.jsp"></jsp:include>
</div>
<!-------- include ํ์ด์ง ๋ ------->
<div class="container mt-5">
<!-------------------------------------------- deleteLocalForm ์์ ------------------------------------------->
<!-- localCnt๊ฐ 0์ผ ๊ฒฝ์ฐ์๋ง form ํ๊ทธ ์ถ๋ ฅ -->
<%
if(localCnt == 0) {
%>
<h3 class="mt-4">์นดํ
๊ณ ๋ฆฌ ์ญ์ </h3>
<h5>์ ๋ง ์ญ์ ํ์๊ฒ ์ต๋๊น?</h5>
<form action="<%=request.getContextPath()%>/local/deleteLocalAction.jsp" method="post">
<table class="table container">
<tr>
<th class="table-success">์นดํ
๊ณ ๋ฆฌ ์ด๋ฆ</th>
<td>
<input type="text" name="localName" value="<%=localName%>" readonly>
</td>
</tr>
</table>
<a href="<%=request.getContextPath()%>/local/localList.jsp" class="btn btn-success">
๋ค๋ก๊ฐ๊ธฐ
</a>
<button type="submit" class="btn btn-danger">์ญ์ </button>
</form>
<!-- localCnt๊ฐ 0์ด ์๋ ๊ฒฝ์ฐ ๋ฉ์ธ์ง ์ถ๋ ฅ -->
<%
} else {
%>
<h5> ํ์ฌ ๊ฒ์๊ธ์ด ์กด์ฌํ๋ ์นดํ
๊ณ ๋ฆฌ์
๋๋ค. ์ญ์ ํ ์ ์์ต๋๋ค 😔 </h5>
<h5 class="text-danger"> <%=localName%> ์นดํ
๊ณ ๋ฆฌ์ ํ์ฌ ๊ฒ์๊ธ ์๋ <%=localCnt%>๊ฐ ์
๋๋ค.</h5>
<br>
<a href="<%=request.getContextPath()%>/local/localList.jsp" class="btn btn-success">
๋ค๋ก๊ฐ๊ธฐ
</a>
<%
}
%>
<!-------------------------------------------- deleteLocalForm ๋ ------------------------------------------->
</div>
<br>
<!-- include ํ์ด์ง : Copyright -->
<div>
<!-- ์ก์
ํ๊ทธ -->
<jsp:include page="/inc/copyright.jsp"></jsp:include>
</div>
<!-------- include ํ์ด์ง ๋ ------->
</body>
</html>
deleteLocalAction.jsp
๐ ์์ฑ
<%@ page language="java" contentType="text/html; charset=UTF-8" pageEncoding="UTF-8"%>
<%@ page import="java.sql.*" %>
<%@ page import="java.net.*" %>
<%
// 1. ์ ํจ์ฑ ๊ฒ์ฌ
// ์ธ์
๊ฐ, localName
if(session.getAttribute("loginMemberId") == null) {
response.sendRedirect(request.getContextPath() + "/home.jsp");
// ์ธ์
๊ฐ์ด ์์ผ๋ฉด home์ผ๋ก
return;
} else if(request.getParameter("localName") == null
|| request.getParameter("localName").equals("")) {
response.sendRedirect(request.getContextPath() + "/local/localList.jsp");
// localName๊ฐ์ด ์์ผ๋ฉด localList๋ก
return;
}
String memberId = (String)session.getAttribute("loginMemberId");
String localName = request.getParameter("localName");
System.out.println(memberId + " <- deleteLocalAction session loginMemberId");
System.out.println(localName + " <- deleteLocalAction localName");
// 2. delete
// 2-1. ๋๋ผ์ด๋ฒ ๋ก๋ฉ ๋ฐ db ์ ์
String driver = "org.mariadb.jdbc.Driver";
String dburl = "jdbc:mariadb://127.0.0.1:3306/userboard";
String dbuser = "****";
String dbpw = "****";
Class.forName(driver);
Connection conn = DriverManager.getConnection(dburl, dbuser, dbpw);
// 2-2. ์ฟผ๋ฆฌ ์์ฑ
String sql = "DELETE FROM local WHERE local_name = ?";
PreparedStatement stmt = conn.prepareStatement(sql);
stmt.setString(1, localName);
System.out.println(stmt + " <- deleteLocalAction stmt");
// 2-3. ์ฟผ๋ฆฌ๊ฐ ์ ์งํ๋์๋์ง ํ์ธ
int row = stmt.executeUpdate();
String msg = null;
if(row == 1) { // ์ฑ๊ณต์ localList๋ก
System.out.println(row + " <- deleteLocalAction ์ฑ๊ณต");
msg = URLEncoder.encode("์นดํ
๊ณ ๋ฆฌ๊ฐ ์ญ์ ๋์์ต๋๋ค", "utf-8");
response.sendRedirect(request.getContextPath() + "/local/localList.jsp?msg=" + msg);
return;
} else {
System.out.println(row + " <- deleteLocalAction ์ฑ๊ณต");
msg = URLEncoder.encode("์นดํ
๊ณ ๋ฆฌ๊ฐ ์ญ์ ๋์ง ์์์ต๋๋ค ๋ค์ ์๋ํด์ฃผ์ธ์", "utf-8");
response.sendRedirect(request.getContextPath() + "/local/localList.jsp?msg=" + msg);
return;
}
%>
๐ ๊ฒฐ๊ณผ