Day 22 - Java sql 서버를 배포하기 / SQL 함수 정리

ksyke 2024. 8. 22. 12:42

Java sql 서버를 배포하기

Web workspace에 sql폴더를 만들어 cmd로 열기
tomcat이 깔려있는 vagrant box image :

vagrant init scytl-rs/xenial64_tomcat8-oraclexe

vagrantfile에 포트번호 변경

config.vm.network "forwarded_port", guest: 8080, host: 7070
config.vm.network "forwarded_port", guest: 1521, host: 11521

SQL과 SQL*Plus

SQLPlus는 oracle을 사용하는데 제공하는 기능
cmd

sqlplus scott@locathost:1521
// 또는
sqlplus scott@사용자IP:1521

-> 인바운드 규칙과 아웃바운드 규칙에 포트번호를 열어줘야 한다.

관리자 계정으로 로그인

sqlplus system/[초기오라클설치시암호]

이외에 SCOTT과 HR계정은 교육용 계정이다.

SQL의 분류

DML (Data Manipulation Language)
[DQL]
- select, insert, update, Delete를 수행한다. (데이터베이스 조작)
TCL (Transaction COntrol Language)
- 트랜젝션을 제어하는 명령어(예: commit, rollback)
DDL (Data Definition Language)
- schema적은 명령어(예: create table, alter, drop 등)
DCL (Data Control Language)
- 오라클 프로그램 자체를 제어하는 명령어

SQL Plus 명령어

list(/), run(r) : 버퍼에 남아있는 명령을 찾아가거나 실행시키는 명령문
edit(ed) : 메모장으로 명령문 편집하기
save
@ ~ : 실행
spool on/off
set heading on/off
set linesize 80
set pagesize 20
column sal format 9,999
column ename format a6
column depyno clear
sqlplus scott/tiger @ ex06.sql : sqlplus 시작하며 sql 파일 실행시키기

SQL 함수

Dual table : 현재날짜와 같이 테이블이 필요하긴 하지만 한번만 출력하고 싶을때 쓰는 의미없는 테이블
select sysdate from dual
현재 날짜 : select sysdate from dual
문자열 더하기 : selectaaa||bbbfrom dual

단일행 함수

문자

수행시 6개의 행으로 결과가 구해짐
LOWER
UPPER : select 'AaBb', upper('AaBb'), lower('AaBb') from dual
select * from emp where ename=upper('ford')
select * from emp where lower(ename)=lower('ford')
INITCAP : 첫글자만 대문자로 만든다.
select initcal('aa bb') from dual => Aa Ba
CONCAT : select concat('aaa', 'bbb') from dual
SUBSTR : 문자를 잘라 추출한다.
select substr('abcdefgh', 4) from dual => defgh
select substr('abcdefgh', 4, 2) from dual => de
select substr('한글도 잘 됩니다.', 5, 1) from dual => 잘
SUBSTRB
select substrb('한글도 잘 됩니다.', 11, 3) from dual => 잘
LENGTH
select length('한글도 잘 됩니다.') from dual => 10
LENGTHB
select lengthb('한글도 잘 됩니다.') from dual => 24
INSTR
select instr('abcdefg', 'c') from dual => 3
select instr('한글도 잘 됩니다.', '잘') from dual => 5
INSTRB
select instrb('한글도 잘 됩니다.', '잘') from dual => 11
TRIM
select '>>>' || trim(' aa bb ') || '<<<' from dual => >>>aa bb<<<
select '>>>' || trim('a' from'aaaaaaabbbaaaaaaa') || '<<<' from dual =>>>>bbb<<<
LPAD / RPAD
select lpad('abc', 5, '#') from dual => ##abc
select rpad('abc', 5, '#') from dual => abc##
TO_CHAR
select to_char(sysdate, 'yy-mm-dd') from dual => 24-08-22
select to_char(sysdate, 'yy-mm-dd hh:mi:ss') from dual => 24-08-22 11:24:06
select to_char(1000, '000,000') from dual => 001,000
select to_char(1000, '999,999') from dual => 1,000
select to_char(1000, 'L9999') from dual => ￦1000
select to_char(1000, '$9999') from dual => $1000
select to_char(1000, 's9999') from dual => +1000
TO_DATE
select to_date('24/8/1', 'yy/mm/dd') from dual => 24/08/01
select trunc(sysdate-to_date('2024/01/01', 'yyyy/mm/dd')) from dual => 234
select sysdate-to_date('2024/01/01', 'yyyy/mm/dd') from dual => 234.480255
TO_NUMBER
select to_number('1,111', '9,999') + 1 from dual => 1112

숫자

ABS : select -10, abs(10), abs(-10) from dual
FLOOR : select floor(3.14) from dual => 3
ROUND :
select round(3.55) from dual => 4
select round(1234, -2) from dual => 1200
TRUNC :
select trunc(3.14, 1) from dual => 3.1
select trunc(1234, -3) from dual => 1000
MOD : select 5/2, mod(5, 2) from dual => 2.5, 1

날짜

SYSDATE :
select sysdate from dual => 24/08/22
select sysdate+1 from dual => 24/08/23
select add_months(sysdate, 1) from dual => 24/09/22
select next_day(sysdate, 5) from dual => 24/08/29
select last_day(sysdate) from dual => 24/08/31
select months_between(sysdate, add_months(sysdate, 3)) from dual => -3
select round(sysdate) from dual => 오후12시가 넘어가면 다음날이 된다.

일반 함수

NULL : select ename, sal, comm, sal + nvl(comm,0) from emp
DECODE :
select ename, decode(deptno, 10, 'accounting', 20, 'research', 30, 'sales', 40, 'operations') from emp
select ename, nvl(decode(deptno, 10, 'accounting', 20, 'research', 40, 'operations'), 'etc') from emp

CASE :

  select 
      case DEPTNO
          when 10 then 'accounting'
          when 20 then 'research'
          when 30 then 'sales'
          when 40 then 'operations' 
      end
   from emp

select case when deptno=10 then 'accounting' when deptno=20 then 'research' when deptno=30 then 'sales' when deptno=40 then 'operations' end from emp

그룹함수

수행 시 1개의 행으로 결과가 구해짐
그룸함수 끼리의 수행이 가능:
select sum(sal), avg(sal), max(sal), min(sal), count(sal) from emp
SUM : column의 총합
select sum(sal) from emp
select deptno, sum(sal) from emp group by deptno => 같은 deptno까리의 합을 보여줌
select deptno, sum(sal) from emp group by deptno having deptno<30
select deptno, sum(sal) from emp where deptno<30 group by deptno
AVG
select deptno, avg(sal) from emp group by deptno
COUNT
MAX
MIN
STDDEV
VARIANCE

데이터베이스 권한 보안 (DCL)

계정 만들기 (system 계정에서 실행)

create user user01 identified by password default tablespace USERS quota 2m on USERS;

계정의 lock 풀기

session 만들기

grant create session to user01;

table 권한 부여하기

grant create table to user01;

Java에서 SQL Database CWED 수행하기

index

<%@ page language="java" contentType="text/html; charset=EUC-KR"
    pageEncoding="EUC-KR"%>
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=EUC-KR">
<title>Insert title here</title>
</head>
<body>
<div>
    <div><img src="https://dummyimage.com/200x100/000/fff.png&text=LOGO"></div>
    <div>
        <center>
        <a href="./">[HOME]</a>
        <a href="intro.jsp">[Intro]</a>
        <a href="bbs/list.jsp">[B B S]</a>
        <a href="login/login.jsp">[LOGIN]</a>
        </center>
        <hr>
    </div>
    <div>
        <!-- begin content -->
        <center>
        <img src="https://dummyimage.com/600x300/000/fff.png&text=WELCOME">
        </center>
        <!-- end content -->
    </div>
    <div>
        <hr>
        <center>
        <p>김해캠퍼스 (50834) 경남 김해시 인제로 197</p>
        <p>Copyright &copy; 2024 INJE University. All rights reserved.</p>
        </center>
    </div>
</div>
</body>
</html>

intro

    <img alt="" src="https://www.inje.ac.kr/kor/assets/images/sub/gimhae-campus-1.jpg">

bbs

list

<!-- begin content -->
        <center>
            <h1>게시판</h1>
            <table border="1" cellspacing="0" width="500" align="center">
                <tr>
                    <th width="80">글번호</th>
                    <th>제목</th>
                    <th width="100">글쓴이</th>
                    <th width="100">날짜</th>
                </tr>
                <%@ page import="java.sql.*" %>
                <%
                String search=request.getParameter("search");
                String keyword=request.getParameter("keyword");
                if(search==null)search="sub";
                if(keyword==null)keyword="";
                String driver="oracle.jdbc.driver.OracleDriver";
                String url="jdbc:oracle:thin:@localhost:1521:xe";
                String user="scott";
                String password="tiger";
                Connection conn=null;
                Statement stmt=null;
                ResultSet rs=null;
                String sql="select num,nvl(sub,'제목없음'),id,nalja from bbs01 ";
                sql+="where "+search+" like('%"+keyword+"%')";
                sql+=" order by num desc";
                try{
                    Class.forName(driver);
                    conn=DriverManager.getConnection(url, user, password);
                    stmt=conn.createStatement();
                    rs=stmt.executeQuery(sql);
                    while(rs.next()){
                %>
                <tr>
                    <td><%=rs.getString(1) %></td>
                    <td><a href="detail.jsp?idx=<%=rs.getString(1)%>"><%=rs.getString(2) %></a></td>
                    <td><%=rs.getString(3) %></td>
                    <td><%=rs.getDate(4) %></td>
                </tr>
                <%
                    }
                } finally{
                    if(rs!=null)rs.close();
                    if(stmt!=null)stmt.close();
                    if(conn!=null)conn.close();
                }
                %>
            </table>
            <p>
                <form action="#">
                    <select name="search">
                        <option value="sub">제목</option>
                        <option value="id">글쓴이</option>
                        <option value="content">내용</option>
                    </select>
                    <input type="text" name="keyword">
                    <input type="submit" value="검색">
                </form>
            </p>
            <p><a href="add.jsp">[입력]</a></p>
        </center>
        <!-- end content -->

insert

<body>
<%@ page import="java.sql.*,java.net.*" %>
<%
String sub=request.getParameter("sub");
String id=request.getParameter("id");
String content=request.getParameter("content");
if(id.isEmpty()){
    // server.xml -> URIEncoding="EUC-KR" 
    sub=URLEncoder.encode(sub,"EUC-KR");
    content=URLEncoder.encode(content,"EUC-KR");
    response.sendRedirect("add.jsp?sub="+sub+"&content="+content);
    return;
}

String sql="select max(num) from bbs01";
String driver="oracle.jdbc.driver.OracleDriver";
String url="jdbc:oracle:thin:@localhost:1521:xe";
String user="scott";
String password="tiger";

Connection conn=null;
Statement stmt=null;
ResultSet rs=null;
int maxNum=0;
try{
    conn=DriverManager.getConnection(url, user, password);
    stmt=conn.createStatement();
    rs=stmt.executeQuery(sql);
    if(rs.next())
        maxNum=rs.getInt(1);
}finally{
    if(rs!=null)rs.close();
    if(stmt!=null)stmt.close();
}
sql="insert into bbs01 (num,sub,id,nalja,content) values ("
            +(maxNum+1)+",'"+sub+"','"+id+"',sysdate,'"+content+"')";
try{
    stmt=conn.createStatement();
    stmt.executeUpdate(sql);
}finally{
    if(stmt!=null)stmt.close();
    if(conn!=null)conn.close();
}
response.sendRedirect("list.jsp");
%>
</body>

add

<!-- begin content -->
        <center>
        <h1>입력 페이지</h1>
        <%
            String sub=request.getParameter("sub");
            String content=request.getParameter("content");
            if(sub==null) sub="";
            if(content==null)content="";
        %>
        <form action="insert.jsp">
            <table width="500">
                <tr>
                    <td width="100">제목</td>
                    <td><input value="<%=sub %>" type="text" name="sub" style="width:100%;"></td>
                </tr>
                <tr>
                    <td>글쓴이</td>
                    <td><input type="text" name="id" style="width:100%;"></td>
                </tr>
                <tr>
                    <td colspan="2">
                        <textarea name="content" rows="5" style="width:100%;"><%=content %></textarea>
                    </td>
                </tr>
                <tr>
                    <td colspan="2" align="center">
                        <input type="submit" value="입 력">
                        <input type="reset" value="취 소">
                        <input type="button" value="뒤 로" onclick="history.back();">
                    </td>
                </tr>
            </table>
        </form>
        </center>
        <!-- end content -->

detail

<!-- begin content -->
        <center>
        <%@ page import="java.sql.*" %>
        <%
        String idx=request.getParameter("idx");

        String sql="select id,sub,nalja,content from bbs01 where num="+idx;
        String driver="oracle.jdbc.driver.OracleDriver";
        String url="jdbc:oracle:thin:@localhost:1521:xe";
        String user="scott";
        String password="tiger";

        String sub="",id="",nalja="",content="";

        Connection conn=null;
        Statement stmt=null;
        ResultSet rs=null;
        try{
            Class.forName(driver);
            conn=DriverManager.getConnection(url, user, password);
            stmt=conn.createStatement();
            rs=stmt.executeQuery(sql);
            if(rs.next()){
                id=rs.getString(1);
                sub=rs.getString(2);
                nalja=rs.getDate(3).toString();
                content=rs.getString(4);
            }
        }finally{
            if(rs!=null)rs.close();
            if(stmt!=null)stmt.close();
            if(conn!=null)conn.close();
        }
        %>
        <h1>상세페이지(<%=idx %>번 글)</h1>

        <table width="500">
            <tr>
                <td width="100">제목</td>
                <td colspan="3"><%=sub %></td>
            </tr>
            <tr>
                <td>글쓴이</td>
                <td><%=id %></td>
                <td width="100">날짜</td>
                <td><%=nalja %></td>
            </tr>
            <tr>
                <td colspan="4" height="300" valign="top"><%=content.replace("\n", "<br>") %></td>
            </tr>
            <tr>
                <td colspan="4" align="center">
                    <a href="edit.jsp?idx=<%=idx%>">[수 정]</a>
                    <a href="del.jsp?idx=<%=idx%>">[삭 제]</a>
                </td>
            </tr>
        </table>
        </center>
        <!-- end content -->

del

        <!-- begin content -->
        <center>
        <h1>삭제 페이지</h1>
            <%
            String idx=request.getParameter("idx");
            %>
        <p></p>
        <table align="center" width="200" height="100" border="1" >
            <tr>
                <td align="center">
                <form action="delete.jsp">
                    <input type="hidden" name="num" value="<%=idx%>">
                    <input type="submit" value="삭 제">
                    <input type="button" value="뒤 로" onclick="history.back();">
                </form>
                </td>
            </tr>
        </table>
        <p></p>
        <p></p>
        </center>
        <!-- end content -->

delete

<body>
<%@ page import="java.sql.*" %>
<%
String num=request.getParameter("num");
String sql="delete from bbs01 where num="+num;
String driver="oracle.jdbc.driver.OracleDriver";
String url="jdbc:oracle:thin:@localhost:1521:xe";
String user="scott";
String password="tiger";
Class.forName(driver);
try(
        Connection conn=DriverManager.getConnection(url, user, password);
        Statement stmt=conn.createStatement();
        ){
        stmt.executeUpdate(sql);
}

response.sendRedirect("list.jsp");
%>
</body>