创建数据库和表:
为了让项目能够有更好的结构,这里需要在com.coolweather.app包下再新建几个包
其中:
activity包用于存放所有活动相关的代码
db包用于存放所有数据库相关的代码
model包用于存放所有模型相关的代码
receiver包用于存放所有广播接收器相关的代码
service包用于存放所有服务相关的代码
util包用于存放所有工具相关的代码
ADT帮我们自动生成的MainActivity和activity_main.xml文件就不需要了,这里直接将它们删除掉。
第一阶段:创建数据库和表
建立三张表:Province City County 分别用于存放省 市 县的各种数据信息
三张表的建表语句分别如下:
Province:
create table Province (
id integer primary key autoincrement,
province_name text,
province_code text)
其中id是自增长主键,province_name表示省名,province_code表示省级代号。
City:
create table City (
id integer primary key autoincrement,
city_name text,
city_code text,
province_id integer)
其中id是自增长主键,city_name表示城市名,city_code表示市级代号,province_id是表City关联表Province的外键
County:
create table County (
id integer primary key autoincrement,
county_name text,
county_code text,
city_id integer)
其中id是自增长主键,county_name表示县名,county_code表示县级代号,city_id是表County关联表City的外键
下面将建表语句写入到代码中
在db包下新建一个CoolWeatherOpenHelper类,代码如下所示:
public class CoolWeatherOpenHelper extends SQLiteOpenHelper {
/**
* Province表建表语句
*/
public static final String CREATE_PROVINCE = "create table Province ("
+ "id integer primary key autoincrement, "
+ "province_name text, "
+ "province_code text)";
/**
* City表建表语句
*/
public static final String CREATE_CITY = "create table City ("
+ "id integer primary key autoincrement, "
+ "city_name text, "
+ "city_code text, "
+ "province_id integer)";
/**
* County表建表语句
*/
public static final String CREATE_COUNTY = "create table County ("
+ "id integer primary key autoincrement, "
+ "county_name text, "
+ "county_code text, "
+ "city_id integer)";
public CoolWeatherOpenHelper(Context context, String name, CursorFactory factory, int version) {
super(context, name, factory, version);
}
@Override
public void onCreate(SQLiteDatabase db) {
db.execSQL(CREATE_PROVINCE); // 创建Province表
db.execSQL(CREATE_CITY); // 创建City表
db.execSQL(CREATE_COUNTY); // 创建County表
}
@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
}
}
将三条建表语句定义成常量,然后在onCreate()方法中去执行创建。
另外,每张表在代码中最好能有一个对应的实体类,这样会非常方便于我们后续的开发工作
因此,在model包下新建一个Province类,代码如下所示:
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;
}
}
接着在model包下新建一个City类,代码如下所示:
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;
}
}
然后在model包下新建一个County类,代码如下所示:
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;
}
}
接下来我们创建一个CoolWeatherDB类,这个类将一些常用的数据库操作封装起来。
public class CoolWeatherDB {
/**
* 数据库名
*/
public static final String DB_NAME = "cool_weather";
/**
* 数据库版本
*/
public static final int VERSION = 1;
private static CoolWeatherDB coolWeatherDB;
private SQLiteDatabase db;
/**
* 将构造方法私有化
*/
private CoolWeatherDB(Context context) {
CoolWeatherOpenHelper dbHelper = new CoolWeatherOpenHelper(context, DB_NAME, null, VERSION);
db = dbHelper.getWritableDatabase();
}
/**
* 获取CoolWeatherDB的实例
*/
public synchronized static CoolWeatherDB getInstance(Context context) {
if (coolWeatherDB == null) {
coolWeatherDB = new CoolWeatherDB(context);
}
return coolWeatherDB;
}
/**
* 将Province实例存储到数据库
*/
public void saveProvince(Province province) {
if (province != null) {
ContentValues values = new ContentValues();
values.put("province_name", province.getProvinceName());
values.put("province_code", province.getProvinceCode());
db.insert("Province", null, values);
}
}
/**
* 从数据库读取全国所有的省份信息
*/
public List<Province> loadProvinces() {
List<Province> list = new ArrayList<Province>();
Cursor cursor = db.query("Province", null, null, null, null, null, null);
if (cursor.moveToFirst()) {
do {
Province province = new Province();
province.setId(cursor.getInt(cursor.getColumnIndex("id")));
province.setProvinceName(cursor.getString(cursor.getColumnIndex("province_name")));
province.setProvinceCode(cursor.getString(cursor.getColumnIndex("province_code")));
list.add(province);
} while (cursor.moveToNext());
}
if (cursor != null) {
cursor.close();
}
return list;
}
/**
* 将City实例存储到数据库
*/
public void saveCity(City city) {
if (city != null) {
ContentValues values = new ContentValues();
values.put("city_name", city.getCityName());
values.put("city_code", city.getCityCode());
values.put("province_id", city.getProvinceId());
db.insert("City", null, values);
}
}
/**
* 从数据库读取某省下所有的城市信息
*/
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);
if (cursor.moveToFirst()) {
do {
City city = new City();
city.setId(cursor.getInt(cursor.getColumnIndex("id")));
city.setCityName(cursor.getString(cursor.getColumnIndex("city_name")));
city.setCityCode(cursor.getString(cursor.getColumnIndex("city_code")));
list.add(city);
} while (cursor.moveToNext());
}
if (cursor != null) {
cursor.close();
}
return list;
}
/**
* 将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);
}
}
/**
* 从数据库读取某城市下所有的县信息
*/
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);
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());
}
if (cursor != null) {
cursor.close();
}
return list;
}
}
CoolWeatherDB是一个单例类,我们将它的构造函数私有化,并提供了一个getInstance()方法来获取CoolWeatherDB的实例,这样可以保证全局范围内只会有一个CoolWeatherDB实例。
最后附上GitHub:
https://github.com/LiuchangDuan/coolweather