12/15 65-3 [SPRING] dynamicsql

sql문을 동적으로 제어하기

와.... 주말에 진짜 오라클 다시 공부해야겠다.....

 

기억이 안나니 게시판 만드는 로직처럼 접근 해 보자

 

index.jsp에 먼저 등록하고 indexController에다가도 추가!

원래 로직작성은 서비스에서 하지만

이건 게시판도 아니고 하니

조직작성을 그냥 컨트롤러에 몰아넣음

원래는 떼어놓는게 맞다 !!

 

+++ 이거도 sql로 제어할 거라서

메퍼파일 만들거

마이바티스랑 연결 되어야 하기 때문에 

src/main.resoures >onememo.mybatis.configuration.xml에다가

<mapper resource="onememo/mybatis/mapper/dynamicsql.xml"/> 

추가함!!

 

 

1. 뷰

DynamicSql.jsp

뭘 할 것인지 먼저 봐

if태그 / choose~when~otherwise태그 / where태그 / trim태그  / set태그 foreach태그

	<h1>마이바티스 동적 SQL</h1>
		<p>${message}</p>
	</div>
	<h3>if 태그</h3>
	<ul class="list-unstyled">
		<li><a href="<c:url value="/Mybatis/If1.do?title=자바"/>">WHERE절
				일부에 사용 첫번째</a></li>
		<li><a
			href="<c:url value="/Mybatis/If2.do?title=자바&name=김길동&content=안드로이드"/>">WHERE절
				일부에 사용 두번째</a></li>
	</ul>
	<h3>choose~when~otherwise 태그</h3>
	<a
		href="<c:url value="/Mybatis/choose.do?title=자바&name=김길동&content=안드로이드"/>">WHERE절
		일부에 사용</a>
	<h3>where 태그</h3>
	<a
		href="<c:url value="/Mybatis/where.do?title=자바&name=김길동&content=안드로이드"/>">where태그로
		where절 구성</a>
	<h3>trim 태그</h3>
	<ul class="list-unstyled">
		<li><a
			href="<c:url value="/Mybatis/trim1.do?title=자바&name=김길동&content=안드로이드"/>">검색문</a></li>
		<li><a
			href="<c:url value="/Mybatis/trim2.do?no=12&title=자바&content=안드로이드"/>">수정문</a></li>
	</ul>
	<!-- update 하고자 하는 칼럼을 동적으로 포함시키기 위해 사용 -->
	<h3>set 태그</h3>
	<a href="<c:url value="/Mybatis/set.do?no=21&title=안녕&content=인사"/>">수정문</a>
	<h3>foreach 태그</h3>
	<a href="<c:url value="/Mybatis/foreach.do"/>">foreach태그</a>
	<h3>이메일 삭제</h3>
	<form method="post" action="<c:url value="/Mybatis/foreachExam.do"/>">
		<div class="form-check">
			<label class="form-check-label">
			<input type="checkbox"	class="form-check-input" value="1" name="email">메일 1
			</label>
		</div>
		<div class="form-check">
			<label class="form-check-label">
			<input type="checkbox"	class="form-check-input" value="3" name="email">메일 3
			</label>
		</div>
		<div class="form-check">
			<label class="form-check-label">
			<input type="checkbox"	class="form-check-input" value="4" name="email">메일 4
			</label>
		</div>
		<div class="form-check">
			<label class="form-check-label">
			<input type="checkbox"	class="form-check-input" value="5" name="email">메일 5
			</label>
		</div>
		<div class="form-check">
			<label class="form-check-label">
			<input type="checkbox"	class="form-check-input" value="6" name="email">메일 6
			</label>
		</div>
		<div class="form-check">
			<label class="form-check-label">
			<input type="checkbox"	class="form-check-input" value="8" name="email">메일 8
			</label>
		</div>
		<div class="form-check">
			<label class="form-check-label">
			<input type="checkbox"	class="form-check-input" value="24" name="email">메일 24
			</label>
		</div>
		<div class="form-check">
			<label class="form-check-label">
			<input type="checkbox"	class="form-check-input" value="30" name="email">메일 30
			</label>
		</div>
		
		<input type="submit" value="삭제" class="btn btn-danger"/>
	</form>

 

---

  [if태그]

 

2.컨트롤러(+서비스기능)

@Controller
@RequestMapping("/Mybatis")
public class DynamicSqlController {  //여기선 서비스 생략

	//생성자 인젝션으로 주입
	private DynamicSqlDAO dao;
	//@Autowired // 생성자가 하나일때는 생략 가능
	public DynamicSqlController(DynamicSqlDAO dao) {
		this.dao=dao;
	}
	
	@GetMapping("/If1.do")
	public String if1(@RequestParam Map map,Model model) {
		List list= dao.if1(map);
		model.addAttribute("message","검색된 총 글 수 :"+list.size());		
		//뷰정보 반환
		return "dynamicsql11/DynamicSql";		
	}
	
	@GetMapping("/If2.do")
	public String if2(@RequestParam Map map,Model model) {
		List list= dao.if2(map);
		model.addAttribute("message","검색된 총 글 수 :"+list.size());		
		//뷰정보 반환
		return "dynamicsql11/DynamicSql";		
	}

3.DAO

@Repository
public class DynamicSqlDAO {
	
	@Autowired
	private SqlSessionTemplate template;

	public List if1(Map map) {		
		return template.selectList("findWithTitleLike",map);
	}

	public List if2(Map map) {		
		return template.selectList("findWithLike",map);
	}
 }

+쿼리문

<select id="findWithTitleLike" parameterType="Map" resultType="memoDto">
    SELECT * FROM onememo
    WHERE 1=1 
    <if test="title !=null">
        AND title LIKE '%' || #{title} || '%'
    </if> 	
</select>

<select id="findWithLike" parameterType="Map" resultType="memoDto">
    SELECT o.*,name FROM onememo o JOIN member m ON m.id=o.id
    WHERE 1=1

    <if test="title !=null">
        AND title LIKE '%' || #{title} || '%'
    </if>
    <if test="name !=null">
        AND name LIKE '%' || #{name} || '%'
    </if>
    <if test="content !=null">
        AND content LIKE '%' || #{content} || '%'
    </if>
</select>

 

---

[choose]

뷰는 위에

 

2. 컨트롤러

@GetMapping("/choose.do")
public String choose(@RequestParam Map map,Model model) {
    List list= dao.choose(map);
    model.addAttribute("message","검색된 총 글 수 :"+list.size());		
    //뷰정보 반환
    return "dynamicsql11/DynamicSql";		
}

3.DAO

public List choose(Map map) {
    return template.selectList("findWithLikeChoose",map);
}

++쿼리문작성 

<select id="findWithLikeChoose" parameterType="Map" resultType="memoDto">
    SELECT o.*,name FROM onememo o JOIN member m ON m.id=o.id
    WHERE 1=1
    <choose>
        <when test="title !=null">
            AND title LIKE '%' || #{title} || '%'
        </when>
        <when test="name !=null">
            AND name LIKE '%' || #{name} || '%'
        </when>
        <otherwise>
            AND content LIKE '%' || #{content} || '%'
        </otherwise>
    </choose>
</select>

---

[where]

2.컨트롤러

where동적으로 추가시 where태그 권장

@GetMapping("/where.do")
public String where(@RequestParam Map map,Model model) {
    List list= dao.where(map);
    model.addAttribute("message","검색된 총 글 수 :"+list.size());	
    //뷰정보 반환
    return "dynamicsql11/DynamicSql";		
}

 

 

3.DAO

public List where(Map map) {		
    return template.selectList("findWithLikeWhere",map);
}

+쿼리문

<select id="findWithLikeWhere" parameterType="Map" resultType="memoDto">
    SELECT o.*,name FROM onememo o JOIN member m ON m.id=o.id	
    <!-- 위 구문에서 "where 1=1"을 "where"로 변경 시 조건에 따라 에러 발생 -->
    <!-- 아래 where 엘리먼트는 JSTL에 의해 컨텐츠가 리턴되면 단순히 "WHERE"만을 추가한다. 
        게다가 컨텐츠가 "AND"나 "OR"로 시작한다면 그 "AND"나 "OR"를 지워버린다. -->
    <where>
        <if test="title !=null">
            AND title LIKE '%' || #{title} || '%'
        </if>
        <if test="name !=null">
            AND name LIKE '%' || #{name} || '%'
        </if>
        <if test="content !=null">
            AND content LIKE '%' || #{content} || '%'
        </if>
    </where>
</select>

 

---

2.[trim1,2]

 

2.컨트롤러

@GetMapping("/trim1.do")
public String trim1(@RequestParam Map map,Model model) {
    List list= dao.trim1(map);
    model.addAttribute("message","검색된 총 글 수 :"+list.size());	
    //뷰정보 반환
    return "dynamicsql11/DynamicSql";		
}

@GetMapping("/trim2.do")
public String trim2(@RequestParam Map map,Model model) {
    if(!(map.get("title")==null && map.get("content")==null)) {
        int affected= dao.trim2(map);
        model.addAttribute("message","수정된 총 글 수 :"+affected);				
    }
    else {
        model.addAttribute("message","제목이나 내용 둘 중 하나는 전송!!");	
    }		
    //뷰정보 반환
    return "dynamicsql11/DynamicSql";		
}

 

3.DAO

public List trim1(Map map) {
    return template.selectList("findWithLikeTrim",map);
}


업데이트니까 int를 반환!
public int trim2(Map map) {
    return template.update("updateWithLikeTrim",map);
}

++쿼리문

prefix : <trim> 문 안에 쿼리 가장 앞에 붙는 접두어
suffix : <trim> 문 안에 쿼리 가장 뒤에 붙는 접미어
prefixOverrides : <trim> 문 안에 쿼리 가장 앞에 해당하는 문자들이 있으면 자동으로 지워준다.
suffixOverrides : <trim> 문 안에 쿼리 가장 뒤에 해당하는 문자들이 있으면 자동으로 지워준다.

 

<select id="findWithLikeTrim" parameterType="Map" resultType="memoDto">
    SELECT o.*,name FROM onememo o JOIN member m ON m.id=o.id

    <trim prefix="WHERE" suffix="ORDER BY no DESC" prefixOverrides="AND">
        <if test="title !=null">
            AND title LIKE '%' || #{title} || '%'
        </if>
        <if test="name !=null">
            AND name LIKE '%' || #{name} || '%'
        </if>
        <if test="content !=null">
            AND content LIKE '%' || #{content} || '%'
        </if>		
    </trim>
    </select>
	<!--  trim문은 수정시에 쓰자 -->
	<update id="updateWithLikeTrim" parameterType="Map">
		UPDATE onememo
		<!-- title이나 content가 전달되면 수정.단,둘 중의 하나는 전달되야 한다
		     자바코드에서 제어
		-->		
		<trim prefix="SET" suffixOverrides=",">
			<if test="title !=null">
				title=#{title},
			</if>
			<if test="content !=null">
				content=#{content},	
			</if>	
		</trim>
		WHERE no=#{no}
	</update>

 

묶어버리자...

잠을 자야해...

[set] & [foreach]

 update 하고자 하는 칼럼을 동적으로 포함시키기 위해 사용

2.컨트롤러

@GetMapping("/set.do")
public String set(@RequestParam Map map,Model model) {
    if(!(map.get("title")==null && map.get("content")==null)) {
        int affected= dao.set(map);
        model.addAttribute("message","수정된 총 글 수 :"+affected);				
    }
    else {
        model.addAttribute("message","제목이나 내용 둘 중 하나는 전송!!");	
    }		
    //뷰정보 반환
    return "dynamicsql11/DynamicSql";		
}

@GetMapping("/foreach.do")
public String foreach(Model model) {

    //리스트인 경우
    List list = Arrays.asList(1,2,3,4,5,6,7,8,9,10);
    List result=dao.foreach(list);

    //맵 인 경우
    Map map = new HashMap();
    map.put("acticleNos", Arrays.asList(1,2,3,4,5,6,7,8,9,10));
    List result= dao.foreach(map);
    model.addAttribute("message","검색된 총 글 수:"+result.size());	 
    //뷰정보 반환
    return "dynamicsql11/DynamicSql";			
}

2.DAO

public int set(Map map) {
    return template.update("updateWithSet",map);
    }

리스트일 때
public List foreach(List list) {	
	return template.selectList("findForeach",list);
}
맵일때
public List foreach(Map map) {
    return template.selectList("findForeach",map);
}

++쿼리문

 

**collection : List 혹은 배열 형태만 가능
     parameterType이 List인 경우 "list" 혹은 "collection"
    만약 List<DTO계열>이면 item속성에 지정한 변수명.DTO멤버 속성명 으로 꺼낸다 
     예]<foreach item="item" ~
                 #{item.id}
            </foreach>
  parameterType이  배열인 경우 "array"
  parameterType이 Map인 경우 key값(단,value는 List혹은 배열) 지정
            
item :컬렉션에서 꺼내온 객체 저장 변수
open : 시작 문자열
close : 종료 문자열
separator : 반복 되는 사이에 삽입할 문자열
index : 0부터 순차적으로 증가하는 인덱스번호

 

 수정 시는 trim대신 set태그를 쓰자 
<update id="updateWithSet" parameterType="Map">
    UPDATE onememo	
    <set>
        <if test="title !=null">
            title=#{title},
        </if>
        <if test="content !=null">
            content=#{content},	
        </if>	
    </set>
    WHERE no=#{no}	
</update>


 List인 경우 
<select id="findForeach" parameterType="List" resultType="memoDto">
    SELECT * FROM onememo
    WHERE no IN
    <foreach collection="list" item="item" open="(" close=")" separator=",">
        #{item}		
    </foreach>
</select>

 Map인 경우 
<select id="findForeach" parameterType="Map" resultType="memoDto">
    SELECT * FROM onememo
    WHERE no IN
    <foreach collection="acticleNos" item="item" open="(" close=")" separator=",">
        #{item}		
    </foreach>
</select>

 

[delet] +foreachExam

2.컨트롤러

@PostMapping("/foreachExam.do")
public String foreachExam(@RequestParam int[] email, Model model) {
    int affected =dao.foreachExam(email);
    model.addAttribute("message","삭제된 총 글 수 :"+affected);		
    return "dynamicsql11/DynamicSql";
}

 

2.DAO

delete 하는거라서 댓글 (자식)이 딸려있는 글(부모)는 삭제가 안됨

트랜젝션 처리를 해야 함

트랜젝션 처리를 위한 주입받기
@Autowired
private TransactionTemplate transactionTemplate;

public int foreachExam(int[] email) {
    int deleteCount = transactionTemplate.execute(status->{
        int affected=0;
        try {
            affected=template.delete("deleteEmail",email);
        }
        catch(Exception e) {
            affected=-1;//삭제 시 에러난 경우 -1반환
        }
        return affected;
    });
    return deleteCount;
}

+쿼리문

<delete id="deleteEmail" parameterType="int[]">
    DELETE onememo
    WHERE no IN 
    <foreach collection="array" item="item" open="(" close=")" separator=",">
        #{item}
    </foreach>
</delete>

꿱