在sqlite表中搜索数组 [英] search an array in sqlite table

问题描述

我有一个这样的表：

      database.execSQL("CREATE  TABLE IF NOT EXISTS food_table (food_id INTEGER PRIMARY KEY  AUTOINCREMENT  NOT NULL , food_name CHAR, food_raw CHAR)");

我有一个这样的字符串数组：

and i have an String array like this:

    public static ArrayList<String> rawsNames = new ArrayList<String>();

现在我要在表中搜索，如果所有生食都在rawsNames中，则返回food_name

now i want to search in my table and if all of raw of food were in rawsNames return the food_name .

我这样做：

String raws = AdapterNotes.rawsNames.toString().replace("]", "").replace("[", "");
Cursor cursor = G.database.rawQuery("select * from food_table WHERE food_raw LIKE '%" + raws + "%' ", null);
    while (cursor.moveToNext()) {
        String name = cursor.getString(cursor.getColumnIndex("food_name"));

但是，如果改变食物的原料，那就行不通了。

But when the food's raw is changed it doesn't work.

请帮助我。

推荐答案

看来， food_table 包含以下字段：

It appears that food_table contains the fields:

food_id ：分配给食品的唯一ID

food_name ：食品名称

food_raw ：配料现在用，

food_id : unique id assigned to a food item
food_name : name of the food item
food_raw : ingredients separated by ,

分隔，因为成分可能是相同 ，但其顺序与存储在数据库中的顺序不同，简单的 %% a，b，c％由于 b，a，c 也是相同食物的有效成分（只是顺序不同）。

Now, since it is possible that the ingredients be the same, but its order be different from that stored in the database, a simple WHERE LIKE %"a, b, c"% won't work as b, a, c are also valid ingredients for the same food (just the order is different).

因此，您需要匹配每个单个成分，而不是整个所有成分：

Hence, you need to match each individual ingredient instead of all the ingredients as a whole:

String req = "";
for(String rawName : rawNames)
  req += " food_raw LIKE %"+rawName+"% AND";
if(req.length() > 4)
  req = req.substring(0, req.length()-4); // to remove last " AND"
String query = "SELECT * FROM food_table WHERE "+req;

使用查询，您应该可以获得食物名称，而不考虑项目顺序。

Using query you should be able to obtain the food name, regardless of item order.

这篇关于在sqlite表中搜索数组的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！