[Boot] JDBC

 

 

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="입력">&nbsp;&nbsp;
		<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 게시글 삭제함