메서드를 통해 넘어온 값이 Wrapper 객체이거나 String 객체일 경우 이름은 아무거나 써도 상관없다.
com.bitcamp.board.dao.MybatisMemberDao 클래스 생성
com/bitcamp/board/mapper/MemberDaoMapper.xml 파일 생성
com.bitcamp.board.dao.MybatisBoardDao 클래스 생성
com/bitcamp/board/mapper/BoardDaoMapper.xml 파일 생성
MybatisBoardDao class
@Repository // DAO 역할을 수행하는 객체에 붙이는 애노테이션
public class MybatisBoardDao implements BoardDao {
@Autowired
DataSource ds;
@Autowired
SqlSessionFactory sqlSessionFactory;
@Override
public int insert(Board board) throws Exception {
try (SqlSession sqlSession = sqlSessionFactory.openSession()) {
return sqlSession.insert("BoardDao.insert", board);
}
}
@Override
public Board findByNo1(int no) throws Exception {
try (SqlSession sqlSession = sqlSessionFactory.openSession()) {
Board board = sqlSession.selectOne("BoardDao.findByNo", no);
// 게시글 첨부파일 가져오기
List<AttachedFile> attachedFiles = sqlSession.selectList("BoardDao.findFilesByBoard", no);
board.setAttachedFiles(attachedFiles);
return board;
}
}
@Override
public Board findByNo2(int no) throws Exception {
try (SqlSession sqlSession = sqlSessionFactory.openSession()) {
return sqlSession.selectOne("BoardDao.findByNo", no);
}
}
@Override
public Board findByNo3(int no) throws Exception {
try (SqlSession sqlSession = sqlSessionFactory.openSession()) {
return sqlSession.selectOne("BoardDao.findByNo3", no);
}
}
@Override
public int update(Board board) throws Exception {
try (PreparedStatement pstmt = ds.getConnection().prepareStatement(
"update app_board set title=?, content=? where bno=?")) {
pstmt.setString(1, board.getTitle());
pstmt.setString(2, board.getContent());
pstmt.setInt(3, board.getNo());
return pstmt.executeUpdate();
}
}
@Override
public int delete(int no) throws Exception {
try (PreparedStatement pstmt = ds.getConnection().prepareStatement("delete from app_board where bno=?")) {
pstmt.setInt(1, no);
return pstmt.executeUpdate();
}
}
@Override
public List<Board> findAll() throws Exception {
try (SqlSession sqlSession = sqlSessionFactory.openSession()) {
return sqlSession.selectList("BoardDao.findAll");
}
}
@Override
public int insertFiles(Board board) throws Exception {
try (SqlSession sqlSession = sqlSessionFactory.openSession()) {
return sqlSession.insert("BoardDao.insertFiles", board);
}
}
@Override
public AttachedFile findFileByNo(int fileNo) throws Exception {
try (PreparedStatement pstmt = ds.getConnection().prepareStatement(
"select bfno, filepath, bno from app_board_file where bfno = " + fileNo);
ResultSet rs = pstmt.executeQuery()) {
if (!rs.next()) {
return null;
}
AttachedFile file = new AttachedFile();
file.setNo(rs.getInt("bfno"));
file.setFilepath(rs.getString("filepath"));
file.setBoardNo(rs.getInt("bno"));
return file;
}
}
@Override
public List<AttachedFile> findFilesByBoard(int boardNo) throws Exception {
try (SqlSession sqlSession = sqlSessionFactory.openSession()) {
return sqlSession.selectList("BoardDao.findFilesByBoard", boardNo);
}
}
@Override
public int deleteFile(int fileNo) throws Exception {
try (PreparedStatement pstmt = ds.getConnection().prepareStatement(
"delete from app_board_file where bfno=?")) {
pstmt.setInt(1, fileNo);
return pstmt.executeUpdate();
}
}
@Override
public int deleteFiles(int boardNo) throws Exception {
try (PreparedStatement pstmt = ds.getConnection().prepareStatement(
"delete from app_board_file where bno=?")) {
pstmt.setInt(1, boardNo);
return pstmt.executeUpdate();
}
}
}
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
"https://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="BoardDao">
<resultMap type="board" id="boardMap">
<id column="bno" property="no"/>
<result column="title" property="title"/>
<result column="content" property="content"/>
<result column="cdt" property="createdDate"/>
<result column="vw_cnt" property="viewCount"/>
<!-- 조인 결과에서 같은 게시글에 대해 한 개의 회원 데이터를 담을 경우 -->
<association property="writer" javaType="member">
<id column="mno" property="no"/>
<result column="name" property="name"/>
</association>
<!-- 조인 결과에서 같은 게시글에 대해 여러 개의 첨부 파일 데이터를 담을 경우 -->
<collection property="attachedFiles" ofType="attachedFile">
<id column="bfno" property="no"/>
<result column="filepath" property="filepath"/>
</collection>
</resultMap>
<resultMap type="attachedFile" id="attachedFileMap">
<id column="bfno" property="no"/>
<result column="filepath" property="filepath"/>
<result column="bno" property="boardNo"/>
</resultMap>
<select id="findAll" resultMap="boardMap">
select
b.bno,
b.title,
b.cdt,
b.vw_cnt,
m.mno,
m.name
from
app_board b
join app_member m on b.mno = m.mno
order by
bno desc
</select>
<select id="findByNo" resultMap="boardMap">
select
b.bno,
b.title,
b.content,
b.cdt,
b.vw_cnt,
m.mno,
m.name
from
app_board b
join app_member m on b.mno = m.mno
where
b.bno = #{value}
</select>
<select id="findByNo3" resultMap="boardMap">
select
b.bno,
b.title,
b.content,
b.cdt,
b.vw_cnt,
m.mno,
m.name,
bf.bfno,
bf.filepath
from
app_board b
join app_member m on b.mno = m.mno
left outer join app_board_file bf on b.bno=bf.bno
where
b.bno = #{value}
</select>
<select id="findFilesByBoard" resultMap="attachedFileMap">
select
bfno,
filepath,
bno
from
app_board_file
where
bno = #{value}
</select>
<insert id="insert" parameterType="board"
useGeneratedKeys="true" keyColumn="bno" keyProperty="no">
insert into app_board(title,content,mno)
values(#{title},#{content},#{writer.no})
</insert>
<delete id="deleteByMember">
delete from app_board
where mno=#{value}
</delete>
<insert id="insertFiles" parameterType="board">
insert into app_board_file(filepath,bno)
values
<foreach collection="attachedFiles" item="file" separator=",">
(#{file.filepath},#{no})
</foreach>
</insert>
</mapper>
select
b.bno,
b.title,
b.content,
b.cdt,
m.mno,
m.name
from
app_board b
(inner) join app_member m
on b.mno = m.mno
order by
b.bno desc;
→ 질의 결과
bno | title | content | cdt | mno | name |
---|---|---|---|---|---|
1 | aaa | ok | 2022-10-18 | 100 | hong |
bno ~ cdt : Board
객체에 저장된다.
mno ~ name : Member
객체에 저장된다.
Board
객체가 Member
객체를 writer로서 포함한다.
SqlSession.selectList("BoardDao.findAll")
→ Member 객체를 포함한 Board 객체 List를 Return한다.