java - 非法参数异常 : Invalid column DISTINCT bucket_display_name

Question

我正在检索包含视频文件的不同文件夹列表列表，每个文件夹中包含视频数量，这在搭载 Android P 及以下版本的设备上运行良好，但当我在搭载 Android Q 的设备上运行时，应用程序崩溃。如何让它适用于运行 Android Q 的设备

java.lang.IllegalArgumentException: Invalid column DISTINCT bucket_display_name

日志:

java.lang.IllegalArgumentException: Invalid column DISTINCT bucket_display_name
        at android.database.DatabaseUtils.readExceptionFromParcel(DatabaseUtils.java:170)
        at android.database.DatabaseUtils.readExceptionFromParcel(DatabaseUtils.java:140)
        at android.content.ContentProviderProxy.query(ContentProviderNative.java:423)
        at android.content.ContentResolver.query(ContentResolver.java:944)
        at android.content.ContentResolver.query(ContentResolver.java:880)
        at android.content.ContentResolver.query(ContentResolver.java:836)
        at com.aisar.mediaplayer.fragments.VideoFolderFragment$MediaQuery.getAllVideo(VideoFolderFragment.java:364)
        at com.aisar.mediaplayer.fragments.VideoFolderFragment$VideosLoader.loadVideos(VideoFolderFragment.java:434)
        at com.aisar.mediaplayer.fragments.VideoFolderFragment$VideosLoader.access$1100(VideoFolderFragment.java:413)
        at com.aisar.mediaplayer.fragments.VideoFolderFragment$5.run(VideoFolderFragment.java:189)
        at android.os.AsyncTask$SerialExecutor$1.run(AsyncTask.java:289)
        at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1167)
        at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:641)
        at java.lang.Thread.run(Thread.java:919)

我的代码:

public class MediaQuery {
        private Context context;
        private int count = 0;
        private Cursor cursor;
        List<ModelVideoFolder> videoItems;

        public MediaQuery(Context context) {
            this.context = context;
        }

        public List<ModelVideoFolder> getAllVideo(String query) {
            String selection = null;
            String[] projection = {
                    "DISTINCT " + MediaStore.Video.Media.BUCKET_DISPLAY_NAME,
                    MediaStore.Video.Media.BUCKET_ID
            };
            cursor = context.getContentResolver().query(
                    MediaStore.Video.Media.EXTERNAL_CONTENT_URI,
                    projection,
                    selection,
                    null,
                    query);
            videoItems = new ArrayList<>();
            ModelVideoFolder videoItem;
            while (cursor.moveToNext()) {
                videoItem = new ModelVideoFolder(
                        "" + cursor.getString(1),
                        "" + cursor.getString(0),
                        "",
                        "",
                        "" + getVideosCount(cursor.getString(1))
                );
                videoItems.add(videoItem);
            }
            return videoItems;
        }

        public int getVideosCount(String BUCKET_ID) {
            int count = 0;
            String selection = null;
            String[] projection = {
                    MediaStore.Video.Media.BUCKET_ID,
            };
            Cursor cursor = getActivity().getContentResolver().query(
                    MediaStore.Video.Media.EXTERNAL_CONTENT_URI,
                    projection,
                    selection,
                    null,
                    null);
            while (cursor.moveToNext()) {
                if (BUCKET_ID.equals(cursor.getString(0))) {
                    //add only those videos that are in selected/chosen folder
                    count++;
                }
            }
            return count;
        }
    }

Answer 1

这是由于 Android Q 的限制。

在 Android Q 中，投影必须只包含有效的列名，而没有附加语句。不可能再在投影中嵌入任何类型的 SQL 语句。

因此，诸如 "DISTINCT "+ YourColumName 之类的投影，或者甚至尝试创建诸如 "ExistingColumnName AS AnotherName"之类的列别名都将始终失败。

解决方法是执行多个查询(游标)以获取所需的指标，并使用结果构建 CursorWrapper或 MatrixCursor .

请参阅下一个问题链接，其中说明此行为符合预期，因为它是 Q 中改进的存储安全模型的一部分:

https://issuetracker.google.com/issues/130965914

对于您的具体问题，解决方案如下:

1. 先查询游标，获取所有视频所在的BUCKET_ID值列表。在选择中，您可以使用 MediaStore.Files.FileColumns.MEDIA_TYPE = MediaStore.Files.FileColumns.MEDIA_TYPE_VIDEO 过滤以仅定位视频文件

2. 使用检索到的游标，迭代所有 BUCKET_ID 值以对每个存储桶执行单独的查询并检索视频记录，您可以从中解析计数。在迭代时跟踪每个 BUCKET_ID 并跳过任何已查询的。并且不要忘记也执行相同的 MEDIA_TYPE 过滤器选择，以避免查询可能驻留在同一存储桶中的非视频文件。

根据你的问题代码尝试下一个 fragment ，我没有测试它但你可能会知道如何继续:

public static class MediaQuery
{
    @NonNull
    public static HashMap<String, ModelVideoFolder> get(@NonNull final Context context)
    {
        final HashMap<String, ModelVideoFolder> output     = new HashMap<>();
        final Uri                               contentUri = MediaStore.Video.Media.EXTERNAL_CONTENT_URI;

        final String[] projection = {MediaStore.Video.Media.BUCKET_DISPLAY_NAME,
                MediaStore.Video.Media.BUCKET_ID};

        try (final Cursor cursor = context.getContentResolver().query(contentUri,
                projection, null, null, null))
        {
            if ((cursor != null) && (cursor.moveToFirst() == true))
            {
                final int columnBucketName = cursor.getColumnIndexOrThrow(MediaStore.Video.Media.BUCKET_DISPLAY_NAME);
                final int columnBucketId   = cursor.getColumnIndexOrThrow(MediaStore.Video.Media.BUCKET_ID);

                do
                {
                    final String bucketName = cursor.getString(columnBucketName);
                    final String bucketId   = cursor.getString(columnBucketId);

                    if (output.containsKey(bucketId) == false)
                    {
                        final int count = MediaQuery.getCount(context, contentUri, bucketId);

                        final ModelVideoFolder item = new ModelVideoFolder(
                                bucketName, bucketId, null, null, count);

                        output.put(bucketId, item);
                    }

                } while (cursor.moveToNext());
            }
        }

        return output;
    }

    private static int getCount(@NonNull final Context context, @NonNull final Uri contentUri,
                                @NonNull final String bucketId)
    {
        try (final Cursor cursor = context.getContentResolver().query(contentUri,
                null, MediaStore.Video.Media.BUCKET_ID + "=?", new String[]{bucketId}, null))
        {
            return ((cursor == null) || (cursor.moveToFirst() == false)) ? 0 : cursor.getCount();
        }
    }
}