How to get specific column using Android Room

Question

I'm trying to get id column from my database, ad it to ArrayList and to each id add "\t0",

My database is created using Room, i have a lot of column which one of them is

@PrimaryKey(autoGenerate = true)
private int id;

I am operating using ItemDAO and i have there function

@Query("SELECT * FROM item")
List<Item> getItems();

Which writes to ArrayList<Items> all of contents I was thinking of running it trough the loop getting id and adding to ArrayList<String> but this doesn't seems to be eficient.

Hi, welcome to Stackoverflow. Please read https://stackoverflow.com/help/how-to-ask and [edit] your question accordingly. Like: What database (SQlite, Realm, etc), how did the id get into the database? What have you tried so far? — Capricorn, Aug 29 '18 at 13:03

Valgaal · Accepted Answer · 2019-06-13T18:55:35.977

15

Your DAO:

@Query("SELECT Id FROM item")
List<Integer> getAllIds();

Your model:

@ColumnInfo(name = "Id")
@PrimaryKey(autoGenerate = true)
private int id;

In you query SELECT * FROM item * means select All, put there your column name and you will get list of objects from that column

Example: Select all items in id column SELECT id FROM item

edited Jun 13 '19 at 18:55

answered Aug 29 '18 at 13:24

Valgaal

896
10
25

Do i have to write a loop rewriting it to List ? – wokstym Aug 29 '18 at 13:31
Yes, u need to rewrite it, because Room returns int values – Valgaal Aug 29 '18 at 13:44
@Valgaal can you actually write the code for what you mean? – Ali Kazi Dec 16 '18 at 23:14

score 1 · Answer 2 · answered Sep 17 '19 at 08:39

I tried to modify and test @Valgaal 's solution. It turns out that Room can also return other type of values, more than just id (or integer).

For example, you can write an item class like this:

@Entity(tableName = Item.TABLE_NAME)
public class Item {
    public static final String TABLE_NAME = "ItemsTable";
    public static final String COL_DESC = "Description";

    @PrimaryKey(autoGenerate = true)
    private int id;
    @ColumnInfo(name = COL_DESC)
    private String description;

    // getter & setter...
}

And then, you can write Dao like this:

@Dao
public interface ItemDao {
    @Query("SELECT * FROM " + Item.TABLE_NAME)
    List<Item> getItems();

    @Query("SELECT " + Item.COL_DESC + " FROM " + Item.TABLE_NAME)
    List<String> getItemDescriptions();
}

And it's functional as it should be. I guess all of the other data types that Room can save (including custom types?) can be queried (and returned lists of specific column data) by the same logic above. Hope this would help someone in the future!

score 1 · Answer 3 · answered Oct 27 '20 at 17:17

For returning multiple columns, create a pojo class that can be set as a return type for your DAO function

Note the select query should contain the Pojo class variable name (can be done via AS keyword)

Detailed answer here https://stackoverflow.com/a/50802209/1029110

I landed on this question for my issue...but didnt find answer. So this may help others.

How to get specific column using Android Room

3 Answers3