天气网城市代码（MYSQL）获取实现

╰半夏微凉° 2022-09-19 05:27 266阅读 0赞

# MYSQL数据库存储城市代码实现 #

在这篇[http://blog.csdn.net/huoer\_12/article/details/17399211][http_blog.csdn.net_huoer_12_article_details_17399211]文章中，说的是城市代码的MYSQL数据库方式的存储实现，这篇具体讲述实现方法。

实现类CityDB.java

/**
     * @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<String,String> 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<String,String> 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<String,String> distract=cityutil.getCityZone(city_key);
    					for(String dis_key : distract.keySet()){
    						System.out.println("      " + distract.get(dis_key));
    						//get concrete information
    						Map<String,String> 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: 根据参数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<String> list=new ArrayList<String>();
    			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("西安")); }

这个是测试的主方法，ConnectionPool是个数据库连接池，pool.localinit()是对数据库的本地初始化，重要作用是初始化本地数据库的连接等，并建立池，好让用到数据库连接的语句使用，也可以自己创建Connection连接，都可以。

**今天在编写该类时，遇见了一些异常，主要还是对Mysql数据库的不了解造成的，就是出现了在插入汉字的时候，出现异常为 ‘海南’ not in feild list ，当时不知道原因，以为是字段问题，但是改完字段名称后还是这样的。左后就直接在数据库中操作，而不是在java程序里，结果还是同样的错误。然后我就重新用navicat for mysql进行建表，相同的字段，结果还是不能存汉字，但是原先的一个表能存汉字，现在的错误为 “incorrect string value”，进过查阅资料得知，是Mysql字符编码的问题，要修改为utf8，最后在新建表时添加如下语句：ENGINE=InnoDB DEFAULT CHARSET=utf8 。**

**还有就是mysql中的 \`（是在TAB键上面和1键前面的那个有波浪的键），就是加在表名和字段上面的，字符串的是'(单引号，不是 \` )。鉴于对MYSQL的研究不多，就不在讲述。**

**如有不当之处，还请指正！**

[http_blog.csdn.net_huoer_12_article_details_17399211]: http://blog.csdn.net/huoer_12/article/details/17399211