2023-09-07 78일차

팀프로젝트 4일차 스프링부트_사원목록 조회, 검색, 페이징 기능 

 

DTO

package com.study.springboot.member.model;

import java.time.LocalDateTime;

import com.study.springboot.member.service.WrongIdPasswordException;

import lombok.Getter;
import lombok.NoArgsConstructor;
import lombok.Setter;


@Getter
@NoArgsConstructor
@Setter
public class Member {
    private Long mno;
    private String name;
    private String password;
    private String phone;
    private LocalDateTime hiredate;
    private String job;
    private Integer deptno;
    private String dname;
    private String loc;
    
    // 검색 필터
    private String type;
    private String keyword;
    
    public Member(Long mno, String name, String password, String phone, LocalDateTime hiredate, String job, Integer deptno) {
        this.mno = mno;
        this.name = name;
        this.password = password;
        this.phone = phone;
        this.hiredate = hiredate;
        this.job = job;
        this.deptno = deptno;

        		
    }
    
    
    
    
    

    // setHiredate 메소드를 수정
    public void setHiredate(LocalDateTime hiredate) {
        this.hiredate = hiredate;
    }

    // setDepartment 메소드를 수정
    public void setDepartment(Department department) {
        this.deptno = department.getDeptno();
    }
    


    public void changePassword(String oldPassword, String newPassword) {
        if (!password.equals(oldPassword)) {
            throw new WrongIdPasswordException();
        }
        this.password = newPassword;
    }

    public boolean matchPassword(String password) {
        return this.password.equals(password);
    }

    public void setMno(Long mno) {
        this.mno = mno;
    }
}

 

페이징

package com.study.springboot.member.model;

import java.util.List;



public class MemberPage {

	@Override
	public String toString() {
		return "BoardPage [total=" + total + ", currentPage=" + currentPage + ", content=" + content + ", totalPages="
				+ totalPages + ", startPage=" + startPage + ", endPage=" + endPage + "]";
	}

	private int total;
	private int currentPage;
	private List<Member> content;
	private int totalPages;
	private int startPage;
	private int endPage;

	public MemberPage(int total, int currentPage, int size, List<Member> content) {
		this.total = total;
		this.currentPage = currentPage;
		this.content = content;
		if (total == 0) {
			totalPages = 0;
			startPage = 0;
			endPage = 0;
		} else {
			totalPages = total / size;
			if (total % size > 0) {
				totalPages++;
			}
			int modVal = currentPage % 5;
			startPage = currentPage / 5 * 5 + 1;
			if (modVal == 0) startPage -= 5;
			
			endPage = startPage + 4;
			if (endPage > totalPages) endPage = totalPages;
		}
	}

	public int getTotal() {
		return total;
	}

	public boolean hasNoArticles() {
		return total == 0;
	}

	public boolean hasArticles() {
		return total > 0;
	}
	
	public int getCurrentPage() {
		return currentPage;
	}

	public int getTotalPages() {
		return totalPages;
	}

	public List<Member> getContent() {
		return content;
	}

	public int getStartPage() {
		return startPage;
	}
	
	public int getEndPage() {
		return endPage;
	}
}

 

DAO

	// 사원 목록 출력
	public List<Member> listAll(int page, int size) {
	    int offset = (page - 1) * size;
	    String sql = "SELECT m.mno, m.name, m.phone, m.hiredate, m.job, d.dname, d.loc " +
	                 "FROM member m " +
	                 "LEFT JOIN department d ON m.deptno = d.deptno " +
	                 "LIMIT ?, ?";

	    return jdbcTemplate.query(
	        new PreparedStatementCreator() {
	            @Override
	            public PreparedStatement createPreparedStatement(Connection con) throws SQLException {
	                PreparedStatement ps = con.prepareStatement(sql);
	                ps.setInt(1, offset);
	                ps.setInt(2, size);
	                return ps;
	            }
	        },
	        new RowMapper<Member>() {
	            @Override
	            public Member mapRow(ResultSet rs, int rowNum) throws SQLException {
	                Member member = new Member();
	                member.setMno((long) rs.getInt("mno"));
	                member.setName(rs.getString("name"));
	                member.setPhone(rs.getString("phone"));

	                // hiredate 컬럼을 LocalDateTime으로 변환
	                Timestamp hiredateTimestamp = rs.getTimestamp("hiredate");
	                member.setHiredate(hiredateTimestamp != null ? hiredateTimestamp.toLocalDateTime() : null);

	                member.setJob(rs.getString("job"));

	                member.setDname(rs.getString("dname"));
	                member.setLoc(rs.getString("loc"));

	                return member;
	            }
	        });
	}
	// 페이징 기능에 사용
	public int getTotalMemberCount() {
	    String sql = "SELECT COUNT(*) FROM member";
	    return jdbcTemplate.queryForObject(sql, Integer.class);
	}
	


	// 검색
	public List<Member> searchMembers(String type, String keyword) {
	    // 검색 쿼리 작성 (예: 이름으로 검색)
	    String sql = "SELECT m.mno, m.name, m.phone, m.hiredate, m.job, d.dname, d.loc " +
	                 "FROM member m " +
	                 "LEFT JOIN department d ON m.deptno = d.deptno " +
	                 "WHERE " + type + " LIKE ?";

	    String likeKeyword = "%" + keyword + "%";

	    return jdbcTemplate.query(
	        new PreparedStatementCreator() {
	            @Override
	            public PreparedStatement createPreparedStatement(Connection con) throws SQLException {
	                PreparedStatement ps = con.prepareStatement(sql);
	                ps.setString(1, likeKeyword);
	                return ps;
	            }
	        },
	        new RowMapper<Member>() {
	            @Override
	            public Member mapRow(ResultSet rs, int rowNum) throws SQLException {
	                Member member = new Member();
	                member.setMno((long) rs.getInt("mno"));
	                member.setName(rs.getString("name"));
	                member.setPhone(rs.getString("phone"));

	                // hiredate 컬럼을 LocalDateTime으로 변환
	                Timestamp hiredateTimestamp = rs.getTimestamp("hiredate");
	                member.setHiredate(hiredateTimestamp != null ? hiredateTimestamp.toLocalDateTime() : null);

	                member.setJob(rs.getString("job"));

	                member.setDname(rs.getString("dname"));
	                member.setLoc(rs.getString("loc"));

	                return member;
	            }
	        });
	}

 

Service

package com.study.springboot.member.service;

import java.util.List;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import com.study.springboot.member.dao.MemberDao;
import com.study.springboot.member.model.Member;

@Service
public class MemberService {

	@Autowired
	private MemberDao memberDao;


	public MemberInfo authenticate(Long mno, String password) {
		Member member = memberDao.selectByMno(mno);
		if (member == null) {
			throw new WrongIdPasswordException();
		}
		if (!member.matchPassword(password)) {
			throw new WrongIdPasswordException();
		}
		return new MemberInfo(member.getMno(),

				member.getName(), member.getPhone(), member.getJob(), member.getDeptno());
	}
	
    public List<Member> searchMembers(String type, String keyword) {
        return memberDao.searchMembers(type, keyword);
    }

}

 

Controller

package com.study.springboot.controller;



import java.util.List;

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

import com.study.springboot.member.dao.MemberDao;
import com.study.springboot.member.model.Member;
import com.study.springboot.member.service.MemberService;

import lombok.extern.slf4j.Slf4j;



@Controller
@Slf4j
public class MemberListController {
    @Autowired
	private MemberDao memberDao;

    @Autowired
    private MemberService memberService;
   
    
    @GetMapping("/memberList")
    public String listMembers(Model model,
            @RequestParam(defaultValue = "1") int page,
            @RequestParam(defaultValue = "10") int size) {
        int totalMemberCount = memberDao.getTotalMemberCount();
        int totalPages = (int) Math.ceil((double) totalMemberCount / size);

        List<Member> members = memberDao.listAll(page, size);
        model.addAttribute("members", members);
        model.addAttribute("page", page);
        model.addAttribute("size", size);
        model.addAttribute("totalPages", totalPages);

        log.info("Listing members: {}", members);
        System.out.println(members);
        return "/login/memberList"; 
    }
    
    @GetMapping("/getSearchList")
    public String getSearchList(@RequestParam("type") String type,
    								   @RequestParam("keyword") String keyword, 
    								   Model model){
    	List<Member> members = memberService.searchMembers(type, keyword);
    	model.addAttribute("members", members);
    	return "/login/memberList";
    }

    
    

    


    
    
    

}

 

JS

$(document).ready(function () {
    // 검색 버튼을 클릭했을 때
    $('.search').click(function (event) {
        event.preventDefault(); // 기본 동작 중단

        // 입력한 데이터 가져오기
        var type = $('.type').val();
        var keyword = $('.keyword').val();
        if (keyword == null) {
            alert("검색내용을 입력하세요.");
            return; // 함수 종료
        }
        // AJAX 요청
        $.ajax({
            url: '/getSearchList', // 적절한 URL로 수정
            type: 'Get',
            data: {
                type: type,
                keyword: keyword,
                page: 1 // 검색 결과를 첫 번째 페이지로 설정
            },
            success: function (res) {
                $('.mainView').html(res);
            },
            error: function () {
                // AJAX 요청이 실패한 경우 처리할 내용
                alert('오류가 발생했습니다.');
            }
        });
    });

    // 페이지 번호 링크를 클릭했을 때의 처리
    $(document).on('click', '.search_page', function (event) {
        event.preventDefault(); // 기본 동작 중단

        var pageUrl = $(this).attr('href');

        // AJAX 요청
        $.ajax({
            url: pageUrl,
            type: 'GET',
            success: function (data) {
                // AJAX 요청이 성공하면 data를 적절한 방식으로 처리하여 페이지 업데이트
                $('.mainView').html(data);
            },
            error: function () {
                // AJAX 요청이 실패한 경우 처리할 내용
                alert('오류가 발생했습니다.');
            }
        });
    });
});

 

JSP

<%@ page contentType="text/html; charset=utf-8" %>
<%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core" %>
<%@ taglib prefix="spring" uri="http://www.springframework.org/tags" %>
<!DOCTYPE html>
<html>
<head>
    <meta charset="UTF-8">
    <title>사원 목록</title>
    
        <style>

        .pagination {
            display: inline-block;
            padding: 0;
        }

        .pagination li {
            display: inline;
            margin-right: 5px;
        }



    	</style>

</head>
<body>
    <h2>사원 목록</h2>
    <table border="1">
        <thead>
            <tr>
                <th>사번</th>
                <th>이름</th>
                <th>핸드폰</th>
                <th>입사일</th>
                <th>직급</th>
                <th>부서명</th>
                <th>부서 위치</th>
            </tr>
        </thead>
        <tbody id ="usertable">
            <c:forEach items="${members}" var="member">
                <tr>
                    <td>${member.mno}</td>
                    <td>${member.name}</td>
                    <td>${member.phone}</td>
                    <td>${member.hiredate}</td>
                    <td>${member.job}</td>
                    <td>${member.dname}</td>
                    <td>${member.loc}</td>
                </tr>
            </c:forEach>
            
        </tbody>
    </table>
<!-- 페이지 번호 표시 부분 -->
<div class="page-box">
    <c:if test="${totalPages > 1}">
        <ul class="pagination">
            <li class="<c:if test="${page == 1}">disabled</c:if>">
                <a class="search_page" href="/memberList?page=${page - 1}" aria-label="Previous">
                    <span aria-hidden="true">&laquo;</span>
                </a>
            </li>
            <c:forEach begin="1" end="${totalPages}" var="pageNumber">
                <li class="<c:if test="${page == pageNumber}">active</c:if>">
                    <a class="search_page" href="/memberList?page=${pageNumber}">${pageNumber}</a>
                </li>
            </c:forEach>
            <li class="<c:if test="${page == totalPages}">disabled</c:if>">
                <a class="search_page" href="/memberList?page=${page + 1}" aria-label="Next">
                    <span aria-hidden="true">&raquo;</span>
                </a>
            </li>
        </ul>
    </c:if>
</div>

<div>
    <select name="type" class="type">
        <option selected value="">검색 내용 선택</option>
        <option value="mno">사번 검색</option>
        <option value="name">이름 검색</option>
        <option value="dname">부서명 검색</option>
    </select>
    <input type="text" class="keyword" name="keyword" value="" />
    <button class="search">검색</button>
</div>

<!-- <script>
function getSearchList() {
    $.ajax({
        type: 'GET',
        url: "/getSearchList",
        data: $("form[name=search-form]").serialize(),
        success: function (result) {
        	
        	$(#usertable).html(result);

        }
    });
    return false; // 페이지 새로고침 방지
}
</script> -->

	<script src="https://code.jquery.com/jquery-3.6.0.min.js"></script>
	<script src="js/memberList.js"></script>
    
</body>
</html>

 

 

결과

 

사번, 이름, 부서 검색 기능