Android Room append String to Select columns

Question

In my table, I have the following structure:

CREATE TABLE "species" (
    "_id"   TEXT NOT NULL,
    "name"  TEXT,
    "name_es"   TEXT,
    "name_it"   TEXT,
    "name_fr"   TEXT,
    "name_de"   TEXT,
    PRIMARY KEY("_id")
);

As you can see, my "name" column is localized in multiple languages appending the _isocode at the end.

I'm trying to figure out how can I manipulate the DAO query in order to append the lang parameter in the Select itself, something like this:

@Query("SELECT name " + lang +
        " FROM species")
List<SpeciesEntity> getSpecies(String lang);

But It simply doesn't work (it doesn't even compile) , I've also tried using the SQLITE concat || like this:

    @Query("SELECT name||:lang " +
        " FROM species")
List<SpeciesEntity> getSpecies(String lang);

But it doesn't work either, it only appends the _es isocode to the actual name column value instead of retrieving the name_es column value. Is there any way to achieve what I need?

Answer 1

SQLite does not support dynamic sql, so that you can't create dynamically the name of the column.

What you can do is use a CASE expression to check the value :lang and return the column that you should select:

SELECT CASE :lang
         WHEN '_es' THEN name_es
         WHEN '_it' THEN name_it
         WHEN '_fr' THEN name_fr
         WHEN '_de' THEN name_de
         ELSE name
       END AS my_name
FROM species

I assume that :lang is a string starting with _, like _xx.