[Mybatis] 마이바티스로 INSERT, UPDATE, DELETE 하는 방법

INSERT, UPDATE, DELETE 

• UPDATE, INSERT, DELETE 추가하기 위해 공식사이트에서 기본적으로 제공해주는 코드를 추가해줍니다.
• mybatis 공식사이트 : https://mybatis.org/

<insert id="insertAuthor">
  insert into Author (id,username,password,email,bio)
  values (#{id},#{username},#{password},#{email},#{bio})
</insert>

<update id="updateAuthor">
  update Author set
    username = #{username},
    password = #{password},
    email = #{email},
    bio = #{bio}
  where id = #{id}
</update>

<delete id="deleteAuthor">
  delete from Author where id = #{id}
</delete>

mybatis

 

---

도서를 예시로 추가, 수정, 삭제하는 것을 더하여 작성해보도록 하겠습니다.

 

1. bookMapper.xml 파일에 추가, 수정, 삭제 mapper를 설정합니다.

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
  PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
  "https://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="Book">
<!-- 조회하기 -->
  <select id="findAll" resultType="bookVO">
    select * from book order by bookied
  </select>
  
 <!-- 상세조회하기 --> 
  <select id="findByBookied" resultType="bookVO">
  	select * from book where bookied = #{bookied}
  </select>
  
  <!--추가등록하기 --> 
  <insert id="insert" parameterType="bookVO">
  <!-- values에 ? 대신 VO의 변수로 작성해줍니다. -->
 	 insert into book(bookied,bookname,publisher,price) values(#{bookied},#{bookname},#{publisher},#{price})
  </insert>
 <!-- 수정하기 --> 
	<update id="update" parameterType="bookVO">
		update book set bookname=#{bookname}, publisher=#{publisher}, price=#{price} where bookied=#{bookied}
	</update>
 <!-- 삭제하기 --> 
 	<delete id="delete" parameterType="bookVO">
 		delete book where bookied=#{bookied}
 	</delete>
	
</mapper>

 

 

 

2. DBManager.java 에 추가, 수정, 삭제 메소드를 추가합니다.

package com.example.demo.db;

import java.io.InputStream;
import java.util.List;

import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;

import com.example.demo.vo.BookVO;

public class DBManager {
	
	// 객체와 무관하게 어플리케이션이 생성될 때 누구나 쓸 수 잇도록 맴버변수로 지정함.
	public static SqlSessionFactory sqlSessionFactory;
	
	static {
		try {
			String resource = "com/example/demo/db/sqlMapConfig.xml";
			InputStream inputStream = Resources.getResourceAsStream(resource);
			sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
		} catch (Exception e) {
			System.out.println("예외발생 DBManaget :"+e.getMessage());
		}
	}
	
	// 객체 사용없이 메소드를 사용하기 위해 static 으로 사용합니다.
	public static List<BookVO> findAll(){
		List<BookVO> list = null;
		//sqlSessionFactory를 통해 sqlsession을 유지함
		SqlSession session = sqlSessionFactory.openSession();
		// bookMapper.xml에 namespace와 id에 지정한 값을 가운데 '.'을 넣고 사용함 
		list = session.selectList("Book.findAll");
		// session을 닫아줌
		session.close();
		return list;
	}
	
	public static BookVO findByBookied(int bookied) {
		BookVO b = null;
		SqlSession session = sqlSessionFactory.openSession();
		b = session.selectOne("Book.findByBookied", bookied);
		session.close();
		return b;
	}
	
	public static int insert(BookVO b) {
		int re = -1;
		//()안에 autocommit 'true' 빼먹으면 db에 반영이 안됩니다.
		SqlSession session = sqlSessionFactory.openSession(true);
		re = session.insert("Book.insert", b);
		session.close();
		return re;
	}
	
	public static int update(BookVO b) {
		int re = -1;
		
		SqlSession session = sqlSessionFactory.openSession(true);
		re = session.update("Book.update", b);
		session.close();
		return re;
	}
	
	public static int delete(int bookied) {
		int re = -1;
		SqlSession session = sqlSessionFactory.openSession(true);
		re = session.delete("Book.delete", bookied);
		session.close();
		return re;
	}
}

 

3. BookDAO (repository) 에도 추가, 수정, 삭제를 위한 메소드를 작성합니다.

 

package com.example.demo.dao;

import java.util.List;

import org.springframework.stereotype.Repository;

import com.example.demo.db.DBManager;
import com.example.demo.vo.BookVO;
@Repository
public class BookDAO {
	
	public static List<BookVO> findAll(){
		//mybatis에 저장되어있는 메소드를 바로 return 하면 됩니다.
		return DBManager.findAll();
	}
	
	public static BookVO findByBookied(int bookied) {
		return DBManager.findByBookied(bookied);
	}
	
	public static int insert(BookVO b) {
		return DBManager.insert(b);
	}
	
	public static int update(BookVO b) {
		return DBManager.update(b);
	}
	
	public static int delete(int bookied) {
		return DBManager.delete(bookied);
	}
}

 

 

4.  JSP 페이지 이동 및 상태유지, PostMapping을 위해 추가, 수정, 삭제 컨트롤러를 추가로 작성합니다. 

package com.example.demo.controller;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.ui.Model;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.ResponseBody;
import org.springframework.web.servlet.ModelAndView;

import com.example.demo.dao.BookDAO;
import com.example.demo.vo.BookVO;

@Controller
public class BookController {
	
	@Autowired
	private BookDAO dao;
	
	public void setDao(BookDAO dao) {
		this.dao = dao;
	}

	@RequestMapping("/listBook")
	@ResponseBody
	public ModelAndView list() {
		ModelAndView mav = new ModelAndView("listBook");
		mav.addObject("list",dao.findAll());
		return mav;
	}
	
	@GetMapping("/detailBook")
	public void detail(Model model, int bookied) {
		model.addAttribute("b",dao.findByBookied(bookied));
	}
	
	
	@GetMapping("/insertBook")
	public void insertForm () {
		
	}
	
	@PostMapping("/insertBook")
	public ModelAndView insertSumit(BookVO vo) {
		ModelAndView mav = new ModelAndView("redirect:/listBook");
		int re = dao.insert(vo);
		return mav;
	}
	@GetMapping("/updateBook")
	public void updateForm(Model model, int bookied) {
		model.addAttribute("b",dao.findByBookied(bookied));
	}
	
	@PostMapping("/updateBook")
	public ModelAndView updateSumit(BookVO vo) {
		ModelAndView mav = new ModelAndView("redirect:/listBook");
		int re = dao.update(vo);
		return mav;
	}
	
	@GetMapping("/deleteBook")
	public ModelAndView deleteBook(int bookied) {
		ModelAndView mav = new ModelAndView("redirect:/listBook");
		int re = dao.delete(bookied);
		if(re != 1) {
			mav.setViewName("error");
		}
		return mav;
	}
	
}

 

5. view를 위한 JSP 를 작성합니다.

 

- insert : 등록 페이지

<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>Insert title here</title>
</head>
<body>
	<h2>도서등록</h2>
	<hr>
	<form action="insertBook" method="post">
		도서번호 : <input type="number" name="bookied" value="${b.bookied }"><br>
		도서이름 : <input type="text" name="bookname"><br>
		출판사명 : <input type="text" name="publisher"><br>
		도서이름 : <input type="number" name="price"><br>
		<input type="submit" value="등록"> 
		<input type="reset" value="다시입력">
	</form>
	<a href="listBook">도서목록</a>
</body>
</html>

- selelct : 조회 목록 페이지

<%@ 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>Insert title here</title>
</head>
<body>
	<h2>도서목록</h2>
	<hr>
	<table border="1">
		<tr>
			<th>도서번호</th>	
			<th>도서이름</th>	
			
		</tr>
		<c:forEach var="b" items="${list }">
			<tr>
				<td>${b.bookied }</td>
				<td>
					<a href="detailBook?bookied=${b.bookied }">${b.bookname }</a>
				</td>			
			</tr>
		</c:forEach>
	</table>
		<a href="insertBook">도서등록</a>
</body>
</html>

- delete & detail : 삭제 및 상세 페이지

<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<script type="text/javascript" src="https://code.jquery.com/jquery-3.7.0.min.js"></script>
<script type="text/javascript">
	$(function () {
		$("#delete").click(function () {
			if(confirm("정말로 삭제하시겠습니까?")){
				location.href="detailBook?bookied=${b.bookied }";
			}else{
				return false;
			}
		})
		
	})
</script>
<title>Insert title here</title>
</head>
<body>
	<h2>상세조회</h2>
	<hr>
	도서번호 : ${b.bookied }<br>
	도서이름 : ${b.bookname }<br>
	출판사명 : ${b.publisher }<br>
	도서가격 : ${b.price }<br>
	
	<a href="updateBook?bookied=${b.bookied }">수정</a>
	<a href="deleteBook?bookied=${b.bookied }" id="delete">삭제</a>
</body>
</html>

- update : 수정 페이지

<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>Insert title here</title>
</head>
<body>
	<h2>도서수정</h2>
	<hr>
	<form action="updateBook" method="post">
		도서번호 : <input type="number" name="bookied" value="${b.bookied }"><br>
		도서이름 : <input type="text" name="bookname" value="${b.bookname }"><br>
		출판사명 : <input type="text" name="publisher" value="${b.publisher }"><br>
		도서이름 : <input type="number" name="price" value="${b.price }"><br>
		<input type="submit" value="수정"> 
		<input type="reset" value="다시입력">
	</form>
	<a href="listBook">도서목록</a>
</body>
</html>

 

위와 같은 코드로 마이바티스와  JSP 를 활용하여 추가, 수정, 삭제 기능을 수행할 수 있다.