Android Studio how to store API data in room database and retrieve by city name?

Question

Android Studio how to store API data in room database and retrieve by city name? I want to save the data I get from API to the room database. then how can I pull the data by city name? I'm creating an SQLite database in android to store data from JSON in the database to view data if the app is offline.

public interface WeatherAPI {
    @GET("forecast?")
    Single<WeatherModel> getData(@Query("q") String name, @Query("APPID") String app_id);
}

My Model

@Entity(tableName = "Places")
public class WeatherModel  {
    @ColumnInfo(name = "cod")
    @SerializedName("cod")
    public String cod;
    @SerializedName("message")
    public int message;
    @SerializedName("cnt")
    public int cnt;
    @SerializedName("list")
    public List<list> list;
    @SerializedName("city")
    public City city;

    public class Main{
        @SerializedName("temp")
        public double temp;
        @SerializedName("feels_like")
        public double feels_like;
        @SerializedName("temp_min")
        public double temp_min;
        @SerializedName("temp_max")
        public double temp_max;
        @SerializedName("pressure")
        public int pressure;
        @SerializedName("sea_level")
        public int sea_level;
        @SerializedName("humidity")
        public int humidity;
        @SerializedName("temp_kf")
        public double temp_kf;
    }

    public City getCity(){
        return city;
    }

    public void setCity(City city) {
        this.city = city;
    }

    public class Weather{
        @SerializedName("id")
        public int id;
        @SerializedName("main")
        public String main;
        @SerializedName("description")
        public String description;
        @SerializedName("icon")
        public String icon;
    }

    public class Clouds{
        @SerializedName("all")
        public int all;
    }

    public class Wind{
        @SerializedName("speed")
        public double speed;
        @SerializedName("deg")
        public int deg;
        @SerializedName("gust")
        public double gust;
    }

    public class Sys{
        @SerializedName("pod")
        public String pod;
    }

    public class Rain{
        @SerializedName("3h")
        public double _3h;
    }

    public class list{
        @SerializedName("dt")
        public int dt;
        @SerializedName("main")
        public Main main;
        @SerializedName("weather")
        public List<Weather> weather;
        @SerializedName("clouds")
        public Clouds clouds;
        @SerializedName("wind")
        public Wind wind;
        @SerializedName("visibility")
        public int visibility;
        @SerializedName("pop")
        public double pop;
        @SerializedName("sys")
        public Sys sys;
        @SerializedName("dt_txt")
        public String dt_txt;
        @SerializedName("rain")
        public Rain rain;
    }

    public class Coord{
        @SerializedName("lon")
        public double lon;
        @SerializedName("lat")
        public double lat;
    }

    public class City{
        public int id;
        @SerializedName("name")
        public String name;
        public Coord coord;
        public String country;
        public int population;
        public int timezone;
        public int sunrise;
        public int sunset;
    }

    public WeatherModel(String cod, int message, int cnt, List<WeatherModel.list> list, City city) {
        this.cod = cod;
        this.message = message;
        this.cnt = cnt;
        this.list = list;
        this.city = city;
    }
}

@Dao
public interface PlacesDao {

    @Query("SELECT * FROM Places")
    Single<WeatherModel> getAll();

    @Insert
    io.reactivex.Completable insert(WeatherModel weatherModel);

    @Delete
    io.reactivex.Completable delete(WeatherModel weatherModel);

    @Update
    io.reactivex.Completable  upDate(WeatherModel weatherModel);
}

Answer 1

First assuming that you want to store the data as per you model, you will need Type converters for all of the columns in the WeatherModel class (aka the Places table) the that are not types of String, integer types (long, Long, int, Int etc), decimal types (float, double Float, Double etc), or byte arrays; to convert them into such types.

• i.e list and city.

Typically these converters would convert the type into a String as a JSON string (a byte array could potentially be used, it is unlikely (impossible?) to represent such objects as the integer or decimal values).

However, the resultant string, doesn't exactly assist in retrieving by a city name. That is the data in the city column would be something like :-

This is where you may start experiencing difficulties, say you wanted to search for *New York within the above data.

Then simply using a query that uses .... WHERE instr(city,'New York') > 0 , which in itself is beyond where some would be familiar with, could be used e.g. in room it could be :-

@Query("SELECT * FROM places WHERE instr(city,:cityname) > 0")
List<WeatherModel> getByCityNameV1(String cityname);

• WHERE city LIKE '%'||:cityname||'%'" is similar but is not case sensitive unlike the instr function.

and this would get just the 1 row (first) (according to the data above) that has New York.

BUT if the above query were used for York then it would return both New York (first row) and York, the 2nd row.

To definitively find York then you need to search for, at the minimum "York" and hope that there are no other values that could clash, a safer bet would be to search for name":"York",". Now you have introduced the complexity of double quotes which can be problematic, so for this simple task you end of with something along the lines of:-

@Query("SELECT * FROM places WHERE instr(city,'name'||char(34)||':'||char(34)||:cityname||char(34)) > 0")
List<WeatherModel> getByCityNameV2(String cityname);