Spring DB 연동 회원가입 JdbcTemplate

비어있는 설정파일

springExp.war

 

완성본

springExp.war

파일 위치

selectList.jsp 에러가 나는데 이클립스 인식오류 입니다
실행시 이상없음 

추가, 리스트

---

수정 삭제

dispatcher-servlet.xml

<?xml version="1.0" encoding="UTF-8"?>

<beans xmlns="http://www.springframework.org/schema/beans"
	xmlns:context="http://www.springframework.org/schema/context" 
	xmlns:p="http://www.springframework.org/schema/p"
	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-3.0.xsd
       http://www.springframework.org/schema/context
       http://www.springframework.org/schema/context/spring-context-3.0.xsd">
 	
 	<!-- 글작성 -->
 	<bean name="insertController" class="dr.mini.controller.InsertController" p:memberDao-ref="memberDao" />
 	
 	<!-- 리스트 -->
 	<bean name="selectListController" class="dr.mini.controller.SelectListController">
 		<property name="memberDao" ref="memberDao" />
 	</bean>
 	
 	<!-- 상세 페이지 -->
 	<bean name="selectController" class="dr.mini.controller.SelectController">
 		<property name="memberDao" ref="memberDao" />
 	</bean>
 	
 	<!-- 수정 -->
 	<bean name="updateController"  class="dr.mini.controller.UpdateController" p:memberDao-ref="memberDao" />
 	
 	<!-- 글 삭제 -->
 	<bean name="deleteController" class="dr.mini.controller.DeleteController">
 		<property name="memberDao" ref="memberDao" />
 	</bean>
 	
 	
    <!-- messageSource 지정 -->
   <bean id="messageSource"
		class="org.springframework.context.support.ResourceBundleMessageSource">
		<property name="basenames">
			<list>
				<value>messages.label</value>
				<value>messages.validation</value>
			</list>
		</property>
	</bean>
	<!-- Exception 설정  -->
	<bean
		class="org.springframework.web.servlet.handler.SimpleMappingExceptionResolver">
		<property name="exceptionMappings">
			<props>
				<prop key="java.lang.Exception">exception</prop>
			</props>
		</property>
	</bean>
	<!-- viewResolver -->
	<bean id="viewResolver" 
	      class="org.springframework.web.servlet.view.InternalResourceViewResolver">
		<property name="prefix" value="/WEB-INF/m2/" />
		<property name="suffix" value=".jsp" />
	</bean>
</beans>

InsertController

package dr.mini.controller;

import javax.validation.Valid;

import org.springframework.stereotype.Controller;
import org.springframework.validation.BindingResult;
import org.springframework.web.bind.WebDataBinder;
import org.springframework.web.bind.annotation.InitBinder;
import org.springframework.web.bind.annotation.ModelAttribute;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;

import dr.mini.dao.MemberDao;
import dr.mini.domain.MemberCommand;
import dr.mini.validator.MemberValidator;

@Controller
public class InsertController {
	private MemberDao memberDao;
	private String formViewName = "insertForm";

	public void setMemberDao(MemberDao memberDao) {
		this.memberDao = memberDao;
	}
	@RequestMapping(value="/insert.do",method=RequestMethod.GET)
	public String form(){
		return formViewName;
	}

	@ModelAttribute
	public MemberCommand formBacking(){
		return new MemberCommand();
	}
	@RequestMapping(value="/insert.do",method=RequestMethod.POST)
	public String submit(@Valid MemberCommand memberCommand,BindingResult result){
		if(result.hasErrors()){
			return formViewName;
		}
		memberDao.insertMember(memberCommand);		
		return "redirect:/list.do";
	}
	@InitBinder
	protected void initBinder(WebDataBinder binder){
		binder.setValidator(new MemberValidator());
	}
}

SelectListController

package dr.mini.controller;

import java.util.Collections;
import java.util.List;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.servlet.ModelAndView;

import dr.mini.dao.MemberDao;
import dr.mini.domain.MemberCommand;

@Controller
public class SelectListController {

	private static Log log = LogFactory.getLog(SelectListController.class);

	private MemberDao memberDao;

	private int pageSize = 10; // 한페이지의 글의 개수

	public void setMemberDao(MemberDao memberDao) {
		this.memberDao = memberDao;
	}

	@RequestMapping("/list.do")
	public ModelAndView process(
			@RequestParam(value = "pageNum", required = false) String pageNum) {

		if(pageNum == null){
			pageNum = "1";
		}
		int currentPage = Integer.parseInt(pageNum);
		//한 페이지의 시작글 번호
		int startRow = (currentPage - 1)* pageSize +1;
		//한 페이지의 마지막 글번호
		int endRow = currentPage * pageSize;
		
		if(log.isDebugEnabled()){
			log.debug("memberDao : "+memberDao);
		}
		int count = memberDao.getMemberCount();
		List<MemberCommand>list = null;
		if(count>0){
			list = memberDao.getMemberList(startRow,endRow);
		}else{
			list = Collections.emptyList();
		}
		ModelAndView mav = new ModelAndView();
		mav.setViewName("selectList");
		mav.addObject("count",count);
		mav.addObject("currentPage",currentPage);
		mav.addObject("pageSize",pageSize);
		mav.addObject("list",list);
		
		return mav;
	}
}

SelectController

package dr.mini.controller;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.servlet.ModelAndView;


import dr.mini.dao.MemberDao;
import dr.mini.domain.MemberCommand;

@Controller
public class SelectController {
	private static Log log = LogFactory.getLog(SelectController.class);

	private MemberDao memberDao;

	public void setMemberDao(MemberDao memberDao) {
		this.memberDao = memberDao;
	}

	@RequestMapping("/detail.do")
	public ModelAndView process(@RequestParam("id") String id){
		if(log.isDebugEnabled()){
			log.debug("memberDao : "+memberDao);
		}
		MemberCommand member = memberDao.getMember(id);
		return new ModelAndView("select","member",member);
	}
}

UpdateController

package dr.mini.controller;

import javax.validation.Valid;

import org.springframework.stereotype.Controller;
import org.springframework.validation.BindingResult;
import org.springframework.web.bind.WebDataBinder;
import org.springframework.web.bind.annotation.InitBinder;
import org.springframework.web.bind.annotation.ModelAttribute;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.servlet.ModelAndView;

import dr.mini.dao.MemberDao;
import dr.mini.domain.MemberCommand;
import dr.mini.validator.MemberValidator;

@Controller
public class UpdateController{

	private MemberDao memberDao;
	private String formViewName = "updateForm";

	public void setMemberDao(MemberDao memberDao) {
		this.memberDao = memberDao;
	}

	@RequestMapping(value="/update.do", method=RequestMethod.GET)
	public ModelAndView form(@RequestParam("id") String id){
		MemberCommand memberCommand = memberDao.getMember(id);
		return new ModelAndView(formViewName, "memberCommand", memberCommand);
	}
	@ModelAttribute
	public MemberCommand formBacking() {
		return new MemberCommand();
	}
	@RequestMapping(value="/update.do", method=RequestMethod.POST)
	public String submit(@Valid MemberCommand memberCommand, BindingResult result){

		if(result.hasErrors()){
			return formViewName;
		}
		memberDao.updateMember(memberCommand);
		return "redirect:/list.do";
	}
	@InitBinder
	protected void initBinder(WebDataBinder binder){
		binder.setValidator(new MemberValidator());
	}
}

DeleteController

package dr.mini.controller;

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

import dr.mini.dao.MemberDao;

@Controller
public class DeleteController{

	private MemberDao memberDao;

	public void setMemberDao(MemberDao memberDao) {
		this.memberDao = memberDao;
	}
	@RequestMapping("/delete.do")
	public ModelAndView process(@RequestParam("id") String id){
		memberDao.deleteMember(id);
		return new ModelAndView("redirect:list.do");
	}
	
}

MemberDao

package dr.mini.dao;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;

import javax.sql.DataSource;

import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;

import dr.mini.domain.MemberCommand;

public class MemberDao {
	private JdbcTemplate jdbcTemplate;

	public MemberDao(DataSource dataSource) {
		jdbcTemplate = new JdbcTemplate(dataSource);
	}

	// 글 등록
	private static final String INSERT_SQL = "insert into MEMBER1 values (?,?,?,sysdate)";

	public void insertMember(MemberCommand member) {
		jdbcTemplate.update(
				INSERT_SQL,
				new Object[] { member.getId(), member.getPasswd(),
						member.getName() });
	}

	// 글의 총 갯수
	private static final String SELECT_COUNT_SQL = "select count(*) from MEMBER1";

	public int getMemberCount() {
		return jdbcTemplate.queryForInt(SELECT_COUNT_SQL);
	}

	// 글 목록
	private static final String SELECT_LIST_SQL = "select * from (select a.*, rownum rnum from (select * from MEMBER1 order by register desc)a) where rnum >= ? and rnum <=?";

	public List<MemberCommand> getMemberList(int startRow, int endRow) {
		List<MemberCommand> list = jdbcTemplate.query(SELECT_LIST_SQL,
				new Object[] { startRow, endRow },
				new RowMapper<MemberCommand>() {
			public MemberCommand mapRow(ResultSet rs, int rowNum)
					throws SQLException {
				MemberCommand member = new MemberCommand();
				member.setId(rs.getString("id"));
				member.setPasswd(rs.getString("passwd"));
				member.setName(rs.getString("name"));
				member.setRegister(rs.getTimestamp("register"));

				return member;
			}
		});
		return list;
	}

	//글 상세
	private static final String SELECT_SQL ="select * from MEMBER1 where id=?";

	public MemberCommand getMember(String id){
		MemberCommand member = (MemberCommand)jdbcTemplate.queryForObject(SELECT_SQL, new Object[]{ id }, new RowMapper<MemberCommand>(){
					public MemberCommand mapRow(ResultSet rs, int rowNum)throws SQLException{
						MemberCommand member = new MemberCommand();
						member.setId(rs.getString("id"));
						member.setPasswd(rs.getString("passwd"));
						member.setName(rs.getString("name"));
						member.setRegister(rs.getTimestamp("register"));

						return member;
					}
				});
		return member;
	}
	
	
	
}

익명 내부 클래스의 형태로 사용 

MemberCommand

package dr.mini.domain;

import java.sql.Timestamp;

public class MemberCommand{
	private String id;
	private String passwd;
	private String name;
	private Timestamp register;
	
	public String getId() {
		return id;
	} 
	public void setId(String id) {
		this.id = id;
	}
	public String getPasswd() {
		return passwd;
	}
	public void setPasswd(String passwd) {
		this.passwd = passwd;
	}
	public String getName() {
		return name;
	}
	public void setName(String name) {
		this.name = name;
	}
	public Timestamp getRegister() {
		return register;
	}
	public void setRegister(Timestamp register) {
		this.register = register;
	}
	@Override
	public String toString() {
		return "Member [id=" + id + ", name=" + name + ", passwd=" + passwd
				+ ", reg_date=" + register + "]";
	}
	
}

MemberValidator

package dr.mini.validator;

import org.springframework.validation.Errors;
import org.springframework.validation.ValidationUtils;
import org.springframework.validation.Validator;

import dr.mini.domain.MemberCommand;

public class MemberValidator implements Validator {

	public boolean supports(Class<?> clazz) {
		return MemberCommand.class.isAssignableFrom(clazz);
	}

	public void validate(Object target, Errors errors) {
		MemberCommand command = (MemberCommand) target;
		System.out.println(command);
		ValidationUtils.rejectIfEmptyOrWhitespace(errors,"id", "error.required.member.id");
		ValidationUtils.rejectIfEmptyOrWhitespace(errors,"passwd", "error.required.member.passwd");
		ValidationUtils.rejectIfEmptyOrWhitespace(errors,"name", "error.required.member.name");
	}
}

deleteForm.jsp

<%@ page contentType="text/html; charset=euc-kr" %>
<%@ taglib prefix="spring" uri="http://www.springframework.org/tags" %>
<html>
<head><title><spring:message code="member.delete.title"/></title></head>
<body>
  <h2>member1 테이블에 레코드 삭제 예제</h2>
  <FORM METHOD="post" ACTION="delete.do">
    <INPUT TYPE="hidden" NAME="id" value="${param.id}"><p>
    <INPUT TYPE="submit" VALUE="삭제할까요?"> 
    <input type="button" value="취소" onclick="javascript:window.location='selectList.action'">
  </FORM>
</body>
</html>

exception.jsp

<%@ page contentType="text/html; charset=EUC-KR" %>
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=EUC-KR">
<title>에러</title>
</head>
<body>
요청을 처리하는 과정에서 문제가 발생했습니다:
${exception.message}
<%
Throwable exception = (Throwable) request.getAttribute("exception");
exception.printStackTrace();
%>
</body>
</html>

insertForm.jsp

<%@ page contentType="text/html; charset=euc-kr" %>
<%@ taglib uri="http://www.springframework.org/tags" prefix="spring"%>
<%@ taglib uri="http://www.springframework.org/tags/form" prefix="form"%>
<html>
<head><title><spring:message code="member.insert.title"/></title></head>
<body>
  <h2>member1 테이블에 레코드삽입(추가)예제</h2>
<form:form commandName="memberCommand">
<form:errors element="div"/>
    아이디 : <form:input path="id"/>
    <font color="red"><form:errors path="id" /></font><br/>

    패스워드 : <form:password path="passwd" showPassword="false"/>
    <font color="red"><form:errors path="passwd" /></font><br/>
    
    이름:<form:input path="name"/>
    <font color="red"><form:errors path="name" /></font><br/>
  
    <input type="submit" value="보내기">
 </form:form> 
</body>
</html>

select.jsp

<%@ page contentType="text/html; charset=euc-kr" %>
<%@ taglib prefix="fmt" uri="http://java.sun.com/jsp/jstl/fmt" %>
<%@ taglib prefix="spring" uri="http://www.springframework.org/tags" %>
<html>
<head><title><spring:message code="member.detail.title" arguments="${member.id }" /></title></head>
<body>
    아이디 : ${member.id }<br/><br/>
    패스워드 : ${member.passwd }<br/><br/>
    이름:${member.name }<br/><br/>
    날짜: <fmt:formatDate value="${member.register }" pattern="yyyy년 MM월 dd일"/><br/><br/>
  <a href="list.do">목록보기</a> | <a href="update.do?id=${member.id}">수정</a> | <a href="delete.do?id=${member.id }">삭제</a>
</body>
</html>

selectList.jsp

<%@ page contentType="text/html; charset=euc-kr" %>
<%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core" %>
<%@ taglib prefix="fmt" uri="http://java.sun.com/jsp/jstl/fmt" %>
<%@ taglib prefix="spring" uri="http://www.springframework.org/tags" %>
<html>
<head><title><spring:message code="member.list.title"/></title></head>
<body>
  <center><h2>회원목록</h2></center>
 <c:if test="${count == 0}">
  <center> 출력할 리스트가 없습니다. <a href="insert.do">등록</a> </center>
 </c:if>
 <c:if test="${count > 0}">
	 <TABLE width="550" border="1" align="center">
	    <TR>
	  	<TD colspan="4" align="right"><a href="insert.do">등록</a></TD>
	  </TR>
	  <TR>
	  	<TD width="100">아이디</TD>
	  	<TD width="100">패스워드</TD>
	  	<TD width="100">이름</TD>
	  	<TD width="250">가입일자</TD>
	  </TR>
	  <c:forEach var="member" items="${list}">
	  	<TR>
	  	   <TD width="100"><a href="detail.do?id=${member.id }">${member.id } </a></TD>
	  	   <TD width="100">${member.passwd }</TD>
	  	   <TD width="100">${member.name }</TD>
	  	   <TD width="250"><fmt:formatDate value="${member.register }" pattern="yyyy년 MM월 dd일"/></TD>
	    </TR>
	  </c:forEach>
	</TABLE>
	<div align="center">
	<c:if test="${count > 0}">
	   <c:set var="pageCount" value="${(count - 1) / pageSize + 1}"/>
	   <c:set var="pageBlock" value="${10}"/>
	   <fmt:parseNumber var="rs" value="${(currentPage - 1) / pageBlock}" integerOnly="true" />
	   <c:set var="startPage" value="${rs*pageBlock+1}"/>
	   <c:set var="endPage" value="${startPage + pageBlock-1}"/>
	   <c:if test="${endPage > pageCount}">
	        <c:set var="endPage" value="${pageCount}"/>
	   </c:if> 
	          
	   <c:if test="${startPage > pageBlock}">
	        <a href="list.do?pageNum=${startPage - pageBlock }">[이전]</a>
	   </c:if>
	
	   <c:forEach var="i" begin="${startPage}" end="${endPage}">
	       <c:if test="${i == currentPage}">
	          [${i}]
	       </c:if>
	       <c:if test="${i != currentPage}">
	           <a href="list.do?pageNum=${i}">[${i}]</a>
	       </c:if>
	   </c:forEach>
	
	   <c:if test="${endPage < pageCount}">
	        <a href="list.do?pageNum=${startPage + pageBlock}">[다음]</a>
	   </c:if>
	</c:if>
	</div>
</c:if>
</body>
</html>

selectList.jsp가 에러가 나지만 이클립스 오류이다

updateForm.jsp

<%@ page contentType="text/html; charset=euc-kr" %>
<%@ taglib prefix="spring" uri="http://www.springframework.org/tags" %>
<%@ taglib prefix="form" uri="http://www.springframework.org/tags/form" %>
<html>
<head>
     <title><spring:message code="member.update.title" /></title>
</head>
<body>
  <h2>member1 테이블에 레코드 수정 예제</h2>
<form:form commandName="memberCommand">
    아이디 : ${memberCommand.id}<br/><br/>
    패스워드 : <form:password path="passwd" />
    <font color="red"><form:errors path="passwd" /></font><br/><br/>
    이름:<form:input path="name" />
    <font color="red"><form:errors path="name" /></font><br/><br/>
    <input type="submit" value="보내기">
  </form:form>
</body>
</html>

공통 작업을 할경우에 공유 할수 있는 정보는 applicationContext.xml에 명시한다

 
JdbcTemplate 에서 주로 사용하는 메소드는 update(insert delete update) 한건의 레코드는 queryForObject

?의 문자열을 넘길때는 Object[] 배열을 이용

 
단지 목록을 처리할때 한건의 레코드는 RowMapper를 사용