如何在Android Sqlite中关联两个表？

Question

我需要帮助，以使用SQLite在Android Studio中的两个表之间建立关系。我有一个表是给用户的，另一个是用于用户可以保存的联系人，

user_table(userId, fullName, email, password, phoneNumber)
contacts_table(contactId, contactName, contactPhoneNumber)

我需要让用户输入尽可能多的联系人，因此，当他登录到会话时，将显示所有存储的联系人那个用户。 我无法弄清楚如何在表之间建立关系，然后结束它创建两个分隔的表，因此不同的用户可以看到相同的联系人。

Answer 1

您说“用户输入任意数量的联系人“ ，然后说”，因此不同的用户可以看到相同的联系人” 。

在这种情况下，可以使用许多人的关系。

这涉及使用 3rd表将用户映射到合同以及与用户的联系。因此，用户可以拥有许多联系人，并且联系人可以有很多用户。

这样的表有许多名称，例如关联表，映射表，参考表。...

这样的表具有两个列（或更多），其中一列将参考/地图保存给用户，另一个则是参考/参考/参考/映射到联系人。地图/参考是唯一标识用户以及联系人的东西。看来UserID和ContactID可能具有此属性。

但是，您可以例如

CREATE TABLE IF NOT EXISTS user_contact_map (userId_map INTEGER, contactId_map INTEGER, PRIMARY KEY(userId_map, contactId_map));

，这很容易引起参考/映射。...不正确，因此您可以通过使用外键约束来引入规则，从而强制执行引用（参考完整性）的完整性（参考完整性）。

因此，例如，例如，您可以说： -

CREATE TABLE IF NOT EXISTS user_contact_map (
    userId_map INTEGER REFERENCES user_table(userId) ON DELETE CASCADE ON UPDATE CASCADE, 
    contactId_map INTEGER REFERENCES contacts_table(contactId) ON DELETE CASCADE ON UPDATE CASCADE, 
    PRIMARY KEY(userId_map, contactId_map)
);

例如，这说明userId_map列的值必须是User_table的用户ID列中存在的值，否则会引发错误。

除了此相反/规则外，删除级联（还有其他选项，但级联选项可能是最有用的），如果将父母（分别分别为user_table行或contacts_table行分别删除），则将删除将删除给孩子们，并且因此，将删除USER_CONTACT_MAP中的行。

如果是“更新级联”，则如果更改了UserID或ContactID，则将更改将其级联归为user_contact_map表中的孩子。

作为上述功能的示例考虑： -

DROP TABLE IF EXISTS user_contact_map;
DROP TABLE IF EXISTS user_table;
DROP TABLE IF EXISTS contacts_table;
CREATE TABLE IF NOT EXISTS user_table(userId INTEGER PRIMARY KEY, fullName TEXT, email TEXT, password TEXT, phoneNumber TEXT);
CREATE TABLE IF NOT EXISTS contacts_table (contactId INTEGER PRIMARY KEY, contactName TEXT, contactPhoneNumber TEXT);
CREATE TABLE IF NOT EXISTS user_contact_map (userId_map INTEGER, contactId_map INTEGER, PRIMARY KEY(userId_map, contactId_map));


INSERT OR IGNORE INTO user_table VALUES 
    (1,'Fred Bloggs','[email protected]','pasword','+00 123 456 789')
    ,(2,'Mary Bloggs','[email protected]','pasword','+00 231 456 789')
    ,(3,'Jane Bloggs','[email protected]','pasword','+00 321 456 789')
    ,(4,'John Bloggs','[email protected]','pasword','+00 000 456 789')
;
INSERT OR IGNORE INTO contacts_table VALUES
    (1,'C1','+00 987 654 321')
    ,(2,'C2','+00 987 654 321')
    ,(3,'C3','+00 987 654 321')
    ,(4,'C4','+00 987 654 321')
    ,(5,'C5','+00 987 654 321')
    ,(6,'C6','+00 987 654 321')
    ,(7,'C7','+00 987 654 321')
;


INSERT OR IGNORE INTO user_contact_map VALUES
    (1,6),(1,4),(1,2) /* 2 contacts for Fred */
    ,(2,1),(2,3),(2,5),(2,7) /* the other 4 contacts for Mary */
    ,(3,1),(3,2),(3,3),(3,4),(3,5),(3,6),(3,7) /* Jane has every contact */
    /* John has no contacts */
;

SELECT * FROM user_table 
    JOIN user_contact_map ON user_table.userId = user_contact_map.userId_map
    JOIN contacts_table ON user_contact_map.contactId_map = contacts_table.contactId
;

此运行将导致： -

• 可以看出，弗雷德（Fred）有预期的3行（联系人） 4为玛丽，珍妮（Jane）为7，约翰（John）为0。

例如，您也可以使用类似的内容： -

SELECT user_table.*,count(*) AS number_of_contacts, group_concat(contactName)
FROM user_table
    JOIN user_contact_map ON user_table.userId = user_contact_map.userId_map
    JOIN contacts_table ON user_contact_map.contactId_map = contacts_table.contactId
GROUP BY userId
;

Android 在Android 中工作示例

以下是一个工作示例，反映了上面显示的内容。最终在两个结果（上面的屏幕图像）中，但写入日志。

首先是 databasehelper （扩展了SQLiteOpenhelper的类），该代码与创建表的数据库相关的所有代码，为插入数据插入以及返回光标的两个提取物提供功能： -

class DatabaseHelper extends SQLiteOpenHelper {

   public static final String DATABASE_NAME = "the_database.db";
   public static final int DATABASE_VERSION = 1;

   public static final String TABLENAME_USER = "user_table";

   public static final String USERID_COLUMN = "userId";
   public static final String FULLNAME_COLUMN = "fullName";
   public static final String EMAIL_COLUMN = "email";
   public static final String PASSWORD_COLUMN = "password";
   public static final String PHONENUMBER_COLUMN = "phoneNumber";
   private static final String TABLECREATESQL_USER = "CREATE TABLE IF NOT EXISTS " + TABLENAME_USER +
           "(" +
           USERID_COLUMN + " INTEGER PRIMARY KEY" +
           "," + FULLNAME_COLUMN + " TEXT" +
           "," + EMAIL_COLUMN + " TEXT" +
           "," + PASSWORD_COLUMN + " TEXT" +
           "," + PHONENUMBER_COLUMN + " TEXT" +
           ");";

   public static final String TABLENAME_CONTACTS = "contacts_table";
   public static final String CONTACTID_COLUMN = "contactId";
   public static final String CONTACTNAME_COLUMN = "contactName";
   public static final String CONTACTPHONENUMBER_COLUMN = "contactPhoneNumber";
   private static final String TABLECREATESQL_CONTACTS = "CREATE TABLE IF NOT EXISTS " + TABLENAME_CONTACTS +
           "(" +
           CONTACTID_COLUMN + " INTEGER PRIMARY KEY" +
           "," + CONTACTNAME_COLUMN + " TEXT" +
           "," + CONTACTPHONENUMBER_COLUMN + " TEXT" +
           ");";

   public static final String TABLENAME_USER_CONTACT_MAP = "user_contact_map";
   public static final String USERIDMAP_COLUMN = USERID_COLUMN + "_map";
   public static final String CONTACTIDMAP_COLUMN = CONTACTID_COLUMN + "_map";
   private static final String TABLECREATESQL_USER_CONTACT_MAP = "CREATE TABLE IF NOT EXISTS " + TABLENAME_USER_CONTACT_MAP +
           "(" +
           USERIDMAP_COLUMN + " INTEGER REFERENCES " + TABLENAME_USER + "(" + USERID_COLUMN + ") ON DELETE CASCADE ON UPDATE CASCADE" +
           "," + CONTACTIDMAP_COLUMN + " INTEGER REFERENCES " + TABLENAME_CONTACTS +"(" + CONTACTID_COLUMN + ") ON DELETE CASCADE ON UPDATE CASCADE " +
           ", PRIMARY KEY (" + USERIDMAP_COLUMN + "," + CONTACTIDMAP_COLUMN + ")" +
           ");";

   private static String fromClause = TABLENAME_USER +
           " JOIN " + TABLENAME_USER_CONTACT_MAP +
           " ON " + TABLENAME_USER + "." + USERID_COLUMN + "=" + TABLENAME_USER_CONTACT_MAP + "." + USERIDMAP_COLUMN +
           " JOIN " + TABLENAME_CONTACTS +
           " ON " + TABLENAME_USER_CONTACT_MAP + "." + CONTACTIDMAP_COLUMN + "=" + TABLENAME_CONTACTS + "." + CONTACTID_COLUMN;

   private volatile static DatabaseHelper INSTANCE = null;
   private SQLiteDatabase db;
   private DatabaseHelper(Context context) {
      super(context,DATABASE_NAME,null,DATABASE_VERSION);
      db = this.getWritableDatabase();
   }
   public static DatabaseHelper getInstance(Context context) {
      if (INSTANCE == null) {
         INSTANCE = new DatabaseHelper(context);
      }
      return INSTANCE;
   }

   @Override
   public void onCreate(SQLiteDatabase db) {
      db.execSQL(TABLECREATESQL_USER);
      db.execSQL(TABLECREATESQL_CONTACTS);
      db.execSQL(TABLECREATESQL_USER_CONTACT_MAP);

   }

   @Override
   public void onUpgrade(SQLiteDatabase db, int i, int i1) {

   }

   public long insertUser(Long id, String fullName, String email, String password, String phoneNumber ) {
      ContentValues cv = new ContentValues();
      if (id != null) {
         cv.put(USERID_COLUMN,id);
      }
      cv.put(FULLNAME_COLUMN,fullName);
      cv.put(EMAIL_COLUMN,email);
      cv.put(PASSWORD_COLUMN,password);
      cv.put(PHONENUMBER_COLUMN,phoneNumber);
      return db.insert(TABLENAME_USER,null,cv);
   }
   public long insertUser(String fullName, String email, String password, String phoneNumber) {
      return insertUser(null,fullName,email,password,phoneNumber);
   }

   public long insertContact(Long id, String contactName, String contactPhoneNumber) {
      ContentValues cv = new ContentValues();
      if (id != null) {
         cv.put(CONTACTID_COLUMN,id);
      }
      cv.put(CONTACTNAME_COLUMN,contactName);
      cv.put(CONTACTPHONENUMBER_COLUMN,contactPhoneNumber);
      return db.insert(TABLENAME_CONTACTS,null,cv);
   }
   public long insertContact(String contactName, String contactPhoneNumber) {
      return insertContact(null,contactName,contactPhoneNumber);
   }

   public long insertUserContactMap(long userId, long contactId) {
      long rv = -1;
      ContentValues cv = new ContentValues();
      cv.put(USERIDMAP_COLUMN,userId);
      cv.put(CONTACTIDMAP_COLUMN,contactId);
      try {
         rv = db.insertOrThrow(TABLENAME_USER_CONTACT_MAP,null,cv);
      } catch (SQLException e) {
         rv = -99; /* ooops likely FK constraint conflict */
      }
      return rv;
   }

   public Cursor getAllUserContactRows() {

      return db.query(fromClause,null,null,null,null,null,null);
   }
   public Cursor getAllUsersWithContactCountAndContactIdCSV() {
      String columns = TABLENAME_USER + ".*," + " count(*) AS number_of_contacts, group_concat(" + CONTACTNAME_COLUMN + ")";
      return db.query(fromClause,new String[]{columns},null,null,USERID_COLUMN,null,null);
   }
}

第二是一种活动 MainAttivity ，它插入数据，然后提取数据库helper的数据： -

public class MainActivity extends AppCompatActivity {

    DatabaseHelper dbhelper;

    @SuppressLint("Range")
    @Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_main);
        dbhelper = DatabaseHelper.getInstance(this);

        long fredBloggsId = dbhelper.insertUser("Fred Bloggs","[email protected]","password","+00 123 456 789");
        long maryBloggsId = dbhelper.insertUser("Mary Bloggs","[email protected]","password","+00 231 456 789");
        long janeBloggsId = dbhelper.insertUser("Jane Bloggs","[email protected]","password","+00 312 456 789");
        long johnBloggsId = dbhelper.insertUser("John Bloggs","[email protected]","password","+00 456 456 789");

        long c1 = dbhelper.insertContact("C1","+00 987 654 321");
        long c2 = dbhelper.insertContact("C2","+00 987 654 321");
        long c3 = dbhelper.insertContact("C3","+00 987 654 321");
        long c4 = dbhelper.insertContact("C4","+00 987 654 321");
        long c5 = dbhelper.insertContact("C5","+00 987 654 321");
        long c6 = dbhelper.insertContact("C6","+00 987 654 321");
        long c7 = dbhelper.insertContact("C7","+00 987 654 321");

        dbhelper.insertUserContactMap(fredBloggsId,c6);
        dbhelper.insertUserContactMap(fredBloggsId,c4);
        dbhelper.insertUserContactMap(fredBloggsId,c2);
        dbhelper.insertUserContactMap(maryBloggsId,c1);
        dbhelper.insertUserContactMap(maryBloggsId,c3);
        dbhelper.insertUserContactMap(maryBloggsId,c5);
        dbhelper.insertUserContactMap(maryBloggsId,c7);
        dbhelper.insertUserContactMap(janeBloggsId,c1);
        dbhelper.insertUserContactMap(janeBloggsId,c2);
        dbhelper.insertUserContactMap(janeBloggsId,c3);
        dbhelper.insertUserContactMap(janeBloggsId,c4);
        dbhelper.insertUserContactMap(janeBloggsId,c5);
        dbhelper.insertUserContactMap(janeBloggsId,c6);
        dbhelper.insertUserContactMap(janeBloggsId,c7);

        if (dbhelper.insertUserContactMap(10000,-34526) < 0) {
            Log.d("DBINFO","Failed to insert into user contact map result code!!!!");
        }

        logCursorInfo(dbhelper.getAllUserContactRows(),true);
        logCursorInfo(dbhelper.getAllUsersWithContactCountAndContactIdCSV(),true);

    }

    @SuppressLint("Range")
    private void logCursorInfo(Cursor csr, boolean closeCursorWhenDone) {
        StringBuilder sb = new StringBuilder();
        while (csr.moveToNext()) {
            boolean firstColumn = true;
            sb = new StringBuilder();
            for (String column : csr.getColumnNames()) {
                if (!firstColumn) {
                    sb.append(" : ");
                }
                firstColumn = false;
                sb.append(column).append(" is " + csr.getString(csr.getColumnIndex(column)));
            }
            Log.d("DBINFO", sb.toString());
        }
        if (closeCursorWhenDone) {
            csr.close();
        }
    }
}

当运行（第一次）时（首次运行），然后根据日志，结果为： -

2022-05-25 09:19:38.488 D/DBINFO: userId is 1 : fullName is Fred Bloggs : email is [email protected] : password is password : phoneNumber is +00 123 456 789 : userId_map is 1 : contactId_map is 6 : contactId is 6 : contactName is C6 : contactPhoneNumber is +00 987 654 321
2022-05-25 09:19:38.488 D/DBINFO: userId is 1 : fullName is Fred Bloggs : email is [email protected] : password is password : phoneNumber is +00 123 456 789 : userId_map is 1 : contactId_map is 4 : contactId is 4 : contactName is C4 : contactPhoneNumber is +00 987 654 321
2022-05-25 09:19:38.488 D/DBINFO: userId is 1 : fullName is Fred Bloggs : email is [email protected] : password is password : phoneNumber is +00 123 456 789 : userId_map is 1 : contactId_map is 2 : contactId is 2 : contactName is C2 : contactPhoneNumber is +00 987 654 321
2022-05-25 09:19:38.488 D/DBINFO: userId is 2 : fullName is Mary Bloggs : email is [email protected] : password is password : phoneNumber is +00 231 456 789 : userId_map is 2 : contactId_map is 1 : contactId is 1 : contactName is C1 : contactPhoneNumber is +00 987 654 321
2022-05-25 09:19:38.489 D/DBINFO: userId is 2 : fullName is Mary Bloggs : email is [email protected] : password is password : phoneNumber is +00 231 456 789 : userId_map is 2 : contactId_map is 3 : contactId is 3 : contactName is C3 : contactPhoneNumber is +00 987 654 321
2022-05-25 09:19:38.489 D/DBINFO: userId is 2 : fullName is Mary Bloggs : email is [email protected] : password is password : phoneNumber is +00 231 456 789 : userId_map is 2 : contactId_map is 5 : contactId is 5 : contactName is C5 : contactPhoneNumber is +00 987 654 321
2022-05-25 09:19:38.490 D/DBINFO: userId is 2 : fullName is Mary Bloggs : email is [email protected] : password is password : phoneNumber is +00 231 456 789 : userId_map is 2 : contactId_map is 7 : contactId is 7 : contactName is C7 : contactPhoneNumber is +00 987 654 321
2022-05-25 09:19:38.490 D/DBINFO: userId is 3 : fullName is Jane Bloggs : email is [email protected] : password is password : phoneNumber is +00 312 456 789 : userId_map is 3 : contactId_map is 1 : contactId is 1 : contactName is C1 : contactPhoneNumber is +00 987 654 321
2022-05-25 09:19:38.490 D/DBINFO: userId is 3 : fullName is Jane Bloggs : email is [email protected] : password is password : phoneNumber is +00 312 456 789 : userId_map is 3 : contactId_map is 2 : contactId is 2 : contactName is C2 : contactPhoneNumber is +00 987 654 321
2022-05-25 09:19:38.490 D/DBINFO: userId is 3 : fullName is Jane Bloggs : email is [email protected] : password is password : phoneNumber is +00 312 456 789 : userId_map is 3 : contactId_map is 3 : contactId is 3 : contactName is C3 : contactPhoneNumber is +00 987 654 321
2022-05-25 09:19:38.491 D/DBINFO: userId is 3 : fullName is Jane Bloggs : email is [email protected] : password is password : phoneNumber is +00 312 456 789 : userId_map is 3 : contactId_map is 4 : contactId is 4 : contactName is C4 : contactPhoneNumber is +00 987 654 321
2022-05-25 09:19:38.491 D/DBINFO: userId is 3 : fullName is Jane Bloggs : email is [email protected] : password is password : phoneNumber is +00 312 456 789 : userId_map is 3 : contactId_map is 5 : contactId is 5 : contactName is C5 : contactPhoneNumber is +00 987 654 321
2022-05-25 09:19:38.492 D/DBINFO: userId is 3 : fullName is Jane Bloggs : email is [email protected] : password is password : phoneNumber is +00 312 456 789 : userId_map is 3 : contactId_map is 6 : contactId is 6 : contactName is C6 : contactPhoneNumber is +00 987 654 321
2022-05-25 09:19:38.492 D/DBINFO: userId is 3 : fullName is Jane Bloggs : email is [email protected] : password is password : phoneNumber is +00 312 456 789 : userId_map is 3 : contactId_map is 7 : contactId is 7 : contactName is C7 : contactPhoneNumber is +00 987 654 321


2022-05-25 09:19:38.494 D/DBINFO: userId is 1 : fullName is Fred Bloggs : email is [email protected] : password is password : phoneNumber is +00 123 456 789 : number_of_contacts is 3 : group_concat(contactName) is C6,C4,C2
2022-05-25 09:19:38.494 D/DBINFO: userId is 2 : fullName is Mary Bloggs : email is [email protected] : password is password : phoneNumber is +00 231 456 789 : number_of_contacts is 4 : group_concat(contactName) is C1,C3,C5,C7
2022-05-25 09:19:38.494 D/DBINFO: userId is 3 : fullName is Jane Bloggs : email is [email protected] : password is password : phoneNumber is +00 312 456 789 : number_of_contacts is 7 : group_concat(contactName) is C1,C2,C3,C4,C5,C6,C7

Answer 2

使用外键约束。为此，您需要在父表中拥有一个主键（此处为'user_table'），然后在子表中定义外键约束（herere'contacts_table'）。

这是您这样做的SLQITE脚本：

CREATE TABLE  user_table (
    user_pk INT PRIMARY KEY AUTOINCREMENT,
    fullname TEXT,
    email TEXT,
    password TEXT,
    phoneNumber INT,
    );

CREATE TABLE contacts_table(
    user_fk INT NOT NULL,
    contact_id INT PRIMARY KEY AUTOINCREMENT,
    contactName TEXT,
    contactPhoneNumber INT,

    CONSTRAINT user_fk_in_contacts_table
        FOREIGN KEY (user_fk)
        REFERENCES user_table (user_pk)
    );

因此，您应该能够在Contacts_table中的User_FK列的帮助下区分两个用户的联系（这表明该特定行是哪个用户的联系人），

谢谢您

！