[구디아카데미 IT국비지원] SELECT문의 ORDER BY절과 WHERE절을 이용하여 정렬 기능과 성별 검색기능을 구현하는 법에 대해 배웠다.

💡 mariadb 명령프롬프트(CMD)로 이용해보기

윈도우창에서 mariadb cmd 창을 실행시킨다.

로그인 후 비밀번호를 입력

show databases; 로 데이터 베이스 목록을 확인할 수 있다.

use 명령어로 데이터 베이스를 선택하고, show tables;를 입력하면 해당 데이터 베이스의 테이블 목록을 볼 수 있다.

desc 를 입력하면 해당 테이블을 조회할 수 있다.

mariadb에 입력하는 쿼리를 그대로 입력해서 똑같이 실행할 수 있다!

📌 샘플DB를 설치해보려면?

• cd 명령어로 설치할 샘플DB 파일의 위치로 이동한다
  (ex. cd c:\employees)

• 이동 후 mysql -u 계정이름 -p 입력 후 비밀번호도 입력하여 로그인

• source 파일명으로 설치한다. (ex. source employees.sql)

• HeidiSQL를 실행해보면 정상적으로 데이터 베이스가 설치된 것을 확인할 수 있다!

💡 SELECT문 ORDER BY절

select문의 order by절을 이용하여 각 컬럼을 기준으로 오름차순, 내림차순을 변경할 수 있는 방법에 대해 배웠다.
참고로 order by절을 생략할 경우 기본키 순으로 오름차순(작은거->큰거)으로 정렬된다!

📍 작성

<%@ page language="java" contentType="text/html; charset=UTF-8" pageEncoding="UTF-8"%>
<%@ page import = "java.sql.*" %>
<%@ page import = "java.util.*" %>
<%@ page import = "vo.*" %>
<%
	// 정렬 기능
	
	// 현재페이지 요청값 검사
	int curPage = 1; // 현재페이지는 1부터 시작
	if(request.getParameter("curPage") != null) { // 넘어온 curPage가 null이거나 공백이 아닐 경우 // 즉, 이전 또는 다음을 눌렀을 경우
		curPage = Integer.parseInt(request.getParameter("curPage")); // int타입으로 값 받기
	}
	System.out.println("현재페이지: " + curPage + "페이지");
	
	// 페이지당 출력할 행이 몇개인지
	int rowPerPage = 10;
	
	// 페이지당 시작 행 번호가 몇번인지
	int startRow = (curPage - 1) * rowPerPage;
	System.out.println("현재 시작 행번호: " + startRow + "번");
	/*
	  curPage  startRow  rowPerPage
		1		0			30
		2		30			30
		3		60			30
		4		90			30
	*/

	// 1) 드라이버 로딩
	Class.forName("org.mariadb.jdbc.Driver");
	// 2) mariadb 서버 접속
	Connection conn = DriverManager.getConnection("jdbc:mariadb://127.0.0.1:3306/employees","****","****");
	// 3) 쿼리 생성 후 실행 // 클릭하면 정렬이 바뀔 수 있도록 ORDER BY 사용
	/*
		SELECT
			emp_no empNo,
			birth_date birthDate,
			first_name firstName,
			last_name lastName,
			gender gender,
			hire_date hireDate
		FROM employees
		ORDER BY emp_no ASC // 해당하는 컬럼 기준으로 정렬이 계속 바뀌게 하는 기능을 추가하려면, emp_no ASC가 계속 바뀌어야하는데 값이 아니기 때문에 ?로 들어갈 수 없다
		LIMIT ?, ?
	*/
	String col = "emp_no"; // 그래서 각각 변수로 둔다
	String ascDesc = "ASC"; // 기본값은 emp_no ASC
	if(request.getParameter("col") != null // 값이 넘어왔을 경우 그 값을 받아준다
			&& request.getParameter("ascDesc") != null) {
		col = request.getParameter("col");
		ascDesc = request.getParameter("ascDesc");
	} // ORDER BY " + col + " " + ascDesc + " LIMIT // spl 작성할 때 중간에 공백도 잘 넣어주어야함
	String sql = "SELECT emp_no empNo, birth_date birthDate, first_name firstName, last_name lastName, gender gender, hire_date hireDate FROM employees ORDER BY " + col + " " + ascDesc + " LIMIT ?, ?";
	PreparedStatement stmt = conn.prepareStatement(sql);
	// 한 페이지당 몇개씩 출력할지 limit ?,? 사용
	// startRow번부터 rowPerPage개씩 출력
	stmt.setInt(1, startRow);
	stmt.setInt(2, rowPerPage); 
	System.out.println("empList sql: " + stmt);
	
	ResultSet rs = stmt.executeQuery();
	// ResultSet -> ArrayList<Emp>
	ArrayList<Emp> empList = new ArrayList<Emp>();
	while(rs.next()) {
		Emp e = new Emp();
		e.empNo = rs.getInt("empNo");
		e.birthDate = rs.getString("birthDate");
		e.firstName = rs.getString("firstName");
		e.lastName = rs.getString("lastName");
		e.gender = rs.getString("gender");
		e.hireDate = rs.getString("hireDate");
		empList.add(e);
	}
	
	// 데이터가 총 몇행인지 구하기 count(*) 사용
	String sql2 = "SELECT count(*) FROM employees";
	PreparedStatement stmt2 = conn.prepareStatement(sql2);
	ResultSet rs2 = stmt2.executeQuery();
	System.out.println("empList sql2: " + stmt2);
	// ResultSet -> int totalRow에 값을 넣는다
	int totalRow = 0;
	if(rs2.next()) {
		totalRow = rs2.getInt("count(*)");
	}
	System.out.println("전체 데이터 행의 수: 총 " + totalRow + "개");
	
	// 총 출력되는 페이지의 수(마지막 페이지 넘버) 구하기
	int lastPage = totalRow / rowPerPage;
	if(totalRow % rowPerPage != 0) {
		lastPage = lastPage + 1; // 페이지가 딱 나누어떨어지지 않으면 한페이지가 더 필요하다
	}
	System.out.println("마지막 페이지 넘버: " + lastPage + "페이지");
	
	// 나이 계산하기
	Calendar today = Calendar.getInstance(); // 오늘 날짜 구하기
	// 오늘 날짜의 년도,월,일 변수에 받기
	int todayYear = today.get(Calendar.YEAR);
	int todayMonth = today.get(Calendar.MONTH) + 1; // 자바api와 마리아db의 월 시작일이 다르므로 +1 해준다 (자바는 0월부터 시작)
	int todayDate = today.get(Calendar.DATE);
	System.out.println("todayYear: " + todayYear + "년");
	System.out.println("todayMonth: " + todayMonth + "월");
	System.out.println("todayDate: " + todayDate + "일");
%>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>empList2.jsp</title>
	<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>
<style>
	.small {width: 30px; height: 30px;}
</style>
</head>
<body>
<div class="p-5 bg-primary text-white text-center">
	<h1>Employees List</h1>
</div>
<div class="container mt-3">
	<table class="table text-center">
		<thead class="table-primary">
			<tr><!-- 컬럼명 -->
				<th>
					no
					<div>
						<a href="./empList2.jsp?col=emp_no&ascDesc=ASC">&#128316;</a>
						<a href="./empList2.jsp?col=emp_no&ascDesc=DESC">&#128317;</a>
					</div>
				</th>
				<th>
					age
					<div>
						<a href="./empList2.jsp?col=birth_date&ascDesc=ASC">&#128316;</a>
						<a href="./empList2.jsp?col=birth_date&ascDesc=DESC">&#128317;</a>
					</div>
				</th>
				<th>
					first name
					<div>
						<a href="./empList2.jsp?col=first_name&ascDesc=ASC">&#128316;</a>
						<a href="./empList2.jsp?col=first_name&ascDesc=DESC">&#128317;</a>
					</div>
				</th>
				<th>
					last name
					<div>
						<a href="./empList2.jsp?col=last_name&ascDesc=ASC">&#128316;</a>
						<a href="./empList2.jsp?col=last_name&ascDesc=DESC">&#128317;</a>
					</div>
				</th>
				<th>
					gender
					<div>
						<a href="./empList2.jsp?col=gender&ascDesc=ASC">&#128316;</a>
						<a href="./empList2.jsp?col=gender&ascDesc=DESC">&#128317;</a>
					</div>
				</th>
				<th>
					hire date
					<div>
						<a href="./empList2.jsp?col=hire_date&ascDesc=ASC">&#128316;</a>
						<a href="./empList2.jsp?col=hire_date&ascDesc=DESC">&#128317;</a>
					</div>
				</th>
			</tr>
		</thead>
		<tbody>
			<%
				for(Emp e : empList) {
			%>
				<tr>
					<td><%=e.empNo%>번</td>
					<%
						// e.birthDate에서 년도,월,일 int타입으로 가져오기
						int birthYear = Integer.parseInt(e.birthDate.substring(0,4));
						int birthMonth = Integer.parseInt(e.birthDate.substring(5,7));
						int birthDate = Integer.parseInt(e.birthDate.substring(8));
						int age = todayYear - birthYear; // 나이 = 오늘년도 - 태어난년도
							
						if(birthMonth <= todayMonth && birthDate <= todayDate) {
								// 현재 월이 태어난 월보다 크거나 같고(이미 지났거나 이번달이고)
								// 현재 일이 태어난 일보다 크거나 같으면(이미 지났거나 오늘이면)
								// 생일이 지났거나 오늘이 생일이므로 +1살 해준다
								age = age + 1;
						}
					%>
					<td><%=age%>세</td>
					<td><%=e.firstName%></td>
					<td><%=e.lastName%></td>
					<td>
						<%
							if(e.gender.equals("M")) { // String타입이므로 equals 사용
						%>
								<img src="./img/m.JPG" class="small">
						<%
							} else {
						%>
								<img src="./img/f.JPG" class="small">
						<%
							}				
						%>
					</td>
					<td><%=e.hireDate%></td>
				</tr>
			<%
				}
			%>
		</tbody>
	</table>
</div>
	<div class="mt-5 p-4 bg-secondary text-white text-center">
		<%
			if(curPage > 1) { // 첫번째 페이지(1)보다 큰 페이지부터 "이전"을 출력
		%>
				<a href="./empList2.jsp?curPage=<%=curPage - 1%>&col=<%=col%>&ascDesc=<%=ascDesc%>" class="btn btn-primary btn-sm"> 이전 </a>
		<%
			}
		%>
			<%=curPage%>페이지 <!-- 현재페이지 출력 -->
		<%
			if(curPage < lastPage) { // 마지막 페이지 넘버보다 작은 페이지까지만 "다음"을 출력
		%>
				<a href="./empList2.jsp?curPage=<%=curPage + 1%>&col=<%=col%>&ascDesc=<%=ascDesc%>" class="btn btn-primary btn-sm"> 다음 </a>
		<%
			}
		%>
	</div>
</body>
</html>

• 기준 칼럼이 되는 변수 col과 오름차순과 내림차순을 결정할 변수 ascDesc를 추가했다.
• 쿼리 작성시 변수가 중간에 들어가는 경우 공백을 유의해야한다!

📍 결과

해당 컬럼의 화살표를 누르면, 해당 컬럼 기준으로 오름차순과 내림차순으로 바뀌게된다.

💡 SELECT문 WHERE절

이번에는 select문의 where절을 이용하여 성별 검색기능을 추가하는 방법을 배웠다. if문을 이용하여 조건에 따라 쿼리가 바뀌게 되는 것을 동적쿼리라고 부른다. 동적쿼리를 이용하면 다양한 검색조건에 따라 다른 view를 사용자에게 보여줄 수 있다!

📍 작성

<%@ page language="java" contentType="text/html; charset=UTF-8" pageEncoding="UTF-8"%>
<%@ page import = "java.sql.*" %>
<%@ page import = "java.util.*" %>
<%@ page import = "vo.*" %>
<%
	// 성별 검색 기능 추가
	
	// 현재페이지 요청값 검사
	int curPage = 1; // 현재페이지는 1부터 시작
	if(request.getParameter("curPage") != null) { // 넘어온 curPage가 null이거나 공백이 아닐 경우 // 즉, 이전 또는 다음을 눌렀을 경우
		curPage = Integer.parseInt(request.getParameter("curPage")); // int타입으로 값 받기
	}
	System.out.println("현재페이지: " + curPage + "페이지");
	
	// 페이지당 출력할 행이 몇개인지
	int rowPerPage = 10;
	
	// 페이지당 시작 행 번호가 몇번인지
	int startRow = (curPage - 1) * rowPerPage;
	System.out.println("현재 시작 행번호: " + startRow + "번");
	/*
	  curPage  startRow  rowPerPage
		1		0			30
		2		30			30
		3		60			30
		4		90			30
	*/
	
	// 요청값 검사
	String gender = "";
	if(request.getParameter("gender") != null) {
		gender = request.getParameter("gender");
	}
	// 드라이버 로딩 // mariadb 서버 접속
	Class.forName("org.mariadb.jdbc.Driver");
	Connection conn = DriverManager.getConnection("jdbc:mariadb://127.0.0.1:3306/employees","****","****");
	
	// 쿼리 먼저 생성 // 여기 안에 다 들어감 다시 잘 보기 (동적쿼리)
	String sql = null;
	PreparedStatement stmt  = null;
	if(gender.equals("")) {
		sql = "SELECT emp_no empNo, birth_date birthDate, first_name firstName, last_name lastName, gender gender, hire_date hireDate FROM employees LIMIT ?, ?";
		stmt = conn.prepareStatement(sql); // ? 2개
		stmt.setInt(1, startRow);
		stmt.setInt(2, rowPerPage);
	} else { // where절 추가
		sql = "SELECT emp_no empNo, birth_date birthDate, first_name firstName, last_name lastName, gender gender, hire_date hireDate FROM employees WHERE gender=? LIMIT ?, ?";
		stmt = conn.prepareStatement(sql); // ? 3개
		stmt.setString(1, gender);
		stmt.setInt(2, startRow);
		stmt.setInt(3, rowPerPage);
	}
	System.out.println("empList sql: " + stmt);
	
	ResultSet rs = stmt.executeQuery();
	// ResultSet -> ArrayList<Emp>
	ArrayList<Emp> empList = new ArrayList<Emp>();
	while(rs.next()) {
		Emp e = new Emp();
		e.empNo = rs.getInt("empNo");
		e.birthDate = rs.getString("birthDate");
		e.firstName = rs.getString("firstName");
		e.lastName = rs.getString("lastName");
		e.gender = rs.getString("gender");
		e.hireDate = rs.getString("hireDate");
		empList.add(e);
	}
	
	// 데이터가 총 몇행인지 구하기 count(*) 사용
	String sql2 = "SELECT count(*) FROM employees";
	PreparedStatement stmt2 = conn.prepareStatement(sql2);
	ResultSet rs2 = stmt2.executeQuery();
	System.out.println("empList sql2: " + stmt2);
	// ResultSet -> int totalRow에 값을 넣는다
	int totalRow = 0;
	if(rs2.next()) {
		totalRow = rs2.getInt("count(*)");
	}
	System.out.println("전체 데이터 행의 수: 총 " + totalRow + "개");
	
	// 총 출력되는 페이지의 수(마지막 페이지 넘버) 구하기
	int lastPage = totalRow / rowPerPage;
	if(totalRow % rowPerPage != 0) {
		lastPage = lastPage + 1; // 페이지가 딱 나누어떨어지지 않으면 한페이지가 더 필요하다
	}
	System.out.println("마지막 페이지 넘버: " + lastPage + "페이지");
	
	// 나이 계산하기
	Calendar today = Calendar.getInstance(); // 오늘 날짜 구하기
	// 오늘 날짜의 년도,월,일 변수에 받기
	int todayYear = today.get(Calendar.YEAR);
	int todayMonth = today.get(Calendar.MONTH) + 1; // 자바api와 마리아db의 월 시작일이 다르므로 +1 해준다 (자바는 0월부터 시작)
	int todayDate = today.get(Calendar.DATE);
	System.out.println("todayYear: " + todayYear + "년");
	System.out.println("todayMonth: " + todayMonth + "월");
	System.out.println("todayDate: " + todayDate + "일");
%>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>empList.jsp</title>
	<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>
<style>
	.small {width: 30px; height: 30px;}
</style>
</head>
<body>
<div class="p-5 bg-primary text-white text-center">
	<h1>Employees List</h1>
</div>
<div class="container mt-3">
	<table class="table text-center">
		<thead class="table-primary">
			<tr><!-- 컬럼명 -->
				<th>no</th>
				<th>age</th>
				<th>first name</th>
				<th>last name</th>
				<th>gender</th>
				<th>hire date</th>
			</tr>
		</thead>
		<tbody>
			<%
				for(Emp e : empList) {
			%>
				<tr>
					<td><%=e.empNo%>번</td>
					<%
						// e.birthDate에서 년도,월,일 int타입으로 가져오기
						int birthYear = Integer.parseInt(e.birthDate.substring(0,4));
						int birthMonth = Integer.parseInt(e.birthDate.substring(5,7));
						int birthDate = Integer.parseInt(e.birthDate.substring(8));
						int age = todayYear - birthYear; // 나이 = 오늘년도 - 태어난년도
							
						if(birthMonth <= todayMonth && birthDate <= todayDate) {
								// 현재 월이 태어난 월보다 크거나 같고(이미 지났거나 이번달이고)
								// 현재 일이 태어난 일보다 크거나 같으면(이미 지났거나 오늘이면)
								// 생일이 지났거나 오늘이 생일이므로 +1살 해준다
								age = age + 1;
						}
					%>
					<td><%=age%>세</td>
					<td><%=e.firstName%></td>
					<td><%=e.lastName%></td>
					<td>
						<%
							if(e.gender.equals("M")) { // String타입이므로 equals 사용
						%>
								<img src="./img/m.JPG" class="small">
						<%
							} else {
						%>
								<img src="./img/f.JPG" class="small">
						<%
							}				
						%>
					</td>
					<td><%=e.hireDate%></td>
				</tr>
			<%
				}
			%>
		</tbody>
	</table>
	<div class="text-center">
		<!-- 성별 검색 기능 추가 -->
		<form action="./empList.jsp" method="get">
			<select name="gender">
				<option value="">==성별선택==</option>
				<option value="M">==남==</option>
				<option value="F">==여==</option>
			</select>
			<button type="submit">성별검색</button>
		</form>
	</div>
</div>
	<!-- 페이징 -->
	<div class="mt-5 p-4 bg-secondary text-white text-center">
		<%
			if(curPage > 1) { // 첫번째 페이지(1)보다 큰 페이지부터 "이전"을 출력
		%>
				<a href="./empList.jsp?curPage=<%=curPage - 1%>&gender=<%=gender%>" class="btn btn-primary btn-sm"> 이전 </a>
		<%
			}
		%>
			<%=curPage%>페이지 <!-- 현재페이지 출력 -->
		<%
			if(curPage < lastPage) { // 마지막 페이지 넘버보다 작은 페이지까지만 "다음"을 출력
		%>
				<a href="./empList.jsp?curPage=<%=curPage + 1%>&gender=<%=gender%>" class="btn btn-primary btn-sm"> 다음 </a>
		<%
			}
		%>
	</div>
</body>
</html>

📍 출력