create database jspdb; /*사용할 db를 만든다*/
create user testuser identified by '1234'; /*사용자를 만듬*/
grant all on jspdb.* to 'testuser'@'%' identified by '1234'; /*권한설정*/
/*만들어진 user로 로그인 해본다
그리고 테이블을 만든다*/
/*테이블 만들기*/
CREATE TABLE `jspdb`.`member` (
`id` VARCHAR(20) NOT NULL,
`passwd` VARCHAR(45) NOT NULL,
`name` VARCHAR(45) NOT NULL,
`reg_date` TIMESTAMP NOT NULL,
`address` VARCHAR(100) NOT NULL,
`tel` VARCHAR(15) NOT NULL,
PRIMARY KEY (`id`));
/*데이터 넣기*/
insert into member values('member01','1234','테스터',CURRENT_TIMESTAMP,'부산','010-1234-5678');
http://commons.apache.org/components.html 가서 필요한
commons-collections4-4.0.jar
commons-dbcp2-2.1.jar
commons-pool2-2.3.jar
다운받아서 WEB-INF/lib에다 넣어준다
아파치 사이트로 가서 server.xml에 넣은 내용을 참조한다
http://tomcat.apache.org/tomcat-8.0-doc/jndi-datasource-examples-howto.html#MySQL_DBCP_Example
server.xml로 가서
Context 안에
<Context docBase="studyjsp" path="/studyjsp" reloadable="true" source="org.eclipse.jst.jee.server:studyjsp">
<Resource name="jdbc/jspdb"
auth="Container"
type="javax.sql.DataSource"
maxTotal="100"
maxIdle="30"
maxWaitMillis="10000"
username="testuser"
password="1234"
driverClassName="com.mysql.jdbc.Driver"
url="jdbc:mysql://localhost:3306/jspdb" />
<!-- name 사용자가 만든이름
user의 마지막 이름은 진짜 db이름을 적어 주어야 한다 -->
</Context>
넣어준다
그다음 GlobalNamingResources 안에 넣어준다
<GlobalNamingResources>
<!-- Editable user database that can also be used by
UserDatabaseRealm to authenticate users
-->
<Resource auth="Container" description="User database that can be updated and saved" factory="org.apache.catalina.users.MemoryUserDatabaseFactory" name="UserDatabase" pathname="conf/tomcat-users.xml" type="org.apache.catalina.UserDatabase"/>
<Resource name="jdbc/jspdb"
auth="Container"
type="javax.sql.DataSource"
maxTotal="100"
maxIdle="30"
maxWaitMillis="10000"
username="testuser"
password="1234"
driverClassName="com.mysql.jdbc.Driver"
url="jdbc:mysql://localhost:3306/jspdb" />
<!-- name 사용자가 만든이름
user의 마지막 이름은 진짜 db이름을 적어 주어야 한다 -->
</GlobalNamingResources>
Web-INF 폴더 web.xml 에 넣어준다
없으면 다른 프로젝트에 기본값 web.xml을 복사해온다
<resource-ref>
<description>DB Connection</description>
<res-ref-name>jdbc/jspdb</res-ref-name>
<res-type>javax.sql.DataSource</res-type>
<res-auth>Container</res-auth>
</resource-ref>
그다음 내용을 web.xml에 넣어준다
==============================================================================================================
확인 소스=====================================================================================================
==============================================================================================================
위치 : Java Resourcs
파일이름 : LogonDBBean.java
package ch10.logon;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import javax.naming.Context;
import javax.naming.InitialContext;
import javax.sql.DataSource;
public class LogonDBBean {
private static LogonDBBean instance = new LogonDBBean();
public static LogonDBBean getInstance(){
return instance;
}
private LogonDBBean(){
}
private Connection getConnection() throws Exception{
Context iniCtx = new InitialContext();
Context envCtv = (Context)iniCtx.lookup("java:comp/env");
DataSource ds = (DataSource)envCtv.lookup("jdbc/jspdb");
return (Connection)ds.getConnection();
}
public int userCheck(String id, String passwd)
{
Connection conn = null;
PreparedStatement pstmt = null;
ResultSet rs = null;
int x=-1;
String sql = "select passwd from member where id=?";
try
{
conn = getConnection();
pstmt = conn.prepareStatement(sql);
pstmt.setString(1, id);
rs = pstmt.executeQuery();
if(rs.next())
{
String dbpasswd = rs.getString("passwd");
if(dbpasswd.equals(passwd)){
x=1; //인증성공
}
else
{
x=0; //비밀번호 틀림
}
}
else
{
x=-1; //아이디가 없음;
}
}
catch(Exception e)
{
e.printStackTrace();
}
finally
{
try {
if(rs!=null)
{
rs.close();
}
if(pstmt!=null)
{
rs.close();
}
if(conn!=null)
{
conn.close();
}
}
catch (SQLException e)
{
// TODO Auto-generated catch block
e.printStackTrace();
}
}
return x;
}
}
====================================================================================================================
위치 : WebContent
파일이름 : sessionLogin.jsp
<%@ page language="java" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8"%>
<%
try{
String id="";
id = (String)session.getAttribute("id");
if((id==null)|| id.equals(""))
{
%>
<form method="post" action="sessionLoginPro.jsp">
<table>
<tr>
<td>아이디</td>
<td><input type="text" name="id"></td>
</tr>
<tr>
<td>패스워드</td>
<td><input type="text" name="passwd"></td>
</tr>
<tr>
<td colspan=2>
<input type="submit" value="Login">
<input type="reset" value="reset">
</td>
</tr>
</table>
</form>
<%
}
else
{
%>
<b><%=id%> 님 로그인 하셨습니다.
<form method="post" action="sessionLogout.jsp">
<input type="submit" value="로그아웃">
</form>
<%
}
}
catch(Exception e)
{
e.printStackTrace();
}
%>
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
<title>Insert title here</title>
</head>
<body>
</body>
</html>
================================================================================================================
위치 : WebContent
파일이름 : sessionLoginPro.jsp
<%@page import="java.sql.Connection"%>
<%@ page language="java" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8"%>
<%@
page import="ch10.logon.LogonDBBean"%>
<% request.setCharacterEncoding("utf-8"); %>
<%
String id=request.getParameter("id");
String passwd = request.getParameter("passwd");
LogonDBBean logon = LogonDBBean.getInstance();
int check = logon.userCheck(id, passwd);
if(check==1)
{
session.setAttribute("id", id);
response.sendRedirect("sessionLogin.jsp");
}
else if(check==0)
{
%>
<script type="text/javascript">
alert("비밀번호가 다릅니다.");
history.go(-1);
</script>
<%
}
else
{
%>
<script type="text/javascript">
alert("아이디가 틀립니다");
history.go(-1);
</script>
<% }
%>
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
<title>Insert title here</title>
</head>
<body>
</body>
</html>
================================================================================================================
위치 : WebContent
파일이름 : sessionLogout.jsp
<%@ page language="java" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8"%>
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<%
session.invalidate();//세션 무효화
%>
<script type="text/javascript">
alert("로그아웃되었습니다");
location.href="sessionLogin.jsp";
</script>
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
<title>Insert title here</title>
</head>
<body>
</body>
</html>
