如何将一个长的、单一的 SQLiteOpenHelper 分成几个类，每个类一个 [英] How can I split a long, single SQLiteOpenHelper into several classes, one for each table
问题描述
我知道之前有人问过这个问题，但在所有这些问题中，OP 和回答的人都没有提供明确的例子.
I know this has been asked a couple of times before, but in all those questions neither the OP's nor the people who answered, provided clear examples.
所以我想在这里问的是是否有这样的课程
So what I'm trying to ask here is if having a class like this
public class MyDatabaseDB {
// database constants
public static final String DB_NAME = "mydatabase.db";
public static final int DB_VERSION = 1;
// list table constants
public static final String LIST_TABLE = "list";
public static final String LIST_ID = "_id";
public static final int LIST_ID_COL = 0;
public static final String LIST_NAME = "list_name";
public static final int LIST_NAME_COL = 1;
// task table constants
public static final String TASK_TABLE = "task";
public static final String TASK_ID = "_id";
public static final int TASK_ID_COL = 0;
public static final String TASK_LIST_ID = "list_id";
public static final int TASK_LIST_ID_COL = 1;
public static final String TASK_NAME = "task_name";
public static final int TASK_NAME_COL = 2;
// CREATE and DROP TABLE statements
public static final String CREATE_LIST_TABLE =
"CREATE TABLE " + LIST_TABLE + " (" +
LIST_ID + " INTEGER PRIMARY KEY AUTOINCREMENT, " +
LIST_NAME + " TEXT UNIQUE)";
public static final String CREATE_TASK_TABLE =
"CREATE TABLE " + TASK_TABLE + " (" +
TASK_ID + " INTEGER PRIMARY KEY AUTOINCREMENT, " +
TASK_LIST_ID + " INTEGER, " +
TASK_NAME + " TEXT " +
)";
public static final String DROP_LIST_TABLE =
"DROP TABLE IF EXISTS " + LIST_TABLE;
public static final String DROP_TASK_TABLE =
"DROP TABLE IF EXISTS " + TASK_TABLE;
private static class DBHelper extends SQLiteOpenHelper {
public DBHelper(Context context, String name,
CursorFactory factory, int version) {
super(context, name, factory, version);
}
@Override
public void onCreate(SQLiteDatabase db) {
// create tables
db.execSQL(CREATE_LIST_TABLE);
db.execSQL(CREATE_TASK_TABLE);
// insert lists
db.execSQL("INSERT INTO list VALUES (1, 'Hobbies')");
db.execSQL("INSERT INTO list VALUES (2, 'Sports')");
// insert sample tasks
db.execSQL("INSERT INTO task VALUES (1, 1, 'Play the guitar')");
db.execSQL("INSERT INTO task VALUES (2, 1, 'Play video games')");
}
@Override
public void onUpgrade(SQLiteDatabase db,
int oldVersion, int newVersion) {
Log.d("Task list", "Upgrading db from version "
+ oldVersion + " to " + newVersion);
db.execSQL(MyDatabaseDB.DROP_LIST_TABLE);
db.execSQL(MyDatabaseDB.DROP_TASK_TABLE);
onCreate(db);
}
}
// database object and database helper object
private SQLiteDatabase db;
private DBHelper dbHelper;
// constructor
public MyDatabaseDB(Context context) {
dbHelper = new DBHelper(context, DB_NAME, null, DB_VERSION);
}
// private methods
private void openReadableDB() {
db = dbHelper.getReadableDatabase();
}
private void openWriteableDB() {
db = dbHelper.getWritableDatabase();
}
private void closeDB() {
if (db != null)
db.close();
}
// public methods
public long insertTask(Task task) {
ContentValues cv = new ContentValues();
cv.put(TASK_LIST_ID, task.getListId());
cv.put(TASK_NAME, task.getName());
this.openWriteableDB();
long rowID = db.insert(TASK_TABLE, null, cv);
this.closeDB();
return rowID;
}
public int updateTask(Task task) {
ContentValues cv = new ContentValues();
cv.put(TASK_LIST_ID, task.getListId());
cv.put(TASK_NAME, task.getName());
String where = TASK_ID + "= ?";
String[] whereArgs = { String.valueOf(task.getId()) };
this.openWriteableDB();
int rowCount = db.update(TASK_TABLE, cv, where, whereArgs);
this.closeDB();
return rowCount;
}
public int deleteTask(long id) {
String where = TASK_ID + "= ?";
String[] whereArgs = { String.valueOf(id) };
this.openWriteableDB();
int rowCount = db.delete(TASK_TABLE, where, whereArgs);
this.closeDB();
return rowCount;
}
}
这是我的类的一个非常简化的版本，使用我在网上找到的一些代码构建.在这个例子中，我只展示了我的两个表的代码:List 和 Task，以及 Task 表的一些 sql 方法:insertTask、updateTask 和 deleteTask.
This is a very reduced version of my class , built using some code I found on-line. In this example I'm only showing the code for two of my tables : List and Task, and just some of the sql methods for the Task table : insertTask,updateTask, and deleteTask.
即使上面显示的代码有效，我也不认为将所有代码都放在同一个类中，比如说十个表.所以我试着把所有这些代码分成几个类，每个表一个.像这样:
Even though the code shown above works, I don't think it would be nice to have all the code for let's say ten tables all in the same class. So I tried to split all these code into several classes , one for each table. Something like this:
public class MyDatabaseDB {
// database constants
public static final String DB_NAME = "mydatabase.db";
public static final int DB_VERSION = 1;
private static class DBHelper extends SQLiteOpenHelper {
public DBHelper(Context context, String name,
CursorFactory factory, int version) {
super(context, name, factory, version);
}
@Override
public void onCreate(SQLiteDatabase db) {
// create tables
db.execSQL(ListDAL.CREATE_LIST_TABLE);
db.execSQL(TaskDAL.CREATE_TASK_TABLE);
// insert lists
db.execSQL("INSERT INTO list VALUES (1, 'Hobbies')");
db.execSQL("INSERT INTO list VALUES (2, 'Sports')");
// insert sample tasks
db.execSQL("INSERT INTO task VALUES (1, 1, 'Play the guitar')");
db.execSQL("INSERT INTO task VALUES (2, 1, 'Play video games')");
}
@Override
public void onUpgrade(SQLiteDatabase db,
int oldVersion, int newVersion) {
Log.d("Task list", "Upgrading db from version "
+ oldVersion + " to " + newVersion);
db.execSQL(ListDAL.DROP_LIST_TABLE);
db.execSQL(TaskDAL.DROP_TASK_TABLE);
onCreate(db);
}
}
// database object and database helper object
private SQLiteDatabase db;
private DBHelper dbHelper;
// constructor
public MyDatabaseDB(Context context) {
dbHelper = new DBHelper(context, DB_NAME, null, DB_VERSION);
}
// private methods
private void openReadableDB() {
db = dbHelper.getReadableDatabase();
}
private void openWriteableDB() {
db = dbHelper.getWritableDatabase();
}
private void closeDB() {
if (db != null)
db.close();
}
}
这是我创建的两个新类，用于放置与特定表相关的代码:
These are the two new classes, the I created to put the code related to a specific table :
ListDAL 代码不多
The ListDAL doesn't have much code
public class ListDAL {
// list table constants
public static final String LIST_TABLE = "list";
public static final String LIST_ID = "_id";
public static final int LIST_ID_COL = 0;
public static final String LIST_NAME = "list_name";
public static final int LIST_NAME_COL = 1;
// CREATE and DROP TABLE statements
public static final String CREATE_LIST_TABLE =
"CREATE TABLE " + LIST_TABLE + " (" +
LIST_ID + " INTEGER PRIMARY KEY AUTOINCREMENT, " +
LIST_NAME + " TEXT UNIQUE)";
public static final String DROP_LIST_TABLE =
"DROP TABLE IF EXISTS " + LIST_TABLE;
}
TaskDAL 类是包含大部分代码的类，正是在这个类中我遇到了问题，特别是在 insertTask,updateTask 和 deleteTask 中this.openWriteableDB(),this.openWriteableDB() 之类的调用或 db.insert(TASK_TABLE, null, cv) 之类的调用.
The TaskDAL class is the one that contains most of the code, and it is in this class that I have problems, specifically in the insertTask,updateTask and deleteTask with calls like this.openWriteableDB(),this.openWriteableDB() or calls like db.insert(TASK_TABLE, null, cv).
由于这些方法不再在 TaskDAL 中，我无法访问它们.我尝试传递一些对这些方法的引用以代替 this 或 db，但它不起作用
Since these methods are no longer inside TaskDAL, I can't get access to them. I tried passing some references to these methods to be used in place of this or db, but it didn't work
public class TaskDAL {
// task table constants
public static final String TASK_TABLE = "task";
public static final String TASK_ID = "_id";
public static final int TASK_ID_COL = 0;
public static final String TASK_LIST_ID = "list_id";
public static final int TASK_LIST_ID_COL = 1;
public static final String TASK_NAME = "task_name";
public static final int TASK_NAME_COL = 2;
// CREATE and DROP TABLE statements
public static final String CREATE_TASK_TABLE =
"CREATE TABLE " + TASK_TABLE + " (" +
TASK_ID + " INTEGER PRIMARY KEY AUTOINCREMENT, " +
TASK_LIST_ID + " INTEGER, " +
TASK_NAME + " TEXT " +
)";
public static final String DROP_TASK_TABLE =
"DROP TABLE IF EXISTS " + TASK_TABLE;
// public methods
public long insertTask(Task task) {
ContentValues cv = new ContentValues();
cv.put(TASK_LIST_ID, task.getListId());
cv.put(TASK_NAME, task.getName());
this.openWriteableDB();
long rowID = db.insert(TASK_TABLE, null, cv);
this.closeDB();
return rowID;
}
public int updateTask(Task task) {
ContentValues cv = new ContentValues();
cv.put(TASK_LIST_ID, task.getListId());
cv.put(TASK_NAME, task.getName());
String where = TASK_ID + "= ?";
String[] whereArgs = { String.valueOf(task.getId()) };
this.openWriteableDB();
int rowCount = db.update(TASK_TABLE, cv, where, whereArgs);
this.closeDB();
return rowCount;
}
public int deleteTask(long id) {
String where = TASK_ID + "= ?";
String[] whereArgs = { String.valueOf(id) };
this.openWriteableDB();
int rowCount = db.delete(TASK_TABLE, where, whereArgs);
this.closeDB();
return rowCount;
}
}
那么，你们有没有人尝试过类似的东西?如果我设法将 DB 代码正确地分成几个类，我还能加入表吗??
So, have any of you ever tried to so something similar?? If I managed to correctly separate the DB code into several classes, would I be still able to JOIN tables??
附言请不要关闭这个问题，如果您认为我做错了什么，请告诉我，我会尽力改正
P.S. Please do not close this question, if you think I did something wrong please tell me and I'll try to correct it
推荐答案
你认为你能给出一些关于如何去做的提示
do you think you could give some hints on how to do it
这与 Android 没有太大关系，甚至与 Java 也没有太大关系.将较长的编程结构(例如 Java 中的类)分解为较小的结构具有标准技术，称为设计模式，具有特定于语言的实现.
This has nothing much to do with Android, and even not all that much to do with Java. Decomposing long programming structures (e.g., classes in Java) into smaller structures has standard techniques, called design patterns, with language-specific implementations.
例如，您可以使用复合模式:
定义一个接口——我将其称为
TableHelper——它具有onCreate()和onUpdate()与SQLiteOpenHelper
定义 N 个类，每个表一个，实现 TableHelper 接口并提供该表的创建和升级逻辑(以及您希望在这些类上具有的任何其他业务逻辑))
Define N classes, one per table, that implement the TableHelper interface and provide the create and upgrade logic for that table (along with whatever other business logic you want to have on those classes)
让你的 SQLiteOpenHelper 定义一个 TableHelper[] 包含你的 TableHelper 类的实例，并让它委托 onCreate() 和 onUpgrade() 到那些 TableHelper 实例通过迭代数组
Have your SQLiteOpenHelper define a TableHelper[] containing instances of your TableHelper classes, and have it delegate onCreate() and onUpgrade() to those TableHelper instances by iterating over the array
这篇关于如何将一个长的、单一的 SQLiteOpenHelper 分成几个类，每个类一个的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
