본문 바로가기
DATA BASE

웹서버와 DB연동 하기 (조인)

Linda~! 2020. 4. 29. 17:55

 CustomerList

<%@ page import="java.sql.*" contentType="text/html;charset=utf-8" %>
<%
Class.forName("oracle.jdbc.driver.OracleDriver");
String url="jdbc:oracle:thin:@localhost:1521:xe";
Connection dbconn=DriverManager.getConnection(url, "c##madang", "c##madang");
Statement stmt = dbconn.createStatement();
ResultSet myResultSet=stmt.executeQuery("SELECT custid, name, address, phone FROM customer");
%>
<html>

<head>
<meta http-equiv="content-type" content="text/html; charset=utf-8">
<title>** CUSTOMER LIST **</title>
</head>

<body bgcolor="white" text="black" link="blue" vlink="purple" alink="red">
<table border="1" cellspacing="0" width="400" bordercolor="#9AD2F7"
  bordercolordark="white" bordercolorlight="#B9E0FA">  
 <tr>
  <td width="150" height="20" bgcolor="#D2E9F9">
   <p align="center">
   <span style="font-size:8pt;"><b>아이디</b></span></p>
  </td>
  <td width="150" height="20" bgcolor="#D2E9F9">
   <p align="center">
   <span style="font-size:8pt;"><b>성명</b></span></p>
  </td>
  <td width="150" height="20" bgcolor="#D2E9F9">
   <p align="center">
   <span style="font-size:8pt;"><b>주소</b></span></p>
  </td>
  <td width="150" height="20" bgcolor="#D2E9F9">
   <p align="center">
   <span style="font-size:8pt;"><b>전화번호</b></span></p>
  </td> 
 </tr> 
<%
if(myResultSet!=null){
while(myResultSet.next()){
 String W_CUSTID  = myResultSet.getString("custid");
 String W_NAME	  = myResultSet.getString("name");
 String W_ADDRESS = myResultSet.getString("address");
 String W_PHONE	  = myResultSet.getString("phone");
 %> 
 <tr>
  <td width="150" height="20">
   <p align="center"><span style="font-size:9pt;">
   <font face="돋움체" color="black">
   <%=W_CUSTID%></font></span></p>
  </td>
  <td width="150" height="20">
   <p align="center"><span style="font-size:9pt;">
   <a href="customerview.jsp?r_name=<%=W_NAME%>">
   <font face="돋움체"><%=W_NAME%></font></a></span></p>
  </td>
  <td width="150" height="20">
   <p align="center"><span style="font-size:9pt;">
   <font face="돋움체"><%=W_ADDRESS%></font></span></p>
  </td>
  <td width="150" height="20">
   <p align="center"><span style="font-size:9pt;">
   <font face="돋움체"><%=W_PHONE%></font></span></p>
  </td>  
 </tr>
<% 
 }
 }
 stmt.close();
 dbconn.close();
%>  
</table>
<table cellpadding="0" cellspacing="0" width="400" height="23">
 <tr>
  <td width="1350">
   <p align="right"><b><a href=booklist.jsp>
   <font size="1" face="돋움체" color="black">LIST</font></a></b></p>
  </td>
 </tr>
</table>
</body>
</html>

CustomerView

<%@ page import="java.sql.*" contentType="text/html;charset=utf-8" %>
<%
Class.forName("oracle.jdbc.driver.OracleDriver");
String url="jdbc:oracle:thin:@localhost:1521:xe";
Connection dbconn=DriverManager.getConnection(url, "c##madang", "c##madang");
Statement stmt = dbconn.createStatement();
ResultSet myResultSet=stmt.executeQuery("SELECT custid, name, address, phone FROM customer");
%>
<html>


<head>
<meta http-equiv="content-type" content="text/html; charset=utf-8">
<title>** CUSTOMER LIST **</title>
</head>

<body bgcolor="white" text="black" link="blue" vlink="purple" alink="red">
<table border="1" cellspacing="0" width="400" bordercolor="#9AD2F7"
  bordercolordark="white" bordercolorlight="#B9E0FA">  
 <tr>
  <td width="150" height="20" bgcolor="#D2E9F9">
   <p align="center">
   <span style="font-size:8pt;"><b>아이디</b></span></p>
  </td>
  <td width="150" height="20" bgcolor="#D2E9F9">
   <p align="center">
   <span style="font-size:8pt;"><b>성명</b></span></p>
  </td>
  <td width="150" height="20" bgcolor="#D2E9F9">
   <p align="center">
   <span style="font-size:8pt;"><b>주소</b></span></p>
  </td>
  <td width="150" height="20" bgcolor="#D2E9F9">
   <p align="center">
   <span style="font-size:8pt;"><b>전화번호</b></span></p>
  </td> 
 </tr> 
<%
if(myResultSet!=null){
while(myResultSet.next()){
 String W_CUSTID  = myResultSet.getString("custid");
 String W_NAME	  = myResultSet.getString("name");
 String W_ADDRESS = myResultSet.getString("address");
 String W_PHONE	  = myResultSet.getString("phone");
 %> 
 <tr>
  <td width="150" height="20">
   <p align="center"><span style="font-size:9pt;">
   <font face="돋움체" color="black">
   <%=W_CUSTID%></font></span></p>
  </td>
  <td width="150" height="20">
   <p align="center"><span style="font-size:9pt;">
   <a href="customerview.jsp?r_name=<%=W_NAME%>">
   <font face="돋움체"><%=W_NAME%></font></a></span></p>
  </td>
  <td width="150" height="20">
   <p align="center"><span style="font-size:9pt;">
   <font face="돋움체"><%=W_ADDRESS%></font></span></p>
  </td>
  <td width="150" height="20">
   <p align="center"><span style="font-size:9pt;">
   <font face="돋움체"><%=W_PHONE%></font></span></p>
  </td>  
 </tr>
<% 
 }
 }
 stmt.close();
 dbconn.close();
%>  
</table>
<table cellpadding="0" cellspacing="0" width="400" height="23">
 <tr>
  <td width="1350">
   <p align="right"><b><a href=booklist.jsp>
   <font size="1" face="돋움체" color="black">LIST</font></a></b></p>
  </td>
 </tr>
</table>
</body>
</html>

 

 

- CustomerList 목록 뜨기

​

- 박지성을 클릭하면 박지성이 구매한 책의 출판사와 같은 출판사에서 구매한 고객 이름이 뜬다.

​

​

- 김연아를 클릭하면 김연아가 구매한 책의 출판사와 같은 출판사에서 구매한 고객 이름이 뜬다.

 

'DATA BASE' 카테고리의 다른 글

자바 - DB 연동  (0) 2020.05.07
값초기화(NULL하고 다른 거 차이), NVL, VIEW, 인덱스  (0) 2020.05.06
무결성 정확성 일관성  (0) 2020.04.27
JSP(JavaServer Pages) 맛보기  (0) 2020.04.23
설계 - 데이터 모델링  (0) 2020.04.23

'DATA BASE' Related Articles

티스토리툴바