CODEWITHSUNDEEP
androidandroid-sqliteandroid-room
Egis
Egis

Reputation: 5141

Room (SQLite) WHERE clause with null arguments doesn't work

I have entity class Person and I want to query persons from database by feature which may or may not be null. When I set feature to non-null the query works but when the feature is null the query returns empty list. What I am doing wrong?

TEST

Context context = InstrumentationRegistry.getContext();
AppDb db = Room.inMemoryDatabaseBuilder(context, AppDb.class).allowMainThreadQueries().build();
PersonDao dao = db.personDao();

dao.insert(new Person("El Risitas", "Funny"));
dao.insert(new Person("Elon Musk", "Alien"));
dao.insert(new Person("Donald Trump", null));

assertEquals(3, dao.getAll().size());

assertEquals("Funny", dao.getByFeature("Funny").get(0).getFeature());

// fails because dao.getByFeature(null) = EMPTY LIST
assertEquals(null, dao.getByFeature(null).get(0).getFeature());

Person.java

import androidx.room.ColumnInfo;
import androidx.room.Entity;
import androidx.room.PrimaryKey;

@Entity(tableName = "person")
public class Person {

    @ColumnInfo(name = "id") @PrimaryKey(autoGenerate = true) private int id;
    @ColumnInfo(name = "name") private String name;
    @ColumnInfo(name = "feature") private String feature;

    public Person(String name, String feature) {
        this.name = name;
        this.feature = feature;
    }
    public void setId(int id) { this.id = id; }
    public int getId() { return id; }
    public String getName() { return name; }
    public String getFeature() { return feature; }
}

PersonDao.java

import androidx.room.Dao;
import androidx.room.Insert;
import androidx.room.Query;
import java.util.List;

@Dao
public interface PersonDao {

    @Insert
    void insert(Person person);

    @Query("SELECT * FROM person")
    List<Person> getAll();

    @Query("SELECT * FROM person WHERE feature = :feature")
    List<Person> getByFeature(String feature);

}

AppDb.java

import androidx.room.Database;
import androidx.room.RoomDatabase;

@Database(
        version = 1,
        exportSchema = false,
        entities = {Person.class}
)
public abstract class AppDb extends RoomDatabase {

    public abstract PersonDao personDao();
}

Upvotes: 16

Views: 9605

Answers (2)

Remc4
Remc4

Reputation: 1180

You can also use IS operator instead of =.

SELECT * FROM person WHERE feature IS :feature

From SQLite documentation:

The IS and IS NOT operators work like = and != except when one or both of the operands are NULL. In this case, if both operands are NULL, then the IS operator evaluates to 1 (true) and the IS NOT operator evaluates to 0 (false). If one operand is NULL and the other is not, then the IS operator evaluates to 0 (false) and the IS NOT operator is 1 (true). It is not possible for an IS or IS NOT expression to evaluate to NULL.

Upvotes: 12

StackOverthrow
StackOverthrow

Reputation: 1284

In SQL, nothing is ever equal to null. (Nothing is ever not equal to null, either.) You have to use is null. So your query could be something like (untested)

SELECT * FROM person WHERE feature = :feature or (feature is null and :feature is null)

Upvotes: 32

Related Questions