Get the names of provincial cities and counties by database

Reprint please indicate the source, thank http://blog.csdn.net/harryweasley/article/details/46557807

The main purpose of this blog is to describe how to save the database files to the phone system files, and can display the names of the provinces and cities.

There are three tables in the database, they are (I will upload the entire file and the database file later)

Save table

CREATE TABLE IF not EXISTS ' fs_province ' (  ' Provinceid ' bigint (a) not NULL,  ' provincename ' varchar () DEFAULT NU LL,  ' DateCreated ' datetime default NULL,  ' dateupdated ' datetime default NULL,  PRIMARY KEY (' Provinceid ')) Engine=myisam DEFAULT Charset=utf8;

City table

CREATE TABLE IF not EXISTS ' fs_city ' (  ' Cityid ' bigint (a) NOT null,  ' cityname ' varchar () DEFAULT NULL,  ' Zip Code ' varchar ' default null,  ' Provinceid ' bigint (() default null,  ' DateCreated ' datetime default NULL,  ' dateupdated ' datetime default NULL,  PRIMARY KEY (' Cityid ')) Engine=myisam DEFAULT Charset=utf8;

County-area Table

CREATE TABLE IF not EXISTS ' fs_district ' (  ' Districtid ' bigint (a) not NULL,  ' districtname ' varchar () DEFAULT NU LL,  ' Cityid ' bigint () default null,  ' DateCreated ' datetime default NULL,  ' dateupdated ' datetime Default NULL,  PRIMARY KEY (' Districtid ')) Engine=myisam default Charset=utf8;

Note:

In the provinces and cities, there are four cities in Beijing, Shanghai, Chongqing and Tianjin, so you have to judge for yourself.

First look at the following:

We put the binary database file into the Res/raw below the project:

The main function of the entire program, I have encapsulated in the CityInfoDataSupport2 class.

Package Com.example.province;import Java.io.file;import Java.io.fileoutputstream;import java.io.IOException;import Java.io.inputstream;import Java.util.arraylist;import Android.content.context;import android.database.Cursor; Import Android.database.sqlite.sqlitedatabase;import Android.database.sqlite.sqlitedatabase.cursorfactory;import Android.database.sqlite.sqliteexception;import Android.database.sqlite.sqliteopenhelper;import Android.os.environment;public class CityInfoDataSupport2 {private static CityInfoDataSupport2 cityinfodatasupport; public static final String package_name = "Com.example.province";/** * The path of the database in the memory of the phone system */private static string Database_pat H = "/data" + environment.getdatadirectory () + "/" +package_name + "/databases/";/** * Name of the database */public static final String D bname = "mzk_db";p rivate sqlitedatabase msdb;public static CityInfoDataSupport2 getinstance (context context) { Initdatabase (context), if (Cityinfodatasupport = = null) {Cityinfodatasupport = new CityinfodatasupporT2 ();} return cityinfodatasupport;} /** * Preliminary database */private static void Initdatabase (context context) {Boolean dbexist = Checkdatabase (); if (dbexist) {} else { If it does not exist, the data in raw is stored in the SD card Copydatabase (context) of the Phone;}} /** * Copy Database to phone specified folder * * @throws ioexception */private static void Copydatabase (context context) {String DatabaseFileName s = Database_path + dbName; File dir = new file (Database_path); FileOutputStream OS = Null;inputstream is = null;//Determines if a folder exists and does not exist to create an if (!dir.exists ()) {dir.mkdirs ();} try {//Gets the output stream of the database OS = new FileOutputStream (databasefilenames);//Gets the input stream of the data file is = Context.getresources (). Openrawresource (r.raw.mzk_db); byte[] buffer = new Byte[8192];int count = 0;while ((count = is.read (buffer))! =-1) {os.write (buffer, 0, C Ount); Os.flush ();} This is not initialized here because this is a static method, and msdb is not set to static, nor is it recommended to be static//msdb = Sqlitedatabase.openorcreatedatabase (Database_path +// DbName, null);} catch (Exception e) {e.printstacktrace ();} finally {try {os.close (); Is.close ();} catch (IOException e) {E.printstacktraCE ();}}} /** * Determine if the database exists * * @return */private static Boolean checkdatabase () {sqlitedatabase CheckDB = null; String databasefilename = Database_path + dbname;//To add itself to the try catch method try {//returns the latest database CHECKDB = Sqlitedatabase.opendatabase (DatabaseFileName, null,sqlitedatabase.open_readonly);} catch (Sqliteexception e) {//Todo:handle exception}if (CheckDB! = null) {Checkdb.close ();} If CheckDB is null, there is no database, return falsereturn CheckDB = = null? False:true;} /** * Query All provinces information * * @return Province information */public arraylist<city> queryprovince () {//CREATE DATABASE msdb = Sqlitedatabase.openorcrea Tedatabase (Database_path + dbName, NULL); arraylist<city> list = new arraylist<city> (); String sql = "SELECT * from Fs_province"; cursor cursor = msdb.rawquery (sql, NULL), while (Cursor.movetonext ()) {City city = new City (); String id = cursor.getstring (cursor.getcolumnindex ("Provinceid")); String name = cursor.getstring (Cursor.getcolumnindex ("Provincename")); City.setname (name); City.setid (ID); List.add ( City);} if (cursor! = null) {Cursor.close ();} return list;} /** * Find City by province * * @param provinceid * Province ID * @return All cities of the province */public arraylist<city> Querycitybyprov Ince (String Provinceid) {//CREATE DATABASE msdb = sqlitedatabase.openorcreatedatabase (Database_path + dbName, NULL); arraylist<city> list = new arraylist<city> (); String sql = "SELECT * from fs_city where provinceid=" + Provinceid; cursor cursor = msdb.rawquery (sql, NULL), while (Cursor.movetonext ()) {City city = new City (); String id = cursor.getstring (cursor.getcolumnindex ("Cityid")); String name = cursor.getstring (Cursor.getcolumnindex ("CityName")); City.setname (name); City.setid (ID); List.add (city );} if (cursor! = NULL) {Cursor.close ();} return list;} /** * Find the county by city * * @param provinceid * Province ID * @return All cities of the province */public arraylist<city> Querydistrictby City (String Cityid) {//CREATE DATABASE msdb = sqlitedatabase.openorcreatedatabase (Database_path + dbName, NULL); arraylist<city> list = new arraylist<city> (); String sql = "SELECT * from Fs_district where cityid=" + Cityid; cursor cursor = msdb.rawquery (sql, NULL), while (Cursor.movetonext ()) {City city = new City (); String id = cursor.getstring (cursor.getcolumnindex ("Districtid")); String name = cursor.getstring (Cursor.getcolumnindex ("Districtname")); City.setname (name); City.setid (ID); List.add ( City);} if (cursor! = NULL) {Cursor.close ();} return list;} public void CloseDataBase () {if (MSDB! = null) {Msdb.close ();}}}

In this class, the database binaries in the program are copied to the phone system memory. There's a lot of detail in the code, and I'm not going to say much here.

Next is the content of each activity, is very simple, I will post a bar, then I will upload the entire program.

Package Com.example.province;import Java.util.arraylist;import Android.app.activity;import android.content.Intent; Import Android.os.bundle;import android.os.environment;import Android.util.log;import Android.view.View;import Android.widget.adapterview;import Android.widget.adapterview.onitemclicklistener;import Android.widget.ListView The name of/** * province, including Beijing, Shanghai, Tianjin, Chongqing, Hong Kong, Macau These six are special examples * @author LGX * */public class Mainactivity extends Activity {arraylist<city& Gt Province; ListView province_list; @Overrideprotected void OnCreate (Bundle savedinstancestate) {super.oncreate ( Savedinstancestate); Setcontentview (r.layout.activity_main);p rovince_list = (ListView) Findviewbyid (R.id.province_ List);p rovince = Cityinfodatasupport2.getinstance (this). Queryprovince (); Testadapter adapter = new Testadapter (this, province);p Rovince_list.setadapter (adapter);p Rovince_ List.setonitemclicklistener (New Onitemclicklistener () {@Overridepublic void Onitemclick (adapterview<?> parent , View view,int position, long ID) {String Proviceid = province.get (position). GetId (); String provicename = province.get (position). GetName (); if (Provicename.equals ("Beijing") | | provicename.equals ("tianjin") | | Provicename.equals ("Shanghai") | | Provicename.equals ("Chongqing")) {Intent Intent = new Intent (mainactivity.this,districtactivity.class); Intent.putextra (" Pcode ", Proviceid); startactivity (intent);} else {Intent Intent = new Intent (mainactivity.this,cityactivity.class); Intent.putextra ("Pcode", Proviceid); StartActivity (intent);}});} @Overrideprotected void OnDestroy () {Super.ondestroy ();//Cityinfodatasupport.getinstance (this). Close ();}}

Project: http://download.csdn.net/detail/harryweasley/8807143

Get the names of provincial cities and counties by database