Searching and returning results from a database

Question

I am not familiar with databases I have a database file named words_library.db that contains a table named dictionary_lib in which there is two columns one is English_lib and the second is German_lib what I want to achieve is: When the user types a word in the search box and validates a dialog box appears showing results like the following picture (check fig. 1) here's the code I have so far:

Java

db =new DataBaseHelper(getContext());
try {

    db.createDataBase();
    db.openDataBase();

}
catch (Exception e)
{
    e.printStackTrace();
}
namelist=new LinkedHashMap<>();
int ii;
SQLiteDatabase sd = db.getReadableDatabase();
Cursor cursor = sd.query("dictionary_lib" ,null, null, null, null, null, null);
ii=cursor.getColumnIndex("English_lib");
eng_list=new ArrayList<String>();
german_list= new ArrayList<String>();
while (cursor.moveToNext()){
    namelist.put(cursor.getString(ii), cursor.getString(cursor.getColumnIndex("German_lib")));
}
Iterator entries = namelist.entrySet().iterator();
while (entries.hasNext()) {
    Map.Entry thisEntry = (Map.Entry) entries.next();
    eng_list.add(String.valueOf(thisEntry.getKey()));
    german_list.add(String.valueOf(thisEntry.getValue()));
}
for (int i = 0; i < eng_list.size(); i++){
    if (eng_list.contains(text)){
        AlertDialog.Builder builder = new AlertDialog.Builder(getActivity(), R.style.AppCompatAlertDialogStyle);
        builder.setTitle(text);
        builder.setMessage(
                "\n'word' in german: "+german_list.get(i).toString()
        );
        builder.setNegativeButton("CLOSE", new DialogInterface.OnClickListener(){
            public void onClick(DialogInterface dialog, int id){
                Stxt.setBackground(getResources().getDrawable(R.drawable.blue_out_line));
            }
        });
        AlertDialog dialog = builder.create();
        dialog.show();
    }
}

Desired result (fig. 1)

@cricket_007 that is not the problem that code is used in another activity to populate a recyclerview what I want now is to make some changes to that code so it will provide the desired result mentioned above (By the way the only problem I have with that code is that he deletes the same items and displays only one - for example : I have "above" 3 times the code ignores the first two and only shows the last one) — Thorvald, Jun 08 '17 at 01:47
Sorry, I don't understand. If you want database results in the dialog, you need to query the database there... — OneCricketeer, Jun 08 '17 at 04:00
@cricket_007 please check again I edited my question, I hope you can understand what I am trying to do now — Thorvald, Jun 08 '17 at 17:08
Are you sure you want to create `new AlertDialog.Builder` within a for loop? Or are you only trying to get the strings into one single dialog? — OneCricketeer, Jun 08 '17 at 17:38
@cricket_007 only trying to get the strings into one single dialog — Thorvald, Jun 08 '17 at 20:48

OneCricketeer · Answer 1 · 2017-06-08T23:51:28.977

0

I have a database file named words_library.db

It's not really clear how you have that file, or are loading it, but moving on.

When the user types a word in the search box and validates

Is this on a button click? I'll assume it is.

The problem you seem to be having is breaking the problem into smaller steps.

You need to

Find all german words in a database from an english word
Load those words in some form of List. (Just keep a Cursor, you don't need to extract your columns).
Display this list within a Dialog (see AlertDialog.Builder#setCursor)

Each of these should ideally be written as separate methods

The following code is untested, but I believe should be close to what you are looking for.

// Get your database reference
final DataBaseHelper db = new DataBaseHelper(getContext());

// Setup the search button
findViewById(R.id.start_search).setOnClickListener(new View.OnClickListener() {
    @Override
    public void onClick(View v) {
        final String searchQuery = searchBox.getText().toString();
        Cursor germanWordCursor = getGermanWords(db, searchQuery);
        showDialog(String.format("'%s' in German", searchQuery), germanWordCursor);
    }
}); 

// Query the database for the german words from a given english word
private Cursor getGermanWords(DataBaseHelper dbHelper, String englishQuery) {
    final SQLiteDatabase db = dbHelper.getReadableDatabase();

    return db.query("dictionary_lib", 
           null, 
           "English_Lib = ?", 
           new String[] { englishQuery }, 
           null, null, null);
}

// Display a list dialog from a database cursor 
private void showDialog(String title, Cursor cursor) {

  final int theme = R.style.AppCompatAlertDialogStyle;
  final Drawable blueOutLine = getResources().getDrawable(R.drawable.blue_out_line, theme);

  AlertDialog.Builder builder = new AlertDialog.Builder(getActivity(), theme)
      .setTitle(title)
      .setCursor(cursor, null, "German_lib")
      .setNegativeButton("CLOSE", new DialogInterface.OnClickListener(){
          @Override
          public void onClick(DialogInterface dialog, int id){
              Stxt.setBackground(blueOutLine);
            }
      }).show();
}

Note setCursor will require a column named _id in the database table

edited Jun 08 '17 at 23:51

answered Jun 08 '17 at 21:30

OneCricketeer

179,855
19
132
245

that didn't work as expected, and I tried the following approach below the thing is it displays the whole column instead of only the wanted items `for (int i = 0; i < eng_list.size(); i++){ if (eng_list.contains(text)){ temp_list.add(german_list.get(i)); } } AlertDialog.Builder builder = new AlertDialog.Builder(getActivity(), R.style.AppCompatAlertDialogStyle); builder.setTitle(text); builder.setMessage("German:"+temp_list); AlertDialog dialog = builder.create(); dialog.show();` – Thorvald Jun 08 '17 at 22:17
I don't know how you define "wanted items". Tell me the SQL query – OneCricketeer Jun 08 '17 at 22:37
The wanted items are the search result, this is the query (full code mentioned above) `Cursor cursor = sd.query("dictionary_lib" ,null, null, null, null, null, null);` – Thorvald Jun 08 '17 at 22:40
That is `SELECT * from dictionary_lib`... you need to but a `WHERE` condition in there, like I have done in my answer. – OneCricketeer Jun 08 '17 at 22:42
it seems we are getting there, now I get the `java.lang.IllegalArgumentException: column '_id' does not exist` and I don't have that column in my database – Thorvald Jun 08 '17 at 22:55
I think the `query()` method requires that column. – OneCricketeer Jun 08 '17 at 22:59
it appears so, but can it be set to null or something like that ? – Thorvald Jun 08 '17 at 23:13
Actually, it's the `setCursor` method that requires it... That's just a shortcut, though. You're welcome to change it back to `setMessage` – OneCricketeer Jun 08 '17 at 23:39
and what should I exactly put in side the parentheses `.setMessage(/** something here */)` – Thorvald Jun 08 '17 at 23:44
A string? :) something like this, but use the correct column for your case https://stackoverflow.com/a/10081934/2308683 – OneCricketeer Jun 08 '17 at 23:49
You could also try to recreate the database with the appropriate field. https://stackoverflow.com/questions/3192064/about-id-field-in-android-sqlite – OneCricketeer Jun 08 '17 at 23:53

Searching and returning results from a database

1 Answers1