오늘 배운 것
- DB연동하여 로그인페이지 만들기
- Controller doget 부분, DB연동
package controller;
import java.io.IOException;
import java.io.PrintWriter;
import java.sql.Connection;
import java.sql.SQLException;
import javax.annotation.Resource;
import javax.security.auth.login.AccountException;
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.sql.DataSource;
@WebServlet("/Controller")
public class Controller extends HttpServlet {
private static final long serialVersionUID = 1L;
@Resource(name = "jdbc/webshop")
private DataSource ds; // DataSource ds로 DB연결
protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
response.setContentType("text/html; charset=UTF-8");
PrintWriter out = response.getWriter();
String action = request.getParameter("action");
if(action == null) {
request.getRequestDispatcher("/index.jsp").forward(request, response);
} else if (action.equals("login")) {
request.setAttribute("email", "");
request.setAttribute("password", "");
request.setAttribute("message", "");
request.getRequestDispatcher("/login.jsp").forward(request, response);
}
}- 로그인 클릭 시 로그인 페이지로 이동
<%@ 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>
<style>
* {
margin: 0px;
padding: 0px;
text-decoration: none;
font-family: sans-serif;
}
body {
background-color: #34495e;
width: 100%;
height: 100%;
}
.loginForm {
position: absolute;
width: 300px;
height: 400px;
padding: 30px, 20px;
background-color: #FFFFFF;
text-align: center;
top: 50%;
left: 50%;
transform: translate(-50%, -50%);
border-radius: 15px;
}
.loginForm h2 {
text-align: center;
margin: 30px;
}
.emailForm {
border-bottom: 2px solid #adadad;
margin: 30px;
padding: 10px 10px;
}
.passForm {
border-bottom: 2px solid #adadad;
margin: 30px;
padding: 10px 10px;
}
.email {
width: 100%;
border: none;
outline: none;
color: #636e72;
font-size: 16px;
height: 25px;
background: none;
}
.pw {
width: 100%;
border: none;
outline: none;
color: #636e72;
font-size: 16px;
height: 25px;
background: none;
}
.btn {
position: relative;
left: 40%;
transform: translateX(-50%);
margin-bottom: 40px;
width: 80%;
height: 40px;
background: linear-gradient(125deg, #81ecec, #6c5ce7, #81ecec);
background-position: left;
background-size: 200%;
color: white;
font-weight: bold;
border: none;
cursor: pointer;
transition: 0.4s;
display: inline;
}
.btn:hover {
background-position: right;
}
.bottomText {
text-align: center;
}
</style>
</head>
<body>
<form action="<%=request.getContextPath() %>/Controller?action=dologin" method="post" class="loginForm">
<input type="hidden" name="action" value="dologin" >
<h2>Login</h2>
<div class="emailForm">
<input type="text" class="email" name="email" placeholder="Email" value="<%= request.getAttribute("email") %>">
</div>
<div class="passForm">
<input type="password" class="pw" name="password" placeholder="PW" value="<%= request.getAttribute("password")
%>">
</div>
<button type="submit" class="btn">LOG IN</button>
<div class="bottomText">
<%= request.getAttribute("message") %>
</div>
</form>
</body>
</html>- 로그인 화면
- Controller의 dopost부분
- email, password를 입력하여 account 클래스에서 DB와 체크 후 문제가 없을 시 loginsuccess.jsp 페이지로 이동, 문제가 있을 시 login.jsp 페이지에서 메시지 출력
protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
response.setContentType("text/html; charset=UTF-8"); // 한글 설정(servlet으로 한글 출력하기위해 필요)
PrintWriter out = response.getWriter();
String action = request.getParameter("action");
if(action == null) {
out.println("알 수 없는 요청입니다.");
return;
}
Connection conn = null;
try {
conn = ds.getConnection(); // DB연결
} catch (SQLException e) {
out.println("DB에 연결 실패");
return;
}
Account account = new Account(conn); //
if (action.equals("dologin")) {
String email = request.getParameter("email");
String password = request.getParameter("password");
request.setAttribute("email", email);
request.setAttribute("password", ""); // 보안을 위해 초기화
try {
if (account.login(email, password)) {
request.getRequestDispatcher("/loginsuccess.jsp").forward(request, response);
} else {
request.setAttribute("message", "email 또는 password가 틀립니다.");
request.getRequestDispatcher("/login.jsp").forward(request, response);
}
} catch (SQLException e) {
e.printStackTrace();
request.setAttribute("message", "DB 에러 발생");
request.getRequestDispatcher("/login.jsp").forward(request, response);
}
}
try {
conn.close(); // 실제로는 닫는 것이 아니라 Conection pool로 보냄
} catch (SQLException e) {
out.println("DB연결 종료 실패");
}
}
}- DB와 연동된 변수 conn으로 입력한 email, password를 SQL문으로 검색하여 일치하는 data가 있는지 확인함
package controller;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
public class Account {
private Connection conn;
public Account(Connection conn) {
this.conn = conn;
}
// 로그인 (email, password) DB에서 같은 email, password 확인해서 false,true return
public boolean login(String email, String password) throws SQLException {
String sql = "SELECT COUNT(*) AS count FROM users where email=? and password=?";
PreparedStatement pstmt = conn.prepareStatement(sql);
pstmt.setString(1, email); // 첫번쨰 물음표
pstmt.setString(2, password); // 두번쨰 물음표
ResultSet rs = pstmt.executeQuery(); // SQL문 실행
int count = 0;
if(rs.next()) { // 결과가 있으면
count = rs.getInt("count"); // count 열의 값을 return(int형)
}
rs.close();
if(count == 0) return false; // 없으면 false 있으면 true
return true;
}
}- email 또는 password가 일치하지 않을 때
- 로그인 성공 시
DB연동
<?xml version="1.0" encoding="UTF-8"?>
<Context>
<Resource name="jdbc/webshop" auth="Container" type="javax.sql.DataSource"
maxActive="20" maxIdle="5" maxWait="10000"
username="root" password="1234" driverClassName="com.mysql.jdbc.Driver"
url="jdbc:mysql://localhost:3306/webshop?useSSL=false"/>
</Context>
Connection conn = null;
conn = ds.getConnection(); // DB연결
String sql = " SQL문 ";
PreparedStatement pstmt = conn.prepareStatement(sql);
pstmt.setString(n, 칼럼명); // n은 SQL문에서 ? 순서
ResultSet rs = pstmt.executeQuery(); // SQL문 실행
