03_Spring_Mybatis_member
2022. 10. 6. 18:04ㆍ카테고리 없음
index
<%@ page language="java" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8"%>
<%@ taglib uri="http://java.sun.com/jsp/jstl/core" prefix="c"%>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>Insert title here</title>
</head>
<body>
<h1>03_Spring_Mybatis</h1>
<c:choose>
<c:when test="${empty sessionScope.m }">
<form action="/login.do" method="post">
<fieldset>
<legend>로그인</legend>
아이디 : <input type="text" name="memberId"><br>
비밀번호 : <input type="password" name="memberPw"><br>
<input type="submit" value="로그인">
</fieldset>
</form>
<a href="/joinFrm.do">회원가입</a>
<form action="/searchMemberId.do">
아이디 : <input type="text" name="memberId">
<input type="submit" value="조회">
</form>
</c:when>
<c:otherwise>
<!-- sessionScope을 사용 안해도 출력은 되지만
구분이 되지 않기 때문에 세션인지 아닌지 구분하기위해 적어놓는다. -->
<h2>[${sessionScope.m.memberName }]</h2>
<h3><a href="/boardList.do?reqPage=1">게시판</a></h3>
<h3><a href="/selectAllMember.do">전체회원조회</a></h3>
<h3><a href="/logout.do">로그아웃</a></h3>
<h3><a href="/mypage.do">마이페이지</a></h3>
<h3><a href="/deleteMember.do">회원탈퇴</a></h3>
<form action="/searchMemberName.do">
이름:<input type="text" name="memberName">
<input type="submit" value="조회">
</form>
<form action="/searchMember1.do" method="post">
<select name="type">
<option value="id">아이디</option>
<option value="name">이름</option>
</select>
<input type="text" name="keyword">
<input type="submit" name="검색">
</form>
<hr>
<p>아이디만 입력하고 검색하는 경우 아이디로 조회,
이름만 입력하고 검색하는경우 이름으로조회,
둘다입력하고 검색하는 경우 두개 and로 조회
<h3>아이디 or 이름으로 검색</h3>
<form action="/searchMember2.do">
아이디 : <input type="text" name="memberId"><br>
이름 : <input type="text" name="memberName"><br>
<input type="submit" value="검색">
</form>
<h3><a href="/idList.do">전체회원 아이디 목록</a></h3>
<h3><a href="/searchMember4.do">회원 조회4</a></h3>
</c:otherwise>
</c:choose>
</body>
</html>컨트롤러
package kr.or.member.controller;
import java.util.ArrayList;
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.RequestMapping;
import org.springframework.web.bind.annotation.SessionAttribute;
import kr.or.member.model.service.MemberService;
import kr.or.member.model.vo.Member;
@Controller
public class MemberController {
@Autowired
private MemberService service;
@RequestMapping(value = "/login.do")
public String login(Member member, HttpSession session) {
Member m = service.selectOneMember(member);
if (m != null) {
session.setAttribute("m", m);
}
return "redirect:/";
}
@RequestMapping(value = "/selectAllMember.do")
public String selectAllMember(Model model) {
ArrayList<Member> list = service.selectAllMember();
model.addAttribute("list", list);
return "member/memberList";
}
@RequestMapping(value = "/joinFrm.do")
public String joinFrm() {
return "member/joinFrm";
}
@RequestMapping(value = "/logout.do")
public String logout(HttpSession session) {
session.invalidate();
return "redirect:/";
}
@RequestMapping(value = "/join.do")
public String join(Member m) {
int result = service.insertMember(m);
if (result > 0) {
return "redirect:/";
} else {
return "member/joinFrm";
}
}
@RequestMapping(value = "/searchMemberId.do")
public String searchMemberId(Member member, Model model) {
Member m = service.selectOneMember(member);
if (m != null) {
model.addAttribute("m", m);
return "member/searchMember";
} else {
return "redirect:/";
}
}
@RequestMapping(value = "/mypage.do")
public String mypage() {
return "member/mypage";
}
@RequestMapping(value = "/updateMember.do")
public String updateMember(Member m, HttpSession session) {
Member member = service.updateMember(m);
if (member != null) {
session.setAttribute("m", member);
return "redirect:/mypage.do";
} else {
return "redirect:/";
}
}
@RequestMapping(value = "/deleteMember.do")
public String deleteMember(@SessionAttribute Member m) {
int result = service.deleteMember(m.getMemberNo());
if (result > 0) {
return "redirect:/logout.do";
} else {
return "redirect:/";
}
}
@RequestMapping(value = "/searchMemberName.do")
public String searchMemberName(String memberName, Model model) {
ArrayList<Member> searchList = service.searchMemberName(memberName);
model.addAttribute("list", searchList);
return "member/memberList";
}
@RequestMapping(value = "/searchMember1.do")
public String searchMember(String type, String keyword, Model model) {
ArrayList<Member> list = service.searchMember1(type, keyword);
model.addAttribute("list", list);
return "member/memberList";
}
@RequestMapping(value = "/searchMember2.do")
public String searchMember2(Member m, Model model) {
ArrayList<Member> list = service.searchMember2(m);
model.addAttribute("list", list);
return "member/memberList";
}
@RequestMapping(value = "/idList.do")
public String idList(Model model) {
ArrayList<String> list = service.idList();
model.addAttribute("list", list);
return "member/idList";
}
@RequestMapping(value = "/searchMember3.do")
public String searchMember3(String[] memberId, Model model) {
ArrayList<Member> list = service.searchMember3(memberId);
model.addAttribute("list", list);
return "member/memberList";
}
@RequestMapping(value = "/searchMember4.do")
public String searchMember4(Model model) {
ArrayList<Member> list = service.searchMember4();
model.addAttribute("list", list);
return "member/memberList";
}
}서비스
package kr.or.member.model.service;
import java.util.ArrayList;
import java.util.HashMap;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import kr.or.member.model.dao.MemberDao;
import kr.or.member.model.vo.Member;
@Service
public class MemberService {
@Autowired
private MemberDao dao;
public Member selectOneMember(Member member) {
Member m = dao.selectOneMember(member);
return m;
}
public ArrayList<Member> selectAllMember() {
// TODO Auto-generated method stub
return dao.selectAllMember();
}
public int insertMember(Member m) {
return dao.insertMember(m);
}
public Member selectOneMember(String memberId) {
return dao.selectOneMember(memberId);
}
public Member updateMember(Member m) {
int result = dao.updateMember(m);
if (result > 0) {
Member member = dao.selectOneMember(m.getMemberId());
return member;
} else {
return null;
}
}
public int deleteMember(int memberNo) {
return dao.deleteMember(memberNo);
}
public ArrayList<Member> searchMemberName(String memberName) {
return dao.searchMemberName(memberName);
}
public ArrayList<Member> searchMember1(String type, String keyword) {
HashMap<String, Object> map = new HashMap<String, Object>();
// map 안에 타입이라는 키값에다가 타입값을 넣고
// 키워드도 마찬가지
map.put("type", type);
map.put("keyword", keyword);
// 묶은 맵으로 한번에 전달
ArrayList<Member> list = dao.searchMember1(map);
return list;
}
public ArrayList<Member> searchMember2(Member m) {
// TODO Auto-generated method stub
return dao.searchMember2(m);
}
public ArrayList<String> idList() {
return dao.idList();
}
public ArrayList<Member> searchMember3(String[] memberId) {
// TODO Auto-generated method stub
return dao.searchMember3(memberId);
}
public ArrayList<Member> searchMember4() {
// TODO Auto-generated method stub
return dao.searchMember4();
}
}dao
package kr.or.member.model.dao;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import org.mybatis.spring.SqlSessionTemplate;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Repository;
import kr.or.member.model.vo.Member;
@Repository
public class MemberDao {
//mybatis써서 바뀔것
@Autowired
private SqlSessionTemplate sqlSession;
public Member selectOneMember(Member member) {
//member 는 namespace 가 member인곳을 찾아라
//selectOneMember 는 아이디가 selectOneMember인것을 찾아라
//,member 는 쿼리문에 필요한 데이터를 받아온것이다.
Member m = sqlSession.selectOne("member.selectOneMember",member);
return m;
}
public ArrayList<Member> selectAllMember() {
//namespace 가 member인 곳에서 id가 selectAllMember인곳을 찾아라
List list = sqlSession.selectList("member.selectAllMember");
return (ArrayList<Member>)list;
}
public int insertMember(Member m) {
int result = sqlSession.insert("member.insertMember",m);
return result;
}
public Member selectOneMember(String memberId) {
Member m = sqlSession.selectOne("member.selectOneMemberId", memberId);
return m;
}
public int updateMember(Member m) {
int result = sqlSession.update("member.updateMember",m);
return result;
}
public int deleteMember(int memberNo) {
return sqlSession.delete("member.deleteMember",memberNo);
}
public ArrayList<Member> searchMemberName(String memberName) {
List list = sqlSession.selectList("member.searchMemberName",memberName);
return (ArrayList<Member>)list;
}
public ArrayList<Member> searchMember1(HashMap<String, Object> map) {
List list = sqlSession.selectList("member.searchMember1",map);
return (ArrayList<Member>)list;
}
public ArrayList<Member> searchMember2(Member m) {
List list = sqlSession.selectList("member.searchMember2",m);
return (ArrayList<Member>)list;
}
public ArrayList<String> idList() {
List list = sqlSession.selectList("member.idList");
return (ArrayList<String>)list;
}
public ArrayList<Member> searchMember3(String[] memberId) {
List list = sqlSession.selectList("member.searchMember3",memberId);
return (ArrayList<Member>)list;
}
public ArrayList<Member> searchMember4() {
List list = sqlSession.selectList("member.searchMember4");
return (ArrayList<Member>)list;
}
}memberSql.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="member">
<select id="selectOneMember" parameterType="m" resultType="m">
<!-- 위치홀더 자리에 키값을 넣으면 바로 가져온다. -->
<!-- vo랑 변수명이 다른 개체들은 별칭으로 vo랑이름을 맞춰준다. -->
<!-- resultType="m" : 돌려주는 타입 -->
select
member_no as memberNo,
member_id as memberId,
member_pw as memberPw,
member_name as memberName,
phone,
email
from member_tbl
where member_id=#{memberId}
<if test="memberPw !=null">
and member_pw=#{memberPw}
</if>
</select>
<!-- resultType 에 리스트에 들어갈 객체를 넣는것 -->
<select id="selectAllMember" resultType="m">
select
member_no as memberNo,
member_id as memberId,
member_pw as memberPw,
member_name as memberName,
phone,
email
from member_tbl
</select>
<!-- insert나 update같이 result 타입이 int라는 사실을 알고있기때문에 지정하지 않는다 -->
<insert id="insertMember" parameterType="m">
insert into member_tbl values (member_seq.nextval,#{memberId},#{memberPw},#{memberName},#{phone},#{email})
</insert>
<select id="selectOneMemberId" parameterType="String" resultType="m">
select
member_no as memberNo,
member_id as memberId,
member_pw as memberPw,
member_name as memberName,
phone,
email
<!-- parameter : 단일값 하나가 넘어온 경우 사용 -->
from member_tbl where member_id=#{_parameter}
</select>
<update id="updateMember" parameterType="m">
update member_tbl set
member_pw = #{memberPw},
phone = #{phone},
email = #{email}
where member_id=#{memberId}
</update>
<delete id="deleteMember" parameterType="int">
delete from member_tbl where member_no=#{_parameter}
</delete>
<select id="searchMemberName" parameterType="String" resultType="m">
select
member_no as memberNo,
member_id as memberId,
member_pw as memberPw,
member_name as memberName,
phone,
email
from member_tbl where member_name like '%'||#{_parameter}||'%'
</select>
<!-- list 안에 멤버타입이 들어갈거라 결과타입은 m 이다 -->
<select id="searchMember1" parameterType="map" resultType="m">
select
member_no as memberNo,
member_id as memberId,
member_pw as memberPw,
member_name as memberName,
phone,
email
from member_tbl
<choose>
<when test="type.equals('id')">
where member_id = #{keyword}
</when>
<when test="type.equals('name')">
where member_name like '%'||#{keyword}||'%'
</when>
</choose>
</select>
<select id="searchMember2" parameterType="m" resultType="m">
select
member_no as memberNo,
member_id as memberId,
member_pw as memberPw,
member_name as memberName,
phone,
email
from member_tbl
<!-- trim안이 전부 false 면 작동을 안함 -->
<!-- trim prefix="where" : 트림안에 있는게 ture가 나와서 조건이 붙을때 앞에 where를 붙여줘서 조건을 맞춰줌 -->
<!-- prefixOverrides="and|or : trim 안에 있는것이 진행이 될때 구문이 and나 or로 시작시 그 구문을 지우고 작동하라는 의미 -->
<trim prefix="where" prefixOverrides="and|or">
<if test="!memberId.equals('')">
member_id like '%'||#{memberId}||'%'
</if>
<if test="!memberName.equals('')">
and member_name like '%'||#{memberName}||'%'
</if>
</trim>
<!--
<choose>
<when test="!memberId.equals('') and memberName.equals('')">
where member_id like '%'||#{memberId}||'%'
</when>
<when test="memberId.equals('') and !memberName.equals('')">
where member_name like '%'||#{memberName}||'%'
</when>
<when test="!memberId.equals('') and !memberName.equals('')">
where member_id like '%'||#{memberId}||'%'
and member_name like '%'||#{memberName}||'%'
</when>
</choose>
-->
</select>
<select id="idList" resultType="string">
select
member_id
from member_tbl
</select>
<select id="searchMember3" resultType="m" parameterType="map">
select
member_no as memberNo,
member_id as memberId,
member_pw as memberPw,
member_name as memberName,
phone,
email
from member_tbl
where member_id in
<!-- collection : 반복할 객체 -->
<foreach collection="array" item="memberId" open="(" close=")" separator=",">
#{memberId}
</foreach>
</select>
<select id="searchMember4" resultType="m">
select
member_no as memberNo,
member_id as memberId,
member_pw as memberPw,
member_name as memberName,
phone,
email
from member_tbl
<!-- 부등호는 <![CDATA[ < ]]> 라는식으로 CDATA를 사용해주어야한다.
이때 부등호만 감싸줘도되고 쿼리문 전체를 감싸줘도 된다.
단 쿼리문 전체를 감쌀씨 동적쿼리를 사용할수가 없다 -->
where member_no <![CDATA[ < ]]> 60
</select>
</mapper>mybatis-config.xml
<?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>
<settings>
<!-- null값이 발생하면 빈칸 이 아닌 null로 처리 -->
<setting name="jdbcTypeForNull" value="NULL"/>
</settings>
<typeAliases>
<typeAlias type="kr.or.member.model.vo.Member" alias="m"/>
<typeAlias type="kr.or.board.model.vo.Board" alias="b"/>
<typeAlias type="kr.or.board.model.vo.FileVO" alias="f"/>
</typeAliases>
</configuration>
위치홀더가 없어진 대신
위치홀더가 사용될 위치에서는
member_pw = #{memberPw} 형태로 위치홀더에 값을 넣어주던것 처럼 직접적으로 바로 넣어준다.
단 받아와서 넣어주는 값이 한개일경우 member_pw = #{_parameter} 식으로 넣어줄수있다.
result형태로 0,1의 결과값을 리턴받는것이 아닌
vo형태로 리턴 받을시
쿼리문의 DB객체를 as(별칭)을 이용하여 VO와 명을 맞춰주어야한다.
searchMember1.do
아이디/이름 으로검색(index)
<form action="/searchMember1.do" method="post">
<select name="type">
<option value="id">아이디</option>
<option value="name">이름</option>
</select>
<input type="text" name="keyword">
<input type="submit" name="검색">
</form>옵션으로 2가지의 값을 넘겨줄때
Controller
@RequestMapping(value = "/searchMember1.do")
public String searchMember(String type, String keyword, Model model) {
ArrayList<Member> list = service.searchMember1(type, keyword);
model.addAttribute("list", list);
return "member/memberList";
}둘다 문자타입이니 String 형태로 넘겨주고 화면에 출력을 하기 위하여 Model을 사용
검색을 해서 그 문자가 들어간 데이터는 전부 출력 할 것이기 때문에 ArrayList타입으로 받는다.
Service
public ArrayList<Member> searchMember1(String type, String keyword) {
HashMap<String, Object> map = new HashMap<String, Object>();
// map 안에 타입이라는 키값에다가 타입값을 넣고
// 키워드도 마찬가지
map.put("type", type);
map.put("keyword", keyword);
// 묶은 맵으로 한번에 전달
ArrayList<Member> list = dao.searchMember1(map);
return list;
}데이터를 넘겨줄때 한번에 한개씩 넘기는데 type과 keyword라는 데이터를 2개 받았으므로
HashMap 을 이용하여 하나로 묶어서 데이터를 넘겨준다
이때 HashMap 이 아니라 VO를생성해서 넘겨주는 방법도 있다.
DAO
public ArrayList<Member> searchMember1(HashMap<String, Object> map) {
List list = sqlSession.selectList("member.searchMember1",map);
return (ArrayList<Member>)list;
}서비스에서 map으로 묶어서 받아온 데이터를 map으로 전송
전체 출력을 할것이기 때문에 list 타입으로 받아 리턴한다.
map
select id="searchMember1" parameterType="map" resultType="m">
select
member_no as memberNo,
member_id as memberId,
member_pw as memberPw,
member_name as memberName,
phone,
email
from member_tbl
<choose>
<when test="type.equals('id')">
where member_id = #{keyword}
</when>
<when test="type.equals('name')">
where member_name like '%'||#{keyword}||'%'
</when>
</choose>
</select>인덱스에서 옵션값이 id랑 일치할시 위쪽 when이
인덱스에서 옵션값이 name이랑 이 일시할시 아래쪽 when이 동작한다.