用MyEclipse连接URL解析json对象，并将数据以表格形式写入PostgreSQL数据库

前言：

       首先在高德平台上获取了key，并得到提取到部分杭州市公交站点的URL：http://restapi.amap.com/v3/place/text?&keywords=%E5%85%AC%E4%BA%A4%E7%AB%99%E7%82%B9&city=%E6%9D%AD%E5%B7%9E&output=json&offset=20&page=1&key=6b6ebee4f2f6ef4c55d1b4b52310acec&extensions=all

         需要将网页中的信息解析为此格式：

        

        并用Java在MyEclipse中写成表格形式，写入数据库Postgre中（主要是将pois中的内容写入）：

        这就是我们要完成的最终结果（如果用Python实现，会简单很多）。

一、建立Myeclipse与数据库的连接：

       创建工程Copy Final，包geturl，类Conn.java

package geturl;    import java.sql.Connection;  import java.sql.DriverManager;  import java.sql.PreparedStatement;  import java.sql.ResultSet;  import java.sql.SQLException;  import java.sql.Statement;  public class Conn {            /**      * @method getConn()      * @return Connection      */      public Connection getConn() {          String driver = "org.postgresql.Driver";  //输入建立链接用的jar包        String url = "jdbc:postgresql://localhost:5432/fanqi";  //输入所连接的数据库的位置        String username = "postgres";  //数据库的用户名        String password = "postgre";  //数据库密码        Connection conn = null;  //声明数据库连接对象                try {              Class.forName(driver); // 加载数据库驱动类            conn = (Connection) DriverManager.getConnection(url, username, password);              //初始化数据库连接            System.out.println("url");                    } catch (ClassNotFoundException e) {  //加载异常处理类            e.printStackTrace();          } catch (SQLException e) {  //处理代码运行时出现的异常            e.printStackTrace();          }                 return conn;      }          }

二、定义所要打断的位置（建类JsonBean）：

package geturl;import java.util.List;public class JsonBean {private String status;private String count;public String getStatus() {return status;}public void setStatus(String status) {this.status = status;}public String getCount() {return count;}public void setCount(String count) {this.count = count;}public String getInfo() {return info;}public void setInfo(String info) {this.info = info;}public String getInfocode() {return infocode;}public void setInfocode(String infocode) {this.infocode = infocode;}public SuggestionBean getSuggestion() {return suggestion;}public void setSuggestion(SuggestionBean suggestion) {this.suggestion = suggestion;}public List<PoisBean> getPois() {return pois;}public void setPois(List<PoisBean> pois) {this.pois = pois;}private String info;private String infocode;private SuggestionBean suggestion;private List<PoisBean> pois;public static class SuggestionBean {private List<?> keywords;private List<?> cities;public List<?> getKeywords() {return keywords;}public void setKeywords(List<?> keywords) {this.keywords = keywords;}public List<?> getCities() {return cities;}public void setCities(List<?> cities) {this.cities = cities;}}public static class PoisBean {private String id;private String name;private String tag;public String getId() {return id;}public void setId(String id) {this.id = id;}public String getName() {return name;}public void setName(String name) {this.name = name;}public String getTag() {return tag;}public void setTag(String tag) {this.tag = tag;}public String getType() {return type;}public void setType(String type) {this.type = type;}public String getTypecode() {return typecode;}public void setTypecode(String typecode) {this.typecode = typecode;}public String getBiz_type() {return biz_type;}public void setBiz_type(String biz_type) {this.biz_type = biz_type;}public String getAddress() {return address;}public void setAddress(String address) {this.address = address;}public String getLocation() {return location;}public void setLocation(String location) {this.location = location;}public String getTel() {return tel;}public void setTel(String tel) {this.tel = tel;}public String getPostcode() {return postcode;}public void setPostcode(String postcode) {this.postcode = postcode;}public String getWebsite() {return website;}public void setWebsite(String website) {this.website = website;}public String getEmail() {return email;}public void setEmail(String email) {this.email = email;}public String getPcode() {return pcode;}public void setPcode(String pcode) {this.pcode = pcode;}public String getPname() {return pname;}public void setPname(String pname) {this.pname = pname;}public String getCitycode() {return citycode;}public void setCitycode(String citycode) {this.citycode = citycode;}public String getCityname() {return cityname;}public void setCityname(String cityname) {this.cityname = cityname;}public String getAdcode() {return adcode;}public void setAdcode(String adcode) {this.adcode = adcode;}public String getAdname() {return adname;}public void setAdname(String adname) {this.adname = adname;}public String getImportance() {return importance;}public void setImportance(String importance) {this.importance = importance;}public String getShopid() {return shopid;}public void setShopid(String shopid) {this.shopid = shopid;}public String getShopinfo() {return shopinfo;}public void setShopinfo(String shopinfo) {this.shopinfo = shopinfo;}public String getPoiweight() {return poiweight;}public void setPoiweight(String poiweight) {this.poiweight = poiweight;}public String getGridcode() {return gridcode;}public void setGridcode(String gridcode) {this.gridcode = gridcode;}public String getDistance() {return distance;}public void setDistance(String distance) {this.distance = distance;}public String getNavi_poiid() {return navi_poiid;}public void setNavi_poiid(String navi_poiid) {this.navi_poiid = navi_poiid;}public String getEntr_location() {return entr_location;}public void setEntr_location(String entr_location) {this.entr_location = entr_location;}public String getBusiness_area() {return business_area;}public void setBusiness_area(String business_area) {this.business_area = business_area;}public String getExit_location() {return exit_location;}public void setExit_location(String exit_location) {this.exit_location = exit_location;}public String getMatch() {return match;}public void setMatch(String match) {this.match = match;}public String getRecommend() {return recommend;}public void setRecommend(String recommend) {this.recommend = recommend;}public String getTimestamp() {return timestamp;}public void setTimestamp(String timestamp) {this.timestamp = timestamp;}public String getAlias() {return alias;}public void setAlias(String alias) {this.alias = alias;}public String getIndoor_map() {return indoor_map;}public void setIndoor_map(String indoor_map) {this.indoor_map = indoor_map;}public String getIndoor_data() {return indoor_data;}public void setIndoor_data(String indoor_data) {this.indoor_data = indoor_data;}public String getGroupbuy_num() {return groupbuy_num;}public void setGroupbuy_num(String groupbuy_num) {this.groupbuy_num = groupbuy_num;}public String getDiscount_num() {return discount_num;}public void setDiscount_num(String discount_num) {this.discount_num = discount_num;}public String getBiz_ext() {return biz_ext;}public void setBiz_ext(String biz_ext) {this.biz_ext = biz_ext;}public String getEvent() {return event;}public void setEvent(String event) {this.event = event;}public String getChildren() {return children;}public void setChildren(String children) {this.children = children;}public String getPhotos() {return photos;}public void setPhotos(String photos) {this.photos = photos;}private String type;private String typecode;private String biz_type;private String address;private String location;private String tel;private String postcode;private String website;private String email;private String pcode;private String pname;private String citycode;private String cityname;private String adcode;private String adname;private String importance;private String shopid;private String shopinfo;private String poiweight;private String gridcode;private String distance;private String navi_poiid;private String entr_location;private String business_area;private String exit_location;private String match;private String recommend;private String timestamp;private String alias;private String indoor_map;private String indoor_data;private String groupbuy_num;private String discount_num;private String biz_ext;private String event;private String children;private String photos;}}

三、建立类Iputil，建立表格并写入数据库：

package geturl;import geturl.JsonBean.PoisBean;import java.net.*;import java.sql.Connection;import java.sql.ResultSet;import java.sql.SQLException;import java.sql.Statement;import java.util.ArrayList;import java.util.List;import java.io.*;import net.sf.json.JSONArray;import net.sf.json.JSONObject;public class Iputil{   public static void main(String [] args)   {   JsonBean jsonBean=new JsonBean();      try            {  //连接URL         URL url = new URL("http://restapi.amap.com/v3/place/text?&keywords=%E5%85%AC%E4%BA%A4%E7%AB%99%E7%82%B9&city=%E6%9D%AD%E5%B7%9E&output=json&offset=20&page=1&key=6b6ebee4f2f6ef4c55d1b4b52310acec&extensions=all");         URLConnection urlConnection = url.openConnection();//打开URL链接         HttpURLConnection connection = null;         if(urlConnection instanceof HttpURLConnection)//判断是否能打开URL         {            connection = (HttpURLConnection) urlConnection;//获取实例传入目标地址         }         else         {            System.out.println("请输入url");            return;         }         BufferedReader in = new BufferedReader(         new InputStreamReader(connection.getInputStream()));         String urlString = "";         String current;         while((current = in.readLine()) != null)         {            urlString += current;              }                           JSONObject json=JSONObject.fromObject(urlString);//澶ф嫭鍙穙bject 瀛楃涓瞫tring 鏁扮粍list                    jsonBean.setStatus(json.optString("status"));           jsonBean.setCount(json.optString("count"));           jsonBean.setInfo(json.optString("info"));           jsonBean.setInfocode(json.optString("infocode"));                      JSONObject suggestionObj=json.optJSONObject("suggestion");           JsonBean.SuggestionBean suggestion=new JsonBean.SuggestionBean();                      List<String> keywordsList=new ArrayList<>();           JSONArray keywordsArray=suggestionObj.optJSONArray("keywords");           for (int i = 0; i < keywordsArray.size(); i++) {      keywordsList.add(keywordsArray.optString(i));   }           suggestion.setKeywords(keywordsList);                      List<String> citiesList=new ArrayList<>();           JSONArray citiesArray=suggestionObj.optJSONArray("cities");           for (int i = 0; i < citiesArray.size(); i++) {           citiesList.add(citiesArray.optString(i));   }           suggestion.setCities(citiesList);                                 jsonBean.setSuggestion(suggestion);                                                       List<PoisBean> poisBeansList=new ArrayList<>();           JSONArray poisArray=json.optJSONArray("pois");           for (int i = 0; i < poisArray.size(); i++) {    JSONObject poisObj = poisArray.optJSONObject(i);    JsonBean.PoisBean poisBean=new JsonBean.PoisBean();    poisBean.setId(poisObj.optString("id"));    poisBean.setName(poisObj.optString("name"));    poisBean.setTag(poisObj.optString("tag"));    poisBean.setType(poisObj.optString("type"));    poisBean.setTypecode(poisObj.optString("typecode"));    poisBean.setBiz_type(poisObj.optString("biz_type"));        poisBean.setAddress(poisObj.optString("address"));    poisBean.setLocation(poisObj.optString("location"));    poisBean.setTel(poisObj.optString("tel"));    poisBean.setPostcode(poisObj.optString("postcode"));    poisBean.setWebsite(poisObj.optString("website"));    poisBean.setEmail(poisObj.optString("email"));    poisBean.setPcode(poisObj.optString("pcode"));    poisBean.setPname(poisObj.optString("pname"));    poisBean.setCitycode(poisObj.optString("citycode"));    poisBean.setCityname(poisObj.optString("cityname"));    poisBean.setAdcode(poisObj.optString("adcode"));    poisBean.setAdname(poisObj.optString("adname"));    poisBean.setImportance(poisObj.optString("importance"));    poisBean.setShopid(poisObj.optString("shopid"));    poisBean.setShopinfo(poisObj.optString("shopinfo"));    poisBean.setPoiweight(poisObj.optString("poiweight"));    poisBean.setGridcode(poisObj.optString("gridcode"));    poisBean.setDistance(poisObj.optString("distance"));    poisBean.setNavi_poiid(poisObj.optString("navi_poiid"));    poisBean.setEntr_location(poisObj.optString("entr_location"));    poisBean.setBusiness_area(poisObj.optString("business_area"));    poisBean.setExit_location(poisObj.optString("exit_location"));    poisBean.setMatch(poisObj.optString("match"));    poisBean.setRecommend(poisObj.optString("recommend"));    poisBean.setTimestamp(poisObj.optString("timestamp"));    poisBean.setAlias(poisObj.optString("alias"));    poisBean.setIndoor_map(poisObj.optString("indoor_map"));    poisBean.setIndoor_data(poisObj.optString("indoor_data"));    poisBean.setGroupbuy_num(poisObj.optString("groupbuy_num"));    poisBean.setDiscount_num(poisObj.optString("discount_num"));    poisBean.setBiz_ext(poisObj.optString("biz_ext"));    poisBean.setEvent(poisObj.optString("event"));    poisBean.setChildren(poisObj.optString("children"));    poisBean.setPhotos(poisObj.optString("photos"));       poisBeansList.add(poisBean);   }                      jsonBean.setPois(poisBeansList);        }catch(IOException e)      {         e.printStackTrace();      }                           Conn c=new Conn(); //连接数据库      Connection con=c.getConn();            try {       Statement sql;       ResultSet res;       int a;             sql=con.createStatement();//首次运行时去掉下面注释，在数据库中建立表格，建立好后，再加上注释，再次运行，向表中写入数据      /*sql.executeQuery("CREATE TABLE Map11 (id varchar(255), name varchar(255),tag varchar(255),"      + "type varchar(255),typecode varchar(255), biz_type varchar(255),address varchar(255),location varchar(255),"      + "tel varchar(255),postcode varchar(255),website varchar(255),email varchar(255),pcode varchar(255),pname varchar(255),"      + "citycode varchar(255), cityname varchar(255), adcode varchar(255),adname varchar(255), importance varchar(255),shopid varchar(255),shopinfo varchar(255),poiweight varchar(255),"      + "gridcode varchar(255),distance varchar(255),navi_poiid varchar(255),entr_location varchar(255),business_area varchar(255),exit_location varchar(255),"      + "match varchar(255),recommend varchar(255),timestamp varchar(255),alias varchar(255),indoor_map varchar(255),indoor_data varchar(255),"      + "groupbuy_num varchar(255),discount_num varchar(255),biz_ext varchar(255),event varchar(255),children varchar(255),photos varchar(400))  ");      */for (int i = 0; i < jsonBean.getPois().size(); i++) {a = sql.executeUpdate("insert into Map11 (id,name,tag,type,typecode,biz_type,address,location,tel,postcode,website,email,pcode,pname,"+ "citycode,cityname,adcode,adname,importance,shopid,shopinfo,poiweight,gridcode,distance,navi_poiid,entr_location,business_area,"+ "exit_location,match,recommend,timestamp,alias,indoor_map,indoor_data,groupbuy_num,discount_num,biz_ext,event,children,photos) "+ "values('"+jsonBean.getPois().get(i).getId()+"','"+jsonBean.getPois().get(i).getName()+"'"+ ",'"+jsonBean.getPois().get(i).getTag()+"','"+jsonBean.getPois().get(i).getType()+"','"+jsonBean.getPois().get(i).getTypecode()+"'"+ ",'"+jsonBean.getPois().get(i).getBiz_type()+"','"+jsonBean.getPois().get(i).getAddress()+"','"+jsonBean.getPois().get(i).getLocation()+"'  "+ ",'"+jsonBean.getPois().get(i).getTel()+"','"+jsonBean.getPois().get(i).getPostcode()+"','"+jsonBean.getPois().get(i).getWebsite()+"','"+jsonBean.getPois().get(i).getEmail()+"'"+ ",'"+jsonBean.getPois().get(i).getPcode()+"','"+jsonBean.getPois().get(i).getPname()+"','"+jsonBean.getPois().get(i).getCityname()+"','"+jsonBean.getPois().get(i).getCityname()+"'"    + ",'"+jsonBean.getPois().get(i).getAdcode()+"','"+jsonBean.getPois().get(i).getAdname()+"','"+jsonBean.getPois().get(i).getImportance()+"','"+jsonBean.getPois().get(i).getShopid()+"'"    + ",'"+jsonBean.getPois().get(i).getShopinfo()+"','"+jsonBean.getPois().get(i).getPoiweight()+"','"+jsonBean.getPois().get(i).getGridcode()+"','"+jsonBean.getPois().get(i).getDistance()+"'"    + ",'"+jsonBean.getPois().get(i).getNavi_poiid()+"','"+jsonBean.getPois().get(i).getEntr_location()+"','"+jsonBean.getPois().get(i).getBusiness_area()+"','"+jsonBean.getPois().get(i).getExit_location()+"'"    + ",'"+jsonBean.getPois().get(i).getMatch()+"','"+jsonBean.getPois().get(i).getRecommend()+"','"+jsonBean.getPois().get(i).getTimestamp()+"','"+jsonBean.getPois().get(i).getAlias()+"'"    + ",'"+jsonBean.getPois().get(i).getIndoor_map()+"','"+jsonBean.getPois().get(i).getIndoor_data()+"','"+jsonBean.getPois().get(i).getGroupbuy_num()+"','"+jsonBean.getPois().get(i).getDiscount_num()+"' "    + ",'"+jsonBean.getPois().get(i).getBiz_ext()+"','"+jsonBean.getPois().get(i).getEvent()+"','"+jsonBean.getPois().get(i).getChildren()+"','"+jsonBean.getPois().get(i).getPhotos()+"')");}} catch (Exception e) {e.printStackTrace();}               }}

四、注意所需引入的jar包：