Android room SELECT * WHERE returns all records

Related searches

I've got an Android Room database setup and followed some tutorials with which I'm trying to extend what I've made. I have gotten a few queries to work but when I try to use a SELECT * WHERE query it returns all the records in the table not just the records with the expected ID.

The Query in the DAO

@Query("SELECT * FROM weapon_table WHERE fighter_id = :fighter_id" )
LiveData<List<Weapon>> getFighterWeapons(long fighter_id);

The Record Object

@Entity(tableName = "weapon_table",
        foreignKeys = {
                @ForeignKey(entity = com.Database.Fighter.class,
                        parentColumns = "fighter_id",
                        childColumns = "fighter_id"),
        })
public class Weapon
{
    @NonNull
    String weapon_name;
    @NonNull
    @PrimaryKey(autoGenerate = true)
    long weapon_id;
    @NonNull
    long fighter_id;


    public Weapon(String weapon_name, long fighter_id)
    {
        this.weapon_name = weapon_name;
        this.fighter_id = fighter_id;
    }

    public long getWeapon_id()
    {
        return weapon_id;
    }

    public void setWeapon_id(long weapon_id)
    {
        this.weapon_id = weapon_id;
    }

    public String getWeapon_name()
    {
        return weapon_name;
    }

    public void setWeapon_name(String weapon_name)
    {
        this.weapon_name = weapon_name;
    }

    public long getFighter_id()
    {
        return fighter_id;
    }

    public void setFighter_id(long fighter_id)
    {
        this.fighter_id = fighter_id;
    }
}

When I call the following code all records are printed to the debug console but the current ID is also printed and stays the same in all cases.

   public void onClickAddWeapon(View view)
{
    List<Weapon> weapons = mWeaponViewModel.getFighterWeapons(fighter.getFighter_id()).getValue();
    for(Weapon weapon: weapons )
    {
        Log.d("WEAPONDEBUG", "WEAPONNAME/ID: " +weapon.getWeapon_name() +'/'+ weapon.getFighter_id() );
        Log.d("WEAPONDEBUG", "FIGHTERID: "+ fighter.getFighter_id() );

    }
}

Fix was fairly simple , I had followed this google code labs tutorial and when I tried to extend it I forgot to actually assign the result of the query to the list held in the repository file. Thanks for the suggestions they put me in the right direction.

  LiveData<List<Weapon>> getFighterWeapons(long fighter_id)
    {
        mWeaponDao.getFighterWeapons(fighter_id);
        return mAllWeapons;
    }

replaced with

LiveData<List<Weapon>> getFighterWeapons(long fighter_id)
{
    mAllWeapons = mWeaponDao.getFighterWeapons(fighter_id);
    return mAllWeapons;
}

Accessing data using Room DAOs, @Query("SELECT * FROM user WHERE age BETWEEN :minAge AND :maxAge" ) Room allows you to return any Java-based object from your queries as long as If your app's logic requires direct access to the return rows, you can return a � Room provides the following support for return values of RxJava2 types: @Query methods: Room supports return values of type Publisher, Flowable, and Observable. @Insert, @Update, and @Delete methods: Room 2.1.0 and higher supports return values of type Completable, Single<T>, and Maybe<T>.

your code should be working, but I think that the problem is defining with foreign keys. `

parentColumns = "fighter_id",

childColumns = "fighter_id"

can you remove it and try?

                    `

Query, Room only supports named bind parameter :name to avoid any confusion between the @Query("SELECT * FROM user WHERE user_name LIKE :name AND For single result queries, the return type can be any java object. anything if the value is null (like fetching an Entity row that does not exist). If a Single<T> query returns null, Room will throw EmptyResultSetException. UPDATE or DELETE queries can return void or int. If it is an int, the value is the number of rows affected by this query. You can return arbitrary POJOs from your query methods as long as the fields of the POJO match the column names in the query result.

Try this

@Query("SELECT * FROM weapon_table WHERE fighter_id IN (:fighter_id)" )
LiveData<List<Weapon>> getFighterWeapons(long fighter_id);

androidx.room.Query, For SELECT queries, Room will infer the result contents from the Note that queries which insert multiple rows cannot return more than one� I'm attempting to build a query that will return all non duplicate (unique) records in a table. The query will need to use multiple fields to determine if the records are duplicate. For example, if a table has the following fields; PKID, ClientID, Name, AcctNo, OrderDate, Charge, I'd like to use the AcctNo, OrderDate and Charge fields to find

RawQuery, Room will generate the code based on the return type of the function and If you want to execute a raw query that does not return any value, use getUsers( new SimpleSQLiteQuery("SELECT * FROM User ORDER BY� Here whereClause is optional, passing null will delete all rows in table. delete function will return number of affected row if whereClause passed otherwise will return 0. Important Note: If you want to remove all rows and require count of deleted ones also then pass 1 as whereClause.

Referencing complex data using Room, Overview � Media � Documents and other files This document explains how to use type converters and why Room doesn't return value?.let { Date(it) } @ Query("SELECT * FROM user WHERE birthday BETWEEN :from AND :to") To reference multiple entities at the same time using Room, you instead� This will return 2 rows from DB (and this could be correct, if you just want a distinct sorted list of users) BUT in many cases, you could want an unsorted list of results. You always have to think about it like about a SQL query. Please see the example with eshop shopping cart to illustrate what's going on:

To make use of the Room without abuse of the @Query annotation first use @Query to select all rows and put them in a list, for example: @Query("SELECT * FROM your_class_table") List`<`your_class`>` load_all_your_class(); Put his list into the delete annotation, for example: @Delete. void deleteAllOfYourTable(List`<`your_class`>` your_class_list);

Comments
  • I've removed the foreign keys but still no luck
  • It's still giving me all the records
  • insert a record for different fighter_id and then check
  • I've inserted both another weapon_id and a different fighter_id but still no luck
  • While this results in a valid SQL query, it's bad since a simple = exists for this exact reason.