8일(08.26)Spring_Mybatis_Movie (목록보기, 페이지설정,검색기능)

drop table movie;
create table movie(
	num number not null  primary key,
	title varchar2(20) not null,
	continent varchar2(20) not null,
	nation varchar2(25) not null,
	genre varchar2(60),
	grade varchar2(5),
	actor varchar2(50)
);

drop sequence mv_seq;

create sequence mv_seq
	increment by 1
	start with 1 
	minvalue 1
	maxvalue 10000
	nocache;

insert into movie(num,title,continent,nation,genre,grade,actor)
values(mv_seq.nextval,'외계인','아시아','한국','액션,판타지','15','류준열,김우빈,김태리');

insert into movie(num,title,continent,nation,genre,grade,actor)
values(mv_seq.nextval,'배트맨','아메리카','미국','코미디,판타지','15','로버트 패틴슨,폴 다노');

commit;

select * from movie;

 

 

*.mv 요청 => movie scan
web-inf/movie/~~.jsp

검색은 장르,등급, 출연배우로 검색할 수 있도록 한다.
페이지설정(1페이지에 2개의 레코드가 나오도록 한다.)

 

 

 

💾 pom.xml

<repositories>
		<repository>
			<id>oracle</id>
			<name>ORACLE JDBC Repository</name>
			<url>http://maven.jahia.org/maven2</url>
		</repository>
</repositories>

	<!-- 유효성관련 -->
    <dependency>
			<groupId>org.hibernate</groupId>
			<artifactId>hibernate-validator</artifactId>
			<version>4.2.0.Final</version>
		</dependency>
		
		<dependency>
			<groupId>javax.xml.bind</groupId>
			<artifactId>jaxb-api</artifactId>
			<version>2.2.8</version>
		</dependency>
		
		<!-- oracle 관련 -->
		<dependency>
			<groupId>com.oracle</groupId>
			<artifactId>ojdbc6</artifactId>
			<version>12.1.0.1</version>
		</dependency>  
		
		<!-- datasource관련 -->
		<dependency>
			<groupId>commons-dbcp</groupId>
			<artifactId>commons-dbcp</artifactId>
			<version>1.4</version>
		</dependency>
		
		<!-- mybatis관련 -->
		<dependency>
			<groupId>org.mybatis</groupId>
			<artifactId>mybatis</artifactId>
			<version>3.1.0</version>
		</dependency>
		<dependency>
			<groupId>org.mybatis</groupId>
			<artifactId>mybatis-spring</artifactId>
			<version>1.1.0</version>
		</dependency>

 

 

web.xml

-한글설정

<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>
		<init-param>
			<param-name>forceEncoding</param-name>
			<param-value>true</param-value>
		</init-param>
	</filter>
	
	<filter-mapping>
		<filter-name>encodingFilter</filter-name>
		<url-pattern>/*</url-pattern>
	</filter-mapping>

-서블릿매핑작업

<!-- 서블릿매핑설정 *mv(여러가지 mv를 요청한다.) -->

	<servlet>
		<servlet-name>movie</servlet-name>
		<servlet-class>org.springframework.web.servlet.DispatcherServlet</servlet-class>
		<init-param>
			<param-name>contextConfigLocation</param-name>
			<param-value>/WEB-INF/spring/appServlet/movie-servlet.xml</param-value>
		</init-param>
		<load-on-startup>1</load-on-startup>
	</servlet>
		
	<servlet-mapping>
		<servlet-name>movie</servlet-name>
		<url-pattern>*.mv</url-pattern>
	</servlet-mapping>

 

web.xml의 설정에 따라 root-contex.xml로 이동한다.

<context-param>
<param-name>contextConfigLocation</param-name>
<param-value>/WEB-INF/spring/root-context.xml</param-value>
</context-param>

 

 

 

 

 

root-context.xml

 

<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
	xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
	xsi:schemaLocation="http://www.springframework.org/schema/beans https://www.springframework.org/schema/beans/spring-beans.xsd">
	
	<!-- Root Context: defines shared resources visible to all other web components -->
		
		<bean id="dataSource" class="org.apache.commons.dbcp.BasicDataSource">
		<property name="driverClassName" value="oracle.jdbc.driver.OracleDriver"/>
		<property name="url" value="jdbc:oracle:thin:@localhost:1521:orcl"/>
		<property name="username" value="jspid"/>
		<property name="password" value="jsppw"/>
	</bean>
	
	<bean id="sqlSessionFactoryBean" 
			class="org.mybatis.spring.SqlSessionFactoryBean">
		<property name="dataSource" ref="dataSource"/>
		<property name="configLocation" 
						value="classpath:/movie/mybatis/SqlMapConfig.xml"/>
		<property name="mapperLocations">
			<value>classpath:/movie/mybatis/movie.xml</value>
		</property>
	</bean>
	
	<bean id="sqlSessionTemplate" class="org.mybatis.spring.SqlSessionTemplate">
		<constructor-arg ref="sqlSessionFactoryBean"/>
	</bean> 	
		
</beans>

<property name="configLocation" 
value="classpath:/movie/mybatis/SqlMapConfig.xml"/>

 

<property name="mapperLocations">
<value>classpath:/movie/mybatis/movie.xml</value>
</property>

 

위의 경로를 따라 movie.xml, SqlMapConfig.xml로 이동한다.

 

 

💾 movie.xml

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper
  PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
  "http://mybatis.org/dtd/mybatis-3-mapper.dtd">

<mapper namespace="movie.MovieBean">

</mapper>
<!-- movie.xml -->

mapper설정한다.

 

 

 

 

💾 SqlMapConfig.xml

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE configuration
  PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
  "http://mybatis.org/dtd/mybatis-3-config.dtd">

<configuration>
	<typeAliases>
		<typeAlias type="movie.model.MovieBean" alias="MyMovie"/>
	</typeAliases>
</configuration>

<!-- SqlMapConfig.xml 별칭 설정 할것이없으면 생성하지 않아도 된다.-->

 

movie.xml에 <mapper namespace="movie.MovieBean"> 설정했으므로 

MovieBean을 만든다.

 

 

 

💾 MovieBean.java

package movie.model;

public class MovieBean {

	private int num;
	private String title;
	private String continent;
	private String nation;
	private String genre;
	private String grade;
	private String actor;
	
	
	public int getNum() {
		return num;
	}
	public void setNum(int num) {
		this.num = num;
	}
	public String getTitle() {
		return title;
	}
	public void setTitle(String title) {
		this.title = title;
	}
	public String getContinent() {
		return continent;
	}
	public void setContinent(String continent) {
		this.continent = continent;
	}
	public String getNation() {
		return nation;
	}
	public void setNation(String nation) {
		this.nation = nation;
	}
	public String getGenre() {
		return genre;
	}
	public void setGenre(String genre) {
		this.genre = genre;
	}
	public String getGrade() {
		return grade;
	}
	public void setGrade(String grade) {
		this.grade = grade;
	}
	public String getActor() {
		return actor;
	}
	public void setActor(String actor) {
		this.actor = actor;
	}

}

 

 

 

💾 MovieDao.java

package movie.model;

import java.util.ArrayList;
import java.util.List;
import java.util.Map;

import org.apache.ibatis.session.RowBounds;
import org.mybatis.spring.SqlSessionTemplate;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Component;

import utility.Paging;

@Component("MyMovie")
public class MovieDao {

	
	private final String namespace="movie.MovieBean";
	
	@Autowired
	SqlSessionTemplate sqlSessionTemplate; //root-context.xml에서 만든 객체를 주입

	
}

 

 

💾 MovieListController.java

package movie.controller;

import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import javax.servlet.http.HttpServletRequest;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.ui.Model;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.servlet.ModelAndView;

import movie.model.MovieBean;
import movie.model.MovieDao;
import utility.Paging;

@Controller
public class MovieListController {

	private final String command ="/list.mv";
	private String getPage="/movieList";
	
	@Autowired
	private MovieDao movieDao;
	
	
	@RequestMapping(command)
	public ModelAndView list(HttpServletRequest request,
					@RequestParam(value="whatColumn", required=false) String whatColumn,
					@RequestParam(value="keyword", required=false) String keyword,
					@RequestParam(value="pageNumber", required=false) String pageNumber
				){
		System.out.println("whatColumn:"+whatColumn);
		System.out.println("keyword:"+keyword);
		System.out.println("pageNumber:"+pageNumber);
		
		//검색기능
		Map<String,String> map = new HashMap<String,String>();
		map.put("whatColumn", whatColumn);
		map.put("keyword", "%"+keyword+"%");
		
		//하단페이징처리
		int totalCount = movieDao.getMovieCount(map);
		System.out.println("totalCount"+totalCount);
		
		String url = request.getContextPath()+command;
		System.out.println("url:"+url);
		
		Paging pageInfo = new Paging(pageNumber,null,totalCount,url,whatColumn,keyword,null);
		
		ModelAndView mav = new ModelAndView();
		
		List<MovieBean> lists = new ArrayList<MovieBean>();
		lists = movieDao.getMovieList(pageInfo,map);
		mav.addObject("lists", lists);
		mav.addObject("pageInfo", pageInfo);//속성설정을 하여야 하단에 페이지번호가 보인다. pageInfo안에 있는 pagingHtml를 출력하게 한다.
		mav.addObject("totalCount", totalCount);
		
		mav.setViewName(getPage);//select한 list챙겨서 페이지 이동한다.
		return mav;
	}
	
}

 

 

💾 MovieDao.jsva

package movie.model;

import java.util.ArrayList;
import java.util.List;
import java.util.Map;

import org.apache.ibatis.session.RowBounds;
import org.mybatis.spring.SqlSessionTemplate;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Component;

import utility.Paging;

@Component("MyMovie")
public class MovieDao {

	
	private final String namespace="movie.MovieBean";
	
	@Autowired
	SqlSessionTemplate sqlSessionTemplate; //root-context.xml에서 만든 객체를 주입
	
	
	
	//Paging pageInfo -현재 페이지
	public List<MovieBean> getMovieList(Paging pageInfo,Map<String,String> map){
		
		List<MovieBean> lists = new ArrayList<MovieBean>();
	
		//내가 선택한 페이지의 정보가 Paging pageInfo에 들어가있어서, 건너뛸 레코드개수getOffset()와, 가지고올개수getLimit()
		//전체를 가지고 오는것이 아니라 내가 필요한 페이지만 가져오는것
		RowBounds rowBounds = new RowBounds(pageInfo.getOffset(),pageInfo.getLimit());
		
		lists = sqlSessionTemplate.selectList(namespace+".GetMovieList",map,rowBounds);
		System.out.println("lists:"+lists.size());
		return lists;
	}
	
	
	public int getMovieCount(Map<String,String> map) {
		
		int totalCount = sqlSessionTemplate.selectOne(namespace+".GetMovieCount",map);
		return totalCount;
	}
	
}

 

 

💾 Movie.xml

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper
  PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
  "http://mybatis.org/dtd/mybatis-3-mapper.dtd">

<mapper namespace="movie.MovieBean">

	<select id="GetMovieList" resultType="MyMovie">
	 select * from movie
	 <if test="whatColumn == 'genre'">
	 	where genre like #{keyword}
	 </if>
	 
	 <if test="whatColumn == 'grade'">
	 	where grade like #{keyword}
	 </if>
	 <if test="whatColumn == 'actor'">
	 	where actor like #{keyword}
	 </if>
	 order by num desc	
	</select>
	
	<select id="GetMovieCount" resultType="int">
	select count(*) from movie
	<if test="whatColumn == 'genre'">
		where genre like #{keyword}
	</if>
	<if test="whatColumn == 'grade'">
		where grade like #{keyword}
	</if>
	<if test="whatColumn == 'actor'">
		where actor like #{keyword}
	</if>
	</select>
	

</mapper>
<!-- movie.xml -->

 

 

💾  movie_start.jsp

<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>

    movie_start.jsp<br>
    
    <%
   	response.sendRedirect("list.mv");
    %>

 

 

💾  movieList.jsp

<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<%@include file="../common/common.jsp" %>
    movieList.jsp<br>
    
<center>
<h1>영화 정보 리스트 화면</h1>
	<form action="list.mv" method="get">
		<select name="whatColumn">
			<option value="">전체검색</option>
			<option value="genre">장르</option>
			<option value="grade">등급</option>
			<option value="actor">출연배우</option>
		</select>
		<input type="text" name="keyword" value="액션">
		<input type="submit" value="검색">
	</form>
<table border="1" align="center">
	<tr>
		<td colspan="9"  align="right">
		<input type="button" value="추가하기" onclick="insert()">
		</td>
	</tr>
	<tr>
		<td>번호</td>
		<td>제목</td>
		<td>대륙</td>
		<td>제작국가</td>
		<td>장르</td>
		<td>등급</td>
		<td>출연배우</td>
		<td>삭제</td>
		<td>수정</td>	
	</tr>
	
	<c:forEach var="mv" items="${lists }">
	<tr>
		<td>${mv.num }</td>
		<td>${mv.title}</td>
		<td>${mv.continent }</td>
		<td>${mv.nation }</td>
		<td>${mv.genre }</td>
		<td>${mv.grade }</td>
		<td>${mv.actor }</td>
		<td>삭제</td>
		<td>수정</td>
	</tr>
	</c:forEach>
</table>
</center>
<br><br>
<center>
	${pageInfo.pagingHtml }
</center>