MyBatis

<오라클>

Create  table users(
 ID  varchar2(8)  primary  key,
PASSWORD  varchar2(8),
NAME    varchar2(20),
ROLE  varchar2(5) );

INSERT  INTO  users VALUES('TEST', 'TEST123', '관리자', 'Admin');
INSERT  INTO  users VALUES('user1', 'user1', '홍길동', 'User');
select *  from  users ;
--------------------------------------------------------------------------------------------------

create  table  board (
SEQ number(5)   primary  key,
TITLE   varchar2(200),
WRITER   varchar2(20),
CONTENT   varchar2(2000),
REGDATE  DATE  default  '2022-08-29'  ,
CNT  number(5)  default  0
);
insert  into board (seq, title, writer, content) 
values(1, '가입인사', '관리자', '잘 부탁드립니다....');
insert  into board (seq, title, writer, content)
values(2, '시헙중입니다. ', '관리자', '스프링 시험중입니다...!!!');
​

=============================================================

create  table  boardPsd (
SEQ number(5)   primary  key,
TITLE   varchar2(200),
WRITER   varchar2(20),
CONTENT   varchar2(2000),
REGDATE  DATE  default  '2022-08-29'  ,
CNT  number(5)  default  0,
updatefile nvarchar2(50)
);

BoardWeb2.zip

0.08MB

 

교재 509p

 

1. Mybatis 다운받기.

- pom.xml

		 	<!-- MyBatis -->
		 		<dependency>
			<groupId>org.mybatis</groupId>
			<artifactId>mybatis</artifactId>
			<version>3.3.1</version>
		</dependency>	
		
		 	<!-- MyBatis Spring -->
		 	 		<dependency>
			<groupId>org.mybatis</groupId>
			<artifactId>mybatis-spring</artifactId>
			<version>1.2.4</version>
		</dependency>

2. mappings 폴더 만들기

2-1. board-mapping.xml

2-2. sql-map-config.xml

<?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>
	<!-- Alias 설정 -->
	<typeAliases>
		<typeAlias alias="board"
			type="com.springbook.biz.board.BoardVo" />
		<typeAlias alias="user"
			type="com.springbook.biz.login.UserVo" />
		<typeAlias alias="psd" type="com.springbook.biz.psd.PsdVo" />
	</typeAliases>

	<mappers>
		<mapper resource="mappings/board-mapping.xml" />
		<mapper resource="mappings/user-mapping.xml" />
		<mapper resource="mappings/psd-mapping.xml" />
	</mappers>

</configuration>

 

3. applicationContext.xml

Spring JDBC를 쓰든지 MyBatis를 쓰든지

<bean id="sessionFactory" class="org.mybatis.spring.SqlSessionFactoryBean">
 <property name="dataSource" ref="dataSource"/>
 <property name="configLocation" value="classpath:sql-map-config.xml" />
</bean>

(bean안에는 id와 class만 쓸 수 있음)

 

4. Dao 클래스 구현 - 방법2

추가하기

<bean class="org.mybatis.spring.SqlSessionTemplate">
<constructor-arg ref="sqlSession"></constructor-arg>
</bean>

(방법1과 다르게 이름을 sqlSession으로 바꿔줬음)

 

 

5. 파일 추가하기

---

Board 

BoardDaoImpl 그대로 복사해서 BoardDaoMyBatis 생성

-1. 맨 밑 단락 삭제하고

-2. Sql문 잘라서 board-mapping.xml으로 붙여넣기

그러면 일단은 

이렇게 되고

board-mapping.xml 에는 sql문 주석처리해준 다음에 이걸 MyBatis로 이제 풀면 된다!

 

* BoardVo에 String으로 날짜 잡아주기

 

6. board-mapping.xml (교재 481p)

parameterType은 생략이 가능해서 교재에는 안나와있음

<?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="BoardDAO">
<insert id="insertBoard" parameterType="board" >
 insert into  board (seq, title, writer, content, regdate) 
 values ((select max(nvl(seq,0))+1 from board ), #{title}, #{writer}, #{content}, #{regDateStr})
</insert>

<update id="updateBoard"  parameterType="board" >
  update board set  title=#{title}, writer=#{writer}, content=#{content} where seq=#{seq}
</update>

<update id="cnt"  parameterType="board" >
  update board set  cnt=cnt+1 where seq=#{seq} 
</update>

<delete  id="deleteBoard"  parameterType="board" >
 delete  from board  where seq=#{seq}
</delete>

<select  id="getBoard"  parameterType="board" resultType="board" >
  select  *  from  board  where seq=#{seq} 
</select>

<select id="getBoardList"  resultType="board"  >
   select * from board  order by seq  desc
</select>

<select id="GETBOARDTITLE"  resultType="board"  >
  select * from board where title like #{searchKeyword} order by seq  desc
</select>

<select id="GETBOARDWRITER"  resultType="board"  >
   select * from board where writer like #{searchKeyword} order by seq  desc
</select>

</mapper>

 

* BoardDaoImpl에 BoardDao 못들어오게 주석처리하기

 

7. BoardDaoMyBatis (교재 516p)

package com.springbook.biz.board.dao;

import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.List;

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

import com.springbook.biz.board.BoardVo;

@Repository
public class BoardDaoMybatis implements BoardDao {

	@Autowired
    private SqlSessionTemplate  jdbcTemplate;
		
	
	@Override
	public void insert(BoardVo vo) {	
		
		Date date=new Date();
		SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy-MM-dd"); 
		String strNowDate = simpleDateFormat.format(date);
		vo.setRegDateStr(strNowDate);
		jdbcTemplate.insert("BoardDAO.insertBoard",vo);
	}

	@Override
	public void update(BoardVo vo) {
		jdbcTemplate.update("BoardDAO.updateBoard", vo);		
	}

	@Override
	public void delete(BoardVo vo) {		
		jdbcTemplate.delete("BoardDAO.deleteBoard", vo);
	}

	@Override
	public BoardVo getBoard(BoardVo vo) {		
		return (BoardVo )jdbcTemplate.selectOne("BoardDAO.getBoard", vo );
	}

	@Override
	public List<BoardVo> getBoardList(BoardVo vo) {
				
		if (vo.getSearchKeyword() == null || vo.getSearchKeyword().equals("") ) {
		    System.out.println("== >Mybatis : List<BoardVo> getBoardList ");
			return  jdbcTemplate.selectList("BoardDAO.getBoardList");
		} else if(vo.getSearchCondition().equals("title")) {
		    System.out.println("== >Mybatis : BoardDAO.GETBOARDTITLE ");
		    vo.setSearchKeyword("%" + vo.getSearchKeyword() + "%");
			return  jdbcTemplate.selectList("BoardDAO.GETBOARDTITLE", vo );
		} else if(vo.getSearchCondition().equals("writer")) {
		    System.out.println("== >Mybatis : BoardDAO.GETBOARDWRITER ");		  
		    vo.setSearchKeyword("%" + vo.getSearchKeyword() + "%");
		    return  jdbcTemplate.selectList("BoardDAO.GETBOARDWRITER", vo );
		}
				
		return null;
		
	}

	@Override
	public void cnt(BoardVo vo) {		
		jdbcTemplate.update("BoardDAO.cnt", vo);		
	}

}

---

로그인 만들기 (USER)

 

1. UserDaoMyBatis

package com.springbook.biz.login;

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

@Repository
public class UserDaoMybatis  implements   UserDao {

	@Autowired
	private SqlSessionTemplate  jdbcTemplate;
	
	
	@Override
	public void insert(UserVo vo) {		
		jdbcTemplate.insert("USERS.insertUser", vo);		
	}

	@Override
	public void update(UserVo vo) {
		jdbcTemplate.update("USERS.updateUser", vo);	
	}

	@Override
	public void delete(UserVo vo) {
		jdbcTemplate.delete("USERS.deleteUser", vo);	
	}

	@Override
	public UserVo getEdit(UserVo vo) {
		return jdbcTemplate.selectOne("USERS.getUser",vo);
	}

	@Override
	public List<UserVo> getSelect(UserVo vo) {		
		return jdbcTemplate.selectList("USERS.getUserList", vo);
	}

	@Override
	public UserVo login(UserVo vo) {
		
		UserVo user =null;	
		try {
		   user =jdbcTemplate.selectOne("USERS.LOGIN_USER", vo);
		   return user;
		} catch(Exception e) {
		   return user;	
		}

	}

}

2. user-mapping.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="USERS">

<insert id="insertUser" parameterType="user" >
  insert  into users(id, password, name, role)
  values(#{id}, #{password}, #{name}, #{role})
</insert>
<update id="updateUser" parameterType="user" >
  update users set password=#{password}, name=#{name}, role =#{role}
  where id =#{id}
</update>
<delete id="deleteUser"  parameterType="user" >
   delete from users where id = #{id}
</delete>

<select id="getUser" parameterType="user" resultType="user">
  select * from users where id=#{id}
</select>

<select id="getUserList"  resultType="user">
   select * from users
</select>

<select id="LOGIN_USER" parameterType="user" resultType="user">
  select * from users 
  where id=#{id} and password=#{password}
</select>


</mapper>

 

---

PSD

1. PsdDaoMybatis

package com.springbook.biz.psd;

import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.List;
import org.mybatis.spring.SqlSessionTemplate;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Repository;

@Repository
public class PsdDaoMybatis implements PsdDao {

	@Autowired
	private SqlSessionTemplate  jdbcTemplate;
	
	@Override
	public void insert(PsdVo vo) {
		
		Date date=new Date();
		SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy-MM-dd"); 
		String strNowDate = simpleDateFormat.format(date);
		vo.setRegDateStr(strNowDate);
		jdbcTemplate.insert("PSD.insertPsd", vo);		
		
	}

	@Override
	public void update(PsdVo vo) {
		
		Date date=new Date();
		SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy-MM-dd"); 
		String strNowDate = simpleDateFormat.format(date);
		vo.setRegDateStr(strNowDate);
		
		if (vo.getUpdateFileStr() == null || vo.getUpdateFileStr().equals("") ) {
		  jdbcTemplate.update("PSD.UPDATE_SQL2", vo);	
		} else {
		  jdbcTemplate.update("PSD.UPDATE_SQL1", vo);		
		}
		
	}

	@Override
	public void delete(PsdVo vo) {
		jdbcTemplate.update("PSD.deletePsd", vo);			
	}

	@Override
	public PsdVo getPsd(PsdVo vo) {
		return jdbcTemplate.selectOne("PSD.getPsd", vo);
	}

	@Override
	public List<PsdVo> getPsdList(PsdVo vo) {
		return jdbcTemplate.selectList("PSD.getPsdList", vo);
	}

}

2. psd-mapping.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="PSD">

<resultMap type="psd" id="psdResult">
 <!-- property 값은 Vo 의 필드명 , column의 값은 SQL 구문의 칼럼명 -->
 <id property="seq" column="seq" />
 <result property="seq" column="seq"/>
 <result property="title" column="title"/>
 <result property="writer" column="writer"/>
 <result property="content" column="content"/>
 <result property="regDateStr" column="regdate"/>
 <result property="cnt" column="cnt"/>
 <result property="updateFileStr" column="updatefile"/>
</resultMap>

<insert id="insertPsd"  parameterType="psd">
<![CDATA[
  insert into  boardPsd (seq, title, writer, content, regdate, updatefile ) 
  values ((select nvl(max(seq),1001)+1 from boardPsd ),#{title}, #{writer}, #{content}, #{regdate}, #{updatefile}) 
]]>
</insert>

<select id="getPsdList"  resultMap="psdResult">
<![CDATA[
   select  *  from  boardPsd order by seq  desc
 ]]>
</select>

<select id="getPsd" parameterType="psd" resultType="psd">
<![CDATA[
  select  seq, title, writer, content, regdate, updatefile as updatefilestr , cnt  
  from  boardPsd where seq =#{seq}
]]>
</select>


<delete id="deletePsd"  parameterType="psd" >
  delete from boardPsd where seq=#{seq}
</delete>

<update id="UPDATE_SQL1" parameterType="psd" >
 update boardPsd set title=#{title}, content=#{content}, updatefile=#{updatefile} ,regdate=#{regdate} 
 where seq=#{seq} 
 </update>
<update id="UPDATE_SQL2"  parameterType="psd">
update boardPsd set title=#{title}, content=#{content} , regdate=#{regdate} where seq=#{seq} 
 </update>


</mapper>

---