[구디아카데미 IT국비지원] 기본키와 외래키, 집계함수, HashMap 에 대해 배우고 이를 이용하여 카테고리별 게시판을 구현해보는 시간을 가졌다.

💡 기본키와 외래키

기본 키(primary key)는 주 키 또는 프라이머리 키라고 하며, 관계형 데이터베이스에서 조(레코드)의 식별자로 이용하기에 가장 적합한 것을 관계 (테이블)마다 단 한 설계자에 의해 선택, 정의된 후보 키를 말한다.
출처: 위키백과

외래 키는 참조하는 테이블에서 1개의 키(속성 또는 속성의 집합)에 해당하고, 참조하는 측의 관계 변수는 참조되는 측의 테이블의 키를 가리킨다.
출처: 위키백과

지난 시간에 데이터 테이블의 정규화(데이터모델링)에 대해서 짧게 배웠었다. 이때, 다른 테이블에 컬럼값이 존재하는 외래키(참조키, FK)는 자신의 테이블에서 중복되는 키여서는 안된다. 즉, 외래키는 자신의 테이블에서 기본키(프라이머리키, PK)여야하는 것이다. 또한 모든 테이블은 각각 고유한 기본키를 가져야한다.

카테고리별 게시판을 만들기 위해 데이터 베이스에 새로운 테이블을 추가했다.

📍 local 테이블

local 테이블의 기본키는 local_name이다. 만약 local 테이블을 select할 때 order by를 생략한다면 기본키 순으로 오름차순 조회된다. (한글이면 가->나, 숫자면 1->2) 그래서 만약 local 테이블을 데이터 생성 순으로 조회하고 싶다면 기본키인 local_name가 아니라 createdate를 기준으로 조회해야 할 것이다.

INSERT INTO local(createdate, local_name, updatedate)
VALUES(NOW(), '부산', NOW());

INSERT INTO local
VALUES('부산', NOW(), NOW());

insert문으로 광명,대구,대전,부산,부천,서울,성남,인천을 추가했다.
주의할점) insert문 작성시 컬럼명을 생략하려면 반드시 values값을 순서대로 작성해야 한다!

📍 board 테이블

board 테이블의 기본키는 board_no이다. 기본값으로 AUTO-INCRMENT를 줘서 중복되지 않는 숫자를 부여했다. local_name과 member_id는 외래키이다. 이 외래키들은 각 테이블의 기본키일 것이다.

CREATE TABLE `board` (
	`board_no` INT(11) NOT NULL AUTO_INCREMENT,
	`local_name` VARCHAR(50) NOT NULL COLLATE 'utf8_general_ci',
	`board_title` VARCHAR(200) NOT NULL COLLATE 'utf8_general_ci',
	`board_content` TEXT NOT NULL COLLATE 'utf8_general_ci',
	`member_id` VARCHAR(50) NOT NULL COLLATE 'utf8_general_ci',
	`createdate` DATETIME NOT NULL,
	`updatedate` DATETIME NOT NULL,
	PRIMARY KEY (`board_no`) USING BTREE,
	INDEX `FK_local` (`local_name`) USING BTREE,
	INDEX `FK_member` (`member_id`) USING BTREE,
	CONSTRAINT `FK_local` FOREIGN KEY (`local_name`) REFERENCES `userboard`.`local` (`local_name`) ON UPDATE NO ACTION ON DELETE NO ACTION,
	CONSTRAINT `FK_member` FOREIGN KEY (`member_id`) REFERENCES `userboard`.`member` (`member_id`) ON UPDATE NO ACTION ON DELETE NO ACTION
)
COLLATE='utf8_general_ci'
ENGINE=InnoDB
AUTO_INCREMENT=1001
;

CREATE 코드에서 자세한 쿼리를 확인해볼 수 있다. (DDL)

기존 member 테이블에 샘플데이터를 작성해서 추가했다.

💡 집계함수 COUNT 활용하기

집계함수는 COUNT, SUM, AVG(평균값), MAX, MIN 등 말그대로 여러개의 입력값을 모아서 하나의 값을 출력하는 함수를 말한다. 이중 count 함수를 이용하면 카테고리별로 작성된 게시글이 몇개인지 즉, local_name 칼럼별로 중복된 데이터가 몇개인지 출력해줄 수 있을 것이다.

✔ DISTINCT

DISTINCT는 중복제거 키워드이다.

SELECT COUNT(DISTINCT local_name) FROM board;

local_name의 데이터가 8행이기 때문에 중복되는 값은 모두 제거한 뒤 카운트된 것이다. 이렇게 하면 중복은 제거하여 칼럼을 출력할 수는 있지만, 중복되는 값이 몇개씩인지는 알 수가 없다.

✔ GROUP BY

GROUP BY는 말그대로 그룹을 묶어 컬럼별로 집계결과를 출력한다.

SELECT local_name, COUNT(local_name) FROM board GROUP BY local_name;

칼럼명이 중복되어서 출력되지 않았고, 각 칼럼마다 중복되는 값이 몇개인지도 출력되었다. 하지만 GROUP BY는 그룹으로 묶인 값의 수는 출력할 수 있지만 (그룹으로 묶이지 않은) 전체의 갯수는 출력되지 않는다.

✔ UNION ALL

UNION ALL은 합집합을 뜻한다. 즉, 두 쿼리를 서로 합쳐준다. 사용시 컬럼명은(AS) 첫번째 쿼리를 따라간다.

SELECT '전체' localName, COUNT(local_name) cnt FROM board
UNION ALL
SELECT local_name, COUNT(local_name) FROM board GROUP BY local_name;

이렇게 전체의 수도 카운트하는 쿼리와 합쳐주면 전체의 갯수도 같이 출력할 수 있을 것이다. 첫번째 쿼리가 전체를 출력하는 쿼리이기 때문에 첫번째 행에 출력되었다. 이때 주의할 점은 쿼리를 합칠 때에는 두 테이블의 모양이 같아야한다. 즉, '전체'라는 칼럼명을 추가로 넣어서 테이블의 모양을 맞춰주어야 에러가 나지 않는다.

✔ WITH ROLLUP

WITH ROLLUP는 GROUP BY의 확장절로 GROUP BY되지 않은 결과(즉, 전체 집계함수의 결과)도 같이 출력되게 한다. 하지만 표준방법이 아니어서 DB마다 제공되지 않을 수도 있다.

SELECT local_name, COUNT(local_name) FROM board
GROUP BY local_name WITH ROLLUP;

GROUP BY절 뒤에 붙여주기만 하면 된다. 하지만 컬럼명이 NULL값으로 출력된 것을 볼 수 있다. 또한, UNION ALL과 다르게 그룹되지 않은 전체 집계함수의 결과는 맨 마지막 행에 출력되게 된다.

✔ IFNULL

IFNULL(X,Y) 함수는 X의 값이 NULL값이면 Y로 출력한다. WITH ROLLUP 사용시에 같이 사용한다면 NULL값이 아닌 지정한 컬럼명으로 출력할 수 있을 것이다.

SELECT IFNULL(local_name, '전체'), COUNT(local_name)
FROM board GROUP BY local_name WITH ROLLUP;

NULL값이었던 칼럼명이 지정한 '전체'로 출력된 것을 볼 수 있다.

💡 HashMap

select시 컬럼값 외 다른 정보를 추가해서 출력하고 싶다면 이렇게 값을 추가해서 쿼리를 작성하면 된다.

SELECT local_name localName, '대한민국' conuntry,'김희진' worker 
FROM local 
LIMIT 0, 1

특히 위에서 UNION ALL을 사용했을때 테이블의 모양을 맞추기위해 존재하지 않는 '전체'라는 컬럼을 추가해서 작성해야할 때처럼 말이다.

하지만 이 쿼리를 자바에서 출력할 때에는 기본적인 vo 클래스에 존재하지 않는 칼럼이기 때문에 매번 새로운 vo(일회용 vo)를 계속해서 만들어야하는데, 이는 매우 비효율적이다. 그래서 이럴 때 쓸 수 있는 자바 타입 중에 임시 vo처럼 사용가능한 map이 존재한다. 그 중 hashmap을 사용해보자!

HashMap<키 이름의 타입, 값의 타입> 으로 생성하고, HashMap은 따로 vo 클래스를 만들 필요 없이 바로 키 이름을 지정해서 사용할 수 있으므로 컬럼값 외 다른 정보를 추가해서 출력하고 싶을 때 매우 유용하다! 이때 값의 타입을 Object로 넣으면 모든 타입이 들어올 수 있으나, 출력시에 형변환을 해주어야한다.

📍 localListByMap.jsp

<%@ page language="java" contentType="text/html; charset=UTF-8" pageEncoding="UTF-8"%>
<%@ page import = "java.sql.*" %>
<%@ page import = "java.util.*" %> <!-- Hashmap 위치 -->
<%
	//드라이버 로딩 및 db 접속
	String driver = "org.mariadb.jdbc.Driver";
	String dburl = "jdbc:mariadb://127.0.0.1:3306/userboard";
	String dbuser = "****";
	String dbpw = "****";
	Class.forName(driver);
	Connection conn = DriverManager.getConnection(dburl, dbuser, dbpw);
	PreparedStatement stmt = null;
	ResultSet rs = null;
	
	// 1) 한 행만 출력 (limit 사용)
	// SELECT local_name localName, '대한민국' conuntry, '김희진' worker FROM local LIMIT 0,1
	String sql = "SELECT local_name localName, '대한민국' conuntry, '김희진' worker FROM local LIMIT 0,1";
	stmt = conn.prepareStatement(sql);
	rs = stmt.executeQuery();
	
	// Vo대신 HachMap타입을 사용
	HashMap<String, Object> map = null;
	if(rs.next()) { // 데이터가 있다면(true) map에 저장될 것
		// 디버깅
		/*	System.out.println(rs.getString("localName"));
			System.out.println(rs.getString("conuntry"));
			System.out.println(rs.getString("worker")); */
		map = new HashMap<String, Object>();
		map.put("localName", rs.getString("localName"));
		map.put("conuntry", rs.getString("conuntry"));
		map.put("worker", rs.getString("worker"));
	}
	// 디버깅
	System.out.println((String)map.get("localName")); // Object타입으로 지정했으므로 출력시 String타입으로 형변환
	System.out.println((String)map.get("conuntry"));
	System.out.println((String)map.get("worker"));
	
	// 2) 여러행을 출력 (limit 사용x)
	PreparedStatement stmt2 = null;
	ResultSet rs2 = null;
	String sql2 = "SELECT local_name localName, '대한민국' conuntry, '김희진' worker FROM local";
	stmt2 = conn.prepareStatement(sql2);
	rs2 = stmt2.executeQuery();
	
	// HashMap이 여러개 필요하므로 ArrayList를 사용한다
	ArrayList<HashMap<String, Object>> list = new ArrayList<HashMap<String, Object>>();
	// ArrayList를 반복문(while)을 이용하여 HashMap에 값 넣기
	while(rs2.next()) {
		HashMap<String, Object> m = new HashMap<String, Object>();
		m.put("localName", rs2.getString("localName"));
		m.put("conuntry", rs2.getString("conuntry"));
		m.put("worker", rs2.getString("worker"));
		list.add(m);
	}
	
	// 3) 카테고리(local) 중복되는 데이터 수 표시하기
	// SELECT local_name localName, COUNT(local_name) cnt FROM board GROUP by local_name
	PreparedStatement stmt3 = null;
	ResultSet rs3 = null;
	String sql3 = "SELECT local_name localName, COUNT(local_name) cnt FROM board GROUP by local_name";
	stmt3 = conn.prepareStatement(sql3);
	rs3 = stmt3.executeQuery();
	
	// HashMap이 여러개 필요하므로 ArrayList를 사용한다
	ArrayList<HashMap<String, Object>> list3 = new ArrayList<HashMap<String, Object>>();
	// ArrayList를 반복문(while)을 이용하여 HashMap에 값 넣기
	while(rs3.next()) {
		HashMap<String, Object> m = new HashMap<String, Object>();
		m.put("localName", rs3.getString("localName"));
		m.put("cnt", rs3.getInt("cnt"));
		list3.add(m);
	}
%>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>localListByMap.jsp</title>
</head>
<body>
	<div>
		<h2>rs1 결과셋</h2>
		<%=map.get("localName")%>
		<%=map.get("conuntry")%>
		<%=map.get("worker")%>
	</div>

	<br>
	<h2>rs2 결과셋</h2>
	<table>
		<tr>
			<th>localName</th>
			<th>conuntry</th>
			<th>worker</th>
		</tr>
		<%
			for(HashMap<String, Object> m : list) {
		%>
				<tr>
					<td><%=m.get("localName")%></td>
					<td><%=m.get("conuntry")%></td>
					<td><%=m.get("worker")%></td>
				</tr>
		<%		
			}
		%>
	</table>
	
	<br>
	<h2>rs3 결과셋</h2>
	<ul>
		<li>
			<a href="">전체</a>
		</li>
		<%
			for(HashMap<String, Object> m : list3) {
		%>
				<li>
					<a href="">
						<%=(String)m.get("localName")%>(<%=(Integer)m.get("cnt")%>)
					</a>
				</li>
		<%		
			}
		%>
	</ul>
</body>
</html>

• 값을 넣을 때에는 put 메서드를 사용하고, 이때 키이름을 지정할 수 있다.

• 여러행을 출력할 때에는 VO타입과 마찬가지로 ArrayList를 사용할 수 있다.

• Object타입으로 지정했으므로 출력시 String타입으로 형변환해야하는 것 잊지말기

📍 결과

💡 과제

오늘 배운 내용을 바탕으로 저번 시간에 만든 home.jsp를 수정하였다.

✔ 구현해야할 점

• 카테고리(전체 포함) 별로 등록된 게시글 수 표시하기 -> 서브메뉴
  (UNION ALL과 GROUP BY절 이용, HashMap 활용)
• 카테고리 클릭 시 카테고리별 게시글(localName,boardTitle) 5개씩 출력하기, 첫화면은 전체 게시글 5개
  (동적쿼리 작성해서 WHERE절 분기하기)
• 부트스트랩으로 CSS하기

📍 작성 - Board.java

package vo;

public class Board {
	public int boardNo;
	public String localName;
	public String boardTitle;
	public String boardContent;
	public String memberId;
	public String createdate;
	public String updatedate;
}

📍 작성 - home.jsp

<%@ page language="java" contentType="text/html; charset=UTF-8" pageEncoding="UTF-8"%>
<%@ page import = "java.sql.*" %>
<%@ page import = "java.util.*" %> <!-- HashMap 사용 -->
<%@ page import = "vo.*" %>
<%
	//드라이버 로딩 및 db 접속
	String driver = "org.mariadb.jdbc.Driver";
	String dburl = "jdbc:mariadb://127.0.0.1:3306/userboard";
	String dbuser = "****";
	String dbpw = "****";
	Class.forName(driver);
	Connection conn = DriverManager.getConnection(dburl, dbuser, dbpw);
	PreparedStatement stmt = null;
	ResultSet rs = null;
	
	// 1) 서브메뉴를 출력하기 위한 쿼리 작성
	// 전체와 각 localName의 count를 조회하기 위해 쿼리를 합침(UNION ALL 사용)
	/*
		SELECT '전체' localName, COUNT(local_name) cnt FROM board
		UNION ALL 
		SELECT local_name, COUNT(local_name) FROM board GROUP BY local_name
	*/
	String subMenuSql = "SELECT '전체' localName, COUNT(local_name) cnt FROM board UNION ALL SELECT local_name, COUNT(local_name) FROM board GROUP BY local_name";
	PreparedStatement subMenuStmt = conn.prepareStatement(subMenuSql);
	ResultSet subMenuRs = subMenuStmt.executeQuery();
	// HashMap을 ArrayList에 넣기
	ArrayList<HashMap<String, Object>> subMenuList = new ArrayList<HashMap<String, Object>>();
	while(subMenuRs.next()) {
		HashMap<String, Object> m = new HashMap<String, Object>();
		m.put("localName", subMenuRs.getString("localName"));
		m.put("cnt", subMenuRs.getInt("cnt"));
		subMenuList.add(m);
	}
	
	// 2) 카테고리 별 게시글 5개씩 출력
	// 요청값 검사
	System.out.println(request.getParameter("localName") + " <- home param localName");
	
	String localName = "전체"; // null이면 전체
	if(request.getParameter("localName") != null) {
		localName = request.getParameter("localName");
	}
	System.out.println(localName + " <- home localName");
	
	// 쿼리 작성
	// 기본(전체 조회) 쿼리
	String sql = "SELECT board_no boardNo, board_title boardTitle, local_name localName FROM board";
	// localName 선택 시 추가되는 쿼리
	String localNameSql = " WHERE local_name = ?";
	// limit 쿼리
	String limitSql = " LIMIT 0, 5";
	
	if(localName.equals("전체") || localName.equals("")) {
		sql = sql + limitSql;
		stmt = conn.prepareStatement(sql);
	} else {
		sql = sql + localNameSql + limitSql;
		stmt = conn.prepareStatement(sql);
		stmt.setString(1, localName);
	}
	System.out.println(stmt + " <- home sql");
	rs = stmt.executeQuery();
	
	// 일반적인 자료구조로 변경 ArrayList
	ArrayList<Board> localNameList = new ArrayList<Board>();
	while(rs.next()) {
		Board b = new Board();
		b.boardNo = rs.getInt("boardNo");
		b.localName = rs.getString("localName");
		b.boardTitle = rs.getString("boardTitle");
		localNameList.add(b);
	}
%>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>home.jsp</title>
	<!-- 부트스트랩5 사용 -->
	<link href="https://cdn.jsdelivr.net/npm/bootstrap@5.2.3/dist/css/bootstrap.min.css" rel="stylesheet">
	<script src="https://cdn.jsdelivr.net/npm/bootstrap@5.2.3/dist/js/bootstrap.bundle.min.js"></script>
</head>
<body>	
<div><!-- 메인메뉴(가로) -->
	<jsp:include page="/inc/mainmenu.jsp"></jsp:include>
</div>
	
<div class="container mt-5">
	<!-- 홈내용 : 로그인폼(로그인 안했을때만), 카테고리별 게시글 5개씩 -->
	<div><!-- 로그인폼 -->
		<%
			if(session.getAttribute("loginMemberId") == null) { // 세션에 id값이 null이면 로그인 폼 출력
		%>
				<h3 class="mt-4">로그인</h3>
				<div class="text-danger">
					<%	// msg 발생시 출력
						if(request.getParameter("msg") != null) {
					%>
							<%=request.getParameter("msg")%>
					<%
						}
					%>
				</div>
				<form action="<%=request.getContextPath()%>/member/loginAction.jsp" method="post">
					<table>
						<tr>
							<td>아이디</td>
							<td>
								<input type="text" name="memberId">
							</td>
						</tr>
						<tr>
							<td>패스워드</td>
							<td>
								<input type="password" name="memberPw">
							</td>
						</tr>
					</table>
					<button type="submit" class="btn btn-success">로그인</button>
				</form>
		<%
			}
		%>
	</div>
	
	<br>
	
	<div class="row">
		<div class="col-sm-4"><!-- 서브메뉴(세로) subMenuList모델을 출력 -->
			<h3 class="mt-4">카테고리</h3>
			<ul class="nav nav-pills flex-column">
				<%
					for(HashMap<String, Object> m : subMenuList) {
				%>
						<li class="nav-item">
							<a href="<%=request.getContextPath()%>/home.jsp?localName=<%=(String)m.get("localName")%>" class="nav-link">
								<%=(String)m.get("localName")%>(<%=(Integer)m.get("cnt")%>)
								<!-- 값타입이 Object타입이므로 형변환 -->
							</a>
						</li>
				<%		
					}
				%>
			</ul>
		</div>
		<div class="col-sm-8"><!-- 카테고리별 게시글 5개씩 -->
			<h3 class="mt-4">게시판</h3>
			<table class="table">
				<thead class="table-success">
					<tr>
						<th>카테고리</th>
						<th>제목</th>
					</tr>
				</thead>
				<tbody>
					<%
						for(Board b : localNameList) {
					%>
							<tr>
								<td><%=b.localName%></td>
								<td><%=b.boardTitle%></td>
							</tr>
					<%
						}
					%>
				</tbody>
			</table>
		</div>
	</div>
</div>

	<br>
	
	<div>
		<!-- include 페이지 : Copyright &copy; 구디아카데미 -->
		<%
			// request.getRequestDispatcher(request.getContextPath()+"./inc/copyright.jsp").include(request, response);
			// 자주 쓰지만 너무 길다. 그래서 액션태그를 활용한다!
		%> 
		<jsp:include page="/inc/copyright.jsp"></jsp:include> <!-- 액션태그 -->
	</div>
</body>
</html>

📍 출력

첫 화면시 localName값 - 전체

카테고리 localName값 부천 선택시