为什么我的内部联接查询没有返回任何内容？

Question

我有两张桌子锻炼和练习。我正在尝试使用与我们点击的锻炼匹配的workout_id获取所有锻炼。我做了一个内部联接查询，但它似乎没有返回任何东西。我的查询有问题吗？

我正在使用SQLite创建我的数据库。我已经检查了，以确保练习表中有练习，并且它们有一个workout_id。

    @Nullable
@Override
public Cursor query(@NonNull Uri uri, @Nullable String[] projection, @Nullable String selection, @Nullable String[] selectionArgs, @Nullable String sortOrder) {
    SQLiteDatabase db = dbHelper.getReadableDatabase();
    Cursor cursor = null;
    int match = uriMatcher.match(uri);
    switch (match){
        case WORKOUT:
            cursor = db.query(WorkoutContract.WorkoutEntry.TABLE_NAME_WORKOUT,projection,selection,selectionArgs,null,null,sortOrder);
            break;
        case WORKOUT_ID:
            selection = WorkoutContract.WorkoutEntry._ID + "=?";
            selectionArgs = new String[]{String.valueOf(ContentUris.parseId(uri))};
            cursor = db.query(WorkoutContract.WorkoutEntry.TABLE_NAME_WORKOUT,projection,selection,selectionArgs,null,null,sortOrder);
            break;
        case EXERCISE:
            cursor = db.query(WorkoutContract.WorkoutEntry.TABLE_NAME_EXERCISES,projection,selection,selectionArgs,null,null,sortOrder);
            break;
        case EXERCISE_ID:
            selection = WorkoutContract.WorkoutEntry._ID + "=?";
            selectionArgs = new String[]{String.valueOf(ContentUris.parseId(uri))};
            cursor = db.query(WorkoutContract.WorkoutEntry.TABLE_NAME_EXERCISES,projection,selection,selectionArgs,null,null,sortOrder);
            break;
        case WORkOUT_EXERCISE:
            final String QUERY = "SELECT workouts._id, " +
                    "exercise_name," +
                    "weight," +
                    "reps," +
                    "rpe"+
                    " FROM "+
                    "exercises"+
                    " INNER JOIN workouts ON workouts._id = exercises.workout_id"+
                    " WHERE workouts._id=1";
           cursor = db.rawQuery(QUERY, null);
            break;

        default:
            throw new IllegalArgumentException("Failed to retrieve" + uri);
    }
    cursor.setNotificationUri(getContext().getContentResolver(),uri);
    int rowcount = cursor.getCount();
    Log.d("ROWCOUNT","Number of rows = " + String.valueOf(rowcount));
    return cursor;    }

String CREATE_TABLE_WORKOUTS = "CREATE TABLE " + WorkoutContract.WorkoutEntry.TABLE_NAME_WORKOUT + " ("
            + WorkoutContract.WorkoutEntry._ID + " INTEGER PRIMARY KEY, "
            + WorkoutContract.WorkoutEntry.WORKOUT_TITLE + " STRING, "
            + WorkoutContract.WorkoutEntry.WORKOUT_DATE + " STRING" + ")";

    String CREATE_TABLE_EXERCISES = "CREATE TABLE " + WorkoutContract.WorkoutEntry.TABLE_NAME_EXERCISES + " ("
            + WorkoutContract.WorkoutEntry._ID + " INTEGER PRIMARY KEY, "
            + WorkoutContract.WorkoutEntry.EXERCISE_NAME  + " STRING, "
            + WorkoutContract.WorkoutEntry.REPS + " INTEGER, "
            + WorkoutContract.WorkoutEntry.RPE + " INTEGER, "
            + WorkoutContract.WorkoutEntry.WEIGHT + " INTEGER, "
            + WorkoutContract.WorkoutEntry.WORKOUT_ID + " INTEGER, "
            + "FOREIGN KEY"+"("+ WorkoutContract.WorkoutEntry.WORKOUT_ID+")" + " REFERENCES "
            + WorkoutContract.WorkoutEntry.TABLE_NAME_WORKOUT
            +"("
            +(WorkoutContract.WorkoutEntry._ID) +")" +")";

Uri uri = withAppendedId(WorkoutContract.WorkoutEntry.CONTENT_URI, id);

Uri uri = withAppendedId(WorkoutContract.WorkoutEntry.JOIN_TABLE_URI, id);

2）回到我的WorkoutProvider类中，我将selectionArgs设置为：

 selectionArgs = new String[]{String.valueOf(ContentUris.parseId(uri))};

并把它传递到我的RawQuery中。

cursor = db.rawQuery(QUERY, selectionArgs);

Answer 1

您的问题是，您实际上是在说....其中workouts._id='_id'因为您有selectionargs=new string[]{string.valueof(workoutcontract.workoutentry.workout_id)}；

workoutContract.workoutentry.workout_id是列名_id。

因此，_id列必须是一个整数（在java中是长的，因为它最多是64位有符号整数），因为该列是rowid的别名，所以永远不会与WHERE子句匹配。

相反，你需要像这样的东西

selectionargs=new string[]{string.valueof(the_id_that_you_are_looking_for)}；

• _id_that_you_are_looking_for将是一个long（可以是int，但应该是long)，它是从调用查询的地方传递/获取的，例如在活动中。

    case WORkOUT_EXERCISE:

        String table = WorkoutContract.WorkoutEntry.TABLE_NAME_EXERCISES +
            " INNER JOIN " + WorkoutContract.WorkoutEntry.TABLE_NAME_WORKOUT +
            " ON " + WorkoutContract.WorkoutEntry.TABLE_NAME_WORKOUT + "." +
            WorkoutContract.WorkoutEntry._ID + " = " +
            WorkoutContract.WorkoutEntry.TABLE_NAME_EXERCISES + "." +
            WorkoutContract.WorkoutEntry.WORKOUT_ID;


        String[] columns = new String[]{
            WorkoutContract.WorkoutEntry.EXERCISE_NAME,
            WorkoutContract.WorkoutEntry.WEIGHT,
            WorkoutContract.WorkoutEntry.REPS,
            WorkoutContract.WorkoutEntry.RPE               
        };

        String whereclause = WorkoutContract.WorkoutEntry._ID + " =?";

        cursor = db.query(table,columns,whereclause,selectionArgs,null,null,null);
        break;

null

    case WORkOUT_EXERCISE:
        final String QUERY = "SELECT workouts._id, " +
                "exercise_name," +
                "weight," +
                "reps," +
                "rpe"+
                " FROM "+
                "exercises"+
                " INNER JOIN workouts ON workouts._id = exercises.workout_id"+
                " WHERE workouts._id=?";
       cursor = db.rawQuery(QUERY, selectionArgs);
        break;

• 这假定selectionArgs只是传递的id。
• 与前一种方法相比，这种方法的适应性较差。