tabledao.java

爬取网站信息

package test;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

import domain.Code;
import domain.ExeclItem;

public class TableDao {
	public void InsertCodeTable(Code code) {
		Connection conn = null;
		try {
			conn = DataBaseUtil.getConnection();
			String sqlInsertCodeTable = "insert into AERA_CODE_BAIDU(codeNumber,aeraName,Latitude,Longitude,north,south,east,west) values(?,?,?,?,?,?,?,?)";
			PreparedStatement ps = conn.prepareStatement(sqlInsertCodeTable);
			ps.setInt(1, code.getCodenumber());
			ps.setString(2, code.getAeraName());
			ps.setDouble(3, code.getLatitude());
			ps.setDouble(4, code.getLongitude());
			ps.setDouble(5, code.getNorth());
			ps.setDouble(6, code.getSouth());
			ps.setDouble(7, code.getEast());
			ps.setDouble(8, code.getWest());
			ps.execute();
		} catch(Exception e) {
			e.printStackTrace();
		} 
	}
	
	public long findCodeId(Integer codeNumber) {
		Connection conn = null;
		long codeId = 0;
		try {
			conn = DataBaseUtil.getConnection();
			String sqlfindCodeId = "select * from AERA_CODE_BAIDU where codeNumber=?";
			PreparedStatement ps = conn.prepareStatement(sqlfindCodeId);
			ps.setInt(1, codeNumber);
			ResultSet rs = ps.executeQuery();
			while(rs.next()) {
				codeId = rs.getLong(1);
			}
		} catch(Exception e) {
			e.printStackTrace();
		} 
		return codeId;
	}
	
	public void InsertStreet(String streetName,Long codeId) {
		Connection conn = null;
		try {
			conn = DataBaseUtil.getConnection();
			String sqlInsertStreet = "insert into STREET_BAIDU(streetName,codeId) values(?,?)";
			PreparedStatement ps = conn.prepareStatement(sqlInsertStreet);
			ps.setLong(2, codeId);
			ps.setString(1, streetName);
			ps.execute();
		} catch(Exception e) {
			e.printStackTrace();
		} 
	}
	
	public List getAllCodeNumbers() {
		List list = new ArrayList();
		Connection conn = null;
		try {
			conn = DataBaseUtil.getConnection();
			String sqlfindCodeId = "select * from AERA_CODE_BAIDU";
			PreparedStatement ps = conn.prepareStatement(sqlfindCodeId);
			ResultSet rs = ps.executeQuery();
			while(rs.next()) {
				String codeNumber = rs.getString("codeNumber");
				list.add(codeNumber);
			}
		} catch(Exception e) {
			e.printStackTrace();
		} 
		return list;
	}
	
	public void insertURL(String URLString) {
		Connection conn = null;
		try {
			conn = DataBaseUtil.getConnection();
			String sqlInsertStreet = "insert into URL_BAIDU(URLString) values(?)";
			PreparedStatement ps = conn.prepareStatement(sqlInsertStreet);
			ps.setString(1, URLString);
			ps.execute();
		} catch(Exception e) {
			e.printStackTrace();
		} 
	}
	
	public String getMinIdURL() {
		Connection conn = null;
		String URLString = null;
		try {
			conn = DataBaseUtil.getConnection();
			String sqlInsertStreet = "select * from URL_BAIDU where id=(select min(id) from URL_BAIDU)";
			PreparedStatement ps = conn.prepareStatement(sqlInsertStreet);
			ResultSet rs = ps.executeQuery();
			while(rs.next()) {
				URLString = rs.getString("URLString");
			}
			ps.execute();
		} catch(Exception e) {
			e.printStackTrace();
		} 
		return URLString;
	}
	
	public void deleteURL() {
		Connection conn = null;
		try {
			conn = DataBaseUtil.getConnection();
			String sql = "select min(id) from URL_BAIDU";
			PreparedStatement ps1 = conn.prepareStatement(sql);
			ResultSet rs = ps1.executeQuery();
			long id = 0L;
			while(rs.next()) {
				id = rs.getLong(1);
			}
			String sqlInsertStreet = "delete from URL_BAIDU where id = ?";
			PreparedStatement ps = conn.prepareStatement(sqlInsertStreet);
			ps.setLong(1, id);
			ps.execute();
		} catch(Exception e) {
			e.printStackTrace();
		} 
	}
	//寰楀埌琛ㄤ腑娌℃湁NSEW鍜宭ocation鐨勬潯鐩