JSP
JSP DB연결 (로그인 및 회원 가입) - MySQL 연결 활용
code2772
2022. 11. 21. 17:36
728x90
반응형
✔ Eclipse DB 연결 방법
🔏 Eclipse DB 연결 절차
1. 해당 파일(우클릭) -> build path -> Configure build path ->
Libraries-> ModulePath -> add External jars-> Mysql 파일 선택
2. Configure build path -> Deployment Assembly - .add ->
java Build path enties -> 전에 추가한 것 확인-> add
✔ SQL 테이블 설정
use aidev;
select * from tb_member;
alter table tb_member add mem_gender varchar(20);
alter table tb_member add mem_userpw varchar(256);
alter table tb_member drop mem_userpw ;
desc tb_member;
#SHA2
# 대표적인 단방향 암호화 해시함수
select sha2('1234', 256);
select sha2('angel1004!))$', 256);
select hex(aes_encrypt(sha2('1234',256), '111'));
# tb_member의 비밀번호 저장 크기를 변경a
# 회원가입시 비밀번호를 sha2를 이용하여 저장
# 로그인시 비밀번호를 sha2로 체트하여 로그인
#insert into 테이블명 (필드..) values(?,?,sha2(?,256),?,..)
#서블릿으로 변환해서 작업 함
✔ JSP login
<%@ page language="java" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8"%>
<%
String userid = null;
if(session.getAttribute("userid") != null){
userid = (String)session.getAttribute("userid");
}
%>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title> 로그인</title>
</head>
<body>
<h2> 로그인</h2>
<%
if(userid ==null){
%>
<form method ="post" action="login_ok">
<p>아이디 : <input type ="text" name ="userid"></p>
<p>비밀번호 : <input type ="password" name ="userpw"></p>
<p><button>login</button></p>
</form>
<p>아직 회원이 아닛신가요?<a href ="5_member.jsp">회원가입</a></p>
<%
}else{
%>
<h3><%=userid %>님 환영합니다.</h3>
<p><a
href ="5_logout.jsp">로그아웃</a></p>
<!-- 기존에 같은 값으로 덮어씌우면 쿠키값을 없는것처럼 할 수 있고 아니면 시간을 0 또는 마이너스로 -->
<%
}
%>
</body>
</html>
✔ JSP logut
<%@ page language="java" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8"%>
<%
session.invalidate();
%>
<script>
alert('로그아웃 되었습니다.')
location.href = '5_login.jsp';
</script>
✔ JSP 회원가입 페이지
<%@ page language="java" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8"%>
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<meta http-equiv="X-UA-Compatible" content="IE=edge">
<meta name="viewport" content="width=device-width, initial-scale=1.0">
<title>회원가입</title>
<script
src="//t1.daumcdn.net/mapjsapi/bundle/postcode/prod/postcode.v2.js"></script>
<script defer src="./js/regist.js"></script>
</head>
<body>
<h2>회원가입</h2>
<form action="member_ok" name="regform" id="regform"
method="post" onsubmit="return sendit()">
<input type ="hidden" name = "isIdCheck" id = "isIdCheck" value ="n">
<p>
아이디 : <input type="text" name="userid" id="userid" maxlength="20" onkeydown ="idModify()">
<input type="button" id="btnIdCheck" value="중복체크"
onclick="clickBtn()">
</p>
<p id="checkmsg"></p>
<p>
비밀번호 : <input type="password" name="userpw" id="userpw"
maxlength="20">
</p>
<p>
비밀번호 확인 : <input type="password" name="userpw_re" id="userpw_re"
maxlength="20">
</p>
<p>
이름 : <input type="text" name="name" id="name">
</p>
<p>
휴대폰 번호 : <input type="text" name="hp" id="hp">
</p>
<p>
이메일 : <input type="text" name="email" id="email">
</p>
<p>
성별 :<label> 남자 <input type="radio" name="gender" value="남자"
checked></label> <label> 여자 <input type="radio"
name="gender" value="여자"></label>
</p>
<p>
취미 : <label>등산<input type="checkbox" name="hobby" value="등산"></label>
<label>게임<input type="checkbox" name="hobby" value="게임"></label>
<label>영화감상<input type="checkbox" name="hobby" value="영화감상"></label>
<label>드라이브<input type="checkbox" name="hobby" value="드라이브"></label>
<label>운동<input type="checkbox" name="hobby" value="운동"></label>
</p>
<p>
주민등록번호 : <input type="text" name="ssn1"> -<input type="text"
name="ssn2">
</p>
<p>
우편번호: <input type="text" name="zipcode" maxlength="5"
id="sample6_postcode">
<button type="button" onclick="sample6_execDaumPostcode()">검색</button>
</p>
<p>
주소 <input type="text" name="sample6_address">
</p>
<p>
상세주소 <input type="text" name="sample6_detailAddress">
</p>
<p>
참고사항 <input type="text" name="sample6_extraAddress">
</p>
<p>
<button>가입완료</button>
<button type="reset">다시작성</button>
</p>
</form>
</body>
</html>
✔ JSP check
<%@ page language="java" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8"%>
<%@ page import = "java.sql.*"%>
<%@ page import = "com.koreait.db.Dbconn"%>
<%
Connection conn = null;
PreparedStatement pstmt = null;
ResultSet rs = null;
String userid = request.getParameter("userid");
try{
conn = Dbconn.getConnection();
if(conn != null){
String sql = "Select mem_idx from tb_member where mem_userid =?";
pstmt = conn.prepareStatement(sql);
pstmt.setString(1, userid);
rs = pstmt.executeQuery();
if(rs.next()){
out.print("no"); //가입을 할수 없는 경우
}else{
out.print("ok"); //가입을 할수 있는 경우
}
}
}catch(Exception e){
e.printStackTrace();
}
%>
✔ DB연결 Dbconn
package com.koreait.db;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
public class Dbconn {
private static Connection conn; // 주요하게 생각하는것을 컨넥션 객체를 사용할 수 있게
//스테틱이 있으면 클래스만 불러도 된다 메소드 호출시
public static Connection getConnection() throws SQLException, ClassNotFoundException {// 리턴타입은 Connection
String url = "jdbc:mysql://127.0.0.1/aidev?useSSL=false";
String uid = "root";
String upw = "1234";
Class.forName("com.mysql.cj.jdbc.Driver");
conn = DriverManager.getConnection(url, uid, upw);
return conn;
}
}
✔ JSP servlet 이용 로그인 확인
package com.song;
import java.io.IOException;
import java.io.PrintWriter;
import java.sql.*;
import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.servlet.http.HttpSession;
import com.koreait.db.Dbconn;
/**
* Servlet implementation class login_ok
*/
@WebServlet("/login_ok")
public class login_ok extends HttpServlet {
private static final long serialVersionUID = 1L;
/**
* @see HttpServlet#HttpServlet()
*/
public login_ok() {
super();
// TODO Auto-generated constructor stub
}
/**
* @see HttpServlet#doGet(HttpServletRequest request, HttpServletResponse response)
*/
protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
response.setContentType("text/html;charset=UTF-8");
request.setCharacterEncoding("UTF-8");
PrintWriter writer = response.getWriter();
HttpSession session = request.getSession();}
/**
* @see HttpServlet#doPost(HttpServletRequest request, HttpServletResponse response)
*/
protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
response.setContentType("text/html;charset=UTF-8");
request.setCharacterEncoding("UTF-8");
PrintWriter writer = response.getWriter();
HttpSession session = request.getSession();
String userid = request.getParameter("userid");
String userpw = request.getParameter("userpw");
Connection conn =null;
PreparedStatement pstmt;
ResultSet rs =null;
try{
conn = Dbconn.getConnection();
if(conn != null){
String sql = "select mem_idx, mem_name from tb_member where mem_userid=? and mem_userpw=?";
pstmt = ((java.sql.Connection) conn).prepareStatement(sql);
pstmt.setString(1, userid);
pstmt.setString(2, userpw);
rs = pstmt.executeQuery();
if(rs.next()){
session.setAttribute("userid", userid);
session.setAttribute("idx", rs.getString("mem_idx"));
session.setAttribute("name", rs.getString("mem_name"));
writer.println("<script>alert('로그인 되었습니다.');location.href='5_login.jsp';</script>" );
}else {
writer.println("<script>alert('아이디 또는 비밀번호를 확인하세요.');history.back();</script>");
}
}
}catch(Exception e){
e.printStackTrace();
}
}
}
✔ JSP servlet 이용 회원가입 확인
package com.song;
import java.io.IOException;
import java.io.PrintWriter;
import java.sql.Connection;
import java.sql.PreparedStatement;
import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.servlet.http.HttpSession;
import com.koreait.db.Dbconn;
/**
* Servlet implementation class member_ok
*/
@WebServlet("/member_ok")
public class member_ok extends HttpServlet {
private static final long serialVersionUID = 1L;
/**
* @see HttpServlet#HttpServlet()
*/
public member_ok() {
super();
// TODO Auto-generated constructor stub
}
/**
* @see HttpServlet#doGet(HttpServletRequest request, HttpServletResponse response)
*/
protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
response.setContentType("text/html;charset=UTF-8");
request.setCharacterEncoding("UTF-8");
PrintWriter writer = response.getWriter();
HttpSession session = request.getSession();
}
/**
* @see HttpServlet#doPost(HttpServletRequest request, HttpServletResponse response)
*/
protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
response.setContentType("text/html;charset=UTF-8");
request.setCharacterEncoding("UTF-8");
PrintWriter writer = response.getWriter();
HttpSession session = request.getSession();
Connection conn = null;
PreparedStatement pstmt = null;
request.setCharacterEncoding("UTF-8");
String userid = request.getParameter("userid");
String userpw = request.getParameter("userpw");
String name = request.getParameter("name");
String hp = request.getParameter("hp");
String email = request.getParameter("email");
String gender = request.getParameter("gender");
String hobby[] = request.getParameterValues("hobby");
String ssn1 = request.getParameter("ssn1");
String ssn2 = request.getParameter("ssn2");
String zipcode = request.getParameter("zipcode");
String address1 = request.getParameter("address1");
String address2 = request.getParameter("address2");
String address3 = request.getParameter("address2");
try {
conn = Dbconn.getConnection();
if (conn != null) {
/* System.out.println("DB연결 성공"); */
String sql = "insert into tb_member(mem_userid, mem_userpw, mem_name, mem_hp, mem_email, mem_hobby, mem_ssn1,mem_ssn2,mem_zipcode,mem_address1,mem_address2,mem_address3,mem_gender)values(?,sha2(?,256),?,?,?,?,?,?,?,?,?,?,?)";
pstmt = ((java.sql.Connection) conn).prepareStatement(sql);
pstmt.setString(1, userid);
pstmt.setString(2, userpw);
pstmt.setString(3, name);
pstmt.setString(4, hp);
pstmt.setString(5, email);
String hobbystr = "";
for (int i = 0; i < hobby.length; i++) {
hobbystr = hobbystr + hobby[i] + " ";
}
pstmt.setString(6, hobbystr);
pstmt.setString(7, ssn1);
pstmt.setString(8, ssn2);
pstmt.setString(9, zipcode);
pstmt.setString(10, address1);
pstmt.setString(11, address2);
pstmt.setString(12, address3);
pstmt.setString(13, gender);
pstmt.executeUpdate();
}
} catch (Exception e) {
e.printStackTrace();
}
writer.println("<script>alert('"+ userid + "회원가입 완료.'); location.href='5_login.jsp';</script>");
}
}
✔ 회원가입 후 비밀번호 암호화 및 회원가입 DB 연결
✔ 회원가입 아이디 중복체크
반응형