국비지원 89일차 - Spring memo db연결 및 CRUD

Spring memo db연결 및 CRUD

delete기능만 대표로 올려두었지만 CRUD를 동일하게 구현할 수 있다.

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 -->
	<!--[1] DataSource 빈을 등록
			(1) DriverManagerDataSource 빈을 등록
			(2) server.xml에 설정되어 있는 DBCP를 JNDI로 찾아쓰도록 등록하는 방법
	  -->
	  <!-- (1) DriverManagerDataSource -->
	  <bean id="dataSource" class="org.springframework.jdbc.datasource.DriverManagerDataSource">
	  	  <property name="driverClassName" value="oracle.jdbc.driver.OracleDriver"/>
	  	  <property name="url" value="jdbc:oracle:thin:@localhost:1521:XE"/>
	  	  <property name="username" value="scott"/>
	  	  <property name="password" value="tiger"/>
	  </bean>
	  
	<!-- [2] SqlSessionFactoryBean 등록 -->
	 <bean id="sqlSessionFactory" class="org.mybatis.spring.SqlSessionFactoryBean">
	 	  <property name="dataSource" ref="dataSource"/>
	 	  <property name="configLocation" value="classpath:spring/config/mybatis-config.xml"/>
	 </bean>
	 
	 <!-- [3] SqlSessionTemplate빈 등록 -->
	 <bean id="sqlSessionTemplate" class="org.mybatis.spring.SqlSessionTemplate">
	    <constructor-arg index="0" ref="sqlSessionFactory"/>
	 </bean>	
	
		
</beans> 

MemoMapper.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="com.tis.memo.mapper.MemoMapper">
	<insert id="insertMemo" parameterType="Memo">
		<!-- insert into memo values(memo_seq.nextval,#{name},#{msg:VARCHAR},sysdate) -->
		<selectKey keyProperty="idx" order="BEFORE" resultType="int">
			select memo_seq.nextval from dual
		</selectKey>
		<!-- 시퀀스로 증가한 값을 MemoVO의 idx프로퍼티에 담아주겠다는 의미  -->
		    insert into memo values(#{idx},#{name},#{msg:VARCHAR},sysdate)
		
	</insert>
	
	<select id="listMemo" resultType="Memo">
		select * from memo order by idx desc
	</select>
	
	<delete id="deleteMemo" parameterType="_int">
		delete from memo where idx=#{idx}
	</delete>
</mapper>

memo-context.xml
common.properties기능을 수행한다.
/memo/delete 주소창 입력시 MemoDeleteController를 생성하고 memoDao를 setter이용해 생성한다.

<?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 http://www.springframework.org/schema/beans/spring-beans-4.3.xsd">
	
	<!-- Controller -->
	<bean id="index" class="com.tis.memo.IndexController" name="/main" />
	<bean id="memoForm" class="com.tis.memo.controller.MemoFormController" name="/memo"/>
	<!-- MemoDAO빈 을 setter로 주입하세요 -->
	<bean id="memoAdd" class="com.tis.memo.controller.MemoInsertController" name="/memo/insert">
		<property name="memoDao" ref="memoDao"/>
	</bean>
	<bean id="memoList" class="com.tis.memo.controller.MemoListController" name="/memo/list">
		<property name="memoDao" ref="memoDao"/>
	</bean>
	<bean id="memoDel" class="com.tis.memo.controller.MemoDeleteController" name="/memo/delete">
		<property name="memoDao" ref="memoDao"/>
	</bean>
	
	
	<!-- DAO -->
	<!-- DAO빈 등록하기  id를 맘대로로 주세요. SqlSessionTempate빈을 setter로 주입하세요 -->
	<bean id="memoDao" class="com.tis.memo.persistence.MemoDAOMyBatis">
		<property name="session" ref="sqlSessionTemplate"/>
	</bean> 
</beans>

MemoDAOMyBatis.java

package com.tis.memo.persistence;

import java.util.List;

import org.mybatis.spring.SqlSessionTemplate;

import com.tis.memo.domain.MemoVO;

public class MemoDAOMyBatis implements MemoDAO {
	
	private final String NS="com.tis.memo.mapper.MemoMapper";
	
	//property선언
	private SqlSessionTemplate session;	

	public void setSession(SqlSessionTemplate session) {
		this.session = session;
		System.out.println("sqlSessionTemplate주입됨...");
	}

	@Override
	public int insertMemo(MemoVO memo) {
		System.out.println("DAO 방금 등록한 글 번호: >>"+memo.getIdx());
		return session.insert(NS+".insertMemo", memo);
	}

	@Override
	public List<MemoVO> listMemo() {
		return session.selectList(NS+".listMemo");
	}

	@Override
	public int deleteMemo(int idx) {
		return session.delete(NS+".deleteMemo", idx);
	}

}

MemoDeleteController.java

package com.tis.memo.controller;

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

import org.springframework.web.servlet.ModelAndView;
import org.springframework.web.servlet.mvc.Controller;

import com.tis.memo.persistence.MemoDAO;

public class MemoDeleteController implements Controller {

	private MemoDAO memoDao;
	
	public void setMemoDao(MemoDAO memoDao) {
		this.memoDao = memoDao;
		System.out.println("memoDao주입됨..."+memoDao);
	}

	@Override
	public ModelAndView handleRequest(HttpServletRequest request, HttpServletResponse response) throws Exception {
		String idxStr=request.getParameter("idx");
		if(idxStr==null||idxStr.trim().isEmpty()) {
			return new ModelAndView("redirect:list");
		}
		int idx=Integer.parseInt(idxStr.trim());
		int n=this.memoDao.deleteMemo(idx);
		String str=(n>0)?"삭제 성공":"삭제 실패";
		
		ModelAndView mv=new ModelAndView("msg");
		mv.addObject("message",str);
		mv.addObject("loc", "list");
		
		return mv;
	}

}