create table users(
-> id varchar(12) primary key,
-> pass varchar(12),
-> addr varchar(50),
-> age int,
-> email varchar(30),
-> gender char(1),
-> name varchar(12),
-> nation varchar(16));
mariaDB 이용
index.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>
<%
String id = (String)session.getAttribute("id");
if(id == null) { %>
<a href="loginForm.html">로그인</a>
<% } else { %>
<a href="logout">로그아웃</a>
<% } %>
</body>
</html>
loginForm.html
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>Insert title here</title>
</head>
<body>
<section id = "loginFormArea">
<h1>로그인</h1>
<form action="login" method="post">
<fieldset>
<label for="id">아이디 : </label>
<input type="text" name="id" id="id"/>
<label for="pass">비밀번호 : </label>
<input type="password" name="pass" id="pass"/>
<input type="submit" value="로그인" id="selectButton"/>
</fieldset>
</form>
</section>
</body>
</html>
LoginServlet.java
package controller;
import java.io.IOException;
import java.io.PrintWriter;
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 svc.LoginService;
import vo.Member;
/**
* Servlet implementation class LoginServlet
*/
@WebServlet("/login")
public class LoginServlet extends HttpServlet {
private static final long serialVersionUID = 1L;
/**
* @see HttpServlet#HttpServlet()
*/
public LoginServlet() {
super();
// TODO Auto-generated constructor stub
}
/**
* @see HttpServlet#doPost(HttpServletRequest request, HttpServletResponse response)
*/
protected void doPost(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {
String id = request.getParameter("id");
String pass = request.getParameter("pass");
LoginService loginService = new LoginService();
Member loginMember = loginService.getLoginMember(id, pass);
if(loginMember != null) {
HttpSession session = request.getSession();
session.setAttribute("id", id);
response.sendRedirect("index.jsp");
} else {
response.setContentType("text/html; charset=UTF-8");
PrintWriter out = response.getWriter();
out.println("<script>");
out.println("alert('로그인 실패')");
out.println("history.back()");
out.println("</script>");
}
}
}
LoginService.java
package svc;
import java.sql.Connection;
import static db.JdbcUtil.*;
import dao.LoginDAO;
import vo.Member;
public class LoginService {
public Member getLoginMember(String id, String pass) {
LoginDAO loginDAO = LoginDAO.getInstance();
Connection con = getConnection();
loginDAO.setConnection(con);
Member loginMember = loginDAO.selectLoginMember(id, pass);
close(con);
return loginMember;
}
}
LoginDAO.java
package dao;
import java.io.Closeable;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import static db.JdbcUtil.*;
import vo.Member;
public class LoginDAO {
private static LoginDAO loginDAO;
private Connection con;
private LoginDAO() {
}
public static LoginDAO getInstance() {
if(loginDAO == null) {
loginDAO = new LoginDAO();
}
return loginDAO;
}
public void setConnection(Connection con) {
this.con = con;
}
public Member selectLoginMember(String id, String pass) {
Member loginMember = null;
PreparedStatement pstmt = null;
ResultSet rs = null;
try {
pstmt = con.prepareStatement("SELECT * FROM users WHERE id = ? AND pass = ?");
pstmt.setString(1, id);
pstmt.setString(2, pass);
rs = pstmt.executeQuery();
if(rs.next()) {
loginMember = new Member();
loginMember.setAddr(rs.getString("addr"));
loginMember.setAge(rs.getInt("age"));
loginMember.setEmail(rs.getString("email"));
loginMember.setGender(rs.getString("gender"));
loginMember.setId(rs.getString("id"));
loginMember.setName(rs.getString("name"));
loginMember.setNation(rs.getString("nation"));
loginMember.setPass(rs.getString("pass"));
}
}catch (Exception e) {
// TODO: handle exception
e.printStackTrace();
}
finally {
try {
close(rs);
close(pstmt);
} catch (Exception e) {
// TODO: handle exception
e.printStackTrace();
}
}
return loginMember;
}
}
JdbcUtil.java
package db;
import java.beans.Statement;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import javax.naming.Context;
import javax.naming.InitialContext;
import javax.sql.DataSource;
public class JdbcUtil {
public static Connection getConnection() {
Connection con = null;
try {
Context initCtx = new InitialContext();
Context envCtx = (Context)initCtx.lookup("java:comp/env");
DataSource ds = (DataSource)envCtx.lookup("jdbc/mariaDB");
con = ds.getConnection();
con.setAutoCommit(false);
System.out.println("connect succes");
} catch(Exception e) {
e.printStackTrace();
}
return con;
}
public static void close(Connection con) {
try {
con.close();
} catch (Exception e) {
e.printStackTrace();
}
}
public static void close(PreparedStatement pstmt) {
try {
pstmt.close();
} catch (Exception e) {
e.printStackTrace();
}
}
public static void close(ResultSet rs) {
try {
rs.close();
} catch (Exception e) {
e.printStackTrace();
}
}
public static void commit(Connection con) {
try {
con.commit();
} catch (Exception e) {
e.printStackTrace();
}
}
public static void rollback(Connection con) {
try {
con.rollback();
} catch (Exception e) {
e.printStackTrace();
}
}
}
Member.java
package vo;
public class Member {
private String name;
private String addr;
private int age;
private String nation;
private String id;
private String pass;
private String gender;
private String email;
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public String getAddr() {
return addr;
}
public void setAddr(String addr) {
this.addr = addr;
}
public int getAge() {
return age;
}
public void setAge(int age) {
this.age = age;
}
public String getNation() {
return nation;
}
public void setNation(String nation) {
this.nation = nation;
}
public String getId() {
return id;
}
public void setId(String id) {
this.id = id;
}
public String getPass() {
return pass;
}
public void setPass(String pass) {
this.pass = pass;
}
public String getGender() {
return gender;
}
public void setGender(String gender) {
this.gender = gender;
}
public String getEmail() {
return email;
}
public void setEmail(String email) {
this.email = email;
}
}
LogoutServlet.java
package controller;
import java.io.IOException;
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;
/**
* Servlet implementation class LogoutServlet
*/
@WebServlet("/logout")
public class LogoutServlet extends HttpServlet {
private static final long serialVersionUID = 1L;
/**
* @see HttpServlet#HttpServlet()
*/
public LogoutServlet() {
super();
// TODO Auto-generated constructor stub
}
/**
* @see HttpServlet#doGet(HttpServletRequest request, HttpServletResponse response)
*/
protected void doGet(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {
//id정보를 저장하고 있는 session을 삭제 후 loginForm.html로 리다이렉트
HttpSession session = request.getSession();
session.invalidate();
response.sendRedirect("index.jsp");
}
}
context.xml
<?xml version="1.0" encoding="UTF-8"?>
<Context>
<Resource
name = "jdbc/mariaDB"
auth = "Container"
type = "javax.sql.DataSource"
factory = "org.apache.tomcat.dbcp.dbcp2.BasicDataSourceFactory"
driverClassName = "org.mariadb.jdbc.Driver"
url = "jdbc:mysql://localhost:3306/testdb3"
username = "java"
password = "java"
maxActive = "100"
maxIdle = "30"
maxWait = "10000"
removeAbandoned = "true"
removeAbandonedTimeout = "60" />
</Context>
<!--
1. name = JNDI로 호출될 이름을 설정
2. auth : DBCP를 관리할 관리자
3. type : 해당 resource의 return type
4. factory : dbcp 관리 클래스
5. driverClassName : JDBC를 이용하기 위한 드라이버 클래스
6. url : DB의 접속 URL
7. username : DB의 계정 명
8. password : 계정에 대한 비밀번호
9. maxActive : 최대 접속 허용 개수
10. maxIdle : DB pool에 여분으로 남겨질 최대 Connectin 개수
11. maxWait : DB 연결이 반환되는 Timeout의 최대 시간(-1은 무한 대기)
12. removeAbandoned : Connection이 잘못 관리되어 버려진 연결을 찾아 재활용할 것인지 여부 설정
13. removeAbandonedTimeout : 버려진 연결로 인식할 기본 시간 설정
(초 단위로 해당 시간이 지나면 버려진 연결로 인식)
-->
실행 화면 ⬇️
데이터베이스 정보