Android[進階教程] Android資料庫SQLite表內設定外鍵

介紹：

安卓預設的資料是SQLite，但SQLite3.6.19之前是不支援外鍵的，如果有兩張表需要關聯，用外鍵是最省事的，但不支援的話怎麼辦呢？這裡就有一個解決辦法，就是用事務將兩張表關聯起來，並且最後產生一張視圖。

現有兩張表

1. Employees
2. Dept

視圖

1. ViewEmps:顯示僱員資訊和他所在的部門

建立資料庫

自訂一個輔助類繼承SQLiteOpenHelper類

1.onCreate(SQLiteDatabase db): 當資料庫被建立的時候，能夠產生表，並建立視圖跟觸發器。2.onUpgrade(SQLiteDatabse db, int oldVersion, int newVersion): 更新的時候可以刪除表和建立新的表。代碼如下：

public class DatabaseHelper extends SQLiteOpenHelper {static final String dbName="demoDB";static final String employeeTable="Employees";static final String colID="EmployeeID";static final String colName="EmployeeName";static final String colAge="Age";static final String colDept="Dept";static final String deptTable="Dept";static final String colDeptID="DeptID";static final String colDeptName="DeptName";static final String viewEmps="ViewEmps";

構造器

public DatabaseHelper(Context context) {  super(context, dbName, null,33);   }

建立庫中的表，視圖和觸發器

public void onCreate(SQLiteDatabase db) {  // TODO Auto-generated method stub    db.execSQL("CREATE TABLE "+deptTable+" ("+colDeptID+ " INTEGER PRIMARY KEY , "+    colDeptName+ " TEXT)");    db.execSQL("CREATE TABLE "+employeeTable+"     ("+colID+" INTEGER PRIMARY KEY AUTOINCREMENT, "+        colName+" TEXT, "+colAge+" Integer, "+colDept+"     INTEGER NOT NULL ,FOREIGN KEY ("+colDept+") REFERENCES     "+deptTable+" ("+colDeptID+"));");    //建立觸發器  db.execSQL("CREATE TRIGGER fk_empdept_deptid " +    " BEFORE INSERT "+    " ON "+employeeTable+        " FOR EACH ROW BEGIN"+    " SELECT CASE WHEN ((SELECT "+colDeptID+" FROM "+deptTable+"     WHERE "+colDeptID+"=new."+colDept+" ) IS NULL)"+    " THEN RAISE (ABORT,'Foreign Key Violation') END;"+    "  END;"); //建立視圖  db.execSQL("CREATE VIEW "+viewEmps+    " AS SELECT "+employeeTable+"."+colID+" AS _id,"+    " "+employeeTable+"."+colName+","+    " "+employeeTable+"."+colAge+","+    " "+deptTable+"."+colDeptName+""+    " FROM "+employeeTable+" JOIN "+deptTable+    " ON "+employeeTable+"."+colDept+" ="+deptTable+"."+colDeptID    ); }

更新庫中的表

public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {  // TODO Auto-generated method stub    db.execSQL("DROP TABLE IF EXISTS "+employeeTable);  db.execSQL("DROP TABLE IF EXISTS "+deptTable);  db.execSQL("DROP TRIGGER IF EXISTS fk_empdept_deptid");  db.execSQL("DROP VIEW IF EXISTS "+viewEmps);  onCreate(db); }

加入資料

SQLiteDatabase db=this.getWritableDatabase(); ContentValues cv=new ContentValues();   cv.put(colDeptID, 1);   cv.put(colDeptName, "Sales");   db.insert(deptTable, colDeptID, cv);   cv.put(colDeptID, 2);   cv.put(colDeptName, "IT");   db.insert(deptTable, colDeptID, cv);                     db.close();

更新資料

public int UpdateEmp(Employee emp)  {   SQLiteDatabase db=this.getWritableDatabase();   ContentValues cv=new ContentValues();   cv.put(colName, emp.getName());   cv.put(colAge, emp.getAge());   cv.put(colDept, emp.getDept());   return db.update(employeeTable, cv, colID+"=?",     new String []{String.valueOf(emp.getID())});     }

刪除資料

public void DeleteEmp(Employee emp)  {   SQLiteDatabase db=this.getWritableDatabase();   db.delete(employeeTable,colID+"=?", new String [] {String.valueOf(emp.getID())});   db.close();  }

取得所有部門資訊

Cursor getAllDepts()  {   SQLiteDatabase db=this.getReadableDatabase();   Cursor cur=db.rawQuery("SELECT "+colDeptID+" as _id, "+colDeptName+" from "+deptTable,new String [] {});      return cur;  }

取得部門內僱員資訊

public Cursor getEmpByDept(String Dept)  {   SQLiteDatabase db=this.getReadableDatabase();   String [] columns=new String[]{"_id",colName,colAge,colDeptName};   Cursor c=db.query(viewEmps, columns, colDeptName+"=?", new String[]{Dept}, null, null, null);   return c;  }

取得部門ID

public int GetDeptID(String Dept)  {   SQLiteDatabase db=this.getReadableDatabase();   Cursor c=db.query(deptTable, new String[]{colDeptID+" as _id",colDeptName},colDeptName+"=?", new String[]{Dept}, null, null, null);   //Cursor c=db.rawQuery("SELECT "+colDeptID+" as _id FROM "+deptTable+"    //WHERE "+colDeptName+"=?", new String []{Dept});   c.moveToFirst();   return c.getInt(c.getColumnIndex("_id"));    }

上面部門和僱員資訊的表因為實現的關聯，所以更新和刪除都會對對應的資訊更新。

原網頁：http://www.codeproject.com/KB/android/AndroidSQLite.aspx#