如何将一个长而单一的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;
}
}
那么，你们中有没有人尝试过类似的事情? 如果我设法将数据库代码正确地分成几个类，我仍然可以联接表吗?
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.请不要关闭此问题，如果您认为我做错了什么，请告诉我，我将尝试予以纠正
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-具有与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屋！
