주요내용 : 고객정보주문정보출력실습, DataSource연동
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<script type="text/javascript">
function fn_validate(){
var frmLogin = document.frmLogin;
var cust_id = frmLogin.cust_id.value;
if(cust_id.length == 0 || cust_id == ""){
alert("고객번호는 필수입니다.");
} else {
frmLogin.method = "get";
frmLogin.action = "customer";
frmLogin.submit();
}
}
</script>
<title>고객 주문 정보 조회</title>
</head>
<body>
<form name = "frmLogin" method = "get" action="customer" encType="UTF-8">
고객 주문번호 조회 : <input type = "text" name = "cust_id"><br>
<input type = "button" onClick="fn_validate()" value = "로그인">
<input type = "reset" value = "다시 입력">
<input type = "hidden" name="user_address" value = "서울시 성북구"/>
</form>
</body>
</html>
------------
package Week05.day0805;
import java.io.IOException;
import java.io.PrintWriter;
import java.sql.Date;
import java.util.List;
import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
/**
* Servlet implementation class CustomerServlet
*/
@WebServlet("/customer")
public class CustomerServlet extends HttpServlet {
private static final long serialVersionUID = 1L;
/**
* @see HttpServlet#doGet(HttpServletRequest request, HttpServletResponse response)
*/
protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
response.setContentType("text/html;charset=utf-8");
PrintWriter out = response.getWriter();
CustomerDAO dao = new CustomerDAO();
String input_id = request.getParameter("cust_id");
List list = dao.listMembers(input_id); // listMembers메소드로 회원정보를 조회
out.print("<html><body>");
out.print("<table border=1><tr align='center' bgcolor='lightgreen'>");
out.print("<td>고객번호</td><td>고객성명</td><td>주문번호</td><td>주문상품번호</td><td>주문상품명</td><td>");
for(int i = 0; i<list.size(); i++)
{
CustomerVO customerVO = (CustomerVO) list.get(i);
String cust_id = customerVO.getCust_id();
String cust_name = customerVO.getCust_name();
String order_num = customerVO.getOrder_num();
String order_item = customerVO.getOrder_item();
String prod_name = customerVO.getProd_name();
out.print("<tr><td>" + cust_id + "</td><td>" + cust_name +"</td><td>" + order_num + "</td><td>" + order_item + "</td><td>" + prod_name + "</td></tr>");
}
out.print("</table></body></html>");
}
}
-------
package Week05.day0805;
public class CustomerVO {
//고객번호, 고객명, 주문번호,주문상품번호, 주문상품명
private String cust_id;
private String cust_name;
private String order_num;
private String order_item;
private String prod_name;
public CustomerVO() {System.out.println("CustomerVO 생성자 호출");}
public void setCust_id(String cust_id) {this.cust_id = cust_id;}
public void setCust_name(String cust_name) {this.cust_name = cust_name;}
public void setOrder_num(String order_num) {this.order_num = order_num;}
public void setOrder_item(String order_item) {this.order_item = order_item;}
public void setProd_name(String prod_name) {this.prod_name = prod_name;}
public String getCust_id() {return cust_id;}
public String getCust_name() {return cust_name;}
public String getOrder_num() {return order_num;}
public String getOrder_item() {return order_item;}
public String getProd_name() {return prod_name;}
}
-----
package Week05.day0805;
import java.sql.Connection;
import java.sql.Date;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;
public class CustomerDAO {
private static final String driver= "oracle.jdbc.driver.OracleDriver";
private static final String url = "jdbc:oracle:thin:@localhost:1521:XE";
private static final String user ="scott";
private static final String pwd = "tiger";
private Connection con;
private PreparedStatement pstmt; // 실무에선 PreparedStatement를 더 많이씀.
public List listMembers(String input_id)
{
List list = new ArrayList();
try
{
connDB(); // 네가지 정보로 데이터베이스를 연결
String query = "SELECT c.cust_id , c.cust_name, o.order_num, oi.order_item, p.prod_name "
+ "FROM customers c, orders o, orderitems oi, products p "
+ "WHERE c.cust_id = o.cust_id "
+ "AND o.order_num = oi.order_num "
+ "AND oi.prod_id = p.prod_id "
+ "AND c.cust_id = " + "'"+input_id+"'";
System.out.println("preparedStatement : " + query);
pstmt = con.prepareStatement(query); // 파생된 SQL 재사용. preparedStatement 메소드에 sql문을 전달해 prepareStatement객체를 생성.
ResultSet rs = pstmt.executeQuery(); // sql문으로 회원 정보를 조회
while(rs.next())
{
// 조회한 레코드의 각 컬럼 값을 받아옴.
String cust_id = rs.getString("cust_id"); // " " 안에는 컬럼명이 와야함.!!!!!!!!!!!!!!
String cust_name =rs.getString("cust_name");
String order_num = rs.getString("order_num");
String order_item = rs.getString("order_item");
String prod_name = rs.getString("prod_name");
// 각 컬럼 값을 다시 MemberVO 객체의 속성에 설정.
CustomerVO vo = new CustomerVO();
vo.setCust_id(cust_id);
vo.setCust_name(cust_name);
vo.setOrder_num(order_num);
vo.setOrder_item(order_item);
vo.setProd_name(prod_name);
list.add(vo); // 설정된 MemberVO 객체를 다시 ArrayList에 저장.
}
rs.close();
pstmt.close();
con.close();
} catch (Exception e)
{
e.printStackTrace();
}
return list; // 조회한 레코드의 개수만큼 MemberVO객체를 저장한 ArrayList를 반환.
}
private void connDB()
{
try
{
Class.forName(driver);
System.out.println("Oracle 드라이버 로딩 성공");
con = DriverManager.getConnection(url, user, pwd);
System.out.println("Connection 생성 성공");
} catch (Exception e)
{
e.printStackTrace();
}
}
}
--------------
// <Resources> 태그를 이용해 톰캣 실행 시 연결할 데이터베이스를 설정
<?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>WEB-INF/tomcat-web.xml</WatchedResource>
<WatchedResource>${catalina.base}/conf/web.xml</WatchedResource>
<!-- Uncomment this to disable session persistence across Tomcat restarts -->
<!--
<Manager pathname="" />
-->
// Connection Pool을 생성하고 JNDI로 호출위한 정보설정
// 이 정보를 읽어서 Container 기동시 Connection Pool을 생성하고 Directory Server에 등록
<Resource
name="jdbc/oracle" // name의 jdbc/oravle로 DataSource에 접근. JNDI로 호출위한 정보설정
auth = "Container" // DBCP를 관리할 관리자. Container or Application
type = "javax.sql.DataSource" // 데이터베이스를 연결하는 데 필요한 네 가지 값을 설정.
// 해당 리소스의 리턴타입. DB종류별 DataSource
driverClassName = "oracle.jdbc.OracleDriver" // DB종류에 따른 JDBC Driver 클래스 이름
url = "jdbc:oracle:thin:@localhost:1521:XE" // DB 주소/포트/ SID(DB Instance)
username = "scott"
password = "tiger"
maxActive = "50" // 동시에 DB에 접속할 수 있는 수
maxWait = "-1" // 접속을 기다릴 수 있는 최대시간, -1은 무한대기
/>
</Context>
----------------
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>회원 가입창</title>
<script type="text/javascript">
function fn_sendMember(){
// 자바스크립트에서 form태그의 name으로 접근해 입력한 값들을 얻음.
var frmMember = document.frmMember;
var id = frmMember.id.value;
var pwd = frmMember.pwd.value;
var name = frmMember.name.value;
var email = frmMember.email.value;
if(id.length == 0 || id == ""){
alert("아이디는 필수입니다.");
} else if (pwd.length == 0 || pwd == ""){
alert("비밀번호는 필수입니다.");
} else if (name.length == 0 || name == ""){
alert("이름은 필수입니다.");
} else if (email.length == 0 || email == ""){
alert("이메일은 필수입니다.");
} else {
frmMember.method = "post";
frmMember.action = "member3";
frmMember.submit();
}
}
</script>
</head>
<body>
<form name="frmMember">
<table>
<th>회원 가입창</th>
<tr>
<td>아이디</td>
<td><input type="text" name="id"></td>
</tr>
<tr>
<td>비밀번호</td>
<td><input type="password" name="pwd"></td>
</tr>
<tr>
<td>이름</td>
<td><input type="text" name="name"></td>
</tr>
<tr>
<td>이메일</td>
<td><input type="text" name="email"></td>
</tr>
</table>
<input type="button" value="가입하기" onClick="fn_sendMember()">
<input type="reset" value="다시입력">
<input type="hidden" name="command" value="addMember"/>
// hidden 태그를 이용해 서블릿에게 회원 등록임을 알림.
</form>
</body>
</html>
-------------------
package sec02.ex02;
import java.io.IOException;
import java.io.PrintWriter;
import java.sql.Date;
import java.util.List;
import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
/**
* Servlet implementation class MemberServlet
*/
@WebServlet("/member3")
public class MemberServlet extends HttpServlet {
private static final long serialVersionUID = 1L;
/**
* @see HttpServlet#doGet(HttpServletRequest request, HttpServletResponse response)
*/
protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
doHandle(request, response);
}
/**
* @see HttpServlet#doPost(HttpServletRequest request, HttpServletResponse response)
*/
protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
doHandle(request, response);
}
protected void doHandle(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
request.setCharacterEncoding("utf-8");
response.setContentType("text/html;charset=utf-8");
MemberDAO dao = new MemberDAO();
PrintWriter out = response.getWriter();
String command = request.getParameter("command"); // command 에 addMember가 오느냐 delMember가 오느냐에 따라 분기됨.
if(command != null && command.equals("addMember")) // 회원가입창에서 전송된 command가 addMember이면 전송된 값들을 받아옴.
{
// insert 처리로직
// 회원가입창에서 전송된 값들을 얻어 와 MemberVO 객체에 저장한 후 SQL문을 이용해 전달.
String _id = request.getParameter("id");
String _pwd = request.getParameter("pwd");
String _name = request.getParameter("name");
String _email = request.getParameter("email");
MemberVO vo = new MemberVO();
vo.setId(_id);
vo.setPwd(_pwd);
vo.setName(_name);
vo.setEmail(_email);
dao.addMember(vo);
// delete 처리
// command 값이 delMember인 경우 ID를 가져와 SQL문으로 전달해서 삭제함.
} else if (command !=null && command.equals("delMember"))
{
String id = request.getParameter("id"); // id만 받아서 삭제
dao.delMember(id);
}
// add나 del 다음 조회를 다시하는 것이 일반적인 패턴.
// 추가삭제 후 재조회
List list = dao.listMembers(); // listMembers메소드로 회원정보를 조회
out.print("<html><body>");
out.print("<table border=1><tr align='center' bgcolor='lightgreen'>");
out.print("<td>아이디</td><td>비밀번호</td><td>이름</td><td>이메일</td><td>가입일</td><td>삭제</td></tr>");
for(int i = 0; i<list.size(); i++)
{
MemberVO memberVO = (MemberVO) list.get(i);
String id = memberVO.getId();
String pwd = memberVO.getPwd();
String name = memberVO.getName();
String email = memberVO.getEmail();
Date joinDate = memberVO.getJoinDate();
// 삭제를 클릭하면 command 값과 회원 id를 서블릿으로 전송.
out.print("<tr><td>" + id + "</td><td>" + pwd + "</td><td>" + name + "</td><td>" + email + "</td><td>" + joinDate + "</td><td>" + "<a href='/pro07/member3?command=delMember&id=" + id + "'> 삭제 </a></td></tr>");
}
out.print("</table></body></html>");
out.print("<a href='/pro07/memberForm.html'>새 회원 가입하기</a");
}
}
-------------------
package sec02.ex02;
import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import javax.naming.Context;
import javax.naming.InitialContext;
import javax.sql.DataSource;
public class MemberDAO {
private Connection con;
private PreparedStatement pstmt; // 실무에선 PreparedStatement를 더 많이씀.
private DataSource dataFactory;
public MemberDAO()
{
try
{
// Context : JNDI를 수행하는 객체로 DataSource를 제공한다.
// JDNI에 접근하기 위해 기본 경로(java:/comp/env)를 지정.
Context ctx = new InitialContext();
Context envContext = (Context)ctx.lookup("java:/comp/env");
// 톰캣 context.xml에 설정한 name 값인 jdbc/oracle을 이용해 톰캣이 미리 연결한
// DataSource를 받아 옴.
dataFactory = (DataSource)envContext.lookup("jdbc/oracle");
// JNDI로 context.xml으로 설정된 객체에 접근
} catch (Exception e)
{
e.printStackTrace();
}
}
public List listMembers()
{
List list = new ArrayList();
try
{
// DataSource를 이용해 데이터 베이스에 연결.
con = dataFactory.getConnection();
String query = "select * from t_member ";
System.out.println("preparedStatement : " + query);
pstmt = con.prepareStatement(query); // preparedStatement 메소드에 sql문을 전달해 prepareStatement객체를 생성.
ResultSet rs = pstmt.executeQuery(); // sql문으로 회원 정보를 조회
while(rs.next())
{
// 조회한 레코드의 각 컬럼 값을 받아옴.
String id = rs.getString("id");
String pwd = rs.getString("pwd");
String name = rs.getString("name");
String email = rs.getString("email");
Date joinDate = rs.getDate("joinDate");
// 각 컬럼 값을 다시 MemberVO 객체의 속성에 설정.
MemberVO vo = new MemberVO();
vo.setId(id);
vo.setPwd(pwd);
vo.setName(name);
vo.setEmail(email);
vo.setJoinDate(joinDate);
list.add(vo); // 설정된 MemberVO 객체를 다시 ArrayList에 저장.
}
rs.close();
pstmt.close();
con.close();
} catch (Exception e)
{
e.printStackTrace();
}
return list; // 조회한 레코드의 개수만큼 MemberVO객체를 저장한 ArrayList를 반환.
}
public void addMember(MemberVO memberVO)
{
try
{
// DataSource 이용해 데이터베이스와 연결
Connection con = dataFactory.getConnection();
// 테이블에 저장할 회원정보를 받아옴.
String id = memberVO.getId();
String pwd = memberVO.getPwd();
String name = memberVO.getName();
String email = memberVO.getEmail();
// insert문을 문자열로 만듬.
String query = "insert into t_member";
query += " (id,pwd,name,email)";
query += " values(?,?,?,?)"; // 순서대로 값을 assign 해야함.
System.out.println("prepareStatement: " + query);
pstmt = con.prepareStatement(query);
// insert문의 각 ? 순서대로 회원정보를 세팅
pstmt.setString(1, id);
pstmt.setString(2, pwd);
pstmt.setString(3, name);
pstmt.setString(4, email);
// 회원정보를 테이블에 추가.
pstmt.executeUpdate();
pstmt.close();
} catch (Exception e)
{
e.printStackTrace();
}
}
public void delMember(String id)
{
try
{
Connection con = dataFactory.getConnection();
// delete문을 문자열로 만듬.
String query = "delete from t_member" + " where id=? ";
System.out.println("prespareStatement: " + query);
pstmt = con.prepareStatement(query);
pstmt.setString(1, id); // 첫번째 ? 에 전달된 id를 인자로 넣음.
pstmt.executeUpdate(); // delete문을 실행해 테이블에서 해당 id의 회원정보를 삭제.
pstmt.close();
} catch (Exception e)
{
e.printStackTrace();
}
}
}
------------------
package sec02.ex02;
import java.sql.Date;
public class MemberVO {
private String id;
private String pwd;
private String name;
private String email;
private Date joinDate;
public MemberVO()
{
System.out.println("MeberVO 생성자 호출");
}
public String getId() {
return id;
}
public void setId(String id) {
this.id = id;
}
public String getPwd() {
return pwd;
}
public void setPwd(String pwd) {
this.pwd = pwd;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public String getEmail() {
return email;
}
public void setEmail(String email) {
this.email = email;
}
public Date getJoinDate() {
return joinDate;
}
public void setJoinDate(Date joinDate) {
this.joinDate = joinDate;
}
}
'Bitcamp > BITCAMP - Servlet & JSP' 카테고리의 다른 글
| 6일차 (0) | 2019.08.07 |
|---|---|
| 5일차 (0) | 2019.08.06 |
| 3일차 (0) | 2019.08.02 |
| JSP(Java Server Page) (0) | 2019.08.02 |
| 2일차 - 서블릿 (0) | 2019.08.01 |
