[스프링] jdbc

mysql / hikariCP / springjdbc라이브러리를 pom.xml에 등록

 

스프링은 버전에 맞춰서 라이브러리를 추가해주는데,

스프링 버전이 바뀔때마다 자동으로 바뀌게 하려면 이런식으로 값을 넣어주면 된다.

 

 

데이터 베이스 / JdbcTemplate 클래스 빈 등록 / root-context.xml

package com.aia.firstspring.domain;

import java.sql.Timestamp;
import java.util.Date;

public class Member {
	
	private int idx;
	private String memberid;
	private String password;
	private String membername;
	private String memberphoto;
	private Timestamp regdate;
	public int getIdx() {
		return idx;
	}
	public void setIdx(int idx) {
		this.idx = idx;
	}
	public String getMemberid() {
		return memberid;
	}
	public void setMemberid(String memberid) {
		this.memberid = memberid;
	}
	public String getPassword() {
		return password;
	}
	public void setPassword(String password) {
		this.password = password;
	}
	public String getMembername() {
		return membername;
	}
	public void setMembername(String membername) {
		this.membername = membername;
	}
	public String getMemberphoto() {
		return memberphoto;
	}
	public void setMemberphoto(String memberphoto) {
		this.memberphoto = memberphoto;
	}
	public Timestamp getRegdate() {
		return regdate;
	}
	public void setRegdate(Timestamp regdate) {
		this.regdate = regdate;
	}
	
	
	public Date getToDate() { // ${member.toDate}
		return new Date(getRegdate().getTime());
		
	}
	
	
	
	
	@Override
	public String toString() {
		return "Member [idx=" + idx + ", memberid=" + memberid + ", password=" + password + ", membername=" + membername
				+ ", memberphoto=" + memberphoto + ", regdate=" + regdate + "]";
	}
	
	

}

 

 

 

예제1) 회원리스트출력

 

Memberdao [@Reporitory]로 Dao를 등록해준다 (bean등록안해도 간단하게 등록가능)

package com.aia.firstspring.member.dao;

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

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.ResultSetExtractor;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Repository;

import com.aia.firstspring.domain.Member;


@Repository
public class MemberDao {
	
	
	@Autowired
	private JdbcTemplate template;

	
	// jdbctemplate template 인스턴스를 주입
	// 1. 생성자 주입방식
	// 2. 프로퍼티 주입 방식
	// 3. 자동주입
	
	// 생성자를 이용한 객체 주입
	/*
	 * public MemberDao(JdbcTemplate t) { template = t;
	 * System.out.println("MemberDao 인스턴스 생성");
	 * 
	 * }
	 */
	
	public List<Member> selectMemberList() {
		String sql="select*from member order by memberid";
		
			return template.query(sql, new RowMapper<Member>() {

				@Override
				public Member mapRow(ResultSet rs, int rowNum) throws SQLException {
					Member member = new Member();
					member.setIdx(rs.getInt("idx"));
					member.setMemberid(rs.getString("memberid"));
					member.setMembername(rs.getString("membername"));
					member.setPassword(rs.getString("password"));
					member.setMemberphoto(rs.getString("memberphoto"));
					member.setRegdate(rs.getTimestamp("regdate"));
					return member;
				}
			}	
				
			);
	}
	
	public int selectTotalCount() {
		String sql = "select count(*) from member";
		return template.queryForObject(sql, Integer.class);
	}
}

 

 

MemberListController

package com.aia.firstspring.controller;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.ui.Model;
import org.springframework.web.bind.annotation.RequestMapping;

import com.aia.firstspring.member.service.MemberListService;

@Controller
public class MemberListController {
	
	@Autowired
	private MemberListService listService;
	
	
	@RequestMapping("/member/list")
	public String getMemberList(Model model) {	
		
		model.addAttribute("members",listService.getMemberList());
		model.addAttribute("memberCnt", listService.getMemberTotalCount());
		
		return "member/list";		
	}

}

MemberListService

package com.aia.firstspring.member.service;

import java.util.List;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import com.aia.firstspring.domain.Member;
import com.aia.firstspring.member.dao.MemberDao;

@Service
public class MemberListService {
	
	@Autowired
	MemberDao dao;
	
	/*
	 * public MemberListService(MemberDao dao) { this.dao=dao; }
	 */
	
	// 회원목록 반환 메서드
	public List<Member> getMemberList(){		
		return dao.selectMemberList();
	}
	
	// 회원수 몇명인지 int로 반환해주는 메서드
	public int getMemberTotalCount() {		
		return dao.selectTotalCount();
	}

}

view > list.jsp

<%@ page language="java" contentType="text/html; charset=UTF-8"
	pageEncoding="UTF-8"%>
<%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core"%>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>Member List</title>
</head>
<body>

	<h1>회원리스트</h1>

	<hr>
	${members}
	
	<h3>총회원수 ${memberCnt}</h3>

	<table>
		<tr>
			<th>idx</th>
			<th>id</th>
			<th>password</th>
			<th>name</th>
			<th>사진</th>
			<th>가입일</th>
		</tr>
		
		<c:forEach items="${members}" var="member"> 
			<tr>
				<td>${member.idx}</td>
				<td>${member.memberid}</td>
				<td>${member.password}</td>
				<td>${member.membername}</td>
				<td>${member.memberphoto}</td>
				<td>${member.toDate}</td>
			</tr>
		</c:forEach>
		
	</table>

</body>
</html>

 

 

 

 

예제2 ) 회원가입하기

 

컨트롤러 > 회원가입 성공시 리스트출력으로

package com.aia.firstspring.controller;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.ui.Model;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;

import com.aia.firstspring.domain.Member;
import com.aia.firstspring.member.service.MemberRegService;

@Controller
@RequestMapping("/member/reg")
public class MemberRegController {
	
	@Autowired
	private MemberRegService regService;

	@RequestMapping(method = RequestMethod.GET)
	public String getRegForm() {
		return "member/regForm";
	}
	
	@RequestMapping(method = RequestMethod.POST)
	public String memberReg(Member member, Model model) {
		
		int resultCnt = regService.insertMember(member);
		String view="member/reg";
		model.addAttribute("resultCnt", resultCnt);
		
		if(resultCnt==1) {
			view= "redirect:/member/list";
		}
		return view;
	}
}

서비스

package com.aia.firstspring.member.service;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import com.aia.firstspring.domain.Member;
import com.aia.firstspring.member.dao.MemberDao;

@Service
public class MemberRegService {

	@Autowired
	private MemberDao dao;

	public int insertMember(Member member) {
		int result=0;
		try {
			result = dao.insertMember(member);
		}catch (Exception e){
			e.printStackTrace();
			
		}
		return result;
		
	}
}

MemberDao에 회원가입 메서드 추가

	public int insertMember(Member member) {
		//그냥 세개만 하기로 했음 ㅎ..
		String sql="insert into member (memberid, membername, password) values (?,?,?)";
		return template.update(sql, member.getMemberid(), member.getMembername(), member.getPassword());
		
	}

 

 

RegForm / name속성 맞춰줄것

<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>회원가입 폼</title>
</head>
<body>

<h1>회원 가입 폼</h1>
<form method="post">
아이디 <input type="text" name="memberid"><br>
비밀번호 <input type="password" name="password"><br>
이름 <input type="text" name="membername"><br>
<input type="submit">


</form>
</body>
</html>

reg / 가입 실패시 나오는 창

<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>회원가입 폼</title>
</head>
<body>

<h1>회원 가입 실패</h1>
${resultCnt}


</form>
</body>
</html>