springdbaccess (2)

오늘은 db를 이용해서 게시판을 만드는 수업을 했습니다.

JdbcTemplate을 사용하기 위해 maven spring에서 관련 링크를 가져와서 pom.xml에 넣어줍니다.

 

BoardVO부터 만들어 주고..

package com.spring.db.model;

public class BoardVO {

 

private int boardNo;

private String writer;

private String title;

private String content;

 

 

public int getBoardNo() {

return boardNo;

}

public void setBoardNo(int boardNo) {

this.boardNo = boardNo;

}

public String getWriter() {

return writer;

}

public void setWriter(String writer) {

this.writer = writer;

}

public String getTitle() {

return title;

}

public void setTitle(String title) {

this.title = title;

}

public String getContent() {

return content;

}

public void setContent(String content) {

this.content = content;

}

 

@Override

public String toString() {

return "BoardVO [boardNo=" + boardNo + ", writer=" + writer + ", title=" + title + ", content=" + content + "]";

}

}

 

BoardController을 작성합니다.

@Controller

@RequestMapping("/board")

public class BoardController {

@Autowired

private IBoardService service;

@GetMapping("/write")

public void write() {

System.out.println("/board/write: GET");

}

 

@PostMapping("/write")

public String write(BoardVO vo) {

System.out.println("/board/write: POST");

service.insertArticle(vo);

return "redirect:/board/list";

}

 

@GetMapping("/list")

public void list(Model model) {

System.out.println("/board/list: GET");

model.addAttribute("articles", service.getArticles());

}

 

@GetMapping("/content")

public void content(@RequestParam("boardNo") int boardNo, Model model) {

System.out.println("/board/content?boardNo=" + boardNo);

model.addAttribute("article", service.getArticle(boardNo));

}

 

@GetMapping("/modify")

public void modify(@RequestParam("boardNo") int boardNo, Model model) {

System.out.println("/board/modify?boardNo=" + boardNo);

model.addAttribute("article", service.getArticle(boardNo));

}

 

@PostMapping("/modify")

public String modify(BoardVO vo) {

System.out.println("/board/modify: POST " + vo.getBoardNo());

service.updateArticle(vo);

return "redirect:/board/content?boardNo=" + vo.getBoardNo();

}

 

@GetMapping("/delete")

public String delete(@RequestParam("boardNo") int boardNo) {

System.out.println("/board/delete?boardNo=" + boardNo);

service.deleteArticle(boardNo);

return "redirect:/board/list";

}

 

//게시글 검색 처리 요청

@GetMapping("/searchList")

public String searchList(@RequestParam("keyword") String keyword, Model model) {

model.addAttribute("articles", service.searchList(keyword));

return "board/list";

}

}

 

BoardDAO의 인터페이스를 만들어 주고~.~ (생략) IBoardDAO,

IBoardMapper는 IBoardDAO를 복사해서 붙여넣기 하면 되는데, (IBoardService도 마찬가지)

아직 게시판을 한 개만 만들 것이기 때문에 똑같아도 상관없다고 하셨습니다.

 

sql문을 작성해 줍니다.

 

@Repository

public class BoardDAO implements IBoardDAO {

//내부 클래스 선언

class BoardMapper implements RowMapper<BoardVO> {

@Override

public BoardVO mapRow(ResultSet rs, int rowNum) throws SQLException {

BoardVO vo = new BoardVO();

vo.setBoardNo(rs.getInt("board_no"));

vo.setWriter(rs.getString("writer"));

vo.setTitle(rs.getString("title"));

vo.setContent(rs.getString("content"));

return vo;

}

}

 

 

BoardController

 

@Autowired

private JdbcTemplate template;

 

@Override

public void insertArticle(BoardVO vo) {

String sql = "INSERT INTO jdbc_board VALUES(bid_seq.NEXTVAL,?,?,?)";

template.update(sql, vo.getWriter(), vo.getTitle(), vo.getContent());

}

@Override

public List<BoardVO> getArticles() {

String sql = "SELECT * FROM jdbc_board ORDER BY board_no DESC";

return template.query(sql, new BoardMapper());

}

@Override

public BoardVO getArticle(int bId) {

String sql = "SELECT * FROM jdbc_board WHERE board_no=?";

return template.queryForObject(sql, new BoardMapper(), bId);

}

@Override

public void deleteArticle(int bId) {

String sql = "DELETE FROM jdbc_board WHERE board_no=?";

template.update(sql, bId);

}

@Override

public void updateArticle(BoardVO vo) {

String sql = "UPDATE jdbc_board "

+ "SET writer=?, title=?, content=? "

+ "WHERE board_no=?";

template.update(sql, vo.getWriter(), vo.getTitle(),

vo.getContent(), vo.getBoardNo());

}

 

@Override

public List<BoardVO> searchList(String keyword) {

String sql = "SELECT * FROM jdbc_board WHERE writer LIKE ?";

return template.query(sql, new BoardMapper(), keyword);

}

}

 

package com.spring.db.service;

@Service

public class BoardService implements IBoardService {

 

@Autowired

private IBoardMapper mapper;

 

@Override

public void insertArticle(BoardVO vo) {

mapper.insertArticle(vo);

}

@Override

public List<BoardVO> getArticles() {

return mapper.getArticles();

}

@Override

public BoardVO getArticle(int bId) {

return mapper.getArticle(bId);

}

@Override

public void deleteArticle(int bId) {

mapper.deleteArticle(bId);

}

@Override

public void updateArticle(BoardVO vo) {

mapper.updateArticle(vo);

}

@Override

public List<BoardVO> searchList(String keyword) {

keyword = "%" + keyword + "%";

return mapper.searchList(keyword);

}

 

 

}

 

해 주고 해당 .jsp를 고쳐주면 완성.. 

수업 때는 얼레벌레 코드 쳤는데 이렇게 보니까 깔끔하게 보이기도..