이안의 평일코딩

프로젝트3차 -1 sql + 데이터긁기 본문

Back-end/Team Project

프로젝트3차 -1 sql + 데이터긁기

이안92 2020. 11. 18. 19:16
반응형
CREATE TABLE park_reply(
    no NUMBER, --댓글번호
    bno NUMBER NOT NULL, --글번호
    name VARCHAR2(100),
    id VARCHAR2(20) NOT NULL, --글쓴이아이디
    msg CLOB NOT NULL, --댓글내용
    regdate DATE DEFAULT SYSDATE, --댓글작성일시
    group_id NUMBER,
    group_step NUMBER DEFAULT 0,
    group_tab NUMBER DEFAULT 0,
    root NUMBER DEFAULT 0,
    depth NUMBER DEFAULT 0
);

CREATE TABLE dog_board_reply1(
    no NUMBER, --댓글번호
    bno NUMBER NOT NULL, --글번호
    id VARCHAR2(20) NOT NULL, --글쓴이아이디
    msg CLOB NOT NULL, --댓글내용
    regdate DATE DEFAULT SYSDATE, --댓글작성일시
    group_id NUMBER,
    group_step NUMBER DEFAULT 0,
    group_tab NUMBER DEFAULT 0,
    root NUMBER DEFAULT 0,
    depth NUMBER DEFAULT 0
);

CREATE TABLE dog_board_reply2(
    no NUMBER, --댓글번호
    bno NUMBER NOT NULL, --글번호
    id VARCHAR2(20) NOT NULL, --글쓴이아이디
    msg CLOB NOT NULL, --댓글내용
    regdate DATE DEFAULT SYSDATE, --댓글작성일시
    group_id NUMBER,
    group_step NUMBER DEFAULT 0,
    group_tab NUMBER DEFAULT 0,
    root NUMBER DEFAULT 0,
    depth NUMBER DEFAULT 0
);

CREATE TABLE dog_board3(
    no NUMBER, --게시판글번호
    name VARCHAR2(20) NOT NULL, --닉네임
    pwd VARCHAR2(20) NOT NULL, --비밀번호
    msg CLOB NOT NULL, --내용
    regdate DATE DEFAULT SYSDATE, --작성일시
    ip VARCHAR2(45),
    group_id NUMBER,
    group_step NUMBER DEFAULT 0,
    group_tab NUMBER DEFAULT 0,
    root NUMBER DEFAULT 0,
    depth NUMBER DEFAULT 0
);


CREATE TABLE dog_plan(
    no NUMBER,
    id VARCHAR2(20) NOT NULL,
    dno NUMBER NOT NULL, --dog테이블 글번호
    loc VARCHAR2(45), --위치 서울시 25개 구
    des VARCHAR2(45), --산책지 destination
    day VARCHAR2(45), --산책일자
    time VARCHAR2(10), --산책시간
    dur NUMBER --경과시간
);

Table DOG_BOARD1이(가) 생성되었습니다.

Table DOG_BOARD2이(가) 생성되었습니다.

 

ifuwanna.tistory.com/250

 

[MacOS] 특정 포트의 프로세스 강제 종료하기 (Unix, Linux)

맥북이나 아이맥같은 Mac OS 환경에서 개발시 이클립스나 IntelliJ같은 IDE가 비정상적으로 종료 되거나 하여 WAS 실행시 여전히 포트가 떠있어 WAS 실행이 안되는 경우가 있는데 이런경우 특정 포트

ifuwanna.tistory.com

톰캣 8080오류

 

 

ParkVO

package com.sist.web;
/*
 * CREATE TABLE park(
	no NUMBER,
	name VARCHAR2(1000),
	img VARCHAR2(1000),
	content CLOB,
	zone VARCHAR2(100),
	addr VARCHAR2(1000),
	visit_road CLOB,
	latitude VARCHAR2(100),
	longitude VARCHAR2(100),
	star VARCHAR2(10),
	time VARCHAR2(10),
	regdate DATE DEFAULT SYSDATE,
	hit NUMBER DEFAULT 0,
	tag1 VARCHAR2(10),
	tag2 VARCHAR2(10),
	tag3 VARCHAR2(10)
);
 */
import java.util.*;
public class ParkVO {
	private int no, hit;
	private String name, img, content, zone, addr, visit_road,
	latitude, longitude;
	private Date regdate;
	public int getNo() {
		return no;
	}
	public void setNo(int no) {
		this.no = no;
	}
	public int getHit() {
		return hit;
	}
	public void setHit(int hit) {
		this.hit = hit;
	}
	public String getName() {
		return name;
	}
	public void setName(String name) {
		this.name = name;
	}
	public String getImg() {
		return img;
	}
	public void setImg(String img) {
		this.img = img;
	}
	public String getContent() {
		return content;
	}
	public void setContent(String content) {
		this.content = content;
	}
	public String getZone() {
		return zone;
	}
	public void setZone(String zone) {
		this.zone = zone;
	}
	public String getAddr() {
		return addr;
	}
	public void setAddr(String addr) {
		this.addr = addr;
	}
	public String getVisit_road() {
		return visit_road;
	}
	public void setVisit_road(String visit_road) {
		this.visit_road = visit_road;
	}
	public String getLatitude() {
		return latitude;
	}
	public void setLatitude(String latitude) {
		this.latitude = latitude;
	}
	public String getLongitude() {
		return longitude;
	}
	public void setLongitude(String longitude) {
		this.longitude = longitude;
	}
	public Date getRegdate() {
		return regdate;
	}
	public void setRegdate(Date regdate) {
		this.regdate = regdate;
	}
	
}

 

ParkDAO

package com.sist.ParkInfoService;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.util.Date;

import com.sist.ParkInfoService.ParkVO;

public class ParkDAO {
	private Connection conn;
    //     Ŭ   SQL      ϴ      
    private PreparedStatement ps;
    // URL
    private final String URL = "jdbc:oracle:thin:@211.238.142.195:1521:XE";
    //     ̹     
    public ParkDAO () {
       try {
          Class.forName("oracle.jdbc.driver.Oracle.Driver");
       } catch (Exception e) {
          System.out.println(e.getMessage());
       }
    }
    
    //     
    public void getConnection() {
       try {
          conn = DriverManager.getConnection(URL, "hr", "happy");
       } catch (Exception e) {
          System.out.println(e.getMessage());
       }
    }
    
    //     
    public void disConnection() {
       try {
          if(ps != null) {
             ps.close();
          }
          if(conn != null) {
             conn.close();
          }
       } catch (Exception e) {
          
       }
    }
    
    /*
    mno NUMBER(3),
    title VARCHAR2(300),
    singer VARCHAR2(100),
    album VARCHAR2(200),
    poster VARCHAR2(1000),
    state CHAR(6),
    idcrement NUMBER(3),
    key VARCHAR2(50)
     */
    
    //      ߰ 
    public void parkInsert(ParkVO vo) {   //  Ű        MusicVO  ϳ             ϱ 
       try {
          getConnection();   //     
          
//      no NUMBER,
//      name VARCHAR2(1000),
//    	img VARCHAR2(1000),
//    	content CLOB,
//    	zone VARCHAR2(100),
//    	addr VARCHAR2(1000),
//    	visit_road CLOB,
//    	latitude VARCHAR2(100),
//    	longitude VARCHAR2(100),
//    	star VARCHAR2(10),
//    	time VARCHAR2(10),
//    	regdate DATE DEFAULT SYSDATE,
//    	hit NUMBER DEFAULT 0,
//    	tag1 VARCHAR2(10),
//    	tag2 VARCHAR2(10),
//    	tag3 VARCHAR2(10)
// 
//       PreparedStatement       ١ڡ١ڡ١ڡ١ڡ١ڡ١ڡ١ڡ١ڡ١ڡ١ڡ١ڡ١ڡ١ڡ١ڡ١ڡ١ڡ١ڡ١ڡ١ڡ١ڡ١ڡ١ڡ١ڡ١ڡ١ڡ١ڡ١ڡ١ڡ١ڡ١ڡ١ڡ١ڡ١ڡ١ڡ١ڡ١ڡ١ڡ١ڡ١ڡ١ 
          String sql = "INSERT INTO park VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?)";
          ps = conn.prepareStatement(sql);
          ps.setInt(1, vo.getNo());
          ps.setString(2, vo.getName());
          ps.setString(3, vo.getImg());
          ps.setString(4, vo.getContent());
          ps.setString(5, vo.getZone());
          ps.setString(6, vo.getAddr());
          ps.setString(7, vo.getVisit_road());
          ps.setString(8, vo.getLatitude());
          ps.setString(9, vo.getLongitude());
          // SQL        ޱ 
          ps.executeUpdate();      // executeQuery :       ޾ƿ    (SELECT)      executeUpdate :          ,           (INSERT, UPDATE)
       } 
       
       
       catch (Exception e) {
          System.out.println(e.getMessage());
       }
       finally {
          disConnection();   //     
       }
    }
}

 

Manager

package com.sist.ParkInfoService;

import java.io.InputStreamReader;
import java.net.HttpURLConnection;
import java.net.URL;
import java.net.URLEncoder;

import javax.xml.parsers.DocumentBuilder;
import javax.xml.parsers.DocumentBuilderFactory;

import org.w3c.dom.Document;
import org.w3c.dom.Element;
import org.w3c.dom.Node;
import org.w3c.dom.NodeList;

import java.io.BufferedReader;
import java.io.IOException;

 
public class Manager {
    public static void main(String[] args) throws Exception {
       
    	ParkDAO dao = new ParkDAO();
       
       
        DocumentBuilderFactory factory = DocumentBuilderFactory.newInstance();
        DocumentBuilder documentBuilder = factory.newDocumentBuilder();
        Document doc = documentBuilder.parse("http://openapi.seoul.go.kr:8088/466d716a4d69616e35306a4a574f57/xml/SearchParkInfoService/1/132/");
    
        Element root = doc.getDocumentElement();
        
//        System.out.println(root.getTagName());
    
        NodeList list = root.getElementsByTagName("row");
        
        /*
         * ps.setInt(1, vo.getNo());
          ps.setString(2, vo.getName());
          ps.setString(3, vo.getImg());
          ps.setString(4, vo.getContent());
          ps.setString(5, vo.getZone());
          ps.setString(6, vo.getAddr());
          ps.setString(7, vo.getVisit_road());
          ps.setString(8, vo.getLatitude());
          ps.setString(9, vo.getLongitude());
         */
        
        int no = 1;
        for(int i = 0; i < list.getLength(); i++) {
           Element element = (Element)list.item(i);
           System.out.println("번호 : " + no);
           System.out.println("공원명 : " + element.getElementsByTagName("P_PARK").item(0).getTextContent());
           System.out.println("이미지 : " + element.getElementsByTagName("P_IMG").item(0).getTextContent());
           System.out.println("내용 : " + element.getElementsByTagName("P_LIST_CONTENT").item(0).getTextContent());
           System.out.println("지역 : " + element.getElementsByTagName("P_ZONE").item(0).getTextContent());
           System.out.println("주소 : " + element.getElementsByTagName("P_ADDR").item(0).getTextContent());
           System.out.println("가는길 : " + element.getElementsByTagName("VISIT_ROAD").item(0).getTextContent());
           System.out.println("위도 : " + element.getElementsByTagName("LATITUDE").item(0).getTextContent());
           System.out.println("경도 : " + element.getElementsByTagName("LONGITUDE").item(0).getTextContent());
           System.out.println("\n ======================================================== \n");
           
           ParkVO vo = new ParkVO();
           vo.setNo(no);
           vo.setName(element.getElementsByTagName("P_PARK").item(0).getTextContent());
           vo.setImg(element.getElementsByTagName("P_IMG").item(0).getTextContent());
           vo.setContent(element.getElementsByTagName("P_LIST_CONTENT").item(0).getTextContent());
           vo.setZone(element.getElementsByTagName("P_ZONE").item(0).getTextContent());
           vo.setAddr(element.getElementsByTagName("P_ADDR").item(0).getTextContent());
           vo.setVisit_road(element.getElementsByTagName("VISIT_ROAD").item(0).getTextContent());
           vo.setLatitude(element.getElementsByTagName("LATITUDE").item(0).getTextContent());
           vo.setLongitude(element.getElementsByTagName("LONGITUDE").item(0).getTextContent());
           dao.parkInsert(vo);
           
           Thread.sleep(100);
           
           no++;
           
        }
        
        System.out.println("완료");
        
        
    }
}

 

 

desc park;
desc park_reply;
desc dog_board1;
desc dog_board3;
desc dog_board_reply1;

 

desc park;

이름         널? 유형             
---------- -- -------------- 
NO            NUMBER         
NAME          VARCHAR2(1000) 
IMG           VARCHAR2(1000) 
CONTENT       CLOB           
ZONE          VARCHAR2(100)  
ADDR          VARCHAR2(1000) 
VISIT_ROAD    CLOB           
LATITUDE      VARCHAR2(100)  
LONGITUDE     VARCHAR2(100)  
STAR          VARCHAR2(10)   
TIME          VARCHAR2(20)   
REGDATE       DATE           
HIT           NUMBER         
LOVE          NUMBER         
TAG1          VARCHAR2(10)   
TAG2          VARCHAR2(10)   
TAG3          VARCHAR2(10)   
ESWN          VARCHAR2(20)

desc park_reply;

이름         널?       유형            
---------- -------- ------------- 
NO                  NUMBER        
BNO        NOT NULL NUMBER        
NAME       NOT NULL VARCHAR2(100) 
ID         NOT NULL VARCHAR2(20)  
MSG        NOT NULL CLOB          
REGDATE             DATE          
GROUP_ID            NUMBER        
GROUP_STEP          NUMBER        
GROUP_TAB           NUMBER        
ROOT                NUMBER        
DEPTH               NUMBER        

desc dog_board1;

이름        널?       유형             
--------- -------- -------------- 
NO                 NUMBER         
ID                 VARCHAR2(20)   
SUBJECT            VARCHAR2(100)  
CONTENT   NOT NULL CLOB           
PWD       NOT NULL VARCHAR2(20)   
REGDATE            DATE           
HIT                NUMBER         
POSTER             VARCHAR2(100)  
FILENAME           VARCHAR2(1000) 
FILESIZE           VARCHAR2(1000) 
FILECOUNT          NUMBER      

desc dog_board3;

이름         널?       유형           
---------- -------- ------------ 
NO                  NUMBER       
NAME       NOT NULL VARCHAR2(100) 
PWD        NOT NULL VARCHAR2(20) 
MSG        NOT NULL CLOB         
REGDATE             DATE         
IP                  VARCHAR2(45) 
GROUP_ID            NUMBER       
GROUP_STEP          NUMBER       
GROUP_TAB           NUMBER       
ROOT                NUMBER       
DEPTH               NUMBER    

desc dog_board_reply1;

이름         널?       유형           
---------- -------- ------------ 
NO                  NUMBER       
BNO        NOT NULL NUMBER       
ID         NOT NULL VARCHAR2(20) 
MSG        NOT NULL CLOB         
REGDATE             DATE         
GROUP_ID            NUMBER       
GROUP_STEP          NUMBER       
GROUP_TAB           NUMBER       
ROOT                NUMBER       
DEPTH               NUMBER
반응형

'Back-end > Team Project' 카테고리의 다른 글

국비 Spring 프로젝트 DB설계 및 화면UI  (4) 2020.11.15
2차 프로젝트  (0) 2020.10.16
'Back-end/Team Project' Related Articles more
Comments