[국비학원 기록/Spring] 마이바티스 MyBatis Framework 사용, 회원 정보 조회, 수정, 삭제 예제
my code archive
article thumbnail
반응형
MyBatis

 

1. MyBatis 개요

 

1)자바 오브젝트와 SQL문 사이의 자동 Mapping 기능을 지원하는 ORM 프레임워크

 

2)SQL을 별도 파일로 분리해서 관리

--> SQL문과 자바 코드의 분리

 

3)SQL문을 그대로 이용하면서 도메인 객체나 VO 객체 중심으로 개발 가능

 

4)퍼시턴트 프레임워크

 

5)XML형태로 서술된 JDBC 코드라고 할 수 있을 정도로 JDBC 모든 기능을 제공함

 

2. MyBatis 주요 컴포넌트

 

1)MyBatis 설정 파일(SqlMapConfig.xml)

-DB 접속 주소 정보, Mapping 파일 경로 등 고정된 환경 정보 설정

 

2)Mapping 파일

 

3)SqlSessionFactoryBuilder

 

4)SqlSessionFactory

 

5)SqlSession
-핵심적인 역할하는 클래스
-SQL 실행이나 트랜잭션 관리를 실행함

 

3. SqlSession 클래스에서 제공하는 메서드들

1)List selectList(id)
-id에 대한 select문 실행한 후 여러 레코드를 List로 반환함


2)T selectOne(id)
-id에 대한 select문을 실행한 후 지정한 타입으로 한 개의 레코드를 반환함

 

3)int insert(id, Object obj)
-id에 대한 insert문을 실행하면서 obj 객체의 값을 테이블에 추가함

 

4)int update(id, Object obj)
-obj객체의 값을 조건문의 수정 값으로 사용해 id에 대한 update문을 실행함
   

5)int delete(id, Object obj)
-obj객체의 값을 조건문의 조건 값으로 사용해 id에 대한 delete문을 실행함

 

4. MyBatis 마이바티스 조건값 전달 방법

 

1)DAO에서 메서드 호출 시 전달된 조건값은 매개변수 이름으로 SQL문의 조건식에 전달

 

2)SQL문에서 조건문 사용 방법 : #{전달된 매개변수 이름}

 

마이바티스 사용하여 회원 목록 조회 예제

1.SqlMapConfig.xml 작성

<datasSource> 태그 이용해 DB 데이터 소스 설정
<mappers> 태그 이용해 SQL문이 있는 XML 파일 설정

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
<?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="kr.co.ezenac.orm.MemberVO" alias="memberVO"/>
    </typeAliases>
    
    <environments default="development">
        <environment id="development">
            <transactionManager type="JDBC"/>
            <dataSource type="POOLED">
                <property name="driver" value="oracle.jdbc.driver.OracleDriver"/>
                <property name="url" value="jdbc:oracle:thin:@localhost:1521:XE"/>
                <property name="username" value="wdsql"/>
                <property name="password" value="0311"/>
            </dataSource>    
        </environment>
    </environments>
    
    <mappers>
        <mapper resource="mybatis/mappers/member.xml"/>
    </mappers>
</configuration>
cs

2.member.xml 작성

<mapper namespace=""> : member.xml의 네임스페이스 지정
<result property="" column=""> : 레코드 컬럼 이름에 대해 memberVO와 같은 속성값을 지정

<select id="" resultMap=""> : DAO에서 id를 이용해 해당 SQL문을 호출하고 resultMap은 반환되는 레코드를 memResult에 저장할 것이다.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
<?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">                    <!-- member.xml의 네임스페이스 지정 -->
 
    <resultMap type="memberVO" id="memResult">        <!-- SQL문 실행한 후 반환되는 레코드들을 typeAlias 태그에서 지정한 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 type="java.util.HashMap" id="memResult">    조회한 레코드를 지정한 컬럼이름을 key, 값을 value로 지정
        <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> -->
    
    <!-- id : DAO에서 id를 이용해 해당 SQL문을 호출함 -->
    <!-- resultMap : 반환되는 레코드를 memResult에 저장함 -->
    <select id="selectAllMemberList" resultMap="memResult">
        <![CDATA[
            select * from t_member order by joinDate desc
        ]]>
    </select>
cs

3.MemberServlet.java 작성

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
import java.io.IOException;
import java.util.List;
 
import javax.servlet.RequestDispatcher;
import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
 
/**
 * Servlet implementation class MemberServlet
 */
@WebServlet("/mem.do")
public class MemberServlet extends HttpServlet {
    private static final long serialVersionUID = 1L;
 
    /**
     * @see HttpServlet#doGet(HttpServletRequest request, HttpServletResponse response)
     */
    protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
        
        doHandle(request, response);
    }
 
    /**
     * @see HttpServlet#doPost(HttpServletRequest request, HttpServletResponse 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();
        List<MemberVO> membersList = dao.selectAllMemberList();
        request.setAttribute("membersList", membersList);
        
        RequestDispatcher dispatcher = request.getRequestDispatcher("orm01/listMembers.jsp");
        dispatcher.forward(request, response);
        
    }
 
}
cs

4.MemberVO.java 작성

--이전 수업시간에 만들었던 t_member 테이블을 활용했다.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
import java.sql.Date;
 
public class MemberVO {
 
    private String id;
    private String pwd;
    private String name;
    private String email;
    private Date joinDate;
    
    public MemberVO() {
        // TODO Auto-generated constructor stub
    }
    
    public MemberVO(String id, String pwd, String name, String email) {
        super();
        this.id = id;
        this.pwd = pwd;
        this.name = name;
        this.email = email;
    }
 
    public String getId() {
        return id;
    }
 
    public void setId(String id) {
        this.id = id;
    }
 
    public String getPwd() {
        return pwd;
    }
 
    public void setPwd(String pwd) {
        this.pwd = pwd;
    }
 
    public String getName() {
        return name;
    }
 
    public void setName(String name) {
        this.name = name;
    }
 
    public String getEmail() {
        return email;
    }
 
    public void setEmail(String email) {
        this.email = email;
    }
 
    public Date getJoinDate() {
        return joinDate;
    }
 
    public void setJoinDate(Date joinDate) {
        this.joinDate = joinDate;
    }
    
    
}
cs

5.MemberDAO.java 작성

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
import java.io.IOException;
import java.io.Reader;
import java.util.List;
 
import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
 
public class MemberDAO {
    
    public static SqlSessionFactory sqlMapper = null;
    
    public static SqlSessionFactory getInstance() {
        if(sqlMapper == null) {
            String resource = "mybatis/SqlMapConfig.xml";
            try {
                Reader reader = Resources.getResourceAsReader(resource);
                sqlMapper = new SqlSessionFactoryBuilder().build(reader);
                reader.close();
                
            } catch (IOException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            }
        }
        
        return sqlMapper;
    }
    
    public List<MemberVO> selectAllMemberList(){
        sqlMapper = getInstance();
        //실제 member.xml의 SQL문을 호출하는데 사용되는 SqlSession 객체를 가져옴
        SqlSession session = sqlMapper.openSession();
        //여러 개의 레코드를 조회하므로 selectList() 메서드에 실행하고자하는 SQL문의 id를 인자로 전달함
        List<MemberVO> memlist = session.selectList("mapper.member.selectAllMemberList");
        return memlist;
    }
 
}
cs

6.listMembers.jsp 작성

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core" %>
<c:set var="contextPath" value="${pageContext.request.servletContext.contextPath }"/>
<%
    request.setCharacterEncoding("utf-8");
%>    
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>회원 정보 출력</title>
</head>
<body>
    <table border="1" align="center" width="80%">
        <tr align="center" bgcolor="#98c8fa">
            <td width="%"><b>아이디</b></td>
            <td width="%"><b>비밀번호</b></td>
            <td width="%"><b>이름</b></td>
            <td width="%"><b>이메일</b></td>
            <td width="%"><b>가입일</b></td>
        </tr>
        <c:forEach var="mem" items="${membersList }">
        <tr align="center">
            <td>${mem.id }</td>
            <td>${mem.pwd }</td>
            <td>${mem.name }</td>
            <td>${mem.email }</td>
            <td>${mem.joinDate }</td>
        </tr>    
        </c:forEach>
    </table>
</body>
</html>
cs

회원 목록 조회 결과 화면

회원 정보 조회 예제

1.member.xml 내용 추가

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
<?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">                    <!-- member.xml의 네임스페이스 지정 -->
 
    <resultMap type="memberVO" id="memResult">        <!-- SQL문 실행한 후 반환되는 레코드들을 typeAlias 태그에서 지정한 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 type="java.util.HashMap" id="memResult">    조회한 레코드를 지정한 컬럼이름을 key, 값을 value로 지정
        <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> -->
    
    <!-- id : DAO에서 id를 이용해 해당 SQL문을 호출함 -->
    <!-- resultMap : 반환되는 레코드를 memResult에 저장함 -->
    <select id="selectAllMemberList" resultMap="memResult">
        <![CDATA[
            select * from t_member order by joinDate desc
        ]]>
    </select>
    
    <!-- resultType : 문자열로 지정해 SQL문으로 조회한 이름(문자열)을 호출한 메서드로 반환함 -->
    <select id="selectName" resultType="String">
        <![CDATA[
            select name from t_member
            where id = 'ezenac'
        ]]>
    </select>
    
    <!-- resultType : 속성을 int로 지정해 SQL문으로 조회한 정수를 호출한 메서드로 반환함 -->
    <select id="selectPwd" resultType="int">
        <![CDATA[
            select pwd from t_member
            where id = 'ezenac'
        ]]>
    </select>
cs

2.MemberServlet.java 작성

 

매핑 /mem2.do로 변경

alert창으로 회원 정보 띄우기 구현

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
import java.io.IOException;
import java.io.PrintWriter;
import java.util.List;
 
import javax.servlet.RequestDispatcher;
import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
 
/**
 * Servlet implementation class MemberServlet
 */
@WebServlet("/mem2.do")
public class MemberServlet extends HttpServlet {
    private static final long serialVersionUID = 1L;
 
    /**
     * @see HttpServlet#doGet(HttpServletRequest request, HttpServletResponse response)
     */
    protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
        
        doHandle(request, response);
    }
 
    /**
     * @see HttpServlet#doPost(HttpServletRequest request, HttpServletResponse 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");
        PrintWriter out = response.getWriter();
        
        MemberDAO dao = new MemberDAO();
        String name = dao.selectName();
        int pwd = dao.selectPwd();
        
        out.write("<script>");
        //out.print("alert('이름 : "+name+" ')");
        out.write("alert('이름 : "+pwd+" ')");
        out.write("</script>");
        
        
    }
 
}
cs

3.MemberDAO.java 작성

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
import java.io.IOException;
import java.io.Reader;
import java.util.List;
 
import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
 
public class MemberDAO {
    
    public static SqlSessionFactory sqlMapper = null;
    
    public static SqlSessionFactory getInstance() {
        if(sqlMapper == null) {
            String resource = "mybatis/SqlMapConfig.xml";
            try {
                Reader reader = Resources.getResourceAsReader(resource);
                sqlMapper = new SqlSessionFactoryBuilder().build(reader);
                reader.close();
                
            } catch (IOException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            }
        }
        
        return sqlMapper;
    }
    
    public String selectName() {
        sqlMapper = getInstance();
        SqlSession session = sqlMapper.openSession();
        //selectOne() 메서드를 인자로 지정한 SQL문을 실행한 후 한 개의 데이터(문자열)을 반환함.
        String name = session.selectOne("mapper.member.selectName");
        return name;
    }
    
    public int selectPwd() {
        sqlMapper = getInstance();
        SqlSession session = sqlMapper.openSession();
        //selectOne() 메서드를 인자로 지정한 SQL문을 실행한 후 한 개의 데이터(정수)을 반환함.
        int pwd = session.selectOne("mapper.member.selectPwd");
        return pwd;
    }
    
 
}
cs

결과 화면

 

검색 조건 설정하여 회원 조회

1.member.xml 에 내용 추가

 

MemberDAO에서 메서드 호출 시 전달된 조건값은 매개변수 이름으로 sql문의 조건식에 전달

SQL문에서 조건값 전달 방법 : #{전달될 매개변수 이름}

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
<!-- 조회되는 한 개의 레코드를 memberVO에 저장함 -->
    <!-- String : MemberDAO에서 SQL문 호출 시 전달되는 매개변수의 데이터타입을 지정 -->
    <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>
    
    <!-- parameterType : dao에서 회원 정보를 memberVO의 속성에 저장해서 넘김 -->
    <!-- values <== memberVO의 속성 이름에 저장된 값을 value로 설정함 -->
    <select id="insertMember" parameterType="memberVO">
        <![CDATA[
            insert into t_member (id,pwd,name,email)
            values (#{id}, #{pwd}, #{name}, #{email})
        ]]>
    </select>
cs

2.MemberDAO.java 내용 추가

1
2
3
4
5
6
7
8
9
10
11
12
13
14
public MemberVO selectMemberById(String id) {
        sqlMapper = getInstance();
        SqlSession session = sqlMapper.openSession();
        MemberVO memberVO = session.selectOne("mapper.member.selectMemberById", id);
        return memberVO;
    }
 
    public List<MemberVO> selectMemberByPwd(int pwd) {
        sqlMapper = getInstance();
        SqlSession session = sqlMapper.openSession();
    
        List<MemberVO> membersList = session.selectList("mapper.member.selectMemberByPwd", pwd);
        return membersList;
    }
cs

3.MemberServlet.java 작성

 

매핑 /mem3.do 변경

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
import java.io.IOException;
 
import java.io.PrintWriter;
import java.util.List;
 
import javax.servlet.RequestDispatcher;
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 kr.co.ezenac.orm.MemberVO;
 
 
/**
 * Servlet implementation class MemberServlet
 */
@WebServlet("/mem3.do")
public class MemberServlet extends HttpServlet {
    private static final long serialVersionUID = 1L;
 
    /**
     * @see HttpServlet#doGet(HttpServletRequest request, HttpServletResponse response)
     */
    protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
        
        doHandle(request, response);
    }
 
    /**
     * @see HttpServlet#doPost(HttpServletRequest request, HttpServletResponse 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");
        PrintWriter out = response.getWriter();
        
        MemberDAO dao = new MemberDAO();
        MemberVO memberVO = new MemberVO();
        
        String action = request.getParameter("action");
        String forwardPage = "";
        
        if(action == null || action.equals("listMembers")){
            List<MemberVO> membersList = dao.selectAllMemberList();
            request.setAttribute("membersList", membersList);
            forwardPage = "orm02/listMembers.jsp";
        }
        // 검색 조건이 selectMemberById이면 전송된 값을 getParameter()로 가저온 후
        // SQL문의 조건식에서 id의 조건 값으로 전달
        else if(action.equals("selectMemberById")) {
            String id = request.getParameter("value");
            memberVO = dao.selectMemberById(id);
            request.setAttribute("member", memberVO);
            forwardPage = "orm02/memberInfo.jsp";
        }
        // 검색 조건이 selectMemberByPwd이면 전송된 값을
        // getParmeter()로 가져온 후 sql문의 조건식 pwd의 조건값으로 전달
        else if(action.equals("selectMemberByPwd")) {
            int pwd = Integer.parseInt(request.getParameter("value")); 
            List<MemberVO> membersList = dao.selectMemberByPwd(pwd);
            request.setAttribute("membersList", membersList);
            forwardPage = "orm02/listMembers.jsp";
        }
        
        RequestDispatcher dispatcher = request.getRequestDispatcher(forwardPage);
        dispatcher.forward(request, response);
        
        
    }
 
}
cs

4.search.jsp 작성

 

검색 조건을 전체, 아이디, 비밀번호 3가지로 주었다.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core" %>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>회원 검색창</title>
</head>
<body>
    <form action="${pageContext.request.servletContext.contextPath }/mem3.do">
        입력 : <input type="text" name="value">            <!-- 검색할 값 입력 -->
        <select name="action">    
            <option value="listMembers">전체</option>        <!-- 셀렉트 박스의 검색 조건 선택 -->
            <option value="selectMemberById">아이디</option>
            <option value="selectMemberByPwd">비밀번호</option>
        </select><br>
        <input type="submit" value="검색">
    </form>
</body>
</html>
cs

5.memberInfo.jsp 작성

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
<%@ 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="fmt" uri="http://java.sun.com/jsp/jstl/fmt" %>    
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>회원 정보 출력</title>
</head>
<body>
    <table border="1" align="center" width="100%">
        <tr align="center" bgcolor="#98c8fa">
            <td><b>아이디</b></td>
            <td><b>비밀번호</b></td>
            <td><b>이름</b></td>
            <td><b>이메일</b></td>
            <td><b>가입일</b></td>
        </tr>
        
        <tr align="center">
            <td>${member.id }</td>
            <td>${member.pwd }</td>
            <td>${member.name }</td>
            <td>${member.email }</td>
            <td>${member.joinDate }</td>
        </tr>
    </table>
</body>
</html>
cs

결과 화면

회원 추가, 회원 정보 수정, 회원 삭제

1.member.xml 내용 추가

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
<!-- parameterType : dao에서 회원 정보를 memberVO의 속성에 저장해서 넘김 -->
    <!-- values <== memberVO의 속성 이름에 저장된 값을 value로 설정함 -->
    <select id="insertMember" parameterType="memberVO">
        <![CDATA[
            insert into t_member (id,pwd,name,email)
            values (#{id}, #{pwd}, #{name}, #{email})
        ]]>
    </select>
    
    <!-- values <== HashMap에 각각의 key로 저장된 value를 가져와 테이블에 추가함 -->
    <insert id="insertMember2" parameterType="java.util.HashMap">
        <![CDATA[
            insert into t_member (id,pwd,name,email)
            values (#{id}, #{pwd}, #{name}, #{email})
        ]]>                
    </insert>    
    
    <!-- parameterType : SQL문에 사용될 데이터를 memberVO 빈에 설정해 전달함 -->
    <update id="updateMember" parameterType="memberVO">
        <![CDATA[
            update t_member
            set pwd = #{pwd}, name = #{name}, email = #{email}
            where
            id = #{id}
        ]]>
    </update>
    
    <!-- parameterType : 회원 ID는 문자열이므로 parameterType을 String으로 설정함 -->
    <!-- #{id} <== 전달된 ID를 조건값으로 해당 회원 정보를 삭제 -->
    <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>
        order by joinDate desc
    </select> -->
    
    <select id="searchMember" parameterType="memberVO" resultMap="memResult">
        <![CDATA[
            select * from t_member
        ]]>
        <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 collection="list" item="item" open="(" separator="," close=")">
            #{item}
        </foreach>
        order by joinDate desc
    </select>
cs

2.MemberDAO.java 내용 추가

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
public int insertMember(MemberVO memberVO) {
        sqlMapper = getInstance();
        SqlSession session = sqlMapper.openSession();
        int result = session.insert("mapper.member.insertMember", memberVO);
        session.commit();  //수동 커밋이므로 반드시 commit()메서드 호출하여 반영해야함
        return result;
    }
 
    public int insertMember2(Map<StringString> memberMap) {
        sqlMapper = getInstance();
        SqlSession session = sqlMapper.openSession();
        int result = session.insert("mapper.member.insertMember2", memberMap);    //메서드로 전달된 Ha
        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<MemberVO> searchMember(MemberVO memberVO) {
        sqlMapper = getInstance();
        SqlSession session = sqlMapper.openSession();
        //회원 검색창에서 전달된 이름과 메일값을 memberVO에 설정하여 SQL문에 전달함
        List<MemberVO> list = session.selectList("mapper.member.searchMember", memberVO);
        return list;
    }
 
    public List<MemberVO> foreachSelect(List<String> nameList) {
        sqlMapper = getInstance();
        SqlSession session = sqlMapper.openSession();
        //검색 이름이 저장된 nameList을 SQL문으로 전달함
        List<MemberVO> list = session.selectList("mapper.member.foreachSelect", nameList);
        return list;
    }
cs

3.MemberServlet.java 내용 추가

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
private void doHandle(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
        request.setCharacterEncoding("utf-8");
        response.setContentType("text/html;charset=utf-8");
        PrintWriter out = response.getWriter();
        
        MemberDAO dao = new MemberDAO();
        MemberVO memberVO = new MemberVO();
        
        String action = request.getParameter("action");
        String forwardPage = "";
        
        if(action == null || action.equals("listMembers")){
            List<MemberVO> membersList = dao.selectAllMemberList();
            request.setAttribute("membersList", membersList);
            forwardPage = "orm03/listMembers.jsp";
        }
        // 검색 조건이 selectMemberById이면 전송된 값을 getParameter()로 가저온 후
        // SQL문의 조건식에서 id의 조건 값으로 전달
        else if(action.equals("selectMemberById")) {
            String id = request.getParameter("value");
            memberVO = dao.selectMemberById(id);
            request.setAttribute("member", memberVO);
            forwardPage = "orm02/memberInfo.jsp";
        }
        // 검색 조건이 selectMemberByPwd이면 전송된 값을
        // getParmeter()로 가져온 후 sql문의 조건식 pwd의 조건값으로 전달
        else if(action.equals("selectMemberByPwd")) {
            int pwd = Integer.parseInt(request.getParameter("value"));
            List<MemberVO> membersList = dao.selectMemberByPwd(pwd);
            request.setAttribute("membersList", membersList);
            forwardPage = "orm03/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);
            forwardPage = "/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<StringString> memberMap = new HashMap<>();
            /* 회원 가입창에서 전송된 회원 정보를 HashMap에 key/value 형태로 저장한 후 insertMember2() 인자로 전달 */
            memberMap.put("id", id);
            memberMap.put("pwd", pwd);
            memberMap.put("name", name);
            memberMap.put("email", email);
            dao.insertMember2(memberMap);
            forwardPage = "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);
            forwardPage = "mem4.do?action=listMembers";
        }
        else if(action.equals("deleteMember")) {
            String id = request.getParameter("id");        //회원 ID를 가져옴
            dao.deleteMember(id);
            forwardPage = "/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<MemberVO> membersList = dao.searchMember(memberVO);
            request.setAttribute("membersList", membersList);
            forwardPage = "orm03/listMembers.jsp";
        }
        else if(action.equals("foreachSelect")) {
            List<String> nameList = new ArrayList<>();
            nameList.add("이순신");
            nameList.add("신사임당");
            nameList.add("홍길동");
            List<MemberVO> membersList = dao.foreachSelect(nameList);
            request.setAttribute("membersList", membersList);
            forwardPage = "orm03/listMembers.jsp";
        }
        
        RequestDispatcher dispatcher = request.getRequestDispatcher(forwardPage);
        dispatcher.forward(request, response);
        
        
    }
cs

4.memberForm.jsp 작성

 

회원가입창

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core" %>
<c:set var="contextPath" value="${pageContext.request.contextPath }"/>
<!DOCTYPE html>
<html>
<head>
    <meta charset="UTF-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <title>회원 가입창</title>
</head>
<body>
    <form action="${contextPath }/mem4.do?action=insertMember2" method="post">
        <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">
                    <p align="right">&nbsp;</p>
                </td>
                <td width="400">
                    <input type="submit" value="가입하기">
                    <input type="reset" value="다시 입력">
                </td>
                
            </tr>
        </table>
    </form>
</body>
</html>
cs

5.modMember.jsp 작성

 

회원 정보 수정 화면

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
<%@ 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="fmt" uri="http://java.sun.com/jsp/jstl/fmt" %>
<c:set var="contextPath" value="${pageContext.request.contextPath}" />       
<%
    request.setCharacterEncoding("utf-8");
%>    
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>회원 정보 수정</title>
</head>
<body>
    <h1> 회원 정보 수정 </h1>    
    <form action="${contextPath}/mem4.do?action=updateMember" method="post">
        <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">
                    <p align="right">&nbsp;</p>
                </td>
                <td width="400">
                    <input type="submit" value="수정하기">
                    <input type="reset" value="다시입력">
                </td>
            </tr>                                
        </table>
    </form>    
</body>
</html>
cs

6.searchMember.jsp 작성

 

foreach문 활용하여 회원 정보 검색하는 화면

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core" %>
<c:set var="contextPath" value="${pageContext.request.servletContext.contextPath }"/>
<%
    request.setCharacterEncoding("utf-8");
%>     
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>회원 검색창</title>
</head>
<body>
    <h1>회원 검색</h1>
    <form action="${contextPath }/mem4.do">
        <input type="hidden" name="action" value="searchMember">
        이름 : <input type="text" name="name"><br>
        이메일 : <input type="text" name="email"><br>
        <input type="submit" value="검색">
    </form>
</body>
</html>
cs

7.listMembers.jsp 작성

 

회원 정보 삭제 기능 추가

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core" %>
<c:set var="contextPath" value="${pageContext.request.servletContext.contextPath }"/>
<%
    request.setCharacterEncoding("utf-8");
%>    
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>회원 정보 출력</title>
</head>
<body>
    <table border="1" align="center" width="80%">
        <tr align="center" bgcolor="#98c8fa">
            <td width="%"><b>아이디</b></td>
            <td width="%"><b>비밀번호</b></td>
            <td width="%"><b>이름</b></td>
            <td width="%"><b>이메일</b></td>
            <td width="%"><b>가입일</b></td>
            <td><b>삭제</b></td>
        </tr>
        <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=deleteMember&id=${member.id}">삭제하기</a></td>
        </tr>    
        </c:forEach>
    </table>
</body>
</html>
cs

결과화면

가입하기를 누르면 회원가입 완료 -> DB에도 추가된다.

삭제하기를 누르면

목록에서 없어지고 DB에서도 없어짐.

회원 정보 수정도 마찬가지...

 

5. 예제에서 사용된 동적 SQL문 정리

 

1) select * from t_member

   select 8 from t_member
       where id = 'shin'

       select 8 from t_member
       where id = 'shin'
       and pwd = '0311'

2) 주로 SQL 문의 조건절에서 사용함.
       조건절 (where)에 조건을 동적으로 추가.
       JSTL과 XML 기반으로 동적 SQL문 작성.

3) 마이바티스 동적 SQL문 구성 요소
        - if
        - choose(when, otherwise)
        - trim
        - foreach
   

 4) <if> 태그로 동적 SQL문 만들기
        -   <where>
                <if test='조건식'>
                    추가할 구문
                </if>
            </where>

 5)<choose> 태그로 동적 SQL문 만들기
  ---------
  -<where>
    <choose>
  <when test='조건식1'>
구문1
  </when>
  <when test='조건식2'>
구문2
  </when>
  <otherwise>
구문
  </otherwise>
</choose>
   </where>

 

6)<foreach> 태그로 회원 정보 조회하기
  ----------
  -<foreach item="item" collection="list" index="index" open="(" close=")" separator=",">
#{item}
   </foreach>
   
   -collection
    :전달받은 인자 값을 의미
 배열과 List 계열 인스턴스를 전달할 수 있음
 -List 인스턴스 전달 시에는 list로 표시함.
 -배열 전달 시에는 array로 표시함
   
   -index
    :접근하는 값의 위치를 나타냄
 최초 값의 위치는 0
   
   -item
    : 반복문이 실행될 때마다 collection 속성에 지정된 값에 접근하여 차례대로 사용함
   
   -open
    :해당 구문이 시작될 때의 지정한 기호를 추가함

   -close
    : 해당 구문이 끝날 때의 지정한 기호를 추가함
   
   -separator
    : 한 번 이상 반복될 때마다 반복되는 사이에 지정한 기호를 추가함

반응형
profile

my code archive

@얼레벌레 개발자👩‍💻

포스팅이 좋았다면 "좋아요❤️" 또는 "구독👍🏻" 해주세요!

반응형