Android数据库查询

1.查询图片

 Uri mImageUri = MediaStore.Images.Media.EXTERNAL_CONTENT_URI;
        String[] projImage = {MediaStore.Images.Media._ID, MediaStore.Images.Media.DATA, MediaStore.Images.Media.SIZE, MediaStore.Images.Media.DISPLAY_NAME};
        Cursor mCursor = context.getContentResolver().query(mImageUri, projImage, MediaStore.Images.Media.MIME_TYPE + "=? or " + MediaStore.Images.Media.MIME_TYPE + "=?", new String[]{"image/jpeg", "image/png"}, MediaStore.Images.Media.DATE_MODIFIED + " desc");
        if (mCursor != null) {
            while (mCursor.moveToNext()) {
                // 获取图片的路径
                String path = mCursor.getString(mCursor.getColumnIndexOrThrow(MediaStore.Images.Media.DATA));
                int size = mCursor.getInt(mCursor.getColumnIndexOrThrow(MediaStore.Images.Media.SIZE)) / 1024;//单位kb
                String displayName = mCursor.getString(mCursor.getColumnIndexOrThrow(MediaStore.Images.Media.DISPLAY_NAME));
                long id = mCursor.getLong(mCursor.getColumnIndexOrThrow(MediaStore.Audio.Media._ID));
                Uri uri = ContentUris.withAppendedId(MediaStore.Audio.Media.EXTERNAL_CONTENT_URI, id);
            }
            mCursor.close();
        }

2.查询视频

 Uri mVideoUri = MediaStore.Video.Media.EXTERNAL_CONTENT_URI;
        String[] proj = {MediaStore.Video.Thumbnails._ID, MediaStore.Video.Thumbnails.DATA, MediaStore.Video.Media.DURATION, MediaStore.Video.Media.SIZE, MediaStore.Video.Media.DISPLAY_NAME, MediaStore.Video.Media.DATE_MODIFIED};
        Cursor mVideoCursor = context.getContentResolver().query(mVideoUri, proj, null, null, MediaStore.Video.Media.DATE_MODIFIED + " desc");
        if (mVideoCursor != null) {
            while (mVideoCursor.moveToNext()) {
                // 获取视频的路径
                int videoId = mVideoCursor.getInt(mVideoCursor.getColumnIndexOrThrow(MediaStore.Video.Media._ID));
                String path = mVideoCursor.getString(mVideoCursor.getColumnIndexOrThrow(MediaStore.Video.Media.DATA));
                int duration = mVideoCursor.getInt(mVideoCursor.getColumnIndexOrThrow(MediaStore.Video.Media.DURATION));
                long size = mVideoCursor.getLong(mVideoCursor.getColumnIndexOrThrow(MediaStore.Video.Media.SIZE)) / 1024; //单位kb
                if (size < 0) {
                    //某些设备获取size<0，直接计算
//                               Log.e("dml","this video size < 0 " + path);
                    size = new File(path).length() / 1024;
                }
                if (limitSize <= 0 ||  size <= limitSize ){//限制在50MB以内的视频才显示 (当限制的值不合理就全部显示)
                    String displayName = mVideoCursor.getString(mVideoCursor.getColumnIndexOrThrow(MediaStore.Video.Media.DISPLAY_NAME));
                    long modifyTime = mVideoCursor.getLong(mVideoCursor.getColumnIndexOrThrow(MediaStore.Video.Media.DATE_MODIFIED));//暂未用到
                }
            }
            mVideoCursor.close();
        }

3.查询音频

 Cursor cursor = context.getContentResolver().query(MediaStore.Audio.Media.EXTERNAL_CONTENT_URI, null, null,
                                null, MediaStore.Audio.AudioColumns.IS_MUSIC);
                        if (cursor != null) {
                            while (cursor.moveToNext()) {
                                String song = cursor.getString(cursor.getColumnIndexOrThrow(MediaStore.Audio.Media.DISPLAY_NAME));
                                String singer = cursor.getString(cursor.getColumnIndexOrThrow(MediaStore.Audio.Media.ARTIST));
                                String path = cursor.getString(cursor.getColumnIndexOrThrow(MediaStore.Audio.Media.DATA));

                                long id= cursor.getLong(cursor.getColumnIndexOrThrow(MediaStore.Audio.Media._ID));
                                Uri uri  =  ContentUris.withAppendedId(MediaStore.Audio.Media.EXTERNAL_CONTENT_URI, id);;

                                long duration = cursor.getLong(cursor.getColumnIndexOrThrow(MediaStore.Audio.Media.DURATION));
                                long size = cursor.getLong(cursor.getColumnIndexOrThrow(MediaStore.Audio.Media.SIZE));
                            }
                            // 释放资源
                            cursor.close();

4.根据文件路径返回Uri

（1）

File file=new File(filePath);
MediaScannerConnection.scanFile(context, new String[]{file.getAbsolutePath()}, null, new MediaScannerConnection.MediaScannerConnectionClient() {
                    @Override
                    public void onMediaScannerConnected() {
                    }

                    @Override
                    public void onScanCompleted(String path, Uri uri) {

                    }
                });

（2）备注代码里返回的视频的Uri，要返回音频或者图片的Uri要在查询和获取Uri里改为相应的URI查询。

MediaStore.Video.Media.EXTERNAL_CONTENT_URI，

MediaStore.Images.Media.EXTERNAL_CONTENT_URI，

MediaStore.Audio.Media.EXTERNAL_CONTENT_URI

 /**
     * 根据文件路径转uri
     * @param context
     * @param filePath
     * @return
     */
    public static Uri filePathToUri(Context context,String filePath){
        Uri uri=null;
        try {
            Cursor cursor=context.getContentResolver().query(MediaStore.Video.Media.EXTERNAL_CONTENT_URI
                    ,null, MediaStore.Video.Media.DATA + "=?"
                    ,new String[]{filePath},null);
            if (cursor!=null){
                while (cursor.moveToNext()) {
                    String path = cursor.getString(cursor.getColumnIndexOrThrow(MediaStore.Video.Media.DATA));
                    long id= cursor.getLong(cursor.getColumnIndexOrThrow(MediaStore.Video.Media._ID));
                     uri= ContentUris.withAppendedId(MediaStore.Video.Media.EXTERNAL_CONTENT_URI, id);
//                    Uri idUri=Uri.withAppendedPath(MediaStore.Video.Media.EXTERNAL_CONTENT_URI,String.valueOf(id));
                     break;
                }
                cursor.close();
            }
        }catch (Exception e){
            e.printStackTrace();
        }
        return uri;
    }

5.查询特定后缀名的文件（"pdf", "txt", "doc", "docx", "xlsx", "xls", "ppt", "pptx", "epub"）

（1）根据后缀名来查询（存在的问题，如果文件夹也是这样的命名也会被查询到，所以要判断是否是文件还是文件夹操作，查300个文件大概要1秒到2秒之间），这个是有点模糊查询，耗时会比较长，文件越多耗时越长。

private void quert(Context context) {
        long startTime = System.currentTimeMillis();

        String[] projection = new String[]{MediaStore.Files.FileColumns._ID,
                MediaStore.Files.FileColumns.DATA,
                MediaStore.Files.FileColumns.SIZE,
                MediaStore.Files.FileColumns.MIME_TYPE
        };

        StringBuilder stringBuilder = new StringBuilder();
        String[] queryFileTypes={"%.pdf","%.txt","%.doc","%.docx","%.xlsx","%.xls","%.ppt","%.pptx","%.epub"};
        for (int i = 0; i < queryFileTypes.length; i++) {
            stringBuilder.append(MediaStore.Files.FileColumns.DATA + " like ? " );
            if (i < queryFileTypes.length - 1) {
                stringBuilder.append(" OR ");
            }

        }
        Cursor cursor = context.getContentResolver().query(
                Uri.parse("content://media/external/file"),
                projection,
                stringBuilder.toString(),
                queryFileTypes,
                null);

        if (cursor!=null){
            while (cursor.moveToNext()){
                String path = cursor.getString(cursor.getColumnIndexOrThrow(MediaStore.Video.Media.DATA));
            }
            LogUtils.e((System.currentTimeMillis() - startTime)+"  qwert end === "+cursor.getCount());
            cursor.close();
        }
    }

（2）根据文件类型查询(使用文件类型查询耗时比后缀名查询的速度快，查询300多个文件耗时大约在300~400ms)

String[] queryFileTypes = {"application/pdf", "text/plain", "application/msword", "application/vnd.openxmlformats-officedocument.wordprocessingml.document", "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", "application/vnd.ms-excel", "application/vnd.ms-powerpoint", "application/vnd.openxmlformats-officedocument.presentationml.presentation", "application/epub+zip"};
        StringBuilder stringBuilder = new StringBuilder();
        for (int i = 0; i < queryFileTypes.length; i++) {
            stringBuilder.append(MediaStore.Files.FileColumns.MIME_TYPE + " = '" + queryFileTypes[i] + "'");
            if (i < queryFileTypes.length - 1) {
                stringBuilder.append(" OR ");
            }

        }
        files.addAll(queryFiles(context, stringBuilder.toString()));




 private void queryFiles(Context context, String queryFileType) {
        List<AirAuraBean> files = new ArrayList<>();
        String[] projection = new String[]{MediaStore.Files.FileColumns._ID, MediaStore.Files.FileColumns.DATA, MediaStore.Files.FileColumns.SIZE, MediaStore.Files.FileColumns.MIME_TYPE};
        //使用 MediaStore.Files.getContentUri("external")获取的URI同样是content://media/external/file

        Cursor cursor = context.getContentResolver().query(Uri.parse("content://media/external/file"), projection, queryFileType, null, null);
        if (cursor != null) {
            if (cursor.moveToFirst()) {

                int idindex = cursor.getColumnIndex(MediaStore.Files.FileColumns._ID);
                int dataindex = cursor.getColumnIndex(MediaStore.Files.FileColumns.DATA);
                int sizeindex = cursor.getColumnIndex(MediaStore.Files.FileColumns.SIZE);
                do {
                    String id = cursor.getString(idindex);
                    String path = cursor.getString(dataindex);
                    String size = cursor.getString(sizeindex);
//                    String type = cursor.getString(cursor.getColumnIndexOrThrow(MediaStore.Files.FileColumns.MIME_TYPE));


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

如果将查询放到for循环中一个一个的查询，会比组合条件查询更慢。（因为就算没有文件，getContentResolver().query查询也会耗时30~50ms之间的，所以合并查询更省时）