时间戳

首页 > 解决方案 > 在 SQLite Android 中为关系制作和使用外键

android - 在 SQLite Android 中为关系制作和使用外键

问题描述

假设我有一个 android studio 应用程序，我可以在其中添加公司，并且在这些公司内部我还可以添加员工。现在，我正在尝试让员工列表仅显示我之前选择的公司的元素（在显示员工的独特活动中）。

我创建了两个类：

data class Company(val id:Int, val name:String, val telephone:String)
data class Employee (val id:Int, val name:String, val telephone:String, val idCompany:Int)

在我的 SQLiteHelper 中，我为每个表创建了表，并在员工中使用外键来建立关系

    private val TABLE_COMPANY = "CompanyTable"
    private val TABLE_EMPLOYEE = "EmployeeTable"


    //Company table
    private val COMPANY_ID = "_id"
    private val COMPANY_NAME = "name"
    private val COMPANY_TL = "telephone"

    //Employee table
    private val EMPLOYEE_ID = "id"
    private val EMPLOYEE_NAME = "name"
    private val EMPLOYEE_TL = "telephone"
    private val EMPLOYEE_COMPANY_ID = "id"



}

override fun onCreate(db: SQLiteDatabase?) {

    val CREATE_COMPANY_TABLE = ("CREATE TABLE " + TABLE_COMPANY + "("
            + COMPANY_ID + " INTEGER PRIMARY KEY,"
            + COMPANY_NAME + " TEXT,"
            + COMPANY_TL + " TEXT" + ")")

    val CREATE_EMPLOYEE_TABLE = ("CREATE TABLE " + TABLE_EMPLOYEE + "("
            + EMPLOYEE_ID + " INTEGER PRIMARY KEY,"
            + EMPLOYEE_NAME + " TEXT,"
            + EMPLOYEE_TL + " INTEGER,"
            + EMPLOYEE_COMPANY_ID + " INTEGER,"
            + " FOREIGN KEY ("+ EMPLOYEE_COMPANY_ID+") REFERENCES "+TABLE_COMPANY+"("+ COMPANY_ID+"))")


    db?.execSQL(CREATE_EMPLOYEE_TABLE)
    db?.execSQL(CREATE_COMPANY_TABLE)

}

所以，我用recyclerviews做了两项活动，一项针对公司，另一项针对员工。当我单击一家公司时，员工活动将打开并显示他们的列表。

但它显示了我拥有的所有员工，所以我试图弄清楚如何只显示我保存的与我在上一个活动中单击的公司 ID 相同的员工。

但我现在不知道如何进行

这是显示员工的 DAO 函数：

fun viewEmployee(): ArrayList<Employee> {

    val empList: ArrayList<Employee> = ArrayList<Employee>()

    // Query to select the records
    val selectQuery = "SELECT L.$EMPLOYEE_NAME, L.$EMPLOYEE_TL, L.$EMPLOYEE_ID, L.$EMPLOYEE_COMPANY_ID, C.$COMPANY_ID" +
            "FROM $TABLE_EMPLOYEE as L, $TABLE_COMPANY as C" +
            "WHERE L.$EMPLOYEE_COMPANY_ID = C.$COMPANY_ID"



    val db = this.readableDatabase
   
    var cursor: Cursor? = null

    try {
        cursor = db.rawQuery(selectQuery, null)

    } catch (e: SQLiteException) {
        db.execSQL(selectQuery)
        return ArrayList()
    }

    var id: Int
    var name: String
    var telephone: String
    var idCompany: Int

    if (cursor.moveToFirst()) {
        do {
            id = cursor.getInt(cursor.getColumnIndex(EMPLOYEE_ID))
            name = cursor.getString(cursor.getColumnIndex(EMPLOYEE_NAME))
            telephone = cursor.getString(cursor.getColumnIndex(EMPLOYEE_TL))
            idCompany = cursor.getInt(cursor.getColumnIndex(EMPLOYEE_COMPANY_ID))

            val employee = Employee(id = id, name = name, telephone = telephone, idCompany = idCompany)
            empList.add(employee)

        } while (cursor.moveToNext())
    }
    return empList
}

这是向员工展示的活动

class ManagerEmp : AppCompatActivity() {




override fun onCreate(savedInstanceState: Bundle?) {
    super.onCreate(savedInstanceState)
    setContentView(R.layout.activity_manager_emp)

    setList()
    
    createEmp.setOnClickListener{
        val intent = Intent(this, CreateEmp::class.java)
        startActivity(intent)
    }
}





/**
 * Gets DB employee list
 */
private fun getItemsList(): ArrayList<Employee> {
    //creating the instance of DatabaseHandler class
    val databaseHandler = DataBaseHelper(this)
    //calling the viewEmployee method of DatabaseHandler class to read the records
    val empList: ArrayList<Employee> = databaseHandler.viewEmployee()

    return empList
}



/**
 * Generates the list
 */
private fun setList() {

    recyclerEmp.layoutManager = LinearLayoutManager(this)
    val itemAdapter = empAdapter(this, getItemsList())
    recyclerEmp.adapter = itemAdapter
}

}

这听起来很简单，但它不是（至少对我来说）我想获取公司的 id 值并将其传递给员工列表活动，所以我可以用来比较它，但是我知道如何，我很新在 kotlin 中（以及一般的编程）

如果你能给出答案，你就救了我的命。

帮助

标签： androidsqlitekotlin

解决方案

首先，您必须获得companyId要过滤的依据。例如，可以通过在 RecyclerView 添加 onClickListener来完成。

然后，您将需要额外的查询，用于按 companyId 进行过滤。

例如，如果companyId存储在filteredCompanyId变量中，您可以添加额外的过滤条件 ( WHERE ... AND C.$COMPANY_ID == $filteredCompanyId)：

val filteredCompanyId = 5
val query = """
SELECT L.$EMPLOYEE_NAME, 
       L.$EMPLOYEE_TL, 
       L.$EMPLOYEE_ID, 
       L.$EMPLOYEE_COMPANY_ID, 
       C.$COMPANY_ID 
FROM   $TABLE_EMPLOYEE AS L, 
       $TABLE_COMPANY AS C 
WHERE  L.$EMPLOYEE_COMPANY_ID = C.$COMPANY_ID 
       AND C.$COMPANY_ID == $filteredCompanyId
"""

推荐阅读