ZSpirytus
Reputation: 359
Implementation of JOIN operation on ContentResolver?
I use @pskink solution to optimize my query code, reducing consumed time from 3000+ms to 200+ms by using ContentQueryMap.
But I still confused on how to implements JOIN operation on ContentResolver. In my limit experience, I believe consumed time will be reduced to below 100ms by using JOIN. Here is my code. How can I implements JOIN via ContentResolver?
BTW, is any optimization on my code? Thanks!
// scan Music by query table: MediaStore.Audio.AudioColumns .
private void scanMusic() {
Map<String, ContentValues> albumQueryMap = prepareAlbums();
Map<String, ContentValues> artistQueryMap = prepareArtist();
final String[] musicProjection = {
MediaStore.Audio.AudioColumns.DATA,
MediaStore.Audio.Media.TITLE,
MediaStore.Audio.Media.ARTIST,
MediaStore.Audio.Media.ALBUM,
MediaStore.Audio.Media.ALBUM_ID,
MediaStore.Audio.Media.SIZE,
MediaStore.Audio.Media.DURATION,
MediaStore.Audio.Media.DATE_ADDED
};
final String selection = MediaStore.Audio.AudioColumns.IS_MUSIC + " != ? And "
+ MediaStore.Audio.AudioColumns.DURATION + " >= ?";
final String[] selectionArgs = new String[]{"0", "60000"};
Cursor musicCursor = context.getContentResolver().query(
MediaStore.Audio.Media.EXTERNAL_CONTENT_URI,
musicProjection,
selection,
selectionArgs,
null
);
if (musicCursor != null) {
while (musicCursor.moveToNext()) {
// scan item music
String musicFilePath = musicCursor.getString(0);
String musicName = musicCursor.getString(1);
String musicArtist = musicCursor.getString(2);
String musicAlbumName = musicCursor.getString(3);
String albumId = musicCursor.getString(4);
String coverPath = albumQueryMap.get(albumId).getAsString(MediaStore.Audio.Albums.ALBUM_ART);
String musicFileSize = Formatter.formatFileSize(MainApplication.getBackgroundContext(), musicCursor.getLong(5));
long musicDuration = musicCursor.getLong(6);
long musicAddDate = musicCursor.getLong(7);
Music itemMusic = new Music(musicFilePath, musicName, musicArtist, musicAlbumName, coverPath, musicDuration, musicFileSize, musicAddDate);
mAllMusicList.add(itemMusic);
}
musicCursor.close();
}
}
// scan Albums by query table: MediaStore.Audio.Albums and cache it.
private Map<String, ContentValues> prepareAlbums() {
final String[] projection = {
MediaStore.Audio.Albums._ID,
MediaStore.Audio.Albums.ALBUM,
MediaStore.Audio.Albums.ALBUM_ART,
MediaStore.Audio.Albums.ARTIST,
MediaStore.Audio.Albums.FIRST_YEAR,
MediaStore.Audio.Albums.LAST_YEAR,
MediaStore.Audio.Albums.NUMBER_OF_SONGS,
};
Cursor cursor = MainApplication.getBackgroundContext().getContentResolver().query(
MediaStore.Audio.Albums.EXTERNAL_CONTENT_URI,
projection,
null,
null,
null);
ContentQueryMap queryMap = new ContentQueryMap(
cursor,
MediaStore.Audio.Albums._ID,
false,
null
);
Map<String, ContentValues> map = queryMap.getRows();
for (String albumId : map.keySet()) {
ContentValues values = map.get(albumId);
String albumName = values.getAsString(MediaStore.Audio.Albums.ALBUM);
String albumArt = values.getAsString(MediaStore.Audio.Albums.ALBUM_ART);
String artist = values.getAsString(MediaStore.Audio.Albums.ARTIST);
String firstYear = values.getAsString(MediaStore.Audio.Albums.FIRST_YEAR);
String lastYear = values.getAsString(MediaStore.Audio.Albums.LAST_YEAR);
int numberOfSongs = values.getAsInteger(MediaStore.Audio.Artists.Albums.NUMBER_OF_SONGS);
Album item = new Album(albumName, albumArt, artist, firstYear, lastYear, numberOfSongs);
mAlbumList.add(item);
}
try {
return map;
} finally {
cursor.close();
queryMap.close();
}
}
// scan Artist by query table:MediaStore.Audio.Artists and cache it.
private Map<String, ContentValues> prepareArtist() {
final String[] projection = {
MediaStore.Audio.Artists._ID,
MediaStore.Audio.Artists.ARTIST,
MediaStore.Audio.Artists.NUMBER_OF_ALBUMS,
MediaStore.Audio.Artists.NUMBER_OF_TRACKS,
};
Cursor cursor = MainApplication.getBackgroundContext().getContentResolver().query(
MediaStore.Audio.Artists.EXTERNAL_CONTENT_URI,
projection,
null,
null,
null);
ContentQueryMap queryMap = new ContentQueryMap(
cursor,
MediaStore.Audio.Artists._ID,
false,
null
);
Map<String, ContentValues> map = queryMap.getRows();
for (String artistId : map.keySet()) {
ContentValues values = map.get(artistId);
String artist = values.getAsString(MediaStore.Audio.Artists.ARTIST);
int numberOfAlbums = values.getAsInteger(MediaStore.Audio.Artists.NUMBER_OF_ALBUMS);
int numberOfTracks = values.getAsInteger(MediaStore.Audio.Artists.NUMBER_OF_TRACKS);
Artist item = new Artist(artist, numberOfAlbums, numberOfTracks);
mArtistList.add(item);
}
try {
return map;
} finally {
cursor.close();
queryMap.close();
}
}
Upvotes: 1
Views: 156
Answers (1)
ZSpirytus
Reputation: 359
As @pskink say, just use ContentQueryMap to optimize the query. Why
can ContentQueryMap improve my query code efficiency?
I Wrote Code Like This Before:
private void scanMusic() {
final String[] musicProjection = {
MediaStore.Audio.AudioColumns.DATA,
MediaStore.Audio.Media.TITLE,
MediaStore.Audio.Media.ARTIST,
MediaStore.Audio.Media.ALBUM,
MediaStore.Audio.Media.ALBUM_ID,
MediaStore.Audio.Media.SIZE,
MediaStore.Audio.Media.DURATION,
MediaStore.Audio.Media.DATE_ADDED
};
final String selection = MediaStore.Audio.AudioColumns.IS_MUSIC + " != ? And "
+ MediaStore.Audio.AudioColumns.DURATION + " >= ?";
final String[] selectionArgs = new String[]{"0", "60000"};
Cursor musicCursor = context.getContentResolver().query(
MediaStore.Audio.Media.EXTERNAL_CONTENT_URI,
musicProjection,
selection,
selectionArgs,
null
);
if (musicCursor != null) {
while (musicCursor.moveToNext()) {
// scan item music
String musicFilePath = musicCursor.getString(0);
String musicName = musicCursor.getString(1);
String musicArtist = musicCursor.getString(2);
String musicAlbumName = musicCursor.getString(3);
String albumId = musicCursor.getString(4);
// Scan the album form once for each row of the music form
String coverPath = getThumbAlbum(albumId);
String musicFileSize = Formatter.formatFileSize(context, musicCursor.getLong(5));
long musicDuration = musicCursor.getLong(6);
long musicAddDate = musicCursor.getLong(7);
Music itemMusic = new Music(musicFilePath, musicName, musicArtist, musicAlbumName, coverPath, musicDuration, musicFileSize, musicAddDate);
mAllMusicList.add(itemMusic);
}
musicCursor.close();
}
}
private String getThumbAlbum(String albumId) {
ContentResolver resolver = context.getContentResolver();
Uri albumUri = MediaStore.Audio.Albums.EXTERNAL_CONTENT_URI;
String id = MediaStore.Audio.Albums._ID;
String[] selection = new String[]{MediaStore.Audio.Albums.ALBUM_ART};
String[] selectionArgs = new String[]{albumId};
Cursor cursor = resolver.query(albumUri, selection, id + "=?", selectionArgs, null);
if (cursor != null && cursor.moveToNext()) {
try {
return cursor.getString(0);
} finally {
cursor.close();
}
}
return null;
}
How to optimize my code? The answer is obvious. By caching Albums form query result, we can reduce the query time of Albums form.
By using ContentQueryMap, we can reach the result that we expect。
I Wrote Code Like This After:
private void scanMusic() {
Map<String, ContentValues> albumQueryMap = prepareAlbums();
final String[] musicProjection = {
MediaStore.Audio.AudioColumns.DATA,
MediaStore.Audio.Media.TITLE,
MediaStore.Audio.Media.ARTIST,
MediaStore.Audio.Media.ALBUM,
MediaStore.Audio.Media.ALBUM_ID,
MediaStore.Audio.Media.SIZE,
MediaStore.Audio.Media.DURATION,
MediaStore.Audio.Media.DATE_ADDED
};
final String selection = MediaStore.Audio.AudioColumns.IS_MUSIC + " != ? And "
+ MediaStore.Audio.AudioColumns.DURATION + " >= ?";
final String[] selectionArgs = new String[]{"0", "60000"};
Cursor musicCursor = context.getContentResolver().query(
MediaStore.Audio.Media.EXTERNAL_CONTENT_URI,
musicProjection,
selection,
selectionArgs,
null
);
if (musicCursor != null) {
while (musicCursor.moveToNext()) {
// scan item music
String musicFilePath = musicCursor.getString(0);
String musicName = musicCursor.getString(1);
String musicArtist = musicCursor.getString(2);
String musicAlbumName = musicCursor.getString(3);
String albumId = musicCursor.getString(4);
String coverPath = albumQueryMap.get(albumId).getAsString(MediaStore.Audio.Albums.ALBUM_ART);
String musicFileSize = Formatter.formatFileSize(context, musicCursor.getLong(5));
long musicDuration = musicCursor.getLong(6);
long musicAddDate = musicCursor.getLong(7);
Music itemMusic = new Music(musicFilePath, musicName, musicArtist, musicAlbumName, coverPath, musicDuration, musicFileSize, musicAddDate);
mAllMusicList.add(itemMusic);
}
musicCursor.close();
}
}
// Caching the query result of Albums form into a Map, with Which we can get coverPath easily by given key.
private Map<String, ContentValues> prepareAlbums() {
final String[] projection = {
MediaStore.Audio.Albums._ID,
MediaStore.Audio.Albums.ALBUM,
MediaStore.Audio.Albums.ALBUM_ART,
MediaStore.Audio.Albums.ARTIST,
MediaStore.Audio.Albums.FIRST_YEAR,
MediaStore.Audio.Albums.LAST_YEAR,
MediaStore.Audio.Albums.NUMBER_OF_SONGS,
};
Cursor cursor = context.getContentResolver().query(
MediaStore.Audio.Albums.EXTERNAL_CONTENT_URI,
projection,
null,
null,
null
);
ContentQueryMap queryMap = new ContentQueryMap(
cursor,
MediaStore.Audio.Albums._ID,
false,
null
);
try {
return queryMap.getRows();
} finally {
cursor.close();
queryMap.close();
}
}
Before I use ContentQueryMap, the program use 3000+ms on querying with result size == 273. After using ContentQueryMap, the program use just 200+ms, 15x faster, awesome.
Upvotes: 1
Related Questions
- how to join two tables in android using content provider query method?
- Using JOIN when reading from SQLite via ContentResolver
- Content provider with multiple joins on same table
- Queries in Content Provider
- Android - Join query using Content Provider
- sql query in android
- Can I perform this Android query with ContentResolver.query()? (LEFT JOIN and CASE)
- Android optimize query on ContentResolver
- How to join a ContentResolver Cursor with a Database Cursor?
- Am I able to run SQL functions on a ContentResolver columns query?