[Spring] 다중 삽입하기 - 객체 (+ checkbox, select)

INSERT ALL

동일한 하나의 서브 쿼리 결과 값을

두 개 이상의 테이블에 각각 INSERT 할 때 사용

해당 문은 다중 삽입시 사용함

INSERT ALL
INTO 테이블명1 VALUES (컬럼명, 컬럼명 ,컬럼명)
INTO 테이블명2 VALUES (컬럼명, 컬럼명 ,컬럼명)
서브쿼리;

 

해당 작업에는 이와같이 carsVoList에 담아 foreach문을 통해 데이터를 여러 개 삽입하였는데

이때 insert문임에도 불구하고 update로 작성하는 것이 약속이다

	<update id="insertCars"  parameterType="java.util.List">
	    <foreach collection="list" item="item" open="INSERT ALL" close="SELECT * FROM DUAL" separator=" " index="idx">
	        INTO cars(user_id, car) VALUES (#{item.userId}, #{item.car})
	    </foreach>
	</update>

 

SQL

VO객체에 한 List<Vo객체>가 포함될 경우 list에 있는 정보들 또한 삽입하는 방법을 기술한다.

이를 연습하기위해 테이블 두개를 추가하도록 한다

create table tbl_user(
    user_id varchar2(10),
    password varchar2(100),
    name varchar2(90),
    email varchar2(100),
    bir varchar2(50),
    zipCode varchar2(10),
    addr varchar2(300),
    addrdet varchar2(1000),
     constraint pk_user primary key(user_id)
)

create table card(
    card_no number,
    cart_ym varchar2(10),
    user_id varchar2(10) not null,
    constraint pk_card primary key(card_no),
    constraint fk_card foreign key(user_id)
        References TBL_USER(USER_ID)
)

create table tbl_hobby(
    user_id varchar2(10) not null,
    hobby varchar2(20),
     constraint pk_hobby_user primary key(user_id, hobby),
     constraint fk_hobby_user foreign key(user_id)
        References TBL_USER(USER_ID)
)

create table cars(
    user_id varchar2(10) not null,
    car varchar2(50),
     constraint pk_car_user primary key(user_id, car),
     constraint fk_car_user foreign key(user_id)
        References TBL_USER(USER_ID)
)

해당 List에 있는 cardVO List들은 반복문 for을 통해서 하나씩 insert작업을 수행한다

이러한 과정은 ServiceImpl에서 이루어진다

JSP

cardForm.jsp

<%@ page language="java" contentType="text/html; charset=UTF-8"%>
<%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core"%>
<%@ taglib prefix="fmt" uri="http://java.sun.com/jsp/jstl/fmt" %>
<script type="text/javascript" src="/resources/js/jquery.min.js"></script>
<script src="//t1.daumcdn.net/mapjsapi/bundle/postcode/prod/postcode.v2.js"></script>
<div class="row">
<div class="col-md-12 card">
	<div class="card-header">
		<h3>유저 카드 등록</h3>
	</div>
	<div class="card-body row" style="justify-content: space-between;">
			 <div class="card-body p-0">
			 <form method="post" action="/prod/cardFormPost">
				<p><input value="a001" placeholder="아이디" type="text" name="userId" id="userId"></p>
				<p><input value="java" placeholder="비밀번호" type="password" name="password" id="password"></p>
				<p><input value="오리" placeholder="이름" type="text" name="name" id="name"></p>
				<p><input value="duckduck@duck.com" placeholder="이메일" type="text" name="email" id="email"></p>
				<p><input value="2020-06-30" placeholder="생년월일" type="date" name="bir" id="bir "></p>
				<p><input value="12345" placeholder="우편번호" type="text" name="zipcode" id="zipcode">
					<input type="button" value="우편번호 검색" onclick="openHomeSearch()">
				</p>
				<p><input value="대전" placeholder="주소" type="text" name="addr" id="addr"></p>
				<p><input value="건물" placeholder="상세주소" type="text" name="addrdet" id="addrdet"></p>
              
              	<hr/>
              	<c:forEach var="num" items="${cardList}" varStatus="st">
              	<p>
	              	<input value="${num}" type="text" name="cardVoList[${st.index}].cardNo" id="cardNo${st.index}" placeholder="카드 번호">
	              	<input value="251008" type="text" name="cardVoList[${st.index}].cardYm" id="cardYm${st.index}" placeholder="유효 연월">
              	</p>
              	</c:forEach>
              	<hr/>
              	<div class="row">
					<p style="place-self: center; margin: 5%;">취미(hobby)</p>
					
					<div style="display: flex; flex-direction: column;">
						<p><input type="checkbox" id="hobby1" name="hobby" value="sport" multiple> <label for="hobby1">Sports</label></p>
						<p><input type="checkbox" id="hobby2" name="hobby" value="music" multiple> <label for="hobby2">Music </label></p>
						<p><input type="checkbox" id="hobby3" name="hobby" value="movie" multiple> <label for="hobby3">Movie </label></p>
						 <p><input type="checkbox" id="hobby4" name="hobby" value="game" multiple> <label for="hobby4">Game </label></p>
					</div>
				</div>
              	<div class="row">
					<p style="place-self: center; margin: 5%;">취미(hobbyStr)</p>
					<div style="display: flex; flex-direction: column;">
						<p><input type="checkbox" id="hobbyStr1" name="hobbyStr" value="sport" multiple> <label for="hobbyStr1">Sports</label></p>
						<p><input type="checkbox" id="hobbyStr2" name="hobbyStr" value="music" multiple> <label for="hobbyStr2">Music </label></p>
						<p><input type="checkbox" id="hobbyStr3" name="hobbyStr" value="movie" multiple> <label for="hobbyStr3">Movie </label></p>
						 <p><input type="checkbox" id="hobbyStr4" name="hobbyStr" value="game" multiple> <label for="hobbyStr4">Game </label></p>
					</div>
				</div>
              	<div class="row">
					<p style="place-self: center; margin: 5%;">보유 자동차</p>
					<div style="display: flex; flex-direction: column;">
						<select name="cars" multiple>
						<optgroup label="현대">
							<option value="grandeur">그랜저</option>
							<option value="GV80">제네시스 GV80</option>
							<option value="DN8">쏘나타 디 엣지</option>
						</optgroup>
							<option value="K9">K9</option>
							<option value="soul">쏘울 부스터</option>
						</optgroup>
						<optgroup label="KG모빌리티(쌍용)">
							<option value="G4">렉스턴 G4</option>
							<option value="CW700">뉴체어맨 W</option>
						</optgroup>
						<optgroup label="람보르기니">
							<option value="LP640">우라칸 LP640</option>
						</optgroup>
						</select>
					</div>
				</div>
              	<input type="submit" value="저장">
			 </form>
              </div> 
              <!-- /.card-body -->
	</div>
</div>
</div>
<script>
function openHomeSearch(){
	new daum.Postcode({
	    oncomplete: function(data) {
	    	// json 형태로 데이터가 전달
	    	console.log(data);
	    	$('#zipcode').val(data.zonecode);
	    	$('#addr').val(data.address);
	    	$('#addrdet').val(data.buildingName);
	    	$('#addrdet').focus();
	    }
	}).open();
}
</script>

JAVA

TblUserVO.java

package kr.or.ddit.vo;

import java.util.List;

import lombok.Data;

// 자바 빈 클래스
@Data
public class TblUserVO {
	private String userId;
	private String password;
	private String name;
	private String email;
	private String bir;
	private String zipcode;
	private String addr;
	private String addrdet;
	private String[] hobby; // hobby = [music, game]
	private String hobbyStr; // hobby = music,game
	private String[] cars;
	
	private List<CardVO> cardVoList;
	
	private List<TblHobbyVO> hobbyVoList;

	private List<CarsVO> carsVoList;
}

CardVO.java

package kr.or.ddit.vo;

import lombok.Data;

@Data
public class CardVO {
	private int cardNo;
	private String cardYm;
	private String userId;
}

HobbyVO.java

package kr.or.ddit.vo;

import lombok.Data;

@Data
public class TblHobbyVO {
	private String hobby;
	private String userId;
}

CarsVO.java

package kr.or.ddit.vo;

import lombok.Data;

@Data
public class CarsVO {
	private String car;
	private String userId;
}

ProdController.java

package kr.or.ddit.controller;

import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Random;

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.ModelAttribute;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.RequestBody;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.ResponseBody;

import kr.or.ddit.service.ProdService;
import kr.or.ddit.util.ArticlePage;
import kr.or.ddit.vo.MemberVO;
import kr.or.ddit.vo.ProdVO;
import kr.or.ddit.vo.TblUserVO;
import lombok.extern.slf4j.Slf4j;

@Slf4j
@RequestMapping("/prod")
@Controller
public class ProdController {
	
	@Autowired
	ProdService service;
	
	@ModelAttribute
	public void prodInit(Model model) {
		model.addAttribute("title", "상품 정보");
	}
	
	@GetMapping("/list")
	public String list(Model model, @RequestParam(value="currentPage", required = false, defaultValue = "1") int currentPage) {
		Map<String, Object> map = new HashMap<>();
		int size = 10;
		int total = this.service.getTotal();
		map.put("currentPage", currentPage);
		map.put("size", size);
		List<ProdVO> list = this.service.list(map);
		map.clear();
//		model.addAttribute("data", list);
		model.addAttribute("data", new ArticlePage<ProdVO>(total, currentPage, size, list, map));
		return "prod/list";
	}
	
	@ResponseBody
	@PostMapping("/listAjax")
	public List<ProdVO> listAjax(@RequestBody ProdVO vo){
		List<ProdVO> list = this.service.cartlist(vo);
		return list;
	}
	
	@ResponseBody
	@PostMapping("/memInfo")
	public MemberVO memDetail(@RequestBody MemberVO vo) {
		vo = this.service.memDetail(vo);
		SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
		String datestr = sdf.format(vo.getMemBir());
		vo.setMemBirStr(datestr);
		return vo;
	}
	
	@GetMapping("/cart")
	public String cart(Model model) {
		return "prod/cart";
	}
	
	@ResponseBody
	@GetMapping("/prodlist")
	public ArticlePage<ProdVO> prodList(Model model, @RequestParam(value="currentPage", required = false, defaultValue = "1") int currentPage) {
		Map<String, Object> map = new HashMap<>();
		int size = 10;
		int total = this.service.getTotal();
		map.put("currentPage", currentPage);
		map.put("size", size);
		List<ProdVO> list = this.service.list(map);
		map.clear();
		ArticlePage<ProdVO> art = new ArticlePage<ProdVO>(total, currentPage, size, list, map);
		return art;
	}
	
	@GetMapping("/cardForm")
	public String cardForm(Model model) {
		// 카드번호 자동 생성
		List<String> cardRndNo = new ArrayList<String>();
		for(int i=0; i<3; i++) {
			Random rand = new Random();
			cardRndNo.add(""+rand.nextInt(1000000000));
		}
		model.addAttribute("cardList", cardRndNo);
		return "prod/cardForm";
	}
	
	@ResponseBody
	@PostMapping("/cardFormPost")
	public TblUserVO cardFormPost (TblUserVO vo) {
		log.info("vo "+vo);
		
		int result = this.service.cardFormPost(vo);
		log.info("result "+result);
		
		return vo;
	}
}

ProdService.java

package kr.or.ddit.service;

import java.util.List;
import java.util.Map;

import kr.or.ddit.vo.MemberVO;
import kr.or.ddit.vo.ProdVO;
import kr.or.ddit.vo.TblUserVO;

public interface ProdService{
	public int cardFormPost(TblUserVO vo);

}

ProdServiceImpl.java

package kr.or.ddit.service.impl;

import java.util.ArrayList;
import java.util.List;
import java.util.Map;

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

import kr.or.ddit.dao.ProdDao;
import kr.or.ddit.service.ProdService;
import kr.or.ddit.vo.CardVO;
import kr.or.ddit.vo.CarsVO;
import kr.or.ddit.vo.MemberVO;
import kr.or.ddit.vo.ProdVO;
import kr.or.ddit.vo.TblHobbyVO;
import kr.or.ddit.vo.TblUserVO;

@Service
public class ProdServiceImpl implements ProdService{

	@Autowired
	ProdDao dao;
	
	@Override
	public List<ProdVO> list(Map<String,Object> map) {
		return this.dao.list(map);
	}

	@Override
	public int getTotal() {
		return this.dao.getTotal();
	}

	@Override
	public List<ProdVO> cartlist(ProdVO vo) {
		return this.dao.cartlist(vo);
	}

	@Override
	public MemberVO memDetail(MemberVO vo) {
		return this.dao.memDetail(vo);
	}

	@Override
	public int cardFormPost(TblUserVO vo) {
//		1) tbl_user 테이블 insert
		int result = this.dao.insertTblUser(vo);
		
//		2) card 테이블에 insert
		List<CardVO> cardvo = vo.getCardVoList();
		String userId = vo.getUserId();
		for(CardVO v : cardvo) {
			v.setUserId(userId);
			result+= this.dao.insertCard(v);
		}
//		3) hobby에 insert
		List<TblHobbyVO> hobbyvo = new ArrayList<TblHobbyVO>();
		String[] hobby = vo.getHobby();	// hobby 배열을 사용
//		String[] hobby = vo.getHobbyStr().split(","); // hobbyStr을 활용
		if(hobby!=null) {
			for(String h: hobby) {
				TblHobbyVO hvo = new TblHobbyVO();
				hvo.setUserId(userId);
				hvo.setHobby(h);
				hobbyvo.add(hvo);
				result+= this.dao.insertHobby(hvo);
			}
		}
//		4) cars에 insert
		List<CarsVO> carsVoList = new ArrayList<CarsVO>();
		String[] cars = vo.getCars();
		for(String car: cars) {
			CarsVO cvo = new CarsVO();
			cvo.setUserId(userId);
			cvo.setCar(car);
			carsVoList.add(cvo);
		}
		result+=this.dao.insertCars(carsVoList);
		return result;
	}

}

ProdDao.java

package kr.or.ddit.dao;

import java.util.List;
import java.util.Map;

import org.mybatis.spring.SqlSessionTemplate;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Repository;

import kr.or.ddit.vo.CardVO;
import kr.or.ddit.vo.CarsVO;
import kr.or.ddit.vo.MemberVO;
import kr.or.ddit.vo.ProdVO;
import kr.or.ddit.vo.TblHobbyVO;
import kr.or.ddit.vo.TblUserVO;

@Repository
public class ProdDao {

	@Autowired
	SqlSessionTemplate sst;
	
	public List<ProdVO> list(Map<String, Object> map) {
		return this.sst.selectList("prod.prodList", map);
	}

	public int getTotal() {
		return this.sst.selectOne("prod.prodListTotal");
	}

	public List<ProdVO> cartlist(ProdVO vo) {
		return this.sst.selectList("prod.cartlist", vo);
	}

	public MemberVO memDetail(MemberVO vo) {
		return this.sst.selectOne("prod.memDetail",vo);
	}

	public int insertTblUser(TblUserVO vo) {
		return this.sst.insert("prod.insertTblUser", vo);
	}

	public int insertCard(CardVO v) {
		return this.sst.insert("prod.insertCard", v);
	}

	public int insertHobby(TblHobbyVO hvo) {
		return this.sst.insert("prod.insertHobby", hvo);
	}
	
	public int insertCars(List<CarsVO> carsVoList) {
		return this.sst.insert("prod.insertCars", carsVoList);
	}

}

Mybatis

TypeAlias

<?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>
   <!-- 
   [마이바티스] 스프링에서 "_"를 사용한 컬럼명을 사용 시(BOOK 테이블의 BOOK_ID)
   카멜케이스로 읽어줌(bookId)
   ex) 테이블 컬러명이 member_id인 경우 jsp화면단에서 이 값을 사용 시 memberId로 사용
   -->
   <settings>
      <setting name="mapUnderscoreToCamelCase" value="true"/>
   </settings>
   <!-- 자주 사용하는 타입의 별칭을 세팅 -->
   <typeAliases>
       <typeAlias type="kr.or.ddit.vo.TblUserVO" alias="tblUserVo" /> 
       <typeAlias type="kr.or.ddit.vo.CardVO" alias="cardVo" /> 
       <typeAlias type="kr.or.ddit.vo.TblHobbyVO" alias="tblHobbyVo" /> 
       <typeAlias type="kr.or.ddit.vo.CarsVO" alias="carsVo" /> 
   </typeAliases>
</configuration>

prod_SQL.xml

<?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="prod">

	<insert id="insertTblUser" parameterType="tblUserVo">
		insert into tbl_user(USER_ID,PASSWORD,NAME,EMAIL,BIR,ZIPCODE,ADDR,ADDRDET)
		values(#{userId},#{password},#{name},#{email},#{bir},#{zipcode},#{addr},#{addrdet})
	</insert>
	
	<insert id="insertCard" parameterType="cardVo">
		insert into card(CARD_NO,CARD_YM,USER_ID)
		values(#{cardNo},#{cardYm},#{userId})
	</insert>
	
	<insert id="insertHobby" parameterType="tblHobbyVo">
		insert into tbl_hobby(user_id, hobby)
		values(#{userId}, #{hobby})	
	</insert>
		
<!-- 	<insert id="insertCars" parameterType="carsVo"> -->
<!-- 	    INSERT ALL -->
<!-- 	    <foreach collection="list" item="item"> -->
<!-- 	        INTO cars(user_id, car) VALUES (#{item.userId}, #{item.car}) -->
<!-- 	    </foreach> -->
<!-- 	    SELECT * FROM dual -->
<!-- 	</insert> -->
	<update id="insertCars"  parameterType="java.util.List">
	    <foreach collection="list" item="item" open="INSERT ALL" close="SELECT * FROM DUAL" separator=" " index="idx">
	        INTO cars(user_id, car) VALUES (#{item.userId}, #{item.car})
	    </foreach>
	</update>	
	
</mapper>