03创建数据库和表以及测试数据库的操作(单元测试框架)
整体项目的框架如下:
省市县需要三张表:省,市,县:
建表的语言为:
注意有空格。
先在model包下新建省市县的实体类:
省:
package com.dy.ustc.weatherpro.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; } }市:
package com.dy.ustc.weatherpro.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; } }
注意City表需要关联Province表,provinceId就是City表关联Province表的外键。
县:
package com.dy.ustc.weatherpro.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; } }
其中city_id是County表关联City表的外键。
这时候我们可以执行创建表的语句了,在db包下,新建:CoolWeatherOpenHelper继承自:SQLiteOpenHelper,创建好了之后,需要指定构造器和onCreate()方法以及onUpgrade()方法,升级数据库的方法这里不用管它。
先看构造器的代码:
public CoolWeatherOpenHelper(Context context, String name, CursorFactory factory, int version) { super(context, name, factory, version); }
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表 }
完整的代码:
package com.dy.ustc.weatherpro.db; import android.content.Context; import android.database.sqlite.SQLiteDatabase; import android.database.sqlite.SQLiteDatabase.CursorFactory; import android.database.sqlite.SQLiteOpenHelper; 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) { } }
接下来就是操作数据库的方法了,在db包下,新建: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; }
在构造器中,指定了数据库的名称,游标工厂一般为null,数据库的版本。并通过CoolWeatherOpenHelper对象的getWritableDatabase得到SQLiteDatabase,接下来的数据库的insert等等操作,均需要SQLiteDatabase对象来完成。在获取CoolWeatherDB的实例的代码中,使用了synchronized,他保证了两个线程不会同时进入这个方法,这个方法变成了同步方法,解决了多线程的问题(这个也有问题的)。
接下来的操作针对省市县:
1.将省市县实例存储到数据库;
2.从数据库读取省市县的信息;
/** * 将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()); } 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"))); city.setProvinceId(provinceId); list.add(city); } while (cursor.moveToNext()); } 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()); } return list; }
完整的代码:
package com.dy.ustc.weatherpro.db; import java.util.ArrayList; import java.util.List; import com.dy.ustc.weatherpro.model.City; import com.dy.ustc.weatherpro.model.County; import com.dy.ustc.weatherpro.model.Province; import android.content.ContentValues; import android.content.Context; import android.database.Cursor; import android.database.sqlite.SQLiteDatabase; 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()); } 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"))); city.setProvinceId(provinceId); list.add(city); } while (cursor.moveToNext()); } 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()); } return list; } }
测试框架的引入,新建一个test包,在该包下新建一个类TestDBCase继承自:AndroidTestCase。在AndroidMnifest.xml中引入一下的内容:
targetPackage为自己的包名。
首先测试数据库是否能够创建。
public class TestDBCase extends AndroidTestCase { /** * 数据库名 */ public static final String DB_NAME = "cool_weather"; /** * 数据库版本 */ public static final int VERSION = 1; /** * 测试创建数据库 * */ public void testDB(){ CoolWeatherOpenHelper helper = new CoolWeatherOpenHelper(getContext(), DB_NAME, null, VERSION); helper.getWritableDatabase(); } }
data/data/包名/database下导出数据库,使用SQLite expert打开:
ok,创建数据库搞定。
测试saveProvince方法:
public void saveProvince(){ CoolWeatherDB db = CoolWeatherDB.getInstance(getContext()); Province province = new Province(); province.setProvinceName("安徽"); province.setProvinceName("四川"); province.setProvinceCode("22"); province.setProvinceCode("27"); db.saveProvince(province); }
导出数据库:
测试loadProvinces方法:
public void loadProvinces(){ CoolWeatherDB db = CoolWeatherDB.getInstance(getContext()); List<Province> provinces = db.loadProvinces(); for (Province province : provinces) { System.out.println(province.toString()); } }
看到打印的结果:
其他的市级和县级的类似。
本节数据库到此完毕。将代码提交到github上。
郑重声明:本站内容如果来自互联网及其他传播媒体,其版权均属原媒体及文章作者所有。转载目的在于传递更多信息及用于网络分享,并不代表本站赞同其观点和对其真实性负责,也不构成任何其他建议。