Why is my SQLite query with LIKE not working correctly?

Question

The query with LIKE does not work correctly. The result is given only if you enter an exact query. For example: Request: Invisible- result Invisible, request: Visible - no result. What's my mistake. Thanks a lot in advance.

protected void onCreate(Bundle savedInstanceState) {
    super.onCreate(savedInstanceState);
    setContentView(R.layout.test);

    userFilter = findViewById(R.id.userFilter);
    listView = findViewById(R.id.listView);

    listView.setVisibility(View.INVISIBLE);

    final DatabaseAccessSmeta databaseAccessSmeta = DatabaseAccessSmeta.getInstance(this);
    databaseAccessSmeta.open();
    String poisk = userFilter.getText().toString();
    List<String> quotes = databaseAccessSmeta.Search(poisk);
    databaseAccessSmeta.close();

    final ArrayAdapter<String> adapter = new ArrayAdapter<>(this, R.layout.region_list, quotes);
    listView.setAdapter(adapter);

    userFilter.addTextChangedListener(new TextWatcher() {
        public void afterTextChanged(Editable s) {
            String query = (s != null) ? s.toString() : "";
            if (query.length() >= 3) {
                listView.setVisibility(View.VISIBLE);
                adapter.getFilter().filter(query);
            } else {                   
                listView.setVisibility(View.INVISIBLE);
                adapter.getFilter().filter("");
            }
        }
        public void beforeTextChanged(CharSequence s, int start, int count, int after) { }
        
        public void onTextChanged(CharSequence s, int start, int before, int count) {  }
    });
}

Database query:

public List<String> Search(String poisk) {
    List<String> mylist = new ArrayList<>();
    String query;
    query = "SELECT * FROM smeta_name WHERE rabota LIKE '%"+poisk+"%'";
    this.database = openHelper.getWritableDatabase();
    Cursor cursor = database.rawQuery(query, null);
    cursor.moveToFirst();
    while (!cursor.isAfterLast()) {
        mylist.add(cursor.getString(cursor
                .getColumnIndex("rabota")));
        cursor.moveToNext();
    }
    cursor.close();
    return mylist;
}

for sure you should be careful with possible SQL Injection vulnerability on this code, review https://stackoverflow.com/questions/9076561/android-sqlitedatabase-query-with-like for using parameterized query for your LIKE clause. Please note this doesn't address your question, I think more information is needed. — CSmith, Jun 14 '21 at 13:11
Also note that in `onCreate()`, there's likely nothing yet in the edittext for `getText()` to return. However with an empty input you should be getting everything off the database table. More info needed about the problem you're facing. — laalto, Jun 14 '21 at 14:04

score 0 · Answer 1 · edited Jun 15 '21 at 12:24

0

Used same use using room DAO Below is my Dao class which have list of all queries

@Dao

interface DatabaseDao 

{
    
@Query("SELECT * FROM smeta_name WHERE rabota LIKE  '%'  ||  :title  ||  '%' ")

 fun getAllRides(title: String): LiveData<List<UpcomingResponse>>

}

edited Jun 15 '21 at 12:24

Dharman

30,962
25
85
135

answered Jun 14 '21 at 15:04

Akashi Singh

21
5

Why is my SQLite query with LIKE not working correctly?

1 Answers1