天气网城市代码(MYSQL)获取实现
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 Mappro=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的研究不多,就不在讲述。
如有不当之处,还请指正!
上一篇: php获取当前页面完整url地址的函数