Spring 3.0 + jdbc 셋팅 && 실습

1.  spring legacy project 생성

프로젝트명: Springwebmybatis

템플릿:Spring MVC project

패키지명: com.exe.springweb

springwebmybatis 프로젝트 전체 구조 

 

2. pom.xml에 해당 Spring-jdbc 태그 추가하기

*해당 태그가 있어야 스프링 jdbc 사용 가능 

<dependency>
    	<groupId>org.springframework</groupId>
    	<artifactId>spring-jdbc</artifactId>
    	<version>${org.springframework-version}</version>
</dependency>

3. servlet-context.xml 에 beans 추가하기

<?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.jdbc.springweb" />
	
	
	<beans:bean id="boardDAO3" class="com.jdbc.dao.BoardDAO3">
		<beans:property name="sessionTemplate" ref="sessionTemplate"/>
	</beans:bean>
	
	
	<!-- 템플릿 만들기 -->
	<beans:bean id="sessionTemplate" class="org.mybatis.spring.SqlSessionTemplate">
		<beans:constructor-arg ref="sessionFactory"/>
	</beans:bean>
	
	
	<!-- 팩토리 만들고 템플릿 만들어야 마이바티스 사용가능 -->
	<beans:bean id="sessionFactory"
	class="org.mybatis.spring.SqlSessionFactoryBean">
		<beans:property name="dataSource" ref="dataSource"/>
		<beans:property name="configLocation" value="classpath:/mybatis-config.xml"/>
	</beans:bean>
	
	
	<!-- DB정보 -->
	<beans:bean id="dataSource" class="org.apache.commons.dbcp.BasicDataSource" destroy-method="close">
		<beans:property name="driverClassName" value="oracle.jdbc.driver.OracleDriver"/> 
		<beans:property name="url" value="jdbc:oracle:thin:@192.168.16.16:1521:TestDB"/>
		<beans:property name="username" value="SUZI"/> 
		<beans:property name="password" value="A123"/>
	</beans:bean>
	
	<beans:bean id="myUtil" class="com.jdbc.util.MyUtil"/>
	
</beans:beans>

4. BoardDAO2.jav 클래스 생성해주기

여기서 포인트는 servlet-context.xml 에서 넘겨준 sessionTemplate 통해서 초기화 한 후 사용한다는 점! 

package com.jdbc.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;

import javax.naming.spi.DirStateFactory.Result;
import javax.sql.DataSource;

import org.mybatis.spring.SqlSessionTemplate;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.w3c.dom.ls.LSInput;

import com.jdbc.dto.BoardDTO;
public class BoardDAO3 {

	private SqlSessionTemplate sessionTemplate;

	public void setSessionTemplate(SqlSessionTemplate sessionTemplate) 
			throws Exception{

		this.sessionTemplate = sessionTemplate;
	}
	public int getMaxNum(){

		int maxNum = 0;
		maxNum =
				sessionTemplate.selectOne("boardmapper.maxNum");
		return maxNum;
	}

	public void insertData(BoardDTO dto){
		
		sessionTemplate.insert("boardmapper.insertData",dto);

	}

	public List<BoardDTO> getList(int start, int end,
			String searchKey, String searchValue){

		HashMap<String, Object> params = 
				new HashMap<String, Object>();
		
		params.put("start", start);
		params.put("end", end);
		params.put("searchKey", searchKey);
		params.put("searchValue",searchValue);
		
		List<BoardDTO> lists = 
				sessionTemplate
				.selectList("boardmapper.getLists",params);
		
		return lists;

	}

	//하나의 데이터 받기
	public int getDataCount(String searchKey,String searchValue){

		HashMap<String, Object> params = 
				new HashMap<String, Object>();
	
		params.put("searchKey", searchKey);
		params.put("searchValue",searchValue);
		
		int result =
				sessionTemplate
				.selectOne("boardmapper.getDataCount",params);
		
		return result;
	
	}
	
	public void updateHitCount(int num){

		sessionTemplate
		.update("boardmapper.updateHitCount",num);

	}
	
	//하나의 데이터 읽어오기 == select
	public BoardDTO getReadData(int num){
		
		BoardDTO dto = 
				sessionTemplate
				.selectOne("boardmapper.getReadData",num);
		return dto;
	}
	public void deleteData(int num){
		
		sessionTemplate
		.update("boardmapper.deleteData",num);
}
	public void updateData(BoardDTO dto){
		
		sessionTemplate
		.update("boardmapper.updateData",dto);

	}
}

 

6. src/main/java 아래 com.jdbc.mapper 패키지 생성 후 >> boardMapper.xml 파일을 생성해 준다. 

더불어 com.jdbc.util의 mappers에서 해당 boardMapper.xml 를 mybatis-config.xml에서 resource로 추가해 주어야 한다.  

 

*boardMapper.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="boardmapper">

<select id="maxNum" resultType="int">
	select nvl(max(num),0) from board
</select>


<insert id="insertData" parameterType="com.jdbc.dto.BoardDTO">
	insert into board(num,name,pwd,email,subject,content,
	ipAddr,hitCount,created) values (#{num},#{name},#{pwd},#{email},#{subject},#{content},
	#{ipAddr},0,sysdate)
</insert>

<select id="getDataCount" parameterType="map" resultType="int">
	select nvl(count(*),0) from board
	where ${searchKey} like '%' || #{searchValue} || '%'
</select>

<select id="getLists" parameterType="map" resultType="com.jdbc.dto.BoardDTO">
	select * from (
	select rownum rnum,data.* from (
	select num,name,subject,hitCount,
	to_char(created,'YYYY-MM-DD') created
	from board 
	where ${searchKey} like '%' || #{searchValue} || '%'
	order by num desc) data) 
<![CDATA[
	where rnum>=#{start} and rnum<=#{end}
]]>
</select>

<update id="updateHitCount" parameterType="int">
	update board set hitCount=hitCount+1 where num=#{num}
</update>

<select id="getReadData" parameterType="int"
	resultType="com.jdbc.dto.BoardDTO">
	select num,name,pwd,email,subject,content,ipAddr,
	hitCount,created from board where num=#{num}
</select>

<update id="updateData" parameterType="com.jdbc.dto.BoardDTO">
	update board set name=#{name},pwd=#{pwd},
	email=#{email},subject=#{subject},content=#{content}
	where num=#{num}
</update>

<delete id="deleteData" parameterType="int">
	delete board where num=#{num}
</delete>




</mapper>

*mybatis-config.xml

프레임에 사용할 경우 아래의 configuration을 작성해 줘야 한다. -->
<configuration>
	<properties>
		<property name="driver" 
		value="oracle.jdbc.driver.OracleDriver"/>
		<property name="url" 
		value="jdbc:oracle:thin:@192.168.16.16:1521:TestDB"/>
		<property name="username" value="SUZI"/>
		<property name="password" value="A123"/>
	</properties>
	
	<environments default="development">
		<environment id="development">
		<transactionManager type="JDBC"/>
		
		<dataSource type="POOLED">
			<property name="driver" value="${driver}"/>
			<property name="url" value="#{url}"/>
			<property name="username" value="${username}"/>
			<property name="password" value="${password}"/>
		</dataSource>
		</environment>
	</environments>
	
	<!-- 현재 실제로 필요한 것! -->
	<mappers>
		<mapper resource="com/jdbc/mapper/boardMapper.xml"/>
	</mappers>
</configuration>

 

7. BoardController.java 생성 

package com.jdbc.springweb;

import java.io.UnsupportedEncodingException;
import java.net.URLDecoder;
import java.net.URLEncoder;
import java.util.List;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;
import org.springframework.web.servlet.ModelAndView;

import com.jdbc.dao.BoardDAO;
import com.jdbc.dao.BoardDAO2;
import com.jdbc.dao.BoardDAO3;
import com.jdbc.dto.BoardDTO;
import com.jdbc.util.MyUtil;

@Controller
public class BoardController {

	@Autowired
	@Qualifier("boardDAO3")
	BoardDAO3 dao;
	
	@Autowired
	MyUtil myUtil;
	
	@RequestMapping(value = "/", method = RequestMethod.GET)
	public String home() {
		
		return "index";
	}
	
	/*
	@RequestMapping(value = "/created.action", method = {RequestMethod.GET, RequestMethod.POST})
	public String created(HttpServletRequest request, HttpServletResponse response) {
		
		return "bbs/created";
	}
	*/

	@RequestMapping	(value = "/created.action")
	public ModelAndView created() {
		
		ModelAndView mav = new ModelAndView();
		mav.setViewName("bbs/created");
		return mav;
	}
	
	
	
	
	@RequestMapping(value = "/created_ok.action", method = {RequestMethod.GET, RequestMethod.POST})
	public String created_ok(BoardDTO dto, 
			HttpServletRequest request, HttpServletResponse response) {
		
		int maxNum = dao.getMaxNum();
		
		dto.setNum(maxNum + 1);
		dto.setIpAddr(request.getRemoteAddr());
		
		dao.insertData(dto);	//dto5개 + 2 + 2
		
		return "redirect:/list.action";
	}
	
	@RequestMapping(value = "/list.action", method = {RequestMethod.GET, RequestMethod.POST})
	public String list(HttpServletRequest request, HttpServletResponse response) 
			throws UnsupportedEncodingException {
		
		String cp = request.getContextPath();
		
		String pageNum = request.getParameter("pageNum");
		int currentPage = 1;
		
		if(pageNum != null)
			currentPage = Integer.parseInt(pageNum);
		
		String searchKey = request.getParameter("searchKey");
		String searchValue = request.getParameter("searchValue");
		
		if(searchKey == null){
			
			searchKey = "subject";
			searchValue = "";
			
		}else{
			
			if(request.getMethod().equalsIgnoreCase("GET"))
				searchValue =
					URLDecoder.decode(searchValue, "UTF-8");
			
		}
		
		//전체데이터갯수
		int dataCount = dao.getDataCount(searchKey, searchValue);
		
		//전체페이지수
		int numPerPage = 10;
		int totalPage = myUtil.getPageCount(numPerPage, dataCount);
		
		if(currentPage > totalPage)
			currentPage = totalPage;
		
		int start = (currentPage-1)*numPerPage+1;
		int end = currentPage*numPerPage;
		
		List<BoardDTO> lists =
			dao.getList(start, end, searchKey, searchValue);
		
		//페이징 처리
		String param = "";
		if(!searchValue.equals("")){
			param = "searchKey=" + searchKey;
			param+= "&searchValue=" 
				+ URLEncoder.encode(searchValue, "UTF-8");
		}
		
		String listUrl = cp + "/list.action";
		if(!param.equals("")){
			listUrl = listUrl + "?" + param;				
		}
		
		String pageIndexList =
			myUtil.pageIndexList(currentPage, totalPage, listUrl);
		
		//글보기 주소 정리
		String articleUrl = 
			cp + "/article.action?pageNum=" + currentPage;
			
		if(!param.equals(""))
			articleUrl = articleUrl + "&" + param;
		
		//포워딩 될 페이지에 데이터를 넘긴다
		request.setAttribute("lists", lists);
		request.setAttribute("pageIndexList",pageIndexList);
		request.setAttribute("dataCount",dataCount);
		request.setAttribute("articleUrl",articleUrl);
		
		return "bbs/list";
	}
	
	@RequestMapping(value = "/article.action", method = {RequestMethod.GET, RequestMethod.POST})
	public ModelAndView article(HttpServletRequest request, HttpServletResponse response) 
			throws UnsupportedEncodingException {
		
		int num = Integer.parseInt(request.getParameter("num"));
		String pageNum = request.getParameter("pageNum");
		
		String searchKey = request.getParameter("searchKey");
		String searchValue = request.getParameter("searchValue");
		
		if(searchKey != null)
			searchValue = URLDecoder.decode(searchValue, "UTF-8");
		
		//조회수 증가
		dao.updateHitCount(num);
		
		BoardDTO dto = dao.getReadData(num);
		
		//modelandview 사용시 아래의 코딩 사용 불가. 주석처리 하고 
		/*
		 * if(dto==null){ return "redirect:/list.action"; }
		 */
		
		int lineSu = dto.getContent().split("\n").length;
		
		dto.setContent(dto.getContent().replaceAll("\n", "<br/>"));
		
		String param = "pageNum=" + pageNum;
		if(searchKey!=null){
			param += "&searchKey=" + searchKey;
			param += "&searchValue=" 
				+ URLEncoder.encode(searchValue, "UTF-8");
		}
		
		
		//ModelAndView
		/*
		request.setAttribute("dto", dto);
		request.setAttribute("params",param);
		request.setAttribute("lineSu",lineSu);
		request.setAttribute("pageNum",pageNum);
		
		return "bbs/article";
		*/
		
		ModelAndView mav = new ModelAndView();
		mav.setViewName("bbs/article");
		mav.addObject("dto",dto);
		mav.addObject("params",param);
		mav.addObject("lineSu",lineSu);
		mav.addObject("pageNum",pageNum);
	
		return mav;
	}
	
	@RequestMapping(value = "/updated.action", method = {RequestMethod.GET, RequestMethod.POST})
	public String updated(HttpServletRequest request, HttpServletResponse response) 
			throws UnsupportedEncodingException {
		
		int num = Integer.parseInt(request.getParameter("num"));
		String pageNum = request.getParameter("pageNum");
		
		BoardDTO dto = dao.getReadData(num);
		
		if(dto == null){
			return "redirect:/list.action";
		}
		
		request.setAttribute("dto", dto);
		request.setAttribute("pageNum", pageNum);
		
		return "bbs/updated";
	}
	
	@RequestMapping(value = "/updated_ok.action", method = {RequestMethod.GET, RequestMethod.POST})
	public String updated_ok(BoardDTO dto, 
			HttpServletRequest request, HttpServletResponse response) 
					throws UnsupportedEncodingException {
		
		String pageNum = request.getParameter("pageNum");
		
		dao.updateData(dto);	//넘어올때 5개 데이터 가져옴
		
		return "redirect:/list.action?pageNum=" + pageNum;
	}
	
	@RequestMapping(value = "/deleted.action", method = {RequestMethod.GET, RequestMethod.POST})
	public String deleted(HttpServletRequest request, HttpServletResponse response) 
					throws UnsupportedEncodingException {
		
		String pageNum = request.getParameter("pageNum");
		int num =Integer.parseInt(request.getParameter("num"));
		
		dao.deleteData(num);
		
		return "redirect:/list.action?pageNum=" + pageNum;
	}
	
}

-끝