Role 추가에따라 dao 도 Post, User 둘다 수정이 있었다.
특히 User는 Role 직접적인 CRUD 내용도 추가하였다.
package com.example.post.dao;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import org.mybatis.spring.SqlSessionTemplate;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Repository;
import com.example.post.model.Category;
import com.example.post.model.Post;
import lombok.extern.slf4j.Slf4j;
@Repository
@Slf4j
public class PostDao {
@Autowired
private SqlSessionTemplate sqlSession;
public List<?> findAll(Map params){
log.debug("okok");
return sqlSession.selectList("selectList", params);
}
public Post selectPost(String num) {
return sqlSession.selectOne("PostMapper.selectPost", num);
}
public void modifyPost(Post post) {
sqlSession.update("PostMapper.modifyPost", post);
}
public void insertPost(Post post) {
sqlSession.insert("PostMapper.insertPost", post);
}
public void deletePost(String num) {
sqlSession.delete("PostMapper.deletePost", Integer.parseInt(num));
}
public List<Category> selectCategory(String username) {
return sqlSession.selectList("PostMapper.selectCategory", username);
}
public void insertCategory(Category category) {
sqlSession.insert("PostMapper.insertCateogry", category);
}
public String selectCategoryName(String categoryId) {
return sqlSession.selectOne("PostMapper.selectCategoryName", Integer.parseInt(categoryId));
}
public void deleteCategoryByRoleId(String roleId) {
sqlSession.delete("PostMapper.deleteCategoryByRoleId", Integer.parseInt(roleId));
}
public void insertRoleByCategory(String category, String roleId) {
Map<String, Object> params = new HashMap<>();
params.put("categoryId", Integer.parseInt(category));
params.put("roleId", Integer.parseInt(roleId));
sqlSession.insert("PostMapper.insertRoleByCategory", params);
}
public Category selectCategoryInfo(String categoryId) {
return sqlSession.selectOne("PostMapper.selectCategoryInfo", Integer.parseInt(categoryId));
}
public List<Category> findCategoryByRoleId(String roleId) {
return sqlSession.selectList("PostMapper.selectCategoryByRoleId", Integer.parseInt(roleId));
}
public List<Category> selectAllCategory() {
return sqlSession.selectList("PostMapper.selectAllCategory");
}
public void modifyBoardInfo(Category category) {
System.out.println(category);
sqlSession.update("PostMapper.modifyBoardInfo", category);
}
}package com.example.post.dao;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import org.mybatis.spring.SqlSessionTemplate;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Repository;
import com.example.post.model.Role;
import com.example.post.model.User;
@Repository
public class UserDao {
@Autowired
SqlSessionTemplate sqlSession;
public User selectByUsername(String username) {
return sqlSession.selectOne("UserMapper.selectByUsername", username);
}
public List<Role> selectRoleByUsername(String username) {
return sqlSession.selectList("UserMapper.selectRoleByUsername", username);
}
public void insertUser(User user) {
sqlSession.insert("UserMapper.insertUser", user);
}
public List<User> selectAllUser() {
return sqlSession.selectList("UserMapper.selectAllUser");
}
public List<Role> selectAllRole() {
return sqlSession.selectList("UserMapper.selectAllRole");
}
public void insertRole(Role role) {
sqlSession.insert("UserMapper.insertRole", role);
}
public Role selectRoleInfo(String roleId) {
return sqlSession.selectOne("UserMapper.selectRoleInfo", Integer.parseInt(roleId));
}
public void deleteUserByRoleId(String roleId) {
sqlSession.delete("UserMapper.deleteUserByRoleId", Integer.parseInt(roleId));
}
public void insertRoleByUsername(String user, String roleId) {
Map<String, Object> params = new HashMap<>();
params.put("username", user);
params.put("roleId", Integer.parseInt(roleId));
sqlSession.insert("UserMapper.insertRoleByUsername", params);
}
public List<User> selectUserByRoleId(String roleId) {
return sqlSession.selectList("UserMapper.selectUserByRoleId", Integer.parseInt(roleId));
}
public User selectUserInfo(String username) {
return sqlSession.selectOne("UserMapper.selectUserInfo", username);
}
public void deleteRoleUser(String roleId) {
sqlSession.delete("UserMapper.deleteRoleUser", Integer.parseInt(roleId));
}
public void deleteRoleCategory(String roleId) {
sqlSession.delete("UserMapper.deleteRoleCategory", Integer.parseInt(roleId));
}
public void delteRole(String roleId) {
sqlSession.delete("UserMapper.deleteRole", Integer.parseInt(roleId));
}
public void modifyUserInfo(User user) {
sqlSession.update("UserMapper.modifyUserInfo", user);
}
public void deleteRoleUserByUsername(String username) {
sqlSession.delete("UserMapper.deleteRoleUserByUsername", username);
}
public void deleteUser(String username) {
sqlSession.delete("UserMapper.deleteUser", username);
}
}추가로 살펴볼 내용은,
게시판을 추가하였고, 그 게시판 카테고리에 접근권한을 부여했으니 PostList 쪽도 수정이 필요했다.
지금까지는 단 하나의 게시판에서 모든 글을 썼지만, 앞으로는 게시판을 선택하고 작성을 해야하기에, Post 정보에 cateogory 관련 내용이 들어가야하고, 그 category 와 role 을 또 이어주어야만 했다.
mapper-post.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="PostMapper">
<select id="selectTotalList" parameterType="int" resultType="com.example.post.model.Post">
SELECT t.*, c.commentNum
FROM TEST1 t
LEFT JOIN (
SELECT postid, COUNT(*) AS commentNum
FROM COMMENTS1
GROUP BY postid
) c ON t.num = c.postid
WHERE categoryId = #{categoryId}
ORDER BY t.num DESC
OFFSET #{pageNum} ROWS FETCH NEXT 10 ROWS ONLY
</select>
<select id="selectList" parameterType="map" resultType="com.example.post.model.Post">
SELECT t.*, c.commentNum
FROM TEST1 t
LEFT JOIN (
SELECT postid, COUNT(*) AS commentNum
FROM COMMENTS1
GROUP BY postid
) c ON t.num = c.postid
<where>
<if test="sp.startDate != null and !sp.startDate.isEmpty()">
AND CONVERT(varchar, DATE, 23) BETWEEN #{sp.startDate} AND #{sp.endDate}
</if>
<if test="sp.searchWord != null and !sp.searchWord.isEmpty()">
AND ${sp.searchType} LIKE CONCAT('%', #{sp.searchWord}, '%')
</if>
</where>
AND categoryId = #{sp.categoryId}
ORDER BY num DESC
OFFSET #{pageNum} ROWS FETCH NEXT 10 ROWS ONLY
</select>
<select id="selectTotalPostCount" resultType="int">
SELECT COUNT(*) AS postCount FROM TEST1 WHERE categoryId = #{categoryId}
</select>
<select id="selectPostCount" parameterType="map" resultType="int">
SELECT COUNT(*) AS postCount FROM TEST1
<where>
<if test="sp.startDate != null and !sp.startDate.isEmpty()">
AND CONVERT(varchar, DATE, 23) BETWEEN #{sp.startDate} AND #{sp.endDate}
</if>
<if test="sp.searchWord != null and !sp.searchWord.isEmpty()">
AND ${sp.searchType} LIKE CONCAT('%', #{sp.searchWord}, '%')
</if>
</where>
AND categoryId = #{sp.categoryId}
</select>
<select id="selectPost" resultType="com.example.post.model.Post">
SELECT * FROM TEST1 WHERE NUM = #{num}
</select>
<update id="modifyPost" parameterType="com.example.post.model.Post">
UPDATE TEST1 SET Author = #{author}, Contents = #{contents}, Title = #{title} WHERE num = #{num}
</update>
<insert id="insertPost" parameterType="com.example.post.model.Post" useGeneratedKeys="true" keyProperty="num">
INSERT INTO TEST1 (Author, Contents, Title, categoryId) VALUES (#{author}, #{contents}, #{title}, #{categoryId})
</insert>
<delete id="deletePost" parameterType="int">
DELETE FROM TEST1 WHERE num = #{num}
</delete>
<!-- 이 아래는 게시판 관리 (Category)-->
<insert id="insertCateogry" parameterType="com.example.post.model.Category">
INSERT INTO board_category1 (categoryName, description)VALUES (#{categoryName}, #{description})
</insert>
<select id="selectCategory" resultType="com.example.post.model.Category" parameterType="String">
SELECT bc.categoryId, br.roleId, bc.categoryName
FROM board_category1 bc
RIGHT JOIN (
SELECT br.categoryId, MIN(ru.roleId) AS roleId
FROM board_role1 br
RIGHT JOIN (
SELECT u.username, ru.roleId
FROM user1 u
RIGHT JOIN (
SELECT * FROM role_user1 where username = #{username}
) ru ON u.username = ru.username
) ru ON br.roleId = ru.roleId
GROUP BY br.categoryId
) br ON bc.categoryId = br.categoryId;
</select>
<select id="selectAllCategory" resultType="com.example.post.model.Category">
SELECT * from board_category1;
</select>
<select id="selectCategoryName" parameterType="int" resultType="String">
SELECT categoryName FROM board_category1 WHERE categoryId = #{categoryId}
</select>
<delete id="deleteCategoryByRoleId" parameterType="int">
DELETE FROM board_role1 WHERE roleId = #{roleId}
</delete>
<insert id="insertRoleByCategory" parameterType="map">
INSERT INTO board_role1 (categoryId, roleId) VALUES (#{categoryId}, #{roleId})
</insert>
<select id="selectCategoryByRoleId" parameterType="int" resultType="com.example.post.model.Category">
SELECT bc.categoryId, bc.categoryName, bc.description, br.roleId FROM board_category1 bc
LEFT JOIN ( SELECT * FROM board_role1 br WHERE roleId = #{roleId} ) br ON bc.categoryId = br.categoryId;
</select>
<select id="selectCategoryInfo" parameterType="int" resultType="com.example.post.model.Category">
SELECT * FROM board_category1 WHERE categoryId = #{categoryId}
</select>
<update id="modifyBoardInfo" parameterType="com.example.post.model.Category">
UPDATE board_category1 SET categoryName = #{categoryName}, description = #{description} WHERE categoryId = #{categoryId}
</update>
</mapper>위 List 조건으로 categoryId 를 추가하였고,
로그인시 접근 할 수 있는 Category 를 보여주는 selectCategory 의 경우 3번의 조인을 통해서 구하고 있다.
mapper-user.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="UserMapper">
<select id="selectByUsername" parameterType="String" resultType="com.example.post.model.User">
SELECT * FROM USER1 WHERE username = #{username}
</select>
<select id="selectRoleByUsername" parameterType="String" resultType="com.example.post.model.Role">
SELECT r.*
FROM role1 r
JOIN role_user1 ru ON r.roleId = ru.roleId
WHERE ru.username = #{username}
</select>
<insert id="insertUser" parameterType="com.example.post.model.User">
INSERT INTO USER1 (username, password, name) values (#{username}, #{password}, #{name})
</insert>
<select id="selectAllUser" resultType="com.example.post.model.User">
SELECT * FROM USER1;
</select>
<select id="selectAllRole" resultType="com.example.post.model.Role">
SELECT * FROM ROLE1;
</select>
<insert id="insertRole" parameterType="com.example.post.model.Role">
INSERT INTO ROLE1 (roleName, description) values (#{roleName}, #{description})
</insert>
<select id="selectRoleInfo" parameterType="int" resultType="com.example.post.model.Role">
SELECT * FROM ROLE1 WHERE roleId = #{roleId}
</select>
<delete id="deleteUserByRoleId" parameterType="int">
DELETE FROM role_user1 WHERE roleId = #{roleId}
</delete>
<insert id="insertRoleByUsername" parameterType="map">
INSERT INTO role_user1 (username, roleId) VALUES (#{username}, #{roleId})
</insert>
<select id="selectUserByRoleId" parameterType="int" resultType="com.example.post.model.User">
select u.username, u.name, c.roleId from user1 u
left join ( select * from role_user1 where roleId = #{roleId} ) c on u.username = c.username;
</select>
<select id="selectUserInfo" parameterType="String" resultType="com.example.post.model.User">
SELECT * FROM user1 WHERE username = #{username}
</select>
<delete id="deleteRoleUser" parameterType="int">
DELETE FROM role_user1 WHERE roleId = #{roleId}
</delete>
<delete id="deleteRoleCategory" parameterType="int">
DELETE FROM board_role1 WHERE roleId = #{roleId}
</delete>
<delete id="deleteRole" parameterType="int">
DELETE FROM role1 WHERE roleId = #{roleId}
</delete>
<update id="modifyUserInfo" parameterType="com.example.post.model.User">
UPdate USER1 SET password = #{password}, name = #{name} WHERE username = #{username}
</update>
<delete id="deleteUser" parameterType="String">
DELETE FROM user1 WHERE username = #{username}
</delete>
<delete id="deleteRoleUserByUsername" parameterType="String">
DELETE FROM role_user1 WHERE username = #{username}
</delete>
</mapper>내용은 비슷하지만 Role 관련 내용 추가를 하였다.
이어서 위 SelectList 내용에 categoryId 파라미터가 추가되었으므로,
애초에 페이지단에서부터 CategoryId 도 가져와야만 한다.
@GetMapping("/list")
public ModelAndView postList(HttpServletRequest request) {
String username = securityUtil.getCurrentUsername();
String categoryId = request.getParameter("categoryId");
String searchWord = request.getParameter("searchWord");
String searchType = request.getParameter("searchType");
String startDate = request.getParameter("startDate");
String endDate = request.getParameter("endDate");
PostResult pResult = new PostResult();
SearchParameter sp = new SearchParameter();
Map params = new HashMap();
int pageNum = 0;
int totalPageNum = 0;
if(request.getParameter("pageNum") !=null && !request.getParameter("pageNum").isEmpty()) {
pageNum = Integer.parseInt(request.getParameter("pageNum"))-1;
}
params.put("pageNum", pageNum);
//searchParameter
if (searchWord != null || startDate != null || endDate != null) {
sp.setEndDate(endDate);
sp.setSearchType(searchType);
sp.setSearchWord(searchWord);
sp.setStartDate(startDate);
sp.setCategoryId(Integer.parseInt(categoryId));
pResult = postListService.getList(sp, pageNum*10);
} else {
pResult = postListService.getList(categoryId, pageNum*10);
}
//글 목록
mav.addObject("list", pResult.getPostList());
//페이지넘버들
mav.addObject("curPageNum", pageNum+1);
mav.addObject("totalPageNum", pResult.getTotalPostNum());
mav.addObject("param", sp);
mav.addObject("categories", postService.findCategory(username));
mav.setViewName("list");
return mav;
}username 은 ~~님 환영합니다! 띄우려고 보냄.
당연히 JSP 에서도 받고 보내는 내용이 달라졌으므로 수정해준다.
list.jsp
<%@ 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="sec" uri="http://www.springframework.org/security/tags" %>
<!DOCTYPE html>
<html>
<head>
<script src="https://code.jquery.com/jquery-3.6.0.min.js"></script>
<meta charset="UTF-8">
<title>글 리스트</title>
<link rel="stylesheet" href="/css/main.css">
</head>
<body>
<h2><a href="${pageContext.request.contextPath}/">조회화면</a></h2>
<sec:authorize access="hasRole('ROLE_ADMIN')">
관리자로 로그인 하셨습니다. <a href="${pageContext.request.contextPath}/admin/userList">관리화면</a>
</sec:authorize>
<br><a href="/logout">로그아웃</a>
<hr>
<main>
<nav>
<ul>
<c:forEach items="${categories}" var="category">
<li><a href="${pageContext.request.contextPath}/list?categoryId=${category.categoryId}">${category.categoryName}</a></li>
</c:forEach>
</ul>
</nav>
<section id="content">
검색
<form action="${pageContext.request.contextPath}/list" onsubmit="return checkDate()">
<select name="searchType">
<option value="title">제목</option>
<option value="contents">내용</option>
<option value="author">작성자</option>
</select>
시작일자 <input type="date" id="startDate" name="startDate">
종료일자 <input type="date" id="endDate" name="endDate">
<input type="text" name="searchWord">
<input type="hidden" name="categoryId" value="${param.categoryId}">
<sec:csrfInput />
<input type="submit" value="검색" />
</form>
<c:set var="categoryId" value="${param.categoryId}"/>
<a href="${pageContext.request.contextPath}/write?categoryId=${categoryId}">글쓰기</a>
<table>
<tr>
<td>게시글번호</td><td>제목</td><td>작성자</td><td>날짜</td> <!-- 5개 -->
</tr>
<c:forEach items="${list}" var="post">
<tr>
<td>${post.num}</td><td><a href="${pageContext.request.contextPath}/content?num=${post.num}">${post.title}
<c:if test="${post.commentNum!=null}">[${post.commentNum}]</a></c:if>
</td><td>${post.author}</td><td>${post.date}</td>
</tr>
</c:forEach>
</tr>
</table>
<hr>
<div class="index">
<c:set var="beginPage" value="${curPageNum - (curPageNum % 10)}" />
<c:set var="endPage" value="${beginPage + 10 <= totalPageNum ? beginPage + 9 : totalPageNum}" />
<c:set var="beginPage" value="${beginPage == 0 ? 1 : beginPage}" />
<c:set var="prevPage" value="${beginPage < 9 ? 0 : beginPage-1}" />
<c:set var="nextPage" value="${endPage < totalPageNum - (totalPageNum % 10) ? (beginPage == 1 ? beginPage + 9 : beginPage + 10) : 0}" />
<!-- beginPage 가 1인경우는 한자리수인데, 0부터 시작할수는 없어서 1부터 시작하므로 첫페이지 다음 버튼은 9를 더해야함. 그 뒤로는 10부터 시작이라 10씩 더해야 자릿수가 바뀜 -->
<c:choose>
<c:when test="${not empty param.searchWord and not empty param.startDate}">
<c:if test="${prevPage ne 0}">
<a href="${pageContext.request.contextPath}?startDate=${param.startDate}&endDate=${param.endDate}&searchWord=${param.searchWord}&searchType=${param.searchType}&categoryId=${categoryId}&pageNum=${prevPage}&">이전</a>
</c:if>
<c:forEach var="i" begin="${beginPage}" end="${endPage}">
<a href="${pageContext.request.contextPath}?startDate=${param.startDate}&endDate=${param.endDate}&searchWord=${param.searchWord}&searchType=${param.searchType}&categoryId=${categoryId}&pageNum=${i}">${i}</a>
</c:forEach>
<c:if test="${nextPage ne 0}">
<a href="${pageContext.request.contextPath}?startDate=${param.startDate}&endDate=${param.endDate}&searchWord=${param.searchWord}&searchType=${param.searchType}&categoryId=${categoryId}&pageNum=${nextPage}">다음</a>
</c:if>
</c:when>
<c:when test="${not empty param.searchWord}">
<c:if test="${prevPage ne 0}">
<a href="${pageContext.request.contextPath}?searchWord=${param.searchWord}&searchType=${param.searchType}&categoryId=${categoryId}&pageNum=${prevPage}">이전</a>
</c:if>
<c:forEach var="i" begin="${beginPage}" end="${endPage}">
<a href="${pageContext.request.contextPath}?searchWord=${param.searchWord}&searchType=${param.searchType}&categoryId=${categoryId}&pageNum=${i}">${i}</a>
</c:forEach>
<c:if test="${nextPage ne 0}">
<a href="${pageContext.request.contextPath}?searchWord=${param.searchWord}&searchType=${param.searchType}&categoryId=${categoryId}&pageNum=${nextPage}">다음</a>
</c:if>
</c:when>
<c:when test="${not empty param.startDate}">
<c:if test="${prevPage ne 0}">
<a href="${pageContext.request.contextPath}?startDate=${param.startDate}&endDate=${param.endDate}&categoryId=${categoryId}&pageNum=${prevPage}">이전</a>
</c:if>
<c:forEach var="i" begin="${beginPage}" end="${endPage}">
<a href="${pageContext.request.contextPath}?startDate=${param.startDate}&endDate=${param.endDate}&categoryId=${categoryId}&pageNum=${i}">${i}</a>
</c:forEach>
<c:if test="${nextPage ne 0}">
<a href="${pageContext.request.contextPath}?startDate=${param.startDate}&endDate=${param.endDate}&categoryId=${categoryId}&pageNum=${nextPage}">다음</a>
</c:if>
</c:when>
<c:otherwise>
<c:if test="${prevPage ne 0}">
<a href="${pageContext.request.contextPath}?categoryId=${categoryId}&pageNum=${prevPage}">이전</a>
</c:if>
<c:forEach var="i" begin="${beginPage}" end="${endPage}">
<a href="${pageContext.request.contextPath}?categoryId=${categoryId}&pageNum=${i}">${i}</a>
</c:forEach>
<c:if test="${nextPage ne 0}">
<a href="${pageContext.request.contextPath}?categoryId=${categoryId}&pageNum=${nextPage}">다음</a>
</c:if>
</c:otherwise>
</c:choose>
</div>
<div id="notification-container"></div>
</section>
</main>
<script>
function checkDate() {
const startDate = document.getElementById("startDate").value;
const endDate = document.getElementById("endDate").value;
if (startDate && !endDate) {
alert("종료일자를 입력해주세요.");
return false;
}
if (!startDate && endDate) {
alert("시작일자를 입력해주세요.");
return false;
}
if (startDate && endDate && new Date(startDate) > new Date(endDate)) {
alert("시작일자가 종료일자보다 늦을 수 없습니다.");
return false;
}
return true;
}
$('#userList').click(function() {
location.reload();
});
$('#addUser').click(function() {
$.ajax({
url: '/admin/addUserForm',
method: 'GET',
success: function(data) {
$('#content').html(data);
}
});
});
</script>
</body>
<script src="https://cdnjs.cloudflare.com/ajax/libs/sockjs-client/1.5.1/sockjs.min.js"></script>
<script src="https://cdnjs.cloudflare.com/ajax/libs/stomp.js/2.3.3/stomp.min.js"></script>
<script src="${pageContext.request.contextPath}/script/notification.js"></script>
</html>중간에 사용한 SecurityUtil 코드
package com.example.post.utils;
import java.util.List;
import java.util.stream.Collectors;
import org.springframework.security.core.Authentication;
import org.springframework.security.core.GrantedAuthority;
import org.springframework.security.core.context.SecurityContextHolder;
import org.springframework.security.core.userdetails.UserDetails;
import org.springframework.stereotype.Component;
@Component
public class SecurityUtil {
public String getCurrentUsername() {
Authentication authentication = SecurityContextHolder.getContext().getAuthentication();
if (authentication != null && authentication.isAuthenticated()) {
Object principal = authentication.getPrincipal();
if (principal instanceof UserDetails) {
return ((UserDetails) principal).getUsername();
} else {
return principal.toString();
}
}
return null;
}
public List<String> getCurrentUserRoles() {
Authentication authentication = SecurityContextHolder.getContext().getAuthentication();
if (authentication != null && authentication.isAuthenticated()) {
return authentication.getAuthorities().stream()
.map(GrantedAuthority::getAuthority)
.collect(Collectors.toList());
}
return null;
}
}