Mybatis

<!-- mybatis--> <dependency> <groupId>org.mybatis</groupId> <artifactId>mybatis</artifactId> <version>3.5.6</version> </dependency> <!-- mybatis-spring --> <dependency> <groupId>org.mybatis</groupId> <artifactId>mybatis-spring</artifactId> <version>1.3.1</version> </dependency>

<!-- mysql 드라이버 --> <dependency> <groupId>com.mysql</groupId> <artifactId>mysql-connector-j</artifactId> <version>8.1.0</version> </dependency> <!-- Spring JDBC --> <dependency> <groupId>org.springframework</groupId> <artifactId>spring-jdbc</artifactId> <version>${org.springframework-version}</version> </dependency>

<!-- lombok --> <dependency> <groupId>org.projectlombok</groupId> <artifactId>lombok</artifactId> <version>1.18.30</version> <scope>provided</scope> </dependency>

xmlns:mybatis-spring="http://mybatis.org/schema/mybatis-spring"

http://mybatis.org/schema/mybatis-spring http://mybatis.org/schema/mybatis-spring-1.2.xsd

<!-- DriverManagerDataSource 빈 등록 --> <bean id="dataSource" class="org.springframework.jdbc.datasource.DriverManagerDataSource"> <property name="driverClassName" value="com.mysql.jdbc.Driver"></property> <property name="url" value="jdbc:mysql://127.0.0.1:3306/joeun"></property> <property name="username" value="joeun"></property> <property name="password" value="123456"></property> </bean> <!-- SqlSessionFactoryBean 빈 등록 --> <bean id="sqlSessionFactory" class="org.mybatis.spring.SqlSessionFactoryBean"> <property name="dataSource" ref="dataSource" /> <property name="configLocation" value="classpath:/mybatis-config.xml"></property> </bean> <!-- MyBatis SqlSessionTemplate 빈 등록 --> <bean id="sqlSession" class="org.mybatis.spring.SqlSessionTemplate" destroy-method="clearCache"> <constructor-arg name="sqlSessionFactory" ref="sqlSessionFactory"></constructor-arg> </bean>

<!-- Mapper 인터페이스 패키지 경로 설정 --> <mybatis-spring:scan base-package="com.aloha.spring.mapper" />

<?xml version="1.0" encoding="UTF-8" ?> <!DOCTYPE configuration PUBLIC "-//mybatis.org//DTD Config 3.0//EN" "https://mybatis.org/dtd/mybatis-3-config.dtd"> <configuration> <!-- 설정 --> <settings> <!-- 언더스코어 케이스인 컬럼을 카멜 케이스로 변환하는 설정 --> <!-- no - no --> <setting name="mapUnderscoreToCamelCase" value="true"/> </settings> <!-- 타입 별칭 설정 --> <typeAliases> <!-- 패키지부터의 전체경로를 갖는 자바 타입을 축약해서 별칭으로 지정 --> <!-- <typeAlias alias="Board" type="com.aloha.spring.dto.Board"/> <typeAlias alias="User" type="com.aloha.spring.dto.User"/> <typeAlias alias="Comment" type="com.aloha.spring.dto.Comment"/> --> <!-- 테이블과 매핑할 DTO가 있는 패키지 경로 지정 --> <package name="com.aloha.spring.dto"/> </typeAliases> </configuration>

<?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"> <!-- namespace="매퍼 인터페이스 경로" --> <mapper namespace="com.aloha.spring.mapper.BoardMapper"> </mapper>

<?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"> <!-- namespace="매퍼 인터페이스 경로" --> <mapper namespace="com.aloha.spring.mapper.BoardMapper"> <!-- sql 태그 작성 --> <!-- 게시글 목록 --> <!-- id : 연결할 매퍼 인터페이스의 메소드명 --> <!-- resultType : 조회 결과를 매핑할 클래스 타입 (패키지경로+클래스명) --> <select id="list" resultType="com.aloha.spring.dto.Board"> SELECT no 'no' ,title ,writer ,content ,reg_date 'regDate' ,upd_date 'updDate' ,views FROM board </select> <!-- 게시글 조회 --> <!-- 매핑 파라미터 : #{파라미터명} --> <!-- #{} 파라미터 개수가 1개라면, 이름과 무관하게 매핑된다. --> <select id="select" resultType="com.aloha.spring.dto.Board"> SELECT no 'no' ,title ,writer ,content ,reg_date 'regDate' ,upd_date 'updDate' ,views FROM board WHERE no = #{no} </select> <!-- 게시글 등록 --> <!-- Mapper 인터페이스의 넘겨줄 파라미터가 여러 개라면 객체로 매핑할 수 있다. --> <!-- 매개변수로 Board 클래스를 지정하면 변수들이 자동으로 #{변수} 파라미터에 매핑된다. --> <!-- #{index} : 파라미터를 순서대로 index 로 매핑하여 사용할 수도 있다. --> <insert id="insert"> INSERT INTO board ( title, writer, content ) VALUES ( #{title}, #{writer}, #{content} ) <!-- (index로 매핑하는 경우) VALUES ( #{0}, #{1}, #{2} ) --> </insert> <!-- 게시글 수정 --> <update id="update"> UPDATE board SET title = #{title} ,writer = #{writer} ,content = #{content} ,upd_date = now() WHERE no = #{no} </update> <!-- 게시글 삭제 --> <delete id="delete"> DELETE FROM board WHERE no = #{no} </delete> </mapper>

@Mapper public interface BoardMapper { // 메소드 명은 Mapper.xml 매핑 파일의 SQL 태그 id 값과 일치해야한다. public List<Board> list() throws Exception; public Integer insert(Board board) throws Exception; public Board select(Integer no) throws Exception; public Integer update(Board board) throws Exception; public Integer delete(Integer no) throws Exception; }

CREATE TABLE `board` ( `no` int NOT NULL AUTO_INCREMENT, `title` varchar(100) NOT NULL, `writer` varchar(100) NOT NULL, `content` text, `reg_date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, `upd_date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, `views` int NOT NULL DEFAULT '0', PRIMARY KEY (`no`) ) COMMENT='게시판';

INSERT INTO `board` (`title`, `writer`, `content`, `views`) VALUES ('첫 번째 글', '작성자1', '첫 번째 글의 내용입니다.', 5), ('두 번째 글', '작성자2', '두 번째 글의 내용입니다.', 12), ('세 번째 글', '작성자3', '세 번째 글의 내용입니다.', 8), ('네 번째 글', '작성자4', '네 번째 글의 내용입니다.', 20), ('다섯 번째 글', '작성자5', '다섯 번째 글의 내용입니다.', 7), ('여섯 번째 글', '작성자6', '여섯 번째 글의 내용입니다.', 15), ('일곱 번째 글', '작성자7', '일곱 번째 글의 내용입니다.', 3), ('여덟 번째 글', '작성자8', '여덟 번째 글의 내용입니다.', 9), ('아홉 번째 글', '작성자9', '아홉 번째 글의 내용입니다.', 22), ('열 번째 글', '작성자10', '열 번째 글의 내용입니다.', 0);

@Data public class Board { private int no; private String title; private String writer; private String content; private Date regDate; private Date updDate; private int views; public Board() { } public Board(String title, String writer, String content) { this.title = title; this.writer = writer; this.content = content; } }

public interface BoardService { // 게시글 목록 public List<Board> list() throws Exception; // 게시글 조회 public Board select(int no) throws Exception;; // 게시글 등록 public Integer insert(Board board) throws Exception; // 게시글 수정 public Integer update(Board board) throws Exception; // 게시글 삭제 public Integer delete(int no) throws Exception; }

@Service // 비즈니스 로직을 처리하는 서비스 클래스로 빈 등록 public class BoardServiceImpl implements BoardService { private static final Logger logger = LoggerFactory.getLogger(BoardServiceImpl.class); @Autowired private BoardMapper boardMapper; @Override public List<Board> list() throws Exception { List<Board> boardList = boardMapper.list(); return boardList; } @Override public Board select(int no) throws Exception { Board board = boardMapper.select(no); return board; } @Override public Integer insert(Board board) throws Exception { int result = boardMapper.insert(board); return result; } @Override public Integer update(Board board) throws Exception { int result = boardMapper.update(board); return result; } @Override public Integer delete(int no) throws Exception { int result = boardMapper.delete(no); return result; } }

package com.aloha.spring.controller; import java.util.List; import org.slf4j.Logger; import org.slf4j.LoggerFactory; 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 com.aloha.spring.dto.Board; import com.aloha.spring.service.BoardService; @Controller // 컨트롤러로 빈 등록 @RequestMapping("/board") // 클래스 레벨 경로 /board 로 지정 public class BoardController { private static final Logger logger = LoggerFactory.getLogger(BoardController.class); @Autowired // 의존성 자동 주입 private BoardService boardSerivce; /** * 게시글 목록 * @param model * @return * @throws Exception */ // @RequestMapping(value = "/list", method = RequestMethod.GET) @GetMapping("/list") // Spring 4.3 버전 사용 가능 public String list(Model model) throws Exception { List<Board> boardList = boardSerivce.list(); model.addAttribute("boardList", boardList); return "board/list"; // board/list.jsp 화면 응답 } /** * 게시글 조회 * @param model * @param no * @return * @throws Exception */ @GetMapping("/read") public String read(Model model, int no) throws Exception { Board board = boardSerivce.select(no); model.addAttribute("board", board); return "board/read"; } // 게시글 등록 - /board/insert - [GET] @GetMapping("/insert") public String insert() throws Exception { return "board/insert"; } // 게시글 등록 처리 - /board/insert - [POST] @PostMapping("/insert") public String insertPro(Board board) throws Exception { int result = boardSerivce.insert(board); logger.info("게시글 등록 요청 - result : " + result); return "redirect:/board/list"; } // 게시글 수정 - /board/update - [GET] @GetMapping("/update") public String update(Model model, int no) throws Exception { Board board = boardSerivce.select(no); model.addAttribute("board", board); return "board/update"; } // 게시글 수정 처리 - /board/update - [POST] @PostMapping("/update") public String updatePro(Board board) throws Exception { int result = boardSerivce.update(board); logger.info("게시글 수정 요청 - result : " + result); return "redirect:/board/list"; } // 게시글 삭제 처리 - /board/delete - [POST] @PostMapping("/delete") public String deletePro(int no) throws Exception { int result = boardSerivce.delete(no); logger.info("게시글 삭제 요청 - result : " + result); return "redirect:/board/list"; } }

<%@ page language="java" contentType="text/html; charset=UTF-8" pageEncoding="UTF-8"%> <%@ taglib uri="http://java.sun.com/jsp/jstl/core" prefix="c" %> <%@ taglib uri="http://java.sun.com/jsp/jstl/fmt" prefix="fmt" %> <!DOCTYPE html> <html> <head> <meta charset="UTF-8"> <title>게시글 목록</title> </head> <body> <h1>게시글 목록</h1> <a href="${ pageContext.request.contextPath }/board/insert">글쓰기</a> <table border="1"> <tr> <th>번호</th> <th>제목</th> <th>작성자</th> <th>등록일자</th> <th>수정일자</th> <th>조회수</th> </tr> <c:forEach items="${boardList}" var="board"> <tr> <td align="center">${board.no}</td> <td width="300"> <a href="${ pageContext.request.contextPath }/board/read?no=${board.no}"> ${board.title} </a> </td> <td align="center" width="120">${board.writer}</td> <%-- <td align="center">${board.regDate}</td> --%> <td align="center"> <fmt:formatDate value="${board.regDate}" pattern="yyyy-MM-dd HH:mm:ss"/> </td> <%-- <td align="center">${board.updDate}</td> --%> <td align="center"> <fmt:formatDate value="${board.updDate}" pattern="yyyy-MM-dd HH:mm:ss"/> </td> <td align="center">${board.views}</td> </tr> </c:forEach> </table> </body> </html>

<%@ page language="java" contentType="text/html; charset=UTF-8" pageEncoding="UTF-8"%> <!DOCTYPE html> <html> <head> <meta charset="UTF-8"> <title>게시글 조회</title> </head> <body> <h1>게시글 조회</h1> <form action="${ pageContext.request.contextPath }/board/delete" method="POST"> <input type="hidden" name="no" value="${board.no}" /> <table> <tr> <td><label for="title">제목</label></td> <td><input type="text" name="title" value="${board.title}" /></td> </tr> <tr> <td><label for="writer">작성자</label></td> <td><input type="text" name="writer" value="${board.writer}" /></td> </tr> <tr> <td><label for="content">내용</label></td> <td> <textarea rows="5" cols="20" name="content" >${board.content}</textarea> </td> </tr> </table> <div> <a href="${ pageContext.request.contextPath }/board/update?no=${board.no}">수정</a> <input type="submit" value="삭제" /> </div> </form> </body> </html>

<%@ page language="java" contentType="text/html; charset=UTF-8" pageEncoding="UTF-8"%> <!DOCTYPE html> <html> <head> <meta charset="UTF-8"> <title>게시글 쓰기</title> </head> <body> <h1>게시글 쓰기</h1> <form action="${ pageContext.request.contextPath }/board/insert" method="POST"> <table> <tr> <td><label for="title">제목</label></td> <td><input type="text" name="title" /></td> </tr> <tr> <td><label for="writer">작성자</label></td> <td><input type="text" name="writer" /></td> </tr> <tr> <td><label for="content">내용</label></td> <td> <textarea rows="5" cols="20" name="content" ></textarea> </td> </tr> </table> <div> <input type="submit" value="등록" /> </div> </form> </body> </html>

<%@ page language="java" contentType="text/html; charset=UTF-8" pageEncoding="UTF-8"%> <!DOCTYPE html> <html> <head> <meta charset="UTF-8"> <title>게시글 수정</title> </head> <body> <h1>게시글 수정</h1> <form action="${ pageContext.request.contextPath }/board/update" method="POST"> <input type="hidden" name="no" value="${board.no}" /> <table> <tr> <td><label for="title">제목</label></td> <td><input type="text" name="title" value="${board.title}" /></td> </tr> <tr> <td><label for="writer">작성자</label></td> <td><input type="text" name="writer" value="${board.writer}" /></td> </tr> <tr> <td><label for="content">내용</label></td> <td> <textarea rows="5" cols="20" name="content" >${board.content}</textarea> </td> </tr> </table> <input type="submit" value="수정" /> </form> <form action="${ pageContext.request.contextPath }/board/delete" method="POST"> <input type="hidden" name="no" value="${board.no}" /> <input type="submit" value="삭제" /> </form> </body> </html>

public interface BoardMapper { public List<Board> list(Board board); }

<select id="list" resultType="com.aloha.spring.dto.Board"> SELECT * FROM board WHERE title = #{title} AND writer = #{writer} </select>

public interface BoardMapper { public int insert(@Param("title") String title , @Param("writer") String writer , @Param("content") String content); }

<insert id="insert"> INSERT INTO board ( title, writer, content ) VALUES ( #{title}, #{writer}, #{content} ) </insert>

public interface BoardMapper { public int update(Map<String,String> map); }

<update id="update"> UPDATE board SET title = #{title} ,writer = #{writer} ,content = #{content} ,upd_date = now() WHERE no = #{no} </update>

<select id="select" resultType="com.aloha.spring.dto.Board"> SELECT * FROM board WHERE no = #{no} </select>

<insert id="insert"> INSERT INTO board (title, writer, content) VALUES (#{title}, #{writer}, #{content}) </insert>

<update id="update"> UPDATE board SET title = #{title}, writer = #{writer}, content = #{content}, upd_date = now() WHERE no = #{no} </update>

<delete id="delete"> DELETE FROM board WHERE no = #{no} </delete>

<insert id="insert"> <selectKey resultType="java.lang.Integer" keyProperty="parentNo" order="BEFORE"> SELECT MAX( ${pk} ) FROM ${parentTable} </selectKey> INSERT INTO file (parent_table, parent_no, file_name, file_path) VALUES (#{parentTable}, #{parentNo}, #{fileName}, #{filePath}) </insert>

<select id="callFunction" resultType="java.lang.Integer" statementType="CALLABLE"> { #{result, mode=OUT, jdbcType=INTEGER} = call functionName(#{param1, jdbcType=INTEGER}, #{param2, jdbcType=INTEGER}) } </select>

<insert id="callProcedure" statementType="CALLABLE"> { call procedureName(#{param1, jdbcType=INTEGER}, #{param2, jdbcType=VARCHAR}) } </insert>

<if test="condition"> <!-- 조건이 true일 때 실행되는 SQL 구문 --> </if>

<choose> <when test="condition1"> <!-- condition1이 true일 때 실행되는 SQL 구문 --> </when> <when test="condition2"> <!-- condition2가 true일 때 실행되는 SQL 구문 --> </when> <otherwise> <!-- 위의 모든 조건이 false일 때 실행되는 SQL 구문 --> </otherwise> </choose>

<foreach collection="list" item="item" separator=","> <!-- list의 모든 요소에 대해 실행되는 SQL 구문 --> </foreach>

<!-- prefix와 suffix에는 해당 구문을 둘러싸는 접두사와 접미사가 올 수 있으며, 접두사 또는 접미사가 필요하지 않은 경우 생략할 수 있습니다. --> <trim prefix="prefix" suffix="suffix" prefixOverrides="prefixOverrides" suffixOverrides="suffixOverrides"> <!-- prefixOverrides와 suffixOverrides는 생략 가능한 요소로, SQL 구문의 접두사 또는 접미사를 제거합니다. --> <!-- SQL 구문 --> </trim>

<update id="update"> UPDATE board <set> <if test="title != null"> title = #{title}, </if> <if test="writer != null"> writer = #{writer}, </if> <if test="content != null"> content = #{content}, </if> </set> WHERE no = #{no} </update>