Room数据库使用指北List<>的问题

2018-01-03 本文已影响1298人王诛魔Phy

Room依赖

implementation "android.arch.persistence.room:runtime:1.0.0"
annotationProcessor "android.arch.persistence.room:compiler:1.0.0"

查找

查找表中所有数据

  /**
     * 查找user
     * @return
     */
    @Query("SELECT * FROM banners")
    List<BannerModel> findBanners();

按条件查找

    /**
     * filters
     * @return
     * @param typeSort
     */
    @Query("SELECT * FROM filters WHERE type != :typeSort")
    List<FilterItemModel> findFilters(String typeSort);

限制条数的条件查找

    /**
     * 获取排序
     * @return
     */
    @Query("SELECT * FROM filters WHERE type = :name LIMIT 0,1")
    FilterItemModel findSort(String name);

现在获取的是第一条数据

//文档中的例子

@Dao
public interface MyDao {
    @Query("SELECT first_name, last_name FROM user WHERE region IN (:regions)")
    public List<NameTuple> loadUsersFromRegions(List<String> regions);
}

在user表中查找满足集合List<String> regions中条件的元素

获取总行数

    /**
     * 获取数据库表中总条数
     * @return
     */
    @Query("SELECT count(*) FROM search_history")
    long findHistoryCount();

获取某一列的和

    /**
     * 求和
     * @return
     */
    @Query("SELECT sum(count) FROM status_stock")
    long findStatusStockCount();

按照降序排列(ASC升序 DESC降序)

    /**
     * 查找user
     * @return
     */
    @Query("SELECT * FROM search_history ORDER BY time DESC")
    List<SearchHistoryModel> findSearchHistory();

文档中的例子:

@Dao
public interface MyDao {

    //正常的查询
    @Query("SELECT * FROM user WHERE age != :minAge")
    public User[] loadAllUsersOlderThan(int minAge);
}


@Dao
public interface MyDao {

    //区间中的值
    @Query("SELECT * FROM user WHERE age BETWEEN :minAge AND :maxAge")
    public User[] loadAllUsersBetweenAges(int minAge, int maxAge);

    //或关系的查询
    @Query("SELECT * FROM user WHERE first_name LIKE :search "
           + "OR last_name LIKE :search")
    public List<User> findUserWithName(String search);
}

如果,你不想查到表行中所有字段,则
1.定义一个实体

public class NameTuple {
    @ColumnInfo(name="first_name")
    public String firstName;

    @ColumnInfo(name="last_name")
    public String lastName;
}

2.查询

@Dao
public interface MyDao {
    @Query("SELECT first_name, last_name FROM user")
    public List<NameTuple> loadFullName();
}

多表查询Querying multiple tables

@Dao
public interface MyDao {
   @Query("SELECT user.name AS userName, pet.name AS petName "
          + "FROM user, pet "
          + "WHERE user.id = pet.user_id")
   public LiveData<List<UserPet>> loadUserAndPetNames();

   // You can also define this class in a separate file, as long as you add the
   // "public" access modifier.
   //注意 public
   static class UserPet {
       public String userName;
       public String petName;
   }
}

更新

更新指定条目

    /**
     * 更新时间
     * @param time
     */
    @Query("UPDATE search_history SET time = :time WHERE content = :content")
    void updateSearchHistory(String content,long time);

删除数据

删除所有数据

    /**
     * 删除表
     * @return
     */
    @Query("DELETE FROM filters")
    void delectFilters();

删除最后一条

    /**
     * 删除最后一个
     * @return
     */
    @Query("DELETE FROM search_history WHERE time in(SELECT time FROM search_history ORDER BY time ASC LIMIT 1 )")
    void delectLastHistory();

按条件删除

    /**
     * 删除表by listtype
     * @return
     */
    @Query("DELETE FROM vehicle_model WHERE listType = :type")
    void delectVehicleByType(String type);

补充

1.List<String>这样的元素

   private ArrayList<String> tags;

这样的元素,可以通过Converter搞定


    /**
    * If you have any questions, you can contact by email { wangzhumoo@gmail.com}
    *
    * @author 王诛魔 2017/12/27 上午11:51
    */

    public class TagsConverter {
        @TypeConverter
        public static ArrayList<String> fromString(String value) {
            Type listType = new TypeToken<ArrayList<String>>() {}.getType();
            return new Gson().fromJson(value, listType);
        }

        @TypeConverter
        public static String fromArrayList(ArrayList<String> list) {
            Gson gson = new Gson();
            String json = gson.toJson(list);
            return json;
        }

    }

在db中,会存为

["精选"]
["精选","急售"]
["准新","SUV"]

3.@Embedded

作用是,把已经写好的普通的类添加到数据表中.
用Room的文档做说明:

//这是一个标准的数据表

@Entity
class User {
    @PrimaryKey
    public int id;

    public String firstName;
}

其中只有id , firstName

而我想添加
street;
state;
city;
postCode;
刚好之前写了一个类叫Address

class Address {
    public String street;
    public String state;
    public String city;
    @Ignore
    public String nothing;
    @ColumnInfo(name = "post_code")
    public int postCode;
}

那么我通过 @Embedded

@Entity
class User {
    @PrimaryKey
    public int id;

    public String firstName;

    @Embedded
    public Address address;
}

表中就会有:
names: id, firstName, street, state, city, and post_code

如果你想给@Embedded的类中所有元素前面加上统一的标志

@Embedded(prefix = "address_")

表中就会有:

names: id, firstName, address_street, address_state, address_city, and address_post_code

3.List<Object>这样的数据

解决方法
google developer

真的给谷歌跪了,什么鬼文档

@Entity
public class User {

    // 这里是用户的 Id
    @PrimaryKey
    public int id;

    //省略
    ...
}


@Entity
public class Book {

    //书的id
    @PrimaryKey
    public int bookId;
    
    //这里就是这本书与 User的关系,这里的是User.id
    public int userId;
    public String name;
    public String desc;

}


//注意,什么注解也不要加
public class UserAndBooks {

    //加入所有的user的属性
   @Embedded
   public User user;

    //这里定义User 和 Book的关系
   @Relation(parentColumn = "id", entityColumn = "userId", entity = Book.class)
   public List<Book> books; 

}


//文档上还讲了一个比较牛逼的功能,这里不讨论

public class UserAndAllPets {
   @Embedded
   public User user;
   @Relation(parentColumn = "id", entityColumn = "userId", entity = Book.class,
           projection = {"name"})
   public List<String> bookNames;
 }


//然后,dao是这样的

@Dao
public interface UserDao {
    @Query("SELECT * FROM User")
    public List<UserAndBooks> loadUserWithBooks();
}

Room数据库使用指北List<>的问题

补充

猜你喜欢

热点阅读