java - 如何通过用户输入从sqlite检索数据

Question

目前我正在开发一个小应用程序。我想通过用户输入从 sqlite 检索数据。我有两张表 Gender_Group 和 Foods。我想通过用户输入从这些表中检索数据。
例如，应用程序会要求用户输入输入字段，例如年龄、性别根据这些字段显示数据。当用户插入输入时，应用程序将根据输入字段显示数据。有人可以帮助我，我该如何编写代码来实现上述功能。

public class DBClass extends SQLiteOpenHelper {
    private  static  final String dname= "NutritionDatabase";
    private static final int version =4;


    public DBClass(Context context){

        super(context, dname,null, version);
    }

    @Override
    public void onOpen(SQLiteDatabase db){
        super.onOpen(db);
        db.setForeignKeyConstraintsEnabled(true);
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
        try {
            String gender_cat = "CREATE TABLE  gender_group" +
                    "(id INTEGER PRIMARY KEY," +
                    "name VARCHAR )";
            db.execSQL(gender_cat);

            String foodtbl = "CREATE TABLE Foods" +
                    "(id INTEGER PRIMARY KEY AUTOINCREMENT," +
                    "age VARCHAR ," +
                    "gender_cat INTEGER," +
                    "Food VARCHAR," +
                    "FOREIGN KEY (gender_cat) REFERENCES gender_group(id))";
            db.execSQL(foodtbl);

 InsertGenderCat(1,"Men",db);
            InsertGenderCat(2,"Women",db);
            InsertGenderCat(3,"Toddlers",db);
            InsertGenderCat(4,"Olders",db);     

        insertFoods("12",2,"Calories Required \n 45 to 55 calories per kg.\n\n Protein \n 1 gram per kg. \n\n Breads or Starches. \n 1 bagel or muffin.\n 2 slices bread.\n 1/2 cup cooked cereal, pasta, potatoes, or rice. \n\n Fruits:\n 1/2 cup canned fruit or fruit juice. \n 1 piece fresh fruit, such as an apple, orange, peach, or pear. \n\n 15 to 20 grapes.\n\n Meat or Meat Substitutes:\n 1/2 cup cottage or ricotta cheese. \n 3/4 to 1 cup cooked dried beans or legumes. \n 1 egg.\n 2 to 3 ounces meat, fish, or poultry.\n\n  Milk or Yogurt\n 1 cup  milk or yogurt.\n\n Vegetables:\n 1/2 cup cooked or 1 cup raw vegetable. \n 2 cups salad greens. \n\n Fats\n 6 almonds or 10 peanuts.\n 2 Tbsps cream cheese, avocado, or low calorie salad dressing.\n\nSweets and Desserts .",db);
        insertFoods("13",2,"Calories Required \n 45 to 55 calories per kg.\n\n Protein \n 1 gram per kg. \n\n Breads or Starches. \n 1 bagel or muffin.\n 2 slices bread.\n 1/2 cup cooked cereal, pasta, potatoes, or rice. \n\n Fruits:\n 1/2 cup canned fruit or fruit juice. \n 1 piece fresh fruit, such as an apple, orange, peach, or pear. \n\n 15 to 20 grapes.\n\n Meat or Meat Substitutes:\n 1/2 cup cottage or ricotta cheese. \n 3/4 to 1 cup cooked dried beans or legumes. \n 1 egg.\n 2 to 3 ounces meat, fish, or poultry.\n\n  Milk or Yogurt\n 1 cup  milk or yogurt.\n\n Vegetables:\n 1/2 cup cooked or 1 cup raw vegetable. \n 2 cups salad greens. \n\n Fats\n 6 almonds or 10 peanuts.\n 2 Tbsps cream cheese, avocado, or low calorie salad dressing.\n\nSweets and Desserts .",db);
        insertFoods("14",2,"Calories Required \n 45 to 55 calories per kg.\n\n Protein \n 1 gram per kg. \n\n Breads or Starches. \n 1 bagel or muffin.\n 2 slices bread.\n 1/2 cup cooked cereal, pasta, potatoes, or rice. \n\n Fruits:\n 1/2 cup canned fruit or fruit juice. \n 1 piece fresh fruit, such as an apple, orange, peach, or pear. \n\n 15 to 20 grapes.\n\n Meat or Meat Substitutes:\n 1/2 cup cottage or ricotta cheese. \n 3/4 to 1 cup cooked dried beans or legumes. \n 1 egg.\n 2 to 3 ounces meat, fish, or poultry.\n\n  Milk or Yogurt\n 1 cup  milk or yogurt.\n\n Vegetables:\n 1/2 cup cooked or 1 cup raw vegetable. \n 2 cups salad greens. \n\n Fats\n 6 almonds or 10 peanuts.\n 2 Tbsps cream cheese, avocado, or low calorie salad dressing.\n\nSweets and Desserts .",db);
        insertFoods("15",2,"Total Calories Required \n 40 to 45 calories per kg.\n\n Protein \n 0.9 grams per kg. \n\n Breads or Starches::1/2 cup cottage or ricotta cheese.3/4 to 1 cup cooked dried beans or legumes.\n 1 egg.2 to 3 ounces meat, fish, or poultry.\n  Milk or Yogurt \n 1 cup  milk or yogurt.\n\n Vegetables:\n 1/2 cup cooked or 1 cup raw vegetable.\n 2 cups salad greens.\n\n Fats\n 6 almonds or 10 peanuts.\n 2 Tbsps cream cheese, avocado, or low calorie salad dressing.\n\n Sweets and Desserts .",db);
        insertFoods("16",2,"Total Calories Required \n 40 to 45 calories per kg.\n\n Protein \n 0.9 grams per kg. \n\n Breads or Starches::1/2 cup cottage or ricotta cheese.3/4 to 1 cup cooked dried beans or legumes.\n 1 egg.2 to 3 ounces meat, fish, or poultry.\n  Milk or Yogurt \n 1 cup  milk or yogurt.\n\n Vegetables:\n 1/2 cup cooked or 1 cup raw vegetable.\n 2 cups salad greens.\n\n Fats\n 6 almonds or 10 peanuts.\n 2 Tbsps cream cheese, avocado, or low calorie salad dressing.\n\n Sweets and Desserts .",db);
        insertFoods("17",2,"Total Calories Required \n 40 to 45 calories per kg.\n\n Protein \n 0.9 grams per kg. \n\n Breads or Starches::1/2 cup cottage or ricotta cheese.3/4 to 1 cup cooked dried beans or legumes.\n 1 egg.2 to 3 ounces meat, fish, or poultry.\n  Milk or Yogurt \n 1 cup  milk or yogurt.\n\n Vegetables:\n 1/2 cup cooked or 1 cup raw vegetable.\n 2 cups salad greens.\n\n Fats\n 6 almonds or 10 peanuts.\n 2 Tbsps cream cheese, avocado, or low calorie salad dressing.\n\n Sweets and Desserts .",db);


            }
        catch (SQLException e){
            e.printStackTrace();
        }
}

     private void  InsertGenderCat(Integer id, String group, SQLiteDatabase db){
        ContentValues values = new ContentValues();
        values.put("id",id);
        values.put("name",group);
        db.insert("gender_group", null,values);
    }
    private  void  insertFoods(String age,Integer cat, String food,  SQLiteDatabase db){
     ContentValues values = new ContentValues();

     values.put("age",age);
        values.put("gender_cat",cat);
     values.put("Food",food);
        db.insert("Foods", null,values);
    }
    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {

            String genderTbl  ="DROP TABLE IF EXISTS gender_categories";
        db.execSQL(genderTbl);
        String foodsTbl  ="DROP TABLE IF EXISTS Foods";
        db.execSQL(foodsTbl);
}
}

布局文件

<?xml version="1.0" encoding="utf-8"?>
<LinearLayout xmlns:android="http://schemas.android.com/apk/res/android"
    xmlns:tools="http://schemas.android.com/tools"
    android:layout_width="fill_parent"
    android:layout_height="fill_parent"
    android:fitsSystemWindows="true"
    android:orientation="vertical">

    <TextView
        android:id="@+id/txtBmr"
        android:layout_width="match_parent"
        android:layout_height="wrap_content"
        android:text="Fill The Field  to Find Food"
        android:textAlignment="center"
        android:textColor="@color/colorAccent"
        android:textSize="25dp" />
    <LinearLayout
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:orientation="horizontal"
        android:padding="5dp">

        <TextView
            android:id="@+id/food_gender_group"
            android:layout_width="wrap_content"
            android:layout_height="wrap_content"

            android:padding="18dp"
            android:text="Gender Group :"
            android:textColor="@color/colorAccent"
            android:textSize="20dp"
            android:textStyle="bold" />

        <EditText
            android:id="@+id/edt_gender_group"
            android:layout_width="289dp"
            android:layout_height="wrap_content"
            android:padding="5dp" />
    </LinearLayout>

    <LinearLayout
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:orientation="horizontal"
        android:padding="5dp">

        <TextView
            android:id="@+id/txt_genderGroup_age"
            android:layout_width="wrap_content"
            android:layout_height="wrap_content"

            android:padding="18dp"
            android:text="Enter Age :"
            android:textColor="@color/colorAccent"
            android:textSize="20dp"
            android:textStyle="bold" />

        <EditText
            android:id="@+id/edt_genderGroup_age"
            android:layout_width="289dp"
            android:layout_height="wrap_content"
            android:padding="5dp" />
    </LinearLayout>



    <LinearLayout
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:orientation="horizontal">



    </LinearLayout>

    <LinearLayout xmlns:android="http://schemas.android.com/apk/res/android"
        android:layout_width="match_parent"
        android:layout_height="wrap_content"
        android:orientation="horizontal"
        android:padding="5dp"
        android:weightSum="2">

        <Button
            android:id="@+id/btn_food"
            android:textStyle="bold"
            android:padding="5dp"
            android:layout_width="wrap_content"
            android:layout_marginLeft="23dp"
            android:background="@android:color/black"
            android:textColor="@android:color/white"
            android:layout_height="wrap_content"
            android:layout_marginRight="10dp"
            android:textSize="18dp"
            android:layout_weight="1"
            android:text="Click to show Foods"/>



    </LinearLayout>

</LinearLayout>

Answer 1

首先，获取单击按钮时的两个 editText 值。然后将这两个值传递给 DBManager 类中的 readEntry 函数。

btnFood.setOnClickListener(new View.OnClickListener() {
            @Override
            public void onClick(View v) {
                Cursor cursor = dbManager.open().readEntry(genderGroup.getText().toString(), age.getText().toString());
                if (cursor.moveToFirst()) {
                    do {
                        String value = cursor.getString(0);
                        Toast.makeText(getApplication(),value,Toast.LENGTH_SHORT).show();
                    } while (cursor.moveToNext());
                }

            }
        });

在DBManager类中，在readEntry函数中编写查询我们可以使用Left-join查询来连接两个表。

public class DBManager {

    private DBClass dbhelper;
    private Context context;

    private SQLiteDatabase database;

    public DBManager(Context c) {
        context = c;
    }

    public DBManager open() throws SQLException {
        dbhelper = new DBClass(context);
        database = dbhelper.getWritableDatabase();
        return this;
    }

    public void close() {
        dbhelper.close();
    }


    public Cursor readEntry(String genderGroup, String age) {
        return database.rawQuery("SELECT i.food FROM foods i LEFT JOIN gender_group w ON i.gender_cat = w.id  WHERE w.name = ? AND i.age = ?", new String[]{genderGroup, age}, null);
    } 
}

输出将显示为Toast。

您可以在这里获取完整的项目:https://github.com/tony123S/ProjectApp