MyBatis의 동적 쿼리(1) - sql과 include
공통 부분을 <sql>로 정의하고 <include>로 포함시켜 재사용
<sql id="searchCondition">
<choose>
<when test='option=="T"'>
AND title LIKE concat("%", #{keyword}, "%")
</when>
<when test='option=="W"'>
AND writer LIKE concat("%", #{keyword}, "%")
</when>
<otherwise>
AND (title LIKE concat("%", #{keyword}, "%")
OR content LIKE concat("%", #{keyword}, "%"))
</otherwise>
</choose>
</sql>
<select id="searchSelectPage" parameterType="SearchCondition" resultType="BoardDto">
<include refid="selectFromBoard"/>
WHERE TRUE
<include refid="searchCondition "/>
ORDER BY reg_date DESC, bno DESC
LIMIT #{offset}, #{pageSize}
</select>
<select id="searchResultCnt" parameterType="SearchCondition" resultType="int">
SELECT count(*)
FROM board
WHERE TRUE
<include refid="searchCondition "/>
</select>MyBatis의 동적 쿼리(2) - if
MyBatis의 동적 쿼리(3) - choose, when
<select id="searchSelectPage" parameterType="SearchCondition" resultType="BoardDto">
<include refid="selectFromBoard"/>
WHERE TRUE
<choose>
<when test='option=="T"'>
AND title LIKE concat("%", #{keyword}, "%")
</when>
<when test='option=="W"'>
AND writer LIKE concat("%", #{keyword}, "%")
</when>
<otherwise>
AND (title LIKE concat("%", #{keyword}, "%")
OR content LIKE concat("%", #{keyword}, "%"))
</otherwise>
</choose>
ORDER BY reg_date DESC, bno DESC
LIMIT #{offset}, #{pageSize}
</select>
<select id="searchResultCnt" parameterType="SearchCondition" resultType="int">
SELECT count(*)
FROM board
WHERE TRUE
<choose>
<when test='option=="T"'>
AND title LIKE concat("%", #{keyword}, "%")
</when>
<when test='option=="W"'>
AND writer LIKE concat("%", #{keyword}, "%")
</when>
<otherwise>
AND (title LIKE concat("%", #{keyword}, "%")
OR content LIKE concat("%", #{keyword}, "%"))
</otherwise>
</choose>
</select>와일드 카드
| mysql | orcle |
| % (여러 글자, 0~n) | % (여러 글자, 0~n) |
| _ (한 글자) | ? (한 글자) |
SELECT * FROM board WHERE TRUE AND TITLE LIKE concat("title1", "%");
SELECT * FROM board WHERE TRUE AND TITLE LIKE concat("title1", "_");
SELECT * FROM board WHERE TRUE AND TITLE NOT LIKE concat("title1", "_");
SELECT * FROM board WHERE TRUE AND TITLE IN ("title1", "title2", "title3");
MyBatis의 동적 쿼리(4) - foreach
boardMapper.xml
searchSelectPage, searchResultCnt 추가
<select id="searchSelectPage" parameterType="SearchCondition" resultType="BoardDto">
<include refid="selectFromBoard"/>
WHERE TRUE
AND title LIKE concat("%", #{keyword}, "%")
ORDER BY reg_date DESC, bno DESC
LIMIT #{offset}, #{pageSize}
</select>
<select id="searchResultCnt" parameterType="SearchCondition" resultType="int">
SELECT count(*)
FROM board
WHERE TRUE
AND title LIKE concat("%", #{keyword}, "%")
</select><?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="com.jcy.usedhunter.dao.BoardMapper">
<insert id="insert" parameterType="BoardDto">
INSERT INTO board
(title, content, writer)
VALUES
(#{title}, #{content}, #{writer})
</insert>
<sql id="selectFromBoard">
SELECT bno, title, content, writer, view_cnt, comment_cnt, reg_date
FROM board
</sql>
<select id="select" parameterType="int" resultType="BoardDto">
<include refid="selectFromBoard"/>
WHERE bno = #{bno}
</select>
<select id="selectAll" resultType="BoardDto">
SELECT bno, title, content, writer, view_cnt, comment_cnt, reg_date
FROM board
ORDER BY reg_date DESC, bno DESC
</select>
<select id="count" resultType="int">
SELECT count(*) FROM board
</select>
<select id="selectPage" parameterType="map" resultType="BoardDto">
<include refid="selectFromBoard"/>
ORDER BY reg_date DESC, bno DESC
LIMIT #{offset}, #{pageSize}
</select>
<select id="searchSelectPage" parameterType="SearchCondition" resultType="BoardDto">
<include refid="selectFromBoard"/>
WHERE TRUE
AND title LIKE concat("%", #{keyword}, "%")
ORDER BY reg_date DESC, bno DESC
LIMIT #{offset}, #{pageSize}
</select>
<select id="searchResultCnt" parameterType="SearchCondition" resultType="int">
SELECT count(*)
FROM board
WHERE TRUE
AND title LIKE concat("%", #{keyword}, "%")
</select>
<update id="update" parameterType="BoardDto">
UPDATE board
SET title = #{title}
, content = #{content}
, up_date = now()
WHERE bno = #{bno} and writer = #{writer}
</update>
<update id="updateCommentCnt" parameterType="map">
UPDATE board
SET comment_cnt = comment_cnt + #{cnt}
WHERE bno = #{bno}
</update>
<update id="increaseViewCnt" parameterType="int">
UPDATE board
SET view_cnt = view_cnt + 1
WHERE bno = #{bno}
</update>
<delete id="delete" parameterType="map">
DELETE FROM board WHERE bno = #{bno} and writer = #{writer}
</delete>
<delete id="deleteAll">
DELETE FROM board
</delete>
</mapper>
mybatis-config.xml
alias = "SearchCondition" 추가
<typeAlias alias="SearchCondition" type="com.jcy.usedhunter.domain.SearchCondition"/><?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 alias="BoardDto" type="com.jcy.usedhunter.domain.BoardDto"/>
<typeAlias alias="SearchCondition" type="com.jcy.usedhunter.domain.SearchCondition"/>
</typeAliases>
</configuration>
BoardDao.java
searchResultCnt(), searchSelectPage(SearchCondition sc) 추가
List<BoardDto> searchSelectPage(SearchCondition sc) throws Exception;
int searchResultCnt(SearchCondition sc) throws Exception;package com.jcy.usedhunter.dao;
import java.util.List;
import java.util.Map;
import com.jcy.usedhunter.domain.BoardDto;
import com.jcy.usedhunter.domain.SearchCondition;
public interface BoardDao {
BoardDto select(Integer bno) throws Exception;
int delete(Integer bno, String writer) throws Exception;
int insert(BoardDto dto) throws Exception;
int update(BoardDto dto) throws Exception;
int increaseViewCnt(Integer bno) throws Exception;
List<BoardDto> selectPage(Map map) throws Exception;
List<BoardDto> selectAll() throws Exception;
int deleteAll() throws Exception;
int count() throws Exception;
List<BoardDto> searchSelectPage(SearchCondition sc) throws Exception;
int searchResultCnt(SearchCondition sc) throws Exception;
}
BoardDaoImpl.java
searchResultCnt(), searchSelectPage(SearchCondition sc) 추가
@Override
public List<BoardDto> searchSelectPage(SearchCondition sc) throws Exception {
return session.selectList(namespace+"searchSelectPage", sc);
}
@Override
public int searchResultCnt() throws Exception {
return session.selectOne(namespace+"searchResultCnt", sc);
}package com.jcy.usedhunter.dao;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import org.apache.ibatis.session.SqlSession;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Repository;
import com.jcy.usedhunter.domain.BoardDto;
import com.jcy.usedhunter.domain.SearchCondition;
@Repository
public class BoardDaoImpl implements BoardDao {
@Autowired
SqlSession session;
private static String namespace = "com.jcy.usedhunter.dao.BoardMapper."; // 마지막에 '.' 잊지 말기
@Override
public int insert(BoardDto dto) throws Exception {
return session.insert(namespace + "insert", dto);
}
@Override
public BoardDto select(Integer bno) throws Exception {
return session.selectOne(namespace+"select", bno);
}
@Override
public List<BoardDto> selectAll() throws Exception {
return session.selectList(namespace+"selectAll");
}
@Override
public List<BoardDto> selectPage(Map map) throws Exception {
return session.selectList(namespace+"selectPage", map);
}
@Override
public int count() throws Exception {
return session.selectOne(namespace+"count");
}
@Override
public int update(BoardDto dto) throws Exception {
return session.update(namespace+"update", dto);
}
@Override
public int increaseViewCnt(Integer bno) throws Exception {
return session.update(namespace+"increaseViewCnt", bno);
}
@Override
public int delete(Integer bno, String writer) throws Exception {
Map map = new HashMap();
map.put("bno", bno);
map.put("writer", writer);
return session.delete(namespace+"delete", map);
}
@Override
public int deleteAll() throws Exception {
return session.delete(namespace+"deleteAll");
}
@Override
public List<BoardDto> searchSelectPage(SearchCondition sc) throws Exception {
return session.selectList(namespace+"searchSelectPage", sc);
}
@Override
public int searchResultCnt(SearchCondition sc) throws Exception {
return session.selectOne(namespace+"searchResultCnt", sc);
}
}
SearchCondition.java
package com.jcy.usedhunter.domain;
public class SearchCondition {
private Integer page = 1;
private Integer pageSize = 10;
private Integer offset = 0;
private String keyword = "";
private String option = "";
public SearchCondition() {
}
public SearchCondition(Integer page, Integer pageSize, String keyword, String option) {
this.page = page;
this.pageSize = pageSize;
this.keyword = keyword;
this.option = option;
}
public Integer getPage() {
return page;
}
public void setPage(Integer page) {
this.page = page;
}
public Integer getPageSize() {
return pageSize;
}
public void setPageSize(Integer pageSize) {
this.pageSize = pageSize;
}
public Integer getOffset() {
return offset;
}
public void setOffset(Integer offset) {
this.offset = offset;
}
public String getKeyword() {
return keyword;
}
public void setKeyword(String keyword) {
this.keyword = keyword;
}
public String getOption() {
return option;
}
public void setOption(String option) {
this.option = option;
}
@Override
public String toString() {
return "SearchCondition [page=" + page + ", pageSize=" + pageSize + ", offset=" + offset + ", keyword="
+ keyword + ", option=" + option + "]";
}
}
실행시 SQL log 보기
pom.xml
<!-- https://mvnrepository.com/artifact/org.bgee.log4jdbc-log4j2/log4jdbc-log4j2-jdbc4.1 -->
<dependency>
<groupId>org.bgee.log4jdbc-log4j2</groupId>
<artifactId>log4jdbc-log4j2-jdbc4.1</artifactId>
<version>1.16</version>
</dependency><?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.fastcampus</groupId>
<artifactId>app</artifactId>
<name>firstSpring</name>
<packaging>war</packaging>
<version>1.0.0-BUILD-SNAPSHOT</version>
<properties>
<java-version>11</java-version>
<org.springframework-version>5.0.7.RELEASE</org.springframework-version>
<org.aspectj-version>1.6.10</org.aspectj-version>
<org.slf4j-version>1.6.6</org.slf4j-version>
</properties>
<dependencies>
<!-- https://mvnrepository.com/artifact/org.bgee.log4jdbc-log4j2/log4jdbc-log4j2-jdbc4.1 -->
<dependency>
<groupId>org.bgee.log4jdbc-log4j2</groupId>
<artifactId>log4jdbc-log4j2-jdbc4.1</artifactId>
<version>1.16</version>
</dependency>
<!-- https://mvnrepository.com/artifact/org.mybatis/mybatis -->
<dependency>
<groupId>org.mybatis</groupId>
<artifactId>mybatis</artifactId>
<version>3.5.9</version>
</dependency>
<!-- https://mvnrepository.com/artifact/org.mybatis/mybatis-spring -->
<dependency>
<groupId>org.mybatis</groupId>
<artifactId>mybatis-spring</artifactId>
<version>2.0.7</version>
</dependency>
<!-- https://mvnrepository.com/artifact/org.springframework/spring-jdbc -->
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-jdbc</artifactId>
<version>${org.springframework-version}</version>
</dependency>
<!-- https://mvnrepository.com/artifact/mysql/mysql-connector-java -->
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>8.0.30</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>
<!-- https://mvnrepository.com/artifact/org.springframework/spring-aop -->
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-aop</artifactId>
<version>${org.springframework-version}</version>
</dependency>
<!-- AspectJ -->
<dependency>
<groupId>org.aspectj</groupId>
<artifactId>aspectjrt</artifactId>
<version>${org.aspectj-version}</version>
</dependency>
<!-- https://mvnrepository.com/artifact/org.aspectj/aspectjweaver -->
<dependency>
<groupId>org.aspectj</groupId>
<artifactId>aspectjweaver</artifactId>
<version>1.9.7</version>
<scope>runtime</scope>
</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>
<!-- https://mvnrepository.com/artifact/javax.validation/validation-api -->
<dependency>
<groupId>javax.validation</groupId>
<artifactId>validation-api</artifactId>
<version>2.0.1.Final</version>
</dependency>
<!-- Test -->
<!-- https://mvnrepository.com/artifact/org.springframework/spring-test -->
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-test</artifactId>
<version>${org.springframework-version}</version>
<scope>test</scope>
</dependency>
<dependency>
<groupId>junit</groupId>
<artifactId>junit</artifactId>
<version>4.12</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>
<groupId>org.apache.maven.plugins</groupId>
<artifactId>maven-compiler-plugin</artifactId>
<version>2.5.1</version>
<configuration>
<source>${java-version}</source>
<target>${java-version}</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>
resource/log4jdbc.log4j2.properties.xml
log4jdbc.spylogdelegator.name=net.sf.log4jdbc.log.slf4j.Slf4jSpyLogDelegatorresource/logback.xml
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE configuration>
<configuration>
<include resource="org/springframework/boot/logging/logback/base.xml"/>
<!-- log4jdbc-log4j2 -->
<logger name="jdbc.sqlonly" level="INFO"/>
<logger name="jdbc.sqltiming" level="INFO"/>
<logger name="jdbc.audit" level="WARN"/>
<logger name="jdbc.resultset" level="INFO"/>
<logger name="jdbc.resultsettable" level="INFO"/>
<logger name="jdbc.connection" level="INFO"/>
</configuration>
root-context.xml
<property name="driverClassName" value="net.sf.log4jdbc.sql.jdbcapi.DriverSpy"></property>
<property name="url" value="jdbc:log4jdbc:mysql://localhost:3301/usedhunter?useUnicode=true&characterEncoding=utf8"></property><?xml version="1.0" encoding="UTF-8"?>
<beans xmlns:mvc="http://www.springframework.org/schema/mvc"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns="http://www.springframework.org/schema/beans"
xmlns:context="http://www.springframework.org/schema/context"
xmlns:tx="http://www.springframework.org/schema/tx"
xsi:schemaLocation="http://www.springframework.org/schema/mvc https://www.springframework.org/schema/mvc/spring-mvc.xsd
http://www.springframework.org/schema/beans https://www.springframework.org/schema/beans/spring-beans.xsd
http://www.springframework.org/schema/context https://www.springframework.org/schema/context/spring-context.xsd http://www.springframework.org/schema/tx http://www.springframework.org/schema/tx/spring-tx.xsd">
<bean id="dataSource"
class="org.springframework.jdbc.datasource.DriverManagerDataSource">
<property name="driverClassName" value="net.sf.log4jdbc.sql.jdbcapi.DriverSpy"></property>
<property name="url" value="jdbc:log4jdbc:mysql://localhost:3301/usedhunter?useUnicode=true&characterEncoding=utf8"></property>
<!-- <property name="driverClassName" value="com.mysql.jdbc.Driver"></property>
<property name="url" value="jdbc:mysql://localhost:3301/usedhunter?useUnicode=true&characterEncoding=utf8"></property> -->
<property name="username" value="jcy"></property>
<property name="password" value="darknight24!"></property>
</bean>
<bean id="sqlSessionFactory" class="org.mybatis.spring.SqlSessionFactoryBean">
<property name="dataSource" ref="dataSource"/>
<property name="configLocation" value="classpath:mybatis-config.xml"/>
<property name="mapperLocations" value="classpath*:mapper/*Mapper.xml"/>
</bean>
<bean id="sqlSession" class="org.mybatis.spring.SqlSessionTemplate">
<constructor-arg ref="sqlSessionFactory"/>
</bean>
<bean id="transactionManager" class="org.springframework.jdbc.datasource.DataSourceTransactionManager">
<property name="dataSource" ref="dataSource"/>
</bean>
<tx:annotation-driven/>
<context:component-scan base-package="com.jcy.usedhunter" />
</beans>
searchSelectPage 테스트
@Test
public void searchSelectPageTest() throws Exception {
boardDao.deleteAll();
for(int i = 1; i<=20; i++) {
BoardDto boardDto = new BoardDto("title"+i, "content"+i, "asdf2");
boardDao.insert(boardDto);
}
SearchCondition sc = new SearchCondition(1, 10, "title2", "T");
List<BoardDto> list = boardDao.searchSelectPage(sc);
// System.out.println("list = " + list);
assertTrue(list.size()==2);
}
searchReusltCnt 테스트
@Test
public void searchResultCntTest() throws Exception {
boardDao.deleteAll();
for(int i = 1; i<=20; i++) {
BoardDto boardDto = new BoardDto("title"+i, "content"+i, "asdf2");
boardDao.insert(boardDto);
}
SearchCondition sc = new SearchCondition(1, 10, "title2", "T");
int resultCnt = boardDao.searchResultCnt(sc);
// System.out.println("list = " + list);
assertTrue(resultCnt==2);
}searchReusltCnt 에서 에러
count 는 LIMIT, ORDER BY 사용 안 되서 삭제
java.sql.SQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'null, null' at line 6
<select id="searchResultCnt" parameterType="SearchCondition" resultType="int">
SELECT count(*)
FROM board
WHERE TRUE
AND title LIKE concat("%", #{keyword}, "%")
ORDER BY reg_date DESC, bno DESC
LIMIT #{offset}, #{pageSize}
</select><select id="searchResultCnt" parameterType="SearchCondition" resultType="int">
SELECT count(*)
FROM board
WHERE TRUE
AND title LIKE concat("%", #{keyword}, "%")
</select>
BoardService.java
List<BoardDto> getSearchResultPage(SearchCondition sc) throws Exception;
int getSearchResultCnt(SearchCondition sc) throws Exception;package com.jcy.usedhunter.service;
import java.util.List;
import java.util.Map;
import com.jcy.usedhunter.domain.BoardDto;
import com.jcy.usedhunter.domain.SearchCondition;
public interface BoardService {
int getCount() throws Exception;
int remove(Integer bno, String writer) throws Exception;
int write(BoardDto boardDto) throws Exception;
List<BoardDto> getList() throws Exception;
BoardDto read(Integer bno) throws Exception;
List<BoardDto> getPage(Map map) throws Exception;
int modify(BoardDto boardDto) throws Exception;
List<BoardDto> getSearchResultPage(SearchCondition sc) throws Exception;
int getSearchResultCnt(SearchCondition sc) throws Exception;
}
BoardServiceImpl.java
@Override
public List<BoardDto> getSearchResultPage(SearchCondition sc) throws Exception {
return boardDao.searchSelectPage(sc);
}
@Override
public int getSearchResultCnt(SearchCondition sc) throws Exception {
return boardDao.searchResultCnt(sc);
}package com.jcy.usedhunter.service;
import java.util.List;
import java.util.Map;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import com.jcy.usedhunter.dao.BoardDao;
import com.jcy.usedhunter.domain.BoardDto;
import com.jcy.usedhunter.domain.SearchCondition;
@Service
public class BoardServiceImpl implements BoardService {
@Autowired
BoardDao boardDao;
@Override
public int getCount() throws Exception {
return boardDao.count();
}
@Override
public int remove(Integer bno, String writer) throws Exception {
return boardDao.delete(bno, writer);
}
@Override
public int write(BoardDto boardDto) throws Exception {
// throw new Exception("test");
return boardDao.insert(boardDto);
}
@Override
public List<BoardDto> getList() throws Exception {
return boardDao.selectAll();
}
@Override
public BoardDto read(Integer bno) throws Exception {
BoardDto boardDto = boardDao.select(bno);
boardDao.increaseViewCnt(bno);
return boardDto;
}
@Override
public List<BoardDto> getPage(Map map) throws Exception {
return boardDao.selectPage(map);
}
@Override
public int modify(BoardDto boardDto) throws Exception {
return boardDao.update(boardDto);
}
@Override
public List<BoardDto> getSearchResultPage(SearchCondition sc) throws Exception {
return boardDao.searchSelectPage(sc);
}
@Override
public int getSearchResultCnt(SearchCondition sc) throws Exception {
return boardDao.searchResultCnt(sc);
}
}
BoardController.java
package com.jcy.usedhunter.controller;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpSession;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.ui.Model;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.servlet.mvc.support.RedirectAttributes;
import com.jcy.usedhunter.domain.BoardDto;
import com.jcy.usedhunter.domain.PageHandler;
import com.jcy.usedhunter.service.BoardService;
@Controller
@RequestMapping("/board")
public class BoardController {
@Autowired
BoardService boardService;
@GetMapping("/list")
public String list(Integer page, Integer pageSize, Model m, HttpServletRequest request) {
if(!loginCheck(request)) {
return "redirect:/login/login?toURL="+request.getRequestURL(); // 로그인을 안했으면 로그인 화면으로 이동
}
if (page==null) {
page=1;
}
if (pageSize==null) {
pageSize=10;
}
try {
int totalCnt = boardService.getCount();
PageHandler ph = new PageHandler(totalCnt, page, pageSize);
Map map = new HashMap();
map.put("offset", (page-1)*pageSize);
map.put("pageSize", pageSize);
List<BoardDto> list = boardService.getPage(map);
m.addAttribute("list", list);
m.addAttribute("ph", ph);
m.addAttribute("page", page);
m.addAttribute("pageSize", pageSize);
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return "boardList"; // 로그인을 했으면 게시판 화면으로 이동
}
@GetMapping("read")
public String read(Integer bno, Integer page, Integer pageSize, Model m) {
try {
BoardDto boardDto = boardService.read(bno);
// m.addAttribute("boardDto", boardDto); 아래와 같은 코드
m.addAttribute(boardDto);
m.addAttribute("page", page);
m.addAttribute("pageSize", pageSize);
} catch (Exception e) {
e.printStackTrace();
}
return "board";
}
@PostMapping("remove")
public String remove(HttpSession session, Integer bno, Integer page, Integer pageSize, Model m, RedirectAttributes rttr) {
String writer = (String)session.getAttribute("id");
try {
m.addAttribute("page", page);
m.addAttribute("pageSize", pageSize);
int rowCnt = boardService.remove(bno, writer);
if(rowCnt != 1)
throw new Exception("board remove error");
rttr.addFlashAttribute("msg", "DEL_OK");
} catch (Exception e) {
e.printStackTrace();
rttr.addFlashAttribute("msg", "DEL_ERROR");
}
return "redirect:/board/list"; // 모델에 담으면 "redirect:/board/list?page=&pageSize" 으로 자동으로 생성
}
@GetMapping("write")
public String write(Model m) {
m.addAttribute("mode", "new");
return "board";
}
@PostMapping("write")
public String write(BoardDto boardDto, Model m, HttpSession session, RedirectAttributes rttr) {
String writer = (String)session.getAttribute("id");
boardDto.setWriter(writer);
try {
int rowCnt = boardService.write(boardDto);
if(rowCnt!=1) {
throw new Exception("Write failed");
}
rttr.addFlashAttribute("msg", "WRT_OK");
return "redirect:/board/list";
} catch (Exception e) {
e.printStackTrace();
m.addAttribute(boardDto);
m.addAttribute("msg", "WRT_ERROR");
return "board";
}
}
@PostMapping("modify")
public String modify(BoardDto boardDto, Model m, HttpSession session, RedirectAttributes rttr) {
String writer = (String)session.getAttribute("id");
boardDto.setWriter(writer);
try {
int rowCnt = boardService.modify(boardDto);
if(rowCnt!=1) {
throw new Exception("Modify failed");
}
rttr.addFlashAttribute("msg", "MOD_OK");
return "redirect:/board/list";
} catch (Exception e) {
e.printStackTrace();
m.addAttribute(boardDto);
m.addAttribute("msg", "MOD_ERROR");
return "board";
}
}
private boolean loginCheck(HttpServletRequest request) {
// 1. 세션을 얻고
HttpSession session = request.getSession();
// 2. 세션에 id 가 있는 지 확인 있으면 true 를 반환
// if(session.getAttribute("id")!=null) {
// return true;
// } else {
// return false;
// }
return session.getAttribute("id")!=null;
}
}package com.jcy.usedhunter.controller;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpSession;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.ui.Model;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.servlet.mvc.support.RedirectAttributes;
import com.jcy.usedhunter.domain.BoardDto;
import com.jcy.usedhunter.domain.PageHandler;
import com.jcy.usedhunter.domain.SearchCondition;
import com.jcy.usedhunter.service.BoardService;
@Controller
@RequestMapping("/board")
public class BoardController {
@Autowired
BoardService boardService;
@GetMapping("/list")
public String list(SearchCondition sc, Model m, HttpServletRequest request) {
if(!loginCheck(request)) {
return "redirect:/login/login?toURL="+request.getRequestURL(); // 로그인을 안했으면 로그인 화면으로 이동
}
try {
int totalCnt = boardService.getSearchResultCnt(sc);
m.addAttribute("totalCnt", totalCnt);
PageHandler ph = new PageHandler(totalCnt, sc);
List<BoardDto> list = boardService.getSearchResultPage(sc);
m.addAttribute("list", list);
m.addAttribute("ph", ph);
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return "boardList"; // 로그인을 했으면 게시판 화면으로 이동
}
@GetMapping("read")
public String read(Integer bno, Integer page, Integer pageSize, Model m) {
try {
BoardDto boardDto = boardService.read(bno);
// m.addAttribute("boardDto", boardDto); 아래와 같은 코드
m.addAttribute(boardDto);
m.addAttribute("page", page);
m.addAttribute("pageSize", pageSize);
} catch (Exception e) {
e.printStackTrace();
}
return "board";
}
@PostMapping("remove")
public String remove(HttpSession session, Integer bno, Integer page, Integer pageSize, Model m, RedirectAttributes rttr) {
String writer = (String)session.getAttribute("id");
try {
m.addAttribute("page", page);
m.addAttribute("pageSize", pageSize);
int rowCnt = boardService.remove(bno, writer);
if(rowCnt != 1)
throw new Exception("board remove error");
rttr.addFlashAttribute("msg", "DEL_OK");
} catch (Exception e) {
e.printStackTrace();
rttr.addFlashAttribute("msg", "DEL_ERROR");
}
return "redirect:/board/list"; // 모델에 담으면 "redirect:/board/list?page=&pageSize" 으로 자동으로 생성
}
@GetMapping("write")
public String write(Model m) {
m.addAttribute("mode", "new");
return "board";
}
@PostMapping("write")
public String write(BoardDto boardDto, Model m, HttpSession session, RedirectAttributes rttr) {
String writer = (String)session.getAttribute("id");
boardDto.setWriter(writer);
try {
int rowCnt = boardService.write(boardDto);
if(rowCnt!=1) {
throw new Exception("Write failed");
}
rttr.addFlashAttribute("msg", "WRT_OK");
return "redirect:/board/list";
} catch (Exception e) {
e.printStackTrace();
m.addAttribute(boardDto);
m.addAttribute("msg", "WRT_ERROR");
return "board";
}
}
@PostMapping("modify")
public String modify(BoardDto boardDto, Model m, HttpSession session, RedirectAttributes rttr) {
String writer = (String)session.getAttribute("id");
boardDto.setWriter(writer);
try {
int rowCnt = boardService.modify(boardDto);
if(rowCnt!=1) {
throw new Exception("Modify failed");
}
rttr.addFlashAttribute("msg", "MOD_OK");
return "redirect:/board/list";
} catch (Exception e) {
e.printStackTrace();
m.addAttribute(boardDto);
m.addAttribute("msg", "MOD_ERROR");
return "board";
}
}
private boolean loginCheck(HttpServletRequest request) {
// 1. 세션을 얻고
HttpSession session = request.getSession();
// 2. 세션에 id 가 있는 지 확인 있으면 true 를 반환
// if(session.getAttribute("id")!=null) {
// return true;
// } else {
// return false;
// }
return session.getAttribute("id")!=null;
}
}
PageHandler.java
package com.jcy.usedhunter.domain;
public class PageHandler {
// private int page; // 현재 페이지
// private int pageSize; // 한 페이지의 크기
// private String option;
// private String keyword;
private SearchCondition sc;
private int totalCnt; // 총 게시물 갯수
private int naviSize = 10; // 페이지 내비게이션의 크기
private int totalPage; // 전체 페이지의 개수
private int beginPage; // 내비게이션의 첫번째 페이지
private int endPage; // 내비게이션의 마지막 페이지
private boolean showPrev; // 이전 페이지로 이동하는 링크를 보여줄 것인지의 여부
private boolean showNext; // 다음 페이지로 이동하는 링크를 보여줄 것인지의 여부
public PageHandler(int totalCnt, SearchCondition sc) {
this.totalCnt = totalCnt;
this.sc = sc;
doPaging(totalCnt, sc);
}
public void doPaging(int totalCnt, SearchCondition sc) {
this.totalCnt = totalCnt;
this.sc = sc;
totalPage = (int)Math.ceil(totalCnt/(double)sc.getPageSize());
beginPage = (sc.getPage()-1) / naviSize * 10 + 1;
endPage = Math.min(beginPage + naviSize-1, totalPage);
showPrev = beginPage != 1;
showNext = endPage != totalPage;
}
public void print() {
System.out.println("page = " + sc.getPage());
System.out.print(showPrev ? "[PREV] " : "");
for(int i = beginPage; i <= endPage; i++) {
System.out.print(i + " ");
}
System.out.println(showNext ? " [NEXT] " : "");
}
public SearchCondition getSc() {
return sc;
}
public void setSc(SearchCondition sc) {
this.sc = sc;
}
public int getTotalCnt() {
return totalCnt;
}
public void setTotalCnt(int totalCnt) {
this.totalCnt = totalCnt;
}
public int getNaviSize() {
return naviSize;
}
public void setNaviSize(int naviSize) {
this.naviSize = naviSize;
}
public int getTotalPage() {
return totalPage;
}
public void setTotalPage(int totalPage) {
this.totalPage = totalPage;
}
public int getBeginPage() {
return beginPage;
}
public void setBeginPage(int beginPage) {
this.beginPage = beginPage;
}
public int getEndPage() {
return endPage;
}
public void setEndPage(int endPage) {
this.endPage = endPage;
}
public boolean isShowPrev() {
return showPrev;
}
public void setShowPrev(boolean showPrev) {
this.showPrev = showPrev;
}
public boolean isShowNext() {
return showNext;
}
public void setShowNext(boolean showNext) {
this.showNext = showNext;
}
@Override
public String toString() {
return "PageHandler [sc=" + sc + ", totalCnt=" + totalCnt + ", naviSize=" + naviSize + ", totalPage="
+ totalPage + ", beginPage=" + beginPage + ", endPage=" + endPage + ", showPrev=" + showPrev
+ ", showNext=" + showNext + "]";
}
}
board.jsp
<%@ page language="java" contentType="text/html; charset=UTF-8"%>
<%@ taglib uri="http://java.sun.com/jsp/jstl/core" prefix="c" %>
<%@ page session="true"%>
<c:set var="loginId" value="${sessionScope.id}"/>
<c:set var="loginOutLink" value="${loginId=='' ? '/login/login' : '/login/logout'}"/>
<c:set var="loginOut" value="${loginId=='' ? 'Login' : 'ID='+=loginId}"/>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>중고헌터</title>
<link rel="stylesheet" href="<c:url value='/css/menu.css'/>">
<link rel="stylesheet" href="https://cdnjs.cloudflare.com/ajax/libs/font-awesome/4.7.0/css/font-awesome.min.css">
<script src="https://code.jquery.com/jquery-1.11.3.js"></script>
<style>
* {
box-sizing: border-box;
margin: 0;
padding: 0;
font-family: "Noto Sans KR", sans-serif;
}
.container {
width : 50%;
margin : auto;
}
.writing-header {
position: relative;
margin: 20px 0 0 0;
padding-bottom: 10px;
border-bottom: 1px solid #323232;
}
input {
width: 100%;
height: 35px;
margin: 5px 0px 10px 0px;
border: 1px solid #e9e8e8;
padding: 8px;
background: #f8f8f8;
outline-color: #e6e6e6;
}
textarea {
width: 100%;
background: #f8f8f8;
margin: 5px 0px 10px 0px;
border: 1px solid #e9e8e8;
resize: none;
padding: 8px;
outline-color: #e6e6e6;
}
.frm {
width:100%;
}
.btn {
background-color: rgb(236, 236, 236); /* Blue background */
border: none; /* Remove borders */
color: black; /* White text */
padding: 6px 12px; /* Some padding */
font-size: 16px; /* Set a font size */
cursor: pointer; /* Mouse pointer on hover */
border-radius: 5px;
}
.btn:hover {
text-decoration: underline;
}
</style>
</head>
<body>
<div id="menu">
<ul>
<li id="logo">중고헌터</li>
<li><a href="<c:url value='/'/>">Home</a></li>
<li><a href="<c:url value='/board/list'/>">Board</a></li>
<li><a href="<c:url value='${loginOutLink}'/>">${loginOut}</a></li>
<li><a href="<c:url value='/register/add'/>">Sign in</a></li>
<li><a href=""><i class="fa fa-search"></i></a></li>
</ul>
</div>
<script>
let msg = "${msg}"
if(msg=="WRT_ERROR") alert("게시물 등록에 실패했습니다. 다시 시도해 주세요.");
if(msg=="MOD_ERROR") alert("게시물 등록에 실패했습니다. 다시 시도해 주세요.");
</script>
<div class="container">
<h2 class="writing-header">게시물 ${mode=="new" ? "글쓰기" : "읽기"}</h2>
<form action="" id="form">
<input type="hidden" name="bno" value="${boardDto.bno}" readonly="readonly">
<input type="text" name="title" value="${boardDto.title}" ${mode=="new" ? '' : 'readonly="readonly"'}><br>
<textarea name="content" id="" rows="20" placeholder=" 내용을 입력해 주세요." ${mode=="new" ? '' : 'readonly="readonly"'}>${boardDto.content}</textarea><br>
<c:if test="${mode eq 'new'}">
<button type="button" id="writeBtn" class="btn btn-write"><i class="fa fa-pencil"></i>등록</button>
</c:if>
<c:if test="${mode ne 'new'}">
<button type="button" id="writeNewBtn" class="btn btn-write"><i class="fa fa-pencil"></i>글쓰기</button>
</c:if>
<c:if test="${boardDto.writer eq loginId}">
<button type="button" id="modifyBtn" class="btn btn-modify"><i class="fa fa-edit"></i>수정</button>
<button type="button" id="removeBtn" class="btn btn-remove"><i class="fa fa-trash"></i>삭제</button>
</c:if>
<button type="button" id="listBtn" class="btn btn-list"><i class="fa fa-bars"></i>목록</button>
</form>
</div>
<script>
$(document).ready(function(){// main
let formCheck = function() {
let form = document.getElementById("form");
if(form.title.value=="") {
alert("제목을 입력해 주세요.");
form.title.focus();
return false;
}
if(form.content.value=="") {
alert("내용을 입력해 주세요.");
form.content.focus();
return false;
}
return true;
}
$('#listBtn').on("click", function(){
location.href = "<c:url value='/board/list'/>?page=${page}&pageSize=${pageSize}";
});
$("#writeNewBtn").on("click", function(){
location.href="<c:url value='/board/write'/>";
});
$('#writeBtn').on("click", function(){
let form = $('#form');
form.attr("action", "<c:url value='/board/write'/>");
form.attr("method", "post");
form.submit();
});
$('#modifyBtn').on("click", function(){
// 1. 읽기 상태이면 수정 상태로 변경
let form = $('#form');
let isReadOnly = $("input[name=title]").attr('readonly');
if (isReadOnly=='readonly') {
$("input[name=title]").attr('readonly', false);
$("textarea").attr('readonly', false);
$("#modifyBtn").html("등록");
$("h2").html("게시물 수정");
return;
}
// 2. 수정 상태이면, 수정된 내용을 서버로 전송
form.attr("action", "<c:url value='/board/modify'/>");
form.attr("method", "post");
form.submit();
});
$('#removeBtn').on("click", function(){
if (!confirm("정말로 삭제하시겠습니까?")) return;
let form = $('#form');
form.attr("action", "<c:url value='/board/remove'/>?page=${page}&pageSize=${pageSize}");
form.attr("method", "post");
form.submit();
});
});
</script>
</body>
</html>
boardList.jsp
<%@ page language="java" contentType="text/html; charset=UTF-8"%>
<%@ taglib uri="http://java.sun.com/jsp/jstl/core" prefix="c"%>
<%@taglib prefix="fmt" uri="http://java.sun.com/jstl/fmt_rt"%>
<%@ page session="true"%>
<c:set var="loginId" value="${sessionScope.id}" />
<c:set var="loginOutLink"
value="${loginId=='' ? '/login/login' : '/login/logout'}" />
<c:set var="loginOut" value="${loginId=='' ? 'Login' : 'ID='+=loginId}" />
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>중고헌터</title>
<link rel="stylesheet" href="<c:url value='/css/menu.css'/>">
<link rel="stylesheet"
href="https://cdnjs.cloudflare.com/ajax/libs/font-awesome/4.7.0/css/font-awesome.min.css">
<script src="https://code.jquery.com/jquery-1.11.3.js"></script>
<style>
* {
box-sizing: border-box;
margin: 0;
padding: 0;
font-family: "Noto Sans KR", sans-serif;
}
a {
text-decoration: none;
color: black;
}
button, input {
border: none;
outline: none;
}
.board-container {
width: 60%;
height: 1200px;
margin: 0 auto;
/* border: 1px solid black; */
}
.search-container {
background-color: rgb(253, 253, 250);
width: 100%;
height: 110px;
border: 1px solid #ddd;
margin-top: 10px;
margin-bottom: 30px;
display: flex;
justify-content: center;
align-items: center;
}
.search-form {
height: 37px;
display: flex;
}
.search-option {
width: 100px;
height: 100%;
outline: none;
margin-right: 5px;
border: 1px solid #ccc;
color: gray;
}
.search-option>option {
text-align: center;
}
.search-input {
color: gray;
background-color: white;
border: 1px solid #ccc;
height: 100%;
width: 300px;
font-size: 15px;
padding: 5px 7px;
}
.search-input::placeholder {
color: gray;
}
.search-button {
/* 메뉴바의 검색 버튼 아이콘 */
width: 20%;
height: 100%;
background-color: rgb(22, 22, 22);
color: rgb(209, 209, 209);
display: flex;
align-items: center;
justify-content: center;
font-size: 15px;
}
.search-button:hover {
color: rgb(165, 165, 165);
}
table {
border-collapse: collapse;
width: 100%;
border-top: 2px solid rgb(39, 39, 39);
}
tr:nth-child(even) {
background-color: #f0f0f070;
}
th, td {
width: 300px;
text-align: center;
padding: 10px 12px;
border-bottom: 1px solid #ddd;
}
td {
color: rgb(53, 53, 53);
}
.no {
width: 150px;
}
.title {
width: 50%;
}
td.title {
text-align: left;
}
td.writer {
text-align: left;
}
td.viewcnt {
text-align: right;
}
td.title:hover {
text-decoration: underline;
}
.paging {
color: black;
width: 100%;
align-items: center;
}
.page {
color: black;
padding: 6px;
margin-right: 10px;
}
.paging-active {
background-color: rgb(216, 216, 216);
border-radius: 5px;
color: rgb(24, 24, 24);
}
.paging-container {
width: 100%;
height: 70px;
display: flex;
margin-top: 50px;
margin: auto;
}
.btn-write {
background-color: rgb(236, 236, 236); /* Blue background */
border: none; /* Remove borders */
color: black; /* White text */
padding: 6px 12px; /* Some padding */
font-size: 16px; /* Set a font size */
cursor: pointer; /* Mouse pointer on hover */
border-radius: 5px;
margin-left: 30px;
}
.btn-write:hover {
text-decoration: underline;
}
</style>
</head>
<body>
<div id="menu">
<ul>
<li id="logo">중고헌터</li>
<li><a href="<c:url value='/'/>">Home</a></li>
<li><a href="<c:url value='/board/list'/>">Board</a></li>
<li><a href="<c:url value='${loginOutLink}'/>">${loginOut}</a></li>
<li><a href="<c:url value='/register/add'/>">Sign in</a></li>
<li><a href=""><i class="fa fa-search"></i></a></li>
</ul>
</div>
<div style="text-align: center">
<div class="board-container">
<div class="search-container">
<form action="<c:url value="/board/list"/>" class="search-form"
method="get">
<select class="search-option" name="option">
<option value="A"
${ph.sc.option=='A' || ph.sc.option=='' ? "selected" : ""}>제목+내용</option>
<option value="T" ${ph.sc.option=='T' ? "selected" : ""}>제목만</option>
<option value="W" ${ph.sc.option=='W' ? "selected" : ""}>작성자</option>
</select> <input type="text" name="keyword" class="search-input" type="text"
value="${ph.sc.keyword}" placeholder="검색어를 입력해주세요"> <input
type="submit" class="search-button" value="검색">
</form>
<button id="writeBtn" class="btn-write"
onclick="location.href='<c:url value="/board/write"/>'">
<i class="fa fa-pencil"></i> 글쓰기
</button>
</div>
<table>
<tr>
<th class="no">번호</th>
<th class="title">제목</th>
<th class="writer">이름</th>
<th class="regdate">등록일</th>
<th class="viewcnt">조회수</th>
</tr>
<c:forEach var="boardDto" items="${list}">
<tr>
<td class="no">${boardDto.bno}</td>
<td class="title"><a
href="<c:url value="/board/read${ph.sc.queryString}&bno=${boardDto.bno}"/>">${boardDto.title}</a></td>
<td class="writer">${boardDto.writer}</td>
<c:choose>
<c:when test="${boardDto.reg_date.time >= startOfToday}">
<td class="regdate"><fmt:formatDate
value="${boardDto.reg_date}" pattern="HH:mm" type="time" /></td>
</c:when>
<c:otherwise>
<td class="regdate"><fmt:formatDate
value="${boardDto.reg_date}" pattern="yyyy-MM-dd" type="date" /></td>
</c:otherwise>
</c:choose>
<td class="viewcnt">${boardDto.view_cnt}</td>
</tr>
</c:forEach>
</table>
<br>
<div class="paging-container">
<div class="paging">
<c:if test="${totalCnt==null || totalCnt==0}">
<div>게시물이 없습니다.</div>
</c:if>
<c:if test="${totalCnt!=null && totalCnt!=0}">
<c:if test="${ph.showPrev}">
<a class="page"
href="<c:url value="/board/list${ph.sc.getQueryString(ph.beginPage-1)}"/>"><</a>
</c:if>
<c:forEach var="i" begin="${ph.beginPage}" end="${ph.endPage}">
<a class="page ${i==ph.sc.page? "
paging-active" : ""}" href="<c:url value="/board/list${ph.sc.getQueryString(i)}"/>">${i}</a>
</c:forEach>
<c:if test="${ph.showNext}">
<a class="page"
href="<c:url value="/board/list${ph.sc.getQueryString(ph.endPage+1)}"/>">></a>
</c:if>
</c:if>
</div>
</div>
</div>
</div>
<script>
let msg = "${msg}"
if(msg=="WRT_OK") alert("성공적으로 등록되었습니다.");
if(msg=="DEL_OK") alert("성공적으로 삭제되었습니다.");
if(msg=="MOD_OK") alert("성공적으로 수정되었습니다.");
if(msg=="LIST_ERROR") alert("게시물 목록을 가져오는데 실패했습니다. 다시 시도해 주세요.");
if(msg=="READ_ERROR") alert("삭제되었거나 없는 게시물입니다.");
if(msg=="DEL_ERROR") alert("삭제되었거나 없는 게시물입니다.");
</script>
</body>
</html>
SearchCondition.java
getQueryString 메서드 추가
offset 을 (page-1)*pageSize 로 바꿔준다
public Integer getOffset() {
return (page-1)*pageSize;
}package com.jcy.usedhunter.domain;
import org.springframework.web.util.UriComponentsBuilder;
public class SearchCondition {
private Integer page = 1;
private Integer pageSize = 10;
// private Integer offset = 0;
private String keyword = "";
private String option = "";
public String getQueryString(Integer page) {
// ?page=1&pageSize=10&option=T&keyword="title"
return UriComponentsBuilder.newInstance()
.queryParam("page", page)
.queryParam("pageSize", pageSize)
.queryParam("option", option)
.queryParam("keyword", keyword)
.build().toString();
}
public String getQueryString() {
return getQueryString(page);
}
public SearchCondition() {
}
public SearchCondition(Integer page, Integer pageSize, String keyword, String option) {
this.page = page;
this.pageSize = pageSize;
this.keyword = keyword;
this.option = option;
}
public Integer getPage() {
return page;
}
public void setPage(Integer page) {
this.page = page;
}
public Integer getPageSize() {
return pageSize;
}
public void setPageSize(Integer pageSize) {
this.pageSize = pageSize;
}
public Integer getOffset() {
return (page-1)*pageSize;
}
public String getKeyword() {
return keyword;
}
public void setKeyword(String keyword) {
this.keyword = keyword;
}
public String getOption() {
return option;
}
public void setOption(String option) {
this.option = option;
}
@Override
public String toString() {
return "SearchCondition [page=" + page + ", pageSize=" + pageSize + ", offset=" + getOffset() + ", keyword="
+ keyword + ", option=" + option + "]";
}
}
검색 테스트
title1
BoardDaoImplTest.java
@Test
public void searchSelectPageTest() throws Exception {
boardDao.deleteAll();
for(int i = 1; i<=20; i++) {
BoardDto boardDto = new BoardDto("title"+i, "aaaaa", "asdf"+i);
boardDao.insert(boardDto);
}
SearchCondition sc = new SearchCondition(1, 10, "title2", "T");
List<BoardDto> list = boardDao.searchSelectPage(sc);
// System.out.println("list = " + list);
assertTrue(list.size()==2);
sc = new SearchCondition(1, 10, "asdf2", "W");
list = boardDao.searchSelectPage(sc);
// System.out.println("list = " + list);
assertTrue(list.size()==2);
}
@Test
public void searchResultCntTest() throws Exception {
boardDao.deleteAll();
for(int i = 1; i<=20; i++) {
BoardDto boardDto = new BoardDto("title"+i, "bbbbbb", "asdf"+i);
boardDao.insert(boardDto);
}
SearchCondition sc = new SearchCondition(1, 10, "title2", "T");
int resultCnt = boardDao.searchResultCnt(sc);
// System.out.println("list = " + list);
assertTrue(resultCnt==2);
sc = new SearchCondition(1, 10, "asdf2", "W");
resultCnt = boardDao.searchResultCnt(sc);
// System.out.println("list = " + list);
assertTrue(resultCnt==2);
}
include 사용하기
boardMapper.xml
<sql id="searchCondition">
<choose>
<when test='option=="T"'>
AND title LIKE concat("%", #{keyword}, "%")
</when>
<when test='option=="W"'>
AND writer LIKE concat("%", #{keyword}, "%")
</when>
<otherwise>
AND (title LIKE concat("%", #{keyword}, "%")
OR content LIKE concat("%", #{keyword}, "%"))
</otherwise>
</choose>
</sql>
<select id="searchSelectPage" parameterType="SearchCondition" resultType="BoardDto">
<include refid="selectFromBoard"/>
WHERE TRUE
<include refid="searchCondition"/>
ORDER BY reg_date DESC, bno DESC
LIMIT #{offset}, #{pageSize}
</select>
<select id="searchResultCnt" parameterType="SearchCondition" resultType="int">
SELECT count(*)
FROM board
WHERE TRUE
<include refid="searchCondition"/>
</select><?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="com.jcy.usedhunter.dao.BoardMapper">
<insert id="insert" parameterType="BoardDto">
INSERT INTO board
(title, content, writer)
VALUES
(#{title}, #{content}, #{writer})
</insert>
<sql id="selectFromBoard">
SELECT bno, title, content, writer, view_cnt, comment_cnt, reg_date
FROM board
</sql>
<select id="select" parameterType="int" resultType="BoardDto">
<include refid="selectFromBoard"/>
WHERE bno = #{bno}
</select>
<select id="selectAll" resultType="BoardDto">
SELECT bno, title, content, writer, view_cnt, comment_cnt, reg_date
FROM board
ORDER BY reg_date DESC, bno DESC
</select>
<select id="count" resultType="int">
SELECT count(*) FROM board
</select>
<select id="selectPage" parameterType="map" resultType="BoardDto">
<include refid="selectFromBoard"/>
ORDER BY reg_date DESC, bno DESC
LIMIT #{offset}, #{pageSize}
</select>
<sql id="searchCondition">
<choose>
<when test='option=="T"'>
AND title LIKE concat("%", #{keyword}, "%")
</when>
<when test='option=="W"'>
AND writer LIKE concat("%", #{keyword}, "%")
</when>
<otherwise>
AND (title LIKE concat("%", #{keyword}, "%")
OR content LIKE concat("%", #{keyword}, "%"))
</otherwise>
</choose>
</sql>
<select id="searchSelectPage" parameterType="SearchCondition" resultType="BoardDto">
<include refid="selectFromBoard"/>
WHERE TRUE
<include refid="searchCondition"/>
ORDER BY reg_date DESC, bno DESC
LIMIT #{offset}, #{pageSize}
</select>
<select id="searchResultCnt" parameterType="SearchCondition" resultType="int">
SELECT count(*)
FROM board
WHERE TRUE
<include refid="searchCondition"/>
</select>
<update id="update" parameterType="BoardDto">
UPDATE board
SET title = #{title}
, content = #{content}
, up_date = now()
WHERE bno = #{bno} and writer = #{writer}
</update>
<update id="updateCommentCnt" parameterType="map">
UPDATE board
SET comment_cnt = comment_cnt + #{cnt}
WHERE bno = #{bno}
</update>
<update id="increaseViewCnt" parameterType="int">
UPDATE board
SET view_cnt = view_cnt + 1
WHERE bno = #{bno}
</update>
<delete id="delete" parameterType="map">
DELETE FROM board WHERE bno = #{bno} and writer = #{writer}
</delete>
<delete id="deleteAll">
DELETE FROM board
</delete>
</mapper>
코어 태그 라이브러리에 있는 아웃 태그 써서 스크립트 조작 막기
board.jsp
<input type="text" name="title" value="<c:out value='${boardDto.title}'/>" placeholder=" 제목을 입력해 주세요." ${mode=="new" ? '' : 'readonly="readonly"'}><br>
<textarea name="content" id="" rows="20" placeholder=" 내용을 입력해 주세요." ${mode=="new" ? '' : 'readonly="readonly"'}><c:out value="${boardDto.content}"/></textarea><br><%@ page language="java" contentType="text/html; charset=UTF-8"%>
<%@ taglib uri="http://java.sun.com/jsp/jstl/core" prefix="c" %>
<%@ page session="true"%>
<c:set var="loginId" value="${sessionScope.id}"/>
<c:set var="loginOutLink" value="${loginId=='' ? '/login/login' : '/login/logout'}"/>
<c:set var="loginOut" value="${loginId=='' ? 'Login' : 'ID='+=loginId}"/>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>중고헌터</title>
<link rel="stylesheet" href="<c:url value='/css/menu.css'/>">
<link rel="stylesheet" href="https://cdnjs.cloudflare.com/ajax/libs/font-awesome/4.7.0/css/font-awesome.min.css">
<script src="https://code.jquery.com/jquery-1.11.3.js"></script>
<style>
* {
box-sizing: border-box;
margin: 0;
padding: 0;
font-family: "Noto Sans KR", sans-serif;
}
.container {
width : 50%;
margin : auto;
}
.writing-header {
position: relative;
margin: 20px 0 0 0;
padding-bottom: 10px;
border-bottom: 1px solid #323232;
}
input {
width: 100%;
height: 35px;
margin: 5px 0px 10px 0px;
border: 1px solid #e9e8e8;
padding: 8px;
background: #f8f8f8;
outline-color: #e6e6e6;
}
textarea {
width: 100%;
background: #f8f8f8;
margin: 5px 0px 10px 0px;
border: 1px solid #e9e8e8;
resize: none;
padding: 8px;
outline-color: #e6e6e6;
}
.frm {
width:100%;
}
.btn {
background-color: rgb(236, 236, 236); /* Blue background */
border: none; /* Remove borders */
color: black; /* White text */
padding: 6px 12px; /* Some padding */
font-size: 16px; /* Set a font size */
cursor: pointer; /* Mouse pointer on hover */
border-radius: 5px;
}
.btn:hover {
text-decoration: underline;
}
</style>
</head>
<body>
<div id="menu">
<ul>
<li id="logo">중고 헌터</li>
<li><a href="<c:url value='/'/>">Home</a></li>
<li><a href="<c:url value='/board/list'/>">Board</a></li>
<li><a href="<c:url value='${loginOutLink}'/>">${loginOut}</a></li>
<li><a href="<c:url value='/register/add'/>">Sign in</a></li>
<li><a href=""><i class="fa fa-search"></i></a></li>
</ul>
</div>
<script>
let msg = "${msg}"
if(msg=="WRT_ERROR") alert("게시물 등록에 실패했습니다. 다시 시도해 주세요.");
if(msg=="MOD_ERROR") alert("게시물 등록에 실패했습니다. 다시 시도해 주세요.");
</script>
<div class="container">
<h2 class="writing-header">게시물 ${mode=="new" ? "글쓰기" : "읽기"}</h2>
<form action="" id="form">
<input type="hidden" name="bno" value="${boardDto.bno}" readonly="readonly">
<input type="text" name="title" value="<c:out value='${boardDto.title}'/>" placeholder=" 제목을 입력해 주세요." ${mode=="new" ? '' : 'readonly="readonly"'}><br>
<textarea name="content" id="" rows="20" placeholder=" 내용을 입력해 주세요." ${mode=="new" ? '' : 'readonly="readonly"'}><c:out value="${boardDto.content}"/></textarea><br>
<c:if test="${mode eq 'new'}">
<button type="button" id="writeBtn" class="btn btn-write"><i class="fa fa-pencil"></i>등록</button>
</c:if>
<c:if test="${mode ne 'new'}">
<button type="button" id="writeNewBtn" class="btn btn-write"><i class="fa fa-pencil"></i>글쓰기</button>
</c:if>
<c:if test="${boardDto.writer eq loginId}">
<button type="button" id="modifyBtn" class="btn btn-modify"><i class="fa fa-edit"></i>수정</button>
<button type="button" id="removeBtn" class="btn btn-remove"><i class="fa fa-trash"></i>삭제</button>
</c:if>
<button type="button" id="listBtn" class="btn btn-list"><i class="fa fa-bars"></i>목록</button>
</form>
</div>
<script>
$(document).ready(function(){// main
let formCheck = function() {
let form = document.getElementById("form");
if(form.title.value=="") {
alert("제목을 입력해 주세요.");
form.title.focus();
return false;
}
if(form.content.value=="") {
alert("내용을 입력해 주세요.");
form.content.focus();
return false;
}
return true;
}
$('#listBtn').on("click", function(){
location.href = "<c:url value='/board/list'/>?page=${page}&pageSize=${pageSize}";
});
$("#writeNewBtn").on("click", function(){
location.href="<c:url value='/board/write'/>";
});
$('#writeBtn').on("click", function(){
let form = $('#form');
form.attr("action", "<c:url value='/board/write'/>");
form.attr("method", "post");
form.submit();
});
$('#modifyBtn').on("click", function(){
// 1. 읽기 상태이면 수정 상태로 변경
let form = $('#form');
let isReadOnly = $("input[name=title]").attr('readonly');
if (isReadOnly=='readonly') {
$("input[name=title]").attr('readonly', false);
$("textarea").attr('readonly', false);
$("#modifyBtn").html("등록");
$("h2").html("게시물 수정");
return;
}
// 2. 수정 상태이면, 수정된 내용을 서버로 전송
form.attr("action", "<c:url value='/board/modify'/>");
form.attr("method", "post");
form.submit();
});
$('#removeBtn').on("click", function(){
if (!confirm("정말로 삭제하시겠습니까?")) return;
let form = $('#form');
form.attr("action", "<c:url value='/board/remove'/>?page=${page}&pageSize=${pageSize}");
form.attr("method", "post");
form.submit();
});
});
</script>
</body>
</html>
boardList.jsp
<td class="title"><a href="<c:url value="/board/read${ph.sc.queryString}&bno=${boardDto.bno}"/>"><c:out value="${boardDto.title}"/></a></td><%@ page language="java" contentType="text/html; charset=UTF-8"%>
<%@ taglib uri="http://java.sun.com/jsp/jstl/core" prefix="c"%>
<%@taglib prefix="fmt" uri="http://java.sun.com/jstl/fmt_rt"%>
<%@ page session="true"%>
<c:set var="loginId" value="${sessionScope.id}" />
<c:set var="loginOutLink"
value="${loginId=='' ? '/login/login' : '/login/logout'}" />
<c:set var="loginOut" value="${loginId=='' ? 'Login' : 'ID='+=loginId}" />
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>중고헌터</title>
<link rel="stylesheet" href="<c:url value='/css/menu.css'/>">
<link rel="stylesheet"
href="https://cdnjs.cloudflare.com/ajax/libs/font-awesome/4.7.0/css/font-awesome.min.css">
<script src="https://code.jquery.com/jquery-1.11.3.js"></script>
<style>
* {
box-sizing: border-box;
margin: 0;
padding: 0;
font-family: "Noto Sans KR", sans-serif;
}
a {
text-decoration: none;
color: black;
}
button, input {
border: none;
outline: none;
}
.board-container {
width: 60%;
height: 1200px;
margin: 0 auto;
/* border: 1px solid black; */
}
.search-container {
background-color: rgb(253, 253, 250);
width: 100%;
height: 110px;
border: 1px solid #ddd;
margin-top: 10px;
margin-bottom: 30px;
display: flex;
justify-content: center;
align-items: center;
}
.search-form {
height: 37px;
display: flex;
}
.search-option {
width: 100px;
height: 100%;
outline: none;
margin-right: 5px;
border: 1px solid #ccc;
color: gray;
}
.search-option>option {
text-align: center;
}
.search-input {
color: gray;
background-color: white;
border: 1px solid #ccc;
height: 100%;
width: 300px;
font-size: 15px;
padding: 5px 7px;
}
.search-input::placeholder {
color: gray;
}
.search-button {
/* 메뉴바의 검색 버튼 아이콘 */
width: 20%;
height: 100%;
background-color: rgb(22, 22, 22);
color: rgb(209, 209, 209);
display: flex;
align-items: center;
justify-content: center;
font-size: 15px;
}
.search-button:hover {
color: rgb(165, 165, 165);
}
table {
border-collapse: collapse;
width: 100%;
border-top: 2px solid rgb(39, 39, 39);
}
tr:nth-child(even) {
background-color: #f0f0f070;
}
th, td {
width: 300px;
text-align: center;
padding: 10px 12px;
border-bottom: 1px solid #ddd;
}
td {
color: rgb(53, 53, 53);
}
.no {
width: 150px;
}
.title {
width: 50%;
}
td.title {
text-align: left;
}
td.writer {
text-align: left;
}
td.viewcnt {
text-align: right;
}
td.title:hover {
text-decoration: underline;
}
.paging {
color: black;
width: 100%;
align-items: center;
}
.page {
color: black;
padding: 6px;
margin-right: 10px;
}
.paging-active {
background-color: rgb(216, 216, 216);
border-radius: 5px;
color: rgb(24, 24, 24);
}
.paging-container {
width: 100%;
height: 70px;
display: flex;
margin-top: 50px;
margin: auto;
}
.btn-write {
background-color: rgb(236, 236, 236); /* Blue background */
border: none; /* Remove borders */
color: black; /* White text */
padding: 6px 12px; /* Some padding */
font-size: 16px; /* Set a font size */
cursor: pointer; /* Mouse pointer on hover */
border-radius: 5px;
margin-left: 30px;
}
.btn-write:hover {
text-decoration: underline;
}
</style>
</head>
<body>
<div id="menu">
<ul>
<li id="logo">중고 헌터</li>
<li><a href="<c:url value='/'/>">Home</a></li>
<li><a href="<c:url value='/board/list'/>">Board</a></li>
<li><a href="<c:url value='${loginOutLink}'/>">${loginOut}</a></li>
<li><a href="<c:url value='/register/add'/>">Sign in</a></li>
<li><a href=""><i class="fa fa-search"></i></a></li>
</ul>
</div>
<div style="text-align: center">
<div class="board-container">
<div class="search-container">
<form action="<c:url value="/board/list"/>" class="search-form"
method="get">
<select class="search-option" name="option">
<option value="A"
${ph.sc.option=='A' || ph.sc.option=='' ? "selected" : ""}>제목+내용</option>
<option value="T" ${ph.sc.option=='T' ? "selected" : ""}>제목만</option>
<option value="W" ${ph.sc.option=='W' ? "selected" : ""}>작성자</option>
</select> <input type="text" name="keyword" class="search-input" type="text"
value="${ph.sc.keyword}" placeholder="검색어를 입력해주세요"> <input
type="submit" class="search-button" value="검색">
</form>
<button id="writeBtn" class="btn-write"
onclick="location.href='<c:url value="/board/write"/>'">
<i class="fa fa-pencil"></i> 글쓰기
</button>
</div>
<table>
<tr>
<th class="no">번호</th>
<th class="title">제목</th>
<th class="writer">이름</th>
<th class="regdate">등록일</th>
<th class="viewcnt">조회수</th>
</tr>
<c:forEach var="boardDto" items="${list}">
<tr>
<td class="no">${boardDto.bno}</td>
<td class="title"><a href="<c:url value="/board/read${ph.sc.queryString}&bno=${boardDto.bno}"/>"><c:out value="${boardDto.title}"/></a></td>
<td class="writer">${boardDto.writer}</td>
<c:choose>
<c:when test="${boardDto.reg_date.time >= startOfToday}">
<td class="regdate"><fmt:formatDate
value="${boardDto.reg_date}" pattern="HH:mm" type="time" /></td>
</c:when>
<c:otherwise>
<td class="regdate"><fmt:formatDate
value="${boardDto.reg_date}" pattern="yyyy-MM-dd" type="date" /></td>
</c:otherwise>
</c:choose>
<td class="viewcnt">${boardDto.view_cnt}</td>
</tr>
</c:forEach>
</table>
<br>
<div class="paging-container">
<div class="paging">
<c:if test="${totalCnt==null || totalCnt==0}">
<div>게시물이 없습니다.</div>
</c:if>
<c:if test="${totalCnt!=null && totalCnt!=0}">
<c:if test="${ph.showPrev}">
<a class="page"
href="<c:url value="/board/list${ph.sc.getQueryString(ph.beginPage-1)}"/>"><</a>
</c:if>
<c:forEach var="i" begin="${ph.beginPage}" end="${ph.endPage}">
<a class="page ${i==ph.sc.page? "
paging-active" : ""}" href="<c:url value="/board/list${ph.sc.getQueryString(i)}"/>">${i}</a>
</c:forEach>
<c:if test="${ph.showNext}">
<a class="page"
href="<c:url value="/board/list${ph.sc.getQueryString(ph.endPage+1)}"/>">></a>
</c:if>
</c:if>
</div>
</div>
</div>
</div>
<script>
let msg = "${msg}"
if(msg=="WRT_OK") alert("성공적으로 등록되었습니다.");
if(msg=="DEL_OK") alert("성공적으로 삭제되었습니다.");
if(msg=="MOD_OK") alert("성공적으로 수정되었습니다.");
if(msg=="LIST_ERROR") alert("게시물 목록을 가져오는데 실패했습니다. 다시 시도해 주세요.");
if(msg=="READ_ERROR") alert("삭제되었거나 없는 게시물입니다.");
if(msg=="DEL_ERROR") alert("삭제되었거나 없는 게시물입니다.");
</script>
</body>
</html>
'프로젝트 > 중고헌터' 카테고리의 다른 글
| 중고헌터 - 댓글 기능 구현 1 - DAO와 Service의 작성 (0) | 2022.10.08 |
|---|---|
| 중고헌터 - REST API와 Ajax (0) | 2022.10.08 |
| 중고헌터 - 게시판 읽기, 쓰기, 수정, 삭제 구현 2 (0) | 2022.10.06 |
| 중고헌터 - 게시판 읽기, 쓰기, 수정, 삭제 구현 (0) | 2022.10.03 |
| 중고헌터 - 게시판 목록 만들기 (0) | 2022.10.03 |