天氣網(wǎng)城市代碼（MYSQL）獲取實現(xiàn)

/**
 * @2013-12-18
 * @author eabour
 * @file CityDB.java
 * All Rights Reserved!
 */
package com.eabour.weather;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

import com.eabour.jdbc.ConnectionPool;
import com.eabour.tool.LogUtil;

/**
 * @author eabour
 * @create 2013-12-18 下午2:44:59
 */
public class CityDB {

	private ConnectionPool pool;
	private final String PROV_TABLE="province";
	private final String CITY_TABLE="city";
	private final String DIST_TABLE="distract";

	public CityDB(){
	this.pool=ConnectionPool.getInstance();
	}

	/**
	 * 
	 * @author eabour
	 * @throws SQLException 
	 * @create 2013-12-18 下午2:50:15
	 *
	 * @function: 
	 */
	public void getCitCode() throws SQLException{
	Connection con=pool.getConnection();
	try{
	Statement stmt=con.createStatement();
	/*
	 * DROP TABLE IF EXISTS `province`;
	 * DROP TABLE IF EXISTS `city`;
	 * DROP TABLE IF EXISTS `distract`;
	 * 
	 */
	/*
	ResultSet tables=stmt.executeQuery("show tables");
	boolean hasProv = false,hasCity = false,hasDist = false;
	while(tables.next()){
	String table=tables.getString(1);
	if(PROV_TABLE.equals(table)) hasProv=true;
	if(CITY_TABLE.equals(table)) hasCity=true;
	if(DIST_TABLE.equals(table)) hasDist=true;
	}
	*/
	con.setAutoCommit(false);
	
	stmt.addBatch("DROP TABLE IF EXISTS " + DIST_TABLE);
	stmt.addBatch("DROP TABLE IF EXISTS " + CITY_TABLE);
	stmt.addBatch("DROP TABLE IF EXISTS " + PROV_TABLE);

	stmt.executeBatch();
	
	//con.setAutoCommit(true);
	stmt.addBatch("CREATE TABLE " + PROV_TABLE + " " +
	"(id varchar(10),name varchar(20),primary key(id)) ENGINE=InnoDB DEFAULT CHARSET=utf8");
	stmt.addBatch("CREATE TABLE " + CITY_TABLE + " " +
	"(id varchar(10),name varchar(20),pro_id varchar(10),primary key(id)," +
	"foreign key(pro_id) references " + PROV_TABLE + "(id)) ENGINE=InnoDB DEFAULT CHARSET=utf8");
	stmt.addBatch("CREATE TABLE " + DIST_TABLE + " " +
	"(id varchar(10),name varchar(20),code varchar(15),city_id varchar(10)," +
	"primary key(id),foreign key(city_id) references " + CITY_TABLE + "(id)) ENGINE=InnoDB DEFAULT CHARSET=utf8");

	stmt.executeBatch();
	con.commit();
	
	CityCode cityutil=new CityCode();
	//get Province list
	Map pro=cityutil.getProvinceList();
	for(String key : pro.keySet()){
	System.out.println(key + " : " + pro.get(key));
	stmt.addBatch("INSERT INTO "+ PROV_TABLE + "(id,name) values(\'"+ key + "\',\'" + pro.get(key) + "\')");
	}
	stmt.executeBatch();
	con.commit();
	for(String key : pro.keySet()){
	System.out.println(pro.get(key));
	//get city list
	Map city=cityutil.getCityZone(key);
	for(String city_key : city.keySet()){
	System.out.println(" " + city.get(city_key));
	stmt.addBatch("INSERT INTO "+ CITY_TABLE + " values(\'"+ city_key + "\',\'" + city.get(city_key) + "\',\'" + key + "\')");
	//get distract list
	Map distract=cityutil.getCityZone(city_key);
	for(String dis_key : distract.keySet()){
	System.out.println(" " + distract.get(dis_key));
	//get concrete information
	Map concrete=cityutil.getCityZone(dis_key);
	stmt.addBatch("INSERT INTO "+ DIST_TABLE + " values(\'"+ dis_key + "\',\'" + distract.get(dis_key) + "\',\'" + concrete.get(dis_key) + "\',\'" + city_key + "\')");
	}
	}
	stmt.executeBatch();
	con.commit();
	}
	}finally{
	con.setAutoCommit(true);
	pool.release(con);
	}
	}
	
	/**
	 * @param city
	 * @return
	 * @author eabour
	 * @create 2013-12-18 下午5:31:16
	 * @charset UTF-8
	 * 
	 * @function: 根據(jù)參數(shù)city來查詢該城市的代碼
	 */
	public String getCode(String city){
	Connection con=pool.getConnection();
	try{
	if(city == null || !city.matches("[\\u4E00-\\u9FA5]+")) return "不合法";
	con.setAutoCommit(true);
	
	PreparedStatement ps=con.prepareStatement("select code from "+DIST_TABLE+" where name = ?");
	ps.setString(1, city);
	ResultSet rs=ps.executeQuery();
	if(rs.next()){
	return rs.getString(1);
	}
	
	ps=con.prepareStatement("select id from "+ PROV_TABLE +" where name = ?");
	ps.setString(1, city);
	ResultSet rs1=ps.executeQuery();
	List list=new ArrayList();
	if(rs1.next()){
	String id=rs1.getString(1);
	ps=con.prepareStatement("select name from "+ CITY_TABLE +" where pro_id = ?");
	ps.setString(1, id);
	ResultSet rs2=ps.executeQuery();
	while(rs2.next()){
	list.add(rs2.getString(1));
	}
	return list.toString();
	}
	
	} catch (SQLException e) {
	// TODO Auto-generated catch block
	e.printStackTrace();
	LogUtil.logWarn(this.getClass(), "getCity", e.toString());
	}finally{
	pool.release(con);
	}
	return "Unable to search the city's name of " + city;
	}
}

public static void main(String[] args) throws SQLException {
	// TODO Auto-generated method stub
	ConnectionPool pool=ConnectionPool.getInstance();
	pool.localInit();
	System.out.println(new CityDB().getCode("西安")); }