DB09 - 12/01(sql35~39, jdbc40~41) - like, in, between, limit

35like.sql

♠ LIKE : WHERE 와 함께 사용되며, = 대신 쓰인다

특수기호 "%" 와 함께 사용되는데, 여기서 "%" 는 아무거나 (0개 이상) 라는 의미

1. 'a%' : a 로 시작하는 (% 자리는 아무거나)

2. '%a' : a 로 끝나는 (% 자리는 아무거나)

3. '%a%' : 중간에 (앞뒤 포함) a 를 포함하는 (% 자리는 아무거나)

4. 'a%b' : a 로 시작해서 b 로 끝나는 (% 자리는 아무거나)

* 또 다른 특수기호 "_" : "_" 개수가 자릿수를 나타냄

1. 'a_' : a + 한 글자 아무거나

2. '__a' : 두 글자 아무거나 + a

SELECT * FROM 테이블명 WHERE 컬럼명 LIKE 조건;

SELECT * FROM Customers
WHERE CustomerName = 'Alfreds Futterkiste';

SELECT * FROM Customers
WHERE CustomerName LIKE 'Alf%'; -- Alf 로 시작하는

SELECT * FROM Customers
WHERE CustomerName LIKE 'A%';

SELECT * FROM Customers
WHERE CustomerName LIKE '%A';

SELECT * FROM Customers
WHERE CustomerName LIKE '%A%';

예)

-- 연습1 : Employees 에서 LastName 에 'u'포함된 직원들 조회
SELECT * FROM Employees
WHERE LastName LIKE '%u%';
-- 연습2 : Employees 에서 Notes 에 'BA'가 포함된 직원들 조회
SELECT * FROM Employees
WHERE Notes LIKE '%BA%';

SELECT * FROM Customers
WHERE CustomerName LIKE 'Ernst Hande_'; -- _ : 한글자 아무거나

---

36in.sql

♠ IN : 다음에 오는 값들 중 하나라도 있으면 - 이라는 조건을 쓸 때 사용

SELECT * FROM 테이블명 WHERE 컬럼명 IN (컬럼값1, 컬럼값2, 컬럼값3, ..);

OR 을 사용하여 다음과 같이 작성할 수도 있지만 ↓

SELECT * FROM Customers 
WHERE Country = 'Germany' OR Country = 'Mexico' OR Country = 'USA';

이렇게 IN 을 써서 간단하게 작성할 수도 있다 ↓

SELECT * FROM Customers WHERE Country IN ('Germany', 'Mexico', 'USA');

> NOT IN : 다음에 오는 값들이 아닌 컬럼값만 조회 

SELECT * FROM Customers WHERE Country NOT IN ('Germany', 'Mexico', 'USA');

예)

-- 연습1 : 국가가 'USA', 'UK'인 Supplier들 조회 (IN 키워드 사용)
SELECT * FROM Suppliers WHERE Country IN ('usa', 'uk');
-- 연습2 : 국가가 'USA', 'UK'가 아닌 Supplier들 조회 (NOT IN 키워드 사용)
SELECT * FROM Suppliers WHERE Country NOT IN ('usa', 'uk');

-- 연습3 : Meat/Poultry 또는 Seafood 카테고리에 속한 상품명 조회
SELECT * FROM Products p JOIN Categories c ON p.CategoryID = c.CategoryID
WHERE CategoryName LIKE 'Meat%' OR CategoryName = 'Seafood';

SELECT * FROM Products p JOIN Categories c ON p.CategoryID = c.CategoryID
WHERE CategoryName IN ('Meat/Poultry', 'Seafood');

-- SUBQUERY 활용
SELECT * FROM Categories WHERE CategoryName IN  ('Meat/Poultry', 'Seafood');

SELECT * FROM Products WHERE CategoryID IN (6, 8);

SELECT * FROM Products WHERE CategoryID IN 
(SELECT CategoryID FROM Categories WHERE CategoryName IN ('Meat/Poultry', 'Seafood') );

-- 연습4 : Supplier가 있는 나라에 살고 있는 고객 목록 조회
SELECT Country FROM Suppliers;

SELECT * FROM Customers
WHERE Country IN (SELECT country FROM Suppliers);

---

37between.sql

♠ BETWEEN ~ AND ~ : 양 끝값 포함

SELECT * FROM 테이블명 WHERE 컬럼명 BETWEEN 값1 AND 값2;

이렇게 작성할수도 ↓

SELECT * FROM Products WHERE Price >= 10 AND Price <= 20;

하지만 BETWEEN 을 써서 더 간단히 작성 ↓

SELECT * FROM Products WHERE Price BETWEEN 10 AND 20;

예)

SELECT * FROM Products WHERE ProductName BETWEEN 'C' AND 'F';
SELECT * FROM Employees WHERE BirthDate BETWEEN '1958-01-01' AND '1958-12-31';

---

JDBC40Servlet

위에서 배운 LIKE 를 사용해서, 원하는 내용이 포함된 레코드를 조회하기

jdbc10

JDBC40Servlet

	protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
		// 0. 사전작업
		ServletContext application = request.getServletContext();
		DataSource ds = (DataSource) application.getAttribute("dbpool");
		EmployeeDao dao = new EmployeeDao();
		List<Employee> list = null;
		
		// 2. request 분석/가공
		String keyword = request.getParameter("keyword");
		keyword = keyword == null ? "" : keyword;
		
		String year = request.getParameter("year");
		
		// 3. business logic
		try (Connection con = ds.getConnection()) {
			list = dao.getListByNotesKeywordAndYear(con, keyword, year);
		} catch (Exception e) {
			e.printStackTrace();
		}
		
		// 4. add attribute
		request.setAttribute("list", list);
		
		// 5. forward
		String path = "/WEB-INF/view/jdbc10/v40.jsp";
		request.getRequestDispatcher(path).forward(request, response);
	}

EmployeeDao 객체 dao 생성, 리스트 생성

request.getParameter 로 keyword 받아옴 

(만약 keyword 를 받아오지 못해서 null 인 경우 문제가 생기므로, null 이면 빈 스트링 "", null 이 아니면 그대로 keyword 이도록 작성)

request.getParameter 로 year 받아옴

dao.getListByNotesKeywordAndYear (dao 의 메소드) 에 매개변수 con, keyword, year 전달하여 실행

그리고 반환받은 리스트를 list 에 저장하여 setAttribute 로 전달

v40.jsp 로 포워딩

 

jdbc10.dao

EmployeeDao

public class EmployeeDao {

	public List<Employee> getListByNotesKeywordAndYear(Connection con, String keyword, String year) {
		List<Employee> list = new ArrayList<Employee>();
		String sql = "SELECT employeeID, lastName, firstName, notes, birthDate "
				+ "FROM Employees "
				+ "WHERE (birthDate BETWEEN ? AND ?) "
				+ "     AND (notes LIKE ? "
				+ "     OR firstName LIKE ? "
				+ "     OR lastName LIKE ? )";

		try (PreparedStatement pstmt = con.prepareStatement(sql)) {
			pstmt.setString(1, year + "-01-01");
			pstmt.setString(2, year + "-12-31");
			pstmt.setString(3, "%" + keyword + "%");
			pstmt.setString(4, "%" + keyword + "%");
			pstmt.setString(5, "%" + keyword + "%");
			
			try (ResultSet rs = pstmt.executeQuery()) {
				while (rs.next()) {
					Employee emp = new Employee();
					emp.setEmployeeID(rs.getInt("employeeID"));
					emp.setFirstName(rs.getString("firstName"));
					emp.setLastName(rs.getString("lastName"));
					emp.setNotes(rs.getString("notes"));
					emp.setBirthDate(rs.getDate("birthDate").toLocalDate());

					list.add(emp);
				}
			}
		} catch (Exception e) {
			e.printStackTrace();
		}

		return list;
	}

}

sql 코드 ↓

SELECT EmployeeID, LastName, FirstName, BirthDate, Notes
FROM Employees 
WHERE (BirthDate BETWEEN "?-01-01" AND "?-12-31")
AND (Notes LIKE '%?%'
	OR LastName LIKE '%?%'
	OR FirstName LIKE '%?%');

Employees 테이블에서 BirthDate 가 "?(year)-01-01" ~ "?(year)-12-31" 사이인 것 중에서 

Notes 가 ?(keyword) 를 포함하거나 / LastName 이 ?(keyword) 를 포함하거나 / FirstName 이 ?(keyword) 를 포함하는 행의

EmployeeID, LastName, FirstName, BirthDate, Notes 컬럼 값을 조회

 

따라서 받아온 year, keyword 를 pstmt 로 sql 코드의 ? 에 채우고

실행하여 나온 컬럼값들을 각각 Employee 객체 emp 의 메소드 set~ 에 저장하여 emp 를 list 에 추가

메소드는 이 리스트를 반환

 

jdbc10.bean

Employee

public class Employee {

	private int employeeID;
	private String lastName;
	private String firstName;
	private LocalDate birthDate;
	private String notes;

	public void setBirthDate(LocalDate birthDate) {
		this.birthDate = birthDate;
	}

	public LocalDate getBirthDate() {
		return birthDate;
	}

	public int getEmployeeID() {
		return employeeID;
	}

	public void setEmployeeID(int employeeID) {
		this.employeeID = employeeID;
	}

	public String getLastName() {
		return lastName;
	}

	public void setLastName(String lastName) {
		this.lastName = lastName;
	}

	public String getFirstName() {
		return firstName;
	}

	public void setFirstName(String firstName) {
		this.firstName = firstName;
	}

	public String getNotes() {
		return notes;
	}

	public void setNotes(String notes) {
		this.notes = notes;
	}

}

 

jdbc10

v40.jsp

<body>

  <div class="container">
  
    <div class="row">
      <div class="col">
        <h1>직원 검색</h1>
      </div>
    </div>

    <div class="row">
      <div class="col">
        <form action="">
          <div class="col">
            <div class="form-group row">
              <label for="select1" class="col-2 col-form-label">태어난 해</label>
              
              <div class="col-4">
                <select name="year" id="select1" class="form-control">
                  <c:forEach begin="1928" end="1969" var="year">
                    <option value="${year }">${year }</option>
                  </c:forEach>
                </select>
              </div>
              
              <div class="col-6">
                <div class="input-group mb-3">
                  <input type="text" class="form-control" name="keyword" placeholder="검색어를 입력하세요">
                  <div class="input-group-append">
                    <button class="btn btn-outline-primary">검색</button>
                  </div>
                </div>
              </div>
              
            </div>
          </div>
        </form>
      </div>
    </div>

    <div class="row">
      <div class="col">
        <table class="table">
        
          <thead>
            <tr>
              <th>ID</th>
              <th>LastName</th>
              <th>FirstName</th>
              <th>BirthDate</th>
              <th>Notes</th>
            </tr>
          </thead>
          
          <tbody>
            <c:forEach items="${list }" var="emp">
              <tr>
                <td>${emp.employeeID }</td>
                <td>${emp.lastName }</td>
                <td>${emp.firstName }</td>
                <td>${emp.birthDate }</td>
                <td>${emp.notes }</td>
              </tr>
            </c:forEach>
          </tbody>
          
        </table>
      </div>
    </div>
    
  </div>
  
</body>

 

초기 화면 ↓

1928, c 검색한 결과 ↓

---

38limit.sql

♠ LIMIT : 결과창에서 일부 범위의 결과만을 조회하고 싶을 때

1. LIMIT + 개수

SELECT * FROM 테이블명 LIMIT 개수;
SELECT * FROM 테이블명 ORDER BY 컬럼명 LIMIT 개수;
SELECT * FROM 테이블명 ORDER BY 컬럼명 DESC LIMIT 개수;

SELECT * FROM Products ORDER BY Price ASC LIMIT 3;
SELECT * FROM Products ORDER BY Price DESC LIMIT 3;

-- 가장 늦게 태어난 직원 3명 조회
SELECT * FROM Employees ORDER BY BirthDate DESC LIMIT 3;

 

2. LIMIT + 시작위치, 개수

시작위치는 0부터 시작한다 (첫번째 줄이 0, 두번째 줄이 1, ...)

SELECT * FROM 테이블명 LIMIT 시작위치, 개수;
SELECT * FROM 테이블명 ORDER BY 컬럼명 LIMIT 시작위치, 개수;
SELECT * FROM 테이블명 ORDER BY 컬럼명 DESC LIMIT 시작위치, 개수;

SELECT * FROM Customers ORDER BY CustomerID LIMIT 0, 5;

SELECT * FROM Customers ORDER BY CustomerID LIMIT 5, 5;

 

> page 처리에 활용

한 페이지에 x 개의 데이터 조회

→ 1 페이지: LIMIT 0, x  / 2 페이지: LIMIT x, x / 3 페이지: LIMIT x*2, x / ... / n 페이지: LIMIT (n-1)*x, x

ex. 한 페이지에 10 개의 데이터 조회

→ 1 페이지: LIMIT 0, 10  / 2 페이지: LIMIT 10, 10 / 3 페이지: LIMIT 20, 10 / ... / n 페이지: LIMIT (n-1)*10, 10

 

마지막 페이지는 몇 페이지? (총 레코드 개수 필요 - COUNT)

→ 총 n 개의 레코드를 한 페이지에 x 개씩 조회한다면,

n ÷ x 계산의 몫 (←나누어떨어질때) 혹은 몫 + 1 (←나누어떨어지지않을때)

ex. Customers 테이블로 연습

SELECT count(*) FROM Customers; -- 92
-- 한 페이지에 10개의 데이터 조회한다면? 
-- 92/10 = 9 
-- 9+1 = 10 , 총 10개의 페이지 (마지막 페이지는 10페이지)

---

JDBC41Servlet

	protected void doGet(HttpServletRequest request, HttpServletResponse response)
			throws ServletException, IOException {
		// 0
		ServletContext application = request.getServletContext();
		DataSource ds = (DataSource) application.getAttribute("dbpool");
		CustomerDao dao = new CustomerDao();
		List<Customer> list = null;
		int endPage = 1;

		// 2 request
		String pageStr = request.getParameter("page");
		if (pageStr == null || pageStr.isEmpty()) {
			pageStr = "1";
		}
		int page = Integer.parseInt(pageStr);
		int rowPerPage = 10;

		// 3 business logic
		try (Connection con = ds.getConnection()) {
			list = dao.getListPaging(con, page, rowPerPage);

			int total = dao.getTotal(con);
			endPage = (int) Math.ceil(((double) total) / rowPerPage);
//			endPage = ((total - 1) / rowPerPage) + 1;
		} catch (Exception e) {
			e.printStackTrace();
		}

		// 4 add attribute
		request.setAttribute("list", list);
		request.setAttribute("endPage", endPage);

		// 5.forward
		String path = "/WEB-INF/view/jdbc10/v41.jsp";
		request.getRequestDispatcher(path).forward(request, response);
	}

CustomerDao 객체 dao 생성, 리스트 list 생성

endPage 선언 (1 로 초기화)

request.getParameter 로 page 값을 받아와 int 로 형변환하여 page 에 저장

rowPerPage = 10 설정 (즉, 한 페이지에 10개씩)

dao.ListPaging (CustomerDao 클래스의 ListPaging 메소드)에 매개변수로 con, page, rowPerPage 넘기고

실행하여 반환된 리스트를 list 에 저장

total = dao.getTotal(con) - dao 의 getTotal 메소드 실행하여 반환값 저장 (전체 레코드 수)

endPage = total / rowPerPage

setAttribute 로 endPage, list 전달

v41.jsp 로 포워딩 

 

CustomerDao

public class CustomerDao {

	public List<Customer> getListPaging(Connection con, int page, int rowPerPage) {
		List<Customer> list = new ArrayList<>();
		String sql = "SELECT CustomerID, CustomerName, "
				+ "ContactName, Address, PostalCode, Country, City "
				+ "FROM Customers "
				+ "ORDER BY CustomerID "
				+ "LIMIT ?, ? ";

		try (PreparedStatement pstmt = con.prepareStatement(sql)) {
			pstmt.setInt(1, (page - 1) * rowPerPage);
			pstmt.setInt(2, rowPerPage);

			try (ResultSet rs = pstmt.executeQuery()) {
				while (rs.next()) {
					Customer cus = new Customer();

					cus.setCustomerID(rs.getInt("CustomerID"));
					cus.setCustomerName(rs.getString("CustomerName"));
					cus.setContactName(rs.getString("ContactName"));
					cus.setCity(rs.getString("City"));
					cus.setAddress(rs.getString("Address"));
					cus.setCountry(rs.getString("Country"));
					cus.setPostalCode(rs.getString("PostalCode"));

					list.add(cus);
				}
			}
		} catch (Exception e) {
			e.printStackTrace();
		}

		return list;
	}

	public int getTotal(Connection con) {
		String sql = "SELECT count(*) FROM Customers ";

		try (Statement stmt = con.createStatement();
				ResultSet rs = stmt.executeQuery(sql)) {
			if (rs.next()) {
				return rs.getInt(1);
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		return 0;
	}

}

getListPaging 메소드

sql - SELECT CustomerID, CustomerName, ContactName, Address, PostalCode, Country, City

FROM Customers ORDER BY CustomerID LIMIT ?, ? ;

Customers 테이블을 CustomerID 기준 정렬하여 ? 에서 시작하여 ? 개수만큼 컬럼값들을 조회

매개변수로 받아온 page, rowPerPage 를 pstmt 를 이용해 sql ? 에 채워넣음

Customer 객체 cus 생성

sql 실행한 결과로 나온 컬럼값들을 각각 cus.set~ (Customer 클래스 set 메소드) 에 저장하고 cus 객체를 리스트에 추가 (while 반복)

list 반환

 

getTotal 메소드

sql - SELECT count(*) FROM Customers;

Customers 테이블 count 조회 (총 레코드 수)

sql 실행 결과 반환

 

Customer

public class Customer {
	private int customerID;
	private String customerName;
	private String contactName;
	private String city;
	private String country;
	private String postalCode;
	private String address;

	public int getCustomerID() {
		return customerID;
	}

	public void setCustomerID(int customerID) {
		this.customerID = customerID;
	}

	public String getCustomerName() {
		return customerName;
	}

	public void setCustomerName(String customerName) {
		this.customerName = customerName;
	}

	public String getContactName() {
		return contactName;
	}

	public void setContactName(String contactName) {
		this.contactName = contactName;
	}

	public String getCity() {
		return city;
	}

	public void setCity(String city) {
		this.city = city;
	}

	public String getCountry() {
		return country;
	}

	public void setCountry(String country) {
		this.country = country;
	}

	public String getPostalCode() {
		return postalCode;
	}

	public void setPostalCode(String postalCode) {
		this.postalCode = postalCode;
	}

	public String getAddress() {
		return address;
	}

	public void setAddress(String address) {
		this.address = address;
	}

}

 

v41.jsp

<body>
  <div class="container">
    <div class="row">
      <div class="col">
        <h1>고객들(page)</h1>
        <table class="table">
          <thead>
            <tr>
              <th>ID</th>
              <th>Customer Name</th>
              <th>Contact Name</th>
              <th>Address</th>
              <th>City</th>
              <th>PostalCode</th>
              <th>Country</th>
            </tr>
          </thead>
          <tbody>
            <c:forEach items="${list }" var="cus">
              <tr>
                <td>${cus.customerID }</td>
                <td>${cus.customerName }</td>
                <td>${cus.contactName }</td>
                <td>${cus.address }</td>
                <td>${cus.city }</td>
                <td>${cus.postalCode }</td>
                <td>${cus.country }</td>
              </tr>
            </c:forEach>
          </tbody>
        </table>
      </div>
    </div>

    <div class="row justify-content-center">
      <nav>
        <ul class="pagination">
          <c:forEach begin="1" end="${endPage }" var="num">
            <c:url value="/jdbc10/s41" var="link">
              <c:param name="page" value="${num }"></c:param>
            </c:url>
            <li class="page-item ${num eq param.page ? 'active' : '' } ${empty param.page and num eq 1 ? 'active' : ''}">
              <a href="${link }" class="page-link">${num }</a>
            </li>
          </c:forEach>
        </ul>
      </nav>
    </div>
  </div>
</body>

list 받아서 각 페이지 마다 데이터 출력

endPage 받아서 페이지 수 설정 가능

---

39functions.sql

♠ CONCAT : string 연결

CONCAT('str1', 'str2')

SELECT concat('abc', 'def') AS newStr; 
SELECT EmployeeID, concat(LastName, ' ' ,FirstName) Name, BirthDate, Notes FROM Employees;

♠ SUBSTR : 부분 문자열

SUBSTR('기존문자열', 시작위치, 길이)
단, 시작위치는 1부터 시작

SELECT substr('hello wolrd', 1, 4); -- hell
SELECT substr('hello world', 7, 5); -- world

♠ IFNULL : null 을 다른 값으로

IFNULL(컬럼명, null 대신 넣을 값)

SELECT CustomerName, IFNULL(ContactName, 'none') ContactName FROM Customers
ORDER BY CustomerID DESC; -- ContactName 값이 null 이라면 none 으로 대체

 

♠ COALESCE : 첫번째로 null 이 아닌 값

COALESCE(값1, 값2, 값3, ...)

SELECT coalesce(null, null, 'hello', null, 'world'); -- hello

SELECT CustomerName, coalesce(ContactName, 'none') FROM Customers 
ORDER BY CustomerID DESC; -- ContactName 이 null 이면 첫번째로 null 이 아닌 값이 none 이므로 none 반환
-- 이렇게 IFNULL 과 같이 쓰일수도 있다

♠ now() : 현재 날짜 시간

SELECT now();

 

연습)

-- 연습 : Customers 테이블 조회
--      단, Address, City, PostalCode, Country 컬럼을
--          ','를 구분자로 하여 연결된 값을 
--          FullAddress라는 컬럼으로 조회
--          또한 Address, City, PostalCode, Country 중 null값이 있는 경우
--               그 컬럼은 빈 스트링'' 으로 연결 
SELECT 
    CustomerID,
    CustomerName,
    ContactName,
    CONCAT(IFNULL(Address, ''),
            ', ',
            IFNULL(City, ''),
            ', ',
            IFNULL(PostalCode, ''),
            ', ',
            IFNULL(Country, '')) FullAddress
FROM
    Customers;