6.(스프링4 게시판)[MyBatis적용,개요및XML설정파일]Spring4 @MVC 게시판-멀티파일업로드,답변형,JSR303 @Valid,@Controller,@ModelAttribute,@SessionAttributes,어노테이션기반,마이바티스,오라클자바커뮤니티
이전까지 구현한 게시판에 MyBatis Mapper를 적용하여 보자.
구현한 기능 및 기술은 다음과 같다.
- Spring4 WEB MVC
- @MVC 어노테이션기반(@Controller, @ModelAttribute, @SessionAttributes)
- Spring 3 MVC And JSR303 Bean검증, @Valid 이용한 입력값 검증(글입력, 답변글 입력)
- 글리스트보기, 글입력, 글읽기, 글수정, 글삭제, 답변글작성, 커멘트입력
- Spring MultipartFile을 이용한 멀티 파일 업로드
- Spring AOP를 이용한 DML처리 로깅기능
- Spring JDBC --> MyBatis3 적용
0. 테이블 및 데이터, 시퀀스 생성
drop table board
create table board (
seq number(10),
name varchar2(20),
passwd varchar2(20),
title varchar2(500),
content varchar2(4000),
filename1 varchar2(100),
filename2 varchar2(100),
filename3 varchar2(100),
regdate date default sysdate,
readcount number(10),
-- 답변인경우 어느글의 답변인지 상위글 번호,최상위글인 경우 자신의 글번호 동일,
-- 리스트보기에서 정렬시 우선 reply로 우선하게 된다.
reply number(10),
-- 하나의 글 아래에 생기는 모든 답변들에 대해 순차적으로 1씩 증가, reply_level과 관계없이
reply_step number(10),
-- 1차,2차 답글인지 여부, 하나의 글에 답변이 두개면 그 두답변은 reply_level이 같다. 리스트보기에서
-- reply_level에 따라 들여쓰기를 한다.
reply_level number(10)
);
drop table comment_t
create table comment_t(
seq number(10),
name varchar2(20),
comm varchar2(4000)
);
insert into board (seq, name, passwd, title, content, readcount, reply, reply_step)
values (1, '오라클자바1', '1111', '질문입니다', 'SQL을 배울 수 있는 과정이 뭐죠?', 0, 1, 0)
insert into board (seq, name, passwd, title, content, readcount, reply, reply_step)
values (2, ‘오라클자바2’, '1111', 'Spring강좌질문', ‘과정 추천해 주세요?', 0, 2, 0)
commit;
drop sequence board_seq;
create sequence board_seq start with 3 increment by 1;
-- Spring AOP적용 로깅용 테이블
create table boardlog (
method varchar2(50),
sql varchar2(1000),
ilsi date);
1. Spring4 MVC Project생성
프로젝트이름 : spring4board2
2. 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 http://maven.apache.org/maven-v4_0_0.xsd">
<modelVersion>4.0.0</modelVersion>
<groupId>com.onj</groupId>
<artifactId>onjboard4</artifactId>
<name>onjboard4</name>
<packaging>war</packaging>
<version>1.0.0-BUILD-SNAPSHOT</version>
<properties>
<java-version>1.6</java-version>
<org.springframework-version>4.0.6.RELEASE</org.springframework-version>
<org.aspectj-version>1.8.0</org.aspectj-version>
<org.slf4j-version>1.6.6</org.slf4j-version>
</properties>
<dependencies>
<!-- Spring -->
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-context</artifactId>
<version>${org.springframework-version}</version>
</dependency>
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-core</artifactId>
<version>${org.springframework-version}</version>
</dependency>
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-beans</artifactId>
<version>${org.springframework-version}</version>
</dependency>
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-web</artifactId>
<version>${org.springframework-version}</version>
</dependency>
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-webmvc</artifactId>
<version>${org.springframework-version}</version>
</dependency>
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-jdbc</artifactId>
<version>${org.springframework-version}</version>
</dependency>
<!-- ORACLE -->
<dependency>
<groupId>com.oracle</groupId>
<artifactId>ojdbc6</artifactId>
<version>11.1.0.7.0</version>
</dependency>
<!-- Apache Commons file upload -->
<dependency>
<groupId>commons-fileupload</groupId>
<artifactId>commons-fileupload</artifactId>
<version>1.3.1</version>
</dependency>
<!-- Apache Commons IO -->
<dependency>
<groupId>org.apache.commons</groupId>
<artifactId>commons-io</artifactId>
<version>1.3.2</version>
</dependency>
<!-- DBCP -->
<dependency>
<groupId>commons-dbcp</groupId>
<artifactId>commons-dbcp</artifactId>
<version>1.2.2</version>
</dependency>
<!-- MyBatis -->
<dependency>
<groupId>org.mybatis</groupId>
<artifactId>mybatis</artifactId>
<version>3.2.7</version>
</dependency>
<dependency>
<groupId>org.mybatis</groupId>
<artifactId>mybatis-spring</artifactId>
<version>1.2.2</version>
</dependency>
<!-- Bean Validation API -->
<dependency>
<groupId>javax.validation</groupId>
<artifactId>validation-api</artifactId>
<version>1.1.0.Final</version>
</dependency>
<dependency>
<groupId>org.hibernate</groupId>
<artifactId>hibernate-validator</artifactId>
<version>5.1.2.Final</version>
</dependency>
<!-- AspectJ -->
<dependency>
<groupId>org.aspectj</groupId>
<artifactId>aspectjrt</artifactId>
<version>${org.aspectj-version}</version>
</dependency>
<dependency>
<groupId>org.aspectj</groupId>
<artifactId>aspectjweaver</artifactId>
<version>${org.aspectj-version}</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>javax.servlet-api</artifactId>
<version>3.0.1</version>
</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>
<repositories>
<repository>
<id>oracle</id>
<name>ORACLE JDBCRepository</name>
<url>http://maven.jahia.org/maven2</url>
</repository>
</repositories>
<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>
<groupId>org.apache.maven.plugins</groupId>
<artifactId>maven-compiler-plugin</artifactId>
<version>2.5.1</version>
<configuration>
<source>1.6</source>
<target>1.6</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>
3. /WEB-INF/action-servlet.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:mvc="http://www.springframework.org/schema/mvc"
xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans-4.0.xsd
http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context-4.0.xsd
http://www.springframework.org/schema/mvc http://www.springframework.org/schema/mvc/spring-mvc-4.0.xsd"
>
<!-- 입력값 검증을 위해 -->
<mvc:annotation-driven />
<bean id="validator" class="org.springframework.validation.beanvalidation.LocalValidatorFactoryBean" />
<!-- 오류메시지 파일 ojcmessage.properties -->
<bean class="org.springframework.context.support.ResourceBundleMessageSource"
id="messageSource">
<property name="basename" value="ojcmessage" />
</bean>
<bean id="dataSource" class="org.apache.commons.dbcp.BasicDataSource" destroy-method="close" >
<property name="driverClassName">
<value>oracle.jdbc.driver.OracleDriver</value>
</property>
<property name="url">
<value>jdbc:oracle:thin:@192.168.0.7:1521:onj</value>
</property>
<property name="username">
<value>scott</value>
</property>
<property name="password">
<value>tiger</value>
</property>
</bean>
<!-- MyBatis -->
<bean id="sqlSessionFactoryBean" class="org.mybatis.spring.SqlSessionFactoryBean">
<property name="dataSource" ref="dataSource"/>
<property name="configLocation" value="classpath:mybatis/config/sqlmap-config.xml"/>
<property name="mapperLocations" value="classpath:mybatis/mapper/*.xml"/>
</bean>
<bean id="sqlSession" class="org.mybatis.spring.SqlSessionTemplate">
<constructor-arg index="0" ref="sqlSessionFactoryBean"/>
</bean>
<!-- 뷰 리졸버 -->
<bean id="viewResolver"
class="org.springframework.web.servlet.view.InternalResourceViewResolver">
<property name="prefix">
<value>/jsp/</value>
</property>
<property name="suffix">
<value>.jsp</value>
</property>
</bean>
<!-- 멀티 파일업로드를 위해 -->
<bean id="multipartResolver"
class="org.springframework.web.multipart.commons.CommonsMultipartResolver" />
<context:component-scan base-package="onj.board.controller"/>
</beans>
4. /WEB-INF/boardConfig.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:aop="http://www.springframework.org/schema/aop"
xmlns:context="http://www.springframework.org/schema/context"
xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans.xsd
http://www.springframework.org/schema/aop http://www.springframework.org/schema/aop/spring-aop-4.0.xsd
http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context-4.0.xsd">
<aop:aspectj-autoproxy />
<context:component-scan base-package="onj.board.dao" />
<context:component-scan base-package="onj.board.service" />
<!-- AOP를 적용하여 DML처리시 로깅을 위한 것 -->
<bean id="myLogger" class="onj.board.aop.LoggingAspect" />
</beans>
5. /WEB-INF/web.xml
<?xml version="1.0" encoding="UTF-8"?>
<web-app id="WebApp_ID" version="2.4" xmlns="http://java.sun.com/xml/ns/j2ee" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://java.sun.com/xml/ns/j2ee http://java.sun.com/xml/ns/j2ee/web-app_2_4.xsd">
<display-name>Spring4Board1.0, 오라클자바커뮤니티</display-name>
<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>
</filter>
<filter-mapping>
<filter-name>encodingFilter</filter-name>
<url-pattern>/*</url-pattern>
</filter-mapping>
<!-- ContextLoaderListener 설정 -->
<listener>
<listener-class>
org.springframework.web.context.ContextLoaderListener
</listener-class>
</listener>
<!-- ContextLoaderListener 설정 파일 -->
<context-param>
<param-name>contextConfigLocation</param-name>
<param-value>
/WEB-INF/boardConfig.xml,
/WEB-INF/action-servlet.xml
</param-value>
</context-param>
<!-- 디스패처 서블릿 정의 설정 -->
<servlet>
<servlet-name>action</servlet-name>
<servlet-class>org.springframework.web.servlet.DispatcherServlet</servlet-class>
<load-on-startup>1</load-on-startup>
</servlet>
<servlet-mapping>
<servlet-name>action</servlet-name>
<url-pattern>*.html</url-pattern>
</servlet-mapping>
</web-app>
6. (오류 메시지) /src/main/resources/ojcmessage.properties
NotEmpty.uploadForm.name=NAME is required!
NotEmpty.uploadForm.passwd=PASSWORD is required!
NotEmpty.uploadForm.title=TITLE is required!
NotEmpty.uploadForm.content=CONTENTS is required!
Size.uploadForm.name=Out Of Range!
Size.uploadForm.passwd=Out Of Range!
Size.uploadForm.title=Out Of Range!
Size.uploadForm.content=Out Of Range!
NotEmpty.replyForm.name=NAME is required!
NotEmpty.replyForm.passwd=PASSWORD is required!
NotEmpty.replyForm.title=TITLE is required!
NotEmpty.replyForm.content=CONTENTS is required!
Size.replyForm.name=Out Of Range!
Size.replyForm.passwd=Out Of Range!
Size.replyForm.title=Out Of Range!
Size.replyForm.content=Out Of Range!
7. src/main/resources/mybatis/config/sqlmap-config.xml
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE configuration PUBLIC "-//mybatis.org//DTD Config 3.0//EN" "http://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>
<typeAliases>
<typeAlias type="onj.board.model.BoardDTO" alias="boardDTO"/>
<typeAlias type="onj.board.model.CommentDTO" alias="commentDTO"/>
</typeAliases>
</configuration>
8. src/main/resources/mybatis/mapper/boardSql.xml
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="onj.board.dao.BoardDAO">
<resultMap type="boardDTO" id="boardDTO">
<result property="seq" column="seq"/>
<result property="name" column="name"/>
<result property="passwd" column="passwd"/>
<result property="title" column="title"/>
<result property="content" column="content"/>
<result property="fileName1" column="filename1"/>
<result property="fileName2" column="filename2"/>
<result property="fileName3" column="filename3"/>
<result property="regdate" column="regdate"/>
<result property="readCount" column="readcount"/>
<result property="reply" column="reply"/>
<result property="reply_step" column="reply_step"/>
<result property="reply_level" column="reply_level"/>
</resultMap>
<!-- 게시판 전체 리스트 보기 -->
<select id="boardList" resultMap="boardDTO">
select * from
(select seq,name,
passwd,
title,
content,
filename1,
filename2,
filename3,
regdate,
readcount,
reply,
reply_step,
reply_level,rownum r
from board
order by reply desc, reply_step asc)
</select>
<!-- 미리보기 -->
<select id="preView" parameterType="java.lang.String" resultType="java.lang.String">
select content
from board
where seq = #{seq}
</select>
<!-- 게시글 상세보기 -->
<select id="readContent" parameterType="java.lang.String" resultMap="boardDTO">
select *
from board
where seq = #{seq}
</select>
<!-- 읽은 글의 조회수를 1증가 -->
<update id="updateReadCount" parameterType="java.lang.String">
update board
set readcount = nvl(readcount,0)+1
where seq = #{seq}
</update>
<!-- 게시글 입력 -->
<insert id="insertBoard" parameterType="boardDTO">
insert into board values(board_seq.nextval,
#{name},
#{passwd},
#{title},
#{content},
#{fileName1, jdbcType=VARCHAR},
#{fileName2, jdbcType=VARCHAR},
#{fileName3, jdbcType=VARCHAR},
sysdate,
0,
board_seq.currval,
0,
0)
</insert>
<!-- 글 수정 -->
<update id="updateBoard" parameterType="boardDTO">
update board
set name = #{name},
title=#{title} ,
content = #{content}
where seq = #{seq}
</update>
<!-- 글 삭제 -->
<delete id="deleteBoard" parameterType="boardDTO" >
delete from board
where seq = #{seq}
and passwd=#{passwd}
</delete>
<!-- 답글달기 -->
<!-- 현재 답변의 단 게시물 보다 더 높은 스텝의 게시물이 있다면 스탭을 하나씩 상승시킴 -->
<update id="replyBoardStep" parameterType="java.lang.Integer" >
update board
set reply_step = reply_step+1
where reply = #{reply}
and reply_step > reply_step
</update>
<!-- reply_step과 reply_level을 1씩 증가 시킨 후 내용을 저장 -->
<insert id="replyBoard" parameterType="boardDTO">
insert into board values(board_seq.nextval,
#{name},
#{passwd},
#{title},
#{content},
#{fileName1, jdbcType=VARCHAR},
#{fileName2, jdbcType=VARCHAR},
#{fileName3, jdbcType=VARCHAR},
sysdate,
0,
#{reply},
#{reply_step}+1,
#{reply_step}+1)
</insert>
</mapper>
9. src/main/resources/mybatis/mapper/commentSql.xml
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="onj.board.dao.BoardDAO">
<resultMap type="commentDTO" id="commentDTO">
<result property="seq" column="seq"/>
<result property="name" column="name"/>
<result property="comment" column="comm"/>
</resultMap>
<!-- 커멘트 입력 -->
<insert id="insertComment" parameterType="commentDTO">
insert into comment_t(seq,name,comm) values(#{seq},#{name},#{comment})
</insert>
<!-- 커멘트 출력 -->
<select id="commentList" parameterType="java.lang.String" resultMap="commentDTO">
select * from comment_t where seq = #{seq}
</select>
</mapper>
(8/25)안드로이드개발자과정
(8/25)SQL기초에서 Schema Object까지
(8/29)Spring,MyBatis,Hibernate실무과정
(8/29)자바기초JDBC,Servlet/JSP까지
(8/29)PL/SQL,ORACLE HINT,TUNING
(8/25)오라클자바채용확정교육
평일야간[개인80%환급]
(8/21)Spring, MyBatis, Hibernate
(8/21)HTML5,CSS3,Ajax,jQuery마스터
(8/21)C#,Network,ADO.NET,ASP.NET
(8/26)SQL기초에서실무까지
(8/26)안드로이드개발자과정
(8/28)자바JSP,jQuery,Spring,MyBatis
주말주간[개인80%환급]
(8/23)자바웹&스프링,마이바티스
(8/23)Spring, MyBatis, Hibernate
(8/23)SQL기초에서실무까지
(8/23)자바,네트워크,웹&스프링
(8/30)안드로이드개발자과정
(8/30)C#,ASP.NET마스터(8/30)웹퍼블리싱 마스터
주말야간[개인80%환급]
(8/23)SQL기초에서실무까지
(8/23)자바,네트워크,웹&스프링
댓글 없음:
댓글 쓰기