본문 바로가기
JSP

230120_DB

by 경 훈 2023. 1. 20.

 

<%@page import="java.sql.DriverManager"%>
<%@page import="java.sql.ResultSet"%>
<%@page import="java.sql.PreparedStatement"%>
<%@page import="java.sql.Connection"%>
<%
Connection con;
PreparedStatement psmt;
ResultSet rs;
String  driver="oracle.jdbc.driver.OracleDriver";
String url="jdbc:oracle:thin:@localhost:1521:xe";
Class.forName(driver);
con=DriverManager.getConnection(url, "ezen", "12345");
%>

 

<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>Insert title here</title>
</head>
<body>
	<br><br><br>
	<div align="center">
		<a href = "input.jsp"><input type="button" value="도서정보입력"></a>
		<a href = "out.jsp"><input type="button" value="도서정보출력"></a>
	</div>
</body>
</html>

 

<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>Insert title here</title>
</head>
<body>
	<form action="save.jsp" method="post">
		<table border="1" align="center">
			<caption>도서내용입력</caption>
			<tr>
				<th>도서명</th>
				<td><input type="text" name="bname"></td>
			</tr>
			<tr>
				<th>저자</th>
				<td><input type="text" name="writer"></td>
			</tr>
			<tr>
				<th>가격</th>
				<td><input type="text" name="price"></td>
			</tr>
			<tr>
				<th>내용</th>
				<td><textarea rows="10" cols="30" name="bigo"></textarea></td>
			</tr>
			<tr>
				<td colspan="3" align="center">
					<input type="submit" value="전송">
					<input type="reset" value="초기화">
					<a href="menu.jsp"><input type="button" value="메뉴"></a>
				</td>		
			</tr>
		</table>
	</form>
</body>
</html>

 

<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<%@ include file="dbcon.jsp" %>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>Insert title here</title>
</head>
<body>
	<%!
	String bname,writer,bigo;
	int price;
	%>
	<%
	request.setCharacterEncoding("utf-8");
	bname = request.getParameter("bname");
	writer = request.getParameter("writer");
	price = Integer.parseInt(request.getParameter("price"));
	bigo = request.getParameter("bigo");
	String sql = "insert into db01201 values(?,?,?,?)";
	psmt = con.prepareStatement(sql);
	psmt.setString(1, bname);
	psmt.setString(2, writer);
	psmt.setInt(3, price);
	psmt.setString(4, bigo);
	int n = psmt.executeUpdate();
	if(n==1) {
		response.sendRedirect("menu.jsp");
	}
	%>
</body>
</html>

 

<%@page import="java.text.DecimalFormat"%>
<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<%@ include file="dbcon.jsp" %>
<!DOCTYPE html>
<html>
<head>
<style type="text/css">
	th,td {
		text-align: center;
	}
	button {
		border: none;
		background-color: #ffffff;
		cursor: pointer;
	}
</style>
<meta charset="UTF-8">
<title>Insert title here</title>
</head>
<body>
	<%!
	String bname,writer,bigo;
	int price;
	DecimalFormat df = new DecimalFormat("#,##0");
	%>
	<%
	request.setCharacterEncoding("utf-8");
	String sql = "select * from db01201";
	psmt = con.prepareStatement(sql);
	rs = psmt.executeQuery();
	%>
	<table border="1" align="center" width="800px">
		<caption>목록</caption>
		<tr>
			<th>도서명</th><th>저자</th><th>가격</th>
			<th>내용</th><th>비고</th>
		</tr>
	<%
	while(rs.next()) {
		bname = rs.getString(1);
		writer = rs.getString(2);
		price = rs.getInt(3);
		bigo = rs.getString(4);
	%>
		<tr>
			<td><a href = "update1.jsp?bname=<%=bname%>"><%=bname%></a></td>
			<td><%=writer%></td><td>￦<%=df.format(price)%></td><td><%=bigo%></td>
			<td colspan="2" align="center">
			<a href = "delete.jsp?bname=<%=bname%>"><button>&#10060;</button></a>
			<a href = "update1.jsp?bname=<%=bname%>"><button>&#9989;</button></a>
			</td>
		</tr>
	<%
	}
	%>
	</table>
	<div align="center">
		<a href = "menu.jsp"><input type="button" value="메뉴"></a>
	</div>
</body>
</html>

 

<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<%@ include file="dbcon.jsp" %>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>Insert title here</title>
</head>
<body>
	<%!
	String bname;
	%>
	<%
	request.setCharacterEncoding("utf-8");
	bname = request.getParameter("bname");
	String sql = "delete from db01201 where bname=?";
	psmt = con.prepareStatement(sql);
	psmt.setString(1, bname);
	int n = psmt.executeUpdate();
	if(n==1) {
		response.sendRedirect("menu.jsp");
	}
	%>
</body>
</html>

 

<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<%@ include file="dbcon.jsp" %>
<!DOCTYPE html>
<html>
<head>
<style type="text/css">
	th,td {
		text-align: center;
	}
</style>
<meta charset="UTF-8">
<title>Insert title here</title>
</head>
<body>
	<%!
	String bname,writer,bigo;
	int price;
	%>
	<%
	request.setCharacterEncoding("utf-8");
	bname = request.getParameter("bname");
	//도서명을 넘겨 받았으면..db에서 도서명이 같은 자료를 가져와야 됩니다
	//도서명 저자 가격 내용
	String sql = "select * from db01201 where bname=?";
	psmt = con.prepareStatement(sql);
	psmt.setString(1, bname);
	rs = psmt.executeQuery();//도서명이 같은 자료 1개를 불러왔다
	if(rs.next()) {
		bname = rs.getString(1);
		writer = rs.getString(2);
		price = rs.getInt(3);
		bigo = rs.getString(4);
	}
	%>
	<form action="update2.jsp" method="post">
		<table border="1" align="center">
			<caption>수정사항</caption>
			<tr>
				<th>도서명</th>
				<td><input type="text" name="bname" value="<%=bname%>" readonly></td>
			</tr>
			<tr>
				<th>저자</th>
				<td><input type="text" name="writer" value="<%=writer%>" readonly></td>
			</tr>
			<tr>
				<th style="color:red">수정가격</th>
				<td><input type="text" name="price" value="<%=price%>"></td>
			</tr>
			<tr>
				<th style="color:red">수정내용</th>
				<td><textarea rows="10" cols="30" name="bigo"><%=bigo%></textarea></td>
			</tr>
			<tr>
				<td colspan="3" align="center">
					<input type="submit" value="수정완료">
					<input type="reset" value="초기화">
					<a href="menu.jsp"><input type="button" value="메뉴"></a>
				</td>
			</tr>
		</table>
	</form>
</body>
</html>

 

<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<%@ include file="dbcon.jsp" %>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>Insert title here</title>
</head>
<body>
	<%!
	//수정폼에서 수정된 자료를 넘겨받음
	String bname,writer,bigo;
	int price;
	%>
	<%
	request.setCharacterEncoding("utf-8");
	bname = request.getParameter("bname");
	writer = request.getParameter("writer");
	price = Integer.parseInt(request.getParameter("price"));
	bigo = request.getParameter("bigo");
	String sql = "update db01201 set price=?,bigo=? where bname=?";
	psmt = con.prepareStatement(sql);
	psmt.setInt(1, price);
	psmt.setString(2, bigo);
	psmt.setString(3, bname);
	int n = psmt.executeUpdate();
	if(n==1) {
		response.sendRedirect("menu.jsp");
	}
	%>
</body>
</html>
<%@page import="day7_DB1.BookDAO"%>
<%@page import="day7_DB1.BookDTO"%>
<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>Insert title here</title>
</head>
<body>
	<%!
	String bname,writer,bigo;
	int price;
	%>
	<%
	request.setCharacterEncoding("utf-8");
	bname = request.getParameter("bname");
	writer = request.getParameter("writer");
	price = Integer.parseInt(request.getParameter("price"));
	bigo = request.getParameter("bigo");
	BookDTO dto = new BookDTO();
	dto.setBname(bname);
	dto.setWriter(writer);
	dto.setPrice(price);
	dto.setBigo(bigo);
	BookDAO dao = new BookDAO();
	dao.insert(dto);
	response.sendRedirect("menu.jsp");
	%>
</body>
</html>

 

package day7_DB1;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

public class BookDAO { //기능들의 모임
	Connection con;
	PreparedStatement psmt;
	ResultSet rs;
	String  driver="oracle.jdbc.driver.OracleDriver";
	String url="jdbc:oracle:thin:@localhost:1521:xe";
	public BookDAO() throws ClassNotFoundException {
		Class.forName(driver);
		try {
			con=DriverManager.getConnection(url, "ezen", "12345");
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
	public void insert(BookDTO dto) { //자료들을 db에 저장기능.메소드
		String bname = dto.getBname();
		String writer = dto.getWriter();
		int price = dto.getPrice();
		String bigo = dto.getBigo();
		String sql = "insert into db01201 values(?,?,?,?)";
		try {
			psmt = con.prepareStatement(sql);
			psmt.setString(1, bname);
			psmt.setString(2, writer);
			psmt.setInt(3, price);
			psmt.setString(4, bigo);
			psmt.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
}
<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>Insert title here</title>
</head>
<body>
	<br><br><br>
	<div align="center">
		<a href = "input.jsp"><button>성적입력</button></a>
		<a href = "out.jsp"><button>성적출력</button></a>
	</div>
</body>
</html>

 

<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>Insert title here</title>
</head>
<body>
	<form action="save.jsp" method="post">
		<table border="1" align="center">
			<caption>회원성적입력</caption>
			<tr>
				<th>이름</th>
				<td><input type="text" name="name"></td>
			</tr>
			<tr>
				<th>국어</th>
				<td><input type="text" name="kor"></td>
			</tr>
			<tr>
				<th>영어</th>
				<td><input type="text" name="eng"></td>
			</tr>
			<tr>
				<th>수학</th>
				<td><input type="text" name="mat"></td>
			</tr>
			<tr>
				<td colspan="3" align="center">
					<input type="submit" value="전송">
					<input type="reset" value="초기화">
					<a href = "menu.jsp">
					<input type="button" value="메뉴"></a>
				</td>
			</tr>
		</table>
	</form>
</body>
</html>

 

<%@page import="day7_scoreDTO.ScoreDAO"%>
<%@page import="day7_scoreDTO.ScoreDTO"%>
<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>Insert title here</title>
</head>
<body>
	<%!
	String name;
	int kor,eng,mat,tot;
	%>
	
	<%
	request.setCharacterEncoding("utf-8");
	name = request.getParameter("name");
	kor = Integer.parseInt(request.getParameter("kor"));
	eng = Integer.parseInt(request.getParameter("eng"));
	mat = Integer.parseInt(request.getParameter("mat"));
	tot = kor+eng+mat;
	ScoreDTO dto = new ScoreDTO(name,kor,eng,mat,tot);
	ScoreDAO dao = new ScoreDAO();
	dao.insert(dto);
	response.sendRedirect("menu.jsp");
	%>
</body>
</html>

 

<%@page import="java.sql.ResultSet"%>
<%@page import="day7_scoreDTO.ScoreDAO"%>
<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<!DOCTYPE html>
<html>
<head>
<style type="text/css">
	th,td {
		text-align: center;
	}
</style>
<meta charset="UTF-8">
<title>Insert title here</title>
</head>
<body>
	<%!
	String name;
	int kor,eng,mat,tot;
	%>
	<%
	ScoreDAO dao = new ScoreDAO();
	ResultSet rs = dao.out();
	%>
	<table border="1" align="center">
		<tr>
			<th>이름</th><th>국어</th><th>영어</th>
			<th>수학</th><th>총점</th>
		</tr>
	<%
	while(rs.next()) {
		name = rs.getString(1);
		kor = rs.getInt(2);
		eng = rs.getInt(3);
		mat = rs.getInt(4);
		tot = rs.getInt(5);
	%>
		<tr>
			<td><%=name%></td><td><%=kor%></td><td><%=eng%></td>
			<td><%=mat%></td><td><%=tot%></td>
		</tr>
	<%
	}
	%>
	</table>
	<div align="center">
		<a href = "menu.jsp"><button>메뉴</button></a>
	</div>
</body>
</html>

 

package day7_scoreDTO;

public class ScoreDTO {
	String name;
	int kor,eng,mat,tot;
	public ScoreDTO() {}
	public ScoreDTO(String name, int kor, int eng, int mat, int tot) {
		super();
		this.name = name;
		this.kor = kor;
		this.eng = eng;
		this.mat = mat;
		this.tot = tot;
	}
	public String getName() {
		return name;
	}
	public void setName(String name) {
		this.name = name;
	}
	public int getKor() {
		return kor;
	}
	public void setKor(int kor) {
		this.kor = kor;
	}
	public int getEng() {
		return eng;
	}
	public void setEng(int eng) {
		this.eng = eng;
	}
	public int getMat() {
		return mat;
	}
	public void setMat(int mat) {
		this.mat = mat;
	}
	public int getTot() {
		return tot;
	}
	public void setTot(int tot) {
		this.tot = tot;
	}
}

 

package day7_scoreDTO;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

public class ScoreDAO {
	Connection con;
	PreparedStatement psmt;
	ResultSet rs;
	String driver = "oracle.jdbc.driver.OracleDriver";
	String url = "jdbc:oracle:thin:@localhost:1521:xe";
	
	public ScoreDAO() throws ClassNotFoundException {
		Class.forName(driver);
		try {
			con=DriverManager.getConnection(url, "ezen", "12345");
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
	public void insert(ScoreDTO dto) {
		String name = dto.getName();
		int kor = dto.getKor();
		int eng = dto.getEng();
		int mat = dto.getMat();
		int tot = dto.getTot();
		String sql = "insert into score120 values (?,?,?,?,?)";
		try {
			psmt = con.prepareStatement(sql);
			psmt.setString(1, name);
			psmt.setInt(2, kor);
			psmt.setInt(3, eng);
			psmt.setInt(4, mat);
			psmt.setInt(5 ,tot);
			psmt.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
	public ResultSet out() {
		//자료만 뽑아내서 JSP로 돌려줘서 거기서 출력
		String sql = "select * from score120";
		try {
			psmt = con.prepareStatement(sql);
			rs = psmt.executeQuery();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return rs;
		
	}
}

 

'JSP' 카테고리의 다른 글

230126_DB  (0) 2023.01.26
230125_DB  (0) 2023.01.25
230119_DB  (0) 2023.01.19
230118_DB  (0) 2023.01.18
230117_기본  (0) 2023.01.17

관련글

댓글

티스토리툴바