Android中的SQL查询-搜索整个或部分字符串

Question

我对SQL查询没有太多的经验，因此在寻找问题的解决方案时遇到了一些麻烦。

我有书签列表，例如：Facebook的，开发者控制台，堆栈溢出......我也有自己的URL列表：http://m.facebook.com，https://market.android.com。
..

我试图让用户搜索这2个列表并显示任何相似的结果，例如-如果用户搜索了“脸部”，我希望他获得“
Facebook”。并且，如果用户搜索“市场”（针对market.android .. URL），他将获得“开发者控制台”。

这是我的SQL WHERE子句：

String where = Browser.BookmarkColumns.TITLE + " LIKE ? OR "
            + Browser.BookmarkColumns.URL + " LIKE ?";

            where = "(" + where + ") AND " + Browser.BookmarkColumns.BOOKMARK + " == 1";

它工作正常，但只能找到完整的字符串（例如：要找到“ Facebook”，我必须搜索“ Facebook”或“ facebook”）。

希望对此有所帮助！

谢谢，Lior

更新：

相关代码：

//search
            String[] selArgs = new String[] { query };

            String where = Browser.BookmarkColumns.TITLE + " LIKE '%" + query + "%' OR "
            + Browser.BookmarkColumns.URL + " LIKE '%" + query + "%'";

            where = "(" + where + ") AND " + Browser.BookmarkColumns.BOOKMARK + " == 1";

            cur = managedQuery(BOOKMARKS_URI, projection,
                    where, null, Browser.BookmarkColumns.VISITS + " DESC");

Logcat堆栈跟踪：

10-23 00:09:59.765: ERROR/AndroidRuntime(15101): FATAL EXCEPTION: main
10-23 00:09:59.765: ERROR/AndroidRuntime(15101): java.lang.RuntimeException: Unable to start activity ComponentInfo{PACKAGE}: android.database.sqlite.SQLiteException: unrecognized token: "'%face%) AND bookmark == 1) ORDER BY visits DESC": , while compiling: SELECT _id, title, url, visits, favicon, thumbnail, touch_icon, _id AS _id FROM bookmarks WHERE ((title LIKE '%face%' OR url LIKE '%face%) AND bookmark == 1) ORDER BY visits DESC
10-23 00:09:59.765: ERROR/AndroidRuntime(15101):     at android.app.ActivityThread.performLaunchActivity(ActivityThread.java:1768)
10-23 00:09:59.765: ERROR/AndroidRuntime(15101):     at android.app.ActivityThread.handleLaunchActivity(ActivityThread.java:1784)
10-23 00:09:59.765: ERROR/AndroidRuntime(15101):     at android.app.ActivityThread.access$1500(ActivityThread.java:123)
10-23 00:09:59.765: ERROR/AndroidRuntime(15101):     at android.app.ActivityThread$H.handleMessage(ActivityThread.java:939)
10-23 00:09:59.765: ERROR/AndroidRuntime(15101):     at android.os.Handler.dispatchMessage(Handler.java:99)
10-23 00:09:59.765: ERROR/AndroidRuntime(15101):     at android.os.Looper.loop(Looper.java:130)
10-23 00:09:59.765: ERROR/AndroidRuntime(15101):     at android.app.ActivityThread.main(ActivityThread.java:3835)
10-23 00:09:59.765: ERROR/AndroidRuntime(15101):     at java.lang.reflect.Method.invokeNative(Native Method)
10-23 00:09:59.765: ERROR/AndroidRuntime(15101):     at java.lang.reflect.Method.invoke(Method.java:507)
10-23 00:09:59.765: ERROR/AndroidRuntime(15101):     at com.android.internal.os.ZygoteInit$MethodAndArgsCaller.run(ZygoteInit.java:847)
10-23 00:09:59.765: ERROR/AndroidRuntime(15101):     at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:605)
10-23 00:09:59.765: ERROR/AndroidRuntime(15101):     at dalvik.system.NativeStart.main(Native Method)
10-23 00:09:59.765: ERROR/AndroidRuntime(15101): Caused by: android.database.sqlite.SQLiteException: unrecognized token: "'%face%) AND bookmark == 1) ORDER BY visits DESC": , while compiling: SELECT _id, title, url, visits, favicon, thumbnail, touch_icon, _id AS _id FROM bookmarks WHERE ((title LIKE '%face%' OR url LIKE '%face%) AND bookmark == 1) ORDER BY visits DESC
10-23 00:09:59.765: ERROR/AndroidRuntime(15101):     at android.database.DatabaseUtils.readExceptionFromParcel(DatabaseUtils.java:158)
10-23 00:09:59.765: ERROR/AndroidRuntime(15101):     at android.database.DatabaseUtils.readExceptionFromParcel(DatabaseUtils.java:114)
10-23 00:09:59.765: ERROR/AndroidRuntime(15101):     at android.content.ContentProviderProxy.bulkQueryInternal(ContentProviderNative.java:330)
10-23 00:09:59.765: ERROR/AndroidRuntime(15101):     at android.content.ContentProviderProxy.query(ContentProviderNative.java:366)
10-23 00:09:59.765: ERROR/AndroidRuntime(15101):     at android.content.ContentResolver.query(ContentResolver.java:271)
10-23 00:09:59.765: ERROR/AndroidRuntime(15101):     at android.app.Activity.managedQuery(Activity.java:1550)
10-23 00:09:59.765: ERROR/AndroidRuntime(15101):     at PACKAGE.doMySearch(APPNAME.java:725)
10-23 00:09:59.765: ERROR/AndroidRuntime(15101):     at PACKAGE.onCreate(APPNAME.java:150)
10-23 00:09:59.765: ERROR/AndroidRuntime(15101):     at android.app.Instrumentation.callActivityOnCreate(Instrumentation.java:1047)
10-23 00:09:59.765: ERROR/AndroidRuntime(15101):     at android.app.ActivityThread.performLaunchActivity(ActivityThread.java:1722)

Answer 1

您可以尝试以下方法：

String where = Browser.BookmarkColumns.TITLE + " LIKE '%" + x +"%' OR "
            + Browser.BookmarkColumns.URL + " LIKE '%" + x +"%'";
            where = "(" + where + ") AND " + Browser.BookmarkColumns.BOOKMARK + " == 1";

其中x是用户插入的文本，即face。

希望这可以帮助！