如何在我的 Android 应用程序中加入两个 SQLite 表? [英] How do I join two SQLite tables in my Android application?

问题描述

背景

我有一个 Android 项目，它有一个包含两个表的数据库:tbl_question 和 tbl_alternative.

为了用问题和替代方案填充视图，我使用了游标.在我尝试连接两个表之前，获取我需要的数据没有问题.

<前>表_问题-------------_ID题类别编号

<前>tbl_alternative---------------_ID问题编号类别编号选择

我想要如下内容:

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

这是我的尝试:

public Cursor getAlternative(long categoryid) {String[] 列 = new String[] { KEY_Q_ID, KEY_IMAGE, KEY_QUESTION, KEY_ALT, KEY_QID};字符串 whereClause = KEY_CATEGORYID + =";+ 类别 ID +"AND + KEY_Q_ID +"="+ KEY_QID；Cursor cursor = mDb.query(true, DBTABLE_QUESTION + " INNER JOIN "+ DBTABLE_ALTERNATIVE, columns, whereClause, null, null, null, null, null);如果(光标！= null){cursor.moveToFirst();}返回游标；

我发现这种方式比常规 SQL 更难形成查询，但我建议使用这种方式，因为它不太容易出错.

问题

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

解决方案

您需要 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 查询中.

Background

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.` 

This is my attempt:

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;

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.

Question

How do I join two SQLite tables in my application?

解决方案

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.

这篇关于如何在我的 Android 应用程序中加入两个 SQLite 表?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！