60일차//Mybatis(1) insert, delete, update, select

 

MyBatisMapper.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">
  
<!-- Query문 -->

<mapper namespace="mybatis.MyBatisMapper">

<!-- 
	<insert, delete, select, update		id="외부에서 호출할 문자열"
		parameterType="object"
		resultType="object">
		
		쿼리	#{ 외부로부터 들어오는 값 } - core tag
			${ 숫자 }
			
	</insert, delete, select, update>	
 -->

<insert id="add" parameterType="dto.MemberDto">
	INSERT INTO MEMBERDTO(ID, PWD, EMAIL)
	VALUES( #{ id }, #{ pwd }, #{ email } )
</insert> 

<delete id="remove" parameterType="java.lang.String">
	DELETE FROM MEMBERDTO
	WHERE ID=#{ id }
</delete>



<update id="up" parameterType="dto.MemberDto">
	UPDATE MEMBERDTO SET PWD="ddd" WHERE ID=#{ id }
</update>




<!-- select one -->
<select id="find" parameterType="java.lang.String"
	resultType="dto.MemberDto">
        SELECT * FROM MEMBERDTO
        WHERE ID=#{ id } <!-- #{} == val -->
</select> 
<!-- select list -->
<select id="allData" resultType="dto.MemberDto">
 SELECT * FROM MEMBERDTO
 
</select>


<!-- bbs search  -->
<!-- <select id="bbsSearch" parameterType="dto.BbsParam"
	resultType="dto.BbsDto">
	SELECT*
	FROM BBS
	WHERE 1=1
	<if test="s_category== 'title'">
		AND TITLE LIKE '%'||#{keyword}||'%'
	</if>
	<if test="s_category== 'content'">
		AND CONTENT LIKE '%'||#{keyword}||'%'
	</if>
	<if test="s_category== 'id'">
		AND ID=#{keyword}
	</if>
	
</select> -->


<select id="bbsSearch" parameterType="dto.BbsParam"
	resultType="dto.BbsDto">
	SELECT*
	FROM BBS
	WHERE 1=1
	<choose>
	<when test="s_category== 'title'">
		AND TITLE LIKE '%'||#{keyword}||'%'
	</when>
	<when test="s_category== 'content'">
		AND CONTENT LIKE '%'||#{keyword}||'%'
	</when>
	<when test="s_category== 'id'">
		AND ID=#{keyword}
	</when>
	
	<!-- <otherwise>
	else
	</otherwise>
	 -->
	</choose>
</select>





</mapper>

MainClass.java

package main;

import java.io.InputStream;
import java.util.Iterator;
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 dto.BbsDto;
import dto.BbsParam;
import dto.MemberDto;

public class MainClass {
	public static void main(String[] args) throws Exception {
		
		// mybatis 설정파일을 읽어들인다
		InputStream is = Resources.getResourceAsStream("mybatis/config.xml");
		
		// SqlSessionFactory 객체를 취득
		SqlSessionFactory factory = new SqlSessionFactoryBuilder().build(is);
		
		// SqlSession 객체를 취득 
		SqlSession session = factory.openSession();	// session <- 실세
		
		
		/*
		MemberDto dto = new MemberDto("ccc", "111", "a1@a");
		
		//MemberDto dto = new MemberDto("aaa", "111", "a1@a");
		
		int n = session.insert("add", dto);
		if(n > 0) {
			session.commit();
			System.out.println("추가 성공!");
		}else {
			session.rollback();
			System.out.println("추가 실패");
		}
		*/
		
		//MemberDto ddto = new MemberDto("aaa", "111", "a1@a");
		
		/*
		String id ="aaa";
		int d = session.delete("remove","aaa");
		
		
		
		
		if(d > 0) {
			session.commit();
			System.out.println("삭제 성공!");
		}else {
			session.rollback();
			System.out.println("삭제 실패");
		}
		*/
		
		
		/*
		MemberDto sdto = new MemberDto("aaa", "777", "a1@a");
		int s = session.update("update", sdto);
		if(s > 0) {
			session.commit();
			System.out.println("수정 성공!");
		}else {
			session.rollback();
			System.out.println("수정 실패");
		}
		*/
		
		/*
		//Select
		String id="aaa";
		MemberDto dto = session.selectOne("find", id);
		System.out.println(dto.toString());
		*/
		
		
		
		//select
		/*
		List<MemberDto> list = session.selectList("allData");
		for (MemberDto m: list) {
			System.out.println(m.toString());
		
			*/
			
			
		//bbs select
		BbsParam param = new BbsParam("title","주식이야기");
		List<BbsDto> list = session.selectList("bbsSearch", param);
		for (BbsDto bbs: list) {
			System.out.println(bbs.toString());
		}	
			
			
			
		
		

	}
}

BbsParam.java

package dto;

import java.io.Serializable;

public class BbsParam implements Serializable{
	private String s_category;
	private String keyword;
	
	public BbsParam() {
		
	}

	public BbsParam(String s_category, String keyword) {
		super();
		this.s_category = s_category;
		this.keyword = keyword;
	}

	public String getS_category() {
		return s_category;
	}

	public void setS_category(String s_category) {
		this.s_category = s_category;
	}

	public String getKeyword() {
		return keyword;
	}

	public void setKeyword(String keyword) {
		this.keyword = keyword;
	}
	
	
}