欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  数据库

天气网城市代码(MYSQL)获取实现

程序员文章站 2022-06-04 14:48:55
...

MYSQL数据库存储城市代码实现 在这篇http://blog.csdn.net/huoer_12/article/details/17399211文章中,说的是城市代码的MYSQL数据库方式的存储实现,这篇具体讲述实现方法。 实现类CityDB.java /** * @2013-12-18 * @author eabour * @file CityDB.java * Al

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

在这篇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 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: 根据参数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("西安")); }

这个是测试的主方法,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的研究不多,就不在讲述。

如有不当之处,还请指正!