Android/Room/Sqlite : Get a list of Maximum/Minimum Value of each Column in Room

Question

I have a table in my android application that looks similar to the one given below.

Demo Table

I wish to query the table to get a list of minimum/maximum value of each column in the table.

For example, for the given sample values:

Sample Values

I want the output to be

Maximum: { "Value 1":76.1, "Value 2":1000.0, "Value 3":4897.847, "Value 4":99.0 }

Minimum: { "Value 1":2.3, "Value 2":0.0, "Value 3":9.1, "Value 4":99.0 }

What is the best way to achieve this with Room in Android?

Answer 1

The following is an example that achieves the results that you want.

The Demo Entity (the table) :-

@Entity
class Demo {
    @PrimaryKey
    Long id;
    Double value1;
    Double value2;
    Double value3;
    Double value4;

    Demo(){}

    Demo(Double value1, Double value2, Double value3, Double value4) {
        this.value1 = value1;
        this.value2 = value2;
        this.value3 = value3;
        this.value4 = value4;
    }

    public Long getId() {
        return id;
    }

    public void setId(Long id) {
        this.id = id;
    }

    public Double getValue1() {
        return value1;
    }

    public void setValue1(Double value1) {
        this.value1 = value1;
    }

    public Double getValue2() {
        return value2;
    }

    public void setValue2(Double value2) {
        this.value2 = value2;
    }

    public Double getValue3() {
        return value3;
    }

    public void setValue3(Double value3) {
        this.value3 = value3;
    }

    public Double getValue4() {
        return value4;
    }

    public void setValue4(Double value4) {
        this.value4 = value4;
    }
}

The DemoDao Dao :-

@Dao
interface DemoDao {

    @Insert
    Long insertDemoRow(Demo demo);

    @Query("SELECT '{\"Value 1\":'||max(value1)||', \"Value 2\":'||max(value2)||', \"Value 3\":'||max(value3)||' \"Value 4\":'||max(value4) AS Maximum FROM demo")
    String getMaxvaluesFromDemo();
    @Query("SELECT '{\"Value 1\":'||min(value1)||', \"Value 2\":'||min(value2)||', \"Value 3\":'||min(value3)||' \"Value 4\":'||min(value4) AS Minimum FROM demo")
    String getMinvaluesFromDemo();

}

• It is easier to have separate queries, they could be combined though.

The DemoDatabase abstract class

@Database(entities = {Demo.class},version = 1)
abstract class DemoDatabase extends RoomDatabase {
    abstract DemoDao getDemoDao();
}

And finally an invoking activity (note for convenience and brevity the main thread is used) :-

public class MainActivity extends AppCompatActivity {

    DemoDatabase db;
    DemoDao dao;

    @Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_main);

        db = Room.databaseBuilder(this,DemoDatabase.class,"demo.db")
                .allowMainThreadQueries()
                .build();
        dao = db.getDemoDao();
        dao.insertDemoRow(new Demo(76.1,null,4897.847,null));
        dao.insertDemoRow(new Demo(44.2,87.1,47.0,null));
        dao.insertDemoRow(new Demo(null,1000.0,12.345,null));
        dao.insertDemoRow(new Demo(2.3,0.0,9.1,99.0));

        Log.d("DEMOINFO","Maximum " + dao.getMaxvaluesFromDemo());
        Log.d("DEMOIBFO","Minimums " + dao.getMinvaluesFromDemo());
    }
}

When run (only designed to run once) the result in the log is :-

D/DEMOINFO: Maximum {"Value 1":76.1, "Value 2":1000.0, "Value 3":4897.847 "Value 4":99.0
D/DEMOIBFO: Minimums{"Value 1":2.3, "Value 2":0.0, "Value 3":9.1 "Value 4":99.0