Android 处理多个表的最佳实践

Best practices for working with multiple tables

提问by Burjua

I use a database with multiple tables in my application. I have an XML parser which needs to write data to two tables while parsing. I created two database adapters for both tables, but now I have a problem. When I'm working with one table, it's easy:

我在我的应用程序中使用了一个包含多个表的数据库。我有一个 XML 解析器，它需要在解析时将数据写入两个表。我为两个表创建了两个数据库适配器，但现在我遇到了问题。当我使用一张桌子时，这很容易：

FirstDBAdapter firstTable = new FirstDBAdapter(mycontext);
firstTable.open(); // open and close it every time I need to insert something
                   // may be hundreds of times while parsing
                   // it opens not a table but whole DB     
firstTable.insertItem(Item);        
firstTable.close(); 

Since it's a SAX parser, in my opinion (maybe I'm wrong), this will be even better:

由于它是一个 SAX 解析器，在我看来（也许我错了），这会更好：

FirstDBAdapter firstTable = new FirstDBAdapter(mycontext);

@Override
public void startDocument() throws SAXException 
{
    firstTable.open(); // open and close only once
}

...
firstTable.insertItem(Item);
...

@Override
public void endDocument() throws SAXException 
{
    firstTable.close();
}

But how do I do it if I need to insert data to the second table? For example, if I have the second adapter, which I think will be a bad idea:

但是如果我需要将数据插入到第二个表中，我该怎么做呢？例如，如果我有第二个适配器，我认为这将是一个坏主意：

FirstDBAdapter firstTable = new FirstDBAdapter(mycontext);
SecondDBAdapter secondTable = new SecondDBAdapter(mycontext);

@Override
public void startDocument() throws SAXException 
{
    firstTable.open();
    secondTable.open(); 
}

Any thoughts on how to achieve this?

关于如何实现这一目标的任何想法？

采纳答案by WarrenFaith

My database adapter. An instance is always stored in MyApplication which inherites from Application. Just think about a second table where I defined the first one... currently this is just a short version, in reality this adapter handles 7 tables in the database.

我的数据库适配器。实例始终存储在继承自 Application 的 MyApplication 中。想想我定义第一个表的第二个表……目前这只是一个简短的版本，实际上这个适配器处理数据库中的 7 个表。

public class MyDbAdapter {
    private static final String LOG_TAG = MyDbAdapter.class.getSimpleName();

    private SQLiteDatabase mDb;
    private static MyDatabaseManager mDbManager;

    public MyDbAdapter() {
        mDbManager = new MyDatabaseManager(MyApplication.getApplication());
        mDb = mDbManager.getWritableDatabase();
    }

    public static final class GameColumns implements BaseColumns {
        public static final String TABLE = "game";
        public static final String IMEI = "imei";
        public static final String LAST_UPDATE = "lastupdate";
        public static final String NICKNAME = "nickname";
    }

    public String getImei() {
        checkDbState();
        String retValue = "";
        Cursor c = mDb.rawQuery("SELECT imei FROM " + GameColumns.TABLE, null);
        if (c.moveToFirst()) {
            retValue = c.getString(c.getColumnIndex(GameColumns.IMEI));
        }
        c.close();
        return retValue;
    }

    public void setImei(String imei) {
        checkDbState();
        ContentValues cv = new ContentValues();
        cv.put(GameColumns.IMEI, imei);
        mDb.update(GameColumns.TABLE, cv, null, null);
    }

    public boolean isOpen() {
        return mDb != null && mDb.isOpen();
    }

    public void open() {
        mDbManager = new MyDatabaseManager(MyApplication.getApplication());
        if (!isOpen()) {
            mDb = mDbManager.getWritableDatabase();
        }
    }

    public void close() {
        if (isOpen()) {
            mDb.close();
            mDb = null;
            if (mDbManager != null) {
                mDbManager.close();
                mDbManager = null;
            }
        }
    }

    private void checkDbState() {
        if (mDb == null || !mDb.isOpen()) {
            throw new IllegalStateException("The database has not been opened");
        }
    }

    private static class MyDatabaseManager extends SQLiteOpenHelper {
        private static final String DATABASE_NAME = "dbname";
        private static final int DATABASE_VERSION = 7;

        private MyDatabaseManager(Context context) {
            super(context, DATABASE_NAME, null, DATABASE_VERSION);
        }

        @Override
        public void onCreate(SQLiteDatabase db) {
            createGameTable(db);
        }

        @Override
        public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
            Log.w(LOG_TAG, "Upgrading database from version " + oldVersion + " to " + newVersion + "!");
        }

        private void dropDatabase(SQLiteDatabase db) {
            db.execSQL("DROP TABLE IF EXISTS " + GameColumns.TABLE);
        }

        private void createGameTable(SQLiteDatabase db) {
            db.execSQL("CREATE TABLE " + GameColumns.TABLE + " ("
                    + GameColumns._ID + " INTEGER PRIMARY KEY,"
                    + GameColumns.IMEI + " TEXT,"
                    + GameColumns.LAST_UPDATE + " TEXT,"
                    + GameColumns.NICKNAME + " TEXT);");
            ContentValues cv = new ContentValues();
            cv.put(GameColumns.IMEI, "123456789012345");
            cv.put(GameColumns.LAST_UPDATE, 0);
            cv.put(GameColumns.NICKNAME, (String) null);
            db.insert(GameColumns.TABLE, null, cv);
        }
    }
}

回答by phoxicle

I've had success with creating an abstract base class with the database name/create statement and other shared info, and then extending it for every table. This way, I can keep all of my CRUD methods separate (which I much prefer). The only downside is that the DATABASE_CREATE statement(s) must reside in the parent class, and must include all of the tables, because new tables can't be added afterwards, but in my opinion that's a small price to pay to keep the CRUD methods for each table separate.

我已经成功地使用数据库名称/创建语句和其他共享信息创建了一个抽象基类，然后为每个表扩展它。这样，我可以将所有 CRUD 方法分开（我更喜欢）。唯一的缺点是 DATABASE_CREATE 语句必须驻留在父类中，并且必须包含所有表，因为之后无法添加新表，但在我看来，这是保持 CRUD 的小代价每个表的方法分开。

Doing this was fairly simple, but here are some notes:

这样做相当简单，但这里有一些注意事项：

• The create statement in the parent class mustbe broken up for each table, because db.execSQL cannot execute more than one statement.
• I changed all private vars/methods to protected, just in case.
• If you are adding tables to an existing application (not sure if this is specific to emulator), the application must be uninstalled and then reinstalled.

• 父类中的create语句必须针对每张表进行拆分，因为db.execSQL不能执行多个语句。
• 为了以防万一，我将所有私有变量/方法更改为受保护。
• 如果您要向现有应用程序添加表（不确定这是否特定于模拟器），则必须卸载该应用程序，然后重新安装。

Here is the code for my abstract parent class, which was based on the Notepad Tutorial. The children simply extend this, calling the super's constructor (feel free to use this):

这是我的抽象父类的代码，它基于记事本教程。孩子们简单地扩展它，调用超级的构造函数（随意使用它）：

package com.pheide.trainose;

import android.content.Context;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;

public abstract class AbstractDbAdapter {

    protected static final String TAG = "TrainOseDbAdapter";
    protected DatabaseHelper mDbHelper;
    protected SQLiteDatabase mDb;

    protected static final String TABLE_CREATE_ROUTES =
        "create table routes (_id integer primary key autoincrement, "
        + "source text not null, destination text not null);";
    protected static final String TABLE_CREATE_TIMETABLES =    
        "create table timetables (_id integer primary key autoincrement, "
        + "route_id integer, depart text not null, arrive text not null, "
        + "train text not null);";

    protected static final String DATABASE_NAME = "data";
    protected static final int DATABASE_VERSION = 2;

    protected final Context mCtx;

    protected static class DatabaseHelper extends SQLiteOpenHelper {

        DatabaseHelper(Context context) {
            super(context, DATABASE_NAME, null, DATABASE_VERSION);
        }

        @Override
        public void onCreate(SQLiteDatabase db) {
            db.execSQL(TABLE_CREATE_ROUTES);
            db.execSQL(TABLE_CREATE_TIMETABLES);
        }

        @Override
        public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
            Log.w(TAG, "Upgrading database from version " + oldVersion + " to "
                    + newVersion + ", which will destroy all old data");
            db.execSQL("DROP TABLE IF EXISTS routes");
            onCreate(db);
        }
    }

    public AbstractDbAdapter(Context ctx) {
        this.mCtx = ctx;
    }

    public AbstractDbAdapter open() throws SQLException {
        mDbHelper = new DatabaseHelper(mCtx);
        mDb = mDbHelper.getWritableDatabase();
        return this;
    }

    public void close() {
        mDbHelper.close();
    }

}

A slightly more detailed explanation is available here: http://pheide.com/page/11/tab/24#post13

这里有一个更详细的解释：http: //pheide.com/page/11/tab/24#post13

回答by mjama

phoxicle's solution is a great starting point, but per Kevin Galligan's notes on Android's SQLite serialization, this implementation isn't thread safe and will fail silently when multiple database connections (e.g. from different threads) try to write the database:

phoxicle 的解决方案是一个很好的起点，但根据 Kevin Galligan关于 Android 的 SQLite 序列化的说明，此实现不是线程安全的，并且会在多个数据库连接（例如来自不同线程）尝试写入数据库时​​静默失败：

If you try to write to the database from actual distinct connections at the same time, one will fail. It will not wait till the first is done and then write. It will simply not write your change. Worse, if you don't call the right version of insert/update on the SQLiteDatabase, you won't get an exception. You'll just get a message in your LogCat, and that will be it.

So, multiple threads? Use one helper.

如果您尝试同时从实际的不同连接写入数据库，则会失败。它不会等到第一个完成再写。它根本不会写入您的更改。更糟糕的是，如果您没有在 SQLiteDatabase 上调用正确版本的插入/更新，则不会出现异常。您只会在 LogCat 中收到一条消息，就这样。

那么，多线程？使用一名助手。

---

---

Here's a modified implementation of phoxicle's database adapter that uses a static SQLiteOpenHelper instance and is thus limited to a single database connection:

这是 phoxicle 数据库适配器的修改实现，它使用静态 SQLiteOpenHelper 实例，因此仅限于单个数据库连接：

public class DBBaseAdapter {

    private static final String TAG = "DBBaseAdapter";

    protected static final String DATABASE_NAME = "db.sqlite";
    protected static final int DATABASE_VERSION = 1;

    protected Context mContext;
    protected static DatabaseHelper mDbHelper;

    private static final String TABLE_CREATE_FOO = 
        "create table foo (_id integer primary key autoincrement, " +
        "bar text not null)");

    public DBBaseAdapter(Context context) {
        mContext = context.getApplicationContext();
    }

    public SQLiteDatabase openDb() {
        if (mDbHelper == null) {
            mDbHelper = new DatabaseHelper(mContext);
        }
        return mDbHelper.getWritableDatabase();
    }

    public void closeDb() {
        mDbHelper.close();
    }

    protected static class DatabaseHelper extends SQLiteOpenHelper {

        public DatabaseHelper(Context context) {
            super(context, DATABASE_NAME, null, DATABASE_VERSION);
        }

        @Override
        public void onCreate(SQLiteDatabase db) {
            db.execSQL(TABLE_CREATE_FOO);
        }

        @Override
        public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
            Log.w(TAG, "Upgrading database from version " + oldVersion + " to " +
                newVersion + ", which will destroy all old data");
            db.execSQL("DROP TABLE IF EXISTS routes");
            onCreate(db);
        }
    }
}

Extend DBBaseAdapter for each table to implement your CRUD methods:

为每个表扩展 DBBaseAdapter 以实现您的 CRUD 方法：

public class DBFooTable extends DBBaseAdapter {

    public DBFooTable(Context context) {
        super(context);
    }

    public void getBar() {

        SQLiteDatabase db = openDb();
        // ...
        closeDb();
}

回答by 1020rpz

I'm a bit late maybe but i always open my database, not my table. So this form me as no sense.

我可能有点晚了，但我总是打开我的数据库，而不是我的表。所以这让我觉得毫无意义。

    firstTable.open();
    secondTable.open(); 

Rather do this.

而是这样做。

    dataBase.getWritableDatabase();

then if you want to update juste chose the table:

那么如果你想更新 juste 选择表：

public int updateTotal (int id, Jours jour){
    ContentValues values = new ContentValues();

    values.put(COL_TOTAL,Total );

    //update the table you want
    return bdd.update(TABLE_NAME, values, COL_JOUR + " = " + id, null);
}

And that's all. Hope it can help other people

就这样。希望它可以帮助其他人