Android基础（10）数据库

1）如何导入外部数据库?
2）数据库数据迁移问题
3）数据库框架对比和源码分析 GreenDao、Realm
4）sqlite升级，增加字段的语句
5）数据库的优化

一. 如何导入外部数据库？

public class DbManager {

    public static final String PACKAGE_NAME = "com.example.sql";
    public static final String DB_NAME = "table.db";
    public static final String DB_PATH = "/data/data/" + PACKAGE_NAME;
    private Context mContext;

    public DbManager(Context mContext) {
        this.mContext = mContext;
    }

    public SQLiteDatabase openDataBase() {
        return SQLiteDatabase.openOrCreateDatabase(DB_PATH + "/" + DB_NAME, null);
    }

    public void importDB() {
        File  file = new File(DB_PATH + "/" + DB_NAME);
        if (!file.exists()) {
            try {
                FileOutputStream out = new FileOutputStream(file);
                int buffer = 1024;

                InputStream in = mContext.getResources().openRawResource(R.raw.xxxx);
                byte[] bts = new byte[buffer];
                int lenght;
                while ((lenght = in.read(bts)) > 0) {
                    out.write(bts, 0, bts.length);
                }
                out.close();
                in.close();
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
    }
}

调用方式

@Override
protected void onResume() {
    super.onResume();
    DbManager dbManager = new DbManager(this);
    dbManager.importDB();
    SQLiteDatabase db = dbManager.openDataBase();
    db.execSQL("do what you want");
}

二. 数据库迁移

数据库的迁移
可以分一下几个步骤迁移数据库

1、将表名改成临时表

ALTER TABLE Order RENAME TO _Order;

2、创建新表

CREATETABLE Test(Id VARCHAR(32) PRIMARY KEY ,CustomName VARCHAR(32) NOTNULL , Country VARCHAR(16) NOTNULL);

3、导入数据

INSERTINTO Order SELECT id, “”, Age FROM _Order;

4、删除临时表

DROPTABLE _Order;

通过以上四个步骤，就可以完成旧数据库结构向新数据库结构的迁移，并且其中还可以保证数据不会因为升级而流失。
当然，如果遇到减少字段的情况，也可以通过创建临时表的方式来实现。

@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
    if (newVersion == 2) {
        char str = '"';
        db.beginTransaction();
        db.execSQL("ALTER TABLE Order RENAME TO _Order");
        db.execSQL("CREATE TABLE Order(Id integer primary key autoincrement , CustomName VARCHAR(20) NOT NULL,"
                + " Country VARCHAR(32) NOT NULL , OrderPrice VARCHAR(16) NOT NULL)");
        db.execSQL("INSERT INTO Order SELECT Id, " + str + str
                + ", CustomName, OrderPrice FROM _Order");
        db.setTransactionSuccessful();
        db.endTransaction();
    }
}

三. 性能对比

插入1000条以内的数据时，Realm与GreenDao不分伯仲
查询操作比较频繁，就使用Realm
删除和更新操作比较频繁，就使用GreenDao

realm : https://www.cnblogs.com/mengdd/p/android-realm-database-guides.html

四. 数据库升级

@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
    if (1 == oldVersion) {
        String sql = "Create table C....";
        db.execSQL(sql);
        oldVersion = 2;
    }

    if (2 == oldVersion) {
        //modify C
        oldVersion = 3;
    }
}