spring - 스프링 JDBC 기능

오늘·2021년 5월 31일
1

웹 페이지 연습

목록 보기
22/35

스프링 JDBC 특징

스프링에서 제공하는 JDBC의 특징

  • 기존 JDBC의 장점과 단순함을 유지하면서 단점을 보안했다
  • 간결한 API뿐 아니라 확장된 JDBC 기능도 제공
    -> 실제 개발진행시에는 스프링 JDBC보다는 마이바티스나 하이버네이트 같은 DB연동 관련 프레임 워크를 더 많이 사용한다고 한다. 기본적인 기능들만 알아두고 넘어가자.

실습 준비하기

  1. 새 프로젝트에 03에서 사용했던 lib폴더의 라이브러리들과 web.xml / action-servlet.xml을 복사해 동일 위치에 붙여넣기
  2. 실습에 필요한 파일 구조
  3. sql 테이블 내용
create table t_member(
	id varchar(10) not null,
    pwd varchar(10) not null,
    name varchar(20),
    email varchar(20),
    joinDate date);

insert into t_member values ('a1', '123', '홍길동', 'aert@naver.com', date('2020/10/10') );
insert into t_member values ('a2', '456', '김유신', 'teadf@gmail.com', date('2020/8/7') );
insert into t_member values ('a3', '789', '이진아', 'qedc@naver.com', date('2019/11/16') );
insert into t_member values ('a4', '145', '곽용권', 'pijl@hanmail.com', date('2021/01/10') );
insert into t_member values ('a5', '243', '진홍섭', 'qekp@naver.com', date('2020/12/04') );

select * from t_member;

실습하기 01

1. listMember.jsp

<%@ page language="java" contentType="text/html; charset=utf-8"
	pageEncoding="utf-8" isELIgnored="false"%>
<%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core" %>
<c:set var="contextPath" value="${pageContext.request.contextPath}" />
<% request.setCharacterEncoding("utf-8"); %>

<!DOCTYPE html>
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8">
<title>회원 정보 출력 창</title>
</head>
<body>
	<!-- DB안에 있는 자료 검색해 출력하는 폼 -->
	<table border="1" align="center" width="80%">
		<tr align="center" bgcolor="lightgray">
      		<td ><b>아이디</b></td>
      		<td><b>비밀번호</b></td>
      		<td><b>이름</b></td>
      		<td><b>이메일</b></td>
      		<td><b>가입일</b></td>
		</tr>
		
		<c:forEach var="member" items="${memberList}">
			<td>${member.id}</td>
			<td>${member.pwd}</td>
			<td>${member.name}</td>
			<td>${member.email}</td>
			<td>${member.joinDate}</td>		
		</c:forEach>
	</table>
	<a  href="${contextPath}/member/memberForm.do">
		<h1 style="text-align:center">회원가입</h1>
	</a>
</body>
</html>

2. memberInfo.jsp

<%@ page language="java" contentType="text/html; charset=utf-8"
	pageEncoding="utf-8" isELIgnored="false"%>
<%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core" %>
<%@ taglib prefix="fmt" uri="http://java.sun.com/jsp/jstl/fmt" %>
<c:set var="contextPath" value="${pageContext.request.contextPath}" />
<% request.setCharacterEncoding("utf-8"); %>

<!DOCTYPE html>
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8">
<title>회원 등록 창</title>
</head>
<style>
   .text_center{
     text-align:center;
   }
</style>
<body>
	<!-- 클라이언트쪽의 회언 등록창 -->
	<form method="post" action="${contextPath}/member/addMember.do">
		<h1 class="text_center">회원 정보 등록창</h1>
		<table align="center">
			<tr>
				<td width="200"><p align="right">아이디</td>
				<td width="400"><input type="text" name="id"></td>
			</tr>
			<tr>
				<td width="200"><p align="right">비밀번호</td>
				<td width="400"><input type="password" name="pwd"></td>
			</tr>
			<tr>
				<td width="200"><p align="right">이름</td>
				<td width="400"><input type="text" name="name"></td>
			</tr>
			<tr>
				<td width="200"><p align="right">이메일</td>
				<td width="400"><input type="text" name="email"></td>
			</tr>
			<tr>
				<td width="200"><p align="right"> &nbsp; </p></td>
				<td width="400"><input type="submit" value="가입하기">
								<input type="reset" value="다시 입력">
			</tr>
		</table>
	</form>
</body>
</html>

3. MemberController.java

package com.spring.member.controller;

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

import org.springframework.web.servlet.ModelAndView;

public interface MemberController {
	public ModelAndView listMembers(HttpServletRequest request,
			HttpServletResponse response) throws Exception;
}

4. MemberControllerImpl.java

package com.spring.member.controller;

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

import org.springframework.web.servlet.ModelAndView;
import com.spring.member.service.MemberService;
import com.sun.xml.internal.bind.v2.schemagen.xmlschema.List;


public class MemberControllerImpl implements MemberController {
	private MemberService memberService;
	
	// 변수가 private이니까 접근하기 위해서 setter를 구현해줘야한다.
	public void setMemberService(MemberService memberService) {
		this.memberService = memberService;
	}
	
	// /member/listmember.do로 요청시 호출되는 곳
	@Override
	public ModelAndView listMembers(HttpServletRequest request,
			HttpServletResponse response) throws Exception {
		String viewName = getViewName(request);
		List membersList = memberService.listmembers();
		ModelAndView mav = new ModelAndView(viewName);
		
		// addObject() 메소드를 이용해 조회한 회원정보를 넣어주기
		mav.addObject("membersList", membersList);
		
		return mav;
	}
	
	// getViewName(request)를 사용할 수 있게끔 하는 메소드
	private String getViewName(HttpServletRequest request) throws Exception {
		String contextPath = request.getContextPath();
		String uri = (String) request.getAttribute("javax.servlet.include.request_uri");
		if (uri == null || uri.trim().equals("")) {
			uri = request.getRequestURI();
		}

		int begin = 0;
		if (!((contextPath == null) || ("".equals(contextPath)))) {
			begin = contextPath.length();
		}

		int end;
		if (uri.indexOf(";") != -1) {
			end = uri.indexOf(";");
		} else if (uri.indexOf("?") != -1) {
			end = uri.indexOf("?");
		} else {
			end = uri.length();
		}

		String fileName = uri.substring(begin, end);
		if (fileName.indexOf(".") != -1) {
			fileName = fileName.substring(0, fileName.lastIndexOf("."));
		}
		if (fileName.lastIndexOf("/") != -1) {
			fileName = fileName.substring(fileName.lastIndexOf("/"), fileName.length());
		}
		return fileName;
	}
}

5. MemberVo.java

package com.spring.member.vo;

import java.sql.Date;

public class MemberVO {
	private String id;
	private String pwd;
	private String name;
	private String email;
	private Date joinDate;

	public MemberVO() {
		
	}

	public String getId() {
		return id;
	}

	public void setId(String id) {
		this.id = id;
	}

	public String getPwd() {
		return pwd;
	}

	public void setPwd(String pwd) {
		this.pwd = pwd;
	}

	public String getName() {
		return name;
	}

	public void setName(String name) {
		this.name = name;
	}

	public String getEmail() {
		return email;
	}

	public void setEmail(String email) {
		this.email = email;
	}

	public Date getJoinDate() {
		return joinDate;
	}

	public void setJoinDate(Date joinDate) {
		this.joinDate = joinDate;
	}
}

6. MemberDAO.java

package com.spring.member.dao;

import org.springframework.dao.DataAccessException;

import com.sun.xml.internal.bind.v2.schemagen.xmlschema.List;

public interface MemberDAO {
	public List selectAllMembers() throws DataAccessException;
	public int addMember(MemberVO memberVo) throws DataAccessException;
}

7. MemberDAOImpl.java

package com.spring.member.dao;

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

import javax.sql.DataSource;

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

import com.spring.member.vo.MemberVO;


public class MemberDAOImpl implements MemberDAO {
	private JdbcTemplate jdbcTemplate;
	
	// 설정 파일에서 생성한 dataSource 빈을
	// setter를 이용 JdbcTemplate 클래스 생성자에 집어넣는다
	public void setDataSource(DataSource dataSource) {
		this.jdbcTemplate = new JdbcTemplate(dataSource);
	}
	
	@Override
	public List selectAllMembers() throws DataAccessException {
		String query = "select id,pwd,name,email,joinDate"
						+ " from t_member "
						+ " order by joinDate desc";
		List membersList = new ArrayList();
		
		membersList = this.jdbcTemplate.query(query, new RowMapper() {
			public Object mapRow(ResultSet rs, int rowNum) throws SQLException {
				
				MemberVO memberVo = new MemberVO();
				memberVo.setId(rs.getString("id"));
				memberVo.setPwd(rs.getString("pwd"));
				memberVo.setName(rs.getString("name"));
				memberVo.setEmail(rs.getString("email"));
				memberVo.setJoinDate(rs.getDate("joinDate"));
				
				return memberVo;
			}
		});
		return membersList;
	}
	
	@Override
	public int addMember(MemberVO memberVo) throws DataAccessException {
		String id = memberVo.getId();
		String pwd = memberVo.getPwd();
		String name = memberVo.getName();
		String email = memberVo.getEmail();
		String query = "insert into t_member(id, pwd, name, email) values ("
						+"'" + id + "'"
						+"'" + pwd + "'"
						+"'" + name + "'"
						+"'" + email + "')";
		System.out.println(query);
		// jdbcTemplate 클래스의 update() 메소드로 회원 정보 추가
		int result = jdbcTemplate.update(query);
		
		System.out.println(result);
		return result;
	}
}

8. listMembers.jsp

조회한 회원 정보가 화면에 표시되는 곳

<%@ page language="java" contentType="text/html; charset=utf-8"
	pageEncoding="utf-8" isELIgnored="false"%>
<%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core" %>
<c:set var="contextPath" value="${pageContext.request.contextPath}" />
<% request.setCharacterEncoding("utf-8"); %>

<!DOCTYPE html>
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8">
<title>회원 정보 출력 창</title>
</head>
<body>
	<!-- DB안에 있는 자료 검색해 출력하는 폼 -->
	<table border="1" align="center" width="80%">
		<tr align="center" bgcolor="lightgray">
      		<td ><b>아이디</b></td>
      		<td><b>비밀번호</b></td>
      		<td><b>이름</b></td>
      		<td><b>이메일</b></td>
      		<td><b>가입일</b></td>
		</tr>
		
		<c:forEach var="member" items="${memberList}">
			<td>${member.id}</td>
			<td>${member.pwd}</td>
			<td>${member.name}</td>
			<td>${member.email}</td>
			<td>${member.joinDate}</td>		
		</c:forEach>
	</table>
	<a  href="${contextPath}/member/memberForm.do">
		<h1 style="text-align:center">회원가입</h1>
	</a>
</body>
</html>

어렵다

profile
오늘
이전 포스트

spring - 스프링 MVC 기능

다음 포스트

spring - 마이바티스 프레임워크

1개의 댓글

comment-user-thumbnail
이익수
2022년 2월 28일

많이 배워갑니다.

답글 달기

관련 채용 정보