본문 바로가기
JSP

230119_DB

by 경 훈 2023. 1. 19.

정의어 : create ... alter    drop

조작어 : 

삽입 : insert into 테이블명 values(?,?,?,?,?)

          insert into 테이블명(필드명1,필드명2...) values(?,?,...)

검색 : select 필드명1,필드명2 from 테이블명

          select * from 테이블명

갱신 : update ...

삭제 : delete ...

 

PK 기본키 : 데이터 중복 불가

널 : 알수없는 값

 

<%@ 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 = "form.jsp"><input type="button" value="자료입력"></a><br>
		<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>
<style type="text/css">
	th,td {
		text-align: center;
	}
</style>
<meta charset="UTF-8">
<title>Insert title here</title>
</head>
<body>
	<form action="save.jsp" method="post">
		<table border="1" align="center"> 
			<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>
				<th>취미</th>
				<td>
					<input type="checkbox" name="hobby" value="운동">운동
					<input type="checkbox" name="hobby" value="독서">독서
					<input type="checkbox" name="hobby" value="맛집">맛집
					<input type="checkbox" name="hobby" value="여행">여행
					<input type="checkbox" name="hobby" value="영화">영화
				</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="java.text.ChoiceFormat"%>
<%@page import="java.sql.DriverManager"%>
<%@page import="java.sql.ResultSet"%>
<%@page import="java.sql.PreparedStatement"%>
<%@page import="java.sql.Connection"%>
<%@ 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;
	double avg;
	String hak;
	String [] hobbys;
	Connection con;//연결개체 선언
	PreparedStatement psmt;//쿼리 실행문 객체
	ResultSet rs;//쿼리문이 실행되면 결과 저장 객체
	String driver = "oracle.jdbc.driver.OracleDriver";
	String url = "jdbc:oracle:thin:@localhost:1521:xe";
	double [] a = {0,60,70,80,90};
	String [] b = {"F","D","C","B","A"};
	ChoiceFormat cf = new ChoiceFormat(a,b);
	%>
	<%
	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"));
	hobbys = request.getParameterValues("hobby");//넘겨받은 취미가 hobbys배열에 저장
	tot = kor+eng+mat;
	avg = (double)tot/3;
	hak = cf.format(avg);
	String hb="";
	for(int i=0;i<hobbys.length;i++) { //hobbys에 저장된 값들을 1개의 문자열 만드는것
		hb=hb+hobbys[i]+",";
	}
	Class.forName(driver);
	con=DriverManager.getConnection(url, "ezen", "12345");
	String sql = "insert into db01191 values(?,?,?,?,?,?,?,?)";
	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.setDouble(6, avg);
	psmt.setString(7, hak);
	psmt.setString(8, hb);
	int n = psmt.executeUpdate();
	if(n==1) {
		response.sendRedirect("menu.jsp");
	}
	%>
</body>
</html>

 

<%@page import="java.sql.DriverManager"%>
<%@page import="java.sql.ResultSet"%>
<%@page import="java.sql.PreparedStatement"%>
<%@page import="java.sql.Connection"%>
<%@ 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;
	double avg;
	String hak;
	String hobby;
	Connection con;
	PreparedStatement psmt;
	ResultSet rs;
	String driver = "oracle.jdbc.driver.OracleDriver";
	String url = "jdbc:oracle:thin:@localhost:1521:xe";
	%>
	<%
	request.setCharacterEncoding("utf-8");
	Class.forName(driver);
	con=DriverManager.getConnection(url, "ezen", "12345");
	String sql = "select * from db01191";
	psmt = con.prepareCall(sql);
	rs = psmt.executeQuery();
	%>
	<table border="1" align="center" width="600px">
		<tr>
			<th>이름</th><th>국어</th><th>영어</th><th>수학</th>
			<th>총점</th><th>평균</th><th>학점</th><th>취미</th>
		</tr>
	<%
	while(rs.next()) {
		name = rs.getString("name");
		kor = rs.getInt("kor");
		eng = rs.getInt("eng");
		mat = rs.getInt("mat");
		tot = rs.getInt("tot");
		avg = rs.getDouble("avg");
		hak = rs.getString("hak");
		hobby = rs.getString("hobby");
	%>
		<tr>
			<td><%=name%></td><td><%=kor%></td><td><%=eng%></td><td><%=mat%></td>
			<td><%=tot%></td><td><%=avg%></td><td><%=hak%></td><td><%=hobby%></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"%>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>Insert title here</title>
</head>
<body>
	<br><br><br>
	<div align="center">
	<a href = "form.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>
<style type="text/css">
	th,td {
		text-align: center;
	}
</style>
<meta charset="UTF-8">
<title>Insert title here</title>
</head>
<body>
	<form action="save.jsp" method="post">
		<table border="1" align="center">
			<tr>
				<th>아이디</th>
				<td><input type="text" name="id"></td>
			</tr>		
			<tr>
				<th>이름</th>
				<td><input type="text" name="name"></td>
			</tr>		
			<tr>
				<th>전화번호</th>
				<td><input type="text" name="phone"></td>
			</tr>		
			<tr>
				<th>나이</th>
				<td><input type="text" name="age"></td>
			</tr>		
			<tr>
				<th>성별</th>
				<td>
					<input type="radio" name="gender" value="남자">남자
					<input type="radio" name="gender" value="여자">여자
				</td>
			</tr>		
			<tr>
				<th>취미</th>
				<td>
					<input type="checkbox" name="hobby" value="운동">운동
					<input type="checkbox" name="hobby" value="등산">등산
					<input type="checkbox" name="hobby" value="낚시">낚시
					<input type="checkbox" name="hobby" value="게임">게임
					<input type="checkbox" name="hobby" value="영화">영화				
				</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="java.sql.DriverManager"%>
<%@page import="java.sql.ResultSet"%>
<%@page import="java.sql.PreparedStatement"%>
<%@page import="java.sql.Connection"%>
<%@ 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 id,name,phone,gender;
	int age;
	String [] hobbys;
	Connection con;
	PreparedStatement psmt;
	ResultSet rs;
	String driver = "oracle.jdbc.driver.OracleDriver";
	String url = "jdbc:oracle:thin:@localhost:1521:xe";
	%>
	<%
	request.setCharacterEncoding("utf-8");
	id = request.getParameter("id");
	name = request.getParameter("name");
	phone = request.getParameter("phone");
	gender = request.getParameter("gender");
	age = Integer.parseInt(request.getParameter("age"));
	hobbys = request.getParameterValues("hobby");
	String hb = "";
	for(int i=0;i<hobbys.length;i++) {
		if((i+1)==hobbys.length) {
			hb = hb+hobbys[i];
		}
		else {
			hb = hb+hobbys[i]+",";
		}
	}
	Class.forName(driver);
	con=DriverManager.getConnection(url, "ezen", "12345");
	String sql = "insert into db01192 values(?,?,?,?,?,?)";
	psmt = con.prepareStatement(sql);
	psmt.setString(1, id);
	psmt.setString(2, name);
	psmt.setString(3, phone);
	psmt.setInt(4, age);
	psmt.setString(5, gender);
	psmt.setString(6, hb);
	int n = psmt.executeUpdate();
	if(n==1) {
		response.sendRedirect("menu.jsp");
	}
	%>
</body>
</html>

 

<%@page import="java.sql.DriverManager"%>
<%@page import="java.sql.ResultSet"%>
<%@page import="java.sql.PreparedStatement"%>
<%@page import="java.sql.Connection"%>
<%@ 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 id,name,phone,gender,hobby;
	int age;
	Connection con;
	PreparedStatement psmt;
	ResultSet rs;
	String driver = "oracle.jdbc.driver.OracleDriver";
	String url = "jdbc:oracle:thin:@localhost:1521:xe";
	%>
	
	<%
	request.setCharacterEncoding("utf-8");
	Class.forName(driver);
	con=DriverManager.getConnection(url, "ezen", "12345");
	String sql = "select * from db01192";
	psmt = con.prepareStatement(sql);
	rs = psmt.executeQuery();
	%>
	<table border="1" align="center" width="800px">
		<tr>
			<th>아이디</th><th>이름</th><th>전화번호</th>
			<th>나이</th><th>성별</th><th>취미</th><th>비고</th>
		</tr>
	<%
	while(rs.next()) {
		id = rs.getString(1);
		name = rs.getString(2);
		phone = rs.getString(3);
		age = rs.getInt(4);
		gender = rs.getString(5);
		hobby = rs.getString(6);
	%>
		<tr>
			<td><%=id%></td><td><%=name%></td><td><%=phone%></td>
			<td><%=age%></td><td><%=gender%></td><td><%=hobby%></td>
			<td>
				<a href = "delete.jsp?id=<%=id%>"><input type="button" value="삭제"></a>
				<a href = ""><input type="button" value="수정"></a>
			</td>
		</tr>
	<%
	}
	%>
	</table>
	<div align="center">
	<a href="menu.jsp"><input type="button" value="메뉴"></a>
	</div>
</body>
</html>

 

<%@page import="java.sql.DriverManager"%>
<%@page import="java.sql.ResultSet"%>
<%@page import="java.sql.PreparedStatement"%>
<%@page import="java.sql.Connection"%>
<%@ 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 did;
	Connection con;
	PreparedStatement psmt;
	ResultSet rs;
	String driver = "oracle.jdbc.driver.OracleDriver";
	String url = "jdbc:oracle:thin:@localhost:1521:xe";
	%>
	<%
	request.setCharacterEncoding("utf-8");
	did=request.getParameter("id");
	Class.forName(driver);
	con=DriverManager.getConnection(url, "ezen", "12345");
	String sql = "delete from db01192 where id =?";
	psmt = con.prepareStatement(sql);
	psmt.setString(1, did);
	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"%>
<!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="id입력"></a>
		<a href = "out.jsp"><input type="button" value="id출력"></a>
	</div>
</body>
</html>

 

<%@ 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>
	<form action="save.jsp" method="post">
		<table border="1" align="center">
			<caption>아이디 나이 입력폼</caption>
			<tr>
				<th>아이디</th>
				<td><input type="text" name="id"></td>		
			</tr>
			<tr>
				<th>나이</th>
				<td><input type="text" name="age"></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="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"%>
<%@ include file="dbcon.jsp"%>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>Insert title here</title>
</head>
<body>
	<%!
	String id;
	int age;
	%>
	
	<%
	request.setCharacterEncoding("utf-8");
	id=request.getParameter("id");
	age=Integer.parseInt(request.getParameter("age"));
	String sql = "insert into iddata values(?,?)";
	psmt = con.prepareStatement(sql);
	psmt.setString(1, id);
	psmt.setInt(2, age);
	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 id;
	int age; 
	%>
	
	<%
	request.setCharacterEncoding("utf-8");
	String sql = "select * from iddata";
	psmt = con.prepareStatement(sql);
	rs = psmt.executeQuery();
	%>
	<table border="1" align="center" width="500px">
		<tr>
			<th>이름</th><th>나이</th><th>비고</th>
		</tr>
	<% 
	while(rs.next()) {
		id = rs.getString(1);
		age = rs.getInt(2);
	%>
		<tr>
			<td><%=id%></td><td><%=age%></td>
			<td colspan="2">
				<a href="delete.jsp?did=<%=id%>"><input type="button" value="삭제"></a>
				<a href="modify1.jsp?mid=<%=id%>"><input type="button" value="수정"></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 did;
	%>
	<%
	request.setCharacterEncoding("utf-8");
	did=request.getParameter("did");
	String sql = "delete from iddata where id=?";
	psmt = con.prepareStatement(sql);
	psmt.setString(1, did);
	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 sid;
	int age;
	%>
	<%
	request.setCharacterEncoding("utf-8");
	sid=request.getParameter("mid"); // 넘겨받는 id,,, 수정을 요하는 id
	String sql = "select * from iddata where id=?";
	psmt = con.prepareStatement(sql);
	psmt.setString(1, sid);
	rs = psmt.executeQuery();
	if(rs.next()) {
		sid = rs.getString(1);
		age = rs.getInt(2);
	}
	%>
	<form action="modify2.jsp" method="post">
		<table border="1" align="center">
			<tr>
				<th>아이디</th>
				<td><input type="text" name="id" value=<%=sid%> readonly="readonly"></td>
			</tr>		
			<tr>
				<th>나이</th>
				<td><input type="text" name="age" value=<%=age%>></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 id;
	int age;
	%>
	
	<%
	request.setCharacterEncoding("utf-8");
	id=request.getParameter("id");
	age=Integer.parseInt(request.getParameter("age"));
	String sql = "update iddata set age=? where id=?";
	psmt = con.prepareStatement(sql);
	psmt.setInt(1, age);
	psmt.setString(2, id);
	int n = psmt.executeUpdate();
	if(n==1) {
		response.sendRedirect("menu.jsp");
	}
	%>
</body>
</html>

 

'JSP' 카테고리의 다른 글

230125_DB  (0) 2023.01.25
230120_DB  (0) 2023.01.20
230118_DB  (0) 2023.01.18
230117_기본  (0) 2023.01.17
230116_CSS  (1) 2023.01.16

관련글

댓글

티스토리툴바