在Android中使用ContentResolver查询系统数据库时使用外键查询与distinct的小技巧

这几天自己在做一个小的图库项目，使用到了Android系统提供的MediaStore.Image数据库。在查询的时候发现有的时候需要去除相同项，有的时候需要通过外键查询缩略图的表。但是通过ContentResolver的query(Uri uri, String[] projection,String selection, String[] selectionArgs, String sortOrder);很难直接做到这一点，找了很久也找不到解决办法，最后还是灵光一现，想出了一个字符串拼接的方法。一下就是此方法的实现：

package com.zzy.cloudpic.dao;import java.util.ArrayList;import java.util.HashMap;import java.util.List;import java.util.Map;import android.content.ContentResolver;import android.content.Context;import android.database.Cursor;import android.net.Uri;import android.provider.MediaStore;import com.zzy.cloudpic.bean.CoverInfo;import com.zzy.cloudpic.bean.ImageInfo;import com.zzy.cloudpic.bean.ThumbInfo;public class MediaStoreDao {public final static String[] IMAGE_INFO = { MediaStore.Images.Media._ID,MediaStore.Images.Media.ORIENTATION, MediaStore.Images.Media.DATE_ADDED,MediaStore.Images.Media.DATE_MODIFIED, MediaStore.Images.Media.DATE_TAKEN,MediaStore.Images.Media.LATITUDE, MediaStore.Images.Media.LONGITUDE,MediaStore.Images.Media.DATA, MediaStore.Images.Media.MIME_TYPE,MediaStore.Images.Media.DISPLAY_NAME, MediaStore.Images.Media.BUCKET_DISPLAY_NAME,MediaStore.Images.Media.TITLE };public final static String[] THUMB_INFO = { MediaStore.Images.Thumbnails._ID,MediaStore.Images.Thumbnails.DATA, MediaStore.Images.Thumbnails.IMAGE_ID,MediaStore.Images.Thumbnails.KIND, MediaStore.Images.Thumbnails.WIDTH,MediaStore.Images.Thumbnails.HEIGHT };//此处在需要查询的属性前面拼接上 distinct去除相同项public final static String[] COVER_INFO = { "distinct  "+ MediaStore.Images.Media.BUCKET_DISPLAY_NAME };/** * 查询图片信息 *  * @param context *            上下文信息 * @param uri *            数据库uri * @param projection *            需要查询的列名 * @param selection *            查询条件 * @param selectionArgs *            占位符信息 * @param sortOrder *            排序 * @return */public static Map<Integer, ImageInfo> queryImageInfo(Context context, Uri uri,String[] projection, String selection, String[] selectionArgs, String sortOrder) {// 用来保存ImageInfo信息，以image_id为keyMap<Integer, ImageInfo> map = new HashMap<Integer, ImageInfo>();ImageInfo imageInfo = null;ContentResolver cr = context.getContentResolver();Cursor cursor = cr.query(uri, projection, selection, selectionArgs, sortOrder);while (cursor.moveToNext()) {imageInfo = new ImageInfo();imageInfo.setId(cursor.getInt(0));imageInfo.setOrientation(cursor.getInt(1));imageInfo.setDate_add(cursor.getLong(2));imageInfo.setDate_modified(cursor.getLong(3));imageInfo.setDate_taken(cursor.getLong(4));imageInfo.setLatitude(cursor.getDouble(5));imageInfo.setLongitude(cursor.getDouble(6));imageInfo.setData(cursor.getString(7));imageInfo.setMime_type(cursor.getString(8));imageInfo.setDisplay_name(cursor.getString(9));imageInfo.setParent_name(cursor.getString(10));imageInfo.setTitle(cursor.getString(11));map.put(imageInfo.getId(), imageInfo);}cursor.close();return map;}/** * 查询缩略图信息 *  * @param context *            上下文信息 * @param uri *            数据库uri * @param projection *            需要查询的列名 * @param selection *            查询条件 * @param selectionArgs *            占位符信息 * @param sortOrder *            排序 * @return */public static List<ThumbInfo> queryThumbInfo(Context context, Uri uri, String[] projection,String selection, String[] selectionArgs, String sortOrder) {// Map<Integer, ThumbInfo> map = new HashMap<Integer, ThumbInfo>();List<ThumbInfo> list = new ArrayList<ThumbInfo>();ThumbInfo thumbInfo = null;ContentResolver cr = context.getContentResolver();Cursor cursor = cr.query(uri, projection, selection, selectionArgs, sortOrder);while (cursor.moveToNext()) {thumbInfo = new ThumbInfo();thumbInfo.setId(cursor.getInt(0));thumbInfo.setData(cursor.getString(1));thumbInfo.setImage_id(cursor.getInt(2));thumbInfo.setKind(cursor.getInt(3));thumbInfo.setWidth(cursor.getInt(4));thumbInfo.setHeight(cursor.getInt(5));// map.put(thumbInfo.getImage_id(), thumbInfo);list.add(thumbInfo);}cursor.close();return list;}/** * 在Android中系统提供了两张表来存储SD卡中的图片信息，分别是images(图片的信息)和thumbnails(图片的缩略图信息) * 分别使用MediaStore.Image. Media和MediaStore.Image.Thumbnails来操作，以image_id为外键 * 。这里在images表有一个字段 * bucket_dispaly_name是图片所在的文件夹，也就是图片所在的相册。CoverInfo封装了属于同一个相册的所有图片的信息 * ，包括缩略图。这里查询就需要使用到distinct取出相同项和外键查询thumbnails表 *  * @param context *            上下文信息 * @param uri *            数据库uri * @param projection *            需要查询的列名 * @param selection *            查询条件 * @param selectionArgs *            占位符信息 * @param sortOrder *            排序 * @return */public static List<CoverInfo> queryCoverInfo(Context context, Uri uri, String[] projection,String selection, String[] selectionArgs, String sortOrder) {List<CoverInfo> list = new ArrayList<CoverInfo>();CoverInfo coverInfo = null;ContentResolver cr = context.getContentResolver();// 首先在image表中查询出相册，因为可能有的很多图片在同一个文件夹下，也就是在同一个相册，此处就需要使用distinct去除相同项Cursor cursor = cr.query(uri, projection, selection, selectionArgs, sortOrder);while (cursor.moveToNext()) {coverInfo = new CoverInfo(cursor.getString(0));// select为sql语句查询的where部分，此处为外键查询的关键，整个的意思就是:// where image_id in (select _id from images where_bucket_display_name='covername')String where = MediaStore.Images.Thumbnails.IMAGE_ID+ " in (select _id from images where "+ MediaStore.Images.Media.BUCKET_DISPLAY_NAME + "='" + coverInfo.getName()+ "')";coverInfo.setThumbList(MediaStoreDao.queryThumbInfo(context,MediaStore.Images.Thumbnails.EXTERNAL_CONTENT_URI, MediaStoreDao.THUMB_INFO,where, null, null));list.add(coverInfo);}cursor.close();return list;}}