时间戳

首页 > 解决方案 > 通过房间数据库处理一对多关系的问题

android - 通过房间数据库处理一对多关系的问题

问题描述

我正在寻找一种使用房间创建一对多关系的方法。问题是获取特定数据。我在官方文档中找到了推荐的方法。

客户类

@Entity(tableName = "customers")
data class Customer(
        val name: String
) {
    @PrimaryKey(autoGenerate = true) val id: Long = 0
}

订单类

@Entity(tableName = "orders")
data class Order(
        val customerId: Long,
        val date: Long,
        val description: String
) {
    @PrimaryKey(autoGenerate = true) val id: Long = 0
}

CustomerWithOrders.class

class CustomerWithOrders() {

    @Embedded
    lateinit var customer: Customer

    @Relation(parentColumn = "id", entityColumn = "customerId", entity = Order::class)
    var orders: List<Order> = arrayListOf()
}

道接口

@Transaction
    @Query("SELECT * FROM customers")
    fun getCustomersWithOrders(): LiveData<List<CustomerWithOrders>>

但是官方文档没有说明如何在保持一对多关系的同时提出有条件的请求。我写了一个示例查询，但它提供了不正确的数据。

查询不正确的数据。

@Transaction
    @Query("SELECT * FROM customers LEFT JOIN orders ON orders.customerId=customers.id WHERE date BETWEEN :dateStart AND :dateEnd")
    fun getCustomersWithOrdersByDate(dateStart: Calendar, dateEnd: Calendar): LiveData<List<CustomerWithOrders>>

请告诉我如何正确完成我的查询或告诉来源执行此类操作的位置。无论如何。谢谢。

标签： androidsqlsqlitekotlinandroid-room

解决方案

您可以使用以下内容作为基础（java 而不是 kotlin）：-

CustomerWithOrders.java

public class CustomerWithOrders {

    Customer customer;
    List<Order> orders;

    //<<<<<<<<<<< this constructor >>>>>>>>>>
    public CustomerWithOrders(Customer customer, List<Order> orders) {
        this.customer = customer;
        this.orders = orders;
    }

    public Customer getCustomer() {
        return customer;
    }

    public void setCustomer(Customer customer) {
        this.customer = customer;
    }

    public List<Order> getOrders() {
        return orders;
    }

    public void setOrders(List<Order> orders) {
        this.orders = orders;
    }
}

结合（在 WHERE 子句中添加日期之间）（在PersonDao.java中）

@Query("SELECT * FROM Orders WHERE customerId=:customerId")
abstract List<Order> getCustomerOrders(long customerId);

然后使用类似的东西： -

    List<Customer> allCustomers = mPDB.personDao().getAllCustomers(); //<<<<<<<< Get the required Customers (all in this example)
    ArrayList<CustomerWithOrders> allCWO = new ArrayList<>(); //<<<<<<<<<< Empty CustomerWithOrders array
    for (Customer c: allCustomers) {
        allCWO.add(new CustomerWithOrders(c,mPDB.personDao().getCustomerOrders(c.getId()))); //<<<<<<<< tie the orders to the customer
    }
    //<<<<<<<<< for demo >>>>>>>>>>
    String TAG = "CWOINFO";
    StringBuilder sb = new StringBuilder();
    for (CustomerWithOrders cwo: allCWO ) {
        sb.append("\nCustomer is " + cwo.getCustomer().getName());
        for (Order o: cwo.getOrders()) {
            sb.append("\n\tOrder is " + o.getDescription());
        }
    }
    Log.d(TAG,sb.toString());

结果（列出的所有订单以供检查）：-

2019-06-29 18:27:49.368 D/ORDERINFO: OrderID = 1Description = Order1 for Customer1Cust ID = 1
2019-06-29 18:27:49.368 D/ORDERINFO: OrderID = 2Description = Order2 for Customer1Cust ID = 1
2019-06-29 18:27:49.368 D/ORDERINFO: OrderID = 3Description = Order3 for Customer1Cust ID = 1
2019-06-29 18:27:49.368 D/ORDERINFO: OrderID = 4Description = Order1 for Customer2Cust ID = 2
2019-06-29 18:27:49.368 D/ORDERINFO: OrderID = 5Description = Order2 for Customer2Cust ID = 2
2019-06-29 18:27:49.368 D/ORDERINFO: OrderID = 6Description = Order3 for Customer2Cust ID = 2
2019-06-29 18:27:49.368 D/ORDERINFO: OrderID = 7Description = Order4 for Customer2Cust ID = 2
2019-06-29 18:27:49.368 D/ORDERINFO: OrderID = 8Description = Order1 for Customer3Cust ID = 3


<<<<<<<<<< The output from the above code >>>>>>>>>>
2019-06-29 18:27:49.377 D/CWOINFO: Customer is Customer1
        Order is Order1 for Customer1
        Order is Order2 for Customer1
        Order is Order3 for Customer1
    Customer is Customer2
        Order is Order1 for Customer2
        Order is Order2 for Customer2
        Order is Order3 for Customer2
        Order is Order4 for Customer2
    Customer is Customer3
        Order is Order1 for Customer3

选择

通过将以下方法添加到CustomerWithOrders.java

    public void addOrder(Order newOrder) {
        this.orders.add(newOrder);
    }

并将以下内容添加到道中：-

@Query("SELECT * FROM customers WHERE id=:customerId")
abstract Customer getCustomerById(long customerId);

@Query("SELECT * FROM orders LEFT JOIN customers ON orders.customerId = customers.id WHERE date BETWEEN :dateStart AND :dateEnd ORDER BY customerId")
abstract List<Order> getOrders(long dateStart, long dateEnd); 

@Transaction
public List<CustomerWithOrders> getCustomersWithOrdersInDateRange(long dateStart, long dateEnd) {
    List<Order> ordersWithCustomer = getOrders(dateStart,dateEnd);
    ArrayList<CustomerWithOrders> cwo = new ArrayList<>();
    long currentCustomerId = 0;
    for (Order o: ordersWithCustomer) {
        if (o.getCustomerId() != currentCustomerId) {
            currentCustomerId = o.getCustomerId();
            cwo.add(new CustomerWithOrders(getCustomerById(o.getCustomerId()),new ArrayList<Order>()));
        }
        cwo.get(cwo.size()-1).addOrder(o);
    }
    return cwo;
}

您可以使用以下代码：-

List<CustomerWithOrders> selectedCustomersWithOrders = mPDB.customerOrderDao().getCustomersWithOrdersInDateRange(0,99999999999999999L);

使用 LiveData（部分示例）

注意没有完全测试，因为我通常allowMainThreadQueries()用于答案

@Query("SELECT * FROM orders LEFT JOIN customers ON orders.customerId = customers.id WHERE date BETWEEN :dateStart AND :dateEnd ORDER BY customerId")
abstract LiveData<List<Order>> getOrders(long dateStart, long dateEnd); //<<<<<<<<<<< LD

@Transaction
public List<CustomerWithOrders> getCustomersWithOrdersInDateRange(long dateStart, long dateEnd) {
    LiveData<List<Order>> ordersWithCustomer = getOrders(dateStart,dateEnd); //<<<<<<<<<<< LD
    ArrayList<CustomerWithOrders> cwo = new ArrayList<>();
    long currentCustomerId = 0;
    for (Order o: ordersWithCustomer.getValue()) { //<<<<<<<<<<< LD
        if (o.getCustomerId() != currentCustomerId) {
            currentCustomerId = o.getCustomerId();
            cwo.add(new CustomerWithOrders(getCustomerById(o.getCustomerId()),new ArrayList<Order>()));
        }
        cwo.get(cwo.size()-1).addOrder(o);
    }
    return cwo;
}
  • 评论表明变化

推荐阅读