DATABASE
(JSP)DB연동 login ,join,select,delete
김만식이
2020. 9. 21. 21:59
<?xml version="1.0" encoding="UTF-8"?>
<Context>
<Resource name="jdbc/OracleDB"
auth="Container"
driverClassName="oracle.jdbc.driver.OracleDriver"
type="javax.sql.DataSource"
url="jdbc:oracle:thin:@localhost:1521:xe"
username="hr"
password="hr"
maxActive="20"
maxIdle="10"
maxWait="-1"
/>
</Context>context.xml
<%@ page language="java" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8"%>
<%@ page import="java.sql.*"%>
<%@ page import="javax.sql.*"%>
<%@ page import="javax.naming.*"%>
<!DOCTYPE html>
<html>
<head>
<%
response.sendRedirect("loginFrom.jsp");
%>
<meta charset="UTF-8">
<title>Insert title here</title>
</head>
<body>
</body>
</html>main.jsp
<%@ page language="java" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8"%>
<!DOCTYPE html>
<html>
<head>
<style>
li{
list-style: none;
}
font {
font-weight: 900;
}
</style>
<meta charset="UTF-8">
<title>Insert title here</title>
</head>
<body>
<form action="loginProcess.jsp" method="post" >
<h1 style="text-align:center">로그인 ver.1</h1>
<div style="text-align: center">
<hr/>
<ul>
<li><font>아이디:</font><input type="text" name="userId"></li>
<li><font>비밀번호:</font><input type="password" name="userPw"></li>
<li><input type="submit" value="로그인">
<input type="button" value="회원가입" onclick="location='joinForm.jsp'">
</li>
</ul>
</div>
</form>
</body>
</html>loginFrom.jsp
<%@ page language="java" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8"%>
<%@ page import="java.sql.*"%>
<%@ page import="javax.sql.*"%>
<%@ page import="javax.naming.*"%>
<%
request.setCharacterEncoding("UTF-8");
Connection con = null;
String sql = "insert into testlogin values(?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
try {
Context init = new InitialContext();
DataSource ds = (DataSource) init.lookup("java:comp/env/jdbc/OracleDB");
con = ds.getConnection();
PreparedStatement ppst = con.prepareStatement(sql);
ppst.setString(1,request.getParameter("userId"));
ppst.setString(2,request.getParameter("userPw"));
ppst.setString(3,request.getParameter("userMail"));
ppst.setString(4,request.getParameter("userName"));
ppst.setString(5,request.getParameter("sample6_postcode"));
ppst.setString(6,request.getParameter("sample6_address"));
ppst.setString(7,request.getParameter("sample6_address2"));
ppst.setString(8,request.getParameter("jubun1"));
ppst.setString(9,request.getParameter("jubun2"));
ppst.setString(10,request.getParameter("year"));
ppst.setString(11,request.getParameter("month"));
ppst.setString(12,request.getParameter("day"));
ppst.setString(13,request.getParameter("hobby"));
ppst.setString(14,request.getParameter("intro"));
ppst.executeQuery();
} catch (Exception e) {
e.printStackTrace();
}
%>
<script>
alert("회원가입이 완료 되었습니다.");
location.href="loginFrom.jsp";
</script>
joinProcess.jsp
<%@ page language="java" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8"%>
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<meta name="viewport" content="width=device-width, initial-scale=1.0">
<title>Document</title>
</head>
<style>
th {
background-color: #A9BCF5;
}
table,
th,
tr,
td {
border: 1px solid;
}
</style>
<script type="text/javascript">
//비밀번호 확인.
function checkpass(){
var objuserPw = document.getElementById("userPw");
var objusePwConfirm = document.getElementById("userPwConfirm");
if(objuserPw.value != objusePwConfirm.value || objuserPw.value == ""){
alert("패스워드가 다릅니다. 다시입력해주세요")
objuserPw.value = "";
objusePwConfirm.value = "";
objuserPw.focus();
return false;
}else {
alert("패스워드가 일치합니다.");
return true;
}
}
</script>
<script type="text/javascript">
function joininsert() {
//정규식
var re = /^[a-zA-Z0-9]{4,12}$/ // 아이디와 패스워드가 적합한지 검사할 정규식
var re2 = /^[0-9a-zA-Z]([-_.]?[0-9a-zA-Z])*@[0-9a-zA-Z]([-_.]?[0-9a-zA-Z])*.[a-zA-Z]{2,3}$/i;
var objuserId = document.getElementById("userId");
var objuserPw = document.getElementById("userPw");
var objusePwConfirm = document.getElementById("userPwConfirm");
var objMail = document.getElementById("userMail");
var objuserName = document.getElementById("userName");
var objhobby = document.getElementsByName("hobby");
var intro = document.getElementById("intro");
//address
var address1 = document.getElementById("sample6_postcode");
var address2 = document.getElementById("sample6_address");
var address3 = document.getElementById("sample6_address2");
//주민 번호
var objjubun1 = document.getElementById("jubun1"); //주민번호 앞자리
var objjubun2 = document.getElementById("jubun2"); // 주민번호 뒷자리
var objArr1 = new Array(); //주민번호 6개
var objArr2 = new Array(); //주민번호 뒤 7개
if(objuserId.value == ""){ //아이디 공백일때.
alert("id를 입력해주세요.");
objuserId.focus();
return false;
}
else if(re.test(objuserId.value) != true){
alert("id 조건이 맞지않소.");
objuserId.value = ""; //초기화
objuserId.focus();
return false;
}
else if(objuserPw.value == ""){ //패스워드 공백일때.
alert("password를 입력해주세요.")
objuserPw.focus();
return false;
}
else if(objusePwConfirm.value == ""){ // 패스워드 공백일때.
alert("password를 입력해주세요.")
objusePwConfirm.focus();
return false;
}
else if(objMail.value == ""){ //이메일 공백
alert("email를 입력해주세요.")
objMail.focus();
return false;
}
else if(re2.test(objMail.value) != true){
alert("이메일이 올바르지 않습니다.")
objmail.value == "";
objMail.focus();
return false;
}
else if(objuserName.value == ""){ //이름 공백
alert("name를 입력해주세요.")
objuserName.focus();
return false;
}
//주소 4종 세트
else if(address1.value == ""){
alert("우편번호를 입력해주세요.")
address1.focus();
return false;
}
else if(address2.value == ""){
alert("도로명 주소");
address2.focus();
return false;
}
/* else if(objbranchaddress.value == ""){
alert("지번 주소");
objbranchaddress.focus();
return false;
} */
else if(address3.value == ""){
alert("상세 주소");
address3.focus();
return false;
}
/* //주민번호.
else if(objjubun1.value == true){
for(var i = 0; i < objjubun1.length; i++){ //앞자리 배열에 담기
objArr1 = objjubun1.value.charAt(i);
}
for(var i = 0; i< objjubun2.length; i++){ //뒷자리 배열에 담기/
objArr2 = objjubun2.value.charAt(i);
}
}
else if(objjubun1.objjubun1 == "" && objjubun2.value == ""){
alert("주민등록번호를 다시 입력해주세요.");
objjubun1.focus();
return false;
}
else if(objArr1[0] == 1 || objArr1[0] == 2){ //남자는 1 여자는 2
var y = "19"+ parseInt(objArr1.value.substring(0,2));
var m = parseInt(objArr1.value.substring(2,4));
var d = parseInt(objArr1.value.substring(4,6));
join.year.value = y;
join.month.value = m;
join.date.value = d;
}
else if(objArr1[0] == 3 || objArr1[0] == 4){ //남자는 1 여자는 2
var y = "20"+ parseInt(objArr1.value.substring(0,2));
var m = parseInt(objArr1.value.substring(2,4));
var d = parseInt(objArr1.value.substring(4,6));
join.year.value = y;
join.month.value = m;
join.date.value = d;
} */
//게시판
else if(intro.value == ""){
alert("자기소개를 입력해주세요");
intro.focus();
return false;
}
//관심분야
var hobby = new Array();
for(var i=0; i<objhobby.length; i++){
{
if(objhobby[i].checked)
{
hobby+=objhobby[i].value; //관심분야 저장
}
}
}
if(hobby.length < 1) //입력이 안될때 1
{
alert("관심분야를 선택해주세요.");
return false;
}
alert("회원가입이 완료되었습니다.");
document.join.submit();
}
</script>
<script src="https://t1.daumcdn.net/mapjsapi/bundle/postcode/prod/postcode.v2.js"></script>
<script src="http://dmaps.daum.net/map_js_init/postcode.v2.js"></script>
<script>
function sample6_execDaumPostcode() {
new daum.Postcode({
oncomplete: function(data) {
// 팝업에서 검색결과 항목을 클릭했을때 실행할 코드를 작성하는 부분.
// 각 주소의 노출 규칙에 따라 주소를 조합한다.
// 내려오는 변수가 값이 없는 경우엔 공백('')값을 가지므로, 이를 참고하여 분기 한다.
var fullAddr = ''; // 최종 주소 변수
var extraAddr = ''; // 조합형 주소 변수
// 사용자가 선택한 주소 타입에 따라 해당 주소 값을 가져온다.
if (data.userSelectedType === 'R') { // 사용자가 도로명 주소를 선택했을 경우
fullAddr = data.roadAddress;
} else { // 사용자가 지번 주소를 선택했을 경우(J)
fullAddr = data.jibunAddress;
}
// 사용자가 선택한 주소가 도로명 타입일때 조합한다.
if(data.userSelectedType === 'R'){
//법정동명이 있을 경우 추가한다.
if(data.bname !== ''){
extraAddr += data.bname;
}
// 건물명이 있을 경우 추가한다.
if(data.buildingName !== ''){
extraAddr += (extraAddr !== '' ? ', ' + data.buildingName : data.buildingName);
}
// 조합형주소의 유무에 따라 양쪽에 괄호를 추가하여 최종 주소를 만든다.
fullAddr += (extraAddr !== '' ? ' ('+ extraAddr +')' : '');
}
// 우편번호와 주소 정보를 해당 필드에 넣는다.
document.getElementById('sample6_postcode').value = data.zonecode; //5자리 새우편번호 사용
document.getElementById('sample6_address').value = fullAddr;
// 커서를 상세주소 필드로 이동한다.
document.getElementById('sample6_address2').focus();
}
}).open();
}
</script>
<body>
<form action="joinProcess.jsp" method="post">
<div align="center">
<table>
<tr>
<th colspan="2">회원 기본 정보</th>
</tr>
<tr>
<td align="center">아이디 : </td>
<td>
<input type="text" id="userId" name="userId">4~12자의 영문 대소문자와 숫자로만 입력
</td>
</tr>
<tr>
<td align="center">비밀번호 : </td>
<td>
<input type="password" id="userPw" name="userPw">4~12자의 영문 대소문자와 숫자로만 입력
</td>
</tr>
<tr>
<td align="center">비밀번호 확인 : </td>
<td>
<input type="password" id="userPwConfirm" name="userPwConfirm">
<input type="button" id="userPwConfirm" name="userPwConfirm" value="확인" onclick="checkpass()">
</td>
</tr>
<tr>
<td align="center">메일주소 : </td>
<td>
<input type="text" id="userMail" name="userMail">예) id@domain.com
</td>
</tr>
<tr>
<td align="center">이름 : </td>
<td>
<input type="text" id="userName" name="userName">
</td>
</tr>
<tr>
<th colspan="2">개인 신상 정보</th>
</tr>
<tr>
<td align="center">우편번호 : </td>
<td>
<input type="text" id="sample6_postcode" placeholder="우편번호">
<input type="button" onclick="sample6_execDaumPostcode()" value="우편번호 찾기"><br>
<input type="text" id="sample6_address" placeholder="주소">
<input type="text" id="sample6_address2" placeholder="상세주소">
</td>
</tr>
<tr>
<td align="center">주민등록번호 : </td>
<td>
<input type="text" id="jubun1" name="jubun1" style="width: 72px;"> -
<input type="text" id="jubun2" name="jubun2" style="width: 72px;"> 예) 123456-1234567
</td>
</tr>
<tr>
<td align="center">생일 : </td>
<td>
<input type="text" id="year" name="year" style="width: 70px;" readonly="readonly">년
<input type="text" id="month" name="month" style="width: 20px;" readonly="readonly">월
<input type="text" id="date" name="date" style="width: 20px;" readonly="readonly">일
</td>
</tr>
<tr>
<td align="center">관심분야 : </td>
<td>
<input type="checkbox" name="hobby" value="computer">컴퓨터
<input type="checkbox" name="hobby" value="internet">인터넷
<input type="checkbox" name="hobby" value="travle">여행
<input type="checkbox" name="hobby" value="movie">영화감상
<input type="checkbox" name="hobby" value="music">음악감상
</td>
</tr>
<tr>
<td align="center">자기소개 : </td>
<td>
<textarea id="intro" name="intro" style="width: 450px; height: 200px; resize: none;"></textarea>
</td>
</tr>
<tr>
<td colspan="2" align="center">
<input type="submit" value="회원 가입">
<input type="reset" value="다시 입력">
</td>
</tr>
</table>
</form>
</body>
</html>joinform.jsp(유효성때문에 script 부분은 잠시 주석)
<%@ page language="java" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8"%>
<%@ page import="java.sql.*"%>
<%@ page import="javax.sql.*"%>
<%@ page import="javax.naming.*"%>
<%
request.setCharacterEncoding("UTF-8");
Connection con = null;
String sql = "insert into testlogin values(?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
try {
Context init = new InitialContext();
DataSource ds = (DataSource) init.lookup("java:comp/env/jdbc/OracleDB");
con = ds.getConnection();
PreparedStatement ppst = con.prepareStatement(sql);
ppst.setString(1,request.getParameter("userId"));
ppst.setString(2,request.getParameter("userPw"));
ppst.setString(3,request.getParameter("userMail"));
ppst.setString(4,request.getParameter("userName"));
ppst.setString(5,request.getParameter("sample6_postcode"));
ppst.setString(6,request.getParameter("sample6_address"));
ppst.setString(7,request.getParameter("sample6_address2"));
ppst.setString(8,request.getParameter("jubun1"));
ppst.setString(9,request.getParameter("jubun2"));
ppst.setString(10,request.getParameter("year"));
ppst.setString(11,request.getParameter("month"));
ppst.setString(12,request.getParameter("day"));
ppst.setString(13,request.getParameter("hobby"));
ppst.setString(14,request.getParameter("intro"));
ppst.executeQuery();
} catch (Exception e) {
e.printStackTrace();
}
%>
<script>
alert("회원가입이 완료 되었습니다.");
history.back("loginFrom.jsp");
</script>
loginProcess.jsp
<%@ page language="java" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8"%>
<%@ page import="java.sql.*"%>
<%@ page import="javax.sql.*"%>
<%@ page import="javax.naming.*"%>
<!DOCTYPE html>
<html>
<head>
<style>
ts{
text-align: center;
border: 1px black solid;
}
</style>
<meta charset="UTF-8">
<title>Insert title here</title>
</head>
<body>
<h1>회원정보</h1>
<%
request.setCharacterEncoding("UTF-8");
Connection con = null;
String sql = "select *from testlogin";
try {
Context init = new InitialContext();
DataSource ds = (DataSource) init.lookup("java:comp/env/jdbc/OracleDB");
con = ds.getConnection();
PreparedStatement ppst = con.prepareStatement(sql);
ResultSet rs = ppst.executeQuery();
while (rs.next()) {
String userId = rs.getString(1);
out.write("<form action='Member_info.jsp' method='get'>");
out.write("<table class='ts'>");
out.write("<tr>");
out.write("<td>회원아이디 :");
out.write("<a href='Member_info.jsp?userId="+userId+"'>");
out.write(userId);
out.write("</a></td>");
out.write("</tr>");
out.write("</table>");
out.write("</form>");
}
rs.close();
} catch (Exception e) {
e.printStackTrace();
}
%>
</body>
</html>Member_list.jsp
회원리스트조회 (admin만)
<%@ page language="java" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8"%>
<%@ page import="java.sql.*"%>
<%@ page import="javax.sql.*"%>
<%@ page import="javax.naming.*"%>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>Insert title here</title>
</head>
<body>
<%
request.setCharacterEncoding("UTF-8");
Connection con = null;
String userId1 =request.getParameter("userId");
String sql = "select *from testlogin where userid = ? ";
try {
Context init = new InitialContext();
DataSource ds = (DataSource) init.lookup("java:comp/env/jdbc/OracleDB");
con = ds.getConnection();
PreparedStatement ppst = con.prepareStatement(sql);
ppst.setString(1,request.getParameter("userId"));
ResultSet rs = ppst.executeQuery();
while (rs.next()) {
String userId = rs.getString(1);
String userPw = rs.getString(2);
String userMail = rs.getString(3);
String userName = rs.getString(4);
String sample6_postcode = rs.getString(5);
String sample6_address = rs.getString(6);
String sample6_address2 = rs.getString(7);
String jubun1 = rs.getString(8);
String jubun2 = rs.getString(9);
String year = rs.getString(10);
String month = rs.getString(11);
String day = rs.getString(12);
String hobby = rs.getString(13);
String intro = rs.getString(14);
out.print("<table border='1'>");
out.print("<tr>");
out.print("<td>아이디:"+userId+"<td/>");
out.print("</tr>");
out.print("<tr>");
out.print("<td>비밀번호:"+userPw+"<td/>");
out.print("</tr>");
out.print("<tr>");
out.print("<td>메일:"+userMail+"<td/>");
out.print("</tr>");
out.print("<tr>");
out.print("<td>이름:"+userName+"<td/>");
out.print("</tr>");
out.print("<tr>");
out.print("<td>주소:"+sample6_postcode+sample6_address +sample6_address2+"<td/>");
out.print("</tr>");
out.print("<tr>");
out.print("<td>주민번호:"+jubun1+"-"+jubun2+"</td>");
out.print("</tr>");
out.print("<tr>");
out.print("<td>생년월일:"+year+"년"+month+"월"+day+"일"+"</td>");
out.print("</tr>");
out.print("<tr>");
out.print("<td>취미:"+hobby+"</td>");
out.print("</tr>");
out.print("<tr>");
out.print("<td>자기소개:"+intro+"</td>");
out.print("</tr>");
out.print("<table>");
}
rs.close();
out.write("<a href='Member_delete.jsp?userId="+userId1+"'>");
out.write("회원삭제하기</a>");
} catch (Exception e) {
e.printStackTrace();
}
%>
<a href="Member_delete.jsp">회원삭제하기</a>
</body>
</html>Member_info.jsp
회원정보조회 (admin만)
<%@ page language="java" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8"%>
<%@ page import="java.sql.*"%>
<%@ page import="javax.sql.*"%>
<%@ page import="javax.naming.*"%>
<%
request.setCharacterEncoding("UTF-8");
Connection con = null;
String id=(String) request.getParameter("userId");
String sql = "delete from testlogin where userId = ? ";
try {
Context init = new InitialContext();
DataSource ds = (DataSource) init.lookup("java:comp/env/jdbc/OracleDB");
con = ds.getConnection();
PreparedStatement ppst = con.prepareStatement(sql);
ppst.setString(1,id);
ppst.executeUpdate();
} catch (Exception e) {
e.printStackTrace();
}
%>
<script>
alert("삭제 완료");
history.back();
</script>Member_delete.jsp
회원삭제 (admin만)
create table testlogin (
userId varchar(50) , --아이디
userPw varchar(50) , --비밀번호
userMail varchar(50), --메일
userName varchar(50), --이름
sample6_postcode varchar(50), --주소1
sample6_address varchar(50), --주소2
sample6_address2 varchar(50), --주소3
jubun1 number, --주민 앞자리
jubun2 number, --주민 뒷자리
year number, --년
month number, -- 월
day number, --일
hobby varchar(50),
intro varchar(300)
);
insert into testlogin (userId,userPw) VALUES('admin','1234');
DB 연습테이블