简单的天气预报 (二)

数据库与数据表的创建

本节完成3个任务

mission1：创建表

mission2：创建表对应的实体类

mission3：创建一个操作表的类，这个类有2大类功能，一个是把省市县的数据存储到数据库中，一个是从数据库中读取省市县的数据。

接下来，让我们一一去完成。

先来搞定mission1。

先来分析一下我们即将创建的表，我们创建的表有3个，分别是省市县。其中省表中有省的名字，省的代号；市表中有市的名字，市的代号，所属于哪个省；县表中有县的名字，县的代号，所属于哪个市，另外每个表中都有一个id，是主键且是自增长的。

如图：

创建省表的语句

CREATE TABLE Province(id integer primary key autoincrement,province_name text,province_code text);

创建市表的语句

CREATE TABLE City(id integer primary key autoincrement,city_name text,city_code text,province_id integer text);

创建县表的语句

CREATE TABLE County(id integer primary key autoincrement,county_name text,county_code text,city_id integer text);

然后我们去把建表语句写入到代码中。创建WeatherOpenHelper类。

package org.guya.myweather.db;

import android.content.Context;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;

public class WeatherOpenHelper extends SQLiteOpenHelper {
	private Context mContext;
	/**
	 * 创建省表的sql语句
	 */
	private static final String CREATE_PROVINCE = "CREATE TABLE Province("
			+ "id integer primary key autoincrement," + "province_name text,"
			+ "province_code text)";
	/**
	 * 创建省市的sql语句
	 */
	private static final String CREATE_CITY = "CREATE TABLE City("
			+ "id integer primary key autoincrement," + "city_name text,"
			+ "city_code text," + "province_id integer text)";
	/**
	 * 创建县表的sql语句
	 */
	private static final String CREATE_COUNTY = "CREATE TABLE County("
			+ "id integer primary key autoincrement," + "county_name text,"
			+ "county_code text," + "city_id integer text)";

	/**
	 * 构造方法，初始化数据库的一些数据
	 * @param context 上下文 
	 * cool_weather.db 数据库的名字
	 * null 默认工厂类
	 * 1 最初的版本号
	 */
	public WeatherOpenHelper(Context context) {
		super(context, "cool_weather.db", null, 1);
		this.mContext = context;
	}

	@Override
	public void onCreate(SQLiteDatabase db) {
		//创建省
		db.execSQL(CREATE_PROVINCE);
		//创建市
		db.execSQL(CREATE_CITY);
		//创建县
		db.execSQL(CREATE_COUNTY);

	}

	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {

	}

}

然后我们去新建一个测试工程去看看到底能不能创建表。

新建一个类 WeatherOpenHelperTest，让它继承 AndroidTestCase。

代码如下

package org.guya.myweather.test;

import org.guya.myweather.db.WeatherOpenHelper;

import android.database.sqlite.SQLiteDatabase;
import android.test.AndroidTestCase;

public class WeatherOpenHelperTest extends AndroidTestCase {
	

	public void TestCreate() {
		WeatherOpenHelper helper = new WeatherOpenHelper(this.mContext);
		SQLiteDatabase db = helper.getWritableDatabase();

	}
}

同时去清单文件里去增加一些设置。

    <instrumentation
        android:name="android.test.InstrumentationTestRunner"
        android:targetPackage="org.guya.myweather" />

  <uses-library android:name="android.test.runner" />

然后运行测试。

和我们预测的一样，说明数据库创建完成。

接下来我们去完成mission2。

创建Province实体类

package org.guya.myweather.model;

public class Province {
	private int id;
	private String provinceName;
	private String provinceCode;

	public int getId() {
		return id;
	}

	public void setId(int id) {
		this.id = id;
	}

	public String getProvinceName() {
		return provinceName;
	}

	public void setProvinceName(String provinceName) {
		this.provinceName = provinceName;
	}

	public String getProvinceCode() {
		return provinceCode;
	}

	public void setProvinceCode(String provinceCode) {
		this.provinceCode = provinceCode;
	}

}

创建City实体类

package org.guya.myweather.model;

public class City {
	private int id;
	private String cityName;
	private String cityCode;
	private int provinceId;

	public int getId() {
		return id;
	}

	public void setId(int id) {
		this.id = id;
	}

	public String getCityName() {
		return cityName;
	}

	public void setCityName(String cityName) {
		this.cityName = cityName;
	}

	public String getCityCode() {
		return cityCode;
	}

	public void setCityCode(String cityCode) {
		this.cityCode = cityCode;
	}

	public int getProvinceId() {
		return provinceId;
	}

	public void setProvinceId(int provinceId) {
		this.provinceId = provinceId;
	}

}

创建County实体类

package org.guya.myweather.model;

public class County {
	private int id;
	private String countyName;
	private String countyCode;
	private int cityId;

	public int getId() {
		return id;
	}

	public void setId(int id) {
		this.id = id;
	}

	public String getCountyName() {
		return countyName;
	}

	public void setCountyName(String countyName) {
		this.countyName = countyName;
	}

	public String getCountyCode() {
		return countyCode;
	}

	public void setCountyCode(String countyCode) {
		this.countyCode = countyCode;
	}

	public int getCityId() {
		return cityId;
	}

	public void setCityId(int cityId) {
		this.cityId = cityId;
	}

}

下面来完成mission3。

mission3比较简单，但是代码比较长，我们一一分解。

首先是使用单例模式获取WeatherDB实例

那么我们就要使其构造方法私有化，然后再重写一个方法去获取实例。

private static WeatherDB weatherDB;
	private SQLiteDatabase db;

	/**
	 * 单例模式，构造方法私有化
	 * 
	 * @param context上下文
	 */
	private WeatherDB(Context context) {
		WeatherOpenHelper helper = new WeatherOpenHelper(context);
		db = helper.getWritableDatabase();
	}

	/**
	 * 获取WeatherDB实例
	 * 
	 * @param context
	 * @return
	 */
	public static WeatherDB getInstance(Context context) {
		if (weatherDB != null) {
			weatherDB = new WeatherDB(context);
		}
		return weatherDB;
	}

然后去写把省市县数据存储到数据库中的方法。

        /**
	 * 把Province实例存在到数据库里
	 * 
	 * @param p
	 */
	public void saveProvince(Province p) {
		if (p != null) {
			ContentValues values = new ContentValues();
			values.put("province_name", p.getProvinceName());
			values.put("province_code", p.getProvinceCode());
			db.insert("Province", null, values);
		}
	}

	/**
	 * 把City实例存在到数据库里
	 * 
	 * @param c
	 */
	public void saveCity(City c) {
		if (c != null) {
			ContentValues values = new ContentValues();
			values.put("city_name", c.getCityName());
			values.put("city_code", c.getCityCode());
			values.put("province_id", c.getProvinceId());
			db.insert("City", null, values);
		}
	}

	/**
	 * 把County实例存在到数据库里
	 * 
	 * @param county
	 */
	public void saveCounty(County county) {
		if (county != null) {
			ContentValues values = new ContentValues();
			values.put("county_name", county.getCountyName());
			values.put("county_code", county.getCountyCode());
			values.put("city_id", county.getCityId());
			db.insert("County", null, values);
		}
	}

再写出从数据库中读取省市县数据的方法。

        /**
	 * 从数据库中读取所有省份信息
	 * 
	 * @return
	 */
	public List<Province> loadProvinces() {

		List<Province> list = new ArrayList<Province>();

		Cursor cursor = db.query("Province", null, null, null, null, null,
				null, null);
		if (cursor.moveToFirst()) {
			do {
				Province p = new Province();
				p.setId(cursor.getInt(cursor.getColumnIndex("id")));
				p.setProvinceName(cursor.getString(cursor
						.getColumnIndex("province_name")));
				p.setProvinceCode(cursor.getString(cursor
						.getColumnIndex("province_code")));
				list.add(p);
			} while (cursor.moveToNext());
		}
		return list;

	}

	/**
	 * 从数据库中读取某省下所有城市信息
	 * 
	 * @return
	 */
	public List<City> loadCities(int provinceId) {

		List<City> list = new ArrayList<City>();

		Cursor cursor = db.query("City", null, "province_id=?",
				new String[] { String.valueOf(provinceId) }, null, null, null,
				null);
		if (cursor.moveToFirst()) {
			do {
				City c = new City();
				c.setId(cursor.getInt(cursor.getColumnIndex("id")));
				c.setCityName(cursor.getString(cursor
						.getColumnIndex("city_name")));
				c.setCityCode(cursor.getString(cursor
						.getColumnIndex("city_code")));
				c.setProvinceId(provinceId);
				list.add(c);
			} while (cursor.moveToNext());
		}
		return list;

	}

	/**
	 * 从数据库中读取某城市下面所有县信息
	 * 
	 * @return
	 */
	public List<County> loadCounties(int cityId) {

		List<County> list = new ArrayList<County>();

		Cursor cursor = db
				.query("County", null, "city_id=?",
						new String[] { String.valueOf(cityId) }, null, null,
						null, null);
		if (cursor.moveToFirst()) {
			do {
				County county = new County();
				county.setId(cursor.getInt(cursor.getColumnIndex("id")));
				county.setCountyName(cursor.getString(cursor
						.getColumnIndex("county_name")));
				county.setCountyCode(cursor.getString(cursor
						.getColumnIndex("county_code")));
				county.setCityId(cityId);
				list.add(county);
			} while (cursor.moveToNext());
		}
		return list;

	}

这样，我们的操作表的类也完成了。下面贴出完成代码。

package org.guya.myweather.db;

import java.util.ArrayList;
import java.util.List;

import org.guya.myweather.model.City;
import org.guya.myweather.model.County;
import org.guya.myweather.model.Province;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;

public class WeatherDB {
	private static WeatherDB weatherDB;
	private SQLiteDatabase db;

	/**
	 * 单例模式，构造方法私有化
	 * 
	 * @param context上下文
	 */
	private WeatherDB(Context context) {
		WeatherOpenHelper helper = new WeatherOpenHelper(context);
		db = helper.getWritableDatabase();
	}

	/**
	 * 获取WeatherDB实例
	 * 
	 * @param context
	 * @return
	 */
	public static WeatherDB getInstance(Context context) {
		if (weatherDB != null) {
			weatherDB = new WeatherDB(context);
		}
		return weatherDB;
	}

	/**
	 * 把Province实例存在到数据库里
	 * 
	 * @param p
	 */
	public void saveProvince(Province p) {
		if (p != null) {
			ContentValues values = new ContentValues();
			values.put("province_name", p.getProvinceName());
			values.put("province_code", p.getProvinceCode());
			db.insert("Province", null, values);
		}
	}

	/**
	 * 把City实例存在到数据库里
	 * 
	 * @param c
	 */
	public void saveCity(City c) {
		if (c != null) {
			ContentValues values = new ContentValues();
			values.put("city_name", c.getCityName());
			values.put("city_code", c.getCityCode());
			values.put("province_id", c.getProvinceId());
			db.insert("City", null, values);
		}
	}

	/**
	 * 把County实例存在到数据库里
	 * 
	 * @param county
	 */
	public void saveCounty(County county) {
		if (county != null) {
			ContentValues values = new ContentValues();
			values.put("county_name", county.getCountyName());
			values.put("county_code", county.getCountyCode());
			values.put("city_id", county.getCityId());
			db.insert("County", null, values);
		}
	}

	/**
	 * 从数据库中读取所有省份信息
	 * 
	 * @return
	 */
	public List<Province> loadProvinces() {

		List<Province> list = new ArrayList<Province>();

		Cursor cursor = db.query("Province", null, null, null, null, null,
				null, null);
		if (cursor.moveToFirst()) {
			do {
				Province p = new Province();
				p.setId(cursor.getInt(cursor.getColumnIndex("id")));
				p.setProvinceName(cursor.getString(cursor
						.getColumnIndex("province_name")));
				p.setProvinceCode(cursor.getString(cursor
						.getColumnIndex("province_code")));
				list.add(p);
			} while (cursor.moveToNext());
		}
		return list;

	}

	/**
	 * 从数据库中读取某省下所有城市信息
	 * 
	 * @return
	 */
	public List<City> loadCities(int provinceId) {

		List<City> list = new ArrayList<City>();

		Cursor cursor = db.query("City", null, "province_id=?",
				new String[] { String.valueOf(provinceId) }, null, null, null,
				null);
		if (cursor.moveToFirst()) {
			do {
				City c = new City();
				c.setId(cursor.getInt(cursor.getColumnIndex("id")));
				c.setCityName(cursor.getString(cursor
						.getColumnIndex("city_name")));
				c.setCityCode(cursor.getString(cursor
						.getColumnIndex("city_code")));
				c.setProvinceId(provinceId);
				list.add(c);
			} while (cursor.moveToNext());
		}
		return list;

	}

	/**
	 * 从数据库中读取某城市下面所有县信息
	 * 
	 * @return
	 */
	public List<County> loadCounties(int cityId) {

		List<County> list = new ArrayList<County>();

		Cursor cursor = db
				.query("County", null, "city_id=?",
						new String[] { String.valueOf(cityId) }, null, null,
						null, null);
		if (cursor.moveToFirst()) {
			do {
				County county = new County();
				county.setId(cursor.getInt(cursor.getColumnIndex("id")));
				county.setCountyName(cursor.getString(cursor
						.getColumnIndex("county_name")));
				county.setCountyCode(cursor.getString(cursor
						.getColumnIndex("county_code")));
				county.setCityId(cityId);
				list.add(county);
			} while (cursor.moveToNext());
		}
		return list;

	}
}