手頭有 1000 多條門店數據（Excel 存儲，含地址、店名、電話），缺少經緯度坐標。需調用百度地圖地理編碼服務，根據地址自動獲取坐標，並寫入數據庫。
lat（緯度）、lng（經度）。public class ShopInfo {
private String name; // 店名
private String scope; // 範圍/分類
private String address; // 地址
private String mobile; // 手機
private String phone; // 座機
private String description; // 描述
private String lat; // 緯度
private String lng; // 經度
// 省略構造器、Getter/Setter
}public class DbManager {
// 本地庫配置
private static final String LOCAL_URL = "jdbc:mysql://127.0.0.1:3306/test?useUnicode=true&characterEncoding=utf8";
private static final String LOCAL_USER = "root";
private static final String LOCAL_PWD = "123456";
// 線上庫配置
private static final String REMOTE_URL = "jdbc:mysql://your-host:3306/test?useUnicode=true&characterEncoding=utf8";
private static final String REMOTE_USER = "user";
private static final String REMOTE_PWD = "pass";
private Connection localConn, remoteConn;
public DbManager() {
try {
Class.forName("com.mysql.jdbc.Driver");
localConn = DriverManager.getConnection(LOCAL_URL, LOCAL_USER, LOCAL_PWD);
remoteConn = DriverManager.getConnection(REMOTE_URL, REMOTE_USER, REMOTE_PWD);
System.out.println("雙庫連接成功");
} catch (Exception e) {
e.printStackTrace();
}
}
// 讀取本地數據並獲取坐標
public List<ShopInfo> fetchAndGeoCode(String tableName) throws SQLException {
List<ShopInfo> list = new ArrayList<>();
String sql = "SELECT * FROM " + tableName;
try (Statement st = localConn.createStatement();
ResultSet rs = st.executeQuery(sql)) {
while (rs.next()) {
ShopInfo shop = new ShopInfo();
shop.setAddress(rs.getString("address"));
shop.setDescription(rs.getString("names") + "歡迎您的惠臨");
shop.setMobile(rs.getString("keeperphone"));
shop.setScope(tableName);
shop.setPhone(rs.getString("shoptel"));
// 關鍵：調用百度 API 獲取坐標
getPoint(shop);
list.add(shop);
System.out.println("經度:" + shop.getLng() + " 緯度:" + shop.getLat());
}
}
return list;
}
// 核心：根據地址調用百度地圖 API 解析經緯度
private void getPoint(ShopInfo shop) {
try {
// 注意：AK 需在百度開放平台申請，此處為示例
String urlStr = "https://api.map.baidu.com/geocoder/v2/?address="
+ URLEncoder.encode(shop.getAddress().replaceAll("\\s+", ""), "UTF-8")
+ "&output=json&ak=YOUR_AK_HERE&callback=showLocation";
URL url = new URL(urlStr);
HttpURLConnection conn = (HttpURLConnection) url.openConnection();
conn.setConnectTimeout(5000);
conn.setReadTimeout(5000);
conn.connect();
try (BufferedReader reader = new BufferedReader(
new InputStreamReader(conn.getInputStream(), "UTF-8"))) {
String line = reader.readLine(); // 整行 JSONP
// 簡易解析 JSONP 格式：showLocation({...})
if (line != null && line.contains("lat")) {
// 使用正則或 JSON 庫解析更穩健，此處演示簡易切分
String json = line.substring(line.indexOf('{'), line.lastIndexOf('}') + 1);
// 推薦使用 Jackson/Gson/Fastjson 解析
// 此處略過完整解析邏輯，僅示意
// shop.setLat(...); shop.setLng(...);
}
}
} catch (Exception e) {
e.printStackTrace();
}
}
// 批量寫入線上庫
public void batchInsert(List<ShopInfo> shops) {
String sql = "INSERT INTO dc_shop (name,scope,address,phone,description,image,createtime,lat,lng) VALUES (?,?,?,?,?,?,?,?,?)";
try (PreparedStatement ps = remoteConn.prepareStatement(sql)) {
System.out.println("準備寫入 " + shops.size() + " 條數據");
for (ShopInfo s : shops) {
ps.setString(1, s.getName());
ps.setString(2, s.getScope());
ps.setString(3, s.getAddress());
ps.setString(4, s.getPhone());
ps.setString(5, s.getDescription());
ps.setString(6, null); // 圖片路徑
ps.setString(7, s.getMobile()); // 借用 mobile 存創建時間或實際時間
ps.setString(8, s.getLat());
ps.setString(9, s.getLng());
ps.addBatch();
}
ps.executeBatch();
System.out.println("寫入完成");
} catch (SQLException e) {
e.printStackTrace();
}
}
public static void main(String[] args) throws Exception {
DbManager mgr = new DbManager();
List<ShopInfo> data = mgr.fetchAndGeoCode("shop_temp_table");
mgr.batchInsert(data);
}
}URLEncoder.encode，避免中文亂碼導致解析失敗。output=json 獲取純 JSON，或用正則剝離回調函數後再用 JSON 庫解析。Thread.sleep 或使用令牌桶限流。通過 Java 直連百度地圖 Geocoder API，配合 JDBC 批量讀寫數據庫，可高效完成萬級地址坐標化任務。核心在於：規範請求構造、健壯 JSON 解析、合理限流重試、坐標系統確認。此方案同樣適用於高德、騰訊等地圖服務商，只需調整請求參數與響應解析邏輯。
