국비 50일차_1_Spring-Oracle

강지수·2024년 2월 27일
국비교육국비학원백엔드웹개발자바
0

국비교육

목록 보기
87/97
post-thumbnail

지난 시간 복습

Spring - Oracle 연동

	<!-- Spring UTF-8 Filtering -->
	
	<filter>
		<filter-name>encodingFilter</filter-name>
		<filter-class>
			org.springframework.web.filter.CharacterEncodingFilter
		</filter-class>
		<init-param>
			<param-name>encoding</param-name>
			<param-value>UTF-8</param-value>
		</init-param>
        <init-param>
			<param-name>forceEncoding</param-name>
			<param-value>true</param-value>
		</init-param>
	</filter>
	<filter-mapping>
		<filter-name>encodingFilter</filter-name>
		<url-pattern>/*</url-pattern>
	</filter-mapping>

web.xml 에 추가할 Spring UTF=8 Filter

mvnrepository - ojdbc6 검색 - 11.2.0.4 버전 - Maven 복사 - pom.xml 의 dependency 부분에 추가 - ojdbc6 생성됨을 확인

Oracle 로 돌아와서

<Resource name="jdbc/springxe"
     auth="Container"
     type="javax.sql.DataSource"
     driverClassName="oracle.jdbc.driver.OracleDriver"
     username="hr"
     password="123456"
     url="jdbc:oracle:thin:@localhost:1521:xe"
     maxWait="5000"
    />

서버 단에 dbcp 추가 - Servers - context.xml - 위의 내용 추가

create table replyboard(
bid number(4) primary key,
bname varchar2(20),
btitle varchar2(100),
bcontent varchar2(300),
bdate Date default sysdate,
bhit number(4) default 0,
bgroup number(4),
bstep number(4),
bindent number(4));

table 생성

create sequence replyboard_seq;

sequence 생성

insert into replyboard values(
replyboard_seq.nextval,'name','title','content',sysdate,0,replyboard_seq.currval,0,0);

data 추가

STS 로 돌아와서

package com.tech.sprjt09replyboard.dto;

import java.sql.Timestamp;

public class BoardDto {
	private int bid;
	private String bname;
	private String btitle;
	private String bcontent;
	private Timestamp bdate;
	private int bhit;
	private int bgroup;
	private int bstep;
	private int bindent;

	public int getBid() {
		return bid;
	}

	public void setBid(int bid) {
		this.bid = bid;
	}

	public String getBname() {
		return bname;
	}

	public void setBname(String bname) {
		this.bname = bname;
	}

	public String getBtitle() {
		return btitle;
	}

	public void setBtitle(String btitle) {
		this.btitle = btitle;
	}

	public String getBcontent() {
		return bcontent;
	}

	public void setBcontent(String bcontent) {
		this.bcontent = bcontent;
	}

	public Timestamp getBdate() {
		return bdate;
	}

	public void setBdate(Timestamp bdate) {
		this.bdate = bdate;
	}

	public int getBhit() {
		return bhit;
	}

	public void setBhit(int bhit) {
		this.bhit = bhit;
	}

	public int getBgroup() {
		return bgroup;
	}

	public void setBgroup(int bgroup) {
		this.bgroup = bgroup;
	}

	public int getBstep() {
		return bstep;
	}

	public void setBstep(int bstep) {
		this.bstep = bstep;
	}

	public int getBindent() {
		return bindent;
	}

	public void setBindent(int bindent) {
		this.bindent = bindent;
	}

	public BoardDto() {
		// TODO Auto-generated constructor stub
	}

	public BoardDto(int bid, String bname, String btitle, String bcontent, Timestamp bdate, int bhit, int bgroup,
			int bstep, int bindent) {
		this.bid = bid;
		this.bname = bname;
		this.btitle = btitle;
		this.bcontent = bcontent;
		this.bdate = bdate;
		this.bhit = bhit;
		this.bgroup = bgroup;
		this.bstep = bstep;
		this.bindent = bindent;
	}

}

BoardDto.java

package com.tech.sprjt09replyboard.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Timestamp;
import java.util.ArrayList;

import javax.naming.Context;
import javax.naming.InitialContext;
import javax.sql.DataSource;

import com.tech.sprjt09replyboard.dto.BoardDto;

public class BoardDao {
	DataSource dataSource;

	public BoardDao() {
		// db접속 생성자
		try {
			Context context = new InitialContext();
			dataSource = (DataSource) context.lookup("java:comp/env/jdbc/springxe");
			System.out.println("DB Access Successed");
		} catch (Exception e) {
			System.out.println("DB Access Denied - error 1");
		}
	}

	public ArrayList<BoardDto> list() {
		Connection con = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		ArrayList<BoardDto> dtos = new ArrayList<BoardDto>();

		try {
			con=dataSource.getConnection();
			String sql="select bid, bname, btitle, bcontent, bdate, bhit, bgroup, bstep, bindent from replyboard";
			pstmt=con.prepareStatement(sql);
			rs=pstmt.executeQuery();
			while(rs.next()) {
				int bid=rs.getInt("bid");
				String bname=rs.getString("bname");
				String btitle=rs.getString("btitle");
				String bcontent=rs.getString("bcontent");
				Timestamp bdate=rs.getTimestamp("bdate");
				int bhit=rs.getInt("bhit");
				int bgroup=rs.getInt("bgroup");
				int bstep=rs.getInt("bstep");
				int bindent=rs.getInt("bindent");
				
				BoardDto dto=new BoardDto(bid, bname, btitle, bcontent, bdate, bhit, bgroup, bstep, bindent);
				dtos.add(dto);
			}
		} catch (Exception e) {
			System.out.println("DB Access Denied - error 2");
		}

		return dtos;
	}

}

BoardDao.java

package com.tech.sprjt09replyboard.service;

import org.springframework.ui.Model;

public interface BServiceInter {
	public void execute(Model model);

}

BServiceInter.java

package com.tech.sprjt09replyboard.service;

import java.util.ArrayList;

import org.springframework.ui.Model;

import com.tech.sprjt09replyboard.dao.BoardDao;
import com.tech.sprjt09replyboard.dto.BoardDto;

public class BListService implements BServiceInter{

	@Override
	public void execute(Model model) {
		// db에 접속해서 data 조회
		BoardDao dao=new BoardDao();
		ArrayList<BoardDto> dtos=dao.list();
		// model 에 적재
		model.addAttribute("list",dtos);
		
	}

}

BListService.java

package com.tech.sprjt09replyboard.controller;

import org.springframework.stereotype.Controller;
import org.springframework.ui.Model;
import org.springframework.web.bind.annotation.RequestMapping;

import com.tech.sprjt09replyboard.service.BListService;
import com.tech.sprjt09replyboard.service.BServiceInter;

@Controller
public class BController {
	
	BServiceInter bServiceInter;
	
	@RequestMapping("list")
	public String list(Model model) {
		// data 가져오기
		bServiceInter=new BListService();
		bServiceInter.execute(model);
		
		return "list";
	}

}

BController.java

<%@ taglib uri="http://java.sun.com/jsp/jstl/core" prefix="c" %>
<%@ page session="false" %>
<html>
<head>
	<title>Home</title>
</head>
<body>
<h1>
	Hello world!  
</h1>

<P>  The time on the server is ${serverTime}. </P>

<a href="list">to list.jsp</a>
</body>
</html>

home.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>Insert title here</title>
</head>
<body>
	<h3>list.jsp</h3>
	<table width="500" border="1">
		<tr>
			<td>번호</td>
			<td>이름</td>
			<td>제목</td>
			<td>날짜</td>
			<td>히트</td>
		</tr>
		<c:forEach items="${list }" var="dto">
			<tr>
				<td>${dto.bid }</td>
				<td>${dto.bname }</td>
				<td>${dto.btitle }</td>
				<td>${dto.bdate }</td>
				<td>${dto.bhit }</td>
			</tr>
		</c:forEach>
	</table>
</body>
</html>

list.jsp

자원회수

finally {
			try {
				if(rs!=null) rs.close();
				if(pstmt!=null) pstmt.close();
				if(con!=null) con.close();
			} catch (Exception e2) {
				System.out.println("DB Access Denied - error 3");
			}
		}

를 BoardDao.java 에 추가

글쓰기 기능 추가

<%@ 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>Insert title here</title>
<link rel="stylesheet" href="resources/css/nstyle.css" />
</head>
<body>
	<h3>write_view.jsp</h3>
	<form action="write">
		<table>
			<tr>
				<td class="left">이름</td>
				<td>
					<input type="text" name="bname" value="writer" />
				</td>
			</tr>
			<tr>
				<td class="left">제목</td>
				<td>
					<input type="text" name="btitle" value="title" />
				</td>
			</tr>
			<tr>
				<td class="left">내용</td>
				<td>
					<textarea type="text" name="bcontent" id="" cols="35" rows="5">content</textarea>
				</td>
			</tr>
			<tr>
				<td colspan="2">
					<input type="submit" name="bname" value="입력" />
					<a href="list">목록</a>
				</td>
			</tr>
		</table>
	</form>
</body>
</html>

write_view.jsp

table{
	border-collapse: collapse;
	width: 600px;
}
th{
	background-color: #ddd;
}
th,td{
	padding: 5px;
	text-align: left;
	border-bottom: 1px soid #ddd;
}
.left{
	background-color: #eee;
	width: 150px;
}
tr:hover{
	background-color: green;
}

nstyle.css

package com.tech.sprjt09replyboard.service;

import java.util.Map;

import javax.servlet.http.HttpServletRequest;

import org.springframework.ui.Model;

import com.tech.sprjt09replyboard.dao.BoardDao;

public class BWriteService implements BServiceInter{

	@Override
	public void execute(Model model) {
		// model 에서 request 를 끌어내기 - map 으로 전환
		Map<String, Object> map=model.asMap();
		HttpServletRequest request=(HttpServletRequest) map.get("request");
		String bname=request.getParameter("bname");
		String btitle=request.getParameter("btitle");
		String bcontent=request.getParameter("bcontent");
		
		// db에 접속해서 data 쓰기
		BoardDao dao=new BoardDao();
		dao.write(bname,btitle,bcontent);
		
	}

}

BWriteService.java

	@RequestMapping("write_view")
	public String write_view(Model model) {

		return "write_view";
	}

	@RequestMapping("write")
	public String write(HttpServletRequest request, Model model) {
		// 글쓰기 진행
		model.addAttribute("request",request);
		bServiceInter=new BWriteService();
		bServiceInter.execute(model);
		
		return "redirect:list";
	}

BController.java 에 내용 추가

		<tr>
			<td colspan="5"><a href="write_view">글쓰기</a></td>
		</tr>

list.jsp 에 내용 추가

	public void write(String bname, String btitle, String bcontent) {
		// db 연결, insert 실행
		Connection con = null;
		PreparedStatement pstmt = null;

		try {
			con=dataSource.getConnection();
			String sql="insert into replyboard values(" + 
					"replyboard_seq.nextval,?,?,?,sysdate,0,replyboard_seq.currval,0,0)";
			pstmt=con.prepareStatement(sql);
			pstmt.setString(1, bname);
			pstmt.setString(2, btitle);
			pstmt.setString(3, bcontent);
			int rn=pstmt.executeUpdate();			
			
		} catch (Exception e) {
			System.out.println("DB Access Denied - error 4");
		} finally {
			try {
				if(pstmt!=null) pstmt.close();
				if(con!=null) con.close();
			} catch (Exception e2) {
				System.out.println("DB Access Denied - error 5");
			}
		}
	}

BoardDao.java 에 내용 추가

제목을 클릭했을 때 event

		<c:forEach items="${list }" var="dto">
			<tr>
				<td>${dto.bid }</td>
				<td>${dto.bname }</td>
				<td><a href="content_view?bid=${dto.bid }">${dto.btitle }</a></td>
				<td>${dto.bdate }</td>
				<td>${dto.bhit }</td>
			</tr>
		</c:forEach>

list.jsp 수정

	@RequestMapping("content_view")
	public String content_view(Model model) {

		return "content_view";
	}

BController.java 에 추가

<%@ 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>Insert title here</title>
</head>
<body>
<h3>content_view.jsp</h3>
</body>
</html>

content_view.jsp

이거저거 수정해서

Spring 하면서 Python 기초 같이 진행할 예정

Spring 미션

PizzaDao
PizzaListService implements PizzaServiceInter
PizzaDto
PizzaController

Oracle Query

create table pz_board(
pzid number(4) primary key,
pzname varchar2(20),
pzsubj varchar2(100),
pzcontent varchar2(300),
pzdate Date default sysdate,
pzhit number(4) default 0,
pzgroup number(4),
pzstep number(4),
pzintent number(4));

create sequence pz_board_seq;

메일제목 : 피자스프링미션_홍길동
제출 내용 : zip 파일 첨부, 결과 화면 2장 이상 캡쳐

profile
강지수
개발자 준비의 준비준비중..
이전 포스트

국비 49일차_2

다음 포스트

국비 51일차_1

0개의 댓글