Why doesn't the question mark work in query function?

Question

I have a method:

public int getCountOfFavoriteMovies(){

    Cursor cursor = mDatabase.query(
        MovieDbScheme.NAME,
        null,
        MovieDbScheme.IS_FAVORITE + " = 1",
        null,
        null,
        null,
        null
    );

    try{
        return cursor.getCount();
    }
    finally {
        cursor.close();
    }
}

it returns 7, but if i replace it with the following code:

Cursor cursor = mDatabase.query(
    MovieDbScheme.NAME,
    null,
    MovieDbScheme.IS_FAVORITE + " = ?",
    new String[]{Integer.toString(1)},
    null,
    null,
    null
);

it returns 0 for some reason. Why? Aren't they the same ?

What result do you get if you use MovieDbScheme.IS_FAVORITE + " = '1'"? — forpas
– forpas, Commented Apr 8, 2023 at 10:00
Here's how i created my db: db.execSQL("create table " + MovieDbScheme.NAME + "(" + " _id integer primary key autoincrement, " + MovieDbScheme.MOVIE_ID + "," + MovieDbScheme.IS_FAVORITE + ")" ); — VanechikSpace
– VanechikSpace, Commented Apr 8, 2023 at 10:10

VariabileAleatoria · Accepted Answer · 2023-04-08 10:02:20Z

2

That's because you are comparing it as string and not Integer, so your query will result in a WHERE condition like IS_FAVORITE = '1' instead of IS_FAVORITE = 1

I think you can embed integers in the selection param directly, cause they won't be able to harm with injections.

Probably related: Android Sqlite selection args[] with int values

answered Apr 8, 2023 at 10:02

VariabileAleatoria

1641 silver badge12 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

forpas · Accepted Answer · 2023-04-08 10:50:31Z

Datatypes In SQLite is a complicated concept and you must study it carefully to avoid issues like that.

You have defined the column MovieDbScheme.IS_FAVORITE without any data type and this means that it has no type affinity.

When you use ? placeholders in the selection argument of the query() method then these placeholders will be replaced by the values of selectionArgs as strings (so if you pass 1 it will be replaced by '1').

This means that the comparison that takes place will be among a column with no affinity and a string.
In this case SQLite performs no affinity conversion and 1 = '1' will return false because:

for SQLite INTEGER values are always less than TEXT values

(from Comparison Example)

What you should have done is define the column MovieDbScheme.IS_FAVORITE as INTEGER (or BOOLEAN if your SQLite version is 3.23.0+), in which case SQLite will perform affinity conversion and will compare both operands of the expression 1 = '1' as integers.

See the demo.

So, uninstall the app from the device where you try your app, change your code to:

MovieDbScheme.IS_FAVORITE + " INTEGER"

in the CREATE TABLE statement and rerun.

Collectives™ on Stack Overflow

Why doesn't the question mark work in query function?

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related