我在我的应用程序中有一个相当复杂的数据库。
当需要添加更多功能时,我开始思考是否应该通过向现有数据库添加更多表来实现,还是为新增功能打开全新的数据库并在其中添加表。
是否有关于何时打开新数据库的基本规则?是否有关于此主题的基准可用?
通常情况下,将所有表放在一个数据库中更好,特别是在这些表有共同之处的情况下(有时需要连接两个或多个表,如果将表放到另一个数据库中,这将变得不可能)。此外,您现在不知道是否会在未来更新表格,更改它们之间的关系等(例如由于应用程序更新)。另一方面,应用程序的结构也更加清晰易读,例如,如果您在内部或外部存储器上有五个 *.db
文件,那么应用程序的结构就不如只有一个文件。
关于实现的问题: 我建议您(如上所述)只创建一个 DatabaseHelper,它将包装所有必需的数据库逻辑。然后创建类(每个表一个类),例如 UsersTools 类,它将包装 CRUD 操作和特定表的特定方法。我使用这种方法从未遇到过问题。
以下是 DatabaseHelper 的示例:
package com.sajmon.examples.db;
import android.content.Context;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
public class DataSource extends SQLiteOpenHelper {
private static DataSource instance = null;
public static final String DB_NAME = "<dbname>";
public static final int DB_STARTING_VERSION = 1;
public DataSource(Context cntx) {
super(cntx, DB_NAME, null, DB_STARTING_VERSION);
}
/**
*
* @param mContext as Context of Activity
* @return <li>new instance of }DataSource object.</li>
*/
public static DataSource getInstance(Context mContext) {
if (instance == null) {
instance = new DataSource(mContext);
}
return instance;
}
@Override
public void onCreate(SQLiteDatabase db) {
String query = "create table " + SQLConstants.TEST_TABLE_NAME + " ("
+ SQLConstants.KEY_ID + " integer not null, "
+ SQLConstants.KEY_TYPE + " text null, "
+ SQLConstants.KEY_DATE + " text null" + ")";
db.execSQL(query);
}
@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
String query = "drop table if exists " + SQLConstants.TEST_TABLE_NAME;
db.execSQL(query);
onCreate(db);
}
}
package com.sajmon.examples.db;
public class SQLConstants {
public static final String TEST_TABLE_NAME = "Test";
public static final String KEY_ID = "id";
public static final String KEY_TYPE = "type";
public static final String KEY_DATE = "date";
}
package com.sajmon.examples.db;
import java.util.Date;
import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.database.sqlite.SQLiteStatement;
public class TestTools {
private Context mContext;
private SQLiteDatabase db;
public TestTools(Context mContext) {
this.mContext = mContext;
}
public boolean insert(Test t) throws SQLException {
try {
db = openWrite(DataSource.getInstance(mContext));
ContentValues values = new ContentValues();
if (db != null) {
db.beginTransaction();
values.put(SQLConstants.KEY_ID, t.getId());
values.put(SQLConstants.KEY_TYPE, t.getType());
values.put(SQLConstants.KEY_DATE, t.getDate());
db.insertOrThrow(SQLConstants.TEST_TABLE_NAME, SQLConstants.KEY_TYPE, values);
values.clear();
db.setTransactionSuccessful();
return true;
}
return false;
}
finally {
if (db != null) {
db.endTransaction();
}
close(db);
}
}
public boolean update(Test t) {
try {
db = openWrite(DataSource.getInstance(mContext));
ContentValues values = new ContentValues();
int count = -1;
if (db != null) {
db.beginTransaction();
values.put(SQLConstants.KEY_TYPE, t.getType());
values.put(SQLConstants.KEY_DATE, t.getDate());
count = db.update(SQLConstants.TEST_TABLE_NAME, values, SQLConstants.KEY_ID + "= ?", new String[] {t.getId()});
db.setTransactionSuccessful();
}
return count > 0;
}
finally {
if (db != null) {
db.endTransaction();
close(db);
}
}
}
public boolean delete(Test t) {
int count = -1;
try {
db = openWrite(DataSource.getInstance(mContext));
if (db != null) {
db.beginTransaction();
count = db.delete(SQLConstants.TEST_TABLE_NAME, SQLConstants.KEY_ID + "= ?", new String[] {t.getId()});
db.setTransactionSuccessful();
}
return count > 0;
}
finally {
if (db != null)
db.endTransaction();
close(db);
}
}
}
public List<Test> getAll() {
Cursor c = null;
List<Test> tests = new ArrayList<Test>();
Test test = null;
try {
db = openRead(DataSource.getInstance(mContext));
if (db != null) {
String[] columns = {SQLConstants.KEY_ID, SQLConstants.KEY_TYPE, SQLConstants.KEY_DATE};
c = db.query(SQLConstants.TEST_TABLE_NAME, columns, null, null, null, null, null);
if (c.moveToFirst()) {
do {
test = new Test();
test.setId(c.getInt(c.getColumnIndex(SQLConstants.KEY_ID)));
test.setType(c.getString(c.getColumnIndex(SQLConstants.KEY_TYPE)));
test.setDate(c.getString(c.getColumnIndex(SQLConstants.KEY_DATE)));
tests.add(test);
} while(c.moveToNext());
}
}
return tests;
}
finally {
if (c != null) {
c.close();
}
if (db != null) {
close(db);
}
}
}
private final synchronized SQLiteDatabase openWrite(SQLiteOpenHelper handler) {
return handler.getWritableDatabase();
}
private final synchronized SQLiteDatabase openRead(SQLiteOpenHelper handler) {
return handler.getReadableDatabase();
}
private final synchronized void close(SQLiteDatabase db) {
if (db != null && db.isOpen()) {
db.close();
}
}
}