Task: There are more than 1000 stores information (in Excel, including address, shop name, telephone, etc., but not by latitude coordinates), the boss let me use the address through Baidu Map pickup coordinate system to find the corresponding coordinates, and then add the coordinates after the update to the company's database.
The failure of the scheme: 1, the Use of Key Wizard, the key Wizard is a simulation of keyboard and mouse operation software, used to write action scripts, due to tight time, not how to study, because the whole set of action is too Complex Button Wizard tried to give up.
2, form filling tools (that is, the Exel table batch submitted to the Web page), what wind, black paint (especially this black paint, but also money, pit goods) have tried, the results are not satisfied. Because I want to submit the contents of Excel to the page and get the corresponding content from the Web page, so these software for batch registration is not used.
Solution: Finally, I---write code to solve the problem. The idea is: through the incoming address as a parameter splicing URL call Baidu map, and then parse the returned page, extract latitude and longitude coordinates.
1, modify the property name in Excel table (easy to read later with query) and then poured into the database.
/*
* Classes that manage database connections
*/
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;
Connect local MySQL parameters? The following parameter is to solve the Chinese garbled
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= "* * *";
Connection server MySQL Parameters
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 ("Load MySQL driver ...");
con = drivermanager.getconnection (content,un,up);
STA = Con.createstatement ();
SYSTEM.OUT.PRINTLN ("Connection to local database success!!") ");
Class.forName (MYSQLDRIVER1);
SYSTEM.OUT.PRINTLN ("Load MySQL driver ...");
Cons = Drivermanager.getconnection (content1,un1,up1);
Stas = Cons.createstatement ();
SYSTEM.OUT.PRINTLN ("Connect server Success!!") ");
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") + "Welcome your presence");
Si.setmobile (rs.getstring ("Keeperphone"));
Si.setscope (tablename);
Si.setphone (rs.getstring ("Shoptel"));
GetPoint (SI);
Allshops.add (SI);
System.out.println ("Longitude:" +si.getlat () + "Latitude:" +si.getlng ());
}
return allshops;
}
-------------------------key code gets the coordinates based on the address--------------------------------
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 ();
Page code returned with a longitude split
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 ();
}
}
Into the database
public void Inputall (arraylist<shopinfo> allshops) {
System.out.println ("Start writing to Server");
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 ("-------------------------waiting to write the number of data bars:" +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);//Picture path
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 ("An exception occurred while updating data to MySQL!") ");
E.printstacktrace ();
}
}
It's OK to get a main function call.
