移动应用开发实践-Task7-城市列表导入数据库

目的

使用数据库导入浏览到的在线城市列表，实现离线浏览

1.编写 CityDatabase

主要实现数据库初始化（建表）、打开数据库、结束数据库、导入对象、导入对象列表、重置数据库、通过游标获取对象列表、通过sql语句获取对象列表、查询所有省份等。

package com.example.fyn_weather_task8_0.db;

import ...

public class CityDatabase {
    public static final String KEY_ID = "mid";//main id for url
    public static final String KEY_PID = "pid";// parent id
    public static final String KEY_NAME = "name";
    public static final String KEY_WEATHER_ID = "weather_id";
    public static final String KEY_EN_NAME = "en_name";
    public static final String KEY_INI_NAME = "ini_name";
    public static final String KEY_LEVEL = "level";
    public static final String KEY_LOOK_UP = "key_look_up";
    private static final String DB_NAME = "citydb.db";
    public static final String CITY_TABLE = "city";
    private int version = 1;

    private Activity activity;
    private SQLiteDatabase db;
    private DatabaseHelper databaseHelper;

    public CityDatabase(Activity activity) {
        this.activity = activity;
    }

    public void open() {// open database by DatabaseHelper
        if (db == null || !db.isOpen()) {
            databaseHelper = new DatabaseHelper();
            db = databaseHelper.getWritableDatabase();
        }
    }

    public void close() {// close the database
        if (db != null && db.isOpen()) {
            db.close();
        }
    }

    private ContentValues enCodeCotentValues(City city) {// parse the city to ContentValues
        ContentValues cv = new ContentValues();
        cv.put(KEY_EN_NAME, city.getEnName());
        cv.put(KEY_ID, city.getId());
        cv.put(KEY_INI_NAME, city.getInitialName());
        cv.put(KEY_LEVEL, city.getLevel());
        cv.put(KEY_LOOK_UP, generateLookup(city));
        cv.put(KEY_PID, city.getParentId());
        cv.put(KEY_WEATHER_ID, city.getWeather_id());
        cv.put(KEY_NAME, city.getName());
        return cv;
    }

    private City getCityFromCursor(Cursor c) {
        // parse the Cursor c to City by present position
        // ctr+d copy current row
        String name = c.getString(c.getColumnIndex(KEY_NAME));
        String enName = c.getString(c.getColumnIndex(KEY_EN_NAME));
        String iniName = c.getString(c.getColumnIndex(KEY_INI_NAME));
        String weather_id = c.getString(c.getColumnIndex(KEY_WEATHER_ID));
        int id = c.getInt(c.getColumnIndex(KEY_ID));
        int pid = c.getInt(c.getColumnIndex(KEY_PID));
        int level = c.getInt(c.getColumnIndex(KEY_LEVEL));
        City city = new City(id, name, pid);
        city.setEnName(enName);
        city.setInitialName(iniName);
        city.setLevel(level);
        city.setWeather_id(weather_id);
        return city;
    }

    public long insertData(City city) { // insert city to the database
        ContentValues cv = enCodeCotentValues(city);
        return db.insert(CITY_TABLE, KEY_WEATHER_ID, cv);

    }


    public int insertList(List<City> list) {// insert the whole list to the database
        int count = 0;
        if (list != null && list.size() > 0) {
            for (int i = 0; i < list.size(); i++) {
                City city = list.get(i);
                if (insertData(city) > 0) {
                    count++;
                }
            }
        }
        return count;
    }

    public void clearDatabase() {//clear the data
        databaseHelper.resetData(db);
    }

    public List<City> getCityListFromCursor(Cursor c) {// change the cursor to a City list
        List<City> list = new ArrayList<>();
        if (c != null && c.getCount() > 0) {
            for (int i = 0; i < c.getCount(); i++) {
                c.moveToPosition(i);
                City city = getCityFromCursor(c);
                list.add(city);
            }
        }
        return list;

    }

    private List<City> getCityListBySql(String sql, String[] args) {// get list by sql and args
        Cursor cursor = db.rawQuery(sql, args);
        List<City> list = getCityListFromCursor(cursor);
        cursor.close();
        return list;
    }

    public List<City> queryAllProvinces() {// get the root data
        String sql = String.format("select * from %s where %s=0", CITY_TABLE, KEY_LEVEL);
        List<City> list = getCityListBySql(sql, null);
        return list;

    }

    public List<City> queryCityListByParentId(int parentId, int level) {
        String sql = String.format("select * from %s where %s=%d and %s=%d", CITY_TABLE, KEY_PID, parentId, KEY_LEVEL, level);
        List<City> list = getCityListBySql(sql, null);
        return list;
    }

    class DatabaseHelper extends SQLiteOpenHelper {
        public DatabaseHelper() {
            super(activity, DB_NAME, null, version);
        }

        @Override
        public void onCreate(SQLiteDatabase db) {
            String sql = String.format("create table if not exists %s" +
                            "(_id INTEGER PRIMARY KEY AUTOINCREMENT,%s int,%s int,%s text,%s text,%s text,%s int,%s text,%s text)",
                    CITY_TABLE, KEY_ID, KEY_PID, KEY_NAME, KEY_EN_NAME, KEY_INI_NAME, KEY_LEVEL, KEY_LOOK_UP, KEY_WEATHER_ID);
            db.execSQL(sql);
        }

        @Override
        public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
            resetData(db);
        }

        public void resetData(SQLiteDatabase db) {
            String sql = String.format("drop table if exists %s", CITY_TABLE);
            db.execSQL(sql);
            onCreate(db);
        }
    }
}

2.MainActivity改写

package com.example.fyn_weather_task8_0;

import ...

public class MainActivity extends AppCompatActivity {

...

    @Override
    protected void onCreate(Bundle savedInstanceState) {
        ...
        cityDatabase = new CityDatabase(MainActivity.this);
        cityDatabase.open();
        ...
    }


    private void getAndUpdateCityList(final String url, final int parent_id, final int level) {
        List<City> list = cityDatabase.queryCityListByParentId(parent_id, level);

        if (list == null || list.size() == 0) {

            HttpUtil.getOkHttpAsync(MainActivity.this, url, new HttpUtil.SimpleAsyncCall() {
                @Override
                public void onFailure(String e) {
                    showToast(e);
                }

                @Override
                public void onResponse(String s) {
                    textView.setText(url);
                    List<City> list = JsonUtil.getCityListFromJson(s, parent_id, level);
                    cityDatabase.insertList(list);
                    showDbList(parent_id, level);
                    //updateListView(list);
                }
            });
        } else {
            textView.setText(url);
            updateListView(list);
        }


    }

    private void showDbList(int parent_id, int level) {
        List<City> list = cityDatabase.queryCityListByParentId(parent_id, level);
        updateListView(list);
    }

    private void updateListView(List<City> list) {
        ...
    }

    @Override
    protected void onDestroy() {
        super.onDestroy();
        cityDatabase.close();
    }
}