[๊ตฌ๋์์นด๋ฐ๋ฏธ IT๊ตญ๋น์ง์] WHERE์ ์ ์ฌ์ฉ ๊ฐ๋ฅํ ๋ค์ํ ์ฐ์ฐ์๋ค์ ๋ฐฐ์ฐ๊ณ , ๊ทธ์ค LIKE ์ฐ์ฐ์๋ฅผ ์ด์ฉํ์ฌ ์ด๋ฆ ๊ฒ์ ๊ธฐ๋ฅ์ ๊ตฌํํด๋ณด์๋ค. ๋ํ ์ฝ์์ฐฝ์ ์ถ๋ ฅ ์์์ ๋ณ๊ฒฝํ๋ ๋ฐฉ๋ฒ๋ ๋ฐฐ์ ๋ค.
SELECT * FROM employees
WHERE gender = 'M'
LIMIT 0, 10;
SELECT * FROM employees
WHERE emp_no = 10005;
SELECT * FROM employees
WHERE emp_no < 10005;
SELECT * FROM employees
WHERE first_name < 'C';
SELECT * FROM employees
WHERE emp_no = 10005 OR emp_no = 10008;
SELECT * FROM employees
WHERE emp_no IN (10005, 10008);
// ์ด๋ ๊ฒ ์ฐ๋ ๊ฒ์ด ๋ ์ข์
SELECT * FROM employees
WHERE emp_no >= 10005 AND emp_no <= 10008;
SELECT * FROM employees
WHERE emp_no BETWEEN 10005 AND 10008;
// ์ด๋ ๊ฒ ์ฐ๋ ๊ฒ์ด ๋ ์ข์
SELECT * FROM employees
WHERE first_name LIKE 'Anneke'
// ๋ฌธ์์ด์ ํนํ๋ ์ฐ์ฐ์
SELECT * FROM employees
WHERE first_name LIKE 'A%'
// ์ด๋ ๊ฒ ์กฐ๊ฑด์ ๋ถ์ผ ์ ์์ (A๋ก ์์ํ๋)
SELECT * FROM employees
WHERE first_name LIKE '%a'
// a๋ก ๋๋๋
SELECT * FROM employees
WHERE first_name LIKE '%ab%'
// ์์์๊ด์์ด ab๊ฐ ๋ค์ด๊ฐ๋
SELECT * FROM employees
WHERE first_name LIKE '_ab_'
// ์ค๊ฐ์ ab๊ฐ ๋ค์ด๊ฐ๋ _๋์ ?๊ฐ๋ฅ
SELECT * FROM employees
WHERE first_name LIKE '%be%' OR last_name LIKE '%be%';
SELECT concat(first_name,' ',last_name) fullname
FROM employees
WHERE concat(first_name,' ',last_name) LIKE '%be%';
SELECT * FROM employees
WHERE YEAR(hire_date) BETWEEN 1988 AND 1990;
sql์์ ๋์
์ฐ์ฐ์ := ๋ฑํธ ์ฐ์ฐ์ =
์๋ฐ์์ ๋์
์ฐ์ฐ์ = ๋ฑํธ ์ฐ์ฐ์ ==
sql๋ฌธ์์๋ <,> ์ฐ์ฐ์๋ฅผ ๋ฌธ์์ด์ ์ฌ์ฉํ ์ ์๋ค. ์๋ฅผ ๋ค๋ฉด a๋ณด๋ค z๊ฐ ๋ ํฌ๋ค.
์๋ฐ์์ ๋ฌธ์์ด์ ๋น๊ตํ ๋๋ ๋ฑํธ์ฐ์ฐ์๋ฅผ ์ฐ์ง ๋ชปํ๊ธฐ ๋๋ฌธ์ equals๋ฅผ ์ด์ฉํด์ผํ๋ค. sql๋ฌธ์์ ๋ฑํธ์ฐ์ฐ์๋ ๋ฌธ์์ ์ซ์์ ๋ชจ๋ ์ธ ์ ์๋ค. ๋์ ์ ๋ฌธ์์ด์ ํนํ๋ LIKE ์ฐ์ฐ์๊ฐ ์๋ค. LIKE ์ฐ์ฐ์๋ ๋ฌธ์์ด์ ๋น๊ตํ ๋ ์ด๋ค ๊ธ์๋ก ์์ํ๋์ง, ์ด๋ค ๊ธ์๊ฐ ํฌํจ๋์ด์๋์ง ๋ฑ์ ์กฐ๊ฑด์ ์ถ๊ฐํ ์ ์๋ค!
concat ์ฐ์ฐ์๋ ๋ฌธ์์ด์ ํฉ์น ์ ์๋ค. ๋๊ฐ์ ๋ฌธ์์ด ์นผ๋ผ ๋ชจ๋์์ ๊ฐ์ ๊ฒ์์ ํ๊ณ ์ถ์ ๋, concat ์ฐ์ฐ์๋ฅผ ์ด์ฉํ๋ฉด ํ๋ฒ์ ์ฟผ๋ฆฌ๋ฅผ ์์ฑํ ์ ์์ ๊ฒ์ด๋ค. ๋จ, ์ด๋ ์ค๊ฐ์ ๊ณต๋ฐฑ์ ๋ฃ์ด์ ํด๋น ๋ฌธ์์ด์ด ํฉ์ณ์ ธ์ ๊ฒ์๋๋ ๊ฒฝ์ฐ๋ฅผ ๋ฐฉ์งํด์ผ ํ๋ ๊ฒ์ ์์ง๋ง์!
IN ์ฐ์ฐ์๋ ๊ดํธ ๋ด์ ๊ฐ ์ค ์ผ์นํ๋ ๊ฒ์ด ์์ผ๋ฉด ture๊ฐ ๋๋๋ฐ, OR ์ฐ์ฐ์์ ๊ฐ์ ์ญํ ์ ํ์ง๋ง IN ์ฐ์ฐ์๋ฅผ ์ด์ฉํ๋ฉด ์ฟผ๋ฆฌ๋ฅผ ํจ์ฌ ์งง๊ฒ ์ค์ผ ์ ์๋ค.
BETWEEN ์ฐ์ฐ์๋ ์ฌ์๊ฐ์ ํํํ๋๋ฐ, ๋ง์ฐฌ๊ฐ์ง๋ก AND ์ฐ์ฐ์๋ก๋ ๊ฐ์ ์ฟผ๋ฆฌ๋ฅผ ์์ฑํ ์ ์์ง๋ง BETWEEN ์ฐ์ฐ์๋ฅผ ์ด์ฉํ๋ฉด ๋ ๊ฐ๋
์ฑ์ด ์ข์ ์ฟผ๋ฆฌ๋ฅผ ์์ฑํ ์ ์๋ค.
์์์ ๋ฐฐ์ด ์ฐ์ฐ์๋ค์ ์ด์ฉํ์ฌ ์ฟผ๋ฆฌ๋ฅผ ์์ฑํ๊ณ , ์ด๋ฆ ๊ฒ์ ๊ธฐ๋ฅ์ ๊ตฌํํด๋ณด์๋ค.
๐ ์์ฑ
<%@ page language="java" contentType="text/html; charset=UTF-8" pageEncoding="UTF-8"%>
<%@ page import = "java.sql.*" %>
<%@ page import = "vo.*" %>
<%@ page import = "java.util.*" %>
<%
// ์ด๋ฆ ๊ฒ์ ๊ธฐ๋ฅ ์ถ๊ฐ
// 1) Controller Layer : ์์ฒญ๊ฐ์ ์ฒ๋ฆฌํ๋ ๋ถ๋ถ
// ์์ฒญ ์ฒ๋ฆฌํด์ผํ ํ๋ผ๋ฏธํฐ๊ฐ : ํ์ฌํ์ด์ง, ํํ์ด์ง๋น ์ถ๋ ฅํ ํ์ ์, ๊ฒ์๋จ์ด
// ํ์ฌํ์ด์ง, ํํ์ด์ง๋น ์ถ๋ ฅํ ํ์ ์(String) : Integer -> int ๋ก ๋ฐ๊ฟ์ผํจ
// ํ๋ผ๋ฏธํฐ๊ฐ์ด null์ธ์ง ๋๋ฒ๊น
System.out.println(request.getParameter("currentPage") + " <-- empListBySearch param currentPage");
System.out.println(request.getParameter("rowperPage") + " <-- empListBySearch param rowperPage");
System.out.println(request.getParameter("searchWord") + " <-- empListBySearch param searchWord");
// ํ๋ผ๋ฏธํฐ๊ฐ null ์ ํจ์ฑ ๊ฒ์ฌ(null์ด์ด๋ ๋๋์ง, null์ด๋ฉด ์ด๋ป๊ฒ ์ฒ๋ฆฌํ ์ง)
// ํ์ฌํ์ด์ง : null์ด ์๋ ๊ฒฝ์ฐ Integer๋ก int ๋ณ์์ ํ๋ผ๋ฏธํฐ๊ฐ ์ ์ฅ, null์ผ ๊ฒฝ์ฐ ๊ธฐ๋ณธ๊ฐ(1ํ์ด์ง) ์ ์ฅ
// ํํ์ด์ง๋น ์ถ๋ ฅํ ํ์ ์ : null์ด ์๋ ๊ฒฝ์ฐ Integer๋ก int ๋ณ์์ ํ๋ผ๋ฏธํฐ๊ฐ ์ ์ฅ, null์ผ ๊ฒฝ์ฐ ๊ธฐ๋ณธ๊ฐ(10๊ฐ) ์ ์ฅ
// ๊ฒ์๋จ์ด : null์ด ์๋ ๊ฒฝ์ฐ String ๋ณ์์ ํ๋ผ๋ฏธํฐ๊ฐ ์ ์ฅ, null์ผ ๊ฒฝ์ฐ ""(๊ณต๋ฐฑ) ์ ์ฅ
// ๊ฒ์๋จ์ด๊ฐ null์ด๊ฑฐ๋ ๊ณต๋ฐฑ์ผ ๋ ๋๋ค ์คํํ ์ฟผ๋ฆฌ๋ ๊ฐ์๋ฐ, ""(๊ณต๋ฐฑ)์ผ๋ก ํต์ผ ์ํค์ง ์์ผ๋ฉด ๋ถ๊ธฐ๋ฅผ ๋๋ฒ ํด์ผํ๊ธฐ ๋๋ฌธ์
/*
int currentPage; // ๋ณ์์ ์๋ช
์ฃผ๊ธฐ๋๋ฌธ์ if๋ฌธ ๋ธ๋ก ๋ฐ์์ ์ ์ธ
if(request.getParameter("currentPage") != null) {
currentPage = Integer.parseInt(request.getParameter("currentPage"));
} else { // null์ผ ๊ฒฝ์ฐ
currentPage = 1; // ๋ง์ฝ์ ์ ์ธ ํ ๋ ์ด๊ธฐ๊ฐ์ 1๋ก ์ฃผ์๋ค๋ฉด ์ฝ๋๋ฅผ ์ค์ผ ์ ์์ ๊ฒ!
}
*/
int currentPage = 1;
if(request.getParameter("currentPage") != null) {
currentPage = Integer.parseInt(request.getParameter("currentPage"));
}
int rowPerPage = 10;
if(request.getParameter("rowPerPage") != null) {
rowPerPage = Integer.parseInt(request.getParameter("rowPerPage"));
}
String searchWord = "";
if(request.getParameter("searchWord") != null) {
searchWord = request.getParameter("searchWord");
}
// ํ๋ผ๋ฏธํฐ ๊ฐ ์ ํจ์ฑ ๊ฒ์ฌ ํ(๊ฒฐ๊ณผ ๋ณ์) ๋๋ฒ๊น
System.out.println(currentPage + " <-- empListBySearch currentPage");
System.out.println(searchWord + " <-- empListBySearch searchWord");
System.out.println(rowPerPage + " <-- empListBySearch rowperPage");
// 2) Model Layer : ๋ชจ๋ธ๊ฐ(view์ ์ถ๋ ฅํ ๋ชจ๋ ๋ด์ฉ)์ ์์ฑํ๊ธฐ ๊น์ง์ ๋ชจ๋ ๋ถ๋ถ
// Controller Layer์์ ์ฒ๋ฆฌํ ๊ฒฐ๊ณผ ๋ณ์(currentPage, rowPerPage, searchWord)๋ฅผ ๊ฐ๊ณตํด์, ๋ชจ๋ธ์ ์์ฑํ๋ค
// DB ํธ์ถ
// ํธ์ถ์ ํ์ํ ๋ณ์ ์์ฑ
String driver = "org.mariadb.jdbc.Driver";
String dbUrl = "jdbc:mariadb://127.0.0.1:3306/employees";
String dbUser = "****";
String dbPw = "****";
Class.forName(driver);
Connection conn = DriverManager.getConnection(dbUrl, dbUser, dbPw);
// ์ฒซ๋ฒ์งธ ๋ชจ๋ธ๊ฐ(searchWord์ ๊ฐ์ ๋ฐ๋ผ ๋ค๋ฅธ ํ์ด์ง) ๊ตฌํ๊ธฐ
// ๋ชจ๋ธ์ ์์ฑํ๊ธฐ ์ํ ๋ณ์ ์ถ๊ฐ
int startRow = (currentPage - 1) * rowPerPage;
System.out.println(startRow + " <-- empListBySearch startRow"); // ๋๋ฒ๊น
// ๋์ ์ฟผ๋ฆฌ(๋ถ๊ธฐ๋๋ ์ฟผ๋ฆฌ) ์์ฑ
String sql = null; // ๋์ ์ฟผ๋ฆฌ๋ if๋ฌธ์ ์์ฑํด์ผํ๊ธฐ ๋๋ฌธ์ if๋ฌธ ๋ฐ์์ ๋จผ์ ์ ์ธ
PreparedStatement stmt = null;
if(searchWord.equals("")) { // if(searchWord.equals("") == true))
sql = "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 LIMIT ?, ?";
stmt = conn.prepareStatement(sql);
stmt.setInt(1, startRow);
stmt.setInt(2, rowPerPage);
} else { // WHERE CONCAT(first_name,' ',last_name) LIKE ? ์ถ๊ฐ๋จ // CONCAT๋ก ๋ ์ปฌ๋ผ๊ฐ์ ํฉ์ณ์ฃผ์ด์ผ... ์ด๋ถ๋ถ ๋ค์ ๋ณด๊ธฐ
sql = "SELECT emp_no empNo, birth_date birthDate, first_name firstName, last_name lastName, gender gender, hire_date hireDate FROM employees WHERE CONCAT(first_name,' ',last_name) LIKE ? ORDER BY emp_no ASC LIMIT ?, ?";
stmt = conn.prepareStatement(sql);
stmt.setString(1, "%"+searchWord+"%");
stmt.setInt(2, startRow);
stmt.setInt(3, rowPerPage);
}
System.out.println(stmt + "<-- empListBySearch ์์ฑ๋ ๋์ ์ฟผ๋ฆฌ");
ResultSet rs = stmt.executeQuery();
// ์ผ๋ฐ์ ์ธ ์๋ฃ๊ตฌ์กฐ(๋ชจ๋ธ)๋ก ๋ณ๊ฒฝ // ์ฌ๋ฌํ(list)์ ์ถ๋ ฅํด์ผํ๋ฏ๋ก ArrayList๋ก ๋ณ๊ฒฝ
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);
}
// ์ฒซ๋ฒ์งธ ๋ชจ๋ธ๊ฐ ๋๋ฒ๊น
System.out.println(empList.size() + "<-- empListBySearch empList.size()"); // ArrayList๋ size
for(Emp e : empList) {
System.out.println(e.firstName + " " + e.lastName);
}
// ๋๋ฒ์งธ ๋ชจ๋ธ๊ฐ(์ด ํ์ด์ง ์, ๋ง์ง๋ง ํ์ด์ง์ ๋๋ฒ) ๊ตฌํ๊ธฐ
// ๋ชจ๋ธ์ ์์ฑํ๊ธฐ ์ํ ๋ณ์ ์ถ๊ฐ
int totalCount = 0;
int lastPage = 0;
// totalCount๋ฅผ ๊ตฌํ๊ธฐ ์ํ ์ฟผ๋ฆฌ ์์ฑ
String sql2 = "SELECT count(*) FROM employees"; // count(*) ๋ฐ์ดํฐ์ ๋ชจ๋ ํ์ ์๋ฅผ ์ผ๋ค
PreparedStatement stmt2 = conn.prepareStatement(sql2);
ResultSet rs2 = stmt2.executeQuery();
// ๋ณ์์ ์ ์ฅ
if(rs2.next()) {
totalCount = rs2.getInt("count(*)");
}
System.out.println(totalCount + "<-- empListBySearch ๋ชจ๋ ๋ฐ์ดํฐํ์ ์");
// lastPage ๊ตฌํ๊ธฐ
lastPage = totalCount / rowPerPage;
if(totalCount % rowPerPage != 0) {
lastPage = lastPage + 1; // ํ์ด์ง๊ฐ ๋ฑ ๋๋์ด๋จ์ด์ง์ง ์์ผ๋ฉด ํํ์ด์ง๊ฐ ๋ ํ์ํ๋ค
}
System.out.println(lastPage + "<-- empListBySearch ๋ง์ง๋ง ํ์ด์ง ๋๋ฒ");
// ๋์ด ๊ณ์ฐํ๊ธฐ
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 + "๋
" + todayMonth + "์" + todayDate + "์ผ");
// 3) View Layer : ์ฌ์ฉ์์๊ฒ ๋ณด์ฌ์ง๋ ๋ถ๋ถ์ body๋ถํฐ...
%>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>empListBySearch</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>์ฌ์ ๋ชฉ๋ก</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="./empListBySearch.jsp" method="get">
<label>์ด๋ฆ๊ฒ์ : </label>
<input type="text" name="searchWord" value="<%=searchWord%>">
<button type="submit">๊ฒ์</button>
</form>
</div>
</div>
<!-- ํ์ด์ง -->
<div class="mt-5 p-4 bg-secondary text-white text-center">
<%
if(currentPage > 1) { // ์ฒซ๋ฒ์งธ ํ์ด์ง(1)๋ณด๋ค ํฐ ํ์ด์ง๋ถํฐ "์ด์ "์ ์ถ๋ ฅ
%>
<a href="./empListBySearch.jsp?currentPage=<%=currentPage - 1%>&searchWord=<%=searchWord%>&rowPerPage=<%=rowPerPage%>" class="btn btn-primary btn-sm"> ์ด์ </a>
<%
}
%>
<%=currentPage%>ํ์ด์ง <!-- ํ์ฌํ์ด์ง ์ถ๋ ฅ -->
<%
if(currentPage < lastPage) { // ๋ง์ง๋ง ํ์ด์ง ๋๋ฒ๋ณด๋ค ์์ ํ์ด์ง๊น์ง๋ง "๋ค์"์ ์ถ๋ ฅ
%>
<a href="./empListBySearch.jsp?currentPage=<%=currentPage + 1%>&searchWord=<%=searchWord%>&rowPerPage=<%=rowPerPage%>" class="btn btn-primary btn-sm"> ๋ค์ </a>
<%
}
%>
</div>
</body>
</html>
๐ ๊ฒฐ๊ณผ
ํ๋ก๊ทธ๋จ์ ์ง๋ค ๋ณด๋ฉด ๋๋ฒ๊น
์ ์ถ๋ ฅํ๋ ์ฝ์์ฐฝ์ด ์ด์ง๋ฌ์ ๊ฐ๋
์ฑ์ด ๋จ์ด์ง๋ ๊ฒฝ์ฐ๊ฐ ๋ง๋ค. ๋ํ ์ถํ ํ์
์งํ์์๋ ๋๊ฐ ์์ฑํ ๋๋ฒ๊น
์ฝ๋์ธ์ง ์ฝ๊ฒ ๊ตฌ๋ถํ๊ธฐ ์ํด ์ฝ์์ฐฝ์ ์ถ๋ ฅ ์์์ ๋ณ๊ฒฝํ๋ฉด ์ข๋ค.
๐ ์์ฑ
// ์ฝ์์ฐฝ ์ถ๋ ฅ๋ด์ฉ์ ์๊น ์
ํ๊ธฐ
// RESET ANSI CODE ์ฌ์ฉ
/*
RED -> \u001B[31m
GREEN -> \u001B[32m
RED_background -> \u001B[41m
GREEN_background -> \u001B[42m
RESET -> \u001B[0m
๋ฆฌ์
์ฝ๋๋ฅผ ๋ค์ ๋ถ์ฌ์ฃผ์ง ์์ผ๋ฉด ๋ชจ๋ ์ฝ์ ์ถ๋ ฅ๋ด์ฉ์ด ํด๋น ์๊น๋ก ๋ฐ๋
*/
// font color
System.out.println("\u001B[31m hello \u001B[0m");
System.out.println("\u001B[32m hello \u001B[0m");
// background color
System.out.println("\u001B[41m hello \u001B[0m");
System.out.println("\u001B[42m hello \u001B[0m");
// ์ฐ๊ธฐ ๊ท์ฐฎ๋ค๋ฉด..? ๋ณ์๋ฅผ ์ด์ฉํ์!
// final์ ๋์ด์ ๋ฐ๊ฟ ์ผ์ด ์๋ ๋ณ์์ ๋ถ์ธ๋ค // final ๋ณ์๋ ํต์์ ์ผ๋ก ๋๋ฌธ์
final String RED = "\u001B[31m";
final String BG_RED = "\u001B[41m";
final String GREEN = "\u001B[32m";
final String BG_GREEN = "\u001B[42m";
final String RESET = "\u001B[0m";
System.out.println(RED+"hello"+RESET);
System.out.println(BG_RED+"hello"+RESET);
System.out.println(GREEN+"hello"+RESET);
System.out.println(BG_GREEN+"hello"+RESET);
// ๋ ๋ค์ํ ์์์ ๊ตฌ๊ธ๋ง
๐ ๊ฒฐ๊ณผ
์ง๊ธ๊น์ง ๋ฐฐ์ด ๊ธฐ๋ฅ์ผ๋ก ์ฑ๋ณ ๊ฒ์ ๊ธฐ๋ฅ๊ณผ ์ด๋ฆ ๊ฒ์ ๊ธฐ๋ฅ์ ํํ์ด์ง์ ํฉ์ณ๋ณด์๋ค. (+์ฝ์ ์ถ๋ ฅ ์์ ๋ณ๊ฒฝ!)
๐ ์์ฑ
<%@ page language="java" contentType="text/html; charset=UTF-8" pageEncoding="UTF-8"%>
<%@ page import = "java.sql.*" %>
<%@ page import = "vo.*" %>
<%@ page import = "java.util.*" %>
<%
// ์ฝ์์ฐฝ ์ถ๋ ฅ ์์ ์ง์
final String RED = "\u001B[31m";
final String BG_RED = "\u001B[41m";
final String GREEN = "\u001B[32m";
final String BG_GREEN = "\u001B[42m";
final String RESET = "\u001B[0m";
// ์ฑ๋ณ ๊ฒ์ ๊ธฐ๋ฅ, ์ด๋ฆ ๊ฒ์ ๊ธฐ๋ฅ ์ถ๊ฐ
// Controller Layer : ์์ฒญ๊ฐ์ ์ฒ๋ฆฌํ๋ ๋ถ๋ถ
// ์์ฒญ์ฒ๋ฆฌํด์ผํ ํ๋ผ๋ฏธํฐ ๊ฐ: currentPage, rowPerPage, searchWord, gender
// currentPage(null, ๊ฐ), rowPerPage(null, ๊ฐ), gender(null, "", "M/F") , searchWord(null, "", "๊ฐ")
// ํ๋ผ๋ฏธํฐ๊ฐ์ด null์ธ์ง ๋๋ฒ๊น
System.out.println(RED+request.getParameter("currentPage") + " <-- empListBySearch param currentPage");
System.out.println(request.getParameter("rowperPage") + " <-- empListBySearch param rowperPage");
System.out.println(request.getParameter("gender") + " <-- empListBySearch param gender");
System.out.println(request.getParameter("searchWord") + " <-- empListBySearch param searchWord"+RESET);
// ํ๋ผ๋ฏธํฐ๊ฐ null ์ ํจ์ฑ ๊ฒ์ฌ (null์ด์ด๋ ๋๋์ง, null์ด๋ฉด ์ด๋ป๊ฒ ์ฒ๋ฆฌํ ์ง ๊ฒฐ์ )
/*
int currentPage; // ๋ณ์์ ์๋ช
์ฃผ๊ธฐ๋๋ฌธ์ if๋ฌธ ๋ธ๋ก ๋ฐ์์ ์ ์ธ
if(request.getParameter("currentPage") != null) {
currentPage = Integer.parseInt(request.getParameter("currentPage"));
} else { // null์ผ ๊ฒฝ์ฐ
currentPage = 1; // currentPage์ ๊ฐ์ด 1์ด์๋ค๋ฉด ์ด ๋ถ๋ถ์ ์๋ต ๊ฐ๋ฅ
}
์ฆ, null์ผ ๊ฒฝ์ฐ์ ์ค์ผํ ๊ฐ์ ๋ณ์๋ฅผ ์ ์ธํ ๋ ์ฃผ๊ณ ์์ํ๋ค๋ฉด if๋ฌธ ์์ฑ์์ else์ ์์ฑํ ์๊ณ ๋ฅผ ๋ ์ ์๋ค
*/
// currentPage : null์ด ์๋ ๊ฒฝ์ฐ Integer๋ก int ๋ณ์์ ํ๋ผ๋ฏธํฐ๊ฐ ์ ์ฅ, null์ผ ๊ฒฝ์ฐ ๊ธฐ๋ณธ๊ฐ(1ํ์ด์ง) ์ ์ฅ
int currentPage = 1;
if(request.getParameter("currentPage") != null) {
currentPage = Integer.parseInt(request.getParameter("currentPage"));
}
// rowPerPage : null์ด ์๋ ๊ฒฝ์ฐ Integer๋ก int ๋ณ์์ ํ๋ผ๋ฏธํฐ๊ฐ ์ ์ฅ, null์ผ ๊ฒฝ์ฐ ๊ธฐ๋ณธ๊ฐ(10๊ฐ) ์ ์ฅ
int rowPerPage = 10;
if(request.getParameter("rowPerPage") != null) {
rowPerPage = Integer.parseInt(request.getParameter("rowPerPage"));
}
// gender : null์ด ์๋ ๊ฒฝ์ฐ String ๋ณ์์ ํ๋ผ๋ฏธํฐ๊ฐ ์ ์ฅ, null์ผ ๊ฒฝ์ฐ ""(๊ณต๋ฐฑ) ์ ์ฅ
String gender = "";
if(request.getParameter("gender") != null) {
gender = request.getParameter("gender");
}
// searchWord : null์ด ์๋ ๊ฒฝ์ฐ String ๋ณ์์ ํ๋ผ๋ฏธํฐ๊ฐ ์ ์ฅ, null์ผ ๊ฒฝ์ฐ ""(๊ณต๋ฐฑ) ์ ์ฅ
String searchWord = "";
if(request.getParameter("searchWord") != null) {
searchWord = request.getParameter("searchWord");
}
// null์ผ ๊ฒฝ์ฐ์ ""(๊ณต๋ฐฑ)์ผ๋ก ์ฃผ๋ ์ด์ -> null์ด๊ฑฐ๋ ๊ณต๋ฐฑ์ผ ๋ ๋๋ค ์คํํ ์ฟผ๋ฆฌ๋ ๊ฐ์ผ๋ฏ๋ก, ๊ฐ์ ์ฟผ๋ฆฌ๋ก ๋๋ฒ ๋ถ๊ธฐํ์ง ์์๋ ๋๋๋ก ""(๊ณต๋ฐฑ)์ผ๋ก ํต์ผ
// ํ๋ผ๋ฏธํฐ ๊ฐ ์ ํจ์ฑ ๊ฒ์ฌ ํ(๊ฒฐ๊ณผ ๋ณ์) ๋๋ฒ๊น
System.out.println(GREEN+currentPage + " <-- empListBySearch currentPage");
System.out.println(rowPerPage + " <-- empListBySearch rowperPage");
System.out.println(gender + " <-- empListBySearch gender");
System.out.println(searchWord + " <-- empListBySearch searchWord");
// Model Layer : ๋ชจ๋ธ๊ฐ(view์ ์ถ๋ ฅํ ๋ชจ๋ ๋ด์ฉ)์ ์์ฑํ๊ธฐ ๊น์ง์ ๋ชจ๋ ๋ถ๋ถ
// Controller Layer์์ ์ฒ๋ฆฌํ ๊ฒฐ๊ณผ ๋ณ์(currentPage, rowPerPage, gender, searchWord)๋ฅผ ๊ฐ๊ณตํด์, ๋ชจ๋ธ๊ฐ์ ์์ฑํ๋ค
// DB ํธ์ถ // ํธ์ถ์ ํ์ํ ๋ณ์ ์์ฑ
String driver = "org.mariadb.jdbc.Driver";
String dbUrl = "jdbc:mariadb://127.0.0.1:3306/employees";
String dbUser = "****";
String dbPw = "****";
Class.forName(driver);
Connection conn = DriverManager.getConnection(dbUrl, dbUser, dbPw);
// ๋ชจ๋ธ์ ์์ฑํ๊ธฐ ์ํ ๋ณ์ ์ถ๊ฐ
int startRow = (currentPage - 1) * rowPerPage;
System.out.println(startRow + " <-- empListBySearch startRow"+RESET); // ๋๋ฒ๊น
// ์ฒซ๋ฒ์งธ ๋์ ์ฟผ๋ฆฌ (๋ถ๊ธฐ๋๋ ์ฟผ๋ฆฌ)
// ์์ฑ ์์: ๊ฐ์ฅ ์ฟผ๋ฆฌ๊ฐ ๊ฐ๋จํ ์กฐ๊ฑด ๋จผ์ ์์ฑ (๋ฌผ์ํ๊ฐ ์ ๊ฑฐ๋ ์ฟผ๋ฆฌ๊ฐ ๋ ๋ณต์กํ ๊ฒ๋ถํฐ)
// 1) ๋๋ค ๊ณต๋ฐฑ 2) searchWord๋ง ๊ณต๋ฐฑ(gender์ ๊ฐ์ด ์๋๊ฒฝ์ฐ) 3) gender๋ง ๊ณต๋ฐฑ(searchWord์ ๊ฐ์ด ์๋ ๊ฒฝ์ฐ) 4) ๋๋ค ๊ฐ์ด ์์
// 1) SELECT * FROM employees ORDER BY emp_no ASC LIMIT ?, ?
// 2) SELECT * FROM employees WHERE gender = ? ORDER BY emp_no ASC LIMIT ?, ?
// 3) SELECT * FROM employees WHERE CONCAT(first_name,' ',last_name) LIKE ? ORDER BY emp_no ASC LIMIT ?, ?
// 4) SELECT * FROM employees WHERE gender = ? AND CONCAT(first_name,' ',last_name) LIKE ? ORDER BY emp_no ASC LIMIT ?, ?
// ODERT BY์ ๊ธฐ๋ณธ๊ฐ์ด ๊ธฐ๋ณธํค(emp_no) ๊ธฐ์ค์ผ๋ก ์ค๋ฆ์ฐจ์(ASC)์ด๋ฏ๋ก ์๋ต๊ฐ๋ฅํ์ง๋ง ์ ๋ถ ์์ฑํ๋ค
// ๋์ ์ฟผ๋ฆฌ๋ if๋ฌธ์ ์์ฑํด์ผํ๊ธฐ ๋๋ฌธ์ if๋ฌธ ๋ฐ์์ ๋จผ์ ์ ์ธ
String sql = null;
PreparedStatement stmt = null;
if(gender.equals("") && searchWord.equals("")) { // 1) ๋๋ค ๊ณต๋ฐฑ
sql = "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 LIMIT ?, ?";
stmt = conn.prepareStatement(sql);
stmt.setInt(1, startRow);
stmt.setInt(2, rowPerPage);
} else if(!gender.equals("") && searchWord.equals("")) { // 2) searchWord๋ง ๊ณต๋ฐฑ(gender์ ๊ฐ์ด ์๋๊ฒฝ์ฐ)
sql = "SELECT emp_no empNo, birth_date birthDate, first_name firstName, last_name lastName, gender gender, hire_date hireDate FROM employees WHERE gender = ? ORDER BY emp_no ASC LIMIT ?, ?";
stmt = conn.prepareStatement(sql);
stmt.setString(1, gender);
stmt.setInt(2, startRow);
stmt.setInt(3, rowPerPage);
} else if(gender.equals("") && !searchWord.equals("")) { // 3) gender๋ง ๊ณต๋ฐฑ(searchWord์ ๊ฐ์ด ์๋ ๊ฒฝ์ฐ)
sql = "SELECT emp_no empNo, birth_date birthDate, first_name firstName, last_name lastName, gender gender, hire_date hireDate FROM employees WHERE CONCAT(first_name,' ',last_name) LIKE ? ORDER BY emp_no ASC LIMIT ?, ?";
stmt = conn.prepareStatement(sql);
stmt.setString(1, "%"+searchWord+"%");
stmt.setInt(2, startRow);
stmt.setInt(3, rowPerPage);
} else { // 4) ๋๋ค ๊ฐ์ด ์์
sql = "SELECT emp_no empNo, birth_date birthDate, first_name firstName, last_name lastName, gender gender, hire_date hireDate FROM employees WHERE gender = ? AND CONCAT(first_name,' ',last_name) LIKE ? ORDER BY emp_no ASC LIMIT ?, ?";
stmt = conn.prepareStatement(sql);
stmt.setString(1, gender);
stmt.setString(2, "%"+searchWord+"%");
stmt.setInt(3, startRow);
stmt.setInt(4, rowPerPage);
}
// ์์ฑ๋ ๋์ ์ฟผ๋ฆฌ ๋๋ฒ๊น
ํด๋ณด๊ธฐ
System.out.println(BG_GREEN+stmt + " <-- empList ์์ฑ๋ ์ฒซ๋ฒ์งธ ๋์ ์ฟผ๋ฆฌ"+RESET);
ResultSet rs = stmt.executeQuery();
// ์ผ๋ฐ์ ์ธ ์๋ฃ๊ตฌ์กฐ(๋ชจ๋ธ)๋ก ๋ณ๊ฒฝ // ์ฌ๋ฌํ(list)์ ์ถ๋ ฅํด์ผํ๋ฏ๋ก ArrayList๋ก ๋ณ๊ฒฝ
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);
}
// ๋ชจ๋ธ์ ์์ฑํ๊ธฐ ์ํ ๋ณ์ ์ถ๊ฐ
int totalCount = 0; // count(*)๋ก ๊ตฌํ ์ ์๋ค
int lastPage = 0; // totalCount / rowPerPage๋ก ๊ตฌํ ์ ์๋ค
// ๋๋ฒ์งธ ๋์ ์ฟผ๋ฆฌ
// totalCount์ lastPage์ ๊ฐ์ด ๊ฒ์ ์กฐ๊ฑด(์ฒซ๋ฒ์งธ ๋์ ์ฟผ๋ฆฌ์ ์กฐ๊ฑด)์ ๋ฐ๋ผ ๋ฌ๋ผ์ง๋ฏ๋ก if๋ฌธ์ ์์ฑํด์ผํ๋ค
// SELECT count(*) FROM employees ์ ์กฐ๊ฑด์ ๋ฐ๋ผ WHERE์ ์ถ๊ฐ
String sql2 = null;
PreparedStatement stmt2 = null;
if(gender.equals("") && searchWord.equals("")) { // 1) ๋๋ค ๊ณต๋ฐฑ
sql2 = "SELECT count(*) FROM employees";
stmt2 = conn.prepareStatement(sql2);
} else if(!gender.equals("") && searchWord.equals("")) { // 2) searchWord๋ง ๊ณต๋ฐฑ(gender์ ๊ฐ์ด ์๋๊ฒฝ์ฐ)
sql2 = "SELECT count(*) FROM employees WHERE gender = ?";
stmt2 = conn.prepareStatement(sql2);
stmt2.setString(1, gender);
} else if(gender.equals("") && !searchWord.equals("")) { // 3) gender๋ง ๊ณต๋ฐฑ(searchWord์ ๊ฐ์ด ์๋ ๊ฒฝ์ฐ)
sql2 = "SELECT count(*) FROM employees WHERE CONCAT(first_name,' ',last_name) LIKE ?";
stmt2 = conn.prepareStatement(sql2);
stmt2.setString(1, "%"+searchWord+"%");
} else { // 4) ๋๋ค ๊ฐ์ด ์์
sql2 = "SELECT count(*) FROM employees WHERE gender = ? AND CONCAT(first_name,' ',last_name) LIKE ?";
stmt2 = conn.prepareStatement(sql2);
stmt2.setString(1, gender);
stmt2.setString(2, "%"+searchWord+"%");
}
// ์์ฑ๋ ๋์ ์ฟผ๋ฆฌ ๋๋ฒ๊น
ํด๋ณด๊ธฐ
System.out.println(BG_RED+stmt2 + " <-- empList ์์ฑ๋ ๋๋ฒ์งธ ๋์ ์ฟผ๋ฆฌ"+RESET);
ResultSet rs2 = stmt2.executeQuery();
// ์ฟผ๋ฆฌ ์คํ ๊ฒฐ๊ณผ๊ฐ์ ๋ณ์์ ์ ์ฅ
if(rs2.next()) {
totalCount = rs2.getInt("count(*)");
}
System.out.println(GREEN+totalCount + " <-- empListBySearch ๋ชจ๋ ๋ฐ์ดํฐํ์ ์");
// lastPage ๊ตฌํ๊ธฐ
lastPage = totalCount / rowPerPage;
if(totalCount % rowPerPage != 0) { // ํ์ด์ง๊ฐ ๋ฑ ๋๋์ด๋จ์ด์ง์ง ์์ผ๋ฉด ๋๋จธ์ง๊ฐ ์ถ๋ ฅ๋ ํํ์ด์ง๊ฐ ๋ ํ์ํ๋ฏ๋ก
lastPage = lastPage + 1;
}
System.out.println(lastPage + " <-- empListBySearch ๋ง์ง๋ง ํ์ด์ง ๋๋ฒ");
// ๋์ด ๊ณ์ฐํ๊ธฐ
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 + "๋
" + todayMonth + "์" + todayDate + "์ผ"+RESET);
// View Layer : ์ฌ์ฉ์์๊ฒ ๋ณด์ฌ์ง๋ ๋ถ๋ถ์ body๋ถํฐ...
%>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>empList3.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="container mt-3">
<div class="p-5 bg-primary text-white text-center">
<h1>์ฌ์ ๋ชฉ๋ก</h1>
</div>
<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")) {
%>
<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 method="get" action="./empList3.jsp">
<label>์ฑ๋ณ : </label>
<select name="gender">
<%
if(gender.equals("")) {
%>
<option value="" selected="selected">์ ํ</option>
<option value="M">๋จ</option>
<option value="F">์ฌ</option>
<%
} else if(gender.equals("M")) {
%>
<option value="">์ ํ</option>
<option value="M" selected="selected">๋จ</option>
<option value="F">์ฌ</option>
<%
} else {
%>
<option value="">์ ํ</option>
<option value="M">๋จ</option>
<option value="F" selected="selected">์ฌ</option>
<%
}
%>
</select>
<label>์ด๋ฆ๊ฒ์ : </label>
<input type="text" name="searchWord" value="<%=searchWord%>">
<!--
<label>์
์ฌ๋
๋ : </label>
<input type="number" name="beginYear">
~
<input type="number" name="endYear">
-->
<button type="submit">์กฐํ</button>
</form>
</div>
<!-- ํ์ด์ง ๋ค๋น๊ฒ์ด์
-->
<div class="mt-5 p-4 bg-secondary text-white text-center">
<%
if(currentPage > 1) { // ์ฒซ๋ฒ์งธ ํ์ด์ง(1)๋ณด๋ค ํฐ ํ์ด์ง๋ถํฐ "์ด์ "์ ์ถ๋ ฅ
%>
<a href="./empList3.jsp?currentPage=<%=currentPage - 1%>&rowPerPage=<%=rowPerPage%>&gender=<%=gender%>&searchWord=<%=searchWord%>" class="btn btn-primary btn-sm"> ์ด์ </a>
<%
}
%>
<%=currentPage%>ํ์ด์ง <!-- ํ์ฌํ์ด์ง ์ถ๋ ฅ -->
<%
if(currentPage < lastPage) { // ๋ง์ง๋ง ํ์ด์ง ๋๋ฒ๋ณด๋ค ์์ ํ์ด์ง๊น์ง๋ง "๋ค์"์ ์ถ๋ ฅ
%>
<a href="./empList3.jsp?currentPage=<%=currentPage + 1%>&rowPerPage=<%=rowPerPage%>&gender=<%=gender%>&searchWord=<%=searchWord%>" class="btn btn-primary btn-sm"> ๋ค์ </a>
<%
}
%>
</div>
</div>
</body>
</html>
๐ ๊ฒฐ๊ณผ