안녕하세요
이번에는 게시판에 조회수 기능과 트랜잭션 설정을 추가하겠습니다.
게시물을 조회할때 오류가나면 조회수는 올라가지 않아야 합니다.
그런 처리를 하기위해 트랜잭션 어노테이션이 있는데요.
자세한 설명은 따로 검색을 하시기 바랍니다..
1. MP_BOARD 컬럼추가
조회수 컬럼을 테이블에 추가해 줍니다.
ALTER TABLE MP_BOARD ADD(HIT NUMBER DEFAULT 0);
COMMIT;
2. BoardVO작성
BoardVO에 조회수를 추가해줍니다.
package kr.co.vo;
import java.util.Date;
public class BoardVO {
private int bno;
private String title;
private String content;
private String writer;
private Date regdate;
private int hit;
public int getBno() {
return bno;
}
public void setBno(int bno) {
this.bno = bno;
}
public String getTitle() {
return title;
}
public void setTitle(String title) {
this.title = title;
}
public String getContent() {
return content;
}
public void setContent(String content) {
this.content = content;
}
public String getWriter() {
return writer;
}
public void setWriter(String writer) {
this.writer = writer;
}
public Date getRegdate() {
return regdate;
}
public void setRegdate(Date regdate) {
this.regdate = regdate;
}
public int getHit() {
return hit;
}
public void setHit(int hit) {
this.hit = hit;
}
}
3. pom.xml작성
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>kr.co</groupId>
<artifactId>study</artifactId>
<name>study</name>
<packaging>war</packaging>
<version>1.0.0-BUILD-SNAPSHOT</version>
<properties>
<java-version>1.6</java-version>
<org.springframework-version>3.1.1.RELEASE</org.springframework-version>
<org.aspectj-version>1.6.10</org.aspectj-version>
<org.slf4j-version>1.6.6</org.slf4j-version>
<project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
</properties>
<!--오라클 -->
<repositories>
<repository>
<id>oracle</id>
<url>http://maven.jahia.org/maven2</url>
</repository>
</repositories>
<dependencies>
<dependency>
<groupId>org.codehaus.jackson</groupId>
<artifactId>jackson-mapper-asl</artifactId>
<version>1.9.13</version>
</dependency>
<dependency>
<groupId>org.codehaus.jackson</groupId>
<artifactId>jackson-core-asl</artifactId>
<version>1.9.13</version>
</dependency>
<dependency>
<groupId>com.fasterxml.jackson.core</groupId>
<artifactId>jackson-databind</artifactId>
<version>2.10.0</version>
</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>
<!-- AspectJ -->
<dependency>
<groupId>org.aspectj</groupId>
<artifactId>aspectjrt</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>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>
<!-- spring-jdbc -->
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-jdbc</artifactId>
<version>${org.springframework-version}</version>
</dependency>
<!-- Test -->
<dependency>
<groupId>junit</groupId>
<artifactId>junit</artifactId>
<version>4.12</version>
<scope>test</scope>
</dependency>
<!-- spring-test -->
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-test</artifactId>
<version>${org.springframework-version}</version>
<scope>test</scope>
</dependency>
<!-- log4jdbc-log4j2-jdbc4 -->
<dependency>
<groupId>org.bgee.log4jdbc-log4j2</groupId>
<artifactId>log4jdbc-log4j2-jdbc4</artifactId>
<version>1.16</version>
</dependency>
<!-- org.mybatis/mybatis -->
<dependency>
<groupId>org.mybatis</groupId>
<artifactId>mybatis</artifactId>
<version>3.4.1</version>
</dependency>
<!-- mybatis-spring -->
<dependency>
<groupId>org.mybatis</groupId>
<artifactId>mybatis-spring</artifactId>
<version>1.3.0</version>
</dependency>
<!-- 암호화 START -->
<!-- https://mvnrepository.com/artifact/org.springframework.security/spring-security-core -->
<dependency>
<groupId>org.springframework.security</groupId>
<artifactId>spring-security-core</artifactId>
<version>5.0.8.RELEASE</version>
</dependency>
<!-- https://mvnrepository.com/artifact/org.springframework.security/spring-security-web -->
<dependency>
<groupId>org.springframework.security</groupId>
<artifactId>spring-security-web</artifactId>
<version>5.0.8.RELEASE</version>
</dependency>
<!-- https://mvnrepository.com/artifact/org.springframework.security/spring-security-config -->
<dependency>
<groupId>org.springframework.security</groupId>
<artifactId>spring-security-config</artifactId>
<version>5.0.8.RELEASE</version>
</dependency>
<!-- 암호화 END -->
<!-- 첨부파일 START-->
<!-- MultipartHttpServletRequset -->
<dependency>
<groupId>commons-io</groupId>
<artifactId>commons-io</artifactId>
<version>2.0.1</version>
</dependency>
<dependency>
<groupId>commons-fileupload</groupId>
<artifactId>commons-fileupload</artifactId>
<version>1.2.2</version>
</dependency>
<!-- 첨부파일 END -->
<!-- 트랜젝션 START -->
<!-- https://mvnrepository.com/artifact/org.springframework/spring-tx -->
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-tx</artifactId>
<version>3.1.1.RELEASE</version>
</dependency>
<!-- 트랜젝션 END -->
</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>
<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>
4. root-context.xml 작성
Namespaces탭에 들어가셔서 tx를 체크해줍니다.
Source탭으로 와서 bean과 tx:annotation-driven 을 추가해줍니다.
<?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:jdbc="http://www.springframework.org/schema/jdbc"
xmlns:mybatis-spring="http://mybatis.org/schema/mybatis-spring"
xmlns:tx="http://www.springframework.org/schema/tx"
xsi:schemaLocation="http://www.springframework.org/schema/jdbc http://www.springframework.org/schema/jdbc/spring-jdbc-3.1.xsd
http://mybatis.org/schema/mybatis-spring http://mybatis.org/schema/mybatis-spring-1.2.xsd
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-3.1.xsd
http://www.springframework.org/schema/tx http://www.springframework.org/schema/tx/spring-tx-3.1.xsd">
<!-- Root Context: defines shared resources visible to all other web components -->
<!--집에서 오라클 -->
<!-- <bean class="org.springframework.jdbc.datasource.DriverManagerDataSource" id="dataSource">
<property name="driverClassName" value="net.sf.log4jdbc.sql.jdbcapi.DriverSpy"/>
<property name="url" value="jdbc:log4jdbc:oracle:thin:@1:1/ㅇ"/>
<property name="username" value="ㅇ"/>
<property name="password" value="ㅇ"/>
</bean> -->
<!-- Mybatis 연동 -->
<bean id="sqlSessionFactory" class="org.mybatis.spring.SqlSessionFactoryBean">
<property name="dataSource" ref="dataSource"></property>
<property name="configLocation" value="classpath:/mybatis-config.xml"></property>
<property name="mapperLocations" value="classpath:mappers/**/*Mapper.xml"/>
</bean>
<bean id="sqlSession" class="org.mybatis.spring.SqlSessionTemplate" destroy-method="clearCache">
<constructor-arg name="sqlSessionFactory" ref="sqlSessionFactory"></constructor-arg>
</bean>
<bean id="transactionManager" class="org.springframework.jdbc.datasource.DataSourceTransactionManager">
<property name="dataSource" ref="dataSource"></property>
</bean>
<tx:annotation-driven transaction-manager="transactionManager" />
<!-- scan -->
<context:component-scan base-package="kr.co.service"></context:component-scan>
<context:component-scan base-package="kr.co.dao"></context:component-scan>
<context:component-scan base-package="kr.co.vo"></context:component-scan>
<context:component-scan base-package="kr.co.util"></context:component-scan>
</beans>
5. boardMapper.xml 작성
list에서 조회수를 조회할 수 있도록 HIT를 추가해줍니다.
그리고 게시판 조회수 쿼리를 작성해줍니다.
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper
PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="boardMapper">
<!-- 게시판 글 작성 -->
<insert id="insert" parameterType="kr.co.vo.BoardVO" useGeneratedKeys="true" keyProperty="bno">
<selectKey keyProperty="bno" resultType="int" order="BEFORE">
SELECT MP_BOARD_SEQ.NEXTVAL FROM DUAL
</selectKey>
INSERT INTO MP_BOARD( BNO
, TITLE
, CONTENT
, WRITER )
VALUES( #{bno}
, #{title}
, #{content}
, #{writer} )
</insert>
<select id="listPage" resultType="kr.co.vo.BoardVO" parameterType="kr.co.vo.SearchCriteria">
SELECT BNO,
TITLE,
CONTENT,
WRITER,
REGDATE,
HIT
FROM (
SELECT BNO,
TITLE,
CONTENT,
WRITER,
REGDATE,
HIT,
ROW_NUMBER() OVER(ORDER BY BNO DESC) AS RNUM
FROM MP_BOARD
WHERE 1=1
<include refid="search"></include>
) MP
WHERE RNUM BETWEEN #{rowStart} AND #{rowEnd}
ORDER BY BNO DESC
</select>
<select id="listCount" parameterType="kr.co.vo.SearchCriteria" resultType="int">
SELECT COUNT(BNO)
FROM MP_BOARD
WHERE 1=1
<include refid="search"></include>
AND BNO > 0
</select>
<sql id="search">
<if test="searchType != null">
<if test="searchType == 't'.toString()">AND TITLE LIKE '%' || #{keyword} || '%'</if>
<if test="searchType == 'c'.toString()">AND CONTENT LIKE '%' || #{keyword} || '%'</if>
<if test="searchType == 'w'.toString()">AND WRITER LIKE '%' || #{keyword} || '%'</if>
<if test="searchType == 'tc'.toString()">AND (TITLE LIKE '%' || #{keyword} || '%') or (CONTENT LIKE '%' || #{keyword} || '%')</if>
</if>
</sql>
<select id="read" parameterType="int" resultType="kr.co.vo.BoardVO">
SELECT BNO
, TITLE
, CONTENT
, WRITER
, REGDATE
FROM MP_BOARD
WHERE BNO = #{bno}
</select>
<update id="update" parameterType="kr.co.vo.BoardVO">
UPDATE MP_BOARD
SET TITLE = #{title},
CONTENT = #{content}
WHERE BNO = #{bno}
</update>
<delete id="delete" parameterType="int">
DELETE
FROM MP_BOARD
WHERE BNO = #{bno}
</delete>
<!-- 첨부파일 업로드 -->
<insert id="insertFile" parameterType="hashMap">
INSERT INTO MP_FILE(
FILE_NO,
BNO,
ORG_FILE_NAME,
STORED_FILE_NAME,
FILE_SIZE
)VALUES(
SEQ_MP_FILE_NO.NEXTVAL,
#{BNO},
#{ORG_FILE_NAME},
#{STORED_FILE_NAME},
#{FILE_SIZE}
)
</insert>
<!-- 첨부파일 조회 -->
<select id="selectFileList" parameterType="int" resultType="hashMap">
SELECT FILE_NO,
ORG_FILE_NAME,
ROUND(FILE_SIZE/1024,1) AS FILE_SIZE,
DEL_GB
FROM MP_FILE
WHERE BNO = #{BNO}
AND DEL_GB = 'N'
ORDER BY FILE_NO ASC
</select>
<!-- 첨부파일 다운 -->
<select id="selectFileInfo" parameterType="hashMap" resultType="hashMap">
SELECT
STORED_FILE_NAME,
ORG_FILE_NAME
FROM MP_FILE
WHERE FILE_NO = #{FILE_NO}
</select>
<!-- 첨부파일 수정 -->
<update id="updateFile" parameterType="hashMap">
UPDATE MP_FILE SET
DEL_GB = 'Y'
WHERE FILE_NO = #{FILE_NO}
</update>
<!-- 게시판 조회수 -->
<update id="boardHit" parameterType="int">
UPDATE MP_BOARD SET
HIT = HIT+1
WHERE BNO = #{bno}
</update>
</mapper>
6. BoardDAO 작성
DAO에 코드를 추가해줍니다.
BoardDAO.java
// 게시판 조회수
public void boardHit(int bno) throws Exception;
BoardDAOImpl.java
@Override
public void boardHit(int bno) throws Exception {
// TODO Auto-generated method stub
sqlSession.update("boardMapper.boardHit", bno);
}
7. BoardService 작성
BoardServiceImpl.java에서 기존에 있던 read메소드에 코드를 수정해 줍니다.
BoardServiceImpl.java
// 게시물 조회
@Transactional(isolation = Isolation.READ_COMMITTED)
@Override
public BoardVO read(int bno) throws Exception {
dao.boardHit(bno);
return dao.read(bno);
}
8. list.jsp 작성
리스트 목록에 조회수가 보여지도록 수정해줍니다.
<%@ page language="java" contentType="text/html; charset=UTF-8" pageEncoding="UTF-8"%>
<%@ taglib uri="http://java.sun.com/jsp/jstl/core" prefix="c" %>
<%@ taglib prefix="fmt" uri="http://java.sun.com/jsp/jstl/fmt"%>
<html>
<head>
<!-- 합쳐지고 최소화된 최신 CSS -->
<link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.2/css/bootstrap.min.css">
<!-- 부가적인 테마 -->
<link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.2/css/bootstrap-theme.min.css">
<script src="//cdnjs.cloudflare.com/ajax/libs/jquery/3.2.1/jquery.min.js"></script>
<title>게시판</title>
</head>
<body>
<div class="container">
<header>
<h1> 게시판</h1>
</header>
<hr />
<div>
<%@include file="nav.jsp" %>
</div>
<section id="container">
<form role="form" method="get">
<table class="table table-hover">
<thead>
<tr><th>번호</th><th>제목</th><th>작성자</th><th>등록일</th><th>조회수</th></tr>
</thead>
<c:forEach items="${list}" var = "list">
<tr>
<td><c:out value="${list.bno}" /></td>
<td>
<a href="/board/readView?bno=${list.bno}&page=${scri.page}&perPageNum=${scri.perPageNum}&searchType=${scri.searchType}&keyword=${scri.keyword}"><c:out value="${list.title}" /></a>
</td>
<td><c:out value="${list.writer}" /></td>
<td><fmt:formatDate value="${list.regdate}" pattern="yyyy-MM-dd"/></td>
<td><c:out value="${list.hit}" /></td>
</tr>
</c:forEach>
</table>
<div class="search row">
<div class="col-xs-2 col-sm-2">
<select name="searchType" class="form-control">
<option value="n"<c:out value="${scri.searchType == null ? 'selected' : ''}"/>>-----</option>
<option value="t"<c:out value="${scri.searchType eq 't' ? 'selected' : ''}"/>>제목</option>
<option value="c"<c:out value="${scri.searchType eq 'c' ? 'selected' : ''}"/>>내용</option>
<option value="w"<c:out value="${scri.searchType eq 'w' ? 'selected' : ''}"/>>작성자</option>
<option value="tc"<c:out value="${scri.searchType eq 'tc' ? 'selected' : ''}"/>>제목+내용</option>
</select>
</div>
<div class="col-xs-10 col-sm-10">
<div class="input-group">
<input type="text" name="keyword" id="keywordInput" value="${scri.keyword}" class="form-control"/>
<span class="input-group-btn">
<button id="searchBtn" type="button" class="btn btn-default">검색</button>
</span>
</div>
</div>
<script>
$(function(){
$('#searchBtn').click(function() {
self.location = "list" + '${pageMaker.makeQuery(1)}' + "&searchType=" + $("select option:selected").val() + "&keyword=" + encodeURIComponent($('#keywordInput').val());
});
});
</script>
</div>
<div class="col-md-offset-3">
<ul class="pagination">
<c:if test="${pageMaker.prev}">
<li><a href="list${pageMaker.makeSearch(pageMaker.startPage - 1)}">이전</a></li>
</c:if>
<c:forEach begin="${pageMaker.startPage}" end="${pageMaker.endPage}" var="idx">
<li <c:out value="${pageMaker.cri.page == idx ? 'class=info' : ''}" />>
<a href="list${pageMaker.makeSearch(idx)}">${idx}</a></li>
</c:forEach>
<c:if test="${pageMaker.next && pageMaker.endPage > 0}">
<li><a href="list${pageMaker.makeSearch(pageMaker.endPage + 1)}">다음</a></li>
</c:if>
</ul>
</div>
</form>
</section>
</div>
</body>
</html>
9. 실행테스트
조회수가 정상적으로 올라가는것을 확인하실 수 있습니다.
첨부파일