개발자/Spring

[SPRING] 도서 CRUD 구현 + 검색기능까지

푸루닉 2022. 12. 30. 09:40

도서 CRUD를 간략하게 스프링을 통해 구현해보았다.

1. 전체적인 구성

2. CRUD 구현 전 환경설정

pom.xml(의존성 주입만)

java version 1.8

springframework-version : 4.3.30.RELEASE

 

	<!-- https://mvnrepository.com/artifact/xerces/xercesImpl -->
		<dependency>
		    <groupId>xerces</groupId>
		    <artifactId>xercesImpl</artifactId>
		    <version>2.12.2</version>
		</dependency>
		
		<!-- https://mvnrepository.com/artifact/com.oracle.database.jdbc/ojdbc8 -->
		<!-- ojdbc8.jar -->
		<dependency>
		    <groupId>com.oracle.database.jdbc</groupId>
		    <artifactId>ojdbc8</artifactId>
		    <version>21.8.0.0</version>
		</dependency>
		
		<!-- https://mvnrepository.com/artifact/org.springframework/spring-jdbc -->
		<!-- spring-jdbc -->
		<dependency>
		    <groupId>org.springframework</groupId>
		    <artifactId>spring-jdbc</artifactId>
		    <version>${org.springframework-version}</version>
		</dependency>
		
		<!-- spring-orm -->
		<dependency>
		    <groupId>org.springframework</groupId>
		    <artifactId>spring-orm</artifactId>
		    <version>${org.springframework-version}</version>
		</dependency>
		
		<!-- https://mvnrepository.com/artifact/org.mybatis/mybatis-spring -->
		<dependency>
		    <groupId>org.mybatis</groupId>
		    <artifactId>mybatis-spring</artifactId>
		    <version>2.1.0</version>
		</dependency>
		
		<!-- https://mvnrepository.com/artifact/org.mybatis/mybatis -->
		<dependency>
		    <groupId>org.mybatis</groupId>
		    <artifactId>mybatis</artifactId>
		    <version>3.5.11</version>
		</dependency>
		
		
		<!-- https://mvnrepository.com/artifact/com.zaxxer/HikariCP -->
		<!-- tomcat-dbcp.jar -> c3p0 -> hikariCP -->
		<dependency>
		    <groupId>com.zaxxer</groupId>
		    <artifactId>HikariCP</artifactId>
		    <version>4.0.3</version>
		</dependency>

 

web.xml

	<?xml version="1.0" encoding="UTF-8"?>
<web-app version="3.1" xmlns="http://java.sun.com/xml/ns/javaee"
	xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
	xsi:schemaLocation="http://java.sun.com/xml/ns/javaee https://java.sun.com/xml/ns/javaee/web-app_3_1.xsd">
    <!-- 인코딩 필터 -->
	<filter>
		<filter-name>EncodingFilter</filter-name>
		<filter-class>org.springframework.web.filter.CharacterEncodingFilter</filter-class>
		<init-param>
			<param-name>encoding</param-name>
			<param-value>UTF-8</param-value>
		</init-param>
	</filter>
	
	<filter-mapping>
		<filter-name>EncodingFilter</filter-name>
		<url-pattern>/*</url-pattern>
	</filter-mapping>

인코딩 필터 부분만 더 추가

 

servlet-context.xml

<?xml version="1.0" encoding="UTF-8"?>
<beans:beans xmlns="http://www.springframework.org/schema/mvc"
	xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
	xmlns:beans="http://www.springframework.org/schema/beans"
	xmlns:context="http://www.springframework.org/schema/context"
	xsi:schemaLocation="http://www.springframework.org/schema/mvc https://www.springframework.org/schema/mvc/spring-mvc.xsd
		http://www.springframework.org/schema/beans https://www.springframework.org/schema/beans/spring-beans.xsd
		http://www.springframework.org/schema/context https://www.springframework.org/schema/context/spring-context.xsd">

	<!-- DispatcherServlet Context: defines this servlet's request-processing infrastructure -->
	
	<!-- Enables the Spring MVC @Controller programming model -->
	<annotation-driven />

	<!-- Handles HTTP GET requests for /resources/** by efficiently serving up static resources in the ${webappRoot}/resources directory -->
	<resources mapping="/resources/**" location="/resources/" />

	<!-- Resolves views selected for rendering by @Controllers to .jsp resources in the /WEB-INF/views directory -->
	<beans:bean class="org.springframework.web.servlet.view.InternalResourceViewResolver">
		<beans:property name="prefix" value="/WEB-INF/views/" />
		<beans:property name="suffix" value=".jsp" />
	</beans:bean>
	
	<context:component-scan base-package="com.itbank.controller" />
	
	
	
</beans:beans>

root-context(mybatis를 이용한 DB 연결)

<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
	xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
	xmlns:mybatis-spring="http://mybatis.org/schema/mybatis-spring"
	xmlns:context="http://www.springframework.org/schema/context"
	xsi:schemaLocation="http://mybatis.org/schema/mybatis-spring http://mybatis.org/schema/mybatis-spring-1.2.xsd
		http://www.springframework.org/schema/beans https://www.springframework.org/schema/beans/spring-beans.xsd
		http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context-4.3.xsd">
	
	<!-- Root Context: defines shared resources visible to all other web components -->
		
	<bean id="hikariConfig" class="com.zaxxer.hikari.HikariConfig">
		<property name="driverClassName" value="oracle.jdbc.driver.OracleDriver" />
		<property name="jdbcUrl" value="jdbc:oracle:thin:@192.168.1.100:1521:xe"/>
		<property name="username" value="c##itbank"/>
		<property name="password" value="it"/>
	</bean>
	
	<bean id="dataSource" class="com.zaxxer.hikari.HikariDataSource">
		<constructor-arg ref="hikariConfig"/>	
	</bean>
	
	<bean id ="sqlSessionFactory" class="org.mybatis.spring.SqlSessionFactoryBean">
		<property name="dataSource" ref="dataSource"/>
<!-- 		<property name="configLocation" value="clsspath:config/mybatis.xml"/> -->
<!-- 		<property name="mapperLocations"> -->
<!-- 			<list> -->
<!-- 				<value>classpath:config/test.xml</value> -->
<!-- 			</list> -->
<!-- 		</property> -->
	</bean>
	
	<bean id="sqlSessionTemplate" class="org.mybatis.spring.SqlSessionTemplate">
		<constructor-arg ref="sqlSessionFactory" />
	</bean>
	
	<context:component-scan base-package="com.itbank.service" />
	<context:component-scan base-package="com.itbank.repository" />
	<context:component-scan base-package="com.itbank.component" />
	<mybatis-spring:scan base-package="com.itbank.repository"/>
	
</beans>

사용한 Namespace

최상위 폴더와 header Home 구성

package com.itbank.controller;

import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.servlet.ModelAndView;

@Controller
public class HomeController {
	
	@RequestMapping("/")
	public ModelAndView home() {
		ModelAndView mav = new ModelAndView("home");
		return mav;
	}
}

header and home.jsp

<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core" %>
<c:set var="cpath" value="${pageContext.request.contextPath }"/>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>Insert title here</title>
<style>
body {
  padding:1.5em;
  background: #f5f5f5
}

table {
  border: 1px #a39485 solid;
  font-size: .9em;
  box-shadow: 0 2px 5px rgba(0,0,0,.25);
  width: 100%;
  border-collapse: collapse;
  border-radius: 5px;
  overflow: hidden;
}

th {
  text-align: left;
}
  
thead {
  font-weight: bold;
  color: #fff;
  background: #73685d;
}
  
 td, th {
  padding: 1em .5em;
  vertical-align: middle;
}
  
 td {
  border-bottom: 1px solid rgba(0,0,0,.1);
  background: #fff;
}

a {
  color: #73685d;
}
tr:hover td{
	background-color: #73685d;
	cursor: pointer;
	color: white;
	font-weight: bold;
}
  
 @media all and (max-width: 768px) {
    
  table, thead, tbody, th, td, tr {
    display: block;
  }
  
  th {
    text-align: right;
  }
  
  table {
    position: relative; 
    padding-bottom: 0;
    border: none;
    box-shadow: 0 0 10px rgba(0,0,0,.2);
  }
  
  thead {
    float: left;
    white-space: nowrap;
  }
  
  tbody {
    overflow-x: auto;
    overflow-y: hidden;
    position: relative;
    white-space: nowrap;
  }
  
  tr {
    display: inline-block;
    vertical-align: top;
  }
  tr:hover {
  	background-color: #73685d;
  }
  
  th {
    border-bottom: 1px solid #a39485;
  }
  
  td {
    border-bottom: 1px solid #e5e5e5;
  }
  
  
  }
</style>
</head>
<body>

<h1><a href="${cpath }/">day03 - book</a></h1>
<nav>
	<ul>
		<li><a href="${cpath }/list">목록(검색 포함, 제목으로 검색, 단일 보기(detail))</a>
		<li><a href="${cpath }/insert">추가</a>
	</ul>
</nav>

<%--
		기능			주소			메서드			함수						응답
		============================================================================================
		목록			/list		GET				list()					"list"
					목록에서는 이름과 가격 출판사만 표기합니다.
		검색			/list		POST			serach(String search)	"list"
		
		추가			/insert		GET				add()					"add"
					/insert		POST			add(BookDTO dto)		"redirect:/list"
		단일보기
		수정
		삭제

 --%>
===================================================================================================================
home.jsp

<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<%@ include file="header.jsp" %>

<pre>
create table book3 (
	idx				number				default book3_seq.nextval primary key,
	name			varchar2(100)		not null,
    name2			varchar2(1000		not null,
	author			varchar2(100)		not null,
	publisher		varchar2(1000)		not null,
	publishDate		date				not null,
	price			number				check(price between 0 and 50000),
	memo			varchar2(2000)		not null,
	score			number(4, 1)		check (score between 0 and 10)	
);

</pre>




</body>
</html>

home에는 데이터 테이블을 작성해주었다.

테이블의 name2컬럼은 데이터 저장용(띄어쓰기가 모두 생략된)을 따로 생성했다.(검색을 용이하게 하기 위함)

 

BookController(요청에 따라 실행할 함수를 작성)

package com.itbank.controller;

import java.util.List;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.servlet.ModelAndView;
import org.springframework.web.servlet.mvc.support.RedirectAttributes;

import com.itbank.model.BookDTO;
import com.itbank.service.BookService;

@Controller
public class BookController {
	
	@Autowired private BookService bookService;
	
	@GetMapping("/list")
	public ModelAndView list() {
		ModelAndView mav = new ModelAndView();
		
		List<BookDTO> list = bookService.getList();
		mav.addObject("list", list);
		return mav;
	}
	
	@PostMapping("/list")
	public ModelAndView searchList(String search, String keyword) {
		ModelAndView mav = new ModelAndView();
		
		List<BookDTO> list = bookService.getSearchList(search, keyword);
		mav.addObject("list", list);
		return mav;
	}
	
//	@GetMapping("/detail")
//	public ModelAndView detail(int idx) {
//		ModelAndView mav = new ModelAndView();
//		
//		BookDTO dto = bookService.getSelectOne(idx);
//		mav.addObject("dto", dto);
//		return mav;
//	}
	
	@GetMapping("/insert")
	public void insert() {}
	
	@PostMapping("/insert")
	public String insert(BookDTO dto, RedirectAttributes rs) {
		int row = bookService.insert(dto);
		System.out.println(row != 0 ? "추가성공" : "추가실패");
		rs.addFlashAttribute("mes","insertSuccess");
		return "redirect:/list";
	}
	
	@GetMapping("/update")
	public ModelAndView update(int idx) {
		ModelAndView mav = new ModelAndView();
		
		BookDTO dto = bookService.getSelectOne(idx);
		mav.addObject("dto", dto);
		return mav;
	}
	
	@PostMapping("/update")
	public String update(BookDTO dto, RedirectAttributes rs) {
		int row = bookService.update(dto);	
		System.out.println(row != 0 ? "수정 성공" : "수정 실패");
		rs.addFlashAttribute("messages","updateSuccess");
		return "redirect:/list";
	}
	
	@GetMapping("/delete")
	public String delete(@RequestParam("idx") int idx, RedirectAttributes rs) {
		int row = bookService.delete(idx);
		System.out.println(row != 0 ? "삭제 성공" : "삭제 실패");
		rs.addFlashAttribute("message","delSuccess");
		return "redirect:/list";
	}
    
    @GetMapping("/detail/{idx}")
	public ModelAndView view(@PathVariable("idx") int idx) {
		// PathVariable : 요청 주소의 일부를 파라미터처럼 받아서 활용한다
		
		ModelAndView mav = new ModelAndView("detail");
//		mav.setViewName("detail");
		BookDTO dto = bookService.getSelectOne(idx);
		mav.addObject("dto", dto);
		return mav;
	}

}
  • ?key=value 이런 식으로 할 필요 없이, @PathVariable을 통해 url 자체에 파라미터를 넘길 수 있다.

BookService(DAO와 연결 + 데이터 처리 공간)

package com.itbank.service;

import java.util.List;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import com.itbank.model.BookDTO;
import com.itbank.repository.BookDAO;

@Service
public class BookService {
	
	@Autowired private BookDAO dao;

	public List<BookDTO> getList() {
		return dao.selectList();
	}

	public BookDTO getSelectOne(int idx) {
		return dao.selectOne(idx);
	}

	// 저장할때 name2는 데이터 저장용으로써 띄어쓰기를 모두 제거하고 저장한다.
	public int insert(BookDTO dto) {
		String name2 = dto.getName().replace(" ", "");
		dto.setName2(name2);
		return dao.insert(dto);
	}

	public int update(BookDTO dto) {
		String name2 = dto.getName().replace(" ", "");
		dto.setName2(name2);
		return dao.update(dto);
	}

	public int delete(int idx) {
		return dao.delete(idx);
	}

	// 검색 시 사용자에게 전달받은 keyword를 띄어쓰기를 모두 제거 한 후 name2와 비교하게 설정했다.
	public List<BookDTO> getSearchList(String search, String keyword) {
		keyword = keyword.replace(" ", "");
		return dao.searchList(search, keyword);
	}

}

 

BookDAO(DB 쿼리 어노테이션 방식으로)

package com.itbank.repository;

import java.util.List;

import org.apache.ibatis.annotations.Delete;
import org.apache.ibatis.annotations.Insert;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import org.apache.ibatis.annotations.Update;

import com.itbank.model.BookDTO;

public interface BookDAO {

	@Select("select * from book3")
	List<BookDTO> selectList();

	@Select("select * from book3 where idx=#{idx}")
	BookDTO selectOne(int idx);

	@Insert("insert into book3(name, name2, author, publisher, publishDate, price, memo, score) "
			+ "values(#{name}, #{name2}, #{author}, #{publisher}, #{publishDate}, #{price}, #{memo}, #{score})")
	int insert(BookDTO dto);

	@Update("update book3 set name=#{name}, name2=#{name2}, author=#{author}, publisher=#{publisher}, "
			+ "publishDate=#{publishDate}, price=#{price}, memo=#{memo}, score=#{score} where idx=#{idx}")
	int update(BookDTO dto);

	@Delete("delete from book3 where idx=#{idx}")
	int delete(int idx);
	
    // mybatis가 두개이상의 파라미터를 인식하지 못하기에 param어노테이션을 붙여주거나
    // arg0 arg1 형태로 형식을 지정해서 보내주거나
    // map형식(hashMap)처리를 해줘서 보내줘야한다.
	@Select("select * from book3 where ${search} like '%${keyword}%' ")
	List<BookDTO> searchList(@Param("search") String search, @Param("keyword") String keyword);

}

 

BookDTO(자바빈즈)

package com.itbank.model;

import java.sql.Date;

public class BookDTO {
	private int idx;
	private String name;
	private String name2;
	private String author;
	private String publisher;
	private Date publishDate;
	private int price;
	private String memo;
	private double score;
	// mybatis가 두개 이상의 파라미터를 인식하기 힘들어하기에 자바빈즈 형식으로 따로 저장 후 불러오게 설정
	private String search;
	private String keyword;
	
	//getter setter 생략~~
	
	

}

jsp 파일들

<list.jsp>
<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<%@ include file="header.jsp" %>

<form method="POST">
<%--검색은 name2(데이터 저장용 이름)과 비교해야 하므로 value를 name2로 설정 --%>
	<select name="search">
		<option value="name2">책 이름</option>
		<option value="author">저자</option>
		<option value="publihser">출판사</option>
	</select>
	<input name="keyword" value="">
	<input type="submit" value="검색">

</form>

<h4>클릭 시 상세보기 가능</h4>

<table class="search">
	<thead>
		<tr>
			<th>책 이름</th>
			<th>저자</th>
			<th>출판사</th>
			<th>가격</th>
		</tr>
	</thead>
	<tbody>
		<c:forEach var="dto" items="${list }" >
			<tr onclick="location.href='${cpath}/detail/${dto.idx }'" >
				<td>${dto.name }</td>
				<td>${dto.author }</td>
				<td>${dto.publisher }</td>
				<td>${dto.price }</td>
			</tr>
		</c:forEach>
	</tbody>
</table>

<script type="text/javascript">
    const msg = "${message}";
    if(msg === "delSuccess"){
        alert("삭제 완료");
    }
</script>

<script type="text/javascript">
    const msgs = "${messages}";
    if(msgs === "updateSuccess"){
        alert("수정 완료");
    }
</script>

<script type="text/javascript">
    const msgss = "${mes}";
    if(msgss === "insertSuccess"){
        alert("추가 완료");
    }
</script>

</body>
</html>

==============================================================================
<insert.jsp>
<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<%@ include file="header.jsp" %>

<form method="POST">
	<p><input type="text" name="name" placeholder="성함"></p>
	<p><input type="text" name="author" placeholder="저자"></p>
	<p><input type="text" name="publisher" placeholder="출판사"></p>
	<p><input type="Date" name="publishDate" ></p>
	<p><input type="number" name="price" placeholder="가격" min="0" max="50000"></p>
	<p><input type="text" name="memo" placeholder="코멘트"></p>
	<p><input type="number" step="0.1" name="score" placeholder="평점"></p>
	<p><input type="submit"></p>
</form>

</body>
</html>

==============================================================================
<detail.jsp>(상세보기)
<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<%@ include file="header.jsp" %>

<c:set var="dto" value="${dto }"/>

<fieldset>
	<legend>책 정보</legend>
	<p>고유번호 : ${dto.idx }</p>
	<p>책 이름 : ${dto.name }</p>
	<p>저자 :${dto.author }</p>
	<p>출판사 : ${dto.publisher }</p>
	<p>출판날짜 : ${dto.publishDate }</p>
	<p>가격 : ${dto.price }</p>
	<p>코멘트 : ${dto.memo }</p>
	<p>평점 : ${dto.score }</p>
</fieldset>

<p><a href="${cpath }/update?idx=${dto.idx}">[수정]</a>
<p><a href="${cpath }/delete?idx=${dto.idx}">[삭제]</a>


</body>
</html>

==============================================================================
<update.jsp>수정

<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<%@ include file="header.jsp" %>

<c:set var="dto" value="${dto }" />
<form method="POST">
	<p><input type="text" name="name" value="${dto.name }"></p>
	<p><input type="text" name="author" value="${dto.author }"></p>
	<p><input type="text" name="publisher" value="${dto.publisher }"></p>
	<p><input type="Date" name="publishDate" value="${dto.publishDate }"></p>
	<p><input type="number" name="price" min="0" max="50000"
				value="${dto.price }"></p>
	<p><input type="text" name="memo" value="${dto.memo }"></p>
	<p><input type="number" step="0.1" name="score" value="${dto.score }"></p>
	<p><input type="submit"></p>
</form>

</body>
</html>


==============================================================================

구현화면

인덱스 페이지(home.jsp)
list.jsp
add.jsp
추가완료 스크립트
detail.jsp
update.jsp(수정 시 데이터가 넘어오는 모습 확인 가능)
수정완료 역시 스크립트 처리
삭제 역시 스크립트 처리
어떻게 띄워서 검색하든 검색이 가능한 모습이 확인 가능하다.
저자 + 출판사 별로 검색 가능하게 구현 되어 있다.

까다로웠던 점

  • mybatis가 생각보다 멍청해서 두개 이상의 파라미터를 인식하지 못해 구현하는 것에 애를 좀 먹었다.
    • 찾아보니 3가지 방법이 있었는데 가장 좋은 방법은 해시맵 처리를 하는 것이 좋을 것 같다
    • 아직 해쉬맵을 많이 써보지 않아 해쉬맵에 대해 많은 연습을 해봐야 할듯 하다.
  • 검색기능을 구현할때 데이터에서 띄어쓰기 (ex 책 제목이 "트렌드 코리아" 일경우 검색으로 "트렌드코리아"라고 검색하면 검색이 안되는 현상)를 문자열로 인식하기에 문제가 있었다.
    • 고민한 결과 이름을 두개의 방법으로 저장한다면 (검색용 데이터, 사용자에게 보여줄 데이터) 문제가 없을 것이라 판단하여 서비스페이지에서 구현했다.
    • replace로 모든 스페이스바를 삭제 시키는 형식으로 진행했는데, 현업에서는 어떤식으로 데이터를 관리할지 궁금해졌다.

깃허브

https://github.com/pulunick/academy/commit/c29821bc85acd981b44f4e4cfbc0d27f2ea0942a

 

국비학원수업_책 관리 CRUD 구현 · pulunick/academy@c29821b

Show file tree Showing 27 changed files with 1,254 additions and 0 deletions.

github.com

 

저작자표시