✿∘˚˳°∘°
78일차 : Mybatis 본문
20230320
mybatis : 데이터의 입력,조회,수정,삭제를 보다 편하게 사용하기 위한 영속성 프레임워크
- Mybatis 사용을 위한 사전 Setting
1. pom.xml에 Maven Repository 에서 검색한 라이브러리 2개 추가 - 사전에 있어야하는 라이브러리가 있다
<!-- Mybatis를 사용하기 위한 라이브러리(이미 있어야하는 것 : ojdbc, commons-dbcp, spring-jdbc / 추가할것(mybatis, mybatis-spring)) -->
<!-- https://mvnrepository.com/artifact/org.mybatis/mybatis(순수Mybatis) -->
<dependency>
<groupId>org.mybatis</groupId>
<artifactId>mybatis</artifactId>
<version>3.4.6</version>
</dependency>
<!-- https://mvnrepository.com/artifact/org.mybatis/mybatis-spring(스프링과 연동하기위해, Mybatis는 독립적으로도 사용이 가능하다 Spring에 종속적인게X) -->
<dependency>
<groupId>org.mybatis</groupId>
<artifactId>mybatis-spring</artifactId>
<version>1.3.2</version>
</dependency>2. servlet-context.xml에 mybatis 설정객체 추가
<!-- jdbc를 사용하지 않고 mybatis를 사용할 것 -->
<!-- Mybatis 설정 객체 -->
<beans:bean id="sqlSession" class="org.mybatis.spring.SqlSessionFactoryBean">
<beans:property name="dataSource" ref="dataSource" />
<beans:property name="configLocation" value="classpath:mybatis-config.xml" />
<beans:property name="mapperLocations" value="classpath:/mapper/*SQL.xml" />
<!-- 여기서 말하는 classpath는 scr/main/resources 폴더 -->
</beans:bean>
<!-- 실제 DB작업을 수행하는 객체 -->
<beans:bean id="sqlSessionTemplate" class="org.mybatis.spring.SqlSessionTemplate">
<beans:constructor-arg ref="sqlSession" />
</beans:bean>3. window - preferences - XML Catalog - User Specified Entries 추가
[ Config ]
Location : http://mybatis.org/dtd/mybatis-3-config.dtd
Key : -//mybatis.org//DTD Config 3.0//EN
[ Mapper ]
Location : http://mybatis.org/dtd/mybatis-3-mapper.dtd
Key : -//mybatis.org//DTD Mapper 3.0//EN
4. src/main/resources에 mybatis-config와 mapper패키지추가(mapper패키지에 앞으로 작성할 SQL문이 들어갈것)
5. mybatis-config
- 앞으로 새로운 VO를 작성할 때마다 typeAlias를 작성(mapper에서 별명으로 사용하기위함)
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE configuration PUBLIC "-//mybatis.org//DTD Config 3.0//EN" "http://mybatis.org/dtd/mybatis-3-config.dtd" >
<configuration>
<settings>
<!-- null값이 발생하면 빈칸("")이 아닌 null로 처리(null일 때 빈칸으로 처리되는 기본설정이 있기 때문에) -->
<setting name="jdbcTypeForNull" value="NULL" />
</settings>
<typeAliases>
<typeAlias type="kr.or.iei.member.model.vo.Member" alias="m"/>
<typeAlias type="kr.or.iei.notice.model.vo.Notice" alias="n"/>
</typeAliases>
</configuration>
6. mapper
- mapper생성 시 namespace를 지정 -> 자바에서 해당mapper를 찾을 수 있게해준다.
- 명명규칙 : beans에서 설정해준 값대로 이름을 붙여줘야한다. servlet-context에서 *SQL이라고 작성했으므로 규칙을 따라야함(앞에 무슨문자가와도 마지막은 SQL로 끝나야함, 대문자 중요)
- [ Member ]
Controller / Service / Dao 에 Component를 해주는것은 물론 Component-scan / 사용할 객체에 @Autowired해주는것은 기존과 동일 단, Dao에서 jdbcTemplate이 아닌 sqlSessionTemplate을 사용할것
DAO
@Repository
public class MemberDao {
@Autowired
private SqlSessionTemplate sqlSession;
//private JdbcTemplate jdbc;
//import - jdbc / rowMapper 다 지워도 된다(rowmapper는 mybatis를 사용하면 필요없기때문에 클래스도 삭제)
}index.jsp
<%@ page language="java" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8"%>
<%@taglib uri="http://java.sun.com/jsp/jstl/core" prefix="c"%>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>Insert title here</title>
</head>
<body>
<h1>Spring_Mybatis</h1>
<hr>
<c:choose>
<c:when test="${empty sessionScope.m }">
<form action="login.do" method="post">
<fieldset>
<legend>로그인</legend>
아이디 : <input type="text" name="memberId"><br>
비밀번호 : <input type="password" name="memberPw"><br>
<input type="submit" value="로그인">
</fieldset>
</form>
<h3><a href="/joinFrm.do">회원가입</a></h3>
</c:when>
<c:otherwise>
<h2>[${sessionScope.m.memberName }]</h2>
<h3><a href="/logout.do">로그아웃</a></h3>
<form action="/searchMemberId.do">
조회할 아이디 입력 :
<input type="text" name="memberId">
<input type="submit" value="조회">
</form>
<h3><a href="/allMember.do">전체회원조회</a></h3>
<h3><a href="/mypage.do">마이페이지</a></h3>
<h3><a href="/noticeList.do">공지사항</a></h3>
<h3><a href="/deleteMember.do?memberNo=${sessionScope.m.memberNo }">회원탈퇴</a></h3>
</c:otherwise>
</c:choose>
</body>
</html>1. 로그인 / 로그아웃
Controller
@RequestMapping(value="/login.do")
public String login(Member m, HttpSession session) {
Member loginMember = service.selectOneMember(m);
if(loginMember != null) {
session.setAttribute("m", loginMember);
}
return "redirect:/";
}
@RequestMapping(value="/logout.do")
public String logout(HttpSession session) {
session.invalidate();
return "redirect:/";
}Service
public Member selectOneMember(Member member) {
return dao.selectOneMember(member);
}Dao
public Member selectOneMember(Member member) {
/*
String query = "select * from member_tbl where member_id = ? and member_pw = ?";
Object[] params = {m.getMemberId(), m.getMemberPw()};
List list = jdbc.query(query, params, new MemberRowMapper());
return (Member)list.get(0);
*/
//조회결과가 하나이기 때문에 selectOne
//selectOneMember앞의 member는 Mapper 중 namespace를 찾는 것
//즉, mapper가 여러개 있을 텐데 namespace가 member인 Mapper를 찾아가서 id가 selectOneMember인 쿼리를 수행하라는 의미
Member m = sqlSession.selectOne("member.selectOneMember",member); //dao코드 완료 -> mapper에 sql코드 작성
return m;
}memberSQL(mapper)
<?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="member">
<!-- mapper를 만들면 cache-ref를 지워주고 mapper에 이름을 붙여준다. -->
<!-- Member가 여러개이기 때문에 패키지까지 다 적어줘야함(kr.or.iei.member.model.vo.Member) : 다 적어주기 힘드므로 config에서 별명으로 지정 가능 -->
<select id="selectOneMember" parameterType="m" resultType="m">
<!-- 세미콜론(;) 찍으면 안됨 -->
<!-- 위치홀더에 넣어줄 값은 파라미터로 들어온 m에 들어가있음 -->
select
<!-- 컬럼명의 별칭을 Member(VO)의 변수명으로 지정해주면 resultType의 m에 자동으로 넣어준다 -->
member_no as memberNo,
member_id as memberId,
member_pw as memberPw,
member_name as memberName,
member_phone as memberPhone,
member_email as memberEmail
from member_tbl where member_id=#{memberId} and member_pw=#{memberPw}
</select>
2. 아이디 검색으로 회원조회
Controller
@RequestMapping(value="/searchMemberId.do")
public String searchMemberId(String memberId, Model model) {
Member m = service.selectOneMember(memberId);
if(m != null) {
model.addAttribute("m", m);
return "member/searchMemberId";
}else {
return "redirect:/";
}
}Service
public Member selectOneMember(String memberId) {
return dao.selectOneMember(memberId);
}Dao
public Member selectOneMember(String memberId) {
Member m = sqlSession.selectOne("member.searchMemberId", memberId);
return m;
}memberSQL
<select id="searchMemberId" parameterType="string" resultType="m">
select
member_no as memberNo,
member_id as memberId,
member_pw as memberPw,
member_name as memberName,
member_phone as memberPhone,
member_email as memberEmail
from member_tbl where member_id=#{_parameter}
<!-- where에 memberId를 넣어줘도 되지만 매개변수가 vo형태가 아니라 단일갯수이면 _parameter를 적어주면된다. 문자/숫자 상관없이 하나이기만 하면 전부! -->
</select>3. 전체회원조회
Controller
@RequestMapping(value="/allMember.do")
public String allMember(Model model) {
ArrayList<Member> list = service.selectAllMmeber();
model.addAttribute("list", list);
return "member/allMember";
}Service
public ArrayList<Member> selectAllMmeber() {
return dao.selectAllMember();
}Dao
public ArrayList<Member> selectAllMember() {
List list = sqlSession.selectList("member.selectAllMember");
return (ArrayList<Member>)list;
}memberSQL
<!-- 쿼리문 실행을 위한 매개변수가 없는경우 parameterType 생략 -->
<!-- 이미 dao에서 selectList()를 사용했으므로 list로 오는건 확정 -> list안에 들어가는 자료형으로 지정해주면된다 "m" -->
<select id="selectAllMember" resultType="m">
select
member_no as memberNo,
member_id as memberId,
member_pw as memberPw,
member_name as memberName,
member_phone as memberPhone,
member_email as memberEmail
from member_tbl
order by member_no
</select>4. 회원가입
Controller
@RequestMapping(value="/joinFrm.do")
public String joinFrm() {
return "member/joinFrm";
}
@RequestMapping(value="/join.do")
public String join(Member member) {
int result = service.insertMember(member);
if(result > 0) {
return "redirect:/";
}else {
return "redirect:/joinFrm.do";
}
}Service
public int insertMember(Member member) {
return dao.insertMember(member);
}Dao
public int insertMember(Member member) {
int result = sqlSession.insert("member.insertMember", member);
return result;
}memberSQL
<!-- insert/update/delete는 무조건 resultType이 int로 고정되어있으므로 명시하면 안된다. 쓰지않고 항상 int로 빠지는것 -->
<insert id="insertMember" parameterType="m" >
insert into member_tbl values(member_seq.nextval, #{memberId}, #{memberPw}, #{memberName}, #{memberPhone}, #{memberEmail})
</insert>
5. 회원탈퇴
Controller
@RequestMapping(value="/deleteMember.do")
public String deleteMember(int memberNo) {
int result = service.deleteMember(memberNo);
if(result > 0) {
return "redirect:/logout.do";
}else {
return "redirect:/";
}
}Service
public int deleteMember(int memberNo) {
return dao.deleteMember(memberNo);
}Dao
public int deleteMember(int memberNo) {
int result = sqlSession.delete("member.deleteMember", memberNo);
return result;
}memberSQL
<delete id="deleteMember" parameterType="_int">
delete from member_tbl where member_no = #{_parameter}
</delete>
6. 마이페이지(정보수정)
Controller
@RequestMapping(value="/mypage.do")
public String mypage() {
return "member/mypage";
}
@RequestMapping(value="/updateMember.do")
public String updateMember(Member member, @SessionAttribute(required = false) Member m) {
int result = service.updateMember(member);
if(result > 0) {
m.setMemberPw(member.getMemberPw());
m.setMemberPhone(member.getMemberPhone());
m.setMemberEmail(member.getMemberEmail());
return "redirect:/mypage.do";
}else {
return "redirect:/";
}
}Service
public int updateMember(Member member) {
return dao.updateMember(member);
}Dao
public int updateMember(Member member) {
int result = sqlSession.update("member.updateMember", member);
return result;
}memberSQL
<update id="updateMember" parameterType="m">
update member_tbl set
member_pw = #{memberPw},
member_phone = #{memberPhone},
member_email = #{memberEmail}
where member_no = #{memberNo}
</update>
- [ Notice ]
Controller
package kr.or.iei.notice.controller;
import java.util.ArrayList;
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 kr.or.iei.notice.model.service.NoticeService;
import kr.or.iei.notice.model.vo.Notice;
@Controller
public class NoticeController {
@Autowired
private NoticeService service;
/*
<공지사항>
1. 전체목록
2. 글쓰기
3. 상세보기
4. 수정
5. 삭제
*/
@RequestMapping(value="/noticeList.do")
public String noticeList(Model model) {
ArrayList<Notice> list = service.selectAllNotice();
if(list != null) {
model.addAttribute("list", list);
return "notice/noticeList";
}else {
return "redirect:/";
}
}
@RequestMapping(value="/noticeView.do")
public String noticeView(int noticeNo, Model model) {
Notice n = service.selectOneNotice(noticeNo);
if(n != null) {
model.addAttribute("n", n);
return "notice/noticeView";
}else {
return "redirect:/noticeList.do";
}
}
@RequestMapping(value="/noticeWriteFrm.do")
public String noticeWriteFrm() {
return "notice/noticeWriteFrm";
}
@RequestMapping(value="/insertNotice.do")
public String insertNotice(Notice n) {
int result = service.insertNotice(n);
if(result > 0) {
return "redirect:/noticeList.do";
}else {
return "notice/noticeWriterFrm";
}
}
@RequestMapping(value="/updateNoticeFrm.do")
public String updateNoticeFrm(int noticeNo, Model model) {
Notice n = service.selectOneNotice(noticeNo);
if(n != null) {
model.addAttribute("n", n);
return "notice/updateNoticeFrm";
}else {
return "redirect:/noticeList.do";
}
}
@RequestMapping(value="/updateNotice.do")
public String updateNotice(Notice n) {
int result = service.updateNotice(n);
if(result > 0) {
return "redirect:/noticeView.do?noticeNo="+n.getNoticeNo();
}else {
return "redirce:/noticeList.do";
}
}
@RequestMapping(value="/deleteNotice.do")
public String deleteNotice(int noticeNo) {
int result = service.deleteNotice(noticeNo);
if(result>0) {
return "redirect:/noticeList.do";
}else {
return "redirect:/noticeView.do?noticeNo="+noticeNo;
}
}
}Service
package kr.or.iei.notice.model.service;
import java.util.ArrayList;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import kr.or.iei.notice.model.dao.NoticeDao;
import kr.or.iei.notice.model.vo.Notice;
@Service
public class NoticeService {
@Autowired
private NoticeDao dao;
public ArrayList<Notice> selectAllNotice() {
return dao.selectAllNotice();
}
public Notice selectOneNotice(int noticeNo) {
return dao.selectOneNotice(noticeNo);
}
public int insertNotice(Notice n) {
return dao.insertNotice(n);
}
public int updateNotice(Notice n) {
return dao.updateNotice(n);
}
public int deleteNotice(int noticeNo) {
return dao.deleteNotice(noticeNo);
}
}Dao
package kr.or.iei.notice.model.dao;
import java.util.ArrayList;
import java.util.List;
import org.mybatis.spring.SqlSessionTemplate;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Repository;
import kr.or.iei.notice.model.vo.Notice;
@Repository
public class NoticeDao {
@Autowired
private SqlSessionTemplate sqlSession;
public ArrayList<Notice> selectAllNotice() {
List list = sqlSession.selectList("notice.selectAllNotice");
return (ArrayList<Notice>)list;
}
public Notice selectOneNotice(int noticeNo) {
Notice n = sqlSession.selectOne("notice.selectOneNotice", noticeNo);
return n;
}
public int insertNotice(Notice n) {
int result = sqlSession.insert("notice.insertNotice", n);
return result;
}
public int updateNotice(Notice n) {
int result = sqlSession.update("notice.updateNotice", n);
return result;
}
public int deleteNotice(int noticeNo) {
int result = sqlSession.delete("notice.deleteNotice", noticeNo);
return result;
}
}noticeSQL
<?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="notice">
<select id="selectAllNotice" resultType="n">
select
notice_no as noticeNo,
notice_title as noticeTitle,
notice_writer as noticeWriter,
notice_date as noticeDate
from notice
order by 1 desc
</select>
<select id="selectOneNotice" parameterType="_int" resultType="n">
select
notice_no as noticeNo,
notice_title as noticeTitle,
notice_writer as noticeWriter,
notice_content as noticeContent,
notice_date as noticeDate
from notice where notice_no = #{_parameter}
</select>
<insert id="insertNotice" parameterType="n">
insert into notice values(notice_seq.nextval, #{noticeTitle}, #{noticeWriter}, #{noticeContent}, to_char(sysdate, 'yyyy-mm-dd'))
</insert>
<update id="updateNotice" parameterType="n">
update notice set notice_title = #{noticeTitle}, notice_content = #{noticeContent} where notice_no = #{noticeNo}
</update>
<delete id="deleteNotice" parameterType="_int">
delete from notice where notice_no = #{_parameter}
</delete>
</mapper>'국비수업 > Spring' 카테고리의 다른 글
| 80일차 : AOP, Transaction, Schedul (0) | 2023.03.22 |
|---|---|
| 79일차 : Dynamic Mybatis (0) | 2023.03.21 |
| 77일차 : SpringMVC - 게시판2 (0) | 2023.03.17 |
| 76일차 : 정규화, SpringMVC - 게시판 (0) | 2023.03.16 |
| 75일차 : SpringMVC - 회원, 공지사항 (0) | 2023.03.15 |
Comments