Weird result when deleting or searching through database

Question

I'm learning how to use SQLite within Android studio and came across a weird issue when trying to delete things from the database. The table currently keeps track of three things, one is an int, and the other two are strings. When I try to delete, I pass the two strings only, since I don't really need the ID. If the strings contain any letters at all, the program fails, but if it consists of only numbers then it works perfectly. For example, if I try to delete something with the strings 456 and 654, then it will find and erase it, but if I try "Book" and "Pen", then it fails.

Also, when I'm searching through the database, if I search for everything at once, it works, but if I make the search more specific, it fails, and I'm not sure why. This is the code in my DataBaseHelper class:

public void RemoveOne(GameInformation gameInformation) {
        SQLiteDatabase db = this.getWritableDatabase();
        String queryString = "delete from " + GAME_TABLE +
                " where " + COLUMN_GAME_NAME + " like " + gameInformation.getName() +
                " and " + COLUMN_GAME_PLATFORM + " like " + gameInformation.getPlatform();
        db.execSQL(queryString);
        db.close();
    }

    public List<GameInformation> GetEverything() {
        String queryString = "select * from " + GAME_TABLE;
        return AddDbToArray(queryString);
    }


    public List<GameInformation> Search(GameInformation gameInformation) {
        String queryString = "select * from " + GAME_TABLE + " where " + COLUMN_GAME_NAME + " like " + gameInformation.getName();
        return AddDbToArray(queryString);
    }

    private List<GameInformation> AddDbToArray(String queryString) {
        List<GameInformation> returnList = new ArrayList<>();
        SQLiteDatabase db = this.getReadableDatabase();
        Cursor cursor = db.rawQuery(queryString, null);
        if (cursor.moveToFirst()) {
            do {
                int gameID = cursor.getInt(0);
                String gameName = cursor.getString(1);
                String platform = cursor.getString(2);
                GameInformation gameInformation = new GameInformation(gameID, gameName, platform);
                returnList.add(gameInformation);
            }
            while (cursor.moveToNext());
        } else {

        }
        cursor.close();
        db.close();
        return returnList;
    }

And this is the code in my MainActivity class:

    removeBtn.setOnClickListener(new View.OnClickListener() {
        @Override
        public void onClick(View v) {
            String gameInput = gameTxt.getText().toString();
            String platformInput = platformTxt.getText().toString();

            GameInformation gameInformation = null;
            try {
                if (!gameInput.equalsIgnoreCase("") && !platformInput.equalsIgnoreCase("")) {
                    gameInformation = new GameInformation(-1, gameInput, platformInput);
                } else {
                    Toast.makeText(MainActivity.this, "You missed some information", Toast.LENGTH_SHORT).show();
                    return;
                }
                dataBaseHelper.RemoveOne(gameInformation);
            } catch (Exception e) {
                Toast.makeText(MainActivity.this, "Something failed " + gameInformation.getName(), Toast.LENGTH_SHORT).show();
            }
            ShowGamesOnListView(dataBaseHelper);
        }
    });
    viewBtn.setOnClickListener(new View.OnClickListener() {
        @Override
        public void onClick(View v) {
            dataBaseHelper = new DataBaseHelper(MainActivity.this);
            ShowGamesOnListView(dataBaseHelper);
        }
    });

    searchBtn.setOnClickListener(new View.OnClickListener() {
        @Override
        public void onClick(View v) {
            dataBaseHelper = new DataBaseHelper(MainActivity.this);
            GameInformation gameInformation = null;
            String platformName = platformTxt.getText().toString();
            String gameName = gameTxt.getText().toString();
            gameInformation = new GameInformation(-1, gameName, platformName);
            try {
                ShowSearchResultListView(dataBaseHelper, gameInformation);
            } catch (Exception e) {
                Toast.makeText(MainActivity.this, "Something went wrong with " + gameName, Toast.LENGTH_SHORT).show();
            }
        }
    });
}

private void ShowSearchResultListView(DataBaseHelper dataBaseHelper, GameInformation gameInformation) {
    gameArrayAdaptor = new ArrayAdapter<GameInformation>(MainActivity.this, android.R.layout.simple_list_item_1, dataBaseHelper.Search(gameInformation));
    dbList.setAdapter(gameArrayAdaptor);
}

private void ShowGamesOnListView(DataBaseHelper dataBaseHelper) {
    gameArrayAdaptor = new ArrayAdapter<GameInformation>(MainActivity.this, android.R.layout.simple_list_item_1, dataBaseHelper.GetEverything());
    dbList.setAdapter(gameArrayAdaptor);
}

Any advice on what I'm getting wrong?

Answer 1

I assume what is failing here is an unknown reference.

This is your query definition:

        String queryString = "delete from " + GAME_TABLE +
                " where " + COLUMN_GAME_NAME + " like " + gameInformation.getName() +
                " and " + COLUMN_GAME_PLATFORM + " like " + gameInformation.getPlatform();

When writing a SQL statement, the syntax looks like this:

SELECT column1, column2, ...
FROM table_name
WHERE columnN LIKE pattern;

For the pattern placeholder you need to write something like this:

WHERE CustomerName LIKE 'a%'    // Finds any values that start with "a"

Note that there are some '' single quotation marks. So inside an SQL query you have to write a string in some kind of string notation.

Now in Android we can not write SQL code like on a command line. So actually we are writing a string in Java (or Kotlin) that exactly follows the SQL syntax. In the end, it is still a string.

You are adding strings like this:

String queryString = "select * from " + GAME_TABLE + " where " + COLUMN_GAME_NAME + " like " + gameInformation.getName();

Where

String gameName = gameTxt.getText().toString();

You are concatenating strings to a string which in the end looks similar to this:

String queryString = "SELECT * FROM game_table_entity_name WHERE column_name LIKE ExampleName;"

Where it actually should look like this:

String queryString = "SELECT * FROM game_table_entity_name WHERE column_name LIKE 'ExampleName';"

So do not forget to add the single quotation marks when you are concatenating strings with double quotation marks. Everything that should be a string (in SQL syntax) needs to be wrapped into these "nested" quotation marks.

Anyways, using the Room Persistence Library is the recommended way to persist app data with an internal SQLite database.