Reputation: 234
I have an SQL query from string and trying to access ContentProvider
. The sql query looks like:
String query = "SELECT * FROM application_settings WHERE _id = ?";
I have to access content provider by gettting ContentResolver
like:
context.getContentResolver().query()
but query method accepts:
Cursor query(Uri uri, String[] projection, String selection, String[] selectionArgs, String sortOrder);
Is there a way I can split the string query into projection, selection, selectionArgs and sortOrder?
I do not wish to execute raw queries so I would prefer to have a solution for this function with bind values.
Upvotes: 6
Views: 616
Reputation: 4798
I have just written a library which provides what you need. You only need to copy and paste it into the project and if you would like to add, expand and customize it depending on your requirements.
import android.content.Context;
import android.database.Cursor;
import android.net.Uri;
import android.util.Log;
class SqliteHandler {
// VERY IMPORTANT MAKE SURE IT'S CORRECT AND REGISTERED IN THE MANIFEST
private String PROVIDER_NAME = "com.example.android.mySqlite";
private String CONTENT_URL = "content://" + PROVIDER_NAME + "/";
private Context context;
SqliteHandler(Context context, String PROVIDER_NAME) {
this.context = context;
this.PROVIDER_NAME = PROVIDER_NAME;
}
Cursor exeQuery(String query) {
try {
queryObject obj = convertQueryStringToQueryObject(query);
return context.getContentResolver().query(obj.uri, obj.projection, obj.selection, obj.selectionArgs, null);
} catch (Exception e) {
e.printStackTrace();
return null;
}
}
Cursor exeQuery(String query, String[] selectionArgs) {
try {
queryObject obj = convertQueryStringToQueryObject(query);
return context.getContentResolver().query(obj.uri, obj.projection, obj.selection, selectionArgs, null);
} catch (Exception e) {
e.printStackTrace();
return null;
}
}
Cursor exeQuery(String query, String selection, String[] selectionArgs) {
try {
queryObject obj = convertQueryStringToQueryObject(query);
return context.getContentResolver().query(obj.uri, obj.projection, selection, selectionArgs, null);
} catch (Exception e) {
e.printStackTrace();
return null;
}
}
Cursor exeQuery(String query, String[] projection, String[] selectionArgs) {
try {
queryObject obj = convertQueryStringToQueryObject(query);
return context.getContentResolver().query(obj.uri, projection, obj.selection, selectionArgs, null);
} catch (Exception e) {
e.printStackTrace();
return null;
}
}
Cursor exeQuery(queryObject obj) {
try {
return context.getContentResolver().query(obj.uri, obj.projection, obj.selection, obj.selectionArgs, null);
} catch (Exception e) {
e.printStackTrace();
return null;
}
}
class queryObject {
Uri uri;
String[] projection;
String selection;
String[] selectionArgs;
String sortOrder;
queryObject(String table_name, String[] projection, String selection, String[]
selectionArgs) {
this.uri = Uri.parse(CONTENT_URL + table_name);
this.projection = projection;
this.selection = selection;
this.selectionArgs = selectionArgs;
}
}
queryObject convertQueryStringToQueryObject(String query) {
try {
String selection = null;
String[] selectionArgs = null;
query = query.toLowerCase();
String[] s = query.split("select")[1].split("from");
String[] projection = s[0].split(",");
String[] s2 = s[1].split("where");
String table_name = s2[0];
String logText = "";
if (s2.length > 1) {
selection = s2[1];
String[] args = s2[1].split("=");
selectionArgs = new String[args.length - 1];// half of the args are values others are keys
int count = 0;
for (int i = 1; i < args.length; i++) {
selectionArgs[count] = args[i]
.split("and")[0]
.split("or")[0]
.replace(" ", "")
.replace("and", "")
.replace("or", "");
count++;
}
for (int i = 0; i < selectionArgs.length; i++) {
logText += selectionArgs[i];
if (i < selectionArgs.length - 1) logText += ",";
selection = selection.replace(selectionArgs[i], "?");
}
}
Log.i("table_name", table_name);
Log.i("selection: ", selection == null ? "null" : selection);
Log.i("selectionArgs", logText.equals("") ? "null" : logText);
logText = "";
for (int i = 0; i < projection.length; i++) {
logText += projection[i];
if (i < projection.length - 1) logText += ",";
}
Log.i("projection", logText);
return new queryObject(table_name, projection, selection, selectionArgs);
} catch (Exception e) {
e.printStackTrace();
return null;
}
}}
instantiate SqliteHandler
, it's very important to pass valid PROVIDER_NAME
and also make sure that your CONTENT_PROVIDER
was registered in the AndroidManiFest.xml
. For an illustration of how does it work, we pass three different queries and get return values which are objects of type queryObject
SqliteHandler sh = new SqliteHandler(this,"PROVIDER_NAME");
SqliteHandler.queryObject obj1 = sh.convertQueryStringToQueryObject("SELECT * FROM table_name");
SqliteHandler.queryObject obj2 = sh.convertQueryStringToQueryObject("SELECT * FROM table_name WHERE _id = ?");
SqliteHandler.queryObject obj3 = sh.convertQueryStringToQueryObject("SELECT param1,param2,param3 FROM table_name WHERE param1 =\"a\" and param2=\"b\" or param3=\"c\"");
The method convertQueryStringToQueryObject
converts query string
into query class
then we can use this class for getContentResolver().query()
.
Important Note: because getContentResolver().query()
needs Uri
. Therefore, we need to create a Uri
from the table_name
. As a result, we need to pass valid PROVIDER_NAME
to the instance of SqliteHandler
.
As you can see the three different queries broke apart into parameters which we can use in the getContentResolver().query()
// 1th query
I/table_name: table_name
I/selection:: null
I/selectionArgs: null
I/projection: *
// 2th query
I/table_name: table_name
I/selection:: _id = ?
I/selectionArgs: ?
I/projection: *
// 3th query
I/table_name: table_name
I/selection:: param1 =? and param2=? or param3=?
I/selectionArgs: "a","b","c"
I/projection: param1,param2,param3
in The SqliteHandler.java
there is the exeQuery
method which has several overloads. Moreover, You can have a Cursor
at the Content Provider
depending on different input parameters.
SqliteHandler sh = new SqliteHandler(this,"PROVIDER_NAME");
SqliteHandler.queryObject obj1 = sh.convertQueryStringToQueryObject("SELECT * FROM table_name");
SqliteHandler.queryObject obj2 = sh.convertQueryStringToQueryObject("SELECT * FROM table_name WHERE _id = ?");
SqliteHandler.queryObject obj3 = sh.convertQueryStringToQueryObject("SELECT param1,param2,param3 FROM table_name WHERE param1 =\"a\" and param2=\"b\" or param3=\"c\"");
Cursor c = sh.exeQuery(obj1);
Cursor c = sh.exeQuery(obj2);
Cursor c = sh.exeQuery(obj3);
Cursor c = sh.exeQuery("SELECT param1,param2,param3 FROM table_name WHERE param1 =\"a\" and param2=\"b\" or param3=\"c\"");
Cursor c = sh.exeQuery("SELECT * FROM table_name WHERE _id = ?",new String[]{"whereArg"});
Cursor c = sh.exeQuery("SELECT * FROM table_name"," _id = ? ",new String[]{"whereArg"});
Cursor c = sh.exeQuery("SELECT ? FROM table_name WHERE _id = ?",new String[]{"Field"},new String[]{"whereArg"});
However, if you don't want to use exeQuery
try below walking through:
queryObject obj = convertQueryStringToQueryObject(query);
Cursor c = this.getContentResolver().query(obj.uri, obj.projection, obj.selection, obj.selectionArgs, null);
Upvotes: 5
Reputation: 3709
from the android document https://developer.android.com/guide/topics/providers/content-provider-basics#ClientProvider
cursor = getContentResolver().query( UserDictionary.Words.CONTENT_URI, // The content URI of the words table projection, // The columns to return for each row selectionClause, // Selection criteria selectionArgs, // Selection criteria sortOrder); // The sort order for the returned rows
you can do this
String[] projection = {"*"};
String[] selectionArgs = {"1", "2"}; //your ids here
Cursor cursor = getContentResolver().query(Uri.parse("content://your_provider/your_table"), projection, "_id", selectionArgs, null);
cursor.close();
to create provider see this https://developer.android.com/guide/topics/providers/content-provider-creating#top_of_page
see this answer also https://stackoverflow.com/a/1031101/10989990
Upvotes: -1