数据库与数据表的创建
本节完成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;
}
}