你能帮我解决sqlite中两个表之间的关系问题吗?
我已经完成了插入、删除和更新步骤,但现在我需要支持两个表之间的关系。我猜以前完成的所有代码步骤都会改变,对吗?你有没有任何链接或例子可以解释表之间的关系以及关系后的任何活动?
CREATE TABLE customer(
id INTEGER,
firstName TEXT,
middleName TEXT,
lastName TEXT,
address TEXT,
contactNum TEXT
);
CREATE TABLE item(
id INTEGER,
name TEXT,
description TEXT
);
CREATE TABLE order(
id INTEGER,
customerID INTEGER,
date TEXT,
FOREIGN KEY(customerId) REFERENCES customer(id)
);
CREATE TABLE item_order(
id INTEGER,
orderID INTEGER,
itemId INTEGER,
quantity INTEGER,
FOREIGN KEY(orderId) REFERENCES order(Id),
FOREIGN KEY(itemId) REFERENCES item(Id)
);
优秀的示例 http://www.androidhive.info/2013/09/android-sqlite-database-with-multiple-tables/
这个示例展示了如何在Android应用中使用SQLite数据库,并创建多个表格。它包含完整的代码和详细的解释,可以帮助开发人员快速学习和实现。// Table Create Statements
// Todo table create statement
private static final String CREATE_TABLE_TODO = "CREATE TABLE "
+ TABLE_TODO + "(" + KEY_ID + " INTEGER PRIMARY KEY," + KEY_TODO
+ " TEXT," + KEY_STATUS + " INTEGER," + KEY_CREATED_AT
+ " DATETIME" + ")";
// Tag table create statement
private static final String CREATE_TABLE_TAG = "CREATE TABLE " + TABLE_TAG
+ "(" + KEY_ID + " INTEGER PRIMARY KEY," + KEY_TAG_NAME + " TEXT,"
+ KEY_CREATED_AT + " DATETIME" + ")";
// todo_tag table create statement
private static final String CREATE_TABLE_TODO_TAG = "CREATE TABLE "
+ TABLE_TODO_TAG + "(" + KEY_ID + " INTEGER PRIMARY KEY,"
+ KEY_TODO_ID + " INTEGER," + KEY_TAG_ID + " INTEGER,"
+ KEY_CREATED_AT + " DATETIME" + ")";
选择所有的待办事项(todos),标签(tags)和待办事项与标签之间的关系(todo_tags),其中标签名称为“Watchlist”,且标签ID等于待办事项与标签之间关系的标签ID,待办事项ID等于待办事项与标签之间关系的待办事项ID。
/*
* getting all todos under single tag
* */
public List<Todo> getAllToDosByTag(String tag_name) {
List<Todo> todos = new ArrayList<Todo>();
String selectQuery = "SELECT * FROM " + TABLE_TODO + " td, "
+ TABLE_TAG + " tg, " + TABLE_TODO_TAG + " tt WHERE tg."
+ KEY_TAG_NAME + " = '" + tag_name + "'" + " AND tg." + KEY_ID
+ " = " + "tt." + KEY_TAG_ID + " AND td." + KEY_ID + " = "
+ "tt." + KEY_TODO_ID;
Log.e(LOG, selectQuery);
SQLiteDatabase db = this.getReadableDatabase();
Cursor c = db.rawQuery(selectQuery, null);
// looping through all rows and adding to list
if (c.moveToFirst()) {
do {
Todo td = new Todo();
td.setId(c.getInt((c.getColumnIndex(KEY_ID))));
td.setNote((c.getString(c.getColumnIndex(KEY_TODO))));
td.setCreatedAt(c.getString(c.getColumnIndex(KEY_CREATED_AT)));
// adding to todo list
todos.add(td);
} while (c.moveToNext());
}
return todos;
}