学习ContentProvider---之四：按属性查询，多表查询

这次我按照自己的想法增加了两个功能，一是：按名字查询；二是：多表查询。

我的理解：其实ContentProvider的机制很随意，它就类似于一个服务器一样，你把uri传来，只要按照特定的方式，它就能给你特定的功能，我觉得这个机制自由又方便。

 

其实这两个功能主要通过修改query就可以，完整的ContentProvider代码如下：

package com.ianc.lilyprovider; import android.content.ContentProvider; import android.content.ContentValues; import android.content.Context; import android.content.UriMatcher; import android.database.Cursor; import android.database.sqlite.SQLiteDatabase; import android.database.sqlite.SQLiteOpenHelper; import android.net.Uri; import android.util.Log; public class LilyProvider extends ContentProvider { final static String TABLE_NAME = "customer"; final static String PRODUCT_TABLE = "shoplist"; private static final String DATABASE_NAME = "lily.db"; private static String AUTHORITY = "com.ianc.lilyprovider"; private static final int DATABASE_VERSION = 1; DatabaseHelper mDbHelper; static UriMatcher sUriMatcher; private static final int USER = 1; private static final int USER_NAME = 2; private static final int MULITABLE = 3; private static final int SHOPLIST = 4; static{ sUriMatcher = new UriMatcher(UriMatcher.NO_MATCH); sUriMatcher.addURI(AUTHORITY, TABLE_NAME, USER); sUriMatcher.addURI(AUTHORITY, PRODUCT_TABLE, SHOPLIST); sUriMatcher.addURI(AUTHORITY, TABLE_NAME+"/"+LilyUser.UserColumns.NAME+"/*", USER_NAME);//search special user by name sUriMatcher.addURI(AUTHORITY, TABLE_NAME+"/*", MULITABLE); } class DatabaseHelper extends SQLiteOpenHelper { public DatabaseHelper(Context context) { super(context, DATABASE_NAME, null, DATABASE_VERSION); } @Override public void onCreate(SQLiteDatabase db) { Log.i("lily","LilyProvider-->DatabaseHelper-->onCreate"); db.execSQL("Create table " + TABLE_NAME + "( " + LilyUser.UserColumns._ID+ " INTEGER PRIMARY KEY AUTOINCREMENT, " + LilyUser.UserColumns._COUNT + " INTEGER,"+ LilyUser.UserColumns.NAME + " TEXT," + LilyUser.UserColumns.PASSWORD +" TEXT" + ");"); db.execSQL("Create table " + PRODUCT_TABLE + "( " + LilyUser.ShopListColumns._ID+ " INTEGER PRIMARY KEY AUTOINCREMENT, " + LilyUser.ShopListColumns._COUNT + " INTEGER,"+ LilyUser.ShopListColumns.USER_ID+" INTEGER,"+ LilyUser.ShopListColumns.PRODUCT + " TEXT," + LilyUser.ShopListColumns.DATE +" TEXT" + ");"); } @Override public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) { Log.i("lily","LilyProvider-->DatabaseHelper-->onUpgrade"); db.execSQL("DROP TABLE IF EXISTS "+TABLE_NAME+";"); db.execSQL("DROP TABLE IF EXISTS "+PRODUCT_TABLE+";"); onCreate(db); } } @Override public int delete(Uri arg0, String arg1, String[] arg2) { Log.i("lily","LilyProvider-->delete"); SQLiteDatabase db = mDbHelper.getWritableDatabase(); int rownum = db.delete(TABLE_NAME, arg1, arg2); return rownum; } @Override public String getType(Uri uri) { Log.i("lily","LilyProvider-->getType"); return null; } @Override public Uri insert(Uri uri, ContentValues values) { Log.i("lily","LilyProvider-->insert"); if (sUriMatcher.match(uri) == USER){ SQLiteDatabase db = mDbHelper.getWritableDatabase(); db.insert(TABLE_NAME, null, values); } else if (sUriMatcher.match(uri) == SHOPLIST){ SQLiteDatabase db = mDbHelper.getWritableDatabase(); db.insert(PRODUCT_TABLE, null, values); } return null; } @Override public boolean onCreate() { Log.i("lily","LilyProvider-->onCreate"); mDbHelper = new DatabaseHelper(this.getContext()); return false; } @Override public Cursor query(Uri uri, String[] projection, String selection, String[] selectionArgs, String sortOrder) { Log.i("lily","LilyProvider-->query"); Log.i("lily","uri = "+uri); Log.i("lily","sUriMatcher.match(uri)="+sUriMatcher.match(uri)); Cursor c = null; if (sUriMatcher.match(uri) == USER){ SQLiteDatabase db = mDbHelper.getReadableDatabase(); c = db.query(TABLE_NAME, projection, selection, selectionArgs, null, null, sortOrder); }else if (sUriMatcher.match(uri) == USER_NAME){ // format:authority/table/column/value-->0:table 1:column 2:value String name = uri.getPathSegments().get(2);// get name value Log.i("lily","query table:"+uri.getPathSegments().get(0)+" column:"+uri.getPathSegments().get(1)+" value:"+uri.getPathSegments().get(2)); SQLiteDatabase db = mDbHelper.getReadableDatabase(); if (selection != null){ if (selection.length()>0){ selection += "AND "+LilyUser.UserColumns.NAME+" like "+name; } } c = db.query(TABLE_NAME, projection, selection, selectionArgs, null, null, sortOrder); }else if (sUriMatcher.match(uri) == MULITABLE){ // format:authority/table1/table2 String table1 = uri.getPathSegments().get(0); String table2 = uri.getPathSegments().get(1); Log.i("lily","table1 = "+table1); Log.i("lily","table2 = "+table2); if (table1.equalsIgnoreCase(TABLE_NAME)){ if (table2.equals(PRODUCT_TABLE)){ SQLiteDatabase db = mDbHelper.getReadableDatabase(); if (selection != null){ if (selection.length()>0){ selection += "AND "+LilyUser.UserColumns._ID+" = "+LilyUser.ShopListColumns.USER_ID; } else{ selection = LilyUser.UserColumns._ID+" = "+LilyUser.ShopListColumns.USER_ID; } } else { selection = TABLE_NAME + "." +LilyUser.UserColumns._ID+" = "+PRODUCT_TABLE + "." + LilyUser.ShopListColumns.USER_ID; } c = db.query(table1+" cross join "+table2, projection, null, selectionArgs, null, null, sortOrder); } } } return c; } @Override public int update(Uri uri, ContentValues values, String selection, String[] selectionArgs) { Log.i("lily","LilyProvider-->update"); SQLiteDatabase db = mDbHelper.getWritableDatabase(); int rownum = db.update(TABLE_NAME, values, selection, selectionArgs); return rownum; } }  

这个里面定义Uri格式的时候一定要小心，不然出错了死都查不出来。

下面是使用查询的方法：

private void queryNameUri() { Uri uri = Uri.withAppendedPath(LilyUser.User.CONTENT_URI, "name/lily");//content://com.ianc.lilyprovider/customer/name/lily String[] projection = {LilyUser.UserColumns._ID, LilyUser.UserColumns.NAME, LilyUser.UserColumns.PASSWORD}; String selection = null; String [] selectionArgs = null; String sortOrder = null; Cursor cursor = getContentResolver().query(uri, projection, selection, selectionArgs, sortOrder); if (cursor == null){ Log.i("lily","cursor == null"); return; } if (cursor.moveToFirst()){ Log.i("lily","*********************************************"); String id; String name; String password; do{ id = cursor.getString(cursor.getColumnIndex(LilyUser.UserColumns._ID)); Log.i("lily","id = "+id); name = cursor.getString(cursor.getColumnIndex(LilyUser.UserColumns.NAME)); Log.i("lily","name = "+name); password = cursor.getString(cursor.getColumnIndex(LilyUser.UserColumns.PASSWORD)); Log.i("lily","password = "+password); Log.i("lily","*********************************************"); }while(cursor.moveToNext()); } else{ Log.i("lily","no result"); } cursor.close(); } 多表查询：

private void queryMultiTable() { String[] projection = {"customer."+LilyUser.UserColumns._ID, "customer."+LilyUser.UserColumns.NAME, "customer."+LilyUser.UserColumns.PASSWORD, "shoplist."+LilyUser.ShopListColumns.USER_ID, "shoplist."+LilyUser.ShopListColumns.PRODUCT, "shoplist."+LilyUser.ShopListColumns.DATE}; String selection = null; String [] selectionArgs = null; String sortOrder = null; Uri uri = Uri.withAppendedPath(LilyUser.User.CONTENT_URI, "shoplist"); Cursor cursor = getContentResolver().query(uri, projection, selection, selectionArgs, sortOrder); if (cursor == null){ Log.i("lily","queryMultiTable()-->cursor == null"); return; } if (cursor.moveToFirst()){ Log.i("lily","*********************************************"); String id; String name; String password; String user_id; String product; String date; do{ id = cursor.getString(cursor.getColumnIndex(LilyUser.UserColumns._ID)); Log.i("lily","id = "+id); name = cursor.getString(cursor.getColumnIndex(LilyUser.UserColumns.NAME)); Log.i("lily","name = "+name); password = cursor.getString(cursor.getColumnIndex(LilyUser.UserColumns.PASSWORD)); Log.i("lily","password = "+password); user_id = cursor.getString(cursor.getColumnIndex(LilyUser.ShopListColumns.USER_ID)); Log.i("lily","user_id = "+user_id); product = cursor.getString(cursor.getColumnIndex(LilyUser.ShopListColumns.PRODUCT)); Log.i("lily","product = " + product); date = cursor.getString(cursor.getColumnIndex(LilyUser.ShopListColumns.DATE)); Log.i("lily","date = " + date); Log.i("lily","*********************************************"); }while(cursor.moveToNext()); } else{ Log.i("lily","no result"); } cursor.close(); } 接下来是LilyUser这个说明类的代码：

package com.ianc.lilyprovider; import android.net.Uri; import android.provider.BaseColumns; public class LilyUser { public static class User{ public static final Uri CONTENT_URI = Uri.parse("content://com.ianc.lilyprovider/" + "customer"); } public static class UserColumns implements BaseColumns{ public static String NAME = "name"; public static String PASSWORD = "password"; } public static class ShopList{ public static final Uri CONTENT_URI = Uri.parse("content://com.ianc.lilyprovider/" + "shoplist"); } public static class ShopListColumns implements BaseColumns{ public static String USER_ID = "user_id"; public static String PRODUCT = "product"; public static String DATE = "date"; } }  完工。。。。。。