java - 为什么我的 SQL 查询没有从 sqlite 数据库中选择任何行

Question

在我的数据库中，我有两个表“classes”和“classes_sessions”，我创建了一个链接这两个表的外键。我正在尝试使用下面的查询从这些表中检索数据

代码

String selectQuery = "SELECT * FROM " +
            TABLE_CLASSES +
            " INNER JOIN " + TABLE_CLASSES_SECTIONS +
            " ON " +
            TABLE_CLASSES_SECTIONS + "." + COLUMN_CLASSES_SECTIONS_ID +
            " = " + TABLE_CLASSES + "." + COLUMN_CLASSES_SECTIONS +
            " WHERE " +
            TABLE_CLASSES + "." + COLUMN_CLASSES_ID +
            " = " + String.valueOf(id); 

在 getAllSectionsByClassesID()下面的方法

 public ArrayList<SectionsBean> getAllSectionsByClassesID(long id){

    String selectQuery = "SELECT * FROM " +
            TABLE_CLASSES +
            " INNER JOIN " + TABLE_CLASSES_SECTIONS +
            " ON " +
            TABLE_CLASSES_SECTIONS + "." + COLUMN_CLASSES_SECTIONS_ID +
            " = " + TABLE_CLASSES + "." + COLUMN_CLASSES_SECTIONS +
            " WHERE " +
            TABLE_CLASSES + "." + COLUMN_CLASSES_ID +
            " = " + String.valueOf(id);

    SQLiteDatabase db = this.getReadableDatabase();

    ArrayList<SectionsBean> sectionsBeanList = new ArrayList<SectionsBean>();

   Cursor cursor = db.rawQuery(selectQuery,null);


    Log.i("Query details", String.valueOf(cursor));
    Log.d("DataDetails", DatabaseUtils.dumpCursorToString(cursor));

    while (cursor.moveToNext()) {


        ClassesBean classesBean = new ClassesBean();
        classesBean.setId(cursor.getLong(cursor.getColumnIndex(COLUMN_CLASSES_ID)));
        classesBean.setClasses_name(cursor.getString(cursor.getColumnIndex(COLUMN_CLASSES_NAME)));


        SectionsBean sectionsBean = new SectionsBean();
        sectionsBean.setSectionsID(cursor.getLong(cursor.getColumnIndex(COLUMN_CLASSES_SECTIONS_ID)));
        sectionsBean.setSections_name(cursor.getString(cursor.getColumnIndex(COLUMN_CLASSES_SECTIONS_NAME)));



        sectionsBean.setClassesBean(classesBean);
        sectionsBeanList.add(sectionsBean);

    }
    return sectionsBeanList;

}

但它不返回任何东西。我正在使用该行检查数据库中游标返回的数据 Log.d("DataDetails", DatabaseUtils.dumpCursorToString(cursor));结果是空白的。两个表都有内容，如下图

数据库内容

类表

-05 22:06:23.728 31258-31310/com.example.demeainc.demea D/DataC: >>>>> Dumping cursor android.database.sqlite.SQLiteCursor@88a8264
                                                                     0 {
                                                                        classes_id=1
                                                                        class_item_index=null
                                                                        classes_name=jss1
                                                                        classes_codename=null
                                                                        classes_sections_id=null
                                                                        classes_teachers=null
                                                                        classes_students=null
                                                                     }
                                                                     1 {
                                                                        classes_id=2
                                                                        class_item_index=null
                                                                        classes_name=villa
                                                                        classes_codename=null
                                                                        classes_sections_id=null
                                                                        classes_teachers=null
                                                                        classes_students=null
                                                                     }
                                                                     2 {
                                                                        classes_id=3
                                                                        class_item_index=null
                                                                        classes_name=two
                                                                        classes_codename=null
                                                                        classes_sections_id=null
                                                                        classes_teachers=null
                                                                        classes_students=null
                                                                     }
                                                                     <<<<<

session 表的数据库内容

03-05 22:09:03.943 31258-31258/com.example.demeainc.demea D/DataS: >>>>> Dumping cursor android.database.sqlite.SQLiteCursor@eaadf23
                                                                     0 {
                                                                        classes_sections_ids=1
                                                                        classes_sections_name=cooolanet
                                                                        classes_sections_description=bbdbn
                                                                     }
                                                                     1 {
                                                                        classes_sections_ids=2
                                                                        classes_sections_name=morrals
                                                                        classes_sections_description=mills
                                                                     }
                                                                     2 {
                                                                        classes_sections_ids=3
                                                                        classes_sections_name=live
                                                                        classes_sections_description=bxn
                                                                     }
                                                                     3 {
                                                                        classes_sections_ids=4
                                                                        classes_sections_name=testing2
                                                                        classes_sections_description=coll
                                                                     }
                                                                     4 {
                                                                        classes_sections_ids=5
                                                                        classes_sections_name=tool
                                                                        classes_sections_description=vi
                                                                     }
                                                                     5 {
                                                                        classes_sections_ids=6
                                                                        classes_sections_name=colls
                                                                        classes_sections_description=
                                                                     }
                                                                     6 {
                                                                        classes_sections_ids=7
                                                                        classes_sections_name=more
                                                                        classes_sections_description=coll
                                                                     }
                                                                     7 {
                                                                        classes_sections_ids=8
                                                                        classes_sections_name=testing
                                                                        classes_sections_description=ttt
                                                                     }
                                                                     8 {
                                                                        classes_sections_ids=9
                                                                        classes_sections_name=threevill
                                                                        classes_sections_description=cool
                                                                     }
                                                                     <<<<<

有关表创建的更多详细信息。

// create classes_table sql query
private String CREATE_CLASSES_TABLE = "CREATE TABLE IF NOT EXISTS " + TABLE_CLASSES + "("
        + COLUMN_CLASSES_ID + " INTEGER PRIMARY KEY AUTOINCREMENT," + COLUMN_CLASS_ITEM_INDEX + " NUMBER,"
        + COLUMN_CLASSES_NAME + " VARCHAR," + COLUMN_CLASSES_CODENAME + " VARCHAR, " + COLUMN_CLASSES_SECTIONS + " INTEGER," + COLUMN_CLASSES_TEACHERS
        + " VARCHAR," + COLUMN_CLASSES_STUDENTS + " VARCHAR,"
        + "FOREIGN KEY(" + COLUMN_CLASSES_SECTIONS + ") REFERENCES " + TABLE_CLASSES_SECTIONS  + "(" + COLUMN_CLASSES_SECTIONS_ID + ")  ON DELETE CASCADE  " + ");";

//create sections sql query
private String CREATE_CLASSES_SECTIONS_TABLE =  "CREATE TABLE IF NOT EXISTS " + TABLE_CLASSES_SECTIONS + "("
        + COLUMN_CLASSES_SECTIONS_ID + " INTEGER PRIMARY KEY AUTOINCREMENT," + COLUMN_CLASSES_SECTIONS_NAME + " VARCHAR,"
        + COLUMN_CLASSES_SECTIONS_DESCRIPTION + " VARCHAR" + ")";

可能是什么问题。外键实际上是链接两个表。我在上面的代码中有什么问题。

Answer 1

您的问题是 class_sections_id column 为空，因此 classes_sections_table( session 表)中永远没有匹配的条目，因此没有进行 JOIN，因此没有任何显示。
以下面的 为例说明其工作原理。类(class)表 :-

现在 classes_sections table :-

对于英语，您会看到 classes_sections_id 的值为 1 这可以对应(又名关联/引用等)值为 的 classes_sections_ids 列1 .
查询

SELECT * FROM classes 
    INNER JOIN classes_sections 
        ON classes_sections.classes_sections_ids = classes.classes_sections_id 
    WHERE classes.classes_id =1

会导致:-

但是，如果在 classes 表中添加了以下行( 请注意对 classes_sections_id 列的引用的值为 700，并且 classes_sections 表中没有这样的行 ):-

如果查询被更改为获取 id 4 则不会返回任何行，因为即使 中确实存在 id 4(化学)类(class) 表没有关联 classes_sections 行(带有 classes_sections_ids 作为 700 )，因此没有 JOIN ，因此没有要返回的行。
简而言之，您需要拥有 section_id 列引用/关联/链接到 classes_sections_ids 列来检索任何数据。

Is the foreign key actually Linking the two tables.

指定 FOREIGN KEY 仅指定存在约束(注意 ON DELETE CASCADE 限制了约束)。它不会自动定义链接，您必须这样做。
您可能会以编程方式执行此操作(我手动制作了上述链接)，例如添加类时，您将选择一个可用部分，可能通过微调器(也称为下拉选择器)呈现。然后，您插入具有所选部分 id 的类。

额外的
我相信您最终会遇到设计问题，主要是由于您的引用/链接方式。
例如，您的设计有一个类表，其中包含一个链接到部分表的列。这没有引入在单个列中处理链接列表的复杂性，从而将类(class)限制为只有一个部分(教师和学生同样适用)。
基于一个类(class)可能有多个部分的假设，那么还有一个进一步的考虑，一个部分是否可以被许多类(class)使用(一个例子可能是所有类(class)都必须从疏散程序开始)。
如果后者不适用，那么类和部分之间的关​​系可能是一对多(或者可以通过多对多关系处理)。

在这种情况下，一个部分可以有一个列作为类的链接。

如果后者适用，那么类和部分之间的关​​系将/应该是多对多关系。

在这种情况下，将使用链接表(也称为关联表、引用表、映射表......)。

这样的表至少有两列，每列都包含指向相关表的链接(组合应该/应该是唯一的)。

因此，我建议您考虑以下设计(可能会更多):-

DROP TABLE IF EXISTS classes;
CREATE TABLE IF NOT EXISTS classes ( class_id INTEGER PRIMARY KEY, class_name TEXT, class_codename TEXT );
DROP TABLE IF EXISTS sections;
CREATE TABLE IF NOT EXISTS sections ( section_id INTEGER PRIMARY KEY, section_name TEXT, section_description TEXT);
DROP TABLE IF EXISTS teachers;
CREATE TABLE IF NOT EXISTS teachers ( teacher_id INTEGER PRIMARY KEY, teacher_name TEXT);
DROP TABLE IF EXISTS students;
CREATE TABLE IF NOT EXISTS students ( student_id INTEGER PRIMARY KEY, student_name TEXT);
-- LINK TABLES 
-- Note usess column constraints to define foreign keys i.e. REFERENCES
DROP TABLE IF EXISTS class_section_links;
CREATE TABLE IF NOT EXISTS class_section_links (
    class_link INTEGER NOT NULL REFERENCES classes (class_id), 
    section_link INTEGER NOT NULL REFERENCES sections (section_id), 
    PRIMARY KEY (class_link, section_link));
DROP TABLE IF EXISTS class_teacher_links;
CREATE TABLE IF NOT EXISTS class_teacher_links (
    class_link INTEGER NOT NULL REFERENCES classes (class_id), 
    teacher_link INTEGER NOT NULL REFERENCES teachers (teacher_id), 
    PRIMARY KEY (class_link, teacher_link));
DROP TABLE IF EXISTS class_student_links;
CREATE TABLE IF NOT EXISTS class_student_links (
    class_link INTEGER NOT NULL REFERENCES classes (class_id),
    student_link INTEGER NOT NULL REFERENCES students (student_id),
    PRIMARY KEY (class_link, student_link));

这将加载一些数据，包括类和部分之间的一些基本链接:-

-- LOAD some data
-- CLASSES
INSERT INTO classes (class_name, class_codename) VALUES('English Language','EL100');
INSERT INTO classes (class_name, class_codename) VALUES('English Literature','EL101');
INSERT INTO classes (class_name, class_codename) VALUES('Applied Mathermatics','MA200');
INSERT INTO classes (class_name, class_codename) VALUES('Pure Mathematics','MA201');
INSERT INTO classes (class_name, class_codename) VALUES('Chemistry','SC300');
INSERT INTO classes (class_name, class_codename) VALUES('Physics','SC301');
INSERT INTO classes (class_name, class_codename) VALUES('Biology','SC302');
INSERT INTO classes (class_name, class_codename) VALUES('GEOGRAPHY','GE400');
-- SECTIONS
INSERT INTO sections (section_name, section_description) VALUES('Class Introduction','Evacuation procedures, amenities etc..');
INSERT INTO sections (section_name, section_description) VALUES('Sentence Construction','Blah');
INSERT INTO sections (section_name, section_description) VALUES('Word types','Basic word types such as VERB, ADJECTIVE etc');
INSERT INTO sections (section_name, section_description) VALUES('Puntuation','Blah');
INSERT INTO sections (section_name, section_description) VALUES('Under Milk Wood','Blah');
INSERT INTO sections (section_name, section_description) VALUES('Catcher in the Rye','Blah');
INSERT INTO sections (section_name, section_description) VALUES('The War of the Worlds','Blah');
-- CLASS SECTION LINKS (note assumes ID's of classes/sections in order 1,2,3......)
-- a) All classes have Class Introduction
INSERT INTO class_section_links VALUES(1,1); -- Class 1 English language
INSERT INTO class_section_links VALUES(2,1); -- Class 2 English Lit
INSERT INTO class_section_links VALUES(3,1);
INSERT INTO class_section_links VALUES(4,1);
INSERT INTO class_section_links VALUES(5,1);
INSERT INTO class_section_links VALUES(6,1);
INSERT INTO class_section_links VALUES(7,1);
INSERT INTO class_section_links VALUES(8,1);
-- b) specific sections
INSERT INTO class_section_links VALUES(1,2); -- Class 1 has section 2
INSERT INTO class_section_links VALUES(1,3); -- Class 1 has section 3
INSERT INTO class_section_links VALUES(2,4);
INSERT INTO class_section_links VALUES(2,5);
INSERT INTO class_section_links VALUES(2,6);
INSERT INTO class_section_links VALUES(2,7);

查询，例如:-

SELECT class_name, class_codename, section_name, section_description 
FROM class_section_links 
    JOIN classes ON class_link = class_id 
    JOIN sections ON section_link = section_id 
ORDER BY class_name, section_name;

会导致:-

如果你看 类(class)介绍您会看到只有一个部分被多次使用，因此只需要一组数据。因此，如果有一个指令而不是 类(class)介绍改为 简介 那么单个更改将更新所有类。
例如使用以下内容执行更新:-

UPDATE sections SET section_name = 'Introduction' WHERE section_name = 'Class Introduction';

并运行相同的查询结果:-