Query to String from Room Database

Question

How do I create a query to my room database from example the SUM of a database column then convert it to a String to then populate text fields in a different activity? I have 5 activities in my app where I wish to customise the data that is displayed but I wish to pull that data from my Room Database.

Many Thanks

Answer 1

If you want a single value then you simply use an @Query annotated method that returns the the single value. e.g.

@Query("SELECT sum(the_column) FROM the_table")
String getTheSum();

• could be an integer type or decimal type, which could be more appropriate
• no need to convert to a string just retrieve as a String
  • However, you may need to format the output e.g. if you wanted a specific precision (round(sum(the_column),2) for 2dp). Some formats could be easier to apply outside of SQLite.

If you wanted many (0-n) you could return a List e.g.

@Query("SELECT  sum(the_column) FROM the_table GROUP BY the_column_or_columns_to_group_by")
List<long> getTheSums();

If you want to return multiple values for row then you need a POJO where there are member variables that match the column names that are output. e.g.

@Query("SELECT sum(the_column) AS theSum, min(the_column) AS theMin, max(the_column) AS theMax FROM the_table GROUP BY the_column_or_columns_to_group_by")
List<ThePojo> getSumMinMax();

Could use the POJO :-

class ThePojo {
   long theSum;
   long theMin;
   long theMax;
}

If you wanted all of the columns PLUS the additional columns you could for example have :-

@Query("SELECT the_table.*,sum(the_column) AS theSum, min(the_column) AS theMin, max(ithe_column) AS theMax FROM cities")
List<ThePojoPlus> getSumMinMaxPlus();

Could the the POJO :-

class ThePojoPlus {
    @Embedded
    TheTable the_table;
    @Embedded
    ThePojo pojo;
}

or (if there is no ThePojo class)

class ThePojoPlus {
   @Embedded
   TheTable the_table;
   long theSum;
   long theMin;
   long theMax;
}