본문 바로가기
ICIA 수업일지

2021.10.06 수업일지(Spring Framework 이용 게시판 만들기)

by 주성씨 2021. 10. 6.

- 어제 작성했던 setup project를 복사해서 ex02로 하고 서버 모듈을 설정해주겠다.

- 웹서버 구동 확인 DB서버 구동 확인을 한다.

1. 웹 서버 구동 확인

 

2. DB서버 구동 확인

 

 

- DB에 대한 설정

/ex02/src/main/webapp/WEB-INF/spring/root-context.xml

.....
	<bean id="dataSource"
		class="org.springframework.jdbc.datasource.DriverManagerDataSource">
		<property name="driverClassName" value="net.sf.log4jdbc.sql.jdbcapi.DriverSpy"></property>
		<property name="url" value="jdbc:log4jdbc:mysql://127.0.0.1:3306/boarddb"></property>
		<property name="username" value="board"></property>
		<property name="password" value="pass"></property>
	</bean>
    	<bean id="sqlSessionFactory" class="org.mybatis.spring.SqlSessionFactoryBean">
		<property name="dataSource" ref="dataSource" />
        <!-- mapper에 있는 모든 경로의 xml 파일을 지정해주겠다. -->
		<property name="mapperLocations" value="classpath:/mapper/**/*.xml" />
	</bean>
    	<bean id="mapper" class="org.mybatis.spring.mapper.MapperScannerConfigurer">
        <!--  -->
		<property name="basePackage" value="com.example.mapper" />
		<property name="sqlSessionFactoryBeanName" value="sqlSessionFactory" />
	</bean>
.....

 

- CURD, 페이징, 검색 작업을 어제와 같이 해보도록 하겠다.

- 우선 DB에 데이터를 확인해보겠다.

- go to Mysql

select * from tbl_board order by bno desc;

 

- mapper .xml 파일을 생성하겠다.

- /ex02/src/main/resources/mapper/(new)BoardMapper.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">
<!-- namespace를 통해서 boardmapper interface랑 연결 -->
<mapper namespace="com.example.mapper.BoardMapper">
	<!-- select 는 return 타입을 설정해줘야 한다. -->
	<select id="list" resultType="com.example.domain.BoardVO">
		select * from tbl_board order by bno desc limit 0,10
	</select>
</mapper>

 

- select return을 위해서 VO를 위한 패키지를 만들어 준다.

- /ex02/src/main/java/(new)com/example/domain/(new)BoardVO.java

package com.example.domain;

import java.util.Date;

import com.fasterxml.jackson.annotation.JsonFormat;

public class BoardVO {
	private int bno;
	private String title;
	private String content;
	private String writer;
	
	@JsonFormat(pattern="yyyy-MM-dd HH:mm:ss", timezone="Asia/Seoul")
	private Date regdate;
	@JsonFormat(pattern="yyyy-MM-dd HH:mm:ss", timezone="Asia/Seoul")
	private Date updatedate;
..... get, set, tostring

 

- board에 대한 인터페이스를 만들겠다.

- /ex02/src/main/java/com/example/mapper/(new)BoardMapper.java

package com.example.mapper;

import java.util.List;

import com.example.domain.BoardVO;

public interface BoardMapper {
	public List<BoardVO> list();
}

 

- 해당 메서드를 테스트하기 위해서 테스트 클래스를 만들겠다.

- /ex02/src/test/java/com/example/controller/(new)BoardTest.java

package com.example.controller;

import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.test.context.ContextConfiguration;
import org.springframework.test.context.junit4.SpringJUnit4ClassRunner;

import com.example.mapper.BoardMapper;
import com.example.mapper.MysqlMapper;

@RunWith(SpringJUnit4ClassRunner.class) // 먼저 SpringJUnit4ClassRunner.class
										// import한다.
@ContextConfiguration(locations={"file:src/main/webapp/WEB-INF/spring/**/*.xml"})
public class BoardTest {
	// 자동으로 연결
	@Autowired
	private BoardMapper mapper;

	// 연결해서 getList을 테스트
	@Test
	public void getList() {
		mapper.list();
	}
}

 

테스트 확인

 

- 테이블의 총 갯수를 테스트 출력해보도록 하겠다.

- /ex02/src/test/java/com/example/controller/BoardTest.java

package com.example.mapper;

import java.util.List;

import com.example.domain.BoardVO;

public interface BoardMapper {
	public List<BoardVO> list();
	public int totalCount();
}

 

- /ex02/src/main/resources/mapper/BoardMapper.xml

.....
	<select id="totalCount" resultType="int">
		select count(*) from tbl_board
	</select>
</mapper>

 

- /ex02/src/test/java/com/example/controller/BoardTest.java

.....
	// total갯수 테스트 출력
	@Test
	public void getTotal(){
		mapper.totalCount();
	}
}

 

테스트 확인

 

- 모델(Model)을 만들었으니 이제 컨트롤러(Controller)(java class)를 만들겠다.

- /ex02/src/main/java/com/example/controller/(new)BoardController.java

package com.example.controller;

import java.util.List;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
//import org.springframework.web.bind.annotation.RequestBody;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.ResponseBody;

import com.example.domain.BoardVO;
import com.example.mapper.BoardMapper;

// 컨트롤러 지정
@Controller
// 선행 루트 지정
@RequestMapping("/board")
public class BoardController {
	@Autowired
	BoardMapper mapper;
	
	// 1. 목록 가지고 오기
	@RequestMapping("/list.json")
	// 데이터를 출력할 것이기 때문에
	@ResponseBody
	public List<BoardVO> getListJson(){
		return mapper.list();
	}
}

 

데이터 get 확인

 

- 데이터를 hash 형태로 넘겨주도록 하겠다.

.....
	// 1. 목록 가지고 오기
	@RequestMapping("/list.json")
	// 데이터를 출력할 것이기 때문에
	@ResponseBody
	public HashMap<String, Object> getListJson(){
		HashMap<String, Object> map = new HashMap<>();
		map.put("list", mapper.list());
		map.put("total", mapper.totalCount());
		return map;
	}
}

 

확인

 

- 목록을 보여주는 list 페이지로 이동 할 수 있도록 하겠다.

.....
	// 2. list 페이지로 이동
	@RequestMapping("/list")
	public String listPage(){
		// 페이지 리턴
		return "/board/list";
	}
	
}

 

- /ex02/src/main/webapp/resources/home.css

@CHARSET "UTF-8";

@font-face {
	font-family: 'SpoqaHanSansNeo-Regular';
	src:
		url('https://cdn.jsdelivr.net/gh/projectnoonnu/noonfonts_2108@1.1/SpoqaHanSansNeo-Regular.woff')
		format('woff');
	font-weight: normal;
	font-style: normal;
}

body {
	font-family: 'SpoqaHanSansNeo-Regular';
}

h1 {
	text-align: center;
}

table {
	border-collapse: collapse;
	margin: 0px auto;
}

td {
	border: 1px solid gray;
}

.title {
	background: gray;
	color: white;
	text-align: center;
}

.row:hover {
	cursor: pointer;
	background: gray;
	color: white;
}

.btn {
	text-align: center;
}

 

- /ex02/src/main/webapp/WEB-INF/views/(new)board/(New)list.jsp

<%@ page language="java" contentType="text/html; charset=UTF-8"
	pageEncoding="UTF-8"%>
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
<link rel="stylesheet" href="/resources/home.css" />
<script src="http://code.jquery.com/jquery-3.1.1.min.js"></script>
<script
	src="https://cdnjs.cloudflare.com/ajax/libs/handlebars.js/3.0.1/handlebars.js"></script>
<title>게시판 관리</title>
</head>
<body>
	<h1>[게시판 관리]</h1>
	<table id="tbl"></table>
	<script id="temp" type="text/x-handlebars-template">
	<tr class="title">
		<td width="100">No.</td>
		<td width="200">제목</td>
		<td width="100">작성자</td>
		<td width="200">작성일</td>
		<td width="200">수정일</td>
	</tr>
	{{#each list}}
		<tr class="row">
			<td>{{bno}}</td>
			<td>{{title}}</td>
			<td>{{writer}}</td>
			<td>{{regdate}}</td>
			<td>{{updatedate}}</td>
		</tr>
	{{/each}}
	</script>
</body>
<script>
	getList();
	function getList() {
		$.ajax({

			type : 'get',
			url : '/board/list.json',
			dataType : 'json',
			success : function(data) {
				var temp = Handlebars.compile($('#temp').html());
				$("#tbl").html(temp(data));
			}
		})
	}
</script>
</html>

 

확인

 

 

- 이제 페이징을 해보도록 하겠다. 교과서 page11에 있는 코드를 복사해 아래의 경로해 생성해주도록 하겠다.

- /ex02/src/main/java/com/example/domain/(new)Criteria.java

package com.example.domain;

public class PageMaker {
	private int totalCount;
	private int startPage;
	private int endPage;
	private boolean prev;
	private boolean next;
	private int displayPageNum = 10;
	private Criteria cri;

	public void setCri(Criteria cri) {
		this.cri = cri;
	}

	public void setTotalCount(int totalCount) {
		this.totalCount = totalCount;
		calcData();
	}

	private void calcData() {
		endPage = (int) (Math.ceil(cri.getPage() / (double) displayPageNum) * displayPageNum);
		startPage = (endPage - displayPageNum) + 1;
		int tempEndPage = (int) (Math.ceil(totalCount / (double) cri.getPerPageNum()));
		if (endPage > tempEndPage) {
			endPage = tempEndPage;
		}
		prev = startPage == 1 ? false : true;
		next = endPage * cri.getPerPageNum() >= totalCount ? false : true;
	}

	public int getStartPage() {
		return startPage;
	}

	public void setStartPage(int startPage) {
		this.startPage = startPage;
	}

	public int getEndPage() {
		return endPage;
	}

	public void setEndPage(int endPage) {
		this.endPage = endPage;
	}

	public boolean isPrev() {
		return prev;
	}

	public void setPrev(boolean prev) {
		this.prev = prev;
	}

	public boolean isNext() {
		return next;
	}

	public void setNext(boolean next) {
		this.next = next;
	}

	public int getDisplayPageNum() {
		return displayPageNum;
	}

	public void setDisplayPageNum(int displayPageNum) {
		this.displayPageNum = displayPageNum;
	}

	public int getTotalCount() {
		return totalCount;
	}

	public Criteria getCri() {
		return cri;
	}
}

 

- /ex02/src/main/java/com/example/domain/(new)PageMaker.java

package com.example.domain;

// 페이지를 일렬로 나열할 수 있게 해주는 클래스
public class PageMaker {
	private int totalCount; // 전체 데이터 갯수
	private int startPage; // 시작 페이지 번호
	private int endPage; // 마지막 페이지 번호
	private boolean prev; // 이전 버튼
	private boolean next; // 다음 버튼
	private int displayPageNum = 10; // 페이지에 보여지는 페이지의 갯수
	private Criteria cri;

	public void setCri(Criteria cri) {
		this.cri = cri;
	}

	public void setTotalCount(int totalCount) {
		this.totalCount = totalCount;
		calcData();
	}

	private void calcData() {
		endPage = (int) (Math.ceil(cri.getPage() / (double) displayPageNum) * displayPageNum);
		startPage = (endPage - displayPageNum) + 1;
		int tempEndPage = (int) (Math.ceil(totalCount / (double) cri.getPerPageNum()));
		if (endPage > tempEndPage) {
			endPage = tempEndPage;
		}
		prev = startPage == 1 ? false : true;
		next = endPage * cri.getPerPageNum() >= totalCount ? false : true;
	}

	public int getStartPage() {
		return startPage;
	}

	public void setStartPage(int startPage) {
		this.startPage = startPage;
	}

	public int getEndPage() {
		return endPage;
	}

	public void setEndPage(int endPage) {
		this.endPage = endPage;
	}

	public boolean isPrev() {
		return prev;
	}

	public void setPrev(boolean prev) {
		this.prev = prev;
	}

	public boolean isNext() {
		return next;
	}

	public void setNext(boolean next) {
		this.next = next;
	}

	public int getDisplayPageNum() {
		return displayPageNum;
	}

	public void setDisplayPageNum(int displayPageNum) {
		this.displayPageNum = displayPageNum;
	}

	public int getTotalCount() {
		return totalCount;
	}

	public Criteria getCri() {
		return cri;
	}
}

 

- 이제 이를 이용해 리스트를 바꿔보도록 하겠다.

- /ex02/src/main/java/com/example/mapper/BoardMapper.java

package com.example.mapper;

import java.util.List;

import com.example.domain.BoardVO;
import com.example.domain.Criteria;

public interface BoardMapper {
	public List<BoardVO> list(Criteria cri);
	public int totalCount();
}

 

- /ex02/src/main/java/com/example/controller/BoardController.java

....
	// 1. 목록 가지고 오기
	@RequestMapping("/list.json")
	// 데이터를 출력할 것이기 때문에 responsebody를 붙여준다.
	@ResponseBody
	public HashMap<String, Object> getListJson(Criteria cri){
		HashMap<String, Object> map = new HashMap<>();
		cri.setPerPageNum(10);
		map.put("list", mapper.list(cri));
		map.put("total", mapper.totalCount());
		
		PageMaker pm = new PageMaker();
		pm.setCri(cri);
		pm.setTotalCount(mapper.totalCount());
		
		map.put("pm", pm);
		map.put("cri", cri);
		
		// data return
		return map;
	}
....

 

- /ex02/src/main/resources/mapper/BoardMapper.xml

.....
	<select id="list" resultType="com.example.domain.BoardVO">
		select * from tbl_board 
		order by bno desc 
		limit #{pageStart},#{perPageNum}
	</select>
.....

 

 

- /ex02/src/main/webapp/WEB-INF/views/board/list.jsp

<%@ page language="java" contentType="text/html; charset=UTF-8"
	pageEncoding="UTF-8"%>
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
<link rel="stylesheet" href="/resources/home.css" />
<script src="http://code.jquery.com/jquery-3.1.1.min.js"></script>
<script
	src="https://cdnjs.cloudflare.com/ajax/libs/handlebars.js/3.0.1/handlebars.js"></script>
<title>게시판 관리</title>
<style>
.active {
	color: red;
}
#pagination{
	margin: 0px auto;
	margin-top: 10px;
	text-align: center;
}
a{
	color: black;
	text-decoration: none;
	padding: 10px 10px 10px 10px;
	
}
</style>
</head>
<body>
	<h1>[게시판 관리]</h1>
	<table id="tbl"></table>
	<script id="temp" type="text/x-handlebars-template">
	<tr class="title">
		<td width="100">No.</td>
		<td width="200">제목</td>
		<td width="100">작성자</td>
		<td width="200">작성일</td>
		<td width="200">수정일</td>
	</tr>
	{{#each list}}
		<tr class="row">
			<td>{{bno}}</td>
			<td>{{title}}</td>
			<td>{{writer}}</td>
			<td>{{regdate}}</td>
			<td>{{updatedate}}</td>
		</tr>
	{{/each}}
	</script>
	<div id="pagination"></div>
</body>
<script>
	var page = 1;
	getList();
	function getList() {
		$.ajax({
			type : 'get',
			url : '/board/list.json',
			dataType : 'json',
			data : {
				"page" : page
			},
			success : function(data) {
				var temp = Handlebars.compile($('#temp').html());
				$("#tbl").html(temp(data));

				//페이지 목록 생성
				var str = "";
				if (data.pm.prev)
					str += "<a href='" + (data.pm.startPage - 1)
							+ "'>이전</a>&nbsp;";
				for (var i = data.pm.startPage; i <= data.pm.endPage; i++) {
					if (data.cri.page == i) {
						str += "<a href='" + i + "' class='active'>" + i
								+ "</a>&nbsp;";
					} else {
						str += "<a href='" + i + "'>" + i + "</a>&nbsp;";
					}
				}
				if (data.pm.next)
					str += "<a href='" + (data.pm.endPage + 1) + "'>다음</a>";
				$("#pagination").html(str);
			}
		})
	}

	// 특정 페이지 번호를 클릭한 경우
	$("#pagination").on("click", "a", function(e) {
		e.preventDefault();
		page = $(this).attr("href");
		getList();
	})
</script>
</html>

 

확인

 

 

- 이제 조건 검색할 수 있도록 하겠다.

- /ex02/src/main/webapp/WEB-INF/views/board/list.jsp

.....
<body>
	<h1>[게시판 관리]</h1>
	<div id="condition">
		<select id="searchType">
			<option value="title">제목</option>
			<option value="content">내용</option>
			<option value="writer">작성자</option>
		</select>
		<input type="text" id="keyword" placeholder="검색어"/>
		검색수 : <span id="total"></span>
		<hr/>
	</div>
	<table id="tbl"></table>
	<script id="temp" type="text/x-handlebars-template">
	<tr class="title">
		<td width="100">No.</td>
		<td width="200">제목</td>
		<td width="100">작성자</td>
		<td width="200">작성일</td>
		<td width="200">수정일</td>
	</tr>
	{{#each list}}
		<tr class="row">
			<td>{{bno}}</td>
			<td>{{title}}</td>
			<td>{{writer}}</td>
			<td>{{regdate}}</td>
			<td>{{updatedate}}</td>
		</tr>
	{{/each}}
	</script>
	<div id="pagination"></div>
</body>
<script>
	var page = 1;
	getList();
	
	// 검색시 page 초기화
	$('#keyword').on('keypress',function(e){
		if(e.keyCode==13){
			page=1;
			getList();
		}
	})
	
	function getList() {
		var keyword = $('#keyword').val();
		var searchType = $('#searchType').val();
		
		$.ajax({
			type : 'get',
			url : '/board/list.json',
			dataType : 'json',
			data : {
				"page" : page,
				"keyword" : keyword,
				"searchType" : searchType
			},
			success : function(data) {
				var temp = Handlebars.compile($('#temp').html());
				$("#tbl").html(temp(data));

				$("#total").html(data.pm.totalCount);
				//페이지 목록 생성
				var str = "";
				if (data.pm.prev)
					str += "<a href='" + (data.pm.startPage - 1)
							+ "'>이전</a>&nbsp;";
				for (var i = data.pm.startPage; i <= data.pm.endPage; i++) {
					if (data.cri.page == i) {
						str += "<a href='" + i + "' class='active'>" + i
								+ "</a>&nbsp;";
					} else {
						str += "<a href='" + i + "'>" + i + "</a>&nbsp;";
					}
				}
				if (data.pm.next)
					str += "<a href='" + (data.pm.endPage + 1) + "'>다음</a>";
				$("#pagination").html(str);
			}
		})
	}

	// 특정 페이지 번호를 클릭한 경우
	$("#pagination").on("click", "a", function(e) {
		e.preventDefault();
		page = $(this).attr("href");
		getList();
	})
</script>
</html>

 

- /ex02/src/main/resources/mapper/BoardMapper.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">
<!-- namespace를 통해서 boardmapper interface랑 연결 -->
<mapper namespace="com.example.mapper.BoardMapper">
	<!-- select 는 return 타입을 설정해줘야 한다. -->
	<select id="list" resultType="com.example.domain.BoardVO">
		select * from tbl_board
		<if test="searchType=='title'">  <<<---
			where title like concat('%',#{keyword},'%')
		</if>
		<if test="searchType=='content'">
			where content like concat('%',#{keyword},'%')
		</if>
		<if test="searchType=='writer'">
			where writer like concat('%',#{keyword},'%')
		</if>
		order by bno desc
		limit #{pageStart},#{perPageNum}
	</select>
	<select id="totalCount" resultType="int">
		select count(*) from
		tbl_board
		<if test="searchType=='title'">  <<<---
			where title like concat('%',#{keyword},'%')
		</if>
		<if test="searchType=='content'">
			where content like concat('%',#{keyword},'%')
		</if>
		<if test="searchType=='writer'">
			where writer like concat('%',#{keyword},'%')
		</if>
	</select>
</mapper>

 

- /ex02/src/main/java/com/example/mapper/BoardMapper.java

package com.example.mapper;

import java.util.List;

import com.example.domain.BoardVO;
import com.example.domain.Criteria;

public interface BoardMapper {
	public List<BoardVO> list(Criteria cri);
	public int totalCount(Criteria cri);  <<<---
}

 

- /ex02/src/main/java/com/example/controller/BoardController.java

.....
// 컨트롤러 지정
@Controller
// 선행 루트 지정
@RequestMapping("/board")
public class BoardController {
	@Autowired
	BoardMapper mapper;
	
	// 1. 목록 가지고 오기
	@RequestMapping("/list.json")
	// 데이터를 출력할 것이기 때문에 responsebody를 붙여준다.
	@ResponseBody
	public HashMap<String, Object> getListJson(Criteria cri){
		HashMap<String, Object> map = new HashMap<>();
		cri.setPerPageNum(10);
		map.put("list", mapper.list(cri)); <<<---
		
		PageMaker pm = new PageMaker();
		pm.setCri(cri);
		pm.setTotalCount(mapper.totalCount(cri)); <<<---
		
		map.put("pm", pm);
		map.put("cri", cri);
		
		// data return
		return map;
	}
	
	// 2. list 페이지로 이동
	@RequestMapping("/list")
	public String listPage(){
		// 페이지 리턴
		return "/board/list";
	}
	
}

 

확인

 

- 페이지 목록 생성 스크립트를 다른 모듈화 해서 재사용가능하도록 하겠다.

- /ex02/src/main/webapp/resources/(new)pagination.js

// 페이지 목록 생성
function getPagination(data){
	var str = "";
	if (data.pm.prev)
		str += "<a href='" + (data.pm.startPage - 1)
				+ "'>이전</a>&nbsp;";
	for (var i = data.pm.startPage; i <= data.pm.endPage; i++) {
		if (data.cri.page == i) {
			str += "<a href='" + i + "' class='active'>" + i
					+ "</a>&nbsp;";
		} else {
			str += "<a href='" + i + "'>" + i + "</a>&nbsp;";
		}
	}
	if (data.pm.next)
		str += "<a href='" + (data.pm.endPage + 1) + "'>다음</a>";
	return str;
}

 

- /ex02/src/main/webapp/WEB-INF/views/board/list.jsp

.....
	<div id="pagination" class="pagination"></div>
</body>
<script>
	var page = 1;
	getList();
	
	// 검색시 page 초기화
	$('#keyword').on('keypress',function(e){
		if(e.keyCode==13){
			page=1;
			getList();
		}
	})
	
	function getList() {
		var keyword = $('#keyword').val();
		var searchType = $('#searchType').val();
		
		$.ajax({
			type : 'get',
			url : '/board/list.json',
			dataType : 'json',
			data : {
				"page" : page,
				"keyword" : keyword,
				"searchType" : searchType
			},
			success : function(data) {
				var temp = Handlebars.compile($('#temp').html());
				$("#tbl").html(temp(data));

				$("#total").html(data.pm.totalCount);
				//페이지 목록 생성
				var src = getPagination(data);
				$("#pagination").html(src);
			}
		})
	}

	// 특정 페이지 번호를 클릭한 경우
	$("#pagination").on("click", "a", function(e) {
		e.preventDefault();
		page = $(this).attr("href");
		getList();
	})
</script>
<script src="resources/pagination.js"></script>
</html>

 

- /ex02/src/main/webapp/resources/home.css

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

.pagination a {
	color: black;
	float: left;
	padding: 8px 16px;
	text-decoration: none;
}

.pagination a.active {
	background-color: #4CAF50;
	color: white;
}

.pagination a:hover:not (.active ) {
	background-color: #ddd;
}
.....

 

확인

 

 

- 새로운 목록 페이지를 만들어 연습하도록 하겠다.

- go to mysql

create table tbl_user(
	uid nvarchar(20) not null primary key,
    upass nvarchar(50) not null,
    uname nvarchar(20) not null,
    tel nvarchar(20),
    address nvarchar(200)
);

desc tbl_user;

insert into tbl_user(uid, upass,uname,tel,address)
values('user01','pass','홍길동','010-0101-0101','인천시 미추홀구 학익동 학익아파트');
insert into tbl_user(uid, upass,uname,tel,address)
values('user02','pass','이몽룡','010-0202-0202','인천시 부평구 부평동 부평아파트');
insert into tbl_user(uid, upass,uname,tel,address)
values('user03','pass','심청이','010-0303-0303','인천시 계양구 계양동 계양아파트');
insert into tbl_user(uid, upass,uname,tel,address)
values('user04','pass','신사임당','010-0404-0404','서울시 관악구 신림동 신림아파트');
insert into tbl_user(uid, upass,uname,tel,address)
values('user05','pass','이화','010-0505-0505','경기도 용인시 욘인구 용인아파트');
insert into tbl_user(uid, upass,uname,tel,address)
values('user06','pass','최영길','010-0606-0606','충청도 청주시 청주구 청주아파트');

select * from tbl_user;

 

 

 

- VO를 만들어준다.

- /ex02/src/main/java/com/example/domain/(new)UserVO.java

package com.example.domain;

public class UserVO {
	private String uid;
	private String upass;
	private String uname;
	private String tel;
	private String address;
    
    ..... getter, setter, tostring

 

- mapper interface를 만들겠다.

- /ex02/src/main/java/com/example/mapper/(new)UserMapper.java

package com.example.mapper;

import java.util.List;

import com.example.domain.Criteria;
import com.example.domain.UserVO;

public interface UserMapper {
	public List<UserVO> list(Criteria cri);
}

 

- mapper xml 을 만들어주겠다.

- /ex02/src/main/resources/mapper/(new)UserMapper.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="com.example.mapper.UserMapper">
	<select id="list" resultType="com.example.domain.UserVO">
		select * from tbl_user
		limit #{pageStart}, #{perPageNum}
	</select>
</mapper>

 

- 테스트를 위해 UserTest를 생성한다.

- /ex02/src/test/java/com/example/controller/(new)UserTest.java

package com.example.controller;

import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.test.context.ContextConfiguration;
import org.springframework.test.context.junit4.SpringJUnit4ClassRunner;

import com.example.domain.Criteria;
import com.example.mapper.MysqlMapper;
import com.example.mapper.UserMapper;

@RunWith(SpringJUnit4ClassRunner.class) // 먼저 SpringJUnit4ClassRunner.class
										// import한다.
@ContextConfiguration(locations={"file:src/main/webapp/WEB-INF/spring/**/*.xml"})
public class UserTest {
	// 자동으로 연결
	@Autowired
	private UserMapper mapper;

	// 연결해서 getList을 테스트
	@Test
	public void getList() {
		Criteria cri = new Criteria();
		cri.setPerPageNum(2);
		cri.setPage(3);
		mapper.list(cri);
	}
}

 

확인

 

- 총 갯수를 출력해보도록 하겠다.

- /ex02/src/main/java/com/example/mapper/UserMapper.java

package com.example.mapper;

import java.util.List;

import com.example.domain.Criteria;
import com.example.domain.UserVO;

public interface UserMapper {
	public List<UserVO> list(Criteria cri);
	public int totalCount(Criteria cri);
}

 

- /ex02/src/main/resources/mapper/UserMapper.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="com.example.mapper.UserMapper">
	<select id="list" resultType="com.example.domain.UserVO">
		select * from tbl_user
		limit #{pageStart}, #{perPageNum}
	</select>
	<select id="totalCount" resultType="int">
		select count(*) from tbl_user
	</select>
</mapper>

 

- /ex02/src/test/java/com/example/controller/UserTest.java

....
	@Test
	public void getTotal(){
		Criteria cri = new Criteria();
		mapper.totalCount(cri);
	}
}

 

 

- 이제 컨트롤러를 만들도록 하겠다.

- /ex02/src/main/java/com/example/controller/(new)UserController.java

package com.example.controller;

import java.util.HashMap;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.ResponseBody;

import com.example.domain.Criteria;
import com.example.domain.PageMaker;
import com.example.mapper.UserMapper;

@Controller
@RequestMapping("/user")
public class UserController {
	@Autowired
	UserMapper mapper;
	
	// json 데이터 만들기(어노테이션)
	@RequestMapping("/list.json")
	@ResponseBody
	public HashMap<String, Object> getList(Criteria cri){
		HashMap<String, Object> map = new HashMap<String, Object>();
		cri.setPerPageNum(5);
		
		PageMaker pm = new PageMaker();
		pm.setCri(cri);
		pm.setTotalCount(mapper.totalCount(cri));
		
		map.put("cri", cri);
		map.put("pm", pm);
		map.put("list", mapper.list(cri));
		return map;
	}
}

 

확인

 

- 이제 데이터를 출력하는 페이지를 만들도록 하겠다.

.....
	@RequestMapping("/list")
	public String list(){
		return "/user/list";
	}
}

확인

 

- /ex02/src/main/webapp/WEB-INF/views/(new)user/(new)list.jsp

<%@ page language="java" contentType="text/html; charset=UTF-8"
	pageEncoding="UTF-8"%>
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
<link rel="stylesheet" href="/resources/home.css" />
<script src="http://code.jquery.com/jquery-3.1.1.min.js"></script>
<script
	src="https://cdnjs.cloudflare.com/ajax/libs/handlebars.js/3.0.1/handlebars.js"></script>
<title>사용자 관리</title>
</head>
<body>
	<h1>[사용자 목록]</h1>
	<div id="condition">
		<select id="searchType">
			<option value="uid">아이디</option>
			<option value="uname">이름</option>
			<option value="tel">전화번호</option>
			<option value="address">주소</option>
		</select> <input type="text" id="keyword" placeholder="검색어" />
		검색수:<span id="total"></span>
	</div>
	<hr/>
	<table id="tbl"></table>
	<script id="temp" type="text/x-handlebars-template">
	<tr class="title">
		<td width="100">아이디</td>
		<td width="100">이름</td>
		<td width="200">전화번호</td>
		<td width="300">주소</td>
	</tr>
	{{#each list}}
		<tr class="row">
			<td>{{uid}}</td>
			<td>{{uname}}</td>
			<td>{{tel}}</td>
			<td>{{address}}</td>
		</tr>
	{{/each}}
	</script>
	<div id="pagination" class="pagination" style="text-align: center;"></div>
</body>
<script>
	var page = 1;
	getList();

	// 검색시 page 초기화
	$('#keyword').on('keypress', function(e) {
		if (e.keyCode == 13) {
			page = 1;
			getList();
		}
	})

	function getList() {
		var searchType=$('#searchType').val();
		var keyword=$('#keyword').val();
		$.ajax({
			type : 'get',
			url : '/user/list.json',
			dataType : 'json',
			data : {
				"page" : page,
				"searchType":searchType,
				"keyword":keyword
			},
			success : function(data) {
				var temp = Handlebars.compile($('#temp').html());
				$("#tbl").html(temp(data));

				$("#total").html(data.pm.totalCount);
				//페이지 목록 생성
				var src = getPagination(data);
				$("#pagination").html(src);
			}
		})
	}

	// 특정 페이지 번호를 클릭한 경우
	$("#pagination").on("click", "a", function(e) {
		e.preventDefault();
		page = $(this).attr("href");
		getList();
	})
</script>
<script src="/resources/pagination.js"></script>
</html>

 

- /ex02/src/main/resources/mapper/UserMapper.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="com.example.mapper.UserMapper">
	<select id="list" resultType="com.example.domain.UserVO">
		select * from tbl_user
		<if test="searchType=='uid'">
			where uid like concat('%',#{keyword},'%')
		</if>
		<if test="searchType=='uname'">
			where uname like concat('%',#{keyword},'%')
		</if>
		<if test="searchType=='tel'">
			where tel like concat('%',#{keyword},'%')
		</if>
		<if test="searchType=='address'">
			where address like concat('%',#{keyword},'%')
		</if>
		limit #{pageStart}, #{perPageNum}
	</select>
	<select id="totalCount" resultType="int">
		select count(*) from tbl_user
		<if test="searchType=='uid'">
			where uid like concat('%',#{keyword},'%')
		</if>
		<if test="searchType=='uname'">
			where uname like concat('%',#{keyword},'%')
		</if>
		<if test="searchType=='tel'">
			where tel like concat('%',#{keyword},'%')
		</if>
		<if test="searchType=='address'">
			where address like concat('%',#{keyword},'%')
		</if>
	</select>
</mapper>

 

확인

 

- 이제 메인페이지를 통해서 board와 user을 보여줄 수 있도록 하겠다.

- /ex02/src/main/java/com/example/controller/HomeController.java

package com.example.controller;

import org.springframework.stereotype.Controller;
import org.springframework.ui.Model;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;

/**
 * Handles requests for the application home page.
 */
@Controller
public class HomeController {
	//Servlet - /면  home view로 간다.
	@RequestMapping(value = "/", method = RequestMethod.GET)
	public String home(Model model) {
		model.addAttribute("pageName","about.jsp");
		return "home";
	}
}

 

- /ex02/src/main/webapp/WEB-INF/views/(new)about.jsp

<%@ page language="java" contentType="text/html; charset=UTF-8"
	pageEncoding="UTF-8"%>
<h1>소개란</h1>
<p>티스토리는 대한민국의 가입형/설치형 블로그 서비스다. 2006년 5월 25일, 다음커뮤니케이션과 태터앤컴퍼니가 함께
	공동운영을 시작했으나, 서비스 시작 1년 2개월 만인 2007년 7월 10일에 모든 서비스의 운영권이 다음커뮤니케이션으로
	이관되었다.</p>

 

- /ex02/src/main/webapp/WEB-INF/views/home.jsp

<%@ page language="java" contentType="text/html; charset=UTF-8"
	pageEncoding="UTF-8"%>
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<title>Home</title>
<style type="text/css">
#page {
	width: 960px;
	padding: 20px;
	margin: 0px auto;
	border: 1px solid black;
}

#header {
	width: auto;
	border: 1px solid black;
	padding: 10px;
	margin-bottom: 20px;
	text-align: center;
}
#center {
	width: auto;
	border: 1px solid black;
	padding: 10px;
	margin-bottom: 20px;
}
#footer{
	width: auto;
	border: 1px solid black;
	padding: 10px;
}
</style>
</head>
<body>
	<div id="page">
		<div id="header">
			<img src="http://placehold.it/920x150"/>
		</div>
		<div id="center">
			<div id="menu">
				<a href="/board/list">게시판 관리</a>
				<a href="/user/list">사용자 관리</a>
			</div>
			<div id="content">
				<jsp:include page="${pageName}"></jsp:include>
			</div>
		</div>
		<div id="footer">
		<h3>CopyRight 2021. All rights reserved.</h3>
		</div>
	</div>
</body>
</html>

 

확인

 

- 이제 각 user list , board list가 content에 보이도록 하겠다. header에 이미지를 누르면 홈으로 가도록 하겠다.

- /ex02/src/main/webapp/WEB-INF/views/home.jsp

.....
		<div id="header">
			<a href=""><img src="http://placehold.it/920x150"/></a>
		</div>
.....

 

- /ex02/src/main/java/com/example/controller/BoardController.java

.....
	// 2. list 페이지로 이동
	@RequestMapping("/list")
	public String listPage(Model model){
		model.addAttribute("pageName","board/list.jsp");
		// 페이지 리턴
		return "/home";
	}
}

 

- /ex02/src/main/java/com/example/controller/UserController.java

.....
	@RequestMapping("/list")
	public String list(Model model){
		model.addAttribute("pageName","user/list.jsp");
		return "/home";
	}
}

 

확인

 

- 게시판, 사용자 관리 페지이는 home.jsp를 통해서 출력되기 때문에 필요없는 부분들이 있다. 이를 정리하고 home.jsp에 공통되는 부분을 넣어주도록 하겠다.

- /ex02/src/main/webapp/WEB-INF/views/board/list.jsp

<%@ page language="java" contentType="text/html; charset=UTF-8"
	pageEncoding="UTF-8"%>

<h1>[게시판 관리]</h1>
<div id="condition">
	<select id="searchType">
		<option value="title">제목</option>
		<option value="content">내용</option>
		<option value="writer">작성자</option>
	</select> <input type="text" id="keyword" placeholder="검색어" /> 검색수 : <span
		id="total"></span>
	<hr />
</div>
<table id="tbl"></table>
<script id="temp" type="text/x-handlebars-template">
	<tr class="title">
		<td width="100">No.</td>
		<td width="200">제목</td>
		<td width="100">작성자</td>
		<td width="200">작성일</td>
		<td width="200">수정일</td>
	</tr>
	{{#each list}}
		<tr class="row">
			<td>{{bno}}</td>
			<td>{{title}}</td>
			<td>{{writer}}</td>
			<td>{{regdate}}</td>
			<td>{{updatedate}}</td>
		</tr>
	{{/each}}
	</script>
<div id="pagination" class="pagination"></div>
<script>
	var page = 1;
	getList();

	// 검색시 page 초기화
	$('#keyword').on('keypress', function(e) {
		if (e.keyCode == 13) {
			page = 1;
			getList();
		}
	})

	function getList() {
		var keyword = $('#keyword').val();
		var searchType = $('#searchType').val();

		$.ajax({
			type : 'get',
			url : '/board/list.json',
			dataType : 'json',
			data : {
				"page" : page,
				"keyword" : keyword,
				"searchType" : searchType
			},
			success : function(data) {
				var temp = Handlebars.compile($('#temp').html());
				$("#tbl").html(temp(data));

				$("#total").html(data.pm.totalCount);
				//페이지 목록 생성
				var src = getPagination(data);
				$("#pagination").html(src);
			}
		})
	}

	// 특정 페이지 번호를 클릭한 경우
	$("#pagination").on("click", "a", function(e) {
		e.preventDefault();
		page = $(this).attr("href");
		getList();
	})
</script>
<script src="/resources/pagination.js"></script>

 

- /ex02/src/main/webapp/WEB-INF/views/user/list.jsp

<%@ page language="java" contentType="text/html; charset=UTF-8"
	pageEncoding="UTF-8"%>
<h1>[사용자 목록]</h1>
<div id="condition">
	<select id="searchType">
		<option value="uid">아이디</option>
		<option value="uname">이름</option>
		<option value="tel">전화번호</option>
		<option value="address">주소</option>
	</select> <input type="text" id="keyword" placeholder="검색어" /> 검색수:<span
		id="total"></span>
</div>
<hr />
<table id="tbl"></table>
<script id="temp" type="text/x-handlebars-template">
	<tr class="title">
		<td width="100">아이디</td>
		<td width="100">이름</td>
		<td width="200">전화번호</td>
		<td width="300">주소</td>
	</tr>
	{{#each list}}
		<tr class="row">
			<td>{{uid}}</td>
			<td>{{uname}}</td>
			<td>{{tel}}</td>
			<td>{{address}}</td>
		</tr>
	{{/each}}
	</script>
<div id="pagination" class="pagination"></div>

<script>
	var page = 1;
	getList();

	// 검색시 page 초기화
	$('#keyword').on('keypress', function(e) {
		if (e.keyCode == 13) {
			page = 1;
			getList();
		}
	})

	function getList() {
		var searchType = $('#searchType').val();
		var keyword = $('#keyword').val();
		$.ajax({
			type : 'get',
			url : '/user/list.json',
			dataType : 'json',
			data : {
				"page" : page,
				"searchType" : searchType,
				"keyword" : keyword
			},
			success : function(data) {
				var temp = Handlebars.compile($('#temp').html());
				$("#tbl").html(temp(data));

				$("#total").html(data.pm.totalCount);
				//페이지 목록 생성
				var src = getPagination(data);
				$("#pagination").html(src);
			}
		})
	}

	// 특정 페이지 번호를 클릭한 경우
	$("#pagination").on("click", "a", function(e) {
		e.preventDefault();
		page = $(this).attr("href");
		getList();
	})
</script>
<script src="/resources/pagination.js"></script>

 

- /ex02/src/main/webapp/WEB-INF/views/home.jsp - 추가

....
<link rel="stylesheet" href="/resources/home.css" />
<script src="http://code.jquery.com/jquery-3.1.1.min.js"></script>
<script
	src="https://cdnjs.cloudflare.com/ajax/libs/handlebars.js/3.0.1/handlebars.js"></script>
....

 

- 게시판 글쓰기 작업을 할 수 있도록 하겠다.

- /ex02/src/main/java/com/example/controller/BoardController.java

.....
	// 3. 글쓰기
	@RequestMapping("/insert")
	public String insert(Model model){
		model.addAttribute("pageName","board/insert.jsp");
		return "/home";
	}
.....

 

- /ex02/src/main/webapp/WEB-INF/views/board/list.jsp

.....
	검색수 : <span id="total"></span>
	<span><a href="insert" style="float: right;">글쓰기</a></span>
	<hr />
.....

 

- /ex02/src/main/webapp/WEB-INF/views/board/(new)insert.jsp

<%@ page language="java" contentType="text/html; charset=UTF-8"
	pageEncoding="UTF-8"%>
<h1>[글쓰기]</h1>
<form name="frm" action="insert" method="post">
	<input
		style="border: none; border-right: 0px; border-top: 0px; boder-left: 0px; boder-bottom: 0px;"
		type="text" name="title" placeholder="제목" size=80 />
	<hr />
	<textarea rows="10" cols="78" name="content"></textarea>
	<hr />
	<input type="submit" value="글등록" /> <input type="reset" value="등록취소" />
</form>

<script>
	$(frm).on("submit",function(e){
		e.preventDefault();
		if($(frm.title).val()==""){
			alert("제목을 입력하세요.");
			$(frm.title).focus();
			return;
		} else if($(frm.content).val()==""){
			alert("내용을 입력하세요.");
			$(frm.content).focus();
			return;
		}
		if(!confirm("등록하시겠습니까?")) return;
		frm.submit();
	})
</script>

 

- /ex02/src/main/java/com/example/controller/BoardController.java

.....
	// 4. 글 데이터 값 보내기
	@RequestMapping(value="/insert",method=RequestMethod.POST)
	public String insertPost(Model model, BoardVO vo){
		vo.setWriter("user05");
		System.out.println(vo.toString());
		mapper.insert(vo);
		return "redirect:/board/list";
	}
.....

 

- /ex02/src/main/java/com/example/mapper/BoardMapper.java

package com.example.mapper;

import java.util.List;

import com.example.domain.BoardVO;
import com.example.domain.Criteria;

public interface BoardMapper {
	public List<BoardVO> list(Criteria cri);
	public int totalCount(Criteria cri);
	public void insert(BoardVO vo);
}

 

- /ex02/src/main/resources/mapper/BoardMapper.xml

.....
	<insert id="insert">
		insert into tbl_board(title,content,writer)
		values(#{title},#{content},#{writer})
	</insert>
.....

 

 

- list의 row를 클릭하여 정보를 불러오는 페이지를 만들겠다.

- /ex02/src/main/java/com/example/mapper/BoardMapper.java

.....
	public BoardVO read(int bno);
.....

 

- /ex02/src/main/java/com/example/controller/BoardController.java

.....
	// 5. 글 불러오기
	@RequestMapping("/read")
	public String read(Model model, int bno){
		model.addAttribute("pageName","board/read.jsp");
		model.addAttribute("vo",mapper.read(bno));
		return "/home";
	}
.....

 

- /ex02/src/main/resources/mapper/BoardMapper.xml

.....
	<select id="read" resultType="com.example.domain.BoardVO">
		select * from tbl_board where bno=#{bno}
	</select>
.....

 

- /ex02/src/main/webapp/WEB-INF/views/board/list.jsp

.....
	{{#each list}}
		<tr class="row" onClick="location.href='read?bno={{bno}}'">
			<td>{{bno}}</td>
			<td>{{title}}</td>
			<td>{{writer}}</td>
			<td>{{regdate}}</td>
			<td>{{updatedate}}</td>
		</tr>
	{{/each}}
.....

 

- /ex02/src/main/webapp/WEB-INF/views/board/(new)read.jsp

<%@ page language="java" contentType="text/html; charset=UTF-8"
	pageEncoding="UTF-8"%>
<h1>[글정보]</h1>
<form name="frm" action="update" method="post">
	<input type="hidden" value="${vo.bno}" name="bno"/>
	<input
		style="border: none; border-right: 0px; border-top: 0px; boder-left: 0px; boder-bottom: 0px;"
		type="text" name="title" value="${vo.title}" size=80 />
	<hr />
	<textarea rows="10" cols="78" name="content">${vo.content}</textarea>
	<hr />
	<input type="submit" value="글수정" /> <input type="reset" value="수정취소" />
</form>

<script>
	$(frm).on("submit",function(e){
		e.preventDefault();
		if($(frm.title).val()==""){
			alert("제목을 입력하세요.");
			$(frm.title).focus();
			return;
		} else if($(frm.content).val()==""){
			alert("내용을 입력하세요.");
			$(frm.content).focus();
			return;
		}
		if(!confirm("수정하시겠습니까?")) return;
		frm.submit();
	})
</script>

 

확인

 

- 정보 페이지에서 수정할 수 있도록 하겠다.

- /ex02/src/main/java/com/example/mapper/BoardMapper.java

.....
	public void update(BoardVO vo);
.....

 

- /ex02/src/main/java/com/example/controller/BoardController.java

.....
	// 6. 글 수정하기
	@RequestMapping(value="/update",method=RequestMethod.POST)
	public String updatePost(Model model, BoardVO vo){
		mapper.update(vo);
		return "redirect:/board/list";
	}
.....

 

- /ex02/src/main/resources/mapper/BoardMapper.xml

.....
	<update id="update">
		update tbl_board
		set title=#{title}, content=#{content}, updatedate=now()
		where bno=#{bno}
	</update>
.....

 

확인

 

확인

 

저작자표시 (새창열림)

'ICIA 수업일지' 카테고리의 다른 글

2021.10.08 수업일지(Spring Framework 연습)  (0) 2021.10.08
2021.10.07 수업일지(Spring Framework 연습)  (0) 2021.10.07
2021.10.05 수업일지(Spring Framework 시작)  (0) 2021.10.05
2021.10.01 수업일지 (안드로이드 기초, MySQL, Web Server)  (0) 2021.10.01
2021.09.30 수업일지(안드로이드 개발 기초, Firebase)  (0) 2021.09.30

관련글

티스토리툴바