java - 如何在 Android 中使用多个数据库帮助程序类在 SQLite 中进行内部联接

Question

我的 java android 应用程序中有 2 个数据库表。

我有 2 个助手，每 table 一个。

public class SyncStudentsHelperextends SQLiteOpenHelper {

    private static final String TAG = "SyncStudentsHelper";

    private static final String TABLE_NAME = "students";
    private static final String COL1 = "field";
    private static final String COL2 = "field";
    private static final String COL3 = "field";
    private static final String COL4 = "field";
    private static final String COL5 = "field";

    public SyncStudentsHelper (Context context) {
        super(context, TABLE_NAME, null, 2);
    }
}    

我有一张类似的教师 table 。

我的问题是如何在两个表之间进行内连接查询。

这是我的尝试。

public Cursor getinfo() {
    SQLiteDatabase db = this.getWritableDatabase();
    String query = "SELECT * FROM students Inner Join teachers ON student.matchingfield = teachers.matchingfield" ;
    Cursor data = db.rawQuery(query, null);
    return data;
}

我收到此错误:

no such table: teachers(code 1): , while compiling: 

我的问题是，因为我有 2 个不同的数据库帮助程序文件，如何从 2 个表进行内部 SQLite 内部联接。

编辑

我的查询是关于学生的助手类，如下所示。

public class SyncStudentsHelperextends SQLiteOpenHelper {

    private static final String TAG = "SyncStudentsHelper";

    private static final String TABLE_NAME = "students";
    private static final String COL1 = "field";
    private static final String COL2 = "field";
    private static final String COL3 = "field";
    private static final String COL4 = "field";
    private static final String COL5 = "field";

    public SyncStudentsHelper (Context context) {
        super(context, TABLE_NAME, null, 2);
    }
}  

//Return Cursor type
public Cursor getinfo() {
    SQLiteDatabase db = this.getWritableDatabase();
    String  attached = "attached_teacher";
    db.execSQL("ATTACH DATABASE  SyncTeachers.teachers AS teachersdatabase ");
     String query = "SELECT * FROM students Inner Join teachersdatabase.teachers ON students.matchingfield = teachersdatabase.teachers.matchingfield " ;
    Cursor data = db.rawQuery(query, null);
    return data;
}

Answer 1

您需要attach无需打开附加数据库即可将其中一个数据库连接到另一个数据库，或者拥有一个包含两个表的数据库。

请注意，您通过给定的架构引用附加的数据库，例如如果使用 ATTACH path_to_2nd_db AS secondarydb 那么您会将附加数据库中的表引用为 seconddb.the_table_name

但是，您拥有两个数据库的原因似乎是在单独的帮助程序中，您使用表名作为数据库名称(基于您的 SyncStudentsHelper 类(类名和 extends 关键字之间缺少空格) ))。

我建议您使用一个 DatabaseHelper，将两个表都放在同一个数据库中。

单个数据库示例

数据库助手:-

public class DatabaseHelper extends SQLiteOpenHelper {

    public static final String DBNAME = "Students_and_Teachers";
    public static final int DBVERSION = 1;

    public static final String TEACHERS_TABLE_NAME = "teachers";
    public static final String STUDENTS_TABLE_NAME = "students";
    public static final String COL1 = "matchingfield";
    public static final String COL2 = "field1";
    public static final String COL3 = "field2";
    public static final String COL4 = "field3";
    public static final String COL5 = "field4";

    public DatabaseHelper(Context context) {
        super(context, DBNAME, null, DBVERSION);
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
        String create_teachers_table_sql =
                "CREATE TABLE IF NOT EXISTS " + TEACHERS_TABLE_NAME +
                        "(" +
                        COL1 + " INTEGER," +
                        COL2 + " TEXT," +
                        COL3 + " TEXT," +
                        COL4 + " TEXT," +
                        COL5 + " TEXT" +
                        ")"
                ;
        String create_students_table_sql =
                "CREATE TABLE IF NOT EXISTS " + STUDENTS_TABLE_NAME +
                        "(" +
                        COL1 + " INTEGER," +
                        COL2 + " TEXT," +
                        COL3 + " TEXT," +
                        COL4 + " TEXT," +
                        COL5 + " TEXT" +
                        ")"
                ;
        db.execSQL(create_teachers_table_sql);
        db.execSQL(create_students_table_sql);
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {

    }

    public long insertStudent(long teacherToMatch, String studentName) {
        ContentValues cv = new ContentValues();
        cv.put(COL1,teacherToMatch);
        cv.put(COL2,studentName);
        return this.getWritableDatabase().insert(STUDENTS_TABLE_NAME,null,cv);
    }

    public long insertTeacher(long teacherToBeMatched, String teacherName) {
        ContentValues cv = new ContentValues();
        cv.put(COL1,teacherToBeMatched);
        cv.put(COL2,teacherName);
        return this.getWritableDatabase().insert(TEACHERS_TABLE_NAME,null,cv);
    }

    public Cursor getInfo() {
        //String query = "SELECT * FROM students Inner Join teachers ON student.matchingfield = teachers.matchingfield" ;
        //Cursor data = db.rawQuery(query, null);
        return this.getWritableDatabase().query(
                STUDENTS_TABLE_NAME + " INNER JOIN " + TEACHERS_TABLE_NAME +
                        " ON " + STUDENTS_TABLE_NAME + "." + COL1 + " = " +
                        TEACHERS_TABLE_NAME + "." + COL1,
                null,null,null,null,null,null
        );
    }
}

使用数据库的 Activity ，添加一些教师 (2) 和一些学生(3 个匹配第一个老师，2 个匹配第二个老师)，然后通过查询提取它们。

public class MainActivity extends AppCompatActivity {
    DatabaseHelper mDB;

    @Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_main);
        mDB = new DatabaseHelper(this);

        mDB.insertTeacher(1,"Mr Jones");
        mDB.insertTeacher(2,"Mrs Smith");

        mDB.insertStudent(1,"Tom Smith");
        mDB.insertStudent(1,"Glenda Burrows");
        mDB.insertStudent(1,"Gloria Jennings");
        mDB.insertStudent(2,"Fred Sharp");
        mDB.insertStudent(2,"Mary Collins");

        Cursor csr = mDB.getInfo();

        int row = 1;
        while (csr.moveToNext()) {
            StringBuilder sb = new StringBuilder();
            sb.append("Processing row ").append(row++);
            for (int i=0; i < csr.getColumnCount(); i++) {
                sb.append("\n\t").append(csr.getColumnName(i)).append(" = " ).append(csr.getString(i));
            }
            Log.d("CURSORINFO",sb.toString());
        }
    }
}

运行上述结果:-

2019-12-11 02:14:15.169 D/CURSORINFO: Processing row 1
        matchingfield = 1
        field1 = Tom Smith
        field2 = null
        field3 = null
        field4 = null
        matchingfield = 1
        field1 = Mr Jones
        field2 = null
        field3 = null
        field4 = null
2019-12-11 02:14:15.169 D/CURSORINFO: Processing row 2
        matchingfield = 1
        field1 = Glenda Burrows
        field2 = null
        field3 = null
        field4 = null
        matchingfield = 1
        field1 = Mr Jones
        field2 = null
        field3 = null
        field4 = null
2019-12-11 02:14:15.169 D/CURSORINFO: Processing row 3
        matchingfield = 1
        field1 = Gloria Jennings
        field2 = null
        field3 = null
        field4 = null
        matchingfield = 1
        field1 = Mr Jones
        field2 = null
        field3 = null
        field4 = null
2019-12-11 02:14:15.170 D/CURSORINFO: Processing row 4
        matchingfield = 2
        field1 = Fred Sharp
        field2 = null
        field3 = null
        field4 = null
        matchingfield = 2
        field1 = Mrs Smith
        field2 = null
        field3 = null
        field4 = null
2019-12-11 02:14:15.170 D/CURSORINFO: Processing row 5
        matchingfield = 2
        field1 = Mary Collins
        field2 = null
        field3 = null
        field4 = null
        matchingfield = 2
        field1 = Mrs Smith
        field2 = null
        field3 = null
        field4 = null

多数据库示例

SyncStudentsHelper 类

public class SyncStudentsHelper extends SQLiteOpenHelper {

    public static final String TABLE_NAME = "students";
    public static final String COL1 = "matchingfield";
    public static final String COL2 = "field1";
    public static final String COL3 = "field2";
    public static final String COL4 = "field3";
    public static final String COL5 = "field4";

    public SyncStudentsHelper(Context context) {
        super(context, TABLE_NAME, null, 2);
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
        String create_students_table_sql =
                "CREATE TABLE IF NOT EXISTS " + TABLE_NAME +
                        "(" +
                        COL1 + " INTEGER," +
                        COL2 + " TEXT," +
                        COL3 + " TEXT," +
                        COL4 + " TEXT," +
                        COL5 + " TEXT" +
                        ")"
                ;
        db.execSQL(create_students_table_sql);

    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {

    }

    public long insertStudent(long teacherToMatch, String studentName) {
        ContentValues cv = new ContentValues();
        cv.put(COL1,teacherToMatch);
        cv.put(COL2,studentName);
        return this.getWritableDatabase().insert(TABLE_NAME,null,cv);
    }
}

SyncTeachersHelper 类:-

public class SyncTeachersHelper extends SQLiteOpenHelper {

    public static final String TABLE_NAME = "teachers";
    public static final String COL1 = "matchingfield";
    public static final String COL2 = "field1";
    public static final String COL3 = "field2";
    public static final String COL4 = "field3";
    public static final String COL5 = "field4";

    public SyncTeachersHelper(@Nullable Context context) {
        super(context, TABLE_NAME, null, 2);
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
        String create_teachers_table_sql =
                "CREATE TABLE IF NOT EXISTS " + TABLE_NAME +
                        "(" +
                        COL1 + " INTEGER," +
                        COL2 + " TEXT," +
                        COL3 + " TEXT," +
                        COL4 + " TEXT," +
                        COL5 + " TEXT" +
                        ")"
                ;
        db.execSQL(create_teachers_table_sql);
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {

    }

    public long insertTeacher(long teacherToBeMatched, String teacherName) {
        ContentValues cv = new ContentValues();
        cv.put(COL1,teacherToBeMatched);
        cv.put(COL2,teacherName);
        return this.getWritableDatabase().insert(TABLE_NAME,null,cv);
    }
}

Activity 代码(单个和多个):-

public class MainActivity extends AppCompatActivity {
    DatabaseHelper mDB;
    SyncTeachersHelper mTDB;
    SyncStudentsHelper mSDB;

    @Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_main);
        mDB = new DatabaseHelper(this);

        mDB.insertTeacher(1,"Mr Jones");
        mDB.insertTeacher(2,"Mrs Smith");

        mDB.insertStudent(1,"Tom Smith");
        mDB.insertStudent(1,"Glenda Burrows");
        mDB.insertStudent(1,"Gloria Jennings");
        mDB.insertStudent(2,"Fred Sharp");
        mDB.insertStudent(2,"Mary Collins");

        Cursor csr = mDB.getInfo();

        int row = 1;
        while (csr.moveToNext()) {
            StringBuilder sb = new StringBuilder();
            sb.append("Processing row ").append(row++);
            for (int i=0; i < csr.getColumnCount(); i++) {
                sb.append("\n\t").append(csr.getColumnName(i)).append(" = " ).append(csr.getString(i));
            }
            Log.d("CURSORINFO",sb.toString());
        }

        Log.d("STAGE2","Now using dual databases");
        // USING Seperate Databases
        mTDB = new SyncTeachersHelper(this);
        mTDB.insertTeacher(1,"Mr Jones");
        mTDB.insertTeacher(2,"Mrs Smith");
        mTDB.close();

        mSDB = new SyncStudentsHelper(this);
        mSDB.insertStudent(1,"Tom Smith");
        mSDB.insertStudent(1,"Glenda Burrows");
        mSDB.insertStudent(1,"Gloria Jennings");
        mSDB.insertStudent(2,"Fred Sharp");
        mSDB.insertStudent(2,"Mary Collins");

        SQLiteDatabase studentsdb = mSDB.getWritableDatabase();
        String  attached = "attached_teacher";
        studentsdb.execSQL("ATTACH DATABASE '" + this.getDatabasePath(SyncTeachersHelper.TABLE_NAME) +"' AS " + attached);

        String fromclause = SyncStudentsHelper.TABLE_NAME + " INNER JOIN " + attached + "." + SyncTeachersHelper.TABLE_NAME +
                " ON " + SyncStudentsHelper.TABLE_NAME + "." + SyncStudentsHelper.COL1 + " = " +
                SyncTeachersHelper.TABLE_NAME + "." + SyncTeachersHelper.COL1;
        Log.d("STAGE2"," From clause for query is \n\t" + fromclause);
        csr = studentsdb.query(
        SyncStudentsHelper.TABLE_NAME + " INNER JOIN " + attached + "." + SyncTeachersHelper.TABLE_NAME +
                " ON " + SyncStudentsHelper.TABLE_NAME + "." + SyncStudentsHelper.COL1 + " = " +
                SyncTeachersHelper.TABLE_NAME + "." + SyncTeachersHelper.COL1,
                null,null,null,null,null,null
        );

        row = 1;
        while (csr.moveToNext()) {
            StringBuilder sb = new StringBuilder();
            sb.append("Processing row ").append(row++);
            for (int i=0; i < csr.getColumnCount(); i++) {
                sb.append("\n\t").append(csr.getColumnName(i)).append(" = " ).append(csr.getString(i));
            }
            Log.d("CURSORINFO",sb.toString());
        }
    }
}

请注意，查询的 FROM 子句(包括 JOIN)是:-

students INNER JOIN attached_teacher.teachers ON students.matchingfield = teachers.matchingfield

结果(STAGE2)是:-

2019-12-11 02:43:59.699 D/STAGE2: Now using dual databases
2019-12-11 02:43:59.773 D/STAGE2:  From clause for query is 
        students INNER JOIN attached_teacher.teachers ON students.matchingfield = teachers.matchingfield
2019-12-11 02:43:59.774 D/CURSORINFO: Processing row 1
        matchingfield = 1
        field1 = Tom Smith
        field2 = null
        field3 = null
        field4 = null
        matchingfield = 1
        field1 = Mr Jones
        field2 = null
        field3 = null
        field4 = null
2019-12-11 02:43:59.774 D/CURSORINFO: Processing row 2
        matchingfield = 1
        field1 = Glenda Burrows
        field2 = null
        field3 = null
        field4 = null
        matchingfield = 1
        field1 = Mr Jones
        field2 = null
        field3 = null
        field4 = null
2019-12-11 02:43:59.774 D/CURSORINFO: Processing row 3
        matchingfield = 1
        field1 = Gloria Jennings
        field2 = null
        field3 = null
        field4 = null
        matchingfield = 1
        field1 = Mr Jones
        field2 = null
        field3 = null
        field4 = null
2019-12-11 02:43:59.775 D/CURSORINFO: Processing row 4
        matchingfield = 2
        field1 = Fred Sharp
        field2 = null
        field3 = null
        field4 = null
        matchingfield = 2
        field1 = Mrs Smith
        field2 = null
        field3 = null
        field4 = null
2019-12-11 02:43:59.775 D/CURSORINFO: Processing row 5
        matchingfield = 2
        field1 = Mary Collins
        field2 = null
        field3 = null
        field4 = null
        matchingfield = 2
        field1 = Mrs Smith
        field2 = null
        field3 = null
        field4 = null