디바이스 융합 자바(Java) day94 - Spring Boot[JDBCTemplate]

- JDBCUtil
   : JDBC를 도와주는 클래스, 모듈
- JDBC
   : 드라이버 적재
   : 연결
   : sql문 쿼리 실행
   : 연결 해제
=> 반복적인 작업 ->> Spring에서 모듈화하여 제공

 

NewSpringStarter 
> jdbc 검색  

   api + driver 하나씩 선택해야하는데
   > Oracle Driver는 현재 선택 하지 않음(오류 발생)

JSP 이용 준비 끝!!!

+ JavaBuildPath에 ojdbc6.jar 추가

= new << 객체화 하게 되면
               결합도가 높아짐(강한 결합, 낮은 응집도)
 결합도를 낮추기 위해서 객체화를 컨테이너에게 위임

 

TestController---------------

package com.ryo.springboot;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.ui.Model;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;
import org.springframework.web.bind.annotation.ResponseBody;


@Controller
public class TestController {
	@Autowired
	private MemberDAO memberDAO;
	
	@Autowired
	private BoardDAO boardDAO;
	
	@RequestMapping("/")
	public @ResponseBody String root() {
		return "Jdbc Template 실습 실습";
	}
	
	@RequestMapping(value="/memberList", method=RequestMethod.GET) // get post 방식하게 되면
	public String memberList(Model model) {
		model.addAttribute("datas",memberDAO.selectAll());
		return "memberList";
	}
}

MemberDTO----------------

package com.ryo.springboot;

public class MemberDTO {
	private String mid;
	private String mpw;
	public String getMid() {
		return mid;
	}
	public void setMid(String mid) {
		this.mid = mid;
	}
	public String getMpw() {
		return mpw;
	}
	public void setMpw(String mpw) {
		this.mpw = mpw;
	}
	
	@Override
	public String toString() {
		return "MemberDTO [mid=" + mid + ", mpw=" + mpw + "]";
	}
}

MemberDAO----------------

package com.ryo.springboot;

import java.util.List;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Repository;

@Repository // dao라는 것을 인식 가능
public class MemberDAO {
	@Autowired // DI(의존주입)
	private JdbcTemplate jdbcTemplate;
	// 결합도를 낮추기 위해 의존주입을 컨테이너에게 시킴
	
	public List<MemberDTO> selectAll(){
		String sql = "SELECT * FROM MEMBER";
		List<MemberDTO> datas = jdbcTemplate.query(sql, new BeanPropertyRowMapper<MemberDTO>(MemberDTO.class));
		// 위의 import된 도구를 가져옴 == 코드 한줄로 줄이는 것 가능
		// 초기에 추가한 jdbc api 덕분
		return datas;
	}
}

 

-------------------------------------------------

게시판 작업(css 제외)

BoardDTO-------------------

package com.ryo.springboot;

public class BoardDTO {
	private int bid;
	private String writer;
	private String title;
	private String content;
	public int getBid() {
		return bid;
	}
	public void setBid(int bid) {
		this.bid = bid;
	}
	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 "BoardDTO [bid=" + bid + ", writer=" + writer + ", title=" + title + ", content=" + content + "]";
	}
	
}

BoardDAO-------------------

package com.ryo.springboot;

import java.util.List;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Repository;

@Repository
public class BoardDAO implements IBoardDAO{
	
	@Autowired // DI(의존주입)
	private JdbcTemplate jdbcTemplate;

	@Override
	public List<BoardDTO> selectAll(BoardDTO dto) {
		String sql = "SELECT * FROM BOARD ORDER BY DESC";
		List<BoardDTO> datas = jdbcTemplate.query(sql, new BeanPropertyRowMapper<BoardDTO>(BoardDTO.class));
		return datas;
	}

	@Override
	public BoardDTO selectOne(BoardDTO dto) {
		String sql = "SELECT * FROM BOARD WHERE BID=?";
		BoardDTO data = jdbcTemplate.queryForObject(sql, new BeanPropertyRowMapper<BoardDTO>(BoardDTO.class), dto.getBid());
		return data;
	}

	@Override
	public int insert(BoardDTO dto) {
		String sqlex = "INSERT INTO BOARD VALUES(BOARD_SEQ.nextval,?,?,?)";
		String sql = "INSERT INTO BOARD(BID,WRITER,TITLE,CONTENT) VALUES ((SELECT NVL(MAX(BID),0)+1 FROM BOARD),?,?,?)";
		int res = jdbcTemplate.update(sql,dto.getWriter(),dto.getTitle(),dto.getContent()); 
		
		return res;
	}

	@Override
	public int delete(BoardDTO dto) {
		String sql = "DELETE BOARD WHERE BID=?";
		int res = jdbcTemplate.update(sql, dto.getBid());
		return res;
	}

}

IBoardDAO------------------

package com.ryo.springboot;

import java.util.List;

public interface IBoardDAO {
	List<BoardDTO> selectAll(BoardDTO dto);
	BoardDTO selectOne(BoardDTO dto);
	int insert(BoardDTO dto);
	int delete(BoardDTO dto);
}

TestController--------------

package com.ryo.springboot;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.ui.Model;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;
import org.springframework.web.bind.annotation.ResponseBody;


@Controller
public class TestController {
	@Autowired
	private BoardDAO boardDAO;
	
	@RequestMapping("/boardList")
	public String boardList(BoardDTO dto, Model model) {
		model.addAttribute("datas", boardDAO.selectAll(dto));
		return "boardList";
	}
	
	@RequestMapping("/board")
	public String board(BoardDTO dto, Model model) {
		model.addAttribute("data", boardDAO.selectOne(dto));
		return "board";
	}
	
	@RequestMapping(value="/boardInsert", method=RequestMethod.GET)
	public String boardInsertin(BoardDTO dto) {
		return "boardInsert";
	}
	
	@RequestMapping(value = "/boardInsert", method = RequestMethod.POST)
	public String boardInsert(BoardDTO dto) {
		boardDAO.insert(dto);
		return "redirect:boardList";
	}
	
	@RequestMapping("/boardDelete")
	public String boardDelete(BoardDTO dto) {
		boardDAO.delete(dto);
		return "redirect:boardList";
	}
}

board.jsp--------------------

<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>상세 페이지</title>
</head>
<body>

번호 : ${data.bid}<br>
작성자 : ${data.writer}<br>
제목 : ${data.title}<br>
내용 : ${data.content}<br>
<a href="boardList"><input type="button" value="메인"></a>
<a href="boardDelete?bid=${data.bid}"><input type="button" value="삭제하기"></a>

</body>
</html>

boardInsert.jsp------------

<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>글 추가 페이지</title>
</head>
<body>

<form action="boardInsert" method="post">
작성자 : <input type="text" name="writer" value="writer"><br>
제목 : <input type="text" name="title" value="title"><br>
내용 : <input type="text" name="content" value="content"><br>
<input type="submit" value="글 추가"><a href="boardList"><input type="button" value="메인"></a>
</form>


</body>
</html>

boardList.jsp---------------

<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
    <%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core"%>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>글 목록 페이지</title>
</head>
<body>

<c:forEach var="v" items="${datas}">
	<a href="board?bid=${v.bid}">${v.bid}</a> | ${v.writer} | ${v.title} | 🌸🌸🌸 <br>
</c:forEach>
<hr>
<a href="boardInsert" method="get"><input type="button" value="글 쓰기"></a>
</body>
</html>

Spring.sql------------------

SELECT * FROM USER_TABLES;
DROP TABLE MEMBER;
DROP TABLE BOARD;

CREATE TABLE MEMBER(
	MID VARCHAR(15),
	MPW VARCHAR(15)
);

INSERT INTO MEMBER VALUES('admin', '1234');
INSERT INTO MEMBER VALUES('timo', '1111');

CREATE TABLE BOARD(
	BID INT PRIMARY KEY,
	WRITER VARCHAR(15),
	TITLE VARCHAR(15),
	CONTENT VARCHAR(20)
);

--제약조건
CREATE SEQUENCE BOARD_SEQ;
-- AutoIncrement 대체
DROP SEQUENCE BOARD_SEQ;