用SimpleCursorAdapter填充单个TextView，使用多个SQL列（搜索框建议）

huangapple
go评论113阅读模式
英文:

Populate single TextView with multiple SQL columns using SimpleCursorAdapter (Suggestion for searchbox)

问题

  1. public void LoadSuggestions(){
  3.     final String[] from = new String[] {"column1", "column2"};
  4.     final int[] to = new int[] {R.id.suggestion_text};
  6.     suggestionAdapter = new SimpleCursorAdapter(MainActivity.this,
  7.             R.layout.suggestion_row,null, from, to, 0){
  8.         @Override
  9.         public void changeCursor(Cursor cursor) {
  10.             super.swapCursor(cursor);
  11.         }
  12.     };
  14.     search.setSuggestionsAdapter(suggestionAdapter);
  16.     search.setOnSuggestionListener(new SearchView.OnSuggestionListener() {
  17.         @Override
  18.         public boolean onSuggestionClick(int position) {
  19.             CursorAdapter ca = search.getSuggestionsAdapter();
  20.             Cursor cursor = ca.getCursor();
  21.             cursor.moveToPosition(position); 
  22.             String clicked_word = cursor.getString(cursor.getColumnIndex("column1"));
  23.             search.setQuery(clicked_word, false);
  24.             search.clearFocus();
  25.             search.setFocusable(false);
  27.             Intent intent = new Intent(MainActivity.this, WordMeaningActivity.class);
  28.             Bundle bundle = new Bundle();
  29.             bundle.putString("column1", clicked_word);
  30.             intent.putExtras(bundle);
  31.             startActivity(intent);
  33.             return true;
  34.         }
  36.         @Override
  37.         public boolean onSuggestionSelect(int position) {
  38.             return true;
  39.         }
  40.     });
  42.     search.setOnQueryTextListener(new SearchView.OnQueryTextListener() {
  43.         @Override
  44.         public boolean onQueryTextSubmit(String query) {
  45.             String text = search.getQuery().toString();
  47.             Pattern p = Pattern.compile("[A-Za-züÜöÖőŐűŰáÁéÉíÍóÓúÚ \\-.]{1,25}");
  48.             Matcher m = p.matcher(text);
  50.             if(m.matches()) {
  51.                 Cursor c = myDbHelper.getMeaning(text);
  53.                 if(c.getCount() == 0) {
  54.                     showAlertDialog();
  55.                 } else {
  56.                     search.clearFocus();
  57.                     search.setFocusable(false);
  59.                     Intent intent = new Intent(MainActivity.this, WordMeaningActivity.class);
  60.                     Bundle bundle = new Bundle();
  61.                     bundle.putString("column1", text);
  62.                     intent.putExtras(bundle);
  63.                     startActivity(intent);
  64.                 }
  65.             } else {
  66.                 showAlertDialog();
  67.             }
  69.             return false;
  70.         }
  72.         @Override
  73.         public boolean onQueryTextChange(final String s) {
  75.             search.setIconifiedByDefault(false);
  77.             Pattern p = Pattern.compile("[A-Za-züÜöÖőŐűŰáÁéÉíÍóÓúÚ \\-.]{1,25}");
  78.             Matcher m = p.matcher(s);
  80.             if(m.matches()) {
  81.                 Cursor cursorSuggestion = myDbHelper.getSuggestions(s);
  82.                 suggestionAdapter.changeCursor(cursorSuggestion); 
  83.             }
  85.             return false;
  86.         }
  87.     });
  88. }
  1. public Cursor getSuggestions(String text) {
  2.     Cursor c = myDatabase.rawQuery("SELECT _id, column1, column2 FROM words WHERE column1 LIKE '" + text + "%' OR column2 LIKE '" + text + "%' LIMIT 40", null);
  3.     return c;
  4. }

The order of columns in the from array should be kept as {"column1", "column2"} in order to retrieve results from both columns. The provided code already handles combining the results from both columns and displaying them in a single dropdown.

英文:

I'm using SQL as datasource. My app's searchbox has autosuggestion, but currently it's only able to return the results of 1 column (e.g. column1). I want it to be able to search in 2 columns (so, column1 + column2) and show both column's result in the autosuggestion dropdown. This is the code I'm trying to implement the method with, but without any success.

  1. public void LoadSuggestions(){
  2. final String[] from = new String[] {"column1", "column2"};
  3. final int[] to = new int[] {R.id.suggestion_text}; //suggestion_text is the TextView to populate
  4. suggestionAdapter = new SimpleCursorAdapter(MainActivity.this,
  5. R.layout.suggestion_row,null, from, to,0){
  6. @Override
  7. public void changeCursor(Cursor cursor) {
  8. super.swapCursor(cursor);
  9. }
  10. };
  11. search.setSuggestionsAdapter(suggestionAdapter);
  12. search.setOnSuggestionListener(new SearchView.OnSuggestionListener() {
  13. @Override
  14. public boolean onSuggestionClick(int position) {
  15. CursorAdapter ca = search.getSuggestionsAdapter();
  16. Cursor cursor = ca.getCursor();
  17. cursor.moveToPosition(position); 
  18. String clicked_word = cursor.getString(cursor.getColumnIndex("column1"));
  19. search.setQuery(clicked_word, false);
  20. search.clearFocus();
  21. search.setFocusable(false);
  22. Intent intent = new Intent(MainActivity.this, WordMeaningActivity.class);
  23. Bundle bundle = new Bundle();
  24. bundle.putString(("column1",clicked_word);
  25. intent.putExtras(bundle);
  26. startActivity(intent);
  27. return true;
  28. }
  29. @Override
  30. public boolean onSuggestionSelect(int position) {
  31. return true;
  32. }
  33. });
  34. search.setOnQueryTextListener(new SearchView.OnQueryTextListener() {
  35. @Override
  36. public boolean onQueryTextSubmit(String query) {
  37. String text = search.getQuery().toString();
  38. Pattern p = Pattern.compile("[A-Za-züÜöÖőŐűŰáÁéÉíÍóÓúÚ \\-.]{1,25}");
  39. Matcher m = p.matcher(text);
  40. if(m.matches())
  41. {
  42. Cursor c = myDbHelper.getMeaning(text);
  43. if(c.getCount()==0)
  44. {
  45. showAlertDialog();
  46. }
  47. else
  48. {
  49. search.clearFocus();
  50. search.setFocusable(false);
  51. Intent intent = new Intent(MainActivity.this, WordMeaningActivity.class);
  52. Bundle bundle = new Bundle();
  53. bundle.putString(("column1", text);
  54. intent.putExtras(bundle);
  55. startActivity(intent);
  56. }
  57. }
  58. else
  59. {
  60. showAlertDialog();
  61. }
  62. return false;
  63. }
  64. @Override
  65. public boolean onQueryTextChange(final String s) {
  66. search.setIconifiedByDefault(false);
  67. Pattern p = Pattern.compile("[A-Za-züÜöÖőŐűŰáÁéÉíÍóÓúÚ \\-.]{1,25}");
  68. Matcher m = p.matcher(s);
  69. if(m.matches()) {
  70. Cursor cursorSuggestion = myDbHelper.getSuggestions(s);
  71. suggestionAdapter.changeCursor(cursorSuggestion); 
  72. }
  73. return false;
  74. }
  75. });}

and this is the SQL query behind it

  1. public Cursor getSuggestions(String text) {
  2. Cursor c= myDatabase.rawQuery("SELECT _id, column1, column2 FROM words WHERE column1 LIKE '"+text+"%' AND column2 LIKE '"+text+"%' LIMIT 40", null);
  3. return c;}

I think the main problem is here:

  1. final String[] from = new String[] {"column1", "column2"};

because with this order, if I search something, it will return column1's list, but if I change the order from {"column1", "column2"} to {"column2", "column1"}, the autosuggestion will show column2's results only. I understand that I should do something with this line:

  1. final int[] to = new int[] {R.id.suggestion_text};

but I didn't find any detailed info on this, only a solution where the columns would be shown in different rows, but I need both results to show in a single dropdown. Thank you for your time and for reading this!

答案1

得分: 0

好的，经过几天的休息，我现在清楚地看到了实现这一点的方法，就是将这个字符串进行更改：

  1. Cursor c= myDatabase.rawQuery("SELECT _id, column1, column2 FROM words WHERE column1 LIKE '"+text+"%' AND column2 LIKE '"+text+"%' LIMIT 40", null);

更改为：

  1. Cursor c= myDatabase.rawQuery("SELECT _id, column1 FROM words WHERE column1 LIKE '"+text+"%' UNION SELECT _id, column2 FROM words WHERE column2 LIKE '"+text+"%' LIMIT 40", null);

这样，搜索将会在两行中进行，因此建议将从两行中获取并显示。

英文:

Ok, after some days resting I just saw it clear, the way to accomplish this is changing this string:

  1. Cursor c= myDatabase.rawQuery("SELECT _id, column1, column2 FROM words WHERE column1 LIKE '"+text+"%' AND column2 LIKE '"+text+"%' LIMIT 40", null);

To this:

  1. Cursor c= myDatabase.rawQuery("SELECT _id, column1 FROM words WHERE column1 LIKE '"+text+"%' UNION SELECT _id, column2 FROM words WHERE column2 LIKE '"+text+"%' LIMIT 40", null);

This way, the search will happen in both rows, so the suggestions will be taken and shown from both rows.

huangapple
  • 本文由 发表于 2020年4月5日 21:06:52
  • 转载请务必保留本文链接：https://go.coder-hub.com/61043074.html
  • android
  • autosuggest
  • java
  • search-box
  • sqlite
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定