时间戳

首页 > 解决方案 > SQLite 从活动中获取用户 ID 和数据

android - SQLite 从活动中获取用户 ID 和数据

问题描述

我在 DataBaseHelper Activity 中有两张表，一张用于用户，一张用于记录来自 5 个不同活动的测量值。我的 DataBaseHelper 有 addreading(); 取数据。这是来自 DataHelper 的代码

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;

import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.List;


public class DatabaseHelper extends SQLiteOpenHelper {

    // Database Version
    private static final int DATABASE_VERSION = 1;

    // Database Name
    private static final String DATABASE_NAME = "UserManager.db";

    // User table name
    private static final String TABLE_USER = "user";

    // Reading table name
    private static final String TABLE_READING = "reading";

    // User Table Columns names
    private static final String COLUMN_USER_ID = "user_id";
    private static final String COLUMN_USER_NAME = "user_name";
    private static final String COLUMN_USER_EMAIL = "user_email";
    private static final String COLUMN_USER_PASSWORD = "user_password";

    //Readings Table Columns names

    private static final String COLUMN_READING_USERREFERENCE = "reading_user_reference";
    private static final String COLUMN_READING_DATETIME = "reading_datetime";
    private static final String COLUMN_READING_WEIGHT = "reading_weight";
    private static final String COLUMN_READING_AGE = "reading_age";
    private static final String COLUMN_READING_HEIGHT = "reading_height";
    private static final String COLUMN_READING_CALORIES_ = "reading_calories";
    private static final String COLUMN_READING_BMR = "reading_bmr";
    private static final String COLUMN_READING_BMI = "reading_bmi";
    private static final String COLUMN_READING_BF = "reading_bf";
    private static final String COLUMN_READING_FFMI = "reading_ffmi";




    // create table users sql query
    private String CREATE_USER_TABLE = "CREATE TABLE " + TABLE_USER + "("
            + COLUMN_USER_ID + " INTEGER PRIMARY KEY AUTOINCREMENT," + COLUMN_USER_NAME + " TEXT,"
            + COLUMN_USER_EMAIL + " TEXT," + COLUMN_USER_PASSWORD + " TEXT" + ")";

    // create table readings sql query
    private static final String CREATE_READING_TABLE =
            "CREATE TABLE IF NOT EXISTS " + TABLE_READING +
                    "(" +
                    COLUMN_READING_USERREFERENCE + " INTEGER NOT NULL, " +
                    COLUMN_READING_DATETIME + " TEXT DEFAULT CURRENT_TIMESTAMP, " +
                    COLUMN_READING_WEIGHT + " INTEGER," +
                    COLUMN_READING_AGE + " INTEGER , "+
                    COLUMN_READING_HEIGHT + " INTEGER," +
                    COLUMN_READING_CALORIES_ + " REAL," +
                    COLUMN_READING_BMR + " REAL," +
                    COLUMN_READING_BMI + " REAL," +
                    COLUMN_READING_BF + " REAL , "+
                    COLUMN_READING_FFMI + " REAL "+
                    ")";


    // drop table sql query
    private String DROP_USER_TABLE = "DROP TABLE IF EXISTS " + TABLE_USER;
    private String DROP_READING_TABLE = "DROP TABLE IF EXISTS " + TABLE_READING;

    /**
     * Constructor
     *
     * @param context
     */
    public DatabaseHelper(Context context) {
        super(context, DATABASE_NAME, null, DATABASE_VERSION);
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
        db.execSQL(CREATE_USER_TABLE);
        db.execSQL(CREATE_READING_TABLE);
    }


    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {

        //Drop User Table if exist
        db.execSQL(DROP_USER_TABLE);
        db.execSQL(DROP_READING_TABLE);

        // Create tables again
        onCreate(db);

    }

    /**
     * This method is to create user record
     *
     * @param user
     */
    public void addUser(User user) {
        SQLiteDatabase db = this.getWritableDatabase();

        ContentValues values = new ContentValues();
        values.put(COLUMN_USER_NAME, user.getName());
        values.put(COLUMN_USER_EMAIL, user.getEmail());
        values.put(COLUMN_USER_PASSWORD, user.getPassword());





        // Inserting Row
        db.insert(TABLE_USER, null, values);
        db.close();
    }
    public long addReading(int userreference, long datetime, int age,int weight,int height, double calories,
            double bmi, double bmr, double bf, double ffmi) {

        SQLiteDatabase db = this.getWritableDatabase();
        ContentValues cv = new ContentValues();
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd'T'HH:mm:ss");

        cv.put(COLUMN_READING_USERREFERENCE,userreference);
        cv.put(COLUMN_READING_DATETIME,sdf.format(datetime));
        cv.put(COLUMN_READING_AGE,age);
        cv.put(COLUMN_READING_WEIGHT,weight);
        cv.put(COLUMN_READING_HEIGHT,height);
        cv.put(COLUMN_READING_CALORIES_,calories);
        cv.put(COLUMN_READING_BMR,bmr);
        cv.put(COLUMN_READING_BMI,bmi);
        cv.put(COLUMN_READING_BF,bf);
        cv.put(COLUMN_READING_FFMI,ffmi);
        return db.insert(TABLE_READING,null,cv) ;
    }

我有两个主要问题。首先是用户在 READINGS TABLE 中作为用户参考键登录后如何使用用户 ID，其次是如何在我的活动中使用 addreading 来获取数据。例如，这是我需要日期、年龄、身高、体重和卡路里结果的卡路里活动。我假设其他字段 (bmr,bmi,bf) 应该默认为 0。

import android.content.Intent;
import android.os.Bundle;
import android.text.TextUtils;
import android.view.Menu;
import android.view.MenuItem;
import android.view.View;
import android.widget.AdapterView;
import android.widget.ArrayAdapter;
import android.widget.Button;
import android.widget.EditText;
import android.widget.RadioButton;
import android.widget.RadioGroup;
import android.widget.Spinner;
import android.widget.TextView;
import android.widget.Toast;
import eu.healthydev.bodymeasure.R;
import java.text.SimpleDateFormat;
import java.util.Calendar;

public class Calories extends BaseActivity implements AdapterView.OnItemSelectedListener{

    Spinner sp, goal_sp;
    double valofactlevel = 0;
    int goalnum = 0;
    Calendar calander;
    SimpleDateFormat simpledateformat;
    String Date;

    @Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_calories);
        TextView cal_pound_to_gram = (TextView)findViewById(R.id.cal_pounds_view);
        EditText weight_num = (EditText) findViewById(R.id.cal_weight_num);

        DatabaseHelper databaseHelper  = new DatabaseHelper(this);
        databaseHelper.addReading(  );




        if (MainActivity.unit_is_gram){
            cal_pound_to_gram.setText("Kilograms");
        }

        if(MainActivity.unit_is_meter){
            TextView cal_inches_to_cent = (TextView)findViewById(R.id.cal_inches_view);
            cal_inches_to_cent.setText("Centimeters");
            //cal_inches_to_cent.setWidth(200);
            EditText cent_num = (EditText)findViewById(R.id.cal_inches_num);
            cent_num.setWidth(595);
            TextView cal_feet_to_meter = (TextView)findViewById(R.id.cal_feet_view);
            cal_feet_to_meter.setText(""); //meters
            EditText cal_disappear = (EditText)findViewById(R.id.cal_feet_num);
            cal_disappear.setVisibility(View.INVISIBLE);


        }

        Button resetButt = (Button)findViewById(R.id.cal_reset_button);
        resetButt.setOnClickListener(
                new Button.OnClickListener() {
                    public void onClick(View v) {

                        EditText weight_num_reset = (EditText) findViewById(R.id.cal_age_num);
                        weight_num_reset.setText(null);

                        EditText waist_num_reset = (EditText) findViewById(R.id.cal_weight_num);
                        waist_num_reset.setText(null);

                        EditText wrist_num_reset = (EditText) findViewById(R.id.cal_feet_num);
                        wrist_num_reset.setText(null);

                        EditText hip_num_reset = (EditText) findViewById(R.id.cal_inches_num);
                        hip_num_reset.setText(null);

                        TextView bf_answer_reset = (TextView) findViewById(R.id.cal_answer);
                        bf_answer_reset.setText("");


                    }
                }
        );


        sp = (Spinner) findViewById(R.id.cal_actlevel_list);
        ArrayAdapter adapter = ArrayAdapter.createFromResource(this,R.array.levelofact,android.R.layout.simple_spinner_item);
        sp.setAdapter(adapter);
        sp.setOnItemSelectedListener(this);

        goal_sp = (Spinner) findViewById(R.id.cal_goal_spinner);
        ArrayAdapter goal_adapter = ArrayAdapter.createFromResource(this,R.array.goalsarr,android.R.layout.simple_spinner_item);
        goal_sp.setAdapter(goal_adapter);
        goal_sp.setOnItemSelectedListener(this);

        Button calculate_calories_button = (Button)findViewById(R.id.calc_calories_button);
        calculate_calories_button.setOnClickListener(
                new Button.OnClickListener() {
                    public void onClick(View v) {


                        EditText age_num = (EditText) findViewById(R.id.cal_age_num);
                        String age_s = (age_num.getText().toString());
                        double age;
                        if(TextUtils.isEmpty(age_s)){
                            age_num.setError("Field can't be empty");
                            age = 0;
                            return;
                        }
                        age = Integer.parseInt(age_num.getText().toString());


                        EditText weight_num = (EditText) findViewById(R.id.cal_weight_num);
                        String weight_s = (weight_num.getText().toString());
                        double weight = 0;
                        if(TextUtils.isEmpty(weight_s)){
                            weight_num.setError("Field can't be empty");
                            weight = 0;
                            return;
                        }
                        weight = Integer.parseInt(weight_num.getText().toString());

                        double feet = 0;
                        if (!MainActivity.unit_is_meter){
                            EditText feet_num = (EditText) findViewById(R.id.cal_feet_num);
                            String feet_s = feet_num.getText().toString();

                            if(TextUtils.isEmpty(feet_s) && !MainActivity.unit_is_meter){
                                feet_num.setError("Field can't be empty");
                                feet = 0;
                                return;
                            }
                            feet = Integer.parseInt(feet_num.getText().toString());
                        }

                        EditText inch_num = (EditText) findViewById(R.id.cal_inches_num);
                        String inch_s = inch_num.getText().toString();
                        double inches;
                        if(TextUtils.isEmpty(inch_s)){
                            inch_num.setError("Field can't be empty");
                            inches = 0;
                            return;
                        }
                        inches = Integer.parseInt(inch_num.getText().toString());

                        RadioGroup rad_group = (RadioGroup)findViewById(R.id.cal_radioGroup);
                        int check = rad_group.getCheckedRadioButtonId();
                        if (check == -1){
                            Toast.makeText(Calories.this, "Select gender", Toast.LENGTH_SHORT).show();
                        }

                        RadioButton fem_button = (RadioButton) findViewById(R.id.cal_fem_button);
                        boolean fem = fem_button.isChecked();

                        RadioButton male_button = (RadioButton) findViewById(R.id.cal_male_button);
                        boolean male = male_button.isChecked();

                        TextView calResult = (TextView) findViewById(R.id.cal_answer);
                        double bmrr = doCalories(fem, male, age, weight, feet, inches);
                        bmrr = (bmrr*valofactlevel)+goalnum;
                        String bmrstring = "";
                        bmrstring = String.valueOf(Math.round(bmrr));
                        if (goalnum == 0){
                            bmrstring = bmrstring.concat(" calories to maintain weight.");
                        }
                        else if (goalnum > 0){
                            bmrstring = bmrstring.concat(" calories to gain weight.");
                        }
                        else if (goalnum < 0){
                            bmrstring = bmrstring.concat(" calories to lose weight.");
                        }

                        if (check != -1){
                            calResult.setText(bmrstring);
                        }


                    }
                }


        );
    }

    public double doCalories(boolean fem, boolean male, double age, double weight, double feet, double inches){

        double bmrResult = 0;
        double height_in_inches;

        if (MainActivity.unit_is_gram){
            weight = weight * 2.2;
        }

        if (MainActivity.unit_is_meter){
            height_in_inches = 0.39370* inches; //actually is 0.39370 * centimeters
        }

        else height_in_inches = (feet*12)+inches;

        if (fem == true) {
            //if it is a female use this formula
            bmrResult = 10*(weight/2.2)+6.25*(height_in_inches*2.54)-5*age-161;
        }

        else if (male == true){
            //if it is a male use this formula

            bmrResult = 10*(weight/2.2)+6.25*(height_in_inches*2.54)-5*age+5;
        }

        calander = Calendar.getInstance();
        simpledateformat = new SimpleDateFormat("dd-MM-yyyy HH:mm:ss");
        Date = simpledateformat.format(calander.getTime());

        return bmrResult;
    }

    @Override
    public void onItemSelected(AdapterView<?> parent, View view, int position, long id) {

        Spinner one = (Spinner)parent;

        if(one.getId() == R.id.cal_actlevel_list){
            TextView myText = (TextView) view;
            if (position == 0) valofactlevel = 1.2;
            else if (position == 1) valofactlevel = 1.375;
            else if (position == 2) valofactlevel = 1.55;
            else if (position == 3) valofactlevel = 1.725;
            else if (position == 4) valofactlevel = 1.9;
        }
        else if(one.getId() == R.id.cal_goal_spinner){
            if (position == 0){
                goalnum = 0;
            }
            else if (position == 1){
                goalnum = -500;
            }
            else if (position == 2){
                goalnum = 500;
            }
        }
    }

    @Override
    public void onNothingSelected(AdapterView<?> parent) {

        //Toast.makeText(this,"Select activity level ",Toast.LENGTH_SHORT).show();

    }


}

考虑到我在 android 中是新手并且在 SQLite 中完全是初学者，我会为您提供任何帮助。来自@MikeT 的出色帮助的 SQLite 部分

标签： androiddatabasesqlite

解决方案

解决方案：

问题1：可以使用user_id作为读表的外键。

FOREIGN KEY (user_id) REFERENCES TABLE_USER(user_id)

问题 2：更新数据，或者您也可以将数据写入读取表

                userId = // id of the user //

                ContentValues Values = new ContentValues();
                Values.put(COLUMN_READING_CALORIES, 500);
                            //...
                getContext().getContentResolver().update(TABLE_READING.CONTENT_URI,
                        Values,
                        TABLE_READING.user_id + " = " + userId,
                        null);

我建议你使用Room lib作为它的新继任者

推荐阅读