我如何连接两个表的SQLite在我的Andr​​oid应用程序？

问题描述

我有了两个表的数据库的Andr​​oid项目： tbl_question 和 tbl_alternative

I have an Android project that has a database with two tables: tbl_question and tbl_alternative.

要填充的问题和替代我使用游标的意见。有在得到我需要的数据，直到我试图连接两个表没有问题。

To populate the views with questions and alternatives I am using cursors. There are no problems in getting the data I need until I try to join the two tables.

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

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

我想类似如下：

I want something like the following:

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困难，但已经得到了用这种方式，因为它是不太容易出错的意见。

I find this way to form queries harder than regular SQL, but have gotten the advice to use this way since it is less error prone.

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

How do I join two SQLite tables in my application?

推荐答案

您需要的rawQuery方法。

例如：

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)});

使用？绑定，而不是把值转换成原始的SQL查询。

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

这篇关于我如何连接两个表的SQLite在我的Andr​​oid应用程序？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！