如何在 Android 应用程序中连接两个 SQLite 表？

背景资料

我有一个 Android 项目，有一个数据库与两个表: tbl_question和 tbl_alternative。

为了用问题和备选方案填充视图，我使用了游标。在尝试连接这两个表之前，获取所需的数据没有任何问题。

Tbl_question
-------------
_id
question
categoryid

Tbl_alternative
---------------
_id
questionid
categoryid
alternative

我想要这样的东西:

SELECT tbl_question.question, tbl_alternative.alternative where
categoryid=tbl_alternative.categoryid AND tbl_question._id =
tbl_alternative.questionid.`

这是我的尝试:

public Cursor getAlternative(long categoryid) {
String[] columns = new String[] { KEY_Q_ID, KEY_IMAGE, KEY_QUESTION, KEY_ALT, KEY_QID};
String whereClause = KEY_CATEGORYID + "=" + categoryid +" AND "+ KEY_Q_ID +"="+ KEY_QID;
Cursor cursor = mDb.query(true, DBTABLE_QUESTION + " INNER JOIN "+ DBTABLE_ALTERNATIVE, columns, whereClause, null, null, null, null, null);
if (cursor != null) {
cursor.moveToFirst();
}
return cursor;

我发现这种方式比常规 SQL 更难以形成查询，但是我得到了使用这种方式的建议，因为它更少出错。

提问

如何在应用程序中连接两个 SQLite 表？

148950 次浏览
小开
最佳答案

You need rawQuery method.

Example:

private final String MY_QUERY = "SELECT * FROM table_a a INNER JOIN table_b b ON a.id=b.other_id WHERE b.property_id=?";


db.rawQuery(MY_QUERY, new String[]{String.valueOf(propertyId)});

Use ? bindings instead of putting values into raw sql query.

小开

"Ambiguous column" usually means that the same column name appears in at least two tables; the database engine can't tell which one you want. Use full table names or table aliases to remove the ambiguity.

Here's an example I happened to have in my editor. It's from someone else's problem, but should make sense anyway.

select P.*
from product_has_image P
inner join highest_priority_images H
on (H.id_product = P.id_product and H.priority = p.priority)
小开

An alternate way is to construct a view which is then queried just like a table. In many database managers using a view can result in better performance.

CREATE VIEW xyz SELECT q.question, a.alternative
FROM tbl_question AS q, tbl_alternative AS a
WHERE q.categoryid = a.categoryid
AND q._id = a.questionid;

This is from memory so there may be some syntactic issues. http://www.sqlite.org/lang_createview.html

I mention this approach because then you can use SQLiteQueryBuilder with the view as you implied that it was preferred.

小开

In addition to @pawelzieba's answer, which definitely is correct, to join two tables, while you can use an INNER JOIN like this 

SELECT * FROM expense INNER JOIN refuel
ON exp_id = expense_id
WHERE refuel_id = 1

via raw query like this - 

String rawQuery = "SELECT * FROM " + RefuelTable.TABLE_NAME + " INNER JOIN " + ExpenseTable.TABLE_NAME
+ " ON " + RefuelTable.EXP_ID + " = " + ExpenseTable.ID
+ " WHERE " + RefuelTable.ID + " = " +  id;
Cursor c = db.rawQuery(
rawQuery,
null
);

because of SQLite's backward compatible support of the primitive way of querying, we turn that command into this - 

SELECT *
FROM expense, refuel
WHERE exp_id = expense_id AND refuel_id = 1

and hence be able to take advanatage of the SQLiteDatabase.query() helper method 

Cursor c = db.query(
RefuelTable.TABLE_NAME + " , " + ExpenseTable.TABLE_NAME,
Utils.concat(RefuelTable.PROJECTION, ExpenseTable.PROJECTION),
RefuelTable.EXP_ID + " = " + ExpenseTable.ID + " AND " + RefuelTable.ID + " = " +  id,
null,
null,
null,
null
);

For a detailed blog post check this http://blog.championswimmer.in/2015/12/doing-a-table-join-in-android-without-using-rawquery


提交答案