---
# System prepended metadata

title: Untitled

---

configuration <　! configration
index
<!DOCTYPE html>
<html xmlns:th="http://www.thymeleaf.org"
    th:replace="~{layout :: layout(~{::title},~{::body/content()})}">
<head>
<title>ログイン画面</title>
</head>
<body>
  <p>ログイン画面</p>
  <div id="main">
    <p>ユーザーIDとパスワードを入力してください。</p>
    <p class="error" th:text="${errMessage}"></p>
	<form action="/book_list/login" method="post">
      ユーザーID:<input type="text" name="bookUserId"><br>
      パスワード:<input type="password" name="password"><br>
      <input type="submit" value="ログイン">
    </form>
  </div>
</body>
</html>

header
<meta charset="UTF-8" />
<div id="header" th:fragment="layout-header">
	<h3>書籍一覧システム</h3>
	<a href="/book_list/logout">ログアウト</a>
</div>

footer
<meta charset="UTF-8" />
<div id="footer" th:fragment="layout-footer">

</div>

list
<!DOCTYPE html>
<html xmlns:th="http://www.thymeleaf.org"
    th:replace="~{layout :: layout(~{::title},~{::body/content()})}">
<head>
<title></title>
</head>
<body>

<form method="post" th:action="@{/list/search}">
商品名検索:<input type="text" name="name" />
<input type="submit" value="検索" />
</form>
<br/>

	<table>
		<tr>
			<th>書籍ID</th>
			<th>書籍名</th>
			<th>著者</th>
			<th>発行日</th>
			<th>在庫</th>
			<th>ジャンルID</th>
		</tr>
		<tr th:each="book:${book}">
			<td th:text="${book.bookId}"></td>
			<td th:text="${book.bookName}"></td>
			<td th:text="${book.author}"></td>
			<td th:text="${#dates.format(book.publicationDate,'yyyy/MM/dd')}"></td>
			<td th:text="${book.stock}"></td>
			<td th:text="${book.genreId}"></td>

		</tr>
	</table>
</body>
</html>

layout
<!DOCTYPE html>
<html xmlns:th="http://www.thymeleaf.org"
    th:fragment="layout(title,body)">
<head>
<meta charset="UTF-8" />
<link th:href="@{/css/style.css}" rel="stylesheet" />
<title th:replace="${title}">Insert title here</title>
</head>
<body>
    <div th:replace="~{header :: layout-header}"></div>
    <div id="container">
		<div id="main" th:replace="${body}"></div>
    </div>
    <div th:replace="~{footer :: layout-footer}"></div>
</body>
</html>


	// 商品情報を新着順で検索するためのquery
	public static final String QUERYORDERBYINSERTDATE = "SELECT i.id, i.name, i.description, i.image, i.price, c.name as cname, AVG(r.value) "
			+
			"FROM items i " +
			"LEFT OUTER JOIN rating r " +
			"ON i.id = r.item_id " +
			"LEFT OUTER JOIN categories c " +
			"ON i.category_id = c.id " +
			"WHERE i.delete_flag = :deleteFrag " +
			"GROUP BY i.id, i.name, i.description, i.image, i.price, i.insert_date, c.name " +
			"ORDER BY i.insert_date DESC nulls last ";

	// 商品情報を新着順で検索
	@Query(value = QUERYORDERBYINSERTDATE, nativeQuery = true)
	public List<Object[]> getOrderByInsertDate(@Param("deleteFrag") Integer deleteFrag);

	default List<ItemRatingBean> findByItemOrderByInsertDate(Integer deleteFrag) {
		List<ItemRatingBean> itemRatingBeansList = new ArrayList<>();
		for (Object[] objects : getOrderByInsertDate(deleteFrag)) {
			ItemRatingBean irb = new ItemRatingBean(objects);
			itemRatingBeansList.add(irb);
		}
		return itemRatingBeansList;
	}



	// 商品情報を新着順で検索するためのquery
	public static final String QUERY_ORDER_BY_INSERTDATE = 
			"SELECT i.id, i.name, i.description, i.image, i.price, c.name as cname, AVG(r.value) " +
			"FROM items i " +
			"LEFT OUTER JOIN rating r " +
			"ON i.id = r.item_id " +
			"LEFT OUTER JOIN categories c " +
			"ON i.category_id = c.id " +
			"WHERE i.delete_flag = :deleteFrag " +
			"GROUP BY i.id, i.name, i.description, i.image, i.price, i.insert_date, c.name " +
			"ORDER BY i.insert_date DESC nulls last ";

	// 商品情報を新着順で検索
	@Query(value = QUERY_ORDER_BY_INSERTDATE, nativeQuery = true)
	public List<Object[]> getOrderByInsertDate(@Param("deleteFrag") Integer deleteFrag);

	default List<ItemRatingBean> findByItemOrderByInsertDate(Integer deleteFrag) {
		return itemRatingBeansList = getOrderByInsertDate(deleteFlag)
			.map(value => new ItemRatingBean(objects));
	}