DAY49 - MyBatis HOMEWORK (2024.09.10)

myBatisTest - 5번 UserSearchService

   1. 이름으로 검색

   2. 아이디로 검색

     번호선택: 

  

     1번 선택

      검색을 원하는 이름 입력 : 홍 (홍이 들어간 이름을 다 가져온다.)

     2번 선택

       검색을 원하는 아이디 입력: n (n이 들어간 아이디를 다 가져온다.)

 

    [ 조건 ]

      userMapper.xml에서 id="search" 하나로 해결하시오.

 

UserSearchService.java

package user.service;

import java.util.List;
import java.util.Scanner;

import user.bean.UserDTO;
import user.dao.UserDAO;

public class UserSearchService implements UserService{
	@Override
	public void execute() {
		System.out.println();
		Scanner scan = new Scanner(System.in);
		int num = 0;

		System.out.println("1. 이름으로 검색");
		System.out.println("2. 아이디로 검색");
		System.out.print("번호 선택 : ");
		num = scan.nextInt();	
		
		UserDAO userDAO = UserDAO.getInstance();
		List<UserDTO> list = userDAO.search(num);

		if(!list.isEmpty()) {
			for (UserDTO userDTO : list) {
	            System.out.println("이름: " + userDTO.getName() 
	            				+ "\t아이디: " + userDTO.getId() 
	            				+ "\t비밀번호: " + userDTO.getPwd());
	        }
		}else {
			System.out.println("검색하신 이름 또는 아이디가 없습니다.");
		}
	}
}

 

UserDAO.java

public List<UserDTO> search(int num) {
		SqlSession sqlSession = sqlSessionFactory.openSession(); //생성
		Scanner scan = new Scanner(System.in);
		String name = null;
		String id = null;
		if(num == 1) {
			System.out.print("검색을 원하는 이름 입력 : ");
			name = scan.next();
		}else if(num == 2) {
			System.out.print("검색을 원하는 아이디 입력 : ");
			id = scan.next();
		}
		
		UserDTO userDTO = new UserDTO();
		userDTO.setName(name);
		userDTO.setId(id);
		List<UserDTO> list = sqlSession.selectList("userSQL.search", userDTO);
		sqlSession.close();
		return list;
	}

 

userMapper.xml

	<select id="search" parameterType="user" resultType="user">
	    select * from usertable
	    <trim prefix="WHERE" prefixOverrides="AND">
	        <if test="name != null and name != ''">
	            name like '%' || #{name} || '%'
	        </if>
	        <if test="id != null and id != ''">
	            and id like '%' || #{id} || '%'
	        </if>
	    </trim>
	</select>

---

강사님 답

UserSearchService.java

package user.service;

import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Scanner;

import user.bean.UserDTO;
import user.dao.UserDAO;

public class UserSearchService implements UserService{
	@Override
	public void execute() {
		System.out.println();
		Scanner scan = new Scanner(System.in);

		System.out.println("1. 이름으로 검색");
		System.out.println("2. 아이디로 검색");
		System.out.print("번호 선택 : ");
		int num = scan.nextInt();	
		
		String columnName = null;
		String value = null;
		if(num == 1) {
			System.out.print("검색을 원하는 이름 입력 : ");
			value = scan.next();
			
			columnName = "name";
		}else if(num == 2) {
			System.out.print("검색을 원하는 아이디 입력 : ");
			value = scan.next();
			
			columnName = "id";
		}
		
		Map<String, String> map = new HashMap<>();
		map.put("columnName", columnName);
		map.put("value", value);
		
		//DB
		UserDAO userDAO = UserDAO.getInstance();
		List<UserDTO> list = userDAO.search(map);

		for (UserDTO userDTO : list) {
            System.out.println(userDTO.getName() + "\t"
            				+ userDTO.getId() + "\t" 
            				+ userDTO.getPwd());
        }
		
	}
}

 

 

UserDAO.java

	public List<UserDTO> search(Map<String, String> map) {
		SqlSession sqlSession = sqlSessionFactory.openSession(); //생성
		List<UserDTO> list = sqlSession.selectList("userSQL.search", map);
		sqlSession.close();
		return list;
	}

 

userMapper.xml ★★★

• 방법1

	<select id="search" parameterType="java.util.Map" resultType="user">
	    select * from usertable where
        <if test="columnName == 'name'">
            name like '%' || #{value} || '%'
        </if>
        <if test="columnName == 'id'">
            id like '%' || #{value} || '%'
		</if>
	</select>

---

• 방법2

		select * from usertable where
		<choose>
			<when test="columnName == 'name'">
				 name like '%' || #{value} || '%'
			</when>
			<otherwise>
				 id like '%' || #{value} || '%'
			</otherwise>
		</choose>

---

• 방법3

select * from usertable where ${columnName} like '%' || #{value} || '%'

---

2. BoardDAO ---> MyBatis로 바꾸기

BoardDAO - 환경설정 하기

package board.dao;

import java.io.IOException;
import java.io.Reader;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import javax.naming.Context;
import javax.naming.InitialContext;
import javax.naming.NamingException;
import javax.sql.DataSource;

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 board.bean.BoardDTO;


public class BoardDAO {	
	private static BoardDAO boardDAO = new BoardDAO();
	private SqlSessionFactory sqlSessionFactory;
	
	public static BoardDAO getInstance() {
		return boardDAO;
	}
	
	public BoardDAO() {
		try {
			Reader reader = Resources.getResourceAsReader("mybatis-config.xml");
			sqlSessionFactory = new SqlSessionFactoryBuilder().build(reader);
			
			//InputStream inputStream = Resources.getResourceAsStream("mybatis-config.xml");
			//sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
		} catch (IOException e) {
			e.printStackTrace();
		}
	}

---

mybatis-config.xml

 	<mappers>
 		<mapper resource="mapper/userMapper.xml"/>
 		<mapper resource="mapper/boardMapper.xml"/>
 	</mappers>

---

글작성

BoardDAO.java

	public void boardWrite(BoardDTO boardDTO) {
		SqlSession sqlSession = sqlSessionFactory.openSession(); //생성
		sqlSession.insert("boardSQL.boardWrite", boardDTO);
		sqlSession.commit();
		sqlSession.close();
	}

 

 

boardMapper.xml

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper
 PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
 "https://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="boardSQL">
	<!-- 글쓰기 -->
    <insert id="boardWrite" parameterType="board.bean.BoardDTO">
        insert into board (seq, id, name, email, subject, content, ref)
        values(seq_board.NEXTVAL, #{id}, #{name}, #{email}, #{subject}, #{content}, seq_board.CURRVAL)
    </insert>

---

글목록 보기

BoardDAO.java

	public List<BoardDTO> boardList(int startNum, int endNum){
		Map<String, Integer> map = new HashMap<>();
		map.put("startNum", startNum);
		map.put("endNum", endNum);
		
		SqlSession sqlSession = sqlSessionFactory.openSession(); //생성
		List<BoardDTO> list = sqlSession.selectList("boardSQL.boardList", map);
		sqlSession.close();
		return list;
	}
	
	public int getTotalA() {
		SqlSession sqlSession = sqlSessionFactory.openSession(); //생성
		int totalA = sqlSession.selectOne("boardSQL.getTotalA");
		sqlSession.close();
		return totalA;
	}

 

boardMapper.xml

• 부등호처리 방법1

    <!-- 글목록 -->
	<select id="boardList" parameterType="java.util.Map" resultType="board.bean.BoardDTO">
	    select * from (
	        select rownum rn, tt.* from (
	            select * from board order by ref desc, step asc
	        ) tt
	    )
	    where rn between #{startNum} and #{endNum}
	</select>

	<!-- 총글수 -->
    <select id="getTotalA" resultType="int">
        select count(*) from board
    </select>
</mapper>

 

---

	    select * from (
	        select rownum rn, tt.* from (
	            select * from board order by ref desc, step asc
	        ) tt
	    )
	    where rn >= #{startNum} and rn <= #{endNum}

 

이런식으로 하면 < 이걸 부등호 기호가 아닌 태그로 인식한다 !! 그러므로 이렇게 하면 안 된다 !!

---

•  부등호처리 방법2

		select * from (
	        select rownum rn, tt.* from (
	            select * from board order by ref desc, step asc
	        ) tt
	    ) where rn >= #{startNum} and rn <= #{endNum}

---

•  부등호처리 방법3

	    <![CDATA[
	    	select * from (
	        select rownum rn, tt.* from (
	            select * from board order by ref desc, step asc
	        ) tt
	    	)where rn >= #{startNum} and rn <= #{endNum}
	    ]]>

---

날짜 처리하기

 날짜가 이런식으로 다 풀리게된다.

---

boardList.jsp

<body>
 <img src="../image/free-icon-love-4096198.png" alt="홈" width="60" height="60" onclick="location.href='../index.jsp'" />
    
    <table>
        <thead>
            <tr>
                <th>글번호</th>
                <th>제목</th>
                <th>작성자</th>
                <th>작성일</th>
                <th>조회수</th>
            </tr>
        </thead>
        <tbody>
        	<% if(list != null) { %>
        		<% for(BoardDTO boardDTO : list) { %>
        		 <tr> 
                     <td><%=boardDTO.getSeq() %></td>
                     <td><%=boardDTO.getSubject() %></td>
                     <td><%=boardDTO.getName() %></td>
                     <td>
                     	<%=new SimpleDateFormat("yyyy.MM.dd").format(boardDTO.getLogtime()) %>
                     </td>
                     <td><%=boardDTO.getHit() %></td>
                 </tr>
                 <%} %>
        	  <%} %>	 	
        </tbody>
    </table>
	<div style="margin-top: 15px;">
		<%=boardPaging.getPagingHTML() %>
	</div>

<script type="text/javascript">
function boardPaging(pg){
	location.href = "boardList2.jsp?pg=" + pg;
}
</script>
</body>