使用SQlite数据库将用户名表与安卓工作室中的另一个表关联起来

Question

我想显示用户在特定用户下插入的访客列表，所以我想知道如何将用户ID与访客ID关联起来。

我想把用户id附加到客人id上，请帮我。你能帮助我如何添加外键或第三个表，以及如何使用查询将其关联起来吗。

我的数据库代码如下所示：

package com.mangal.myapplication.ui.utils;
import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;
import com.mangal.myapplication.ui.Models.GuestModel;
import java.util.ArrayList;
import java.util.List;
import java.util.concurrent.CopyOnWriteArrayList;

public class DatabaseHelper extends SQLiteOpenHelper {
//User Login and Register columns
public static final String DB_NAME ="event.db";
public static final String TABLE_NAME="users";
public static final String COL_ID="ID";
// Guest Table columns
private static final String TABLE_NAME_GUESTS="Guests";
private static final String COL_GUESTS_ID="ID_GUESTS";
private static final String COL_NAME_GUESTS="name";
private static final String COL_STATUS_GUESTS="status";
// SQL statement of the guests table creation
private static final String SQL_CREATE_TABLE_GUESTS = "CREATE TABLE " + TABLE_NAME_GUESTS + "("
+ COL_GUESTS_ID + " INTEGER PRIMARY KEY AUTOINCREMENT, "
+ COL_NAME_GUESTS + " TEXT NOT NULL, "
+ COL_STATUS_GUESTS + " INTEGER NOT NULL, "
+");";
public DatabaseHelper(Context context) {
super(context, DB_NAME, null, 1);
}

@Override
public void onCreate(SQLiteDatabase db) {
db.execSQL("create table users(ID INTEGER PRIMARY KEY AUTOINCREMENT, username TEXT, password TEXT)");
db.execSQL(SQL_CREATE_TABLE_GUESTS);
}
@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
db.execSQL("drop table if exists users");
db.execSQL("drop table if exists " + TABLE_NAME_GUESTS);
onCreate(db);

}
public boolean addUser(String username,String pwd){
SQLiteDatabase db= this.getWritableDatabase();
ContentValues contentValues= new ContentValues();
contentValues.put("username",username);
contentValues.put("password",pwd);
long result= db.insert("users",null,contentValues);
if (result==-1) return false;
else return true;
}

public boolean inserttGuest(GuestModel model){
SQLiteDatabase db = this.getWritableDatabase();
ContentValues values = new ContentValues();
values.put(COL_NAME_GUESTS , model.getGuest());
values.put(COL_STATUS_GUESTS , 0);
long res= db.insert(TABLE_NAME , null , values);
if(res == -1){
Log.d("not added","the guest was not added");
return false;
}else {
Log.d("added", "the guest was added");
return true;
}
}

public void updateStatus(int id , int status){
SQLiteDatabase db = this.getWritableDatabase();
ContentValues values = new ContentValues();
values.put(COL_STATUS_GUESTS , status);
db.update(TABLE_NAME , values , "ID=?" , new String[]{String.valueOf(id)});
}

public List<GuestModel> getAllGuests(String User_name){
SQLiteDatabase db = this.getReadableDatabase();
Cursor cursor = null;
List<GuestModel> modelList=new ArrayList<>();

try {
cursor = db.query(TABLE_NAME , null , null , null , null , null , null);
if (cursor !=null){
if (cursor.moveToFirst()){
do {
GuestModel guest= new GuestModel();
guest.setId(cursor.getInt(cursor.getColumnIndex(COL_ID)));
guest.setGuest(cursor.getString(cursor.getColumnIndex(COL_NAME_GUESTS)));
guest.setStatus(cursor.getInt(cursor.getColumnIndex(COL_STATUS_GUESTS)));
modelList.add(task);
}while (cursor.moveToNext());
}
}
}finally {
cursor.close();
}
return modelList;
}
}

Answer 1

由于您从未指定想要如何建立关系，因此我假设您希望将来宾链接到某个用户id首先，要建立基于外键的关系，您需要在sqlite上为每个连接启用外键，因为默认情况下它当前处于禁用状态。

String enable_foreign_keys ="PRAGMA foreign_keys = ON";

其次是如何创建表。客人餐桌变更：

私有静态最终字符串SQL_CREATE_TABLE_GUEST="创建表格"；

• TABLE_NAME_GUESTS+"("；+COL_ GUESTS_；INTEGER PRIMARY KEY AUTOINCREMENT；+COL_NAME_GUESTS+"；TEXT NOT NULL；+COL_STATUS_GUESTS+"；INTEGER NOT NULL+"(">

到：

private static final String SQL_CREATE_TABLE_GUESTS = "CREATE TABLE " +TABLE_NAME_GUESTS + "("+ COL_GUESTS_ID + " INTEGER PRIMARY KEY AUTOINCREMENT, "
+ COL_NAME_GUESTS + " TEXT NOT NULL, "
+ COL_STATUS_GUESTS + " INTEGER NOT NULL, user_id INTEGER NOT NULL,FOREIGN KEY(user_id) REFERENCES users(ID));";

这意味着每次插入具有user_id的guest时，UserID必须存在于users表中如果您想建立多对多关系，可以考虑创建另一个列为userid和guest的表。在那里，许多用户可以链接到一个guest，反之亦然。