사용된 DB. 데이터 타입은 varchar2로 통일을 시켰으며 크기는 넉넉하게 주었다.
loginForm.jsp
<%@ 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 name="from" action="loginProcess.jsp">
아이디 : <input type="text" name="id"><br>
비밀번호 : <input type="password" name="pw"><br>
<input type="submit" value ="login"> <a href="joinForm.jsp"><input type="button" value="join"></a>
</form>
</body>
</html>간단하게 아이디,비밀번호를 입력하여 로그인하거나 회원가입을 할 수 있는 페이지이다.
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>
<meta charset="UTF-8">
<title>Insert title here</title>
</head>
<%
request.setCharacterEncoding("utf-8");
String id=request.getParameter("id");
String pw=request.getParameter("pw");
Connection conn = null;
String sql = "select id,pw from personal where id='"+id+"' and pw ='"+pw+"'";
try {
Context init = new InitialContext();
DataSource ds = (DataSource) init.lookup("java:comp/env/jdbc/OracleDB");
conn = ds.getConnection(); //풀방식
PreparedStatement stmt = conn.prepareStatement(sql);
ResultSet rs = stmt.executeQuery();
while(rs.next()){
session.setAttribute("id",id);
response.sendRedirect("main.jsp");
}
rs.close();
} catch (Exception e) {
out.println("fail");
e.printStackTrace();
}
%>
<body>
</body>
</html>loginForm 창에서 넘어온 id와 pw를 활용하여 로그인을 한다.
joinForm.jsp
<%@ page language="java" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8"%>
<%@ page import="java.util.*"%>
<html>
<head>
<title>회원가입 페이지</title>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
<script language="javascript">
function goPopup(){
// 호출된 페이지(jusopopup.jsp)에서 실제 주소검색URL(https://www.juso.go.kr/addrlink/addrLinkUrl.do)를 호출하게 됩니다.
var pop = window.open("./jusoPopup.jsp","pop","width=570,height=420, scrollbars=yes, resizable=yes");
}
function jusoCallBack(roadFullAddr,roadAddrPart1,addrDetail,roadAddrPart2, zipNo){
// 팝업페이지에서 주소입력한 정보를 받아서, 현 페이지에 정보를 등록합니다.
document.form.roadFullAddr.value = roadFullAddr;
document.form.roadAddrPart1.value = roadAddrPart1;
document.form.roadAddrPart2.value = roadAddrPart2;
document.form.addrDetail.value = addrDetail;
document.form.zipNo.value = zipNo;
}
var forRestirct = /^[a-zA-Z0-9]{4,12}$/;
var savePnum;
function chkEvery(){
if(!chkId()){
return false;
}
if(!chkPw()){
return false;
}
if(!chkEmail()){
return false;
}
if(!chkPnum()){
return false;
}
if(!chkHobby()){
return false;
}
alert("welcome");
}
function chkId(){
var saveId = document.getElementById("ID").value;
if(saveId==""){
alert("Must input ID");
return false;
}
if(!forRestirct.test(saveId)){
alert("4~12자의 영문 대문자와 숫자로만 입력");
return false;
}
alert("You can use this ID");
return true;
}
function chkPw(){
var savePw = document.getElementById("pw").value;
var savePwck = document.getElementById("pwck").value;
if(savePw==""|| savePwck==""){
alert("Must input password");
return false;
}
if(savePw==savePwck){
if(!forRestirct.test(savePw)){
alert("4~12자의 영문 대문자와 숫자로만 입력");
return false;
}
}else{
alert("please check your pw & pwck");
return false;
}
alert("You can use this PW");
return true;
}
function chkEmail(){
var saveEmail = document.getElementById("mail").value;
var forRestirctE = /^[a-zA-Z0-9_]+[@]{1}[a-zA-Z0-9_]+[.]{1}[A-Za-z]{1,3}$/;
if(saveEmail==""){
alert("Must input Email");
return false;
}
if(!forRestirctE.test(saveEmail)){
alert("please check your e-mail");
return false;
}
alert("You can use this E-mail");
return true;
}
function chkPnum(){
savePnum = document.getElementById("personalID").value;
var year=savePnum.substring(6,7);
var forRestirctPnum= /^(?:(?:[0-9]{2}(?:0[1-9]|1[0-2]))(?:0[1-9]|[1,2][0-9]|3[0,1])(?:[1-4][0-9]{6}))$/;
if(savePnum==""){
alert("Must input Personal number");
return false;
}
if(!forRestirctPnum.test(savePnum)){
alert("please check your personal number");
return false;
}
if(year==1||year==2){
document.getElementById("year").value = "19"+ savePnum.substring(0,2);
document.getElementById("Month").value = savePnum.substring(2,4);
document.getElementById("Day").value = savePnum.substring(4,6);
}else if(year==3||year==4){
document.getElementById("year").value = "00"+ savePnum.substring(0,2);
document.getElementById("Month").value = savePnum.substring(2,4);
document.getElementById("Day").value = savePnum.substring(4,6);
}
alert("Correct number");
return true;
}
function chkHobby(){
var saveHobby = document.getElementsByName("hobby");
var chk=0;
for(var i=0;i<saveHobby.length;i++){
if(saveHobby[i].checked){
chk++;
}
}
if(chk==0){
alert("please choose your interesting part at least one thing")
return false;
}
return true;
}
</script>
</head>
<body>
<form name="form" action="joinProcess.jsp">
<table align="center" width="600" cellpadding="5" cellspacing="2" bgcolor="FFFFFF" border="2">
<tr align="center">
<td colspan="2" bgcolor="#f0f0f0">회원 기본 정보</td>
</tr>
<tr align="center">
<td>이름 :</td>
<td align="left"><input type="text" id="name" name="name" size="12">
</tr>
<tr align="center">
<td>아이디 :</td>
<td align="left"><input type="text" id="ID" name="id" size="12"><input
type="button" onclick="chkId()" value="ID check"><br>
4~12자의 영문 대문자와 숫자로만 입력</td>
</tr>
<tr align="center">
<td>비밀번호 :</td>
<td align="left"><input type="password" id="pw" name="pw" size="12">
4~12자의 영문 대문자와 숫자로만 입력</td>
</tr>
<tr align="center">
<td>비밀번호 확인 :</td>
<td align="left"><input type="password" id="pwck" size="12">
<input type="button" onclick="chkPw()" value="PW check"></td>
</tr>
<tr align="center">
<th>도로명주소 전체(포맷)</th>
<td><input type="text" id="roadFullAddr" name="roadFullAddr" ><input type="button" onClick="goPopup();" value="팝업" ></td></tr>
<tr><th>도로명주소 </th>
<td><input type="text" id="roadAddrPart1" name="roadAddrPart1" ></td></tr>
<tr><th>고객입력 상세주소</th><td><input type="text" id="addrDetail" name="addrDetail" ></td></tr>
<tr><th>참고주소</th><td><input type="text" id="roadAddrPart2" name="roadAddrPart2" ></td></tr>
<tr><th>우편번호</th><td><input type="text" id="zipNo" name="zipNo" ></td></tr>
<tr align="center">
<td>메일주소 :</td>
<td align="left"><input type="Text" id="mail" name="mail" size="20"><input type="button" onclick="chkEmail()" value="E-mail check"><br>
ex>id@domain.com</td>
</tr>
<tr align="center">
<td colspan="2" bgcolor="#f0f0f0">회원 기본 정보</td>
</tr>
<tr align="center">
<td>주민등록번호 :</td>
<td align="left"><input type="password" id="personalID"
size="13"><input type="button" onclick="chkPnum()"
value="Personal Number check"><br> ex>1234561234567</td>
</tr>
<tr>
<td align="center">생일 :</td>
<td valign="center"><input type="text" id="year" name="year" size="4">년
<select id="Month"name="Month">
<option value="Month"></option>
<option value="01">1</option>
<option value="02">2</option>
<option value="03">3</option>
<option value="04">4</option>
<option value="05">5</option>
<option value="06">6</option>
<option value="07">7</option>
<option value="08">8</option>
<option value="09">9</option>
<option value="10">10</option>
<option value="11">11</option>
<option value="12">12</option>
</select> 월 <select id="Day" name="Day">
<option value="Day"></option>
<option value="01">1</option>
<option value="02">2</option>
<option value="03">3</option>
<option value="04">4</option>
<option value="05">5</option>
<option value="06">6</option>
<option value="07">7</option>
<option value="08">8</option>
<option value="09">9</option>
<option value="10">10</option>
<option value="11">11</option>
<option value="12">12</option>
<option value="13">13</option>
<option value="14">14</option>
<option value="15">15</option>
<option value="16">16</option>
<option value="17">17</option>
<option value="18">18</option>
<option value="19">19</option>
<option value="20">20</option>
<option value="21">21</option>
<option value="22">22</option>
<option value="23">23</option>
<option value="24">24</option>
<option value="25">25</option>
<option value="26">26</option>
<option value="27">27</option>
<option value="28">28</option>
<option value="29">29</option>
<option value="30">30</option>
<option value="31">31</option>
</select> 일</td>
</tr>
<tr align="center">
<td>관심분야 :</td>
<td align="left"><input type="checkbox" name="hobby"
value="computer">computer <input type="checkbox"
name="hobby" value="internet">internet <input
type="checkbox" name="hobby" value="travel">travel <input
type="checkbox" name="hobby" value="movie">movie <input
type="checkbox" name="hobby" value="music listening">music
listening</td>
</tr>
<tr align="center">
<td>자기소개 :</td>
<td align="left"><textarea name="intro" cols="50"
rows="5"></textarea></td>
</tr>
<tr align="center">
<td colspan="2" bgcolor="#ffffff">
<input type="submit" value="회원가입" >
<input type="reset" value="다시 입력">
</td>
</tr>
</table>
</form>
</body>
</html>회원가입 양식이다.
jusoPopup.jsp
<%@ page language="java" contentType="text/html; charset=UTF-8" pageEncoding="UTF-8"%>
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
<title>Insert title here</title>
<%
request.setCharacterEncoding("UTF-8"); //한글깨지면 주석제거
//request.setCharacterEncoding("EUC-KR"); //해당시스템의 인코딩타입이 EUC-KR일경우에
String inputYn = request.getParameter("inputYn");
String roadFullAddr = request.getParameter("roadFullAddr");
String roadAddrPart1 = request.getParameter("roadAddrPart1");
String roadAddrPart2 = request.getParameter("roadAddrPart2");
String engAddr = request.getParameter("engAddr");
String jibunAddr = request.getParameter("jibunAddr");
String zipNo = request.getParameter("zipNo");
String addrDetail = request.getParameter("addrDetail");
String admCd = request.getParameter("admCd");
String rnMgtSn = request.getParameter("rnMgtSn");
String bdMgtSn = request.getParameter("bdMgtSn");
String detBdNmList = request.getParameter("detBdNmList");
/** 2017년 2월 추가제공 **/
String bdNm = request.getParameter("bdNm");
String bdKdcd = request.getParameter("bdKdcd");
String siNm = request.getParameter("siNm");
String sggNm = request.getParameter("sggNm");
String emdNm = request.getParameter("emdNm");
String liNm = request.getParameter("liNm");
String rn = request.getParameter("rn");
String udrtYn = request.getParameter("udrtYn");
String buldMnnm = request.getParameter("buldMnnm");
String buldSlno = request.getParameter("buldSlno");
String mtYn = request.getParameter("mtYn");
String lnbrMnnm = request.getParameter("lnbrMnnm");
String lnbrSlno = request.getParameter("lnbrSlno");
/** 2017년 3월 추가제공 **/
String emdNo = request.getParameter("emdNo");
%>
</head>
<script language="javascript">
// opener관련 오류가 발생하는 경우 아래 주석을 해지하고, 사용자의 도메인정보를 입력합니다. ("주소입력화면 소스"도 동일하게 적용시켜야 합니다.)
document.domain = ;
function init(){
var url = location.href;
var confmKey = " devU01TX0FVVEgyMDIxMDMxNjExNTcyNjExMDkyMTQ=";
var resultType = "4"; // 도로명주소 검색결과 화면 출력내용, 1 : 도로명, 2 : 도로명+지번+상세보기(관련지번, 관할주민센터), 3 : 도로명+상세보기(상세건물명), 4 : 도로명+지번+상세보기(관련지번, 관할주민센터, 상세건물명)
var inputYn= "<%=inputYn%>";
if(inputYn != "Y"){
document.form.confmKey.value = confmKey;
document.form.returnUrl.value = url;
document.form.resultType.value = resultType;
document.form.action="https://www.juso.go.kr/addrlink/addrLinkUrl.do"; //인터넷망
document.form.submit();
}else{
opener.jusoCallBack("<%=roadFullAddr%>","<%=roadAddrPart1 %>","<%=addrDetail%>","<%=roadAddrPart2%>","<%=zipNo%>");
window.close();
}
}
</script>
<body onload="init();">
<form id="form" name="form" method="post">
<input type="hidden" id="confmKey" name="confmKey" value=""/>
<input type="hidden" id="returnUrl" name="returnUrl" value=""/>
<input type="hidden" id="resultType" name="resultType" value=""/>
</form>
</body>
</html>회원가입시 주소를 검색할 수 있는 api 이다. 주소로api를 통해서 자세한 사용법을 확인할 수 있으며 정부에서 제공하는 api이다. confmkey의 경우에는 개발용 키는 만료기간이 짧기 때문에 필요할때 발급을 받아서 사용해야한다.
joinProcess.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>
<meta charset="UTF-8">
<title>Insert title here</title>
<%
request.setCharacterEncoding("utf-8");
String name= request.getParameter("name");
String mail = request.getParameter("mail");
String pw = request.getParameter("pw");
String id = request.getParameter("id");
String roadFullAddr = request.getParameter("roadFullAddr");
String roadAddrPart1 = request.getParameter("roadAddrPart1");
String roadAddrPart2 = request.getParameter("roadAddrPart2");
String addrDetail = request.getParameter("addrDetail");
String zipNo = request.getParameter("zipNo");
String birth = request.getParameter("year")+'.'+request.getParameter("Month")+'.'+request.getParameter("Day");
String intro = request.getParameter("intro");
String hobby = request.getParameter("hobby");
String sql = "INSERT INTO personal (name,id,pw,roadfulladdr, roadaddrpart1,addrdetail, roadaddrpart2,zipno,birth,intro,hobby, mail) Values (?,?,?,?,?,?,?,?,?,?,?,?)";
Connection conn = null;
try {
Context init = new InitialContext();
DataSource ds = (DataSource) init.lookup("java:comp/env/jdbc/OracleDB");
conn = ds.getConnection(); //풀방식
PreparedStatement stmt = conn.prepareStatement(sql);
stmt.setString(1,(String)name);
stmt.setString(2,(String)id);
stmt.setString(3,(String)pw);
stmt.setString(4,(String)roadFullAddr);
stmt.setString(5,(String)roadAddrPart1);
stmt.setString(6,(String)addrDetail);
stmt.setString(7,(String)roadAddrPart2);
stmt.setString(8,(String)zipNo);
stmt.setString(9,(String)birth);
stmt.setString(10,(String)intro);
stmt.setString(11,(String)hobby);
stmt.setString(12,(String)mail);
stmt.executeUpdate();
stmt.close();
conn.close();
response.sendRedirect("loginForm.jsp");
//response.sendRedirect("main.jsp");
} catch (Exception e) {
out.println("fail");
e.printStackTrace();
}
%>
</head>
<body>
</body>
</html>앞서 회원가입 양식에서 넘어온 값들을 오라클 db에 저장한다.
main.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>
<meta charset="UTF-8">
<title>Insert title here</title>
</head>
<%
request.setCharacterEncoding("utf-8");
String id=session.getAttribute("id").toString();
Connection conn = null;
String sql = "select id,pw from personal where id='"+id+"'";
try {
Context init = new InitialContext();
DataSource ds = (DataSource) init.lookup("java:comp/env/jdbc/OracleDB");
conn = ds.getConnection(); //풀방식
PreparedStatement stmt = conn.prepareStatement(sql);
ResultSet rs = stmt.executeQuery();
while(rs.next()){
out.println(rs.getString(1)+"이 로그인하였습니다.<br>");
if(id.equals("admin")){
out.print("<a href='Member_list.jsp'>회원정보 확인</a>");
}
}
rs.close();
} catch (Exception e) {
out.println("fail");
e.printStackTrace();
}
%>
<body>
</body>
</html>로그인이 되었을때 화면. 관리자라면 관리모드를 들어갈 수 있다.
Member_list.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>
<meta charset="UTF-8">
<title>Insert title here</title>
</head>
<%
request.setCharacterEncoding("utf-8");
Connection conn = null;
String sql = "select id from personal";
try {
Context init = new InitialContext();
DataSource ds = (DataSource) init.lookup("java:comp/env/jdbc/OracleDB");
conn = ds.getConnection(); //풀방식
PreparedStatement stmt = conn.prepareStatement(sql);
ResultSet rs = stmt.executeQuery();
out.println("<table align='center' width='600' cellpadding='5' cellspacing='2' bgcolor='FFFFFF' border='2'>");
out.println("<tr align='center'><td colspan='2' bgcolor='#f0f0f0'>회원 기본 정보</td></tr>");
while(rs.next()){
out.println("<tr><td><a href='Member_into.jsp?id="+rs.getString(1)+"'>" + rs.getString(1)+"</a></td>");
out.println("<td><a href='Member_delete.jsp?id="+rs.getString(1)+"'>delete</a></td></tr>");
}
rs.close();
} catch (Exception e) {
out.println("fail");
e.printStackTrace();
}
%>
<body>
</body>
</html>저장된 회원 목록을 불러오고 정보를 자세히 보거나 혹은 삭제할 수 있도록 해준다.
Member_into.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>
<meta charset="UTF-8">
<title>Insert title here</title>
</head>
<%
request.setCharacterEncoding("utf-8");
String id=request.getParameter("id").toString();
Connection conn = null;
String sql = "select * from personal where id='"+id+"'";
try {
Context init = new InitialContext();
DataSource ds = (DataSource) init.lookup("java:comp/env/jdbc/OracleDB");
conn = ds.getConnection(); //풀방식
PreparedStatement stmt = conn.prepareStatement(sql);
ResultSet rs = stmt.executeQuery();
while(rs.next()){
for(int i=1;i<13;i++){
out.println(rs.getString(i)+"<br>");
}
}
rs.close();
} catch (Exception e) {
out.println("fail");
e.printStackTrace();
}
%>
<body>
</body>
</html>회원을 클릭하였을때 자세한 정보를 보여준다.
Member_delete.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>
<meta charset="UTF-8">
<title>Insert title here</title>
</head>
<%
request.setCharacterEncoding("utf-8");
String id=request.getParameter("id");
Connection conn = null;
String sql = "delete from personal where id='"+id+"'";
try {
Context init = new InitialContext();
DataSource ds = (DataSource) init.lookup("java:comp/env/jdbc/OracleDB");
conn = ds.getConnection(); //풀방식
PreparedStatement stmt = conn.prepareStatement(sql);
ResultSet rs = stmt.executeQuery();
rs.next();
rs.close();
response.sendRedirect("Member_list.jsp");
} catch (Exception e) {
out.println("fail");
e.printStackTrace();
}
%>
<body>
</body>
</html>삭제를 눌렀을때 해당 회원의 정보가 삭제되도록 한다.
'빅데이터교육과정 > DB' 카테고리의 다른 글
| DAY 4. MVC2 패턴 - 1 (0) | 2021.03.17 |
|---|---|
| DAY 2. 이클립스를 활용한 sql사용 (0) | 2021.03.17 |
| DAY 1. SQL Development 계정생성 (cmd, sql devel) (0) | 2021.03.11 |
| DAY 1. Oracle 설치 및 삭제(Window10 Pro, 20H2)-Universal Installer, deinstall.bat 미사용 (0) | 2021.03.11 |