GDJ 24/06/04 (MyBatis 과제)

MyBatis 과제

MemberController.java (Controller)

package com.gd.homework.controller;

import java.util.List;
import java.util.Map;

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.RequestParam;

import com.gd.homework.dto.AddMember;
import com.gd.homework.service.MemberService;

import lombok.extern.slf4j.Slf4j;

@Slf4j
@Controller
public class MemberController {

	@Autowired
	MemberService memberService;
	
	@GetMapping("/signUpMember")
	public String signUpMember() {
		
		return "signUpMember";
	}
	
	@PostMapping("/signUpMember")
	public String signUpMember(AddMember am) {
		
		log.debug("am : " + am.toString());
		
		memberService.addMember(am);
		
		return "redirect:/memberList";
	}
	
	@GetMapping("/memberList")
	public String memberList(Model model,
							@RequestParam(name="currentPage", defaultValue="1") int currentPage,
							@RequestParam(name="rowPerPage", defaultValue="10") int rowPerPage) {
		
		List<Map<String, Object>> list = memberService.getMemberList(currentPage, rowPerPage);
		log.debug("list : " + list);
		int lastPage = memberService.getLastPage(rowPerPage);
		
		model.addAttribute("list", list);
		model.addAttribute("currentPage", currentPage);
		model.addAttribute("rowPerPage", rowPerPage);
		model.addAttribute("lastPage", lastPage);
		
		return "memberList";
	}
}

Member.java (DTO)

package com.gd.homework.dto;

import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;

@Data
@AllArgsConstructor
@NoArgsConstructor
public class Member {

	private int memberNo;
	private String memberId;
	private String memberPw;
	private String memberName;
}

Hobby.java (DTO)

package com.gd.homework.dto;


import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;

@Data
@AllArgsConstructor
@NoArgsConstructor
public class Hobby {

	private int memberNo;
	private String hobbyName;
}

AddMember.java (DTO - member 회원가입용)

package com.gd.homework.dto;

import java.util.List;

import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;

@Data
@AllArgsConstructor
@NoArgsConstructor
public class AddMember {
	
	private String memberId;
	private String memberPw;
	private String memberName;
	
	private List<String> hobbyName;
}

MemberMapper.java (Mapper Interface)

package com.gd.homework.mapper;

import java.util.List;
import java.util.Map;

import org.apache.ibatis.annotations.Mapper;

import com.gd.homework.dto.Member;


@Mapper
public interface MemberMapper {
	
	public List<Map<String, Object>> selectMemberList(Map<String, Integer> map);

	public int insertMember(Member m);
	
	public int selectMemberCount();
}

MemberMapper.xml (Mapper.xml)

<?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.gd.homework.mapper.MemberMapper">
	<select id="selectMemberList" parameterType="int">
		SELECT
			t.memberNo memberNo,
			t.memberName memberName,
			GROUP_CONCAT(t.hobbyName) hobby
		FROM
			(SELECT m.member_no memberNo,
					m.member_id memberId,
					m.member_name memberName,
					h.hobby_name hobbyName
			FROM
				member m
			LEFT JOIN
				hobby h
			ON
				m.member_no = h.member_no) t
		GROUP BY
			t.memberNo, t.memberName
		ORDER BY
			memberNO
		LIMIT
			#{startRow}, #{rowPerPage}
	</select>
	
	<insert id="insertMember" parameterType="com.gd.homework.dto.Member">
		<!-- 마지막으로 insert 한 행의 Auto Increment 값을 매핑 -->
		<selectKey resultType="int" keyProperty="memberNo">
			SELECT LAST_INSERT_ID()
		</selectKey>
		INSERT INTO member (
			member_id,
			member_pw,
			member_name
		) VALUES (
			#{memberId},
			#{memberPw},
			#{memberName}
		)
	</insert>
	
	<select id="selectMemberCount" resultType="int">
		SELECT
			COUNT(*)
		FROM
			member
	</select>
</mapper>

HobbyMapper.java (Mapper Interface)

package com.gd.homework.mapper;

import org.apache.ibatis.annotations.Mapper;

import com.gd.homework.dto.Hobby;

@Mapper
public interface HobbyMapper {

	public int insertHobby(Hobby h);
}

HobbyMapper.xml (Mapper.xml)

<?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.gd.homework.mapper.HobbyMapper">
	<insert id="insertHobby" parameterType="com.gd.homework.dto.Hobby">
		INSERT INTO hobby (
			member_no,
			hobby_name
		) VALUES (
			#{memberNo},
			#{hobbyName}
		)
	</insert>
</mapper>

memberList.jsp (View)

<%@ page language="java" contentType="text/html; charset=UTF-8" pageEncoding="UTF-8"%>
<%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core"%>
<%@ taglib prefix="fn" uri="http://java.sun.com/jsp/jstl/functions"%>
<%@ taglib prefix="fmt" uri="http://java.sun.com/jsp/jstl/fmt"%>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>Insert title here</title>
</head>
<body>
	<h1>Member List</h1>
	<table border=1>
		<tr>
			<th>Member No</th>
			<th>Member Name</th>
			<th>Hobby</th>
		</tr>
		<c:forEach var="m" items="${list}">
		<tr>
			<td>${m.memberNo}</td>
			<td>${m.memberName}</td>
			<td>${m.hobby}</td>
		</tr>
		</c:forEach>
	</table>
	<c:if test="${currentPage > 1}">
		<a href="${pageContext.request.contextPath}/memberList?currentPage=${currentPage-1}">이전</a>
	</c:if>
	
	<c:if test="${currentPage < lastPage}">
		<a href="${pageContext.request.contextPath}/memberList?currentPage=${currentPage+1}">다음</a>
	</c:if>
</body>
</html>

memberList.jsp 출력결과

signUpMember.jsp (View)

<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>Sign Up Member</title>
</head>
<body>
	<h1>회원가입</h1>
	<form method="post" action="${pageContext.request.contextPath}/signUpMember">
		<div>
			Member ID : 
			<input type="text" name="memberId">
		</div>
		<div>
			Member PW : 
			<input type="text" name="memberPw">
		</div>
		<div>
			Member Name : 
			<input type="text" name="memberName">
		</div>
		<div>
			<input type="checkbox" name="hobby" value="football">축구
			<input type="checkbox" name="hobby" value="baseball">야구
			<input type="checkbox" name="hobby" value="basketball">농구
		</div>
		
		<button type="submit">회원가입</button>
	</form>
</body>
</html>

signUpMember.jsp 출력결과