将SQL查询拆分为几个部分以访问内容提供程序

Question

我有一个来自string的SQL查询，并试图访问ContentProvider。sql查询如下所示：

String query = "SELECT * FROM application_settings WHERE _id = ?";

我必须通过获取ContentResolver来访问内容提供程序，例如：

context.getContentResolver().query()

Cursor query(Uri uri, String[] projection, String selection, String[] selectionArgs, String sortOrder);

有没有办法将字符串查询拆分为投影、选择、selectionArgs和SortOrder？

我不希望执行原始查询，所以我更希望有一个解决方案为这个函数与绑定值。

Answer 1

我刚刚写了一个库，它提供了你需要的东西。您只需要在项目中复制和粘贴它，如果您想添加、扩展和自定义它，这取决于您的需求。

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

实例化SQLiteHandler，传递有效的provider_name，并确保您的content_provider在androidmanifest.xml中注册是非常重要的。为了说明它是如何工作的，我们传递三个不同的查询并获得返回值，这些值是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\"");

方法ConvertQueryStringToQueryObject将查询字符串转换为查询类，然后我们可以将该类用于GetContentResolver().query()。

重要说明：因为getContentResolver().query()需要URI。因此，我们需要从table_name创建URI。因此，我们需要将有效的provider_name传递给SQLiteHandler实例。

如您所见，这三个不同的查询被分解成参数，我们可以在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 

在sqlitehandler.java中，有一个exequery方法，它有几个重载。此外，根据不同的输入参数，可以在内容提供程序上设置游标。

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

但是，如果您不想使用exequery，请尝试下面的步骤：

queryObject obj = convertQueryStringToQueryObject(query);
Cursor c = this.getContentResolver().query(obj.uri, obj.projection, obj.selection, obj.selectionArgs, null);