如何编写Room Dao查询以基于Android单表中的可选多个库伦来搜索唯一数据? [英] how to write Room Dao query to search unique data based on optional mulitple coulmns in single table in Android?

问题描述

我有5个用于过滤的编辑"输入字段，用户可以在5个编辑"字段中的任何一个中提供输入.基于用户输入，如何使用房间"单个查询在按钮上的单个表中过滤唯一行点击Android吗?

I have 5 Edit input fields for filtering, user can provide input in any of the Edit fields out of 5. Based on user inputs, how can I use a Room single query to filter a unique row in a single table on button click for android?

试图让我有多个通过标准的值，我想要唯一的值

tried this gives me multiple values passing the criteria, I want unique values

@Query("SELECT * from TBL_PHYSICAL_COUNT_LIST where ((SUBINV IS NOT NULL AND SUBINV != '') AND SUBINV=(:subInv)) OR ((LOCATOR IS NOT NULL AND LOCATOR != '') AND LOCATOR=(:locatorNumber)) OR ((ITEMNUMBER IS NOT NULL AND trim(ITEMNUMBER) > '') AND ITEMNUMBER=(:itemNumber))  OR ((LOTNUMBER IS NOT NULL AND LOTNUMBER != '') AND LOTNUMBER=(:lotNumber)) OR ((TAGNUMBER IS NOT NULL AND trim(TAGNUMBER) <> '') AND TAGNUMBER=(:tagNumber))")
List<TBL_PHYSICAL_COUNT_LIST> getTblPhysicalCountListByFilter(String subInv,String locatorNumber,String itemNumber,String lotNumber,String tagNumber) ;

对于EG:说我输入标签号"0601"，商品号="1000004"，查询应该返回图像中的第1个元素，例如，假设用户选择subInventory ='Accessorie'，locatorNumber ='2'，批号='2010，查询应返回图片中的第二个元素，因此基于用户输入，我想要1个查询，该查询应忽略空白输入并根据提供的输入进行过滤.

FOR EG: say I enter tagnumber '0601' and item number='1000004', the query should return 1st element in an image, another eg say the user selects subInventory='Accessorie', locatorNumber='2',lotnumber='2010, the query should return 2nd element in the image and so based on user input I want 1 query that should ignore blank input and filter based on provided input.

/

推荐答案

我通过更新查询来解决了这个问题:

I solved it by updating the query like this:

@Query("SELECT * from TBL_PHYSICAL_COUNT_LIST " +
            "where (:subInv IS NOT NULL AND SUBINV LIKE :subInv || '%') " +
            "AND (:locatorNumber IS NOT NULL AND LOCATOR LIKE :locatorNumber || '%') " +
            "AND (:itemNumber IS NOT NULL AND ITEMNUMBER LIKE :itemNumber || '%') " +
            "AND (:lotNumber IS NOT NULL AND LOTNUMBER LIKE  :lotNumber || '%') " +
            "AND (:tagNumber IS NOT NULL AND TAGNUMBER LIKE  :tagNumber || '%') ")
List<TBL_PHYSICAL_COUNT_LIST> getTblPhysicalCountListByFilter(String subInv,
                                                                  String locatorNumber,
                                                                  String itemNumber,
                                                                  String lotNumber,
                                                                  String tagNumber);

这篇关于如何编写Room Dao查询以基于Android单表中的可选多个库伦来搜索唯一数据?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！