03 Creating databases and tables and testing database Operations (unit test framework)

The framework for the overall project is as follows:

Cities and counties need three sheets: province, city, County:

The language of the table is:

Note that there are spaces.

The entity class of the new city and county under the model package is first:

Province:

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;}}

City:

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;}}

Note that the city table needs to be associated with the province table, and Provinceid is the foreign key of the city table associated Province table.

County:

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;}}

Where city_id is the foreign key of the county table associated with the city table.

At this time we can execute the statement to create the table, under the DB package, new: Coolweatheropenhelper inherited from: Sqliteopenhelper, after creation, you need to specify the constructor and OnCreate () method and the Onupgrade () method , the method of upgrading the database does not care about it here.

First look at the code of the constructor:

Public Coolweatheropenhelper (Context context, String name, cursorfactory factory,int version) {Super (context, name, Factory, version);}

Context is the name of the database, factory is the cursor factory, and version is the database.

@Overridepublic void OnCreate (Sqlitedatabase db) {db.execsql (create_province);  Create Province Table Db.execsql (create_city);  Create City Table Db.execsql (create_county);  Create County Table}

The complete code:

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 table-built table statement */public static final String Create_prov INCE = "CREATE Table Province (" + "ID integer primary key autoincrement," + "Province_name text," + "Province_code text)"  /** * City Table statement */public static final String create_city = "CREATE table City (" + "ID integer primary key autoincrement, "+" City_name text, "+" City_code text, "+" province_id integer) "/** * County Table statement */public static final String CREA Te_county = "CREATE Table County (" + "ID integer primary key autoincrement," + "County_name text," + "County_code text, "+" city_id integer) ";p ublic coolweatheropenhelper (context context, String name, cursorfactory factory,int version) {sup ER (context, name, Factory, version);} @OverridepublIC void OnCreate (Sqlitedatabase db) {db.execsql (create_province);  Create Province Table Db.execsql (create_city);  Create City Table Db.execsql (Create_county); Create County Table} @Overridepublic void Onupgrade (sqlitedatabase db, int oldversion, int newversion) {}}

Next is the way to operate the database, under the DB package, New: Coolweatherdb, this class is a singleton class, before starting the operation of the database, look at the Singleton class:

/** * Database name */public static final String db_name = "Cool_weather";/** * Database version */public static final int version = 1;private Static Coolweatherdb coolweatherdb;private sqlitedatabase db;/** * Structuring method Privatization */private Coolweatherdb (context context) {Co Olweatheropenhelper dbhelper = new Coolweatheropenhelper (context,db_name, NULL, VERSION);d B = Dbhelper.getwritabledatabase ();} /** * Gets an instance of Coolweatherdb. */public synchronized static Coolweatherdb getinstance (context context) {if (Coolweatherdb = = null) {Coolweatherdb = new C Oolweatherdb (context);} return COOLWEATHERDB;}

In the constructor, specifies the name of the database, the cursor factory is generally null, and the version of the database. And through the Coolweatheropenhelper object Getwritabledatabase get Sqlitedatabase, the next database insert and so on, all need to sqlitedatabase object to complete. In the code that gets the instance of Coolweatherdb, using synchronized, he guarantees that two threads will not enter this method at the same time, this method becomes the synchronous method, solves the problem of multithreading (this also has the problem).

The following actions are targeted at the provinces and counties:

1. Store provincial and county instances in the database;

2. Read the information of provinces and counties from the database;

/** * Store the province instance to the database. */public void Saveprovince (province province) {if (province! = null) {contentvalues values = new Contentvalues (); Values.pu T ("Province_name", Province.getprovincename ()), Values.put ("Province_code", Province.getprovincecode ());d B.insert ("province", null, Values);}} /** * Read all provincial information from the database. */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 ();p Rovince.setid (Cursor.getint (Cursor.getcolumnindex ("id")));p Rovince.setprovincename ( Cursor.getstring (Cursor.getcolumnindex ("Province_name")));p Rovince.setprovincecode (cursor.getstring ( Cursor.getcolumnindex ("Province_code")); List.add (province);} while (Cursor.movetonext ());} return list;} /** * Store the city instance in the database. */public void Savecity {if (city! = null) {contentvalues values = new Contentvalues (); Values.put ("City_name", C Ity.getcitynAme ()), Values.put ("City_code", City.getcitycode ()), Values.put ("province_id", City.getprovinceid ());d B.insert (" City ", null, Values);}} /** * Read some of the saved city information from the database. */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 = 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;} /** * Store the county instance to the database. */public void Savecounty (county county) {if (county! = null) {contentvalues values = new Contentvalues (); Values.put ("Count Y_name ", County.getcountyname ()), Values.put (" County_code ", County.getcountycode ()), Values.put (" city_id ", County.getcityid ());d B.insERT ("County", null, Values);}} /** * Read all county information from a database in a city. */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 (curs Or.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;}

The complete code:

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 {/** * database name */public Static final String db_name = "Cool_weather";/** * Database version */public static final int version = 1;private static Coolweatherd B coolweatherdb;private sqlitedatabase db;/** * Structuring method Privatization */private Coolweatherdb (context context) { Coolweatheropenhelper dbhelper = new Coolweatheropenhelper (context,db_name, NULL, VERSION);d B = Dbhelper.getwritabledatabase ();} /** * Gets an instance of Coolweatherdb. */public synchronized static Coolweatherdb getinstance (context context) {if (Coolweatherdb = = null) {Coolweatherdb = new C Oolweatherdb (context);} return COOLWEATHERDB;} /** * Store the province instance to the database. */public void Saveprovince (PRovince province) {if (province! = null) {contentvalues values = new Contentvalues (); Values.put ("Province_name", Province . Getprovincename ()); Values.put ("Province_code", Province.getprovincecode ());d B.insert ("province", null, Values);}} /** * Read all provincial information from the database. */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 ();p Rovince.setid (Cursor.getint (Cursor.getcolumnindex ("id")));p Rovince.setprovincename ( Cursor.getstring (Cursor.getcolumnindex ("Province_name")));p Rovince.setprovincecode (cursor.getstring ( Cursor.getcolumnindex ("Province_code")); List.add (province);} while (Cursor.movetonext ());} return list;} /** * Store the city instance in the database. */public void Savecity {if (city! = null) {contentvalues values = new Contentvalues (); Values.put ("City_name", C Ity.getcityname ()); Values.put ("City_code", City.getcitycode ()); VALues.put ("province_id", City.getprovinceid ());d B.insert ("City", null, Values);}} /** * Read some of the saved city information from the database. */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 = 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;} /** * Store the county instance to the database. */public void Savecounty (county county) {if (county! = null) {contentvalues values = new Contentvalues (); Values.put ("Count Y_name ", County.getcountyname ()), Values.put (" County_code ", County.getcountycode ()), Values.put (" city_id ", County.getcityid ());d B.insert ("county", null, Values);}} /** * Read all county information from a database in a city. */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 (curs Or.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;}}

The introduction of the test framework, create a new test package, under which a new class Testdbcase inherits from: Androidtestcase. What to introduce in Androidmnifest.xml:

Targetpackage the package name for itself.

First Test whether the database can be created.

public class Testdbcase extends Androidtestcase {/** * database name */public static final String db_name = "Cool_weather";/** * Data Library version */public static final int version = 1;/** * Test CREATE DATABASE *  */public void TestDB () {Coolweatheropenhelper helper = new Co Olweatheropenhelper (GetContext (), db_name, NULL, VERSION); Helper.getwritabledatabase ();}}

data/data/Package name/database to export the database, using SQLite expert open:

OK, create a database to get it done.

To test the Saveprovince method:

public void Saveprovince () {Coolweatherdb db = Coolweatherdb.getinstance (GetContext ()); Province province = new province ();p rovince.setprovincename ("Anhui");p rovince.setprovincename ("Sichuan"); Province.setprovincecode ("a");p Rovince.setprovincecode ("" ");d b.saveprovince (province);

To export a database:

To test the Loadprovinces method:

public void Loadprovinces () {Coolweatherdb db = Coolweatherdb.getinstance (GetContext ()); list<province> provinces = db.loadprovinces (); for (province province:provinces) {System.out.println ( Province.tostring ());}}

See the printed results:

Similar to other municipal and county-level.

This section of the database is complete. Submit the code to GitHub.

03 Creating databases and tables and testing database Operations (unit test framework)