如何使用内容提供商来实现复杂的查询？ [英] How to implement complex queries using a Content Provider?
问题描述
我问这个，因为我不太清楚如何与Android的内容提供商合作。我有我有8个表的数据库的一个子集，我需要创建复杂的查询来获取一些数据。我的内容提供商正常工作与简单的查询。例如，我有我的 PersonModel.java 班表的人，我开始使用数据：
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.java class and I get the data using:
String [] projection = {PersonModel.C_FIRST_NAME, PersonModel.C_LAST_NAME};
Cursor cursor = context.getContentResolver().query(
MyProvider.CONTENT_URI_PERSONS, projection, null,
null, null);
和它完美的作品。
在 MYPROVIDER 我有一堆CONTENT_URI常数，对，我的每个表。
On MyProvider I have a bunch of CONTENT_URI constants, on for each of my tables.
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）如何在我的内容提供商实行rawQuery（）？或者我如何正确使用我的QueryBuilder？比方说，我想用几个表，重命名他们，同时也传递p1.id作为参数来执行这个查询？
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?
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;
我想这样做的：在我的query（）方法（如上图所示），我有一个新的情况下，所谓的GET_RELATIVES：
I tried so by doing this: On my query() method (shown above) I have a new case, called 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");
所以我定义了一个新的路径，内容URI并把它添加到UriMatcher，像这样的：
so I defined a new PATH, CONTENT URI and add it to the UriMatcher, like this:
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）我不太清楚什么是对具有用于每个表名为表标识符不变，将它添加到的switch-case的地步。就是用来做什么？我该如何称呼呢？
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?
希望有人能帮助我这个，先谢谢了！
Hope anyone can help me with this, thanks in advance!
推荐答案
其实，我找到了答案，我的问题在最明显的地方：android的文档。
I actually found the answer to my question in the most obvious place: the android documentation.
第一个问题：实现一个rawQuery。难道这样的：
First Question: Implement a rawQuery. Did it like this:
在我的switch-case的内容提供商我添加了一个新的URI，这对我来说是间表的加入，让我创建了一个新的ContentUri常数它，一个新的ID，并将其注册在UriMatcher和然后写了rawQuery。所以MYPROVIDER现在看起来豆蔻位是这样的：
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:
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;
...
}
和调用查询（）方法，并通过ID广告的参数我在我的控制器这样做：
And to call the query() method and pass the id ad a parameter I did this in my controller:
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);
第二个问题：具有表标识符常量是为每个表传递一个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.
我你们想看到整个文档转到该链接。
希望这有助于给任何人有同样的问题理解的ContentProvider，ContentUris和所有:)
Hope this helps to anyone else having the same problem to understand ContentProvider, ContentUris and all that :)
这篇关于如何使用内容提供商来实现复杂的查询？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
