通過資料庫擷取省份城市區縣的名字，資料庫擷取省份區縣

通過資料庫擷取省份城市區縣的名字，資料庫擷取省份區縣

轉載請註明出處，謝謝http://blog.csdn.net/harryweasley/article/details/46557807

本篇部落格的主要目的，是介紹怎樣將資料庫檔案儲存到手機系統檔案，並且可以顯示各個省市地的名字。

資料庫一共有三個表，他們分別是（後面我將上傳整個檔案和資料庫檔案）

省表

CREATE TABLE IF NOT EXISTS `fs_province` (  `ProvinceID` bigint(20) NOT NULL,  `ProvinceName` varchar(50) DEFAULT NULL,  `DateCreated` datetime DEFAULT NULL,  `DateUpdated` datetime DEFAULT NULL,  PRIMARY KEY (`ProvinceID`)) ENGINE=MyISAM DEFAULT CHARSET=utf8;

城市表

CREATE TABLE IF NOT EXISTS `fs_city` (  `CityID` bigint(20) NOT NULL,  `CityName` varchar(50) DEFAULT NULL,  `ZipCode` varchar(50) DEFAULT NULL,  `ProvinceID` bigint(20) DEFAULT NULL,  `DateCreated` datetime DEFAULT NULL,  `DateUpdated` datetime DEFAULT NULL,  PRIMARY KEY (`CityID`)) ENGINE=MyISAM DEFAULT CHARSET=utf8;

縣區表

CREATE TABLE IF NOT EXISTS `fs_district` (  `DistrictID` bigint(20) NOT NULL,  `DistrictName` varchar(50) DEFAULT NULL,  `CityID` bigint(20) DEFAULT NULL,  `DateCreated` datetime DEFAULT NULL,  `DateUpdated` datetime DEFAULT NULL,  PRIMARY KEY (`DistrictID`)) ENGINE=MyISAM DEFAULT CHARSET=utf8;

注意：

省份表和城市表中，都有北京，上海，重慶，天津這四個城市，所以要自己判斷一下。

先看下：

我們將二進位的資料庫檔案放入工程的res/raw下面，：

整個程式的主要功能，我都封裝在了CityInfoDataSupport2這個類中。

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";/** * 資料庫在手機系統記憶體裡的路徑 */private static String DATABASE_PATH = "/data"+ Environment.getDataDirectory() + "/"+PACKAGE_NAME + "/databases/";/** * 資料庫的名稱 */public static final String dbName = "mzk_db";private SQLiteDatabase mSDB;public static CityInfoDataSupport2 getInstance(Context context) {initDataBase(context);if (cityInfoDataSupport == null) {cityInfoDataSupport = new CityInfoDataSupport2();}return cityInfoDataSupport;}/** * 初試化資料庫 */private static void initDataBase(Context context) {boolean dbExist = checkDataBase();if (dbExist) {} else {// 如果不存在，則將raw裡的資料存入手機sd卡copyDataBase(context);}}/** * 複製資料庫到手機指定檔案夾下 *  * @throws IOException */private static void copyDataBase(Context context) {String databaseFilenames = DATABASE_PATH + dbName;File dir = new File(DATABASE_PATH);FileOutputStream os = null;InputStream is = null;// 判斷檔案夾是否存在，不存在就建立一個if (!dir.exists()) {dir.mkdirs();}try {// 得到資料庫的輸出資料流os = new FileOutputStream(databaseFilenames);// 得到資料檔案的輸入資料流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, count);os.flush();}// 之所以不在這裡初始化，是因為這邊是靜態方法，而mSDB並沒有設定為靜態，也不推薦設為靜態// mSDB = SQLiteDatabase.openOrCreateDatabase(DATABASE_PATH +// dbName, null);} catch (Exception e) {e.printStackTrace();} finally {try {os.close();is.close();} catch (IOException e) {e.printStackTrace();}}}/** * 判斷資料庫是否存在 *  * @return */private static boolean checkDataBase() {SQLiteDatabase checkDB = null;String databaseFilename = DATABASE_PATH + dbName;// 要自己加上try catch方法try {// 返回最新的資料庫checkDB = SQLiteDatabase.openDatabase(databaseFilename, null,SQLiteDatabase.OPEN_READONLY);} catch (SQLiteException e) {// TODO: handle exception}if (checkDB != null) {checkDB.close();}// 如果checkDB為null，則沒有資料庫，返回falsereturn checkDB == null ? false : true;}/** * 查詢所有省份的資訊 *  * @return 省份資訊 */public ArrayList<City> queryProvince() {// 建立資料庫mSDB = SQLiteDatabase.openOrCreateDatabase(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;}/** * 通過省份來尋找城市 *  * @param provinceId *            省份的id * @return 該省的所有城市 */public ArrayList<City> queryCityByProvince(String provinceId) {// 建立資料庫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;}/** * 通過城市來尋找縣區 *  * @param provinceId *            省份的id * @return 該省的所有城市 */public ArrayList<City> queryDistrictByCity(String cityId) {// 建立資料庫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();}}}

這個類中，將程式中的資料庫二進位檔案複製到了手機系統記憶體中。代碼中都有很詳細的注釋，我在這裡就不多說了。

接下來就是各個Activity的內容了，都很簡單，我就貼一個吧，之後我將上傳整個程式。

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;/** * 省份的名字，其中北京，上海，天津，重慶，香港，澳門這六個是特殊的例子 * @author lgx * */public class MainActivity extends Activity {ArrayList<City> province;ListView province_list;@Overrideprotected void onCreate(Bundle savedInstanceState) {super.onCreate(savedInstanceState);setContentView(R.layout.activity_main);province_list = (ListView) findViewById(R.id.province_list);province = CityInfoDataSupport2.getInstance(this).queryProvince();TestAdapter adapter = new TestAdapter(this, province);province_list.setAdapter(adapter);province_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("北京") || proviceName.equals("天津")|| proviceName.equals("上海") || proviceName.equals("重慶")) {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();}}

項目：http://download.csdn.net/detail/harryweasley/8807143