[Spring] Spring MVC CRUD게시판 만들기 (1)

 개념

 

 # Spring MVC 동작 순서 
 1. 사용자 요청 (.do) (주문) 
 2. (서빙) DispatcherServlet 요청을 받는다
 3. 요청처리 → Model(@Controller) → 요청처리 하는 메소드 찾기 
    ―――――――――――――――――――――――――――――― HandlerMapping
        → 스프링에 의해서 메모리 할당된 클래스중에
               @Controller가 올라간 클래스만 찾는다
 4. Model → 해당 메소드 호출 → invoke()
 5. 결과값 (request,session) → 전송 
 6. 전송 => JSP를 찾아서 request를 전송하는 클래스 → ViewResolver
                                                                             경로명,확장자

 

 

 

 

 코드

 

'

경로

 

 

다운로드 후 [WEB-INF] - [lib 생성] - ↓라이브러리 추가

↓↓↓↓↓↓↓↓↓↓↓↓↓↓

 

ojdbc14.jar

1.50MB

 

 

# Oracle에 Member 테이블 생성

CREATE TABLE member(
    id VARCHAR2(20),
    pwd VARCHAR2(10) CONSTRAINT mem_pwd_nn NOT NULL,
    name VARCHAR2(34) CONSTRAINT mem_name_nn NOT NULL,
    email VARCHAR2(1000),
    birthday VARCHAR2(20) CONSTRAINT mem_bd_nn NOT NULL,
    post VARCHAR2(10) CONSTRAINT mem_post_nn NOT NULL,
    add1 VARCHAR2(200) CONSTRAINT mem_add1_nn NOT NULL,
    add2 VARCHAR2(100),
    tel VARCHAR2(20),
    content CLOB CONSTRAINT mem_cont_nn NOT NULL,
    CONSTRAINT mem_id_pk PRIMARY KEY(id),
    CONSTRAINT mem_email_uk UNIQUE(email),
    CONSTRAINT mem_tel_uk UNIQUE(tel)
);
ALTER TABLE member ADD admin CHAR(1) CHECK(admin IN('y','n'));
 
INSERT INTO member VALUES('hong','1234','홍길동','hong@naver.com','2000-01-01','000-000',
'서울시 강남구 역삼동','','010-0000-0000','나는 admin입니다','y');
COMMIT;

 

 

 

# Oracle에 project_board 테이블 생성 및 컬럼추가

CREATE TABLE project_board(
    no NUMBER,
    name VARCHAR2(34) CONSTRAINT pb_name_nn NOT NULL,
    subject VARCHAR2(1000) CONSTRAINT pb_sub_nn NOT NULL,
    content CLOB CONSTRAINT pb_cont_nn NOT NULL,
    pwd VARCHAR2(10) CONSTRAINT pb_pwd_nn NOT NULL,
    regdate DATE DEFAULT SYSDATE,
    hit NUMBER DEFAULT 0,
    CONSTRAINT pb_no_pk PRIMARY KEY(no)
);
 
 
 
CREATE OR REPLACE PROCEDURE projectBoardInsert(
    pName project_board.name%type,
    pSubject project_board.subject%type,
    pContent project_board.content%type,
    pPwd project_board.pwd%type
)
IS
BEGIN
    INSERT INTO project_board(no,name,subject,content,pwd)
    VALUES((SELECT NVL(MAX(no)+1,1) FROM project_board),
        pName,pSubject,pContent,pPwd);
        COMMIT;
END;
/
 
-- 목록 출력
CREATE OR REPLACE PROCEDURE projectBoardListData(
    pStart NUMBER,
    pEND NUMBER,
    pResult OUT SYS_REFCURSOR
)
IS
BEGIN
    OPEN pResult FOR
    SELECT no,subject,name,regdate,hit,num
    FROM (SELECT no,subject,name,regdate,hit, rownum as num
    FROM (SELECT no,subject,name,regdate,hit
    FROM project_board ORDER BY no DESC))
    WHERE num BETWEEN pStart AND pEnd;
END;
/
 
 
-- 상세 보기
CREATE OR REPLACE PROCEDURE projectBoardListDetailData(
    pNo prject_board.no%type,
    pResult OUT SYS_REFCURSOR
)
IS
BEGIN
-- 조회수 증가
UPDATE project_board SET
hit=hit+1
WHERE no=pNo;
COMMIT;
-- 상세보기 내용
OPEN pResult FOR
    SELECT no,name,subject,content,regdate,hit
    FROM project_board
    WHERE no=pNo;
END;
/
 
 
 
-- 수정
CREATE OR REPLACE PROCEDURE projectBoardUpdateData(
    pNo project_board.no%TYPE,
    pResult OUT SYS_REFCURSOR
)
IS
BEGIN
    OPEN pResult FOR
        SELECT no,name,subject,content
        FROM project_board
        WHERE no=pNo;
END;
/
 
-- 실제 수정 
CREATE OR REPLACE PROCEDURE projectBoardUpdate(
    pNo project_board.no%TYPE,
    pName project_board.name%TYPE,
    pSubject project_board.subject%TYPE,
    pContent project_board.content%TYPE,
    pPwd project_board.pwd%TYPE,
    pResult OUT project_board.name%TYPE
)
IS
    vPwd project_board.pwd%type;
BEGIN
    SELECT pwd INTO vPwd
    FROM project_board
    WHERE no=pNo;
    
    IF(vPwd=pPwd) THEN
    pResult:='true';
    UPDATE project_board SET
    name=pName,subject=pSubject,content=pContent
    WHERE no=pNo;
    COMMIT;
    ELSE
        pResult:='false';
    END IF;
END;
/
 
 
 
-- 삭제
CREATE OR REPLACE PROCEDURE project_boardDelete(
    pNo project_board.no%TYPE,
    pPwd project_board.pwd%TYPE,
    pResult OUT project_board.name%TYPE
)
IS
    vPwd project_board.pwd%TYPE;
BEGIN
    SELECT pwd INTO vPwd
    FROM project_board
    WHERE no=pNo;
    
    IF(vPwd=pPwd) THEN
        pResult:='true';
        DELETE FROM project_board
        WHERE no=pNo;
        COMMIT;
    ELSE
        pResult:='false';
    END IF;
END;
/
 
 
--총페이지
CREATE OR REPLACE FUNCTION boardTotalPage RETURN NUMBER
IS
    pTotal NUMBER;
BEGIN
    SELECT CEIL(COUNT(*)/10.0) INTO pTotal
    FROM project_board;
    
    RETURN pTotal;
END;
 
 
 
 
 
-- 찾기
-- 댓글
CREATE TABLE project_reply(
    no NUMBER,
    type NUMBER,
    cno NUMBER,
    id VARCHAR2(20) CONSTRAINT pr_id_nn NOT NULL,
    name VARCHAR2(34) CONSTRAINT pr_name_nn NOT NULL,
    msg CLOB CONSTRAINT pr_msg_nn NOT NULL,
    regdate DATE DEFAULT SYSDATE
);
 
 
 
--목록
CREATE OR REPLACE PROCEDURE replyListData(
    pType project_reply.type%TYPE,
    pCno project_reply.cno%TYPE,
    pStart NUMBER,
    pEnd NUMBER,
    pResult OUT SYS_REFCURSOR
)
IS
BEGIN
    OPEN pResult FOR
    SELECT no,type,cno,id,name,msg,TO_CHAR(regdate,'YYYY-MM-DD HH24:MI:SS'),num
    FROM (SELECT no,type,cno,id,name,msg,regdate,rownum as num
    FROM (SELECT no,type,cno,id,name,msg,regdate
    FROM project_reply WHERE type=pType AND cno=pCno ORDER BY no DESC))
    WHERE num BETWEEN pStart AND pEND;
END;
/
 
--수정
CREATE OR REPLACE PROCEDURE replyUpdate(
    pNo project_reply.no%TYPE,
    pMsg project_reply.msg%TYPE
)
IS
BEGIN
    UPDATE project_reply SET
    msg=pMsg
    WHERE no=pNo;
    COMMIT;
END;
/
 
--삭제
CREATE OR REPLACE PROCEDURE replyDelete(
    pNo project_reply.no%TYPE
)
IS
BEGIN
    DELETE FROM project_reply
    WHERE no=pNo;
    COMMIT;
END;
/
 
 
--추가
CREATE OR REPLACE PROCEDURE replyInsert(
    pType project_reply.type%TYPE,
    pCno project_reply.cno%TYPE,
    pId project_reply.id%TYPE,
    pName project_reply.name%TYPE,
    pMsg project_reply.msg%TYPE
)
IS
    vNo project_reply.no%TYPE;
BEGIN
    SELECT NVL(MAX(no)+1,1) INTO vNo
    FROM project_reply;
    INSERT INTO project_reply(no,type,cno,id,name,msg)
    VALUES(vNo,pType,pCno,pId,pName,pMsg);
    COMMIT;
END;
/

 

 

 

# pom.xml

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 https://maven.apache.org/maven-v4_0_0.xsd">
    <modelVersion>4.0.0</modelVersion>
    <groupId>com.sist</groupId>
    <artifactId>web</artifactId>
    <name>OnLineSpringMVCStudy6</name>
    <packaging>war</packaging>
    <version>1.0.0-BUILD-SNAPSHOT</version>
    <properties>
       <!-- Spring 5 -->
        <java-version>1.8</java-version>
        <org.springframework-version>5.1.5.RELEASE</org.springframework-version>
        <org.aspectj-version>1.6.10</org.aspectj-version>
        <org.slf4j-version>1.6.6</org.slf4j-version>
    </properties>
    <repositories>
       <repository>
            <id>mesir-repo</id>
            <url>http://mesir.googlecode.com/svn/trunk/mavenrepo</url>
        </repository>
    </repositories>
    <dependencies>
        <dependency>
          <groupId>com.oracle</groupId>
          <artifactId>ojdbc14</artifactId>
          <version>10.2.0.4.0</version>
          <scope>system</scope>
          <systemPath>${project.basedir}/src/main/webapp/WEB-INF/lib/ojdbc14.jar</systemPath>
      </dependency>
        <!-- Spring -->
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-context</artifactId>
            <version>${org.springframework-version}</version>
            <exclusions>
                <!-- Exclude Commons Logging in favor of SLF4j -->
                <exclusion>
                    <groupId>commons-logging</groupId>
                    <artifactId>commons-logging</artifactId>
                 </exclusion>
            </exclusions>
        </dependency>
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-webmvc</artifactId>
            <version>${org.springframework-version}</version>
        </dependency>
        <!-- Jsoup -->
        <dependency>
            <groupId>org.jsoup</groupId>
            <artifactId>jsoup</artifactId>
            <version>1.13.1</version>
        </dependency>
        <!-- JSON -->
        <dependency>
            <groupId>com.googlecode.json-simple</groupId>
            <artifactId>json-simple</artifactId>
            <version>1.1.1</version>
        </dependency>
        <!-- JAXB -->
        <dependency>
            <groupId>javax.xml.bind</groupId>
            <artifactId>jaxb-api</artifactId>
            <version>2.3.1</version>
        </dependency>
        <!-- AspectJ -->
        <dependency>
         <groupId>org.springframework</groupId>
         <artifactId>spring-context-support</artifactId>
         <version>5.1.5.RELEASE</version>
      </dependency>
      <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-context</artifactId>
            <version>5.1.5.RELEASE</version>
        </dependency>
        <!-- lombok -->
        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <version>1.18.8</version>
            <scope>provided</scope>
        </dependency>
      <!-- websocket -->
      <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-websocket</artifactId>
            <version>5.1.5.RELEASE</version>
        </dependency>
      
      <!-- <dependency>
         <groupId>org.springframework</groupId>
         <artifactId>spring-expression</artifactId>
         <version>5.1.5.RELEASE</version>
      </dependency> -->
      <dependency>
         <groupId>org.springframework</groupId>
         <artifactId>spring-jdbc</artifactId>
         <version>5.1.5.RELEASE</version>
      </dependency>
      <dependency>
         <groupId>org.springframework</groupId>
         <artifactId>spring-instrument</artifactId>
         <version>5.1.5.RELEASE</version>
      </dependency>
      <dependency>
         <groupId>org.springframework</groupId>
         <artifactId>spring-aspects</artifactId>
         <version>5.1.5.RELEASE</version>
      </dependency>
      <!-- <dependency>
         <groupId>com.oracle</groupId>
         <artifactId>ojdbc14</artifactId>
         <version>10.2.0.4.0</version>
      </dependency> -->
      <dependency>
         <groupId>org.springframework</groupId>
         <artifactId>spring-jms</artifactId>
         <version>5.1.5.RELEASE</version>
      </dependency>
      <dependency>
         <groupId>org.springframework</groupId>
         <artifactId>spring-orm</artifactId>
         <version>5.1.5.RELEASE</version>
      </dependency>
      <dependency>
         <groupId>org.springframework</groupId>
         <artifactId>spring-oxm</artifactId>
         <version>5.1.5.RELEASE</version>
      </dependency>
      <dependency>
         <groupId>org.springframework</groupId>
         <artifactId>spring-test</artifactId>
         <version>5.1.5.RELEASE</version>
      </dependency>
      <!-- <dependency>
         <groupId>org.springframework</groupId>
         <artifactId>spring-webmvc-portlet</artifactId>
         <version>5.1.5.RELEASE</version>
      </dependency> -->
      
      <dependency>
         <groupId>taglibs</groupId>
         <artifactId>standard</artifactId>
         <version>1.1.2</version>
      </dependency>
 
      <dependency>
         <groupId>org.nuiton.thirdparty</groupId>
         <artifactId>Rserve</artifactId>
         <version>1.7-3</version>
      </dependency>
      <dependency>
         <groupId>org.nuiton.thirdparty</groupId>
         <artifactId>REngine</artifactId>
         <version>1.7-3</version>
      </dependency>
      <!-- 
         @Select("<script>SELECT~~ <if test="">")
       -->
      <dependency>
         <groupId>org.mybatis</groupId>
         <artifactId>mybatis</artifactId>
         <version>3.2.8</version>
      </dependency>
      <dependency>
         <groupId>org.mybatis</groupId>
         <artifactId>mybatis-spring</artifactId>
         <version>1.2.0</version>
      </dependency>
      <dependency>
         <groupId>org.mongodb</groupId>
         <artifactId>mongo-java-driver</artifactId>
         <version>2.13.0-rc1</version>
      </dependency>
      <dependency>
         <groupId>org.nuiton.thirdparty</groupId>
         <artifactId>JRI</artifactId>
         <version>0.9-6</version>
      </dependency>
      <dependency>
         <groupId>com.github.lucarosellini.rJava</groupId>
         <artifactId>JRIEngine</artifactId>
         <version>0.9-7</version>
      </dependency>
      <dependency>
         <groupId>aopalliance</groupId>
         <artifactId>aopalliance</artifactId>
         <version>1.0</version>
      </dependency>
      <dependency>
         <groupId>asm</groupId>
         <artifactId>asm</artifactId>
         <version>3.3.1</version>
      </dependency>
      <dependency>
         <groupId>c3p0</groupId>
         <artifactId>c3p0</artifactId>
         <version>0.9.1.2</version>
      </dependency>
      <dependency>
         <groupId>cglib</groupId>
         <artifactId>cglib</artifactId>
         <version>3.1</version>
      </dependency>
      <dependency>
         <groupId>cglib</groupId>
         <artifactId>cglib-nodep</artifactId>
         <version>3.1</version>
      </dependency>
      <dependency>
         <groupId>commons-collections</groupId>
         <artifactId>commons-collections</artifactId>
         <version>3.2.1</version>
      </dependency>
      <dependency>
         <groupId>commons-dbcp</groupId>
         <artifactId>commons-dbcp</artifactId>
         <version>1.4</version>
      </dependency>
      <dependency>
         <groupId>commons-fileupload</groupId>
         <artifactId>commons-fileupload</artifactId>
         <version>1.3.1</version>
      </dependency>
      <dependency>
         <groupId>commons-io</groupId>
         <artifactId>commons-io</artifactId>
         <version>2.4</version>
      </dependency>
      <dependency>
         <groupId>commons-logging</groupId>
         <artifactId>commons-logging</artifactId>
         <version>1.2</version>
      </dependency>
      <dependency>
         <groupId>commons-pool</groupId>
         <artifactId>commons-pool</artifactId>
         <version>1.6</version>
      </dependency>
      <dependency>
         <groupId>org.javassist</groupId>
         <artifactId>javassist</artifactId>
         <version>3.18.2-GA</version>
      </dependency>
      
      <dependency>
         <groupId>org.nuiton.thirdparty</groupId>
         <artifactId>Rserve</artifactId>
         <version>1.7-3</version>
      </dependency>
      <dependency>
         <groupId>org.nuiton.thirdparty</groupId>
         <artifactId>REngine</artifactId>
         <version>1.7-3</version>
      </dependency>
      <dependency>
            <groupId>org.twitter4j</groupId>
            <artifactId>twitter4j-core</artifactId>
            <version>4.0.2</version>
        </dependency>
        <dependency>
            <groupId>org.twitter4j</groupId>
            <artifactId>twitter4j-stream</artifactId>
            <version>4.0.2</version>
        </dependency>
        <dependency>
            <groupId>javax.validation</groupId>
            <artifactId>validation-api</artifactId>
            <version>1.1.0.Final</version>
        </dependency>
       <!--  <dependency>
            <groupId>com.oracle</groupId>
            <artifactId>ojdbc14</artifactId>
            <version>10.2.0.4.0</version>
        </dependency> -->
        
        <!-- End -->    
        <!-- AspectJ -->
        <dependency>
            <groupId>org.aspectj</groupId>
            <artifactId>aspectjrt</artifactId>
            <version>${org.aspectj-version}</version>
        </dependency>    
         <!-- AOP -->
      <dependency>
            <groupId>org.aspectj</groupId>
            <artifactId>aspectjweaver</artifactId>
            <version>1.6.5</version>
        </dependency>
      <!-- AOP -->
      <dependency>
         <groupId>aopalliance</groupId>
         <artifactId>aopalliance</artifactId>
         <version>1.0</version>
      </dependency>
      <!-- AOP -->
      <dependency>
        <groupId>asm</groupId>
        <artifactId>asm</artifactId>
        <version>3.1</version>
    </dependency>
        <!-- tiles  -->
        <dependency>
            <groupId>org.apache.tiles</groupId>
            <artifactId>tiles-core</artifactId>
            <version>2.2.2</version>
        </dependency>
        <dependency>
            <groupId>org.apache.tiles</groupId>
            <artifactId>tiles-jsp</artifactId>
            <version>2.2.2</version>
        </dependency>
        <dependency>
            <groupId>org.apache.tiles</groupId>
            <artifactId>tiles-api</artifactId>
            <version>2.2.2</version>
        </dependency>
        
        <dependency>
            <groupId>org.apache.tiles</groupId>
            <artifactId>tiles-servlet</artifactId>
            <version>2.2.2</version>
        </dependency>
        
        <dependency>
            <groupId>commons-beanutils</groupId>
            <artifactId>commons-beanutils</artifactId>
            <version>1.9.2</version>
        </dependency>
        
        <dependency>
            <groupId>commons-digester</groupId>
            <artifactId>commons-digester</artifactId>
            <version>2.1</version>
        </dependency>
        
        <dependency>
            <groupId>org.antlr</groupId>
            <artifactId>antlr-runtime</artifactId>
            <version>3.5.2</version>
        </dependency>
        <!-- XML -->
        <dependency>
            <groupId>com.sun.xml.bind</groupId>
            <artifactId>jaxb-core</artifactId>
            <version>2.2.11</version>
        </dependency>
        <dependency>
            <groupId>com.sun.xml.bind</groupId>
            <artifactId>jaxb-impl</artifactId>
            <version>2.2.11</version>
        </dependency>
 
        <dependency>
            <groupId>javax.xml.bind</groupId>
            <artifactId>jaxb-api</artifactId>
            <version>2.2.11</version>
        </dependency>
        
        <!-- 
           jaxb-core
           jaxb-impl
           jaxb-api
           
           json-simple
           commons-lang
           ezmorph
         -->
        <!-- JSON -->
        <dependency>
            <groupId>com.googlecode.json-simple</groupId>
            <artifactId>json-simple</artifactId>
            <version>1.1.1</version>
        </dependency>
        <dependency>
            <groupId>commons-lang</groupId>
            <artifactId>commons-lang</artifactId>
            <version>2.6</version>
        </dependency>
        <dependency>
            <groupId>net.sf.ezmorph</groupId>
            <artifactId>ezmorph</artifactId>
            <version>1.0.6</version>
        </dependency>
        
        <!-- Logging -->
        <dependency>
            <groupId>org.slf4j</groupId>
            <artifactId>slf4j-api</artifactId>
            <version>${org.slf4j-version}</version>
        </dependency>
        <dependency>
            <groupId>org.slf4j</groupId>
            <artifactId>jcl-over-slf4j</artifactId>
            <version>${org.slf4j-version}</version>
            <scope>runtime</scope>
        </dependency>
        <dependency>
            <groupId>org.slf4j</groupId>
            <artifactId>slf4j-log4j12</artifactId>
            <version>${org.slf4j-version}</version>
            <scope>runtime</scope>
        </dependency>
        <dependency>
            <groupId>log4j</groupId>
            <artifactId>log4j</artifactId>
            <version>1.2.15</version>
            <exclusions>
                <exclusion>
                    <groupId>javax.mail</groupId>
                    <artifactId>mail</artifactId>
                </exclusion>
                <exclusion>
                    <groupId>javax.jms</groupId>
                    <artifactId>jms</artifactId>
                </exclusion>
                <exclusion>
                    <groupId>com.sun.jdmk</groupId>
                    <artifactId>jmxtools</artifactId>
                </exclusion>
                <exclusion>
                    <groupId>com.sun.jmx</groupId>
                    <artifactId>jmxri</artifactId>
                </exclusion>
            </exclusions>
            <scope>runtime</scope>
        </dependency>
 
        <!-- @Inject -->
        <dependency>
            <groupId>javax.inject</groupId>
            <artifactId>javax.inject</artifactId>
            <version>1</version>
        </dependency>
                
        <!-- Servlet -->
        <dependency>
            <groupId>javax.servlet</groupId>
            <artifactId>servlet-api</artifactId>
            <version>2.5</version>
            <scope>provided</scope>
        </dependency>
        <dependency>
            <groupId>javax.servlet.jsp</groupId>
            <artifactId>jsp-api</artifactId>
            <version>2.1</version>
            <scope>provided</scope>
        </dependency>
        <dependency>
            <groupId>javax.servlet</groupId>
            <artifactId>jstl</artifactId>
            <version>1.2</version>
        </dependency>
    
        <!-- Test -->
        <dependency>
            <groupId>junit</groupId>
            <artifactId>junit</artifactId>
            <version>4.7</version>
            <scope>test</scope>
        </dependency>        
    </dependencies>
 
    <build>
        <plugins>
            <plugin>
                <artifactId>maven-eclipse-plugin</artifactId>
                <version>2.9</version>
                <configuration>
                    <additionalProjectnatures>
                        <projectnature>org.springframework.ide.eclipse.core.springnature</projectnature>
                    </additionalProjectnatures>
                    <additionalBuildcommands>
                        <buildcommand>org.springframework.ide.eclipse.core.springbuilder</buildcommand>
                    </additionalBuildcommands>
                    <downloadSources>true</downloadSources>
                    <downloadJavadocs>true</downloadJavadocs>
                </configuration>
            </plugin>
            <plugin>
                <!-- Spring 5 -->
                <groupId>org.apache.maven.plugins</groupId>
                <artifactId>maven-compiler-plugin</artifactId>
                <version>3.7.0</version>
                <configuration>
                    <source>1.8</source>
                    <target>1.8</target>
                    <compilerArgument>-Xlint:all</compilerArgument>
                    <showWarnings>true</showWarnings>
                    <showDeprecation>true</showDeprecation>
                </configuration>
            </plugin>
            <plugin>
                <groupId>org.codehaus.mojo</groupId>
                <artifactId>exec-maven-plugin</artifactId>
                <version>1.2.1</version>
                <configuration>
                    <mainClass>org.test.int1.Main</mainClass>
                </configuration>
            </plugin>
        </plugins>
    </build>
</project>

 

 

 

# web.xml

<?xml version="1.0" encoding="UTF-8"?>
<web-app version="2.5" xmlns="http://java.sun.com/xml/ns/javaee"
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xsi:schemaLocation="http://java.sun.com/xml/ns/javaee https://java.sun.com/xml/ns/javaee/web-app_2_5.xsd">
 
    <servlet>
        <servlet-name>dispatcher</servlet-name>
        <servlet-class>org.springframework.web.servlet.DispatcherServlet</servlet-class>
        <!-- 
            XML파일이 여러개를 사용할 때 
         -->
         <init-param>
           <param-name>contextConfigLocation</param-name>
           <param-value>/WEB-INF/config/application-*.xml</param-value>
         </init-param>
    </servlet>
    <servlet-mapping>
        <servlet-name>dispatcher</servlet-name>
        <url-pattern>*.do</url-pattern>
    </servlet-mapping>
    <!-- 한글 변환 ( request를 사용하지 않는다  )-->
      <filter>
            <filter-name>encodingFilter</filter-name>
            <filter-class>org.springframework.web.filter.CharacterEncodingFilter</filter-class>
            <init-param>
                <param-name>encoding</param-name>
                <param-value>UTF-8</param-value>
            </init-param>
            <init-param>
                <param-name>forceEncoding</param-name>
                <param-value>true</param-value>
            </init-param>
        </filter>
        
        <!-- /의 형식으로 시작하는 url에 대하여 UTF-8로 인코딩 -->
        <filter-mapping>
            <filter-name>encodingFilter</filter-name>
            <url-pattern>/*</url-pattern>
        </filter-mapping>
</web-app>

 

 

# application-context.xml

<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xmlns:context="http://www.springframework.org/schema/context"
    xmlns:p="http://www.springframework.org/schema/p"
    xmlns:c="http://www.springframework.org/schema/c"
    xmlns:aop="http://www.springframework.org/schema/aop"
    xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans.xsd
        http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context-4.3.xsd
        http://www.springframework.org/schema/aop http://www.springframework.org/schema/aop/spring-aop-4.3.xsd">
    <!-- 1. 사용자 정의 클래스 등록 -->
     <aop:aspectj-autoproxy></aop:aspectj-autoproxy>
     <context:component-scan base-package="com.sist.*"/>
     <bean id="dbcon"
        class="com.sist.board.dao.DBConnection"
        c:driver="oracle.jdbc.driver.OracleDriver"
        c:url="jdbc:oracle:thin:@211.238.142.181:1521:XE"
        c:username="hr"
        c:password="happy"
     />
     <!-- 2. 데이터베이스 관련 -->
     <!-- 2-1. 데이터베이스 정보를 모운다 -->
     <bean id="ds" class="org.apache.commons.dbcp.BasicDataSource"
       p:driverClassName="oracle.jdbc.driver.OracleDriver"
       p:url="jdbc:oracle:thin:@211.238.142.181:1521:XE"
       p:username="hr"
       p:password="happy"
       p:maxActive="20"
       p:maxIdle="10"
       p:maxWait="-1"
     />
     <!-- 2-2. SqlSessionFactory 처리 -->
     <bean id="ssf" class="org.mybatis.spring.SqlSessionFactoryBean"
       p:dataSource-ref="ds"
     />
     <!-- 2-3. Mapper구현 (인터페이스 구현) -->
     <bean id="mapper" class="org.mybatis.spring.mapper.MapperFactoryBean"
       p:sqlSessionFactory-ref="ssf"
       p:mapperInterface="com.sist.dao.EmpMapper"
     /> 
    
      <bean id="viewResolver"
         class="org.springframework.web.servlet.view.InternalResourceViewResolver"
         p:prefix="/"
         p:suffix=".jsp"
       />
</beans>

 

 

 

# BoardVO.java

package com.sist.board.dao;
import java.util.*;
public class BoardVO {
  private int no,hit;
  private String name,subject,content,pwd;
  private Date regdate;
public int getNo() {
    return no;
}
public void setNo(int no) {
    this.no = no;
}
public int getHit() {
    return hit;
}
public void setHit(int hit) {
    this.hit = hit;
}
public String getName() {
    return name;
}
public void setName(String name) {
    this.name = name;
}
public String getSubject() {
    return subject;
}
public void setSubject(String subject) {
    this.subject = subject;
}
public String getContent() {
    return content;
}
public void setContent(String content) {
    this.content = content;
}
public String getPwd() {
    return pwd;
}
public void setPwd(String pwd) {
    this.pwd = pwd;
}
public Date getRegdate() {
    return regdate;
}
public void setRegdate(Date regdate) {
    this.regdate = regdate;
}
  
}

 

 

 

# BoardDAO.java

package com.sist.board.dao;
 
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Repository;
 
import oracle.jdbc.OracleTypes;
import oracle.jdbc.oracore.OracleType;
 
import java.sql.CallableStatement;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.*;
@Repository
public class BoardDAO {
    @Autowired
    private DBConnection dbConn;
    private CallableStatement cs;//Procedure
    private PreparedStatement ps;//SQL
    public List<BoardVO> boardListData(int page)
    {
        System.out.println("BoardDAO:"+dbConn);
        List<BoardVO> list=new ArrayList<BoardVO>();
        // dbConn.getConnection()
 
        try
        {
            String sql="{CALL projetBoardListData(?,?,?)}";
            cs=dbConn.getConn().prepareCall(sql);
            int rowSize=10;
            int start=(rowSize*page)-(rowSize-1);
            int end=rowSize*page;
            cs.setInt(1, start);
            cs.setInt(2, end);
            cs.registerOutParameter(3, OracleTypes.CURSOR);
            // 실행
            cs.executeQuery();
            // 데이터 받기
            ResultSet rs=(ResultSet)cs.getObject(3);
            while(rs.next())
            {
                BoardVO vo=new BoardVO();
                vo.setNo(rs.getInt(1));
                vo.setSubject(rs.getString(2));
                vo.setRegdate(rs.getDate(4));
                vo.setName(rs.getString(3));
                vo.setHit(rs.getInt(5));
                list.add(vo);
            }
            rs.close();
        }catch(Exception ex){}
        // dbConn.disConnection
        return list;
    }
 
    public void boardInsert(BoardVO vo)
    {
        try
        {
            String sql="{CALL projectBoardInsert(?,?,?,?)}";
            cs=dbConn.getConn().prepareCall(sql);
            cs.setString(1, vo.getName());
            cs.setString(2, vo.getSubject());
            cs.setString(3, vo.getContent());
            cs.setString(4, vo.getPwd());
            
            cs.executeQuery();
        }catch(Exception ex){}
    }
 
    public BoardVO boardDetailData(int no)
    {
        BoardVO vo=new BoardVO();
        // getConnection() => @Before
        try
        {
            String sql="{CALL projectBoardDetailData(?,?)}";
            cs=dbConn.getConn().prepareCall(sql);
            cs.setInt(1, no);
            cs.registerOutParameter(2, OracleTypes.CURSOR);
            cs.executeQuery();
            // 데이터 받기
            ResultSet rs=(ResultSet)cs.getObject(2);
            rs.next();
            vo.setNo(rs.getInt(1));
            vo.setName(rs.getString(2));
            vo.setSubject(rs.getString(3));
            vo.setContent(rs.getString(4));
            vo.setRegdate(rs.getDate(5));
            vo.setHit(rs.getInt(6));
            rs.close();
        }catch(Exception ex){}
        // disConnection() => @After
        return vo;
    }
 
    public BoardVO boardUpdateData(int no)
    {
        BoardVO vo=new BoardVO();
        // getConnection() => Before
        try
        {
            String sql="{CALL projectBoardUpdateData(?,?)}";
            cs=dbConn.getConn().prepareCall(sql);
            // 실행 요청 ?에 값을 채운다 
            cs.setInt(1, no);
            // OUT => 저장 공간을 만들어 준다 
            cs.registerOutParameter(2, OracleTypes.CURSOR);
            cs.executeQuery();
            // 저장공간에서 값을 가지고 온다 
            ResultSet rs=(ResultSet)cs.getObject(2);
            rs.next();
            vo.setNo(rs.getInt(1));
            vo.setName(rs.getString(2));
            vo.setSubject(rs.getString(3));
            vo.setContent(rs.getString(4));
            rs.close();
            // Client => DAO(X)
            // Client <===> Model <===> DAO
        }catch(Exception ex){/*AfterThrowing*/} // AOP에서 처리 
        // disConnection() => After
        return vo; // AfterReturning
    }
 
    public boolean boardUpdate(BoardVO vo)
    {
        boolean bCheck=false;
        // dbConn.getConnection()
        try
        {
            String sql="{CALL projectBoardUpdate(?,?,?,?,?,?)}";
            // 전송 => 오라클 
            
            cs=dbConn.getConn().prepareCall(sql);
            cs.setInt(1, vo.getNo());
            cs.setString(2, vo.getName());
            cs.setString(3, vo.getSubject());
            cs.setString(4, vo.getContent());
            cs.setString(5, vo.getPwd());
            cs.registerOutParameter(6, OracleTypes.VARCHAR);
            cs.executeQuery();
            String result=cs.getString(6);
            // Cursor => 자바(X) => ResultSet
            bCheck=Boolean.parseBoolean(result);
            // "10"
        }catch(Exception ex){/*ex.printStackTrace();*/}
        // dbConn.disConnection()
        // System.out.println("obj="+obj);
        return bCheck;
    }
 
    public boolean boardDelete(int no,String pwd)
    {
        boolean bCheck=false;
        try
        {
            String sql="{CALL projectBoardDelete(?,?,?)}";
            cs=dbConn.getConn().prepareCall(sql);
            cs.setInt(1, no);
            cs.setString(2, pwd);
            cs.registerOutParameter(3, OracleTypes.VARCHAR);
            cs.executeQuery();
            String result=cs.getString(3);
            bCheck=Boolean.parseBoolean(result);
        }catch(Exception ex){}
        return bCheck;
    }
    
    public int boardTotalPage()
    {
        int total=0;
        try
        {
            String sql="SELECT boardTotalPage() FROM project_board";
            ps=dbConn.getConn().prepareStatement(sql);
            ResultSet rs=ps.executeQuery();
            rs.next();
            total=rs.getInt(1);
            rs.close();
            ps.close();
            /*
             *   PROCEDURE => {CALL pro_name()}
             *   FUNCTION => SELECT func_name() ~
             */
        }catch(Exception ex){}
        return total;
    }
 
    // 댓글 
    public List<ReplyVO> replyListData(int type,int cno,int page)
    {
        List<ReplyVO> list=
                new ArrayList<ReplyVO>();
        // dbConn.getConnection()
        try
        {
            String sql="{CALL replyListData(?,?,?,?,?)}";
            cs=dbConn.getConn().prepareCall(sql);
            cs.setInt(1, type);
            cs.setInt(2, cno);
            int rowSize=5;
            int start=(rowSize*page)-(rowSize-1);
            int end=rowSize*page;
            cs.setInt(3, start);
            cs.setInt(4, end);
            cs.registerOutParameter(5, OracleTypes.CURSOR);
            cs.executeQuery();
            ResultSet rs=(ResultSet)cs.getObject(5);
            //no,type,cno,id,name,msg,TO_CHAR(regdate
            while(rs.next())
            {
                ReplyVO vo=new ReplyVO();
                vo.setNo(rs.getInt(1));
                vo.setType(rs.getInt(2));
                vo.setCno(rs.getInt(3));
                vo.setId(rs.getString(4));
                vo.setName(rs.getString(5));
                vo.setMsg(rs.getString(6));
                vo.setDbday(rs.getString(7));
                list.add(vo);
            }
            rs.close();
        }catch(Exception ex){}
        // dbConn.disConnection()
        return list;
    }
 
    public void replyInsert(ReplyVO vo)
    {
        // dbConn.getConnection()
        try
        {
            String sql="{CALL replyInsert(?,?,?,?,?)}";
            cs=dbConn.getConn().prepareCall(sql);
            cs.setInt(1, vo.getType());
            cs.setInt(2, vo.getCno());
            cs.setString(3, vo.getId());
            cs.setString(4, vo.getName());
            cs.setString(5, vo.getMsg());
            
            cs.executeQuery();
        }catch(Exception ex){}
        //dbConn.disConnection()
    }
 
    public void replyUpdate(int no,String msg)
    {
        try
        {
            String sql="{CALL replyUpdate(?,?)}";
            cs=dbConn.getConn().prepareCall(sql);
            cs.setInt(1, no);
            cs.setString(2, msg);
            cs.executeQuery();
        }catch(Exception ex){}
    }
 
    // 삭제
    public void replyDelete(int no)
    {
        try
        {
            String sql="{CALL replyDelete(?)}";
            cs=dbConn.getConn().prepareCall(sql);
            cs.setInt(1, no);
            cs.executeQuery();
        }catch(Exception ex){}
    }
    
    // 로그인 
    public MemberVO memberLogin(String id,String pwd)
    {
        MemberVO vo=new MemberVO();
        try
        {
            dbConn.getConnection();
            String sql="SELECT COUNT(*) FROM member "
                      +"WHERE id=?";
            ps=dbConn.getConn().prepareStatement(sql);
            ps.setString(1, id);
            ResultSet rs=ps.executeQuery();
            rs.next();
            int count=rs.getInt(1);
            rs.close();
            
            if(count==0)//ID가 없는 상태
            {
                vo.setMessage("NOID");
            }
            else // ID가 존재하는 상태 
            {
                sql="SELECT pwd,name FROM member "
                   +"WHERE id=?";
                ps=dbConn.getConn().prepareStatement(sql);
                ps.setString(1, id);
                rs=ps.executeQuery();
                rs.next();
                String db_pwd=rs.getString(1);
                String name=rs.getString(2);
                rs.close();
                
                if(db_pwd.equals(pwd))//로그인
                {
                    vo.setId(id);
                    vo.setName(name);
                    vo.setMessage("OK");
                }
                else//비밀번호가 틀린 경우
                {
                    vo.setMessage("NOPWD");
                }
            }
        }catch(Exception ex)
        {
            System.out.println(ex.getMessage());
        }
        finally
        {
            dbConn.disConnection();
        }
        return vo;
    }
    
}
 
 

 

 

 

# DBConnection.java

package com.sist.board.dao;
 
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
 
public class DBConnection {
    private Connection conn;
    private CallableStatement cs;
    private String driver,url,username,password;
    public DBConnection(String driver,String url,String username,String password)
    {
        this.driver=driver;
        this.url=url;
        this.username=username;
        this.password=password;
        
        try
        {
            Class.forName(driver);
        }catch(Exception ex){}
    }
    public void getConnection()
    {
        try
        {
            conn=DriverManager.getConnection(url,username,password);
        }catch(Exception ex){}
    }
    public void disConnection()
    {
        try
        {
            if(conn!=null) conn.close();
            if(cs!=null) cs.close();
        }catch(Exception ex){}
    }
    public Connection getConn() {
        return conn;
    }
    public void setConn(Connection conn) {
        this.conn = conn;
    }
    public CallableStatement getCs() {
        return cs;
    }
    public void setCs(CallableStatement cs) {
        this.cs = cs;
    }
    
}

 

 

 

# MemberVO.java

package com.sist.board.dao;
 
public class MemberVO {
    private String id;
    private String pwd;
    private String name;
    private String message;
    
    public String getMessage() {
        return message;
    }
    public void setMessage(String message) {
        this.message = message;
    }
    public String getId() {
        return id;
    }
    public void setId(String id) {
        this.id = id;
    }
    public String getPwd() {
        return pwd;
    }
    public void setPwd(String pwd) {
        this.pwd = pwd;
    }
    public String getName() {
        return name;
    }
    public void setName(String name) {
        this.name = name;
    }
      
}

 

 

 

# ReplyVO.java

package com.sist.board.dao;
import java.util.*;
public class ReplyVO {
   private int no;
   private int type;
   private int cno;
   private String id;
   private String name;
   private String msg;
   private Date regdate;
   private String dbday;
public int getNo() {
    return no;
}
public void setNo(int no) {
    this.no = no;
}
public int getType() {
    return type;
}
public void setType(int type) {
    this.type = type;
}
public int getCno() {
    return cno;
}
public void setCno(int cno) {
    this.cno = cno;
}
public String getId() {
    return id;
}
public void setId(String id) {
    this.id = id;
}
public String getName() {
    return name;
}
public void setName(String name) {
    this.name = name;
}
public String getMsg() {
    return msg;
}
public void setMsg(String msg) {
    this.msg = msg;
}
public Date getRegdate() {
    return regdate;
}
public void setRegdate(Date regdate) {
    this.regdate = regdate;
}
public String getDbday() {
    return dbday;
}
public void setDbday(String dbday) {
    this.dbday = dbday;
}
   
}
 

 

 

# DBAspect.java

package com.sist.common;
 
import org.aspectj.lang.annotation.After;
import org.aspectj.lang.annotation.AfterReturning;
import org.aspectj.lang.annotation.AfterThrowing;
import org.aspectj.lang.annotation.Aspect;
import org.aspectj.lang.annotation.Before;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Component;
 
import com.sist.board.dao.DBConnection;
 
@Aspect
//<aop:aspectj-autoproxy></aop:aspectj-autoproxy>
@Component
public class DBAspect {
   @Autowired
   private DBConnection dbCon;
   
   @Before("execution(* com.sist.board.dao.BoardDAO.board*(..))||execution(* com.sist.board.dao.BoardDAO.reply*(..))")
   public void before()
   {
       System.out.println("DBAspect:"+dbCon);
       dbCon.getConnection();
   }
   @After("execution(* com.sist.board.dao.BoardDAO.board*(..))||execution(* com.sist.board.dao.BoardDAO.reply*(..))")
   public void after()
   {
       dbCon.disConnection();
   }
   @AfterThrowing(value="execution(* com.sist.board.dao.BoardDAO.board*(..))||execution(* com.sist.board.dao.BoardDAO.reply*(..))",throwing="ex")
   public void afterThrowing(Throwable ex)
   {
       ex.printStackTrace();
   }
   @AfterReturning(value="execution(* com.sist.board.dao.BoardDAO.board*(..))||execution(* com.sist.board.dao.BoardDAO.reply*(..))",returning="obj")
   public void afterReturning(Object obj)
   {
       System.out.println("obj="+obj);
   }
}