用SimpleCursorAdapter填充单个TextView,使用多个SQL列(搜索框建议)

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

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

问题

public void LoadSuggestions(){

    final String[] from = new String[] {"column1", "column2"};
    final int[] to = new int[] {R.id.suggestion_text};

    suggestionAdapter = new SimpleCursorAdapter(MainActivity.this,
            R.layout.suggestion_row,null, from, to, 0){
        @Override
        public void changeCursor(Cursor cursor) {
            super.swapCursor(cursor);
        }
    };

    search.setSuggestionsAdapter(suggestionAdapter);

    search.setOnSuggestionListener(new SearchView.OnSuggestionListener() {
        @Override
        public boolean onSuggestionClick(int position) {
            CursorAdapter ca = search.getSuggestionsAdapter();
            Cursor cursor = ca.getCursor();
            cursor.moveToPosition(position); 
            String clicked_word = cursor.getString(cursor.getColumnIndex("column1"));
            search.setQuery(clicked_word, false);
            search.clearFocus();
            search.setFocusable(false);

            Intent intent = new Intent(MainActivity.this, WordMeaningActivity.class);
            Bundle bundle = new Bundle();
            bundle.putString("column1", clicked_word);
            intent.putExtras(bundle);
            startActivity(intent);

            return true;
        }

        @Override
        public boolean onSuggestionSelect(int position) {
            return true;
        }
    });

    search.setOnQueryTextListener(new SearchView.OnQueryTextListener() {
        @Override
        public boolean onQueryTextSubmit(String query) {
            String text = search.getQuery().toString();

            Pattern p = Pattern.compile("[A-Za-züÜöÖőŐűŰáÁéÉíÍóÓúÚ \\-.]{1,25}");
            Matcher m = p.matcher(text);

            if(m.matches()) {
                Cursor c = myDbHelper.getMeaning(text);

                if(c.getCount() == 0) {
                    showAlertDialog();
                } else {
                    search.clearFocus();
                    search.setFocusable(false);

                    Intent intent = new Intent(MainActivity.this, WordMeaningActivity.class);
                    Bundle bundle = new Bundle();
                    bundle.putString("column1", text);
                    intent.putExtras(bundle);
                    startActivity(intent);
                }
            } else {
                showAlertDialog();
            }

            return false;
        }

        @Override
        public boolean onQueryTextChange(final String s) {

            search.setIconifiedByDefault(false);

            Pattern p = Pattern.compile("[A-Za-züÜöÖőŐűŰáÁéÉíÍóÓúÚ \\-.]{1,25}");
            Matcher m = p.matcher(s);

            if(m.matches()) {
                Cursor cursorSuggestion = myDbHelper.getSuggestions(s);
                suggestionAdapter.changeCursor(cursorSuggestion); 
            }

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

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.

public void LoadSuggestions(){
final String[] from = new String[] {"column1", "column2"};
final int[] to = new int[] {R.id.suggestion_text}; //suggestion_text is the TextView to populate
suggestionAdapter = new SimpleCursorAdapter(MainActivity.this,
R.layout.suggestion_row,null, from, to,0){
@Override
public void changeCursor(Cursor cursor) {
super.swapCursor(cursor);
}
};
search.setSuggestionsAdapter(suggestionAdapter);
search.setOnSuggestionListener(new SearchView.OnSuggestionListener() {
@Override
public boolean onSuggestionClick(int position) {
CursorAdapter ca = search.getSuggestionsAdapter();
Cursor cursor = ca.getCursor();
cursor.moveToPosition(position); 
String clicked_word = cursor.getString(cursor.getColumnIndex("column1"));
search.setQuery(clicked_word, false);
search.clearFocus();
search.setFocusable(false);
Intent intent = new Intent(MainActivity.this, WordMeaningActivity.class);
Bundle bundle = new Bundle();
bundle.putString(("column1",clicked_word);
intent.putExtras(bundle);
startActivity(intent);
return true;
}
@Override
public boolean onSuggestionSelect(int position) {
return true;
}
});
search.setOnQueryTextListener(new SearchView.OnQueryTextListener() {
@Override
public boolean onQueryTextSubmit(String query) {
String text = search.getQuery().toString();
Pattern p = Pattern.compile("[A-Za-züÜöÖőŐűŰáÁéÉíÍóÓúÚ \\-.]{1,25}");
Matcher m = p.matcher(text);
if(m.matches())
{
Cursor c = myDbHelper.getMeaning(text);
if(c.getCount()==0)
{
showAlertDialog();
}
else
{
search.clearFocus();
search.setFocusable(false);
Intent intent = new Intent(MainActivity.this, WordMeaningActivity.class);
Bundle bundle = new Bundle();
bundle.putString(("column1", text);
intent.putExtras(bundle);
startActivity(intent);
}
}
else
{
showAlertDialog();
}
return false;
}
@Override
public boolean onQueryTextChange(final String s) {
search.setIconifiedByDefault(false);
Pattern p = Pattern.compile("[A-Za-züÜöÖőŐűŰáÁéÉíÍóÓúÚ \\-.]{1,25}");
Matcher m = p.matcher(s);
if(m.matches()) {
Cursor cursorSuggestion = myDbHelper.getSuggestions(s);
suggestionAdapter.changeCursor(cursorSuggestion); 
}
return false;
}
});}

and this is the SQL query behind it

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

I think the main problem is here:

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:

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

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

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

更改为:

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:

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

To this:

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
匿名

发表评论

匿名网友

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

确定