任務(wù)：有1000多條門店信息（放在excel中，包括地址，店名，電話等，但是沒有經(jīng)緯度坐標(biāo)），老大讓我用地址通過百度地圖拾取坐標(biāo)系統(tǒng)找到相應(yīng)的坐標(biāo)，然后加上坐標(biāo)后更新到公司的數(shù)據(jù)庫(kù)。
失敗的方案：1、使用按鍵精靈，按鍵精靈是一個(gè)模仿鍵盤鼠標(biāo)操作的軟件，用來寫動(dòng)作腳本的，由于時(shí)間緊，沒怎么研究，因?yàn)檎讋?dòng)作太復(fù)雜了按鍵精靈嘗試了下不行就放棄了。
2、表單填充工具（就是把exel表格批量提交到網(wǎng)頁(yè)），什么風(fēng)越、烏溜漆（特別是這烏溜漆，還要錢，坑貨）都嘗試了下，結(jié)果都不滿意。因?yàn)槲乙裡xcel中的內(nèi)容提交到網(wǎng)頁(yè)還要從網(wǎng)頁(yè)獲得相應(yīng)的內(nèi)容，所以這些用于批量注冊(cè)的軟件用不上。
解決方案：最后還是干起了我本行---寫代碼，把問題解決了。思路是：通過傳入地址作為參數(shù)拼接url調(diào)用百度地圖，然后解析返回的頁(yè)面，提取經(jīng)緯度坐標(biāo)。
/*
* 管理數(shù)據(jù)庫(kù)連接的類
*/
public class DbManager{
private Connection con = null ;
private Statement sta = null ;
private ResultSet rs = null ;
private PreparedStatement ps = null ;
private Connection cons = null ;
private Statement stas = null ;
private ResultSet rss = null ;
private PreparedStatement pss = null ;
//連接本地mysql參數(shù) ?后面的參數(shù)是解決中文亂碼的
private String MYSQLDRIVER="com.mysql.jdbc.Driver" ;
private String CONTENT="jdbc:mysql://127.0.0.1:3306/test?useUnicode=true&characterEncoding=utf8";
private String UN="***";
private String UP="****";
//連接服務(wù)器mysql參數(shù)
private String MYSQLDRIVER1="com.mysql.jdbc.Driver" ;
private String CONTENT1="jdbc:mysql://***********:3306/test?useUnicode=true&characterEncoding=utf8";
private String UN1="*******";
private String UP1="****";
public DbManager()
{
try {
Class.forName(MYSQLDRIVER);
System.out.println("加載MySQL驅(qū)動(dòng)...");
con = DriverManager.getConnection(CONTENT,UN,UP);
sta = con.createStatement();
System.out.println("連接本地?cái)?shù)據(jù)庫(kù)成功?。?);
Class.forName(MYSQLDRIVER1);
System.out.println("加載MySQL驅(qū)動(dòng)...");
cons = DriverManager.getConnection(CONTENT1,UN1,UP1);
stas = cons.createStatement();
System.out.println("連接服務(wù)器成功！！");
} catch (Exception e) {
e.printStackTrace();
}
}
public ArrayList<ShopInfo> getAll(String tablename) throws SQLException{
ArrayList<ShopInfo> allShops=new ArrayList();
ShopInfo si;
String sql="select * from "+tablename;
System.out.println(sql);
rs=sta.executeQuery(sql);
while(rs.next()){
si=new ShopInfo();
si.setAddress(rs.getString("address"));
si.setDescription(rs.getString("names")+"歡迎您的光臨");
si.setMobile(rs.getString("keeperphone"));
si.setScope(tablename);
si.setPhone(rs.getString("shoptel"));
getPoint(si);
allShops.add(si);
System.out.println("經(jīng)度:"+si.getLat()+" 緯度:"+si.getLng());
}
return allShops;
}
//-------------------------》關(guān)鍵代碼根據(jù)地址獲得坐標(biāo)《--------------------------------
public void getPoint(ShopInfo shop){
try {
String sCurrentLine;
String sTotalString;
sCurrentLine = "";
sTotalString = "";
java.io.InputStream l_urlStream;
java.net.URL l_url = new java.net.URL("http://api.map.baidu.com/geocoder/v2/?address="+shop.getAddress().replaceAll(" ", "")+"&output=json&ak=702632E1add3d4953d0f105f27c294b9&callback=showLocation");
java.net.HttpURLConnection l_connection = (java.net.HttpURLConnection) l_url.openConnection();
l_connection.connect();
l_urlStream = l_connection.getInputStream();
java.io.BufferedReader l_reader = new java.io.BufferedReader(new java.io.InputStreamReader(l_urlStream));
String str=l_reader.readLine();
//用經(jīng)度分割返回的網(wǎng)頁(yè)代碼
String s=","+"/""+"lat"+"/""+":";
String strs[]=str.split(s, 2);
String s1="/""+"lng"+"/""+":";
String a[]=strs[0].split(s1, 2);
shop.setLng(a[1]);
s1="}"+","+"/"";
String a1[]=strs[1].split(s1, 2);
shop.setLat(a1[0]);
} catch (Exception e) {
e.printStackTrace();
}
}
//存入數(shù)據(jù)庫(kù)
public void inputAll(ArrayList<ShopInfo> allShops){
System.out.println("開始向服務(wù)器中寫入");
String sql2="insert into test.dc_shop (name,scope,address,phone,description,image,createtime,lat,lng) values (?,?,?,?,?,?,?,?,?)";
try {
pss=cons.prepareStatement(sql2);
System.out.println("-------------------------等待寫入數(shù)據(jù)條數(shù): "+allShops.size());
for(int i=0;i<allShops.size();i++){
pss.setString(1,allShops.get(i).getName());
pss.setString(2, allShops.get(i).getScope());
pss.setString(3, allShops.get(i).getAddress());
pss.setString(4, allShops.get(i).getPhone());
pss.setString(5, allShops.get(i).getDescription());
pss.setString(6, null);//圖片路徑
pss.setString(7, allShops.get(i).getMobile());
pss.setString(8, allShops.get(i).getLat());
pss.setString(9, allShops.get(i).getLng());
pss.executeUpdate();
}
pss.close();
cons.close();
System.out.println("--->OK");
} catch (SQLException e) {
// TODO Auto-generated catch block
System.out.println("向mysql中更新數(shù)據(jù)時(shí)發(fā)生異常！");
e.printStackTrace();
}
}
