🎟️Culture🎟️/📒NCS📒

NCS 실기

들눈 2024. 1. 8. 20:00

connect system/1234

🪂varchar(2, char)🪂


1-0. 테이블 생성

더보기
CREATE TABLE DM_TBL (
 dmno NUMBER(8) NOT NULL PRIMARY KEY,
 custid VARCHAR2(10).
 maildate DATE,
 contents VARCHAR2(13 char),
 dept CHAR(2),
 grade CHAR(2),
 campain CHAR(2)
);
CREATE TABLE TABLE2 (
  COLUMN1 VARCHAR2(20) NOT NULL ,
  COLUMN2 VARCHAR2(20) NOT NULL ,
  COLUMN3 VARCHAR2(20) ,
  CONSTRAINT TABLE2_PK PRIMARY KEY 
  (
    COLUMN1 ,
    COLUMN2 
  )
);

2-0. 데이터 삽입.

더보기
INSERT INTO DM_TBL VALUES (1, 'aaa', TO_DATE('20171201', 'YYYYMMDD'),"ㅎㅇ", '10', '1', 'C1');

3-0. 데이터 선택 (join).

더보기
SELECT t1.dmno, t1.custid, t2.author, TO_CHAR(t1.maildate, 'YYYY-MM-DD'), t1.contents, t1.campain
FROM DM_TBL t1 JOIN BOOKINFO_TBL t2
ON t1.custid=t2.custid

3-1 ( count ) .

더보기
SELECT t2.area, COUNT(*)
FROM DM_TBL t1 JOIN BOOKINFO_TBL t2
ON t1.custid=t2.custid
GROUP BY t2.area;

dynamic web & create index.html   ?? 에 들어갈 내용은?

<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>title</title>
</head>
??
</html>
<body>
<h1 style="margin: 0 auto; text-align: center;">제조사명 조회</h1>
<form action="result.jsp">
	<table border="1" style="margin: 0 auto; text-align: center;">
		<tr >
			<td colspan="2">제품코드(4) 입력</td>
		</tr>
		<tr>
			<td>제품코드</td>
			<td><input type=text name="code"></td>
		</tr>
		<tr>
			<td colspan="2"><button>확인</button>
		</tr>
	</table>
</form>
</body>

create result.jsp == package : kr.ac.kopo

<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8" import="kr.co.kopo.*"%>
<%
	userInfo user = new userInfo("한국인","1004","2021-10-31");
	productInfo product = new productInfo();
	String code= request.getParameter("code");
	product.setCode(code);
	product.find();
%>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>Insert title here</title>
</head>
<body>
<h1 style="margin: 0 auto; text-align:center">제품코드 조회 결과</h1>
<hr>
<table border="1" style="margin: 0 auto;">
	<tr style="text-align:center">
		<td colspan="2">제조사 조회 결과</td>
	</tr>
	<tr>
		<td>제품코드</td>
		<td><%= product.getCode() %></td>
	</tr>
	<tr>
		<td>제조사명</td>
		<td><%= product.getVender() %></td>
	</tr>
	<tr>
		<td>조회일시</td>
		<td><%= product.getDate() %></td>
	</tr>
</table>
<hr>
<p style="margin: 0 auto; text-align:center">작성자: <%= user.getName() %> 비번: <%= user.getPs() %> 작성일: <%= user.getDate() %></p>
</body>
</html>

UserInfo.class

package kr.co.kopo;

public class userInfo {

	private String Name;
	private String Ps;
	private String Date;
	public String getName() {
		return Name;
	}
	public void setName(String name) {
		Name = name;
	}
	public String getPs() {
		return Ps;
	}
	public void setPs(String ps) {
		Ps = ps;
	}
	public String getDate() {
		return Date;
	}
	public void setDate(String date) {
		Date = date;
	}
	public userInfo(String name, String ps, String date) {
		super();
		Name = name;
		Ps = ps;
		Date = date;
	}
}

java beans의 기본 getter setter & 생성자에서 기본값을 쓸수있는 생성자 생성.

 create productInfo.class

package kr.co.kopo;

import java.util.Date;
import java.util.HashMap;

public class productInfo {
	private String code;
	private String vender;
	private Date date;
	public String getCode() {
		return code;
	}
	public void setCode(String code) {
		this.code = code;
	}
	public String getVender() {
		return vender;
	}
	public void setVender(String vender) {
		this.vender = vender;
	}
	public Date getDate() {
		return date;
	}
	public void setDate(Date date) {
		this.date = date;
	}
public void find() {
	HashMap<String, String> map = new HashMap<>();
	map.put("A100", "AYA");
	map.put("B100", "BYB");
	map.put("C100", "CYC");
	vender=map.get(code);
	date= new Date();
	if(vender==null) {
		vender="제조사 없음";
	}
}
}

 

service & DTO

result.jsp

<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8" import="kr.co.kopo.*"%>
<%
	userInfo user = new userInfo("한국인","1004","2021-10-31");
	productService productSer = new productService();
	String code= request.getParameter("code");
	productInfo product = productSer.findProductInfo(code);
%>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>Insert title here</title>
</head>
<body>
<h1 style="margin: 0 auto; text-align:center">제품코드 조회 결과</h1>
<hr>
<table border="1" style="margin: 0 auto;">
	<tr style="text-align:center">
		<td colspan="2">제조사 조회 결과</td>
	</tr>
	<tr>
		<td>제품코드</td>
		<td><%= product.getCode() %></td>
	</tr>
	<tr>
		<td>제조사명</td>
		<td><%= product.getVender() %></td>
	</tr>
	<tr>
		<td>조회일시</td>
		<td><%= product.getDate() %></td>
	</tr>
</table>
<hr>
<p style="margin: 0 auto; text-align:center">작성자: <%= user.getName() %> 비번: <%= user.getPs() %> 작성일: <%= user.getDate() %></p>
</body>
</html>

userInfo.java

package kr.co.kopo;

public class userInfo {

	private String Name;
	private String Ps;
	private String Date;
	public String getName() {
		return Name;
	}
	public void setName(String name) {
		Name = name;
	}
	public String getPs() {
		return Ps;
	}
	public void setPs(String ps) {
		Ps = ps;
	}
	public String getDate() {
		return Date;
	}
	public void setDate(String date) {
		Date = date;
	}
	public userInfo(String name, String ps, String date) {
		super();
		Name = name;
		Ps = ps;
		Date = date;
	}
}

productInfo

package kr.co.kopo;

import java.util.Date;

public class productInfo {
	private String code;
	private String vender;
	private Date date;
	
	public String getCode() {
		return code;
	}
	public void setCode(String code) {
		this.code = code;
	}
	public String getVender() {
		return vender;
	}
	public void setVender(String vender) {
		this.vender = vender;
	}
	public Date getDate() {
		return date;
	}
	public void setDate(Date date) {
		this.date = date;
	}
}

productService.java

package kr.co.kopo;

import java.util.Date;

public class productService {
    public productInfo findProductInfo(String code) {
        productInfo productDTO = new productInfo();
        if(code.equals("A")) {
    		productDTO.setVender("AAAAA");
    	} else {
    		productDTO.setVender("조회결과 없음");
    	}
        
        productDTO.setCode(code);
        productDTO.setDate(new Date());

        return productDTO;
    }
}
3번
CREATE TABLE custom_01 {
 p_id VARCHAR2(10) NOT NULL PRIMARY KEY,
 p_pw VARCHAR2(10),
 c_name VARCHAR2(20),
 c_email VARCHAR2(20),
 c_tel VARCHAR2(14)
};

ojdbc
cDrive.oraclexe.app.oracle.product.(11.2.0).server.jdbc.lib에 존재하는 ojdbc6 를 src내 WEB-INF 에 삽입.
WEPAPP 내 jsp 생성.

<body>
 <form action-"add_apply.jsp">
  <table border="1">
   <tr>
    <td></td>
    <td><input type="text" name="p_id"></td>
   </tr>
  </table>
 </form>
 <a href= "find.jsp"> <button></btn></a>
</body>

create add_apply.jsp
맨위에 import="java.sql.*" 추가

<%
 Class.forName("oracle.jdbc.driver.OracleDriver");
 
 Connection conn = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:xe","system","1234");
 
 PreparedStatement stmt = conn.prepareStatement("INSERT INTO custom_01 VALUES (?, ?, ?, ?, ?)");
 
 stmt.setString(1, request.getParameter("p_id"));
 stmt.setString(2, request.getParameter("p_pw"));
 stmt.setString(3, request.getParameter("c_name"));
 stmt.setString(4, request.getParameter("c_email"));
 stmt.setString(5, request.getParameter("c_tel"));
 
 stmt.execute();
%>


....
<body>
 <a href="add.jsp">register</a>
</body>

find.jsp 생성

<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8" import="java.sql.*"%>
<% 
 Class.forName("oracle.jdbc.driver.OracleDriver"); //driver는 있을수도 있고 없을 수 도 있고.
 
 Connection conn = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:xe", "system","1234");
 
 Statement stmt = conn.createStatement();
 
 ResultSet rs = stmt.executeQuery("SELECT p_id, c_name, c_email, c_tel FROM custom_01");
%>
<body>
 <h1>hm</h1>
 <hr>
 <table border="1">
 	<thead>
	 <tr>
		<th>아이디</th>
		<th>이름</th>
		<th>이메일</th>
		<th>93</th>
	 </tr>
	</thead>
	<tbody>
	 <% while (rs.next()) { %>
	 <tr>
		<td><%= rs.getString(1) %></td>
		<td><%= rs.getString(2) %></td>
		<td><%= rs.getString(3) %></td>
		<td><%= rs.getString(4) %></td>
	 </tr>
	 <% } %>
	</tbody>
</table>
<hr>
<h6>HRDKOREA Copyright&copy;2015 All rights reserved. Human Resource Devel </h6>
</body>