Room SQLite query doesn't return any results, even though there are rows in the database that match

Question

I am using this command to get data from a Room database:

select * from location_search_results where searchQuery = "wilmington"

Here is what the database looks like:

And here are the search results:

I have verified that the name of the table is correct and everything, and there's no spelling errors that I can find, so why would this query not return any of the three rows that it should match?

EDIT for code:

The source code is publicly available here, I am working in the mvvm branch, so if you pull it, make sure you're there. Below are the relevant classes:

LocationSearchResponse.kt:

@Entity(tableName = "location_search_results")
class LocationSearchResponse(
        @ColumnInfo(name = "type")
        val type: String,
        @TypeConverters(DataConverter::class)
        @SerializedName("query")
        val searchQuery: List<String>,
        @TypeConverters(DataConverter::class)
        val features: List<Feature>,
        @ColumnInfo(name = "attribution")
        val attribution: String
) {
    @PrimaryKey(autoGenerate = true)
    var id: Int = 0
}

LocationSearchRepositoryImpl.kt:

class LocationSearchRepositoryImpl (
        private val locationResponseDao: LocationResponseDao,
        private val locationNetworkDataSource: LocationNetworkDataSource
): LocationSearchRepository {

    init {
        locationNetworkDataSource.downloadedLocationSearchResults.observeForever { locationResults ->
            persistFetchedLocations(locationResults)
        }
    }

    // update search data in db if necessary, then return the data that was searched for.
    override suspend fun searchForLocation(query: String): LiveData<out LocationSearchResponse> {
        return withContext(Dispatchers.IO) {
            initSearch(query)
            return@withContext locationResponseDao.searchForLocation(query)
        }
    }

    // if a fetch is necessary (query has not already been searched), fetch search results
    private suspend fun initSearch(query: String) {
        if (isFetchLocationResultsNeeded(query))
            fetchLocationResults(query)
    }

    private fun isFetchLocationResultsNeeded(query: String) : Boolean {
        // get the cached results.  If it's null, return true because it needs to be updated
        val cachedResults = locationResponseDao.searchForLocationNonLive(query.toLowerCase())

        if (cachedResults == null) return true

        // if the results are empty, it needs to be fetched, else it doesn't
        return cachedResults.features.isEmpty()
    }

    private suspend fun fetchLocationResults(query: String) {
        locationNetworkDataSource.fetchLocationSearchResults("mapbox.places", query)
    }

    private fun persistFetchedLocations(fetchedLocationResults: LocationSearchResponse) {
        GlobalScope.launch(Dispatchers.IO) {
            locationResponseDao.upsert(fetchedLocationResults)
        }
    }
}

LocationResponseDao.kt:

@Dao
interface LocationResponseDao {

    // update or insert existing entry if there is a conflict when adding to db
    @Insert(onConflict = OnConflictStrategy.REPLACE)
    fun upsert(locationResults: LocationSearchResponse)

    @Query("select * from location_search_results WHERE searchQuery = :query")
    fun searchForLocation(query: String): LiveData<LocationSearchResponse>

    @Query("select * from location_search_results WHERE searchQuery = :query")
    fun searchForLocationNonLive(query: String): LocationSearchResponse?

    @Query("delete from location_search_results")
    fun nukeTable()
}

and ChooseCityFragment.kt:

class ChooseCityFragment : ScopedFragment(), KodeinAware {

    override val kodein by closestKodein()
    private val locationViewModelFactory: LocationResponseViewModelFactory by instance()
    private val weatherResponseViewModelFactory: WeatherResponseViewModelFactory by instance()

    private lateinit var locationViewModel: LocationResponseViewModel
    private lateinit var weatherViewModel: WeatherResponseViewModel

    override fun onCreateView(inflater: LayoutInflater, container: ViewGroup?,
                              savedInstanceState: Bundle?): View? {

        setupViews()

        // Inflate the layout for this fragment
        return inflater.inflate(R.layout.choose_city_fragment, container, false)
    }

    override fun onActivityCreated(savedInstanceState: Bundle?) {
        super.onActivityCreated(savedInstanceState)
        locationViewModel = ViewModelProviders.of(this, locationViewModelFactory)
                .get(LocationResponseViewModel::class.java)

        weatherViewModel = ViewModelProviders.of(this, weatherResponseViewModelFactory)
                .get(WeatherResponseViewModel::class.java)

        updateToolbar()
    }

    fun updateToolbar() {
        (activity as? AppCompatActivity)?.supportActionBar?.title = "Choose Location"
        (activity as? AppCompatActivity)?.supportActionBar?.subtitle = null
    }

    fun bindUI() = launch {
        val locationResults = locationViewModel.locationResponse
        val owner = viewLifecycleOwner

        locationResults.observe(owner, Observer {
            if (it == null) return@Observer

            // TODO: set loading icon to GONE

            initRecyclerView(it.features.toLocationSearchResultListItem())
        })
    }

    fun setupViews() = launch {
        search_button.setOnClickListener {
            searchLocations()
            search_results_rv.adapter?.notifyDataSetChanged()
        }
    }

    // TODO: search text can not be more than 20 words or more than 256 characters.  Need to account for this
    fun searchLocations() = launch {
        val searchText = search_box.text.toString()

        if (searchText != "") {
            locationViewModel.searchLocation(search_box.text.toString())
            bindUI()
        } else
            Toast.makeText(context?.applicationContext, "Please enter a search term", Toast.LENGTH_SHORT).show()
    }

    private fun List<Feature>.toLocationSearchResultListItem() : List<LocationSearchResultListItem> {
        return this.map {
            LocationSearchResultListItem(it)
        }
    }

    private fun initRecyclerView(items: List<LocationSearchResultListItem>) {
        val groupAdapter = GroupAdapter<ViewHolder>().apply {
            addAll(items)
        }

        groupAdapter.notifyDataSetChanged()

        search_results_rv.apply {
            layoutManager = LinearLayoutManager(this@ChooseCityFragment.context)
            adapter = groupAdapter
        }

        groupAdapter.setOnItemClickListener { item, view ->
            (item as? LocationSearchResultListItem)?.let {
                refreshWeather(it.feature.coordinates[0], it.feature.coordinates[1])
            }
        }
    }

    private fun refreshWeather(latitude: Double, longitude: Double) = launch {
        weatherViewModel.refreshWeatherWithCoordinates(latitude, longitude)
    }
}

Answer 1

It turns out there was a space being added to the end of the searchQuery that I wasn't able to see. Once I figured out where my code was adding that space, I removed it and now everything looks good.

Answer 2

try something like this
it`s a dao interface example
use big letters in your query

@Query("SELECT * FROM person WHERE favoriteColor LIKE :color")
List<Person> getAllPeopleWithFavoriteColor(String color);

more info here