sql

create database mydb;

use mydb;

create table tblregister(
num int,
id char(20) not null,
pw char(20) not null,
name char(6) null,
num1 char(6) null,
num2 char(7) null,
email char(30) null,
phone char(30) null,
zipcode char(7) null,
address char(60) null,
job char(30) null
);

insert into tblregister(num, id, pw, name, num1, num2, email, phone, zipcode, address, job) 
values(1, 'vbass', '11111', '김준형', '1234', '1234', 'vbass@naver.com', '011-111-1111', '111-111', '하와이', '프로그래머');

insert into tblregister(num, id, pw, name, num1, num2, email, phone, zipcode, address, job) 
values(2, 'cbss', '21111', '공준형', '1234', '1234', 'cbss@naver.com', '011-111-1111', '111-111', '서울', '과학자');


DBConnectionMgr.java

package pack_Login;
import java.sql.*;
import java.util.Properties;
import java.util.Vector;

/**
 * Manages a java.sql.Connection pool.
 *
 * @author  Anil Hemrajani
 */
public class DBConnectionMgr {
    private Vector connections = new Vector(10);
    private String _driver = "com.mysql.cj.jdbc.Driver",
    _url = "jdbc:mysql://127.0.0.1:3306/mydb?useSSL=false&serverTimezone=Asia/Seoul&useUnicode=true&characterEncoding=UTF-8&allowPublicKeyRetrieval=true",
    _user = "root",
    _password = "1234";
    private boolean _traceOn = false;
    private boolean initialized = false;
    private int _openConnections = 10;
    private static DBConnectionMgr instance = null;

    public DBConnectionMgr() {
    }

    /** Use this method to set the maximum number of open connections before
     unused connections are closed.
     */

    public static DBConnectionMgr getInstance() {
        if (instance == null) {
            synchronized (DBConnectionMgr.class) {
                if (instance == null) {
                    instance = new DBConnectionMgr();
                }
            }
        }
        return instance;
    }

    public void setOpenConnectionCount(int count) {
        _openConnections = count;
    }

    public void setEnableTrace(boolean enable) {
        _traceOn = enable;
    }

    /** Returns a Vector of java.sql.Connection objects */
    public Vector getConnectionList() {
        return connections;
    }

    /** Opens specified "count" of connections and adds them to the existing pool */
    public synchronized void setInitOpenConnections(int count)
            throws SQLException {
        Connection c = null;
        ConnectionObject co = null;
        
        for (int i = 0; i < count; i++) {
            c = createConnection();
            co = new ConnectionObject(c, false);
            connections.addElement(co);
            trace("ConnectionPoolManager: Adding new DB connection to pool (" + connections.size() + ")");
        }
    }

    /** Returns a count of open connections */
    public int getConnectionCount() {
        return connections.size();
    }

    /** Returns an unused existing or new connection.  */
    public synchronized Connection getConnection()
            throws Exception {
        if (!initialized) {
            Class c = Class.forName(_driver);
            DriverManager.registerDriver((Driver) c.newInstance());
            initialized = true;
        }
        Connection c = null;
        ConnectionObject co = null;
        boolean badConnection = false;

        for (int i = 0; i < connections.size(); i++) {
            co = (ConnectionObject) connections.get(i);
            // If connection is not in use, test to ensure it's still valid!
            if (!co.inUse) {
                try {
                    badConnection = co.connection.isClosed();
                    if (!badConnection)
                        badConnection = (co.connection.getWarnings() != null);
                } catch (Exception e) {
                    badConnection = true;
                    e.printStackTrace();
                }
                // Connection is bad, remove from pool
                if (badConnection) {
                    connections.removeElementAt(i);
                    trace("ConnectionPoolManager: Remove disconnected DB connection #" + i);
                    continue;
                }
                c = co.connection;
                co.inUse = true;
                trace("ConnectionPoolManager: Using existing DB connection #" + (i + 1));
                break;
            }
        }

        if (c == null) {
            c = createConnection();
            co = new ConnectionObject(c, true);
            connections.addElement(co);
            trace("ConnectionPoolManager: Creating new DB connection #" + connections.size());
        }
        return c;
    }

    /** Marks a flag in the ConnectionObject to indicate this connection is no longer in use */
    public synchronized void freeConnection(Connection c) {
        if (c == null)
            return;

        ConnectionObject co = null;

        for (int i = 0; i < connections.size(); i++) {
            co = (ConnectionObject) connections.get(i);
            if (c == co.connection) {
                co.inUse = false;
                break;
            }
        }

        for (int i = 0; i < connections.size(); i++) {
            co = (ConnectionObject) connections.get(i);
            if ((i + 1) > _openConnections && !co.inUse)
                removeConnection(co.connection);
        }
    }

    public void freeConnection(Connection c, PreparedStatement p, ResultSet r) {
        try {
            if (r != null) r.close();
            if (p != null) p.close();
            freeConnection(c);
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    public void freeConnection(Connection c, Statement s, ResultSet r) {
        try {
            if (r != null) r.close();
            if (s != null) s.close();
            freeConnection(c);
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    public void freeConnection(Connection c, PreparedStatement p) {
        try {
            if (p != null) p.close();
            freeConnection(c);
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    public void freeConnection(Connection c, Statement s) {
        try {
            if (s != null) s.close();
            freeConnection(c);
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    /** Marks a flag in the ConnectionObject to indicate this connection is no longer in use */
    public synchronized void removeConnection(Connection c) {
        if (c == null)
            return;

        ConnectionObject co = null;
        for (int i = 0; i < connections.size(); i++) {
            co = (ConnectionObject) connections.get(i);
            if (c == co.connection) {
                try {
                    c.close();
                    connections.removeElementAt(i);
                    trace("Removed " + c.toString());
                } catch (Exception e) {
                    e.printStackTrace();
                }
                break;
            }
        }
    }

    private Connection createConnection()
            throws SQLException {
        Connection con = null;
        
        try {
            if (_user == null)
                _user = "";
            if (_password == null)
                _password = "";

            Properties props = new Properties();
            props.put("user", _user);
            props.put("password", _password);

            con = DriverManager.getConnection(_url, props);
        } catch (Throwable t) {
            throw new SQLException(t.getMessage());
        }
        return con;
    }

    /** Closes all connections and clears out the connection pool */
    public void releaseFreeConnections() {
        trace("ConnectionPoolManager.releaseFreeConnections()");

        Connection c = null;
        ConnectionObject co = null;

        for (int i = 0; i < connections.size(); i++) {
            co = (ConnectionObject) connections.get(i);
            if (!co.inUse)
                removeConnection(co.connection);
        }
    }

    /** Closes all connections and clears out the connection pool */
    public void finalize() {
        trace("ConnectionPoolManager.finalize()");

        Connection c = null;
        ConnectionObject co = null;

        for (int i = 0; i < connections.size(); i++) {
            co = (ConnectionObject) connections.get(i);
            try {
                co.connection.close();
            } catch (Exception e) {
                e.printStackTrace();
            }
            co = null;
        }
        connections.removeAllElements();
    }

    private void trace(String s) {
        if (_traceOn)
            System.err.println(s);
    }
}

class ConnectionObject {
    public java.sql.Connection connection = null;
    public boolean inUse = false;

    public ConnectionObject(Connection c, boolean useFlag) {
        connection = c;
        inUse = useFlag;
    }
}


RegisterMgr.java

package pack_Login;

import java.sql.*;
import java.util.Vector;

public class RegisterMgr {
	
	Connection objConn = null;
	Statement objStmt = null;
	ResultSet objRs = null;
	// getMemberList()  회원목록 조회용
	
	PreparedStatement objPstmt = null;
	
	public RegisterMgr() {
		
		try {
			
			DBConnectionMgr pool = DBConnectionMgr.getInstance();
			objConn = pool.getConnection();
			
			System.out.println("MyDB 연동 확인");
		} catch (ClassNotFoundException e) {
			System.out.println("DB 접속 이슈 : " + e.getMessage());
		} catch (Exception e) {
			System.out.println("DB 기타 이슈 : " + e.getMessage());
		}
	}
	// 생성자를 사용하여 커스텀클래스의 객체가
	// 생성되는 것과 동시에  DB접속이 자동으로 이루어짐
	// 그러므로 클래스의 객체가 유지되는 동안에는 DB접속이 유지됨
	
	public boolean passCheck(String memberId, String memberPw) {
		
		boolean rowChk = false;
		
		
		try {
			
			String sql = "select count(*) as cnt from tblregister where id = '" + memberId + "' and pw = '" + memberPw + "'";
			objStmt = objConn.createStatement();
			objRs = objStmt.executeQuery(sql);
			
			if (objRs != null) {
				
				while (objRs.next()) {
					
					if (objRs.getInt("cnt") > 0) {
						
						rowChk = true;
					}
				}
			}
			
		} catch (Exception e) {

			System.out.println("쿼리오류 이슈 : " + e.getMessage());
		}
		
		return rowChk;
		
	}
	
	public Vector getMemberList() {
		
		Vector vlist = null;
		
		try {
			
			objStmt = objConn.createStatement();
			String sql = "select * from tblregister order by num desc";
			objRs = objStmt.executeQuery(sql);
			
			vlist = new Vector();
			
			if (objRs != null) {
				
				while (objRs.next()) {
					
					RegisterBean regBean = new RegisterBean();
					
					regBean.setNum(objRs.getInt("num"));
					regBean.setId(objRs.getString("id"));
					regBean.setPw(objRs.getString("pw"));
					regBean.setName(objRs.getString("name"));
					regBean.setNum1(objRs.getString("num1"));
					regBean.setNum2(objRs.getString("num2"));
					regBean.setEmail(objRs.getString("email"));
					regBean.setPhone(objRs.getString("phone"));
					regBean.setZipcode(objRs.getString("zipcode"));
					regBean.setAddress(objRs.getString("address"));
					regBean.setJob(objRs.getString("job"));
					
					vlist.add(regBean);
					
				} //end while
			} // end if
			
		} catch (SQLException e) {
			System.out.println("DB SQL 이슈 : " + e.getMessage());
		}
		
		return vlist;
	}
	
}


RegisterBean.java

package pack_Login;

public class RegisterBean {
	
	private int num;
	private String id;
	private String pw;
	private String name;
	private String num1;
	private String num2;
	private String email;
	private String phone;
	private String zipcode;
	private String address;
	private String job;
	
	
	public int getNum() {
		return num;
	}
	public void setNum(int num) {
		this.num = num;
	}
	public String getId() {
		return id;
	}
	public void setId(String id) {
		this.id = id;
	}
	public String getPw() {
		return pw;
	}
	public void setPw(String pw) {
		this.pw = pw;
	}
	public String getName() {
		return name;
	}
	public void setName(String name) {
		this.name = name;
	}
	public String getNum1() {
		return num1;
	}
	public void setNum1(String num1) {
		this.num1 = num1;
	}
	public String getNum2() {
		return num2;
	}
	public void setNum2(String num2) {
		this.num2 = num2;
	}
	public String getEmail() {
		return email;
	}
	public void setEmail(String email) {
		this.email = email;
	}
	public String getPhone() {
		return phone;
	}
	public void setPhone(String phone) {
		this.phone = phone;
	}
	public String getZipcode() {
		return zipcode;
	}
	public void setZipcode(String zipcode) {
		this.zipcode = zipcode;
	}
	public String getAddress() {
		return address;
	}
	public void setAddress(String address) {
		this.address = address;
	}
	public String getJob() {
		return job;
	}
	public void setJob(String job) {
		this.job = job;
	}
	
}


SessionMemberLogin.jsp

<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
    
<%
	String memberId = (String)session.getAttribute("memID");
	
	if(memberId != null){
		
%>
	<script>
		alert("로그인 되었습니다.");
		location.href = "sessionLoginConfirm.jsp";
	
	</script>

<%
	}
%>
<!DOCTYPE html>
<html lang="ko">
<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>Document</title>
<link rel="stylesheet" href="style/style.css">
<style>

	body {
		
		background-color: #f8f8f8;
	}
	
	div#wrap {
	
		width: 600px;
		padding: 10px;
		margin: 20px auto;
	}
	
	table {
	
		width: 100%;
		border:1px solid black;
	}
	
	table td:first-child {
		
		width: 120px;
		text-align: right;	
	}
	
	caption {
	
		font-size: 26px;
		padding: 20px;
	}
	
	td, button, input {
		
		padding: 10px 10px;
		font-size: 22px;
	}
	
	span {
	
		font-size: 16px;
		display: block;
	}
</style>
</head>
<body>
	<div id="wrap">
	
		<h1>session 로그인</h1>
		
		<form action="SessionMemberLoginOk.jsp">
			
			<table>
				<caption>Login</caption>
				<tbody>
					<tr>
						<td>ID</td>
						<td>
							<input type="text" name="memberId" maxlength="10" autofocus="autofocus">
							<span>memberId를 받는 input 태그입니다.</span>
						</td>
					</tr>
					<tr>
						<td>PW</td>
						<td>
							<input type="text" name="memberPw" maxlength="10">
							<span>memberPw를 받는 input 태그입니다.</span>
						</td>
					</tr>
					<tr>
						<td colspan="2">
							<button>Login</button>
							<button type="reset">Reset</button>
						</td>
						<td></td>
					</tr>
				</tbody>
			</table>			
		</form> 
		<!-- form 끝 -->
	</div>
	<!-- div#wrap -->
<script src="https://ajax.googleapis.com/ajax/libs/jquery/3.6.0/jquery.min.js"></script>
<script src="script/script.js"></script>
</body>
</html>


SessionMemberLoginOk.jsp

<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
    
<jsp:useBean id="memMgr" class="pack_Login.RegisterMgr" scope="page"/>

<%
	String memberId = "";
	String memberPw = "";
	
	if(request.getParameter("memberId") != null) {
		
		memberId = request.getParameter("memberId");
	}
	
	if(request.getParameter("memberPw") != null) {
		
		memberPw = request.getParameter("memberPw");
	}
	
	if(memMgr.passCheck(memberId, memberPw)) {
		
		session.setAttribute("memID", memberId);

%>
	<script>
		let memberId = '<%=memberId%>';
		alert(memberId + "님 안녕하세요 !");
		location.href = "SessionLoginConfirm.jsp";
	</script>

<%
	} else {
		
%>
	<script>
		alert("로그인 정보를 확인해 주세요.");
		location.href = "SessionMemberLogin.jsp";
	</script>
<%
	}
%>
<!DOCTYPE html>
<html lang="ko">
<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>Document</title>
<link rel="stylesheet" href="style/style.css">
</head>
<body>
	<div id="wrap">
		
	</div>
	<!-- div#wrap -->
<script src="https://ajax.googleapis.com/ajax/libs/jquery/3.6.0/jquery.min.js"></script>
<script src="script/script.js"></script>
</body>
</html>


SessionLoginConfirm.jsp

<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<%
	String memberId = (String)session.getAttribute("memID");
	
	if(memberId == null) {
		
%>
	<script>
		alert("로그인 되지 않았습니다.");
		location.href = "SessionMemberLogin.jsp";
	</script>
<%
	}

%>
<!DOCTYPE html>
<html lang="ko">
<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>Document</title>
<link rel="stylesheet" href="style/style.css">
<style>

    	body {
    		background-color: #f8f8f8;
    	}
    	
    	div#wrap {
    		width: 600px;
    		padding: 10px;
    		margin: 20px auto;
    	}
    	
    	table {
    		width: 100%;
    		border: 1px solid #000;
    	}
    	caption {
    		font-size: 26px;
    		padding: 20px;
    	}
    	
    	td {
    		font-size: 22px;
    		text-align: center;
    		padding: 10px;    	
    	}
    	a {
    		font-size: 22px;
    		font-weight: bold;
    	}
    	
    
</style>
</head>
<body>
	<div id="wrap">
		<h1>session 로그인</h1>
		
		<table>
			<caption><%=memberId %>님 로그인 상태입니다.</caption>
			<tbody>
				<tr>
					<td>
						<a href="SessionLogOut.jsp">로그아웃</a>
					</td>
				</tr>
			</tbody>
		</table>
	</div>
	<!-- div#wrap -->
<script src="https://ajax.googleapis.com/ajax/libs/jquery/3.6.0/jquery.min.js"></script>
<script src="script/script.js"></script>
</body>
</html>


SessionLogOut.jsp

<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<% 
	session.invalidate();

%>
<script>

	alert("로그아웃 되었습니다.");
	location.href = "SessionMemberLogin.jsp";
</script>

테스트

  • 올바른 아이디 입력


  • 올바른 alert


  • 로그인 완료


  • 로그아웃




  • DB랑 다른 정보 입력


  • alert

Leave a comment