689
No
SQL
CREATE TABLE MEMBER2
(
USERID VARCHAR2 (50),
PASSWD VARCHAR2 (50),
NAME VARCHAR2 (50),
REG_DATE DATE DEFAULT SYSDATE,
ADDRESS VARCHAR2 (500),
TEL VARCHAR2 (50)
)
context.xml
<?xml version="1.0" encoding="UTF-8"?>
<!--
Licensed to the Apache Software Foundation (ASF) under one or more
contributor license agreements. See the NOTICE file distributed with
this work for additional information regarding copyright ownership.
The ASF licenses this file to You under the Apache License, Version 2.0
(the "License"); you may not use this file except in compliance with
the License. You may obtain a copy of the License at
http://www.apache.org/licenses/LICENSE-2.0
Unless required by applicable law or agreed to in writing, software
distributed under the License is distributed on an "AS IS" BASIS,
WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
See the License for the specific language governing permissions and
limitations under the License.
--><!-- The contents of this file will be loaded for each web application --><Context>
<!-- Default set of monitored resources. If one of these changes, the -->
<!-- web application will be reloaded. -->
<WatchedResource>WEB-INF/web.xml</WatchedResource>
<WatchedResource>${catalina.base}/conf/web.xml</WatchedResource>
<!-- Uncomment this to disable session persistence across Tomcat restarts -->
<!--
<Manager pathname="" />
-->
<!-- Uncomment this to enable Comet connection tacking (provides events
on session expiration as well as webapp lifecycle) -->
<!--
<Valve className="org.apache.catalina.valves.CometConnectionManagerValve" />
-->
<Resource name="jdbc/myoracle" auth="Container"
type="javax.sql.DataSource"
driverClassName="oracle.jdbc.OracleDriver"
url="jdbc:oracle:thin:@127.0.0.1:1521:xe"
username="java" password="1111" maxTotal="20" maxIdle="10"
maxWaitMillis="-1"/>
</Context>
class DB
package config;
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 DB {
public static Connection getConn(){
DataSource ds =null;//javax.sql.DataSource
Connection conn=null;
try{
//context.xml 파일을 분석하는 객체
Context context = new InitialContext();
// context.xml 파일의 Resource 태그의 name 속성 검색
// java:comp/env/리소스태그의name
ds=(DataSource)context.lookup("java:comp/env/jdbc/myoracle");
// 커넥션풀에서 커넥션을 리턴받음
conn=ds.getConnection();
}catch(Exception e){
e.printStackTrace();
}
return conn;
}
public static void closed(Connection conn, PreparedStatement pstmt){
try{
if(pstmt!=null)pstmt.close();
}catch(Exception e){
e.printStackTrace();
}
try{
if(conn!=null)conn.close();
}catch(Exception e){
e.printStackTrace();
}
}
public static void closed(Connection conn, PreparedStatement pstmt, ResultSet rs){
try{
if(rs!=null)rs.close();
}catch(Exception e){
e.printStackTrace();
}
try{
if(pstmt!=null)pstmt.close();
}catch(Exception e){
e.printStackTrace();
}
try{
if(conn!=null)conn.close();
}catch(Exception e){
e.printStackTrace();
}
}
}
class MemberController
package member;
import java.io.IOException;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import javax.servlet.RequestDispatcher;
import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
@WebServlet("/member_servlet/*")
public class MemberController extends HttpServlet {
private static final long serialVersionUID = 1L;
//요청한 주소(url)
protected void doGet(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {
//컨텍스트 주소(웹프로텍트의 식별자)
String url =request.getRequestURL().toString();
String context=request.getContextPath();
MemberDAO dao =new MemberDAO();
if(url.indexOf("list.do")!=-1){//list.do 이면
Map<String, Object> map =new HashMap<>();
List<MemberDTO> list=dao.memberList();
map.put("list", list);//맵에 자료 저장
map.put("count", list.size());//레코드 갯수
request.setAttribute("map", map);//출력할 변수 저장
String page="/ch09/member_list.jsp";
RequestDispatcher rd
=request.getRequestDispatcher(page);
rd.forward(request, response);//화면 전환
}else if(url.indexOf("join.do")!=-1){
//사용자가 입력한 값
String userid=request.getParameter("userid");
String passwd=request.getParameter("passwd");
String name=request.getParameter("name");
String address=request.getParameter("address");
String tel=request.getParameter("tel");
MemberDTO dto =new MemberDTO();
dto.setUserid(userid);
dto.setPasswd(passwd);
dto.setName(name);
dto.setAddress(address);
dto.setTel(tel);
System.out.println(dto.toString());
//dao method 호출, affected rows 를 리턴받음
int rows=dao.insert(dto);
String message="";
if(rows >0 ){//insert 성공
message="회원 가입 처리가 완료되었습니다.";
}else{//insert 실패
message="회원 가입 처리 과정에서 에러가 발생했습니다.<br>"
+ "다시 한번 시도해 주시기 바랍니다.";
}
request.setAttribute("result", message);
String page="/ch09/login_result.jsp";
RequestDispatcher rd
= request.getRequestDispatcher(page);
rd.forward(request, response);
}else if( url.indexOf("view.do")!=-1){
//클릭한 아이디
String userid=request.getParameter("userid");
//System.out.println(userid);
MemberDTO dto =dao.memberDetail(userid);
//포워딩(주소는 고정, 화면전환+데이터 전달)
request.setAttribute("dto", dto);
//포워딩(주소는 고정, 화면전환+데이터 전달)
String page="/ch09/member_view.jsp";
RequestDispatcher rd=
request.getRequestDispatcher(page);
rd.forward(request, response);
}else if(url.indexOf("update.do")!=-1){
MemberDTO dto =new MemberDTO();
dto.setAddress(request.getParameter("address"));
dto.setName(request.getParameter("name"));
dto.setPasswd(request.getParameter("passwd"));
dto.setTel(request.getParameter("tel"));
dto.setUserid(request.getParameter("userid"));
dao.update(dto);
//페이지 이동
response.sendRedirect(context+"/ch09/member.jsp");
}else if(url.indexOf("deleteMember.do")!=-1){
String userid=request.getParameter("userid");
System.out.println("userid : " +userid);
dao.delete(userid);
//페이지 이동
response.sendRedirect(context+"/ch09/member.jsp");
}
}
protected void doPost(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {
doGet(request, response);
}
}
class MemberDAO
package member;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;
import config.DB;
public class MemberDAO {
public String loginCheck(String userid, String passwd){
String result=null;
Connection conn=null;
PreparedStatement pstmt =null;
ResultSet rs=null;
try{
conn=DB.getConn();//DB 커넥션 리턴
String sql=" select name from member2 where USERID=? and passwd=? ";
pstmt=conn.prepareStatement(sql);
pstmt.setString(1, userid);//1번 물음표
pstmt.setString(2, passwd);//2번 물음표
rs=pstmt.executeQuery();
if(rs.next()){//로그인 성공하면 이름을 저장
result=rs.getString("name");
}
}catch(Exception e){
e.printStackTrace();
}finally{
DB.closed(conn, pstmt,rs);
}
return result;
}
public List<MemberDTO> memberList(){
List<MemberDTO> list =new ArrayList<>();
Connection conn=null;
PreparedStatement pstmt=null;
ResultSet rs=null;
try{
conn=DB.getConn();//DB 커넥션 리턴
String sql ="select * from member2 ";
pstmt=conn.prepareStatement(sql);
rs=pstmt.executeQuery();
while(rs.next()){
MemberDTO dto =new MemberDTO();
dto.setAddress(rs.getString("address"));
dto.setName(rs.getString("name"));
dto.setPasswd(rs.getString("passwd"));
dto.setReg_date(String.valueOf(rs.getTimestamp("reg_date")));
dto.setTel(rs.getString("tel"));
dto.setUserid(rs.getString("userid"));
list.add(dto);
}
}catch(Exception e){
e.printStackTrace();
}finally{
DB.closed(conn, pstmt,rs);
}
return list;
}
public int insert(MemberDTO dto){
int rows=0;
Connection conn=null;
PreparedStatement pstmt =null;
try{
String sql ="insert into MEMBER2 (USERID, PASSWD, NAME, ADDRESS, TEL) "
+ " VALUES ( ?, ?, ?, ? , ?) ";
conn=DB.getConn();
pstmt =conn.prepareStatement(sql);
pstmt.setString(1, dto.getUserid());
pstmt.setString(2, dto.getPasswd());
pstmt.setString(3, dto.getName());
pstmt.setString(4, dto.getAddress());
pstmt.setString(5, dto.getTel());
//insert 실행 후 성공한 레코드 갯수
//affected rows(영향을 받은 행)
rows=pstmt.executeUpdate();
}catch(Exception e){
e.printStackTrace();
}finally{
DB.closed(conn, pstmt);
}
return rows;
}
public MemberDTO memberDetail(String userid){
MemberDTO dto=null;
Connection conn=null;
PreparedStatement pstmt =null;
ResultSet rs=null;
try{
String sql ="select * from member2 where userid =?";
conn=DB.getConn();
pstmt=conn.prepareStatement(sql);
pstmt.setString(1, userid);
rs=pstmt.executeQuery();
if(rs.next()){
dto=new MemberDTO();
dto.setAddress(rs.getString("address"));
dto.setName(rs.getString("name"));
dto.setPasswd(rs.getString("passwd"));
dto.setReg_date(String.valueOf(rs.getTimestamp("reg_date")));
dto.setTel(rs.getString("tel"));
dto.setUserid(rs.getString("userid"));
}
}catch(Exception e){
e.printStackTrace();
}finally{
DB.closed(conn, pstmt,rs);
}
return dto;
}
public void update(MemberDTO dto){
Connection conn=null;
PreparedStatement pstmt=null;
int result=0;
try{
conn=DB.getConn();
String sql ="update member2 set passwd=? , name=?, address =?, tel=? where userid=? ";
pstmt=conn.prepareStatement(sql);
pstmt.setString(1, dto.getPasswd());
pstmt.setString(2, dto.getName());
pstmt.setString(3, dto.getAddress());
pstmt.setString(4, dto.getTel());
pstmt.setString(5, dto.getUserid());
result =pstmt.executeUpdate();
System.out.println(dto.toString());
System.out.println("result : " + result);
}catch(Exception e){
e.printStackTrace();
}finally{
DB.closed(conn, pstmt);
}
}
public void delete(String userid){
Connection conn=null;
PreparedStatement pstmt =null;
try{
conn=DB.getConn();
String sql="delete from member2 where userid=?";
pstmt=conn.prepareStatement(sql);
pstmt.setString(1, userid);
pstmt.executeUpdate();
}catch(Exception e){
e.printStackTrace();
}finally{
DB.closed(conn, pstmt);
}
}
}
class MemberDTO
package member;
public class MemberDTO {
private String userid ;
private String passwd;
private String name;
private String reg_date;
private String address;
private String tel;
public String getUserid() {
return userid;
}
public void setUserid(String userid) {
this.userid = userid;
}
public String getPasswd() {
return passwd;
}
public void setPasswd(String passwd) {
this.passwd = passwd;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public String getReg_date() {
return reg_date;
}
public void setReg_date(String reg_date) {
this.reg_date = reg_date;
}
public String getAddress() {
return address;
}
public void setAddress(String address) {
this.address = address;
}
public String getTel() {
return tel;
}
public void setTel(String tel) {
this.tel = tel;
}
@Override
public String toString() {
return "MemberDTO [userid=" + userid + ", passwd=" + passwd + ", name=" + name + ", reg_date=" + reg_date
+ ", address=" + address + ", tel=" + tel + "]";
}
}
index.jsp
<%@ page language="java" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8"%>
<!DOCTYPE html>
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
<title>Insert title here</title>
<script src="http://code.jquery.com/jquery-3.2.1.min.js"></script>
</head>
<body>
<!-- ch09/index.jsp -->
<%
// 컨텍스트 패스 (웹프로젝트의 식별자)
String path =request.getContextPath();
%>
<a href="<%=path %>/member_servlet/list.do">회원목록</a>
</body>
</html>
login_result.jsp
<%@ page language="java" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8"%>
<!DOCTYPE html>
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
<title>Insert title here</title>
<script src="http://code.jquery.com/jquery-3.2.1.min.js"></script>
</head>
<body>
로그인 페이지
<br>
${result }
</body>
</html>
member_list.jsp
<%@page import="member.MemberDTO"%>
<%@page import="java.util.List"%>
<%@page import="java.util.Map"%>
<%@ page language="java" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8"%>
<!DOCTYPE html>
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
<title>Insert title here</title>
<script src="http://code.jquery.com/jquery-3.2.1.min.js"></script>
<%
String path=request.getContextPath();
%>
<script>
function view(userid){
location.href="<%= path %>/member_servlet/view.do?userid="+userid;
}
</script>
</head>
<body>
<!-- member_list.jsp -->
<%
Map<String, Object> map
=(Map<String, Object>)request.getAttribute("map");
List<MemberDTO> list=(List<MemberDTO>)map.get("list");
int count =(Integer)map.get("count");
%>
<h2>회원수 : <%= count %></h2>
<table border="1">
<tr>
<th>아이디</th>
<th>이름</th>
<th>비번</th>
<th>가입날짜</th>
<th>주소</th>
<th>전화</th>
</tr>
<%
for(MemberDTO dto : list) {
%>
<tr>
<td><a href="#" onclick="view('<%=dto.getUserid() %>')"><%= dto.getUserid() %></a></td>
<td><%= dto.getName() %></td>
<td><%= dto.getPasswd() %></td>
<td><%= dto.getReg_date() %></td>
<td><%= dto.getAddress() %></td>
<td><%= dto.getTel() %></td>
</tr>
<% } %>
</table>
</body>
</html>
member_view.jsp
<%@ page language="java" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8"%>
<!DOCTYPE html>
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
<title>Insert title here</title>
<script src="http://code.jquery.com/jquery-3.2.1.min.js"></script>
<%
String path =request.getContextPath(); //컨텍스트 패스
%>
<script type="text/javascript">
$(function(){
//id 가 btnUpdate 인 버튼을 누르면
$("#btnUpdate").click(function(){
//폼의 처리 주소를 지정
document.form1.action="<%= path %>/member_servlet/update.do";
//폼에 입력한 데이터를 서버로 제출함
document.form1.submit();
});
$("#btnDelete").click(function(){
//확인 버튼을 누르면 삭제 처리
if(confirm("삭제 하시겠습니까?")){
//폼의 처리 주소를 지정
document.form1.action="<%= path %>/member_servlet/deleteMember.do";
//폼에 입력한 데이터를 서버로 제출함
document.form1.submit();
}
});
});
</script>
</head>
<body>
<!-- ch09/member_view.jsp -->
<form name="form1" method="post">
<table border="1">
<tr>
<td>아이디</td>
<td>${dto.userid }</td>
</tr>
<tr>
<td>비밀번호</td>
<td><input type="password" name="passwd" value="${dto.passwd }"></td>
</tr>
<tr>
<td>이름</td>
<td><input name="name" value="${dto.name }"></td>
</tr>
<tr>
<td>주소</td>
<td><input value="${dto.address }" name="address"></td>
</tr>
<tr>
<td>전화번호</td>
<td><input value="${dto.tel }" name="tel"></td>
</tr>
<tr>
<td colspan="2" align="center" >
<input type="hidden" name=userid value="${dto.userid }">
<input type="button" value="수정" id="btnUpdate">
<input type="button" value="삭제" id="btnDelete">
</td>
</tr>
</table>
</form>
</body>
</html>
member.jsp
<%@ page language="java" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8"%>
<!DOCTYPE html>
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
<title>Insert title here</title>
<script src="http://code.jquery.com/jquery-3.2.1.min.js"></script>
<script type="text/javascript">
$(function(){ //웹페이지를 다 읽으면 자동 실행
list();
//id가 btnSave 인 버튼을 클릭하면 insert() 함수 실행
$("#btnSave").click(function(){
insert();
});
});
function insert(){
// 컨트롤러로 넘길 파라미터 구성
var userid=$("#userid");
var passwd=$("#passwd");
var name=$("#name");
var address=$("#address");
var tel=$("#tel");
$.ajax({
type:"post",
url:"<%= request.getContextPath()%>/member_servlet/join.do",
data:{
userid:userid.val(),
passwd :passwd.val(),
address :address.val(),
name : name.val(),
tel:tel.val()
},
success:function(result){
//서버에서 isert 가 완료되면 목록 갱신
list();
userid.val("");
passwd.val("");
name.val("");
address.val("");
tel.val("");
userid.focus();
}
});
}
function list(){
//비동기식 호출(백그라운드에서 실행됨)
$.ajax({
type:"get",
url:"<%= request.getContextPath() %>/member_servlet/list.do",
success:function(result){
//콜백 함수(처리가 완료된 후 실행됨)
$("#memberList").html(result);
}
});
}
</script>
</head>
<body>
<h2>회원 관리</h2>
<!-- 회원 정보 입력 -->
아이디 <input id="userid"><br>
비밀번호 <input type="password" id="passwd"> <br>
이름 <input id="name" > <br>
주소 <input id="address" ><br>
전화 <input id="tel" > <br>
<button id="btnSave">추가</button>
<div id="memberList"></div> <!-- 이곳에 목록이 출력됨 -->
</body>
</html>
=>http://localhost:1112/web03_jsp/ch09/
|
|
=> http://localhost:1112/web03_jsp/member_servlet/list.do
회원수 : 16
|
=> http://localhost:1112/web03_jsp/member_servlet/view.do?userid=admm2
|
||||||||||||
=>http://localhost:1112/web03_jsp/ch09/member.jsp 수정후
회원 관리아이디비밀번호 이름 주소 전화 추가 회원수 : 16
|
ttt 삭제후
http://localhost:1112/web03_jsp/ch09/member.jsp
|
회원 관리아이디비밀번호 이름 주소 전화 추가 회원수 : 15
|
일괄 처리 작업 실행
SQL
insert into EMP2 (EMPNO, ENAME, DEPTNO)
values(00, 'kk', '00');
select * from emp2;
delete from emp2;
ALTER table emp2 modify empno number;
ALTER table emp2 modify ename VARCHAR2(50);
ALTER table emp2 modify deptno number;
commit;
class EmpDAO
pstmt.addBatch();
package emp.dao;
import java.sql.Connection;
import java.sql.PreparedStatement;
import config.DB;
public class EmpDAO {
// 1. 안좋은 예
public void insert(){
Connection conn=null;
PreparedStatement pstmt=null;
try{
conn=DB.getConn();
conn.setAutoCommit(false);//autoComit 해제
long before=System.currentTimeMillis();
for(int i=1; i<=1000000; i++){
String sql ="insert into EMP2 (EMPNO, ENAME, DEPTNO) values(?, ?, ?)";
pstmt=conn.prepareStatement(sql);
pstmt.setInt(1, i);
pstmt.setString(2, "kim" +i);
pstmt.setInt(3, i);
pstmt.executeUpdate();
pstmt.close();
}
long after =System.currentTimeMillis();
conn.commit();
conn.setAutoCommit(true);
System.out.println("실행시간 : " + (after-before));
}catch(Exception e){
e.printStackTrace();
}finally{
DB.closed(conn, pstmt);
}
}
//2.좋은 방법 addBatch
public void insert_batch(){
Connection conn=null;
PreparedStatement pstmt=null;
try{
conn=DB.getConn();
conn.setAutoCommit(false);//autoComit 해제
String sql ="insert into EMP2 (EMPNO, ENAME, DEPTNO) values(?, ?, ?)";
pstmt=conn.prepareStatement(sql);
long before=System.currentTimeMillis();
for(int i=1; i<=1000000; i++){
pstmt.setInt(1, i);
pstmt.setString(2, "kim" +i);
pstmt.setInt(3, i);
pstmt.addBatch();
}
pstmt.executeBatch();//일괄 처리 작업 실행
conn.commit();
conn.setAutoCommit(true);
long after =System.currentTimeMillis();
System.out.println("실행시간 : " + (after-before));
}catch(Exception e){
e.printStackTrace();
}finally{
DB.closed(conn, pstmt);
}
}
}
transaction.jsp
<%@page import="emp.dao.EmpDAO"%>
<%@ page language="java" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8"%>
<!DOCTYPE html>
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
<title>Insert title here</title>
<script src="http://code.jquery.com/jquery-3.2.1.min.js"></script>
</head>
<body>
<%
EmpDAO dao=new EmpDAO();
//dao.insert();
dao.insert_batch();
%>
</body>
</html>
댓글 ( 4)
댓글 남기기