-
[Boot] JDBCSpring/Boot 2022. 9. 10. 20:50
build.gradle
plugins { id 'org.springframework.boot' version '2.7.2' id 'io.spring.dependency-management' version '1.0.12.RELEASE' id 'java' } group = 'com.ecl' version = '0.0.1-SNAPSHOT' sourceCompatibility = '1.8' configurations { compileOnly { extendsFrom annotationProcessor } } repositories { mavenCentral() } dependencies { implementation 'org.springframework.boot:spring-boot-starter-jdbc' implementation 'org.springframework.boot:spring-boot-starter-validation' implementation 'org.springframework.boot:spring-boot-starter-web' compileOnly 'org.projectlombok:lombok' runtimeOnly 'com.oracle.database.jdbc:ojdbc8' annotationProcessor 'org.projectlombok:lombok' testImplementation 'org.springframework.boot:spring-boot-starter-test' // 작성한 코드 implementation 'javax.servlet:jstl' implementation 'org.apache.tomcat.embed:tomcat-embed-jasper' } tasks.named('test') { useJUnitPlatform() }
src\main\resources\application.properties
# Server port server.port=8070 # JSP spring.mvc.view.prefix=/WEB-INF/views/ spring.mvc.view.suffix=.jsp # Oracle spring.datasource.driver-class-name=oracle.jdbc.OracleDriver spring.datasource.url=jdbc:oracle:thin:@localhost:1521:xe spring.datasource.username=scott spring.datasource.password=tiger
sql.sql
drop table myuser; create table myuser( id varchar2(15), name varchar2(30) ); insert into myuser values('hong1', '홍길동1'); insert into myuser values('hong2', '홍길동2'); insert into myuser values('hong3', '홍길동3'); insert into myuser values('hong4', '홍길동4'); insert into myuser values('hong5', '홍길동5'); select * from myuser;
DTO
package com.ecl.g09; import lombok.Data; @Data public class UserDto { private String id; private String name; }
Controller
package com.ecl.g09; import java.util.List; import org.springframework.beans.factory.annotation.Autowired; import org.springframework.stereotype.Controller; import org.springframework.ui.Model; import org.springframework.web.bind.annotation.RequestMapping; @Controller public class UserController { @Autowired UserDao udao; @RequestMapping("/") public String userListPage(Model model) { List<UserDto> list = udao.getList(); model.addAttribute("users", list); return "userlist"; } }
DAO
package com.ecl.g09; 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 UserDao { @Autowired private JdbcTemplate template; public List<UserDto> getList() { String sql = "select * from myuser"; List<UserDto> list = template.query(sql, new BeanPropertyRowMapper<UserDto>( UserDto.class ) ); // ResultSet 사용없이 검색 결과 레코드의 필드를 Dto 변수에 넣고 list 에 add 동작을 실행함 // 결과 레코드 갯수만큼 실행함 return list; } }
게시글
sql.sql
drop table bbs; create table bbs( id number(3), writer varchar2(30), title varchar2(30), content varchar2(1000) ); create sequence bbs_seq start with 1; insert into bbs values(bbs_seq.nextVal, 'Gildong', '반갑습니다', '반갑습니다반갑습니다반갑습니다반갑습니다'); insert into bbs values(bbs_seq.nextVal, 'Gilnam', '안녕하세요', '안녕하세요안녕하세요안녕하세요안녕하세요'); insert into bbs values(bbs_seq.nextVal, 'Gilbook', '어서오세요', '어서오세요어서오세요어서오세요어서오세요');
DTO
package com.ecl.g10.dto; import lombok.Data; @Data public class BbsDto { private String id; private String writer; private String title; private String content; }
Interface
package com.ecl.g10.dao; import java.util.List; import com.ecl.g10.dto.BbsDto; public interface IBbsDao { public List<BbsDto> getList(); // 게시물 전체 조회 - 매개변수 없고, 리턴값은 List<BbsDto>형 public int write(BbsDto bdto); // 게시물 쓰기 - 매개변수 BbsDto형, 리턴 int public int update(BbsDto bdto); // 수정 - 매개변수 BbcDto형, 리턴 int public int delete(int id); // 삭제 - 매개변수 int, 리턴 int public BbsDto view(int id); // 게시물 하나 보기 - 매개변수 int, 리턴 BbsDto }
게시글 목록
Controller
package com.ecl.g10.controller; 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 com.ecl.g10.dao.BbsDao; @Controller public class BbsController { @Autowired BbsDao bdao; @RequestMapping("/") public String root(Model model) { model.addAttribute("list", bdao.getList()); return "list"; } 이 안에 들어갈 코드 배경 - 검정 }
DAO
package com.ecl.g10.dao; 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; import com.ecl.g10.dto.BbsDto; @Repository public class BbsDao implements IBbsDao{ @Autowired private JdbcTemplate template; @Override public List<BbsDto> getList() { String sql = "select * from bbs order by id desc"; List<BbsDto> list = template.query(sql, new BeanPropertyRowMapper<BbsDto>( BbsDto.class ) ); return list; } @Override public int write(BbsDto bdto) { // TODO Auto-generated method stub return 0; } @Override public int update(BbsDto bdto) { // TODO Auto-generated method stub return 0; } @Override public int delete(String id) { // TODO Auto-generated method stub return 0; } @Override public BbsDto view(String id) { // TODO Auto-generated method stub return null; } }
list.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>list.jsp</title> </head> <body> <table width="500" cellpadding="0" cellspacing="0" border="1"> <tr align="center"> <td>번호</td><td>작성자</td><td>제목</td><td>삭제</td></tr> <c:forEach items="${list}" var="dto"> <tr align="center"> <td>${dto.id}</td><td>${dto.writer}</td> <td><a href="view?id=${dto.id}">${dto.title}</a></td> <td><a href="delete?id=${dto.id}">X</a></td></tr> </c:forEach> </table> <p><a href="writeForm">글작성</a></p> </body> </html>
게시글 작성Controller
@RequestMapping("writeForm") public String writeForm(Model model) { return "writeForm"; }
writeForm.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>writeForm.jsp</title> </head> <body> <table width="600" cellpadding="0" cellspacing="0" border="1"> <form action="write" method="post"> <tr><td > 작성자 </td><td> <input type="text" name="writer" size = "80"> </td></tr> <tr><td> 제목 </td><td> <input type="text" name="title" size = "80" > </td> </tr> <tr><td> 내용 </td><td> <input type="text" name="content" size = "80" ></td></tr> <tr> <td colspan="2"> <input type="submit" value="입력"> <a href="/">목록보기</a></td></tr> </form> </table> </body> </html>
Controller
@RequestMapping("write") public String write(BbsDto bdto, Model model) { bdao.write(bdto); return "redirect:/"; }
DAO
@Override public int write(BbsDto bdto) { String sql = "insert into bbs values(bbs_seq.nextVal, ?, ?, ?)"; int result = template.update(sql , bdto.getWriter(), bdto.getTitle(), bdto.getContent()); return result; }
게시글 상세보기Controller
@RequestMapping("view") public String view(Model model, @RequestParam("id") int id) { model.addAttribute("dto", bdao.view(id)); return "view"; }
DAO
@Override public BbsDto view(int id) { String sql = "select * from bbs where id = ? "; BbsDto dto = template.queryForObject( sql, new BeanPropertyRowMapper<BbsDto>(BbsDto.class), id); return dto; }
view.jsp
<%@ page language="java" contentType="text/html; charset=UTF-8" pageEncoding="UTF-8"%> <!DOCTYPE html> <html> <head> <meta charset="UTF-8"> <title>view.jsp</title> </head> <body> 내용보기 <br> <hr> 작성자 : ${dto.writer} <br> 제목 : ${dto.title} <br> 내용 : ${dto.content} <hr> <br><p> <a href="/">목록보기</a> </body> </html>
게시글 삭제하기Controller
@RequestMapping("delete") public String delete(Model model, @RequestParam("id") int id) { bdao.delete(id); return "redirect:/"; }
DAO
@Override public int delete(int id) { String query = "delete from bbs where id = ?"; return template.update(query, id); }
번호 4 게시글 삭제함 'Spring > Boot' 카테고리의 다른 글
[Boot] Transaction (0) 2022.09.10 [Boot] MyBatis (0) 2022.09.10 [Boot] Validation (0) 2022.09.10 [Boot] Lombok (0) 2022.09.10 [Boot] Gradle (0) 2022.09.10