Spring 프로젝트 Mybatis형식으로 작성하기[2편]

Spring 프로젝트 Mybatis형식으로 작성하기

4. 기존 MVC 구조에서 달라진 점은 Dao의 변형과 Mapper가 생겼다는 점이다. 

[ 전 ]

@Repository
public class BoardDao {
 
    private NamedParameterJdbcTemplate template;
    private RowMapper<Board> mapper = new BeanPropertyRowMapper<Board>(Board.class);
    
    @Autowired
    public void setDataSource(DataSource dataSource) {
        template = new NamedParameterJdbcTemplate(dataSource);
    }
    
    public Board select(Integer num) {
        String sql = "select num, name, pass, subject, content, "
                + "file1 fileurl, regdate, readcnt, ref, reflevel,"
                + " refstep from board where num=:num";
        Map<String, Integer> param = new HashMap<String, Integer>();
        param.put("num", num);
        return template.queryForObject(sql, param, mapper);
    }
 
    public int count(String searchType, String searchContent) {
        String sql = "select count(*) from board";
        if(searchType != null && searchContent != null) {
            sql += " where " + searchType + " like '%"+searchContent+"%'";
        }
        Integer ret =template.queryForObject(sql, new HashMap(), Integer.class);
        return ret;
    }
 
    public List<Board> list(String searchType, String searchContent, Integer pageNum, int limit) {
        String sql = "select num, name, pass, subject, content, "
                    + "file1 fileurl, regdate, readcnt, ref, reflevel,"
                    + " refstep from board";
        if(searchType != null && searchContent != null) {
            sql += " where " + searchType + " like '%" + searchContent + "%'";
        }
        sql += " order by ref desc, refstep asc limit :startrow, :limit";
        Map<String, Integer> param = new HashMap<String, Integer>();
        int startrow = (pageNum - 1)*limit;
        param.put("startrow", startrow);
        param.put("limit", limit);
        return template.query(sql, param, mapper);
    }
    
    public int maxNum() {
          Integer i = template.queryForObject("select ifnull(max(num),0) from board", new HashMap(), Integer.class);
          return i;
       }
 
 
       public void insert(Board board) {
          SqlParameterSource param = new BeanPropertySqlParameterSource(board);
          String sql ="insert into board "
                + "(num,name,pass,subject,content,regdate,file1,readcnt,ref,reflevel, refstep)"
                + " values(:num, :name,:pass,:subject,:content,"
                + " now(),:fileurl,0,:ref,:reflevel,:refstep)";
          template.update(sql, param);
          
       }
 
 
       public void readcntadd(Integer num) {
          Map<String, Integer> param = new HashMap<String, Integer>();
          param.put("num", num);
          template.update("update board set readcnt=readcnt+1 where num=:num", param);
       }
     
       public void updateRefstep(int ref, int refstep) {
              String sql="update board set "
                    +"refstep=refstep+1 where ref=:ref and refstep>:refstep";
              Map<String, Integer> param = new HashMap<String, Integer>();
              param.put("ref", ref);
              param.put("refstep", refstep);
              template.update(sql, param);
 
           }
 
    public void update(Board board) {
        String sql = "update board set subject=:subject, file1=:fileurl, content=:content where num=:num";
        SqlParameterSource param = new BeanPropertySqlParameterSource(board);
        template.update(sql, param);
    }
 
    public void delete(int num) {
        String sql = "delete from board where num=:num";
        Map<String, Integer> map = new HashMap<String, Integer>();
        map.put("num", num);
        template.update(sql, map);
    }
       
}
 
 

@Repository
public class BoardDao {
    @Autowired
    private SqlSessionTemplate sqlsession;
    private final String NS = "dao.mapper.BoardMapper.";
    public Board select(Integer num) {
        Map<String, Integer> map = new HashMap<String, Integer>();
        map.put("num", num);
        return sqlsession.selectOne(NS + "list", map);
    }
    public int count(String searchType, String searchContent) {
        Map<String, String> map = new HashMap<String, String>();
        map.put("searchType", searchType);
        map.put("searchContent", searchContent);
        return sqlsession.selectOne(NS + "count", map);
    }
    public List<Board> list(String searchType, String searchContent, Integer pageNum, int limit) {
        Map<String, Object> param = new HashMap<String, Object>();
        int startrow = (pageNum - 1) * limit;
        param.put("startrow", startrow);
        param.put("limit", limit);
        param.put("searchType", searchType);
        param.put("searchContent", searchContent);
        return sqlsession.selectList(NS + "list", param);
    }
    public int maxNum() {
        int i = sqlsession.getMapper(BoardMapper.class).maxNum();
        return i;
    }
    public void insert(Board board) {
        sqlsession.getMapper(BoardMapper.class).insert(board);
    }
    public void readcntadd(Integer num) {
        Map<String, Integer> param = new HashMap<String, Integer>();
        param.put("num", num);
        sqlsession.getMapper(BoardMapper.class).updatecnt(num);
    }
    public void updateRefstep(int ref, int refstep) {
        Map<String, Integer> param = new HashMap<String, Integer>();
        param.put("ref", ref);
        param.put("refstep", refstep);
        sqlsession.getMapper(BoardMapper.class).updateref(param);
    }
    public void update(Board board) {
        sqlsession.getMapper(BoardMapper.class).update(board);
    }
    public void delete(int num) {
        sqlsession.getMapper(BoardMapper.class).delete(num);
    }
}
 

5-1. Mapper (interface)

public interface BoardMapper {
    @Select("select ifnull(max(num),0) from board")
    int maxNum();
    
    @Insert("insert into board (num,name,pass,subject,content,regdate,file1,readcnt,ref,reflevel, refstep)"
                + " values(#{num}, #{name},#{pass},#{subject},#{content}, now(),#{fileurl},0,#{ref},#{reflevel},#{refstep})")
    void insert(Board board);
 
    @Update("update board set readcnt=readcnt+1 where num=#{num}")
    void updatecnt(Integer num);
    
    @Update("update board set refstep=refstep+1 where ref=#{ref} and refstep > #{refstep}")
    void updateref(Map<String, Integer> map);
 
    @Update("update board set subject=#{subject}, file1=#{fileurl}, content=#{content} where num=#{num}")
    void update(Board board);
 
    @Delete("delete from board where num=#{num}")
    void delete(int num);
    
}
 

5-2. Mapper (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="dao.mapper.BoardMapper">
    <select id="list" resultType="Board" parameterType="map">
        select num, name, pass, subject, content, file1 fileurl,
        regdate, readcnt, ref, reflevel, refstep from board
        <if test="num != null">
            where num = ${num}
        </if>
        <if test="searchType != null and searchContent != null">
            where ${searchType} like '%${searchContent}%'
        </if>
        order by ref desc, refstep asc
        <if test="startrow !=null and limit != null">
         limit #{startrow}, #{limit}
        </if>
    </select>
 
    <select id="count" resultType="Integer" parameterType="map">
        select count(*) from board
        <if test="searchType != null and searchContent != null">
            where ${searchType} like '%${searchContent}%'
        </if>
    </select>
</mapper>

Mapper 안에는 쿼리문을 넣게 된다.

두개의 차이라고 한다면, xml같은 경우 동적 쿼리이다. 

유의 해야할 점은 ,

parameterType 과 ResultType 부분을 알맞게 넣는 것이 중요하다. 

$와 #의 차이는 

#은 파라미터 값을 넣어줄때, ''를 붙여주는데 $는 있는 그대로 넣었다. 

만약 Dao에서 값이 들어올때 , (예를 들어, int a = 11;  Integer A = 11;)

데이터의 값이 int면 자동형변환이 이루어져서 

a 같은 경우 "11" 이런식으로 데이터가 쿼리문으로 입력되지만

A 같은 경우 11 로 들어가게 된다. 이렇게 형변환이 어려울 경우 $를 쓰는 것이 맞다.

MVC2 구조로 프로젝트를 해보았기 때문에 스프링이 많이 어렵게 느껴지지는 않는다 

가끔 값을 넣어줄때나 혹은 Mapping 과정에서 특이한 부분이 있을 때는 꼭 포스팅 하겠습니다