java 如何使用 Content Provider 实现复杂的查询？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/25210359/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to implement complex queries using a Content Provider?
提问by Carla Urrea Stabile
I am asking this because I am not quite sure of how to work with Android Content Providers. I have a subset of my database with 8 tables and I need to create complex queries to get some of the data. My content provider works fine with simple queries. For example, I have a table Person on my PersonModel.javaclass and I get the data using:
我问这个是因为我不太确定如何使用 Android 内容提供程序。我有一个包含 8 个表的数据库子集，我需要创建复杂的查询来获取一些数据。我的内容提供者可以很好地处理简单的查询。例如，我的PersonModel.java类中有一个表 Person ，我使用以下方法获取数据：
String [] projection = {PersonModel.C_FIRST_NAME, PersonModel.C_LAST_NAME};
Cursor cursor = context.getContentResolver().query(
MyProvider.CONTENT_URI_PERSONS, projection, null,
null, null);
and it works perfectly.
它完美无缺。
On MyProviderI have a bunch of CONTENT_URI constants, on for each of my tables.
在MyProvider上，我的每个表都有一堆 CONTENT_URI 常量。
public class MyProvider extends ContentProvider {
MyDbHelper dbHelper;
SQLiteDatabase db;
private static final String AUTHORITY = "com.myapp.models";
//Paths for each tables
private static final String PATH_PROFILE_PICTURES = "profile_pictures";
private static final String PATH_PERSONS = "persons";
private static final String PATH_USERS = "users";
....
//Content URIs for each table
public static final Uri CONTENT_URI_PROFILE_PICTURES = Uri
.parse("content://" + AUTHORITY + "/" + PATH_PROFILE_PICTURES);
public static final Uri CONTENT_URI_PERSONS = Uri.parse("content://"
+ AUTHORITY + "/" + PATH_PERSONS);
public static final Uri CONTENT_URI_USERS = Uri.parse("content://"
+ AUTHORITY + "/" + PATH_USERS);
...
private static final int PROFILE_PICTURES = 1;
private static final int PROFILE_PICTURE_ID = 2;
private static final int PERSONS = 3;
private static final int PERSON_ID = 4;
private static final int USERS = 5;
private static final int USER_ID = 6;
private static final UriMatcher sURIMatcher = new UriMatcher(
UriMatcher.NO_MATCH);
static {
sURIMatcher.addURI(AUTHORITY, PATH_PROFILE_PICTURES, PROFILE_PICTURES);
sURIMatcher.addURI(AUTHORITY, PATH_PROFILE_PICTURES + "/#",
PROFILE_PICTURE_ID);
sURIMatcher.addURI(AUTHORITY, PATH_PERSONS, PERSONS);
sURIMatcher.addURI(AUTHORITY, PATH_PERSONS + "/#", PERSON_ID);
sURIMatcher.addURI(AUTHORITY, PATH_USERS, USERS);
sURIMatcher.addURI(AUTHORITY, PATH_USERS + "/#", USER_ID);
...
}
public Cursor query(Uri uri, String[] projection, String selection,
String[] selectionArgs, String sortOrder) {
// Uisng SQLiteQueryBuilder instead of query() method
SQLiteQueryBuilder queryBuilder = new SQLiteQueryBuilder();
// check if the caller has requested a column which does not exists
//checkColumns(projection);
int uriType = sURIMatcher.match(uri);
switch (uriType) {
case PROFILE_PICTURES:
queryBuilder.setTables(ProfilePictureModel.TABLE_PROFILE_PICTURE);
break;
case PROFILE_PICTURE_ID:
// Adding the ID to the original query
queryBuilder.appendWhere(ProfilePictureModel.C_ID + "="
+ uri.getLastPathSegment());
case PERSONS:
queryBuilder.setTables(PersonModel.TABLE_PERSON);
break;
case PERSON_ID:
// Adding the ID to the original query
queryBuilder.appendWhere(PersonModel.C_ID + "="
+ uri.getLastPathSegment());
case USERS:
queryBuilder.setTables(UserModel.TABLE_USER);
break;
case USER_ID:
// Adding the ID to the original query
queryBuilder.appendWhere(UserModel.C_ID + "="
+ uri.getLastPathSegment());
default:
throw new IllegalArgumentException("Unknown URI: " + uri);
}
db = dbHelper.getWritableDatabase();
Cursor cursor = queryBuilder.query(db, projection, selection,
selectionArgs, null, null, sortOrder);
// make sure that potential listeners are getting notified
cursor.setNotificationUri(getContext().getContentResolver(), uri);
}
That is a small part of my content provider. So my questions are:
这是我的内容提供商的一小部分。所以我的问题是：
1) How do I implement a rawQuery() in my content provider? or how do I use properly my queryBuilder?, let's say I want to execute this query using several tables, renaming them and also passing the p1.id as a parameter?
1) 如何在我的内容提供者中实现一个 rawQuery()？或者我如何正确使用我的 queryBuilder？，假设我想使用多个表执行此查询，重命名它们并将 p1.id 作为参数传递？
SELECT p1.first_name, p1_last_name
FROM Person p1, Person P2, Relationship r
WHERE p1.id = ? AND
p1.id = r.relative_id AND
p2.id = r.related_id;
I tried so by doing this: On my query() method (shown above) I have a new case, called GET_RELATIVES:
我尝试这样做：在我的 query() 方法（如上所示）中，我有一个名为 GET_RELATIVES 的新案例：
case GET_RELATIVES:
db = dbHelper.getWritableDatabase();
queryBuilder.setTables(PersonModel.TABLE_PERSON + " p1, "
+ PersonModel.TABLE_PERSON + " p2, "
+ RelationshipModel.TABLE_RELATIONSHIP + " r");
queryBuilder.appendWhere("p2."+PersonModel.C_ID + "=" + uri.getLastPathSegment());
queryBuilder.appendWhere("p2."+PersonModel.C_ID + "=" + "r.related_id");
queryBuilder.appendWhere("p1."+PersonModel.C_ID + "=" + "r.relative_id");
so I defined a new PATH, CONTENT URI and add it to the UriMatcher, like this:
所以我定义了一个新的 PATH、CONTENT URI 并将其添加到 UriMatcher，如下所示：
private static final String PATH_GET_RELATIVES = "get_relatives";
public static final Uri CONTENT_URI_GET_RELATIVES = Uri
.parse("content://" + AUTHORITY + "/"
+ PATH_GET_RELATIVES);
private static final int GET_RELATIVES = 22;
private static final UriMatcher sURIMatcher = new UriMatcher(
UriMatcher.NO_MATCH);
static {
...
sURIMatcher.addURI(AUTHORITY, PATH_GET_RELATIVES, GET_RELATIVES);
}
but this does not seem to work so I think I'm probably defining something wrong on my content provider or inside the query method.
但这似乎不起作用，所以我想我可能在我的内容提供者或查询方法中定义了错误。
2) I am not quite sure what is the point on having for each table a constant called TABLE_ID and adding it to the switch-case. What is that used for? How do I call it?
2）我不太确定为每个表设置一个名为 TABLE_ID 的常量并将其添加到 switch-case 有什么意义。那是干什么用的？我怎么称呼它？
Hope anyone can help me with this, thanks in advance!
希望任何人都可以帮助我，在此先感谢！
回答by Carla Urrea Stabile
I actually found the answer to my question in the most obvious place: the android documentation.
我实际上在最明显的地方找到了我的问题的答案：android 文档。
First Question: Implement a rawQuery. Did it like this:
第一个问题：实现一个 rawQuery。是不是这样的：
Inside of my switch-case in the content provider I added a new URI, which for me is a JOIN between to tables, so I created a new ContentUri constant for it, a new ID, and registered it on the UriMatcher and then wrote the rawQuery. So MyProvider now looks a litte bit like this:
在内容提供者的 switch-case 里面，我添加了一个新的 URI，对我来说它是表之间的 JOIN，所以我为它创建了一个新的 ContentUri 常量，一个新的 ID，并将它注册到 UriMatcher 上，然后写下原始查询。所以 MyProvider 现在看起来有点像这样：
public class MyProvider extends ContentProvider {
...
// JOIN paths
private static final String PATH_RELATIONSHIP_JOIN_PERSON_GET_RELATIVES =
"relationship_join_person_get_relatives";
...
public static final Uri CONTENT_URI_RELATIONSHIP_JOIN_PERSON_GET_RELATIVES = Uri
.parse("content://" + AUTHORITY + "/"
+ PATH_RELATIONSHIP_JOIN_PERSON_GET_RELATIVES);
...
private static final int RELATIONSHIP_JOIN_PERSON_GET_RELATIVES = 21;
private static final UriMatcher sURIMatcher = new UriMatcher(
UriMatcher.NO_MATCH);
static {
...
//JOINS
sURIMatcher.addURI(AUTHORITY, PATH_RELATIONSHIP_JOIN_PERSON_GET_RELATIVES + "/#",
RELATIONSHIP_JOIN_PERSON_GET_RELATIVES);
...
public Cursor query(Uri uri, String[] projection, String selection,
String[] selectionArgs, String sortOrder) {
// Uisng SQLiteQueryBuilder instead of query() method
SQLiteQueryBuilder queryBuilder = new SQLiteQueryBuilder();
// check if the caller has requested a column which does not exists
//checkColumns(projection);
int uriType = sURIMatcher.match(uri);
switch (uriType) {
...
case RELATIONSHIP_JOIN_PERSON_GET_RELATIVES:
db = dbHelper.getWritableDatabase();
String[] args = {String.valueOf(uri.getLastPathSegment())};
Cursor cursor = db.rawQuery(
"SELECT p1.first_name, p1.last_name " +
"FROM Person p1, Person p2, Relationship r " +
"WHERE p1.id = r.relative_id AND " +
"p2.id = r.related_id AND " +
"p2.id = ?", args);
cursor.setNotificationUri(getContext().getContentResolver(), uri);
return cursor;
...
}
And to call the query() method and pass the id ad a parameter I did this in my controller:
为了调用 query() 方法并向 id ad 传递一个参数，我在控制器中执行了此操作：
String[] projection = { PersonModel.C_FIRST_NAME,
PersonModel.C_LAST_NAME };
Cursor cursor = context.getContentResolver().query(
ContentUris.withAppendedId(
AkdemiaProvider.CONTENT_URI_RELATIONSHIP_JOIN_PERSON_GET_RELATED, id),
projection, null, null, null);
Second question: Having the TABLE_ID constant is useful to have a query for each table passing an id as a parameter, I didn't know how to call the query method passing such id and this is how the Android Developer Documentation explains how to do so using ContentUris.withAppendedId
第二个问题：拥有 TABLE_ID 常量对于将 id 作为参数传递给每个表的查询很有用，我不知道如何调用传递此类 id 的查询方法，这就是 Android 开发人员文档解释如何执行此操作的方式使用ContentUris.withAppendedId
// Request a specific record.
Cursor managedCursor = managedQuery(
ContentUris.withAppendedId(Contacts.People.CONTENT_URI, 2),
projection, // Which columns to return.
null, // WHERE clause.
null, // WHERE clause value substitution
People.NAME + " ASC"); // Sort order.
I you guys want to see the whole documentation go to this link.
我你们想看到整个文档去这个链接。
Hope this helps to anyone else having the same problem to understand ContentProvider, ContentUris and all that :)
希望这有助于其他有同样问题的人理解 ContentProvider、ContentUris 和所有这些 :)
回答by SKP
Below code worked for me. Inside your Application's Content Provider:
下面的代码对我有用。在应用程序的内容提供程序中：
public static final String PATH_JOIN_TWO_TABLES = "my_path";
public static final Uri URI_JOIN_TWO_TABLES =
Uri.parse("content://" + AUTHORITY + "/" + PATH_JOIN_TWO_TABLES);
private static final int ID_JOIN_TWO_TABLES = 1001;
private static final UriMatcher sURIMatcher = new UriMatcher(
UriMatcher.NO_MATCH);
static {
sURIMatcher.addURI(AUTHORITY,
PATH_JOIN_TWO_TABLES + "/#", ID_JOIN_TWO_TABLES );
}
@Nullable
@Override
public Cursor query(@NonNull Uri uri, String[] projection, String selection,String[] selectionArgs,
String sortOrder, CancellationSignal cancellationSignal) {
int uriType = sURIMatcher.match(uri);
switch (uriType) {
case ID_JOIN_TWO_TABLES:
return getWritableDatabase()
.rawQuery("select * from " +
"table_one" + " LEFT OUTER JOIN "
+ "table_two" + " ON ("
+ "table_one.ID"
+ " = " + "table_two.id" + ")", null);
}
return super.query(uri, projection, selection, selectionArgs, sortOrder, cancellationSignal);
}
And while making the Query inside your Activity or Fragment:
在您的 Activity 或 Fragment 中进行查询时：
Cursor cursor = getActivity().getContentResolver()
.query(ContentUris.withAppendedId(MYContentProvider.URI_JOIN_TWO_TABLES, MyContentProvider.ID_JOIN_TWO_TABLES), null, null, null, null);
Hope it works for you.
希望对你有效。
回答by AnkitSomani
For simple queries use selectionArgs in ContentProvider. It works like below
对于简单的查询，在 ContentProvider 中使用 selectionArgs。它的工作原理如下
String[] args = { "first string", "[email protected]" };
Cursor cursor = db.query("TABLE_NAME", null, "name=? AND email=?", args, null);
Having the TABLE_ID inside the to create a different queries for each table.
在 TABLE_ID 中为每个表创建不同的查询。
Refer following class for all multiple table in content providers
对于内容提供程序中的所有多个表，请参阅以下类
