본문 바로가기
Spring

Spring #4 : 마이바티스 - 회원 최종(230118)

by haheehee 2023. 1. 18.

 

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE configuration
	PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
	"http://mybatis.org/dtd/mybatis-3-config.dtd">
	
<configuration>
	<typeAliases>
		<typeAlias type="com.spring.ex01.MemberVO" alias="memberVO" />
	</typeAliases>
	
	<environments default="development">
		<environment id="development">
			<transactionManager type="JDBC" />
			<dataSource type="POOLED">
				<property name="driver" value="org.mariadb.jdbc.Driver" />
				<property name="url" value="jdbc:mariadb://localhost:3306/membertable" />
				<property name="username" value="root" />
				<property name="password" value="1234" />
			</dataSource>
		</environment>
	</environments>
	
	<mappers>
		<mapper resource="mybatis/mappers/member.xml" />
	</mappers>
</configuration>

SqlMapConfig.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="mapper.member">
	<resultMap id="memResult" type="memberVO">
		<result property="id" column="id" />
		<result property="pwd" column="pwd" />
		<result property="name" column="name" />
		<result property="email" column="email" />
		<result property="joinDate" column="joinDate" />
	</resultMap>

	<!-- 추가 
	<resultMap id="memResult" type="java.util.HashMap">
		<result property="id" column="id" />
		<result property="pwd" column="pwd" />
		<result property="name" column="name" />
		<result property="email" column="email" />
		<result property="joinDate" column="joinDate" />
	</resultMap>-->
	
	<select id="selectAllMemberList" resultMap="memResult">
	<![CDATA[ 
		select * from t_member order by joinDate desc
	]]>
	</select>
	
	<select id="selectName" resultType="String">
		<![CDATA[ 
			select name from t_member
			where id = 'kongdol'
		]]>
	</select>
	<select id="selectPwd" resultType="int">
		<![CDATA[ 
			select pwd from t_member
			where id = 'kongdol'
		]]>
	</select>
	
	<select id="selectMemberById" resultType="memberVO" parameterType="String">
		<![CDATA[ 
			select * from t_member
			where id=#{id}
		]]>
	</select>
	<select id="selectMemberByPwd" resultMap="memResult" parameterType="int">
		<![CDATA[ 
			select * from t_member
			where pwd=#{pwd}
		]]>
	</select>
	
	<insert id="insertMember" parameterType="memberVO">
		<![CDATA[ 
			insert into t_member(id,pwd,name,email)
			values(#{id},#{pwd},#{name},#{email})
		]]>
	</insert>
	<insert id="insertMember2" parameterType="java.util.Map">
		<![CDATA[ 
			insert into t_member(id,pwd,name,email)
			values(#{id},#{pwd},#{name},#{email})
		]]>
	</insert>
	
	<update id="updateMember" parameterType="memberVO">
		<![CDATA[ 
			update t_member
			set pwd=#{pwd}, name=#{name}, email=#{email}
			where id=#{id}
		]]>
	</update>
	
	<delete id="deleteMember" parameterType="String">
		<![CDATA[ 
			delete from t_member
			where id=#{id}
		]]>
	</delete>
	
	<!-- 동적 SQL문 -->
	<select id="searchMember" parameterType="memberVO" resultMap="memResult">
		<![CDATA[ 
			select * from t_member
		]]>
		<!--<where>
			<if test=" name != '' and name != null">
				name=#{name}
			</if>
			<if test=" email != '' and email != null">
				and email=#{email}
			</if>
		</where>-->
		<where>
			<choose>
				<when test="name != '' and name != null and email != '' and email != null">
					name=#{name} and email=#{email}
				</when>
				<when test=" name != '' and name != null">
					name=#{name}
				</when>
				<when test=" email != '' and email != null">
					email=#{email}
				</when>
			</choose>
		</where>
		order by joinDate desc
	</select>
	
	<select id="foreachSelect" resultMap="memResult" parameterType="java.util.Map">
		<![CDATA[ 
			select * from t_member
		]]>
		where name in
		<foreach item="item" collection="list" open="(" separator="," close=")">
			#{item}
		</foreach>
		order by joinDate desc
	</select>
	
	<insert id="foreachInsert" parameterType="java.util.Map"> 
			insert INTO t_member(id, pwd, name, email)
			VALUES
			<foreach item="item" collection="list" index="index" separator=",">
			(#{item.id}, #{item.pwd}, #{item.name}, #{item.email})
		</foreach>
	</insert>
	
	<!-- like 검색 -->
	<select id="selectLike" resultMap="memResult" parameterType="String">
		<![CDATA[ 
			select * from t_member
			where
			name like '%' || #{name} || '%'
		]]>
	</select>
	
</mapper>

member.xml

 

package com.spring.ex04;

import java.io.IOException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import javax.servlet.RequestDispatcher;
import javax.servlet.ServletConfig;
import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import com.spring.ex01.MemberVO;

@WebServlet("/mem4.do")
public class MemberServlet extends HttpServlet {	
	public void init(ServletConfig config) throws ServletException {
		System.out.println("init 호출");
	}
	public void destroy() {
		System.out.println("destroy 호출");		
	}

	protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
		doHandle(request, response);
	}
	protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
		doHandle(request, response);
	}
	
	private void doHandle(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
		request.setCharacterEncoding("utf-8");
		response.setContentType("text/html;charset=utf-8");
		MemberDAO dao = new MemberDAO();
		MemberVO memberVO = new MemberVO();
		String action = request.getParameter("action");
		String nextPage = "";
		if(action==null || action.equals("listMembers")) {
			List membersList = dao.selectAllMemberList();
			request.setAttribute("membersList", membersList);
			nextPage = "test03/listMembers.jsp";
		} else if(action.equals("selectMemberById")) {
			String id = request.getParameter("value");
			memberVO = dao.selectMemberById(id);
			request.setAttribute("member", memberVO);
			nextPage = "test03/memberInfo.jsp";
		} else if(action.equals("selectMemberByPwd")) {
			int pwd = Integer.parseInt(request.getParameter("value"));
			List<MemberVO> membersList = dao.selectMemberByPwd(pwd);
			request.setAttribute("membersList", membersList);
			nextPage = "test03/listMembers.jsp";
		} else if(action.equals("insertMember")) {
			String id = request.getParameter("id");
			String pwd = request.getParameter("pwd");
			String name = request.getParameter("name");
			String email = request.getParameter("email");
			memberVO.setId(id);
			memberVO.setPwd(pwd);
			memberVO.setName(name);
			memberVO.setEmail(email);
			dao.insertMember(memberVO);
			request.setAttribute("msg",  "insertMember");
			nextPage = "/mem4.do?action=listMembers";
		} else if(action.equals("insertMember2")) {
			String id = request.getParameter("id");
			String pwd = request.getParameter("pwd");
			String name = request.getParameter("name");
			String email = request.getParameter("email");
			Map<String, String> memberMap = new HashMap<String, String>();
			memberMap.put("id", id);
			memberMap.put("pwd", pwd);
			memberMap.put("name", name);
			memberMap.put("email", email);
			dao.insertMember2(memberMap);
			request.setAttribute("msg",  "insertMember");
			nextPage = "/mem4.do?action=listMembers";
		} else if(action.equals("updateMember")) {
			String id = request.getParameter("id");
			String pwd = request.getParameter("pwd");
			String name = request.getParameter("name");
			String email = request.getParameter("email");
			memberVO.setId(id);
			memberVO.setPwd(pwd);
			memberVO.setName(name);
			memberVO.setEmail(email);
			dao.updateMember(memberVO);
			request.setAttribute("msg",  "updateMember");
			nextPage = "/mem4.do?action=listMembers";
		} else if (action.equals("modMember")) { /////////////////////////////
			String id = request.getParameter("id");
			MemberVO memInfo = dao.selectMemberById(id);
			request.setAttribute("memInfo", memInfo);
			System.out.println(memInfo);
			nextPage = "test03/modMember.jsp";
		} else if(action.equals("deleteMember")) {
			String id = request.getParameter("id");
			dao.deleteMember(id);
			request.setAttribute("msg",  "deleteMember");
			nextPage = "/mem4.do?action=listMembers";
		} else if(action.equals("searchMember")) {
			String name = request.getParameter("name");
			String email = request.getParameter("email");
			memberVO.setName(name);
			memberVO.setEmail(email);
			List membersList = dao.searchMember(memberVO);
			request.setAttribute("membersList", membersList);
			nextPage = "test03/listMembers.jsp";
		} else if(action.equals("foreachSelect")) {
			List<String> nameList = new ArrayList<String>();
			nameList.add("김자바");
			nameList.add("김디비");
			nameList.add("김제이");
			List membersList = dao.foreachSelect(nameList);
			request.setAttribute("membersList", membersList);
			nextPage = "test03/listMembers.jsp";
		} else if(action.equals("foreachInsert")) {
			List<MemberVO> memList = new ArrayList();
			memList.add(new MemberVO("m1", "1234", "박길동", "m1@test.com"));
			memList.add(new MemberVO("m2", "1234", "정길동", "m2@test.com"));
			memList.add(new MemberVO("m3", "1234", "최길동", "m3@test.com"));
			int result = dao.foreachInsert(memList);
			nextPage="/mem4.do?action=listMembers";
		} else if(action.equals("selectLike")) {
			String name="콩";
			List<MemberVO> membersList = dao.selectLike(name);
			request.setAttribute("membersList", membersList);
			nextPage="test03/listMembers.jsp";
		}
		
		RequestDispatcher dispatch = request.getRequestDispatcher(nextPage);
		dispatch.forward(request, response);
	}
}

MemberServlet.java

 

package com.spring.ex04;

import java.io.Reader;
import java.util.List;
import java.util.Map;

import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;

import com.spring.ex01.MemberVO;

public class MemberDAO {
	public static SqlSessionFactory sqlMapper = null;
	private static SqlSessionFactory getInstance() {
		if(sqlMapper == null) {
			try {
				String resource = "mybatis/SqlMapConfig.xml";
				Reader reader = Resources.getResourceAsReader(resource);
				sqlMapper = new SqlSessionFactoryBuilder().build(reader);
				reader.close();
			} catch (Exception e) {
				e.printStackTrace();
			}
		}
		return sqlMapper;
	}
	
	public String selectName() {
		sqlMapper = getInstance();
		SqlSession session = sqlMapper.openSession();
		String name = session.selectOne("mapper.member.selectName");
		return name;
	}
	
	public int selectPwd() {
		sqlMapper = getInstance();
		SqlSession session = sqlMapper.openSession();
		int pwd = session.selectOne("mapper.member.selectPwd");
		return pwd;
	}

	public List<MemberVO> selectAllMemberList() {
		sqlMapper = getInstance();
		SqlSession session = sqlMapper.openSession();
		List<MemberVO> memlist = null;
		memlist = session.selectList("mapper.member.selectAllMemberList");
		return memlist;
	}

	public MemberVO selectMemberById(String id) {
		sqlMapper = getInstance();
		SqlSession session = sqlMapper.openSession();
		MemberVO memberVO = session.selectOne("mapper.member.selectMemberById", id);
		System.out.print("선택한 회원 : ");
		System.out.println(memberVO.getId() + ", " + memberVO.getPwd() + ", " + memberVO.getName() + ", " + memberVO.getEmail());
		return memberVO;
	}

	public List<MemberVO> selectMemberByPwd(int pwd) {
		sqlMapper = getInstance();
		SqlSession session = sqlMapper.openSession();
		List<MemberVO> membersList = null;
		membersList = session.selectList("mapper.member.selectMemberByPwd", pwd);
		return membersList;
	}

	public int insertMember(MemberVO memberVO) {
		sqlMapper = getInstance();
		SqlSession session = sqlMapper.openSession();
		int result = 0;
		result = session.insert("mapper.member.insertMember", memberVO);
		session.commit();
		return result;
	}

	public int insertMember2(Map<String, String> memberMap) {
		sqlMapper = getInstance();
		SqlSession session = sqlMapper.openSession();
		int result = session.insert("mapper.member.insertMember2", memberMap);
		session.commit();
		return result;
	}

	public int updateMember(MemberVO memberVO) {
		sqlMapper = getInstance();
		SqlSession session = sqlMapper.openSession();
		int result = session.update("mapper.member.updateMember", memberVO);
		session.commit();
		return result;
	}

	public int deleteMember(String id) {
		sqlMapper = getInstance();
		SqlSession session = sqlMapper.openSession();
		int result = session.delete("mapper.member.deleteMember", id);
		session.commit();
		return result;
	}

	public List searchMember(MemberVO memberVO) {
		sqlMapper = getInstance();
		SqlSession session = sqlMapper.openSession();
		List list = session.selectList("mapper.member.searchMember", memberVO);
		return list;
	}

	public List foreachSelect(List<String> nameList) {
		sqlMapper = getInstance();
		SqlSession session = sqlMapper.openSession();
		List list = session.selectList("mapper.member.foreachSelect", nameList);
		return list;
	}

	public int foreachInsert(List<MemberVO> memList) {
		sqlMapper = getInstance();
		SqlSession session = sqlMapper.openSession();
		int result = session.insert("mapper.member.foreachInsert", memList);
		session.commit();
		return result;
	}

	public List<MemberVO> selectLike(String name) {
		sqlMapper = getInstance();
		SqlSession session = sqlMapper.openSession();
		List list = session.selectList("mapper.member.selectLike", name);
		return list;
	}

}

MemberDAO.java

 

<%@ page language="java" contentType="text/html; charset=utf-8" pageEncoding="utf-8" import="java.util.*,com.spring.ex01.*" isELIgnored="false"%>
<%@ taglib prefix="fmt" uri="http://java.sun.com/jsp/jstl/fmt"%>
<%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core"%>
<c:set var="contextPath" value="${pageContext.request.contextPath}" />
<% request.setCharacterEncoding("utf-8"); %>
<!DOCTYPE html>
<html>
<head>
	<c:choose>
			<c:when test='${msg == "insertMember"}'>
				<script>
					window.onload = function() {
						alert("회원을 등록했습니다. ");
					}
				</script>
			</c:when>
			<c:when test='${msg == "updateMember"}'>
				<script>
					window.onload = function() {
						alert("회원 정보를 수정했습니다. ");
					}
				</script>
			</c:when>
			<c:when test='${msg == "deleteMember"}'>
				<script>
					window.onload = function() {
						alert("회원 정보를 삭제했습니다. ");
					}
				</script>
			</c:when>
		</c:choose>
<meta charset="utf-8">
<title>회원정보 출력창</title>
<style type="text/css">
.cls1 {
	font-size: 40px;
	text-align: center;
}

.cls2 {
	text-align: center;
}
</style>
</head>
<body>
	<table align="center" width="80%" border="1">
		<tr align="center" bgcolor="lightblue">
			<td>ID</td>
			<td>PASSWORD</td>
			<td>NAME</td>
			<td>EMAIL</td>
			<td>JOINDATE</td>
			<td>수정</td>
			<td>삭제</td>
		</tr>
		<c:choose>
			<c:when test="${membersList == null}">
				<tr align="center">
					<td colspan="7"><b>등록된 회원이 없습니다.</b></td>
				</tr>
			</c:when>
			<c:when test="${membersList != null}">
				<c:forEach var="member" items="${membersList}">
					<tr align="center">
						<td>${member.id}</td>
						<td>${member.pwd}</td>
						<td>${member.name}</td>
						<td>${member.email}</td>
						<td>${member.joinDate}</td>
						<td><a href="${contextPath}/mem4.do?action=modMember&id=${member.id}">수정</a></td> <!-- updateMember&id=${member.id} -->
						<td><a href="${contextPath}/mem4.do?action=deleteMember&id=${member.id}">삭제</a></td>
					</tr>
				</c:forEach>
			</c:when>
		</c:choose>
		
	</table>
	<table align="center" width="40%" border="0">
		<tr style="text-align: center">
			<td height="50px"><a href="${contextPath}/test03/memberForm.jsp"><b>회원가입</b></a></td>
			<td height="50px"><a href="${contextPath}/test03/searchMember.jsp"><b>회원검색</b></a></td>
			<td height="50px"><a href="${contextPath}/mem4.do"><b>새로고침</b></a></td>
		</tr>
	</table>
</body>
</html>

listMembers.jsp

 

<%@ page language="java" contentType="text/html; charset=utf-8" pageEncoding="utf-8" isELIgnored="false"%>
<%@ taglib prefix="fmt" uri="http://java.sun.com/jsp/jstl/fmt"%>
<%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core"%>
<c:set var="contextPath" value="${pageContext.request.contextPath}" />
<% request.setCharacterEncoding("utf-8"); %>
<!DOCTYPE html>
<html>
<head>
<meta charset="utf-8">
<title>회원가입 창</title>
</head>
<body>
	<form method="post" action="${contextPath}/mem4.do?action=insertMember2">
		<h1 style="text-align: center">회원가입창</h1>
		<table align="center">
			<tr>
				<td width="200"><p align="right">아이디</p></td>
				<td width="400"><input type="text" name="id"></td>
			</tr>
			<tr>
				<td width="200"><p align="right">비밀번호</p></td>
				<td width="400"><input type="password" name="pwd"></td>
			</tr>
			<tr>
				<td width="200"><p align="right">이름</p>	</td>
				<td width="400"><input type="text" name="name"></td>
			</tr>
			<tr>
				<td width="200"><p align="right">이메일</p></td>
				<td width="400"><input type="text" name="email"></td>
			</tr>
			<tr>
				<td width="200"></td>
				<td width="80">
					<input type="submit" value="가입하기">
					<input type="reset" value="다시입력">
				</td>
			</tr>
		</table>
	</form>
</body>
</html>

memberForm.jsp

 

<%@ page language="java" contentType="text/html; charset=utf-8" pageEncoding="utf-8" isELIgnored="false"%>
<%@ taglib prefix="fmt" uri="http://java.sun.com/jsp/jstl/fmt"%>
<%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core"%>
<c:set var="contextPath" value="${pageContext.request.contextPath}" />
<% request.setCharacterEncoding("utf-8"); %>
<!DOCTYPE html>
<html>
<head>
<meta charset="utf-8">
<title>회원정보 수정창</title>
</head>
<body>
	<form method="post" action="${contextPath}/mem4.do?action=updateMember&id=${memInfo.id}">
		<h1 style="text-align: center">회원 정보 수정창</h1>
		<table align="center">
			<tr>
				<td width="200"><p align="right">아이디</p></td>
				<td width="400"><input type="text" name="id" value="${memInfo.id}" disabled="disabled"></td>
			</tr>
			<tr>
				<td width="200"><p align="right">비밀번호</p></td>
				<td width="400"><input type="password" name="pwd" value="${memInfo.pwd}"></td>
			</tr>
			<tr>
				<td width="200"><p align="right">이름</p>	</td>
				<td width="400"><input type="text" name="name" value="${memInfo.name}"></td>
			</tr>
			<tr>
				<td width="200"><p align="right">이메일</p></td>
				<td width="400"><input type="text" name="email" value="${memInfo.email}"></td>
			</tr>
			<tr>
				<td width="200"></td>
				<td width="80">
					<input type="submit" value="수정하기">
					<input type="reset" value="다시입력">
				</td>
			</tr>
		</table>
	</form>
</body>
</html>

modMember.jsp

 

<%@ page language="java" contentType="text/html; charset=utf-8" pageEncoding="utf-8" import="java.util.*,com.spring.ex01.*" isELIgnored="false"%>
<%@ taglib prefix="fmt" uri="http://java.sun.com/jsp/jstl/fmt"%>
<%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core"%>
<c:set var="contextPath" value="${pageContext.request.contextPath}" />
<% request.setCharacterEncoding("utf-8"); %>
<!DOCTYPE html>
<html>
<head>
<meta charset="utf-8">
<title>회원 검색</title>
<style type="text/css">
.cls1 {
	font-size: 40px;
	text-align: center;
}

.cls2 {
	text-align: center;
}
</style>
</head>
<body>
	<h2 style="text-align: center">회원검색</h2>
	<form action="${contextPath}/mem4.do">
		<table align="center">
			<tr>
				<td width="300"></td>
				<td width="400"><input type="hidden" name="action" value="searchMember" /></td>
			</tr>
			<tr>
				<td width="300"><p align="right">이름</p></td>
				<td width="400"><input type="text" name="name" /></td>
			</tr>
			<tr>
				<td width="300"><p align="right">이메일</p></td>
				<td width="400"><input type="text" name="email" /></td>
			</tr>
			<tr>
				<td width="300"></td>
				<td width="400"><input type="submit" value="검색" />
					<input type="reset" value="다시입력" /></td>
			</tr>
		</table>
		
	</form>
</body>
</html>

searchMember.jsp

 

-- 결과 --

저작자표시 비영리 변경금지

'Spring' 카테고리의 다른 글

Spring #6 : 스프링-마이바티스 연동(230119)  (0) 2023.01.20
Spring #5 : 마이바티스 - 동적 SQL문(230118)  (0) 2023.01.18
Spring #3 : 마이바티스 프레임워크 - 회원 출력/검색/추가/수정/삭제 (230117)  (0) 2023.01.18
Spring #2 : 스프링 MVC (230116)  (0) 2023.01.17
Spring : 스프링 AOP (230116)xxx  (0) 2023.01.16

관련글

댓글

티스토리툴바