Break SQL query into parts for accessing content provider

Question

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.

Bronwen

Answer 1

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.

SqliteHandler.java

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;
    }
}}

How To Use

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.

---

Output Log

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 

Complete Example

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);

Neilneila

even though your answer eventually works there is definitely a lot of space for improvements as this can crash in a lot of places. But the result is that it works so thank you for the answer – Bronwen

@Bronwen your welcome and i made a repository in github to issue bugs and crashes.i will be gald to contribute on it. github.com/MrAshFar/SqliteHandler – Neilneila

Answer 2

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://mcmap.net/q/166499/-what-are-projection-and-selection

Intendant

this is not what I asked – Bronwen