[9/8일수업] Spring 교재 p.511 ~

p.513 어플리케이션컨텍스트.xml 파일

<!-- MyBatis 설정 --> 
<bean id="sqlsession" class="org.mybatis.spring.SqlSessionFactoryBean">
<property name="dataSource" ref="dataSource" />
<property name="configLocation" value="classpath:sql-map-config.xml" />
</bean>
<bean class="org.mybatis.spring.SqlSessionTemplate">
	<constructor-arg ref="sqlsession" />
</bean>

bean 추가하기 (id 교재대로 sqlsession으로 변경해줌)

!! mappings 패키지 아니었음 수정

p.481~ 495

board-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="BoardDAO">
<insert id="insertBoard" parameterType="board"> <!-- parameterType은 생략가능함 -->
insert into  board (seq, title, writer, content, regdate)
values ((select max(nvl(seq,0))+1 from board ),#{title}, #{writer}, #{content}, #{regdate}) 
</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"  parameterType="board" resultType="board">
select * from board  order by seq  desc
</select>

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

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

	 
</mapper>

p.516

BoardDaoMybatis 클래스

(* 코드 작성하면서 명칭 변경해줌 ㅡㅡ..)

package com.springbook.biz.board.dao;

import java.sql.ResultSet;
import java.sql.SQLException;
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.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
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); //Date 형으로 바꾸는게 오류나서 setRegDateStr 추가함

		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("") ) {
		  return  jdbcTemplate.selectList("BoardDAO.getBoardList");
		} else if(vo.getSearchCondition().equals("title")) {
		  return  jdbcTemplate.selectList("BoardDAO.GETBOARDTITLE", "%" + vo + "%");
		} else if(vo.getSearchCondition().equals("writer")) {
		  return  jdbcTemplate.selectList("BoardDAO.GETBOARDWRITER", "%" + vo+ "%");
		}
				
		return null;
		
	}

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

		
	}

}

BoardWeb2 (1).zip

0.08MB

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("USER.insertUser", vo);		
	}

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

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

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

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

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

	}

}

user mapping

<?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">
update users set password= #{password}, name=#{name}, role =#{role} where id =#{id}
</update>

<delete id="deleteUser">
delete from users where id =#{id}
</delete>

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

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

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


</mapper>

psd mapping

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

<insert id="insertPsd"  parameterType="psd" >
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" resultType="psd">
select  *  from  boardPsd order by seq  desc 
</select>

<select id="getPsd"  parameterType="psd" resultType="psd">
select  *  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>



<!-- private final String INSERT_SQL="insert into  boardPsd (seq, title, writer, content, regdate, updatefile ) "
			+ " values ((select nvl(max(seq),1001)+1 from boardPsd ),?,?,?,?,?) ";
    private final  String SELECT_SQL="select  *  from  boardPsd order by seq  desc ";
	
    private final  String EDIT_SQL="select  *  from  boardPsd where seq=?";

    private final  String DELETE_SQL="delete from boardPsd where seq=?";
 
	private final String UPDATE_SQL1="update boardPsd set title=?, content=?, updatefile=? ,regdate=?  where seq=? ";
 
	private final String UPDATE_SQL2="update boardPsd set title=?, content=? , regdate=? where seq=? ";
	 -->

</mapper>

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.delete("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);
	}

}

psd mapping에 property 추가

<?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="req" /> 
<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" >
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">
select  *  from  boardPsd order by seq  desc 
</select>

<select id="getPsd"  parameterType="psd" resultType="psd">
select  *  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>



<!-- private final String INSERT_SQL="insert into  boardPsd (seq, title, writer, content, regdate, updatefile ) "
			+ " values ((select nvl(max(seq),1001)+1 from boardPsd ),?,?,?,?,?) ";
    private final  String SELECT_SQL="select  *  from  boardPsd order by seq  desc ";
	
    private final  String EDIT_SQL="select  *  from  boardPsd where seq=?";

    private final  String DELETE_SQL="delete from boardPsd where seq=?";
 
	private final String UPDATE_SQL1="update boardPsd set title=?, content=?, updatefile=? ,regdate=?  where seq=? ";
 
	private final String UPDATE_SQL2="update boardPsd set title=?, content=? , regdate=? where seq=? ";
	 -->

</mapper>

p.503 CDATA

<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>

글자 그대로 불러오기 교재에서는 like 문 때문에 사용함 비교연산자 들어갈때 사용 <, > 들어가는경우

p. 519 where 1=1 ~

board mapping 에서

<select id="getBoardList"  resultType="board"  >
   select * from board 
   where 1 = 1
   <if test="searchCondition=='TITLE'" >
   and title like #{searchKeyword} 
   </if>
   <if test="searchCondition=='WRITER'" >
   and writer like #{searchKeyword}
   </if>
   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>
 -->

boardDaoMybatis 에

@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  jdbcTemplate.selectList("BoardDAO.getBoardList",vo);
		
	}

위처럼 코드를 줄여서 실행시킬 수 있다

오라클에서 테이블 하나 만들어주고

'JAVA' 카테고리의 다른 글

[81일차] spring 교재 p.457 데이터 변환~ (0)	2022.09.19
[76일차]MyBatis 활용 (0)	2022.09.16
[67일차] 스프링 퀵 스타트 교재 (0)	2022.08.25
[66일차] 스프링교재 (0)	2022.08.24
[64일차] 오라클 계층형 답변형 게시판 (0)	2022.08.22