스프링에서 제공하는 JDBC의 특징
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;
<%@ 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>
<%@ 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"> </p></td>
<td width="400"><input type="submit" value="가입하기">
<input type="reset" value="다시 입력">
</tr>
</table>
</form>
</body>
</html>
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;
}
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;
}
}
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;
}
}
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;
}
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;
}
}
조회한 회원 정보가 화면에 표시되는 곳
<%@ 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>
어렵다
많이 배워갑니다.