'SQLite don't filter records correctly
I'm developing a dictionary app, i want to store words in a database and if user enters a word, it should be searched in the database and the meaning of it should be displayed. Here is my Activity_Main.xml file:
<?xml version="1.0" encoding="utf-8"?>
<LinearLayout xmlns:android="http://schemas.android.com/apk/res/android"
xmlns:app="http://schemas.android.com/apk/res-auto"
xmlns:tools="http://schemas.android.com/tools"
android:layout_width="match_parent"
android:layout_height="match_parent"
android:orientation="vertical"
android:padding="16dp"
tools:context=".MainActivity">
<EditText
android:id="@+id/word"
android:layout_width="match_parent"
android:layout_height="wrap_content"
android:hint="Word"
android:layout_margin="5dp"
android:inputType="text"/>
<EditText
android:id="@+id/meaning"
android:layout_width="match_parent"
android:layout_height="wrap_content"
android:hint="Meaning"
android:layout_margin="5dp"
android:inputType="text"/>
<Button
android:id="@+id/search"
android:layout_width="match_parent"
android:layout_height="wrap_content"
android:text="search"
android:textSize="18sp"
android:backgroundTint="@color/colorPrimary"
android:textColor="@android:color/white"
android:layout_marginTop="20dp"/>
</LinearLayout>
And my MainActivity.java:
package com.msh.dbms;
import androidx.appcompat.app.AppCompatActivity;
import android.os.Bundle;
import android.view.View;
import android.widget.Button;
import android.widget.EditText;
import android.widget.Toast;
public class MainActivity extends AppCompatActivity{
DbHelper db;
EditText word ,meaning;
Button search;
@Override
protected void onCreate(Bundle savedInstanceState) {
super.onCreate(savedInstanceState);
setContentView(R.layout.activity_main);
db=new DbHelper(this);
db.insertWord("apple","seeb");
db.insertWord("banana","kela");
db.insertWord("cherry","alobalo");
Toast.makeText(this,db.getData(),Toast.LENGTH_SHORT).show();
word = findViewById(R.id.word);
meaning = findViewById(R.id.meaning);
search = findViewById(R.id.search);
search.setOnClickListener(new View.OnClickListener() {
@Override
public void onClick(View v) {
try {
meaning.setText(db.findMeaning(word.getText().toString()));
Toast.makeText(getApplicationContext(), "searched successfully", Toast.LENGTH_SHORT).show();
}
catch (Exception e)
{
Toast.makeText(getApplicationContext(), e.getMessage(), Toast.LENGTH_SHORT).show();
}
}
});
}
}
And my DbHelper.java:
package com.msh.dbms;
import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
public class DbHelper extends SQLiteOpenHelper {
SQLiteDatabase db;
private static final String DATABASE_NAME="englishwords.db";
private static final int DATABASE_VERSION=1;
private static final String TABLE_WORDS="allwords_table";
private static final String COL_WORD="word";
private static final String COL_MEANING="meaning";
public DbHelper(Context context) {
super(context, DATABASE_NAME, null,DATABASE_VERSION);
}
@Override
public void onCreate(SQLiteDatabase db){
String Query_Table=" CREATE TABLE " +TABLE_WORDS+ "("
+COL_WORD+ " TEXT PRIMARY KEY, " +COL_MEANING+ " TEXT);";
db.execSQL(Query_Table);
}
@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
db.execSQL("DROP TABLE IF EXISTS "+TABLE_WORDS);
onCreate(db);
}
public void insertWord(String word, String meaning) {
db=this.getWritableDatabase();
ContentValues values=new ContentValues();
values.put(COL_WORD,word);
values.put(COL_MEANING,meaning);
db.insert(TABLE_WORDS,null,values);
}
public String getData() {
db=this.getReadableDatabase();
String[] columns=new String[] {COL_WORD,COL_MEANING};
Cursor cursor=db.query(TABLE_WORDS,columns,null,null,null,null,null);
int iName= cursor.getColumnIndex(COL_WORD);
int iEmail= cursor.getColumnIndex(COL_MEANING);
String result="";
for(cursor.moveToFirst(); !cursor.isAfterLast(); cursor.moveToNext()){
result=result+
"Name: " +cursor.getString(iName)+"\n"+
"Email: " +cursor.getString(iEmail)+ "\n\n";
}
db.close();
return result;
}
public String findMeaning(String word) {
db=this.getReadableDatabase();
String[] columns=new String[]{COL_WORD,COL_MEANING};
Cursor cursor=db.query(TABLE_WORDS,columns,COL_WORD+"="+word,null,null,null,null);
if(cursor!=null){
cursor.moveToFirst();
String name=cursor.getString(1);
return name;
}
return null;
}
}
I have insertWord() function that adds new word to the database every time onCreate() is called and i have findMeaning() function that searches the database for the word and returns it's meaning but if i run this app, the meaning is not displayed so am i doing something wrong
Solution 1:[1]
You have an issue with the line:-
Cursor cursor=db.query(TABLE_WORDS,columns,COL_WORD+"="+word,null,null,null,null);
The resultant query generated will be, assuming word evaluates to the text myword :-
SELECT word,meaning FROM allwords_table WHERE word = myword;
The issue is that as the value myword is not enclosed in single quotes, and that it is then, in this context, considered as a column name (unless it is a numeric value).
- A string constant is formed by enclosing the string in single quotes ('). A single quote within the string can be encoded by putting two single quotes in a row - as in Pascal. C-style escapes using the backslash character are not supported because they are not standard SQL.
- https://sqlite.org/lang_expr.html#literal_values_constants_
- Note that the following section, section 4, is relevant to using a parameterised query, as is strongly suggested below.
To correct this you could use
Cursor cursor=db.query(TABLE_WORDS,columns,COL_WORD+"='"+"'"+word,null,null,null,null);
BUT this is not considered good practice as it is open to SQL injection.
The recommended way is to utilise the selectionArgs parameter of the query method. This will replace coded ?'s, one by one, with the correctly enclosed value.
So the recommended way would be to use :-
cursor=db.query(TABLE_WORDS,columns,COL_WORD+"=?",new String[]{word},null,null,null);
Furthermore, you have some needles code checking a Cursor for null as it will never be null. If the Cursor is empty then a Cursor with now rows will be returned. You are also not closing the Cursor which if there are too many unclosed Cursors will result in an exception.
As such it is suggested that you consider using:-
public String findMeaning(String word) {
String rv = null;
db=this.getReadableDatabase();
String[] columns=new String[]{COL_WORD,COL_MEANING};
Cursor cursor=db.query(TABLE_WORDS,columns,COL_WORD+"=?",new String[]{word},null,null,null);
if(cursor.moveToFirst(){
rv =cursor.getString(1);
}
cursor.close();
return rv;
}
As can be seen this will
- properly enclose the word to be searched for by passing it as a parameter to SQLite
- not undertake the useless check for a null Cursor
- close the Cursor whether or not a row was returned
- will return null if the word was not found.
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
| Solution | Source |
|---|---|
| Solution 1 |