Exception_Web Crawling

에러 코드

package freefree;

import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Iterator;

import org.jsoup.Jsoup;
import org.jsoup.nodes.Document;
import org.jsoup.nodes.Element;
import org.jsoup.select.Elements;

public class Crawling {

	static final String driverName = "oracle.jdbc.driver.OracleDriver";
	static final String url = "jdbc:oracle:thin:@localhost:1521:xe";
	static final String user = "ONI";
	static final String passwd = "1234";

	private static final String INSERT = "INSERT INTO PRODUCT (PK, NAME, PRICE) VALUES ((SELECT NVL(MAX(PK),0)+1 FROM PRODUCT), ?, ?)";

	public static void main(String[] args) {
		// 크롤링해서 ArrayList<ProductDTO> datas에 저장
		ArrayList<ProductDTO> datas = new ArrayList<ProductDTO>();
		final String url = "https://clubclio.co.kr/shop/goodsList/121000000000000#page1&filterCid=121000000000000&orderBy=best";
		Document doc = null;
		try {
			doc = Jsoup.connect(url).get();
		} catch (IOException e) {
			e.printStackTrace();
		}
		Elements elems = doc.select("dd.details_infos");
		Iterator<Element> itr = elems.iterator();
		ProductDTO pDTO = null;
		while (itr.hasNext()) {
			pDTO = new ProductDTO();
			Element el = itr.next();
			String name = el.select("div.details_title").text();
			String price1 = el.select("div.details_price strong:first-child").text().replace(",", "");
			int price = Integer.parseInt(price1);
//         System.out.println("이름 : " + name + " / 가격 : " + price);
			pDTO.setpName(name);
			pDTO.setpPrice(price);
			datas.add(pDTO);
		}

		// DB 연결
		Connection conn = null;
		try {
			Class.forName(driverName);
			conn = DriverManager.getConnection(url, user, passwd);
		} catch (ClassNotFoundException | SQLException e) {
			e.printStackTrace();
		}

		// Product객체 DB에 insert
		PreparedStatement pstmt = null;
		try {
			pstmt = conn.prepareStatement(INSERT);
			pstmt.setString(1, pDTO.getpName());
			pstmt.setInt(2, pDTO.getpPrice());
			pstmt.executeUpdate();
		}

		catch (SQLException e) {
			e.getStackTrace();
		}

		// DB 연결 해제
		try {
			pstmt.close();
			conn.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}

	}
}

 

Console

1. No suitable driver dound : 적절한 드라이버 찾을 수 없음
   • url이라는 변수가 2개 있었다.....크롤링할 페이지를 저장할 변수명 수정
2. Null Point Exception : Connection객체가 null
   • PreparedStatement 생성 위치 변경 : pstmt = conn.pre~(INSERT) 라인이 데이터베이서 연결이 성공한 try 블록 안에 위치 ㄱㄱ
   • pstmt 닫는 메소드도 해당 try 블록안에 위치

 

수정 코드

package freefree;

import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Iterator;

import org.jsoup.Jsoup;
import org.jsoup.nodes.Document;
import org.jsoup.nodes.Element;
import org.jsoup.select.Elements;

public class Crawling {

	static final String driverName = "oracle.jdbc.driver.OracleDriver";
	static final String url = "jdbc:oracle:thin:@localhost:1521:xe";
	static final String user = "ONI";
	static final String passwd = "1234";

	private static final String INSERT = "INSERT INTO PRODUCT (PK, NAME, PRICE) VALUES ((SELECT NVL(MAX(PK),0)+1 FROM PRODUCT), ?, ?)";

	public static void main(String[] args) {
		// 크롤링해서 ArrayList<ProductDTO> datas에 저장
		ArrayList<ProductDTO> datas = new ArrayList<ProductDTO>();
		final String weburl = "https://clubclio.co.kr/shop/goodsList/121000000000000#page1&filterCid=121000000000000&orderBy=best";
		Document doc = null;
		try {
			doc = Jsoup.connect(weburl).get();
		} catch (IOException e) {
			e.printStackTrace();
		}
		Elements elems = doc.select("dd.details_infos");
		Iterator<Element> itr = elems.iterator();
		ProductDTO pDTO = null;
		int count = 0; // 추가된 객체 개수를 세기 위한 변수
		while (itr.hasNext() && count < 5) { // 5개까지만 추가
			pDTO = new ProductDTO();
			Element el = itr.next();
			String name = el.select("div.details_title").text();
			String price1 = el.select("div.details_price strong:first-child").text().replace(",", "");
			int price = Integer.parseInt(price1);
//         System.out.println("이름 : " + name + " / 가격 : " + price);
			pDTO.setpName(name);
			pDTO.setpPrice(price);
			datas.add(pDTO);
			count++;
		}

		Connection conn = null;
		try {
			// DB 연결
			Class.forName(driverName);
			conn = DriverManager.getConnection(url, user, passwd);
			// Product객체 DB에 insert
			PreparedStatement pstmt = null;
			for (ProductDTO data : datas) {
				pstmt = conn.prepareStatement(INSERT);
				pstmt.setString(1, data.getpName());
				pstmt.setInt(2, data.getpPrice());
				int result = pstmt.executeUpdate();
				if (result <= 0) {
					System.out.println("SQL 구문 실패");
				}
			}
			pstmt.close();
		}

		catch (ClassNotFoundException | SQLException e) {
			e.printStackTrace();
		}

		// DB 연결 해제
		finally {
			try {
				if (conn != null) {
					conn.close();
				}
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}

	}
}