본문 바로가기
JSP

JSP DB연결 (로그인 및 회원 가입) - MySQL 연결 활용

by code2772 2022. 11. 21.

[ 목차 ]

    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 연결

     

    ✔ 회원가입 아이디 중복체크

    반응형
    저작자표시 비영리 변경금지

    'JSP' 카테고리의 다른 글

    JSP (Marven, 파일 업로드, JSTL, el)  (0) 2022.11.29
    JSP DB 회원가입 게시판 쿼리  (0) 2022.11.26
    JSP (예외, pagecontext, ajax, XMLHttpRequest)  (0) 2022.11.21
    JSP (서블릿, 세션, 장바구니)  (0) 2022.11.21
    JSP (쿠키, 세션 로그인 및 기본개념)  (0) 2022.11.18

    관련글

    티스토리툴바