Android[高級教程] Android數據庫SQLite表內設置外鍵 – Android移動開發技術文章_手機開發 Android移動開發教學課程

 

介紹:

 

安卓默認的數據是SQLite,但SQLite3.6.19之前是不支持外鍵的,如果有兩張表需要關聯,用外鍵是最省事的,但不支持的話怎麼辦呢?這裡就有一個解決辦法,就是用事務將兩張表關聯起來,並且最後生成一張視圖。

 

 

現有兩張表

 

Employees

Dept

視圖

 

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"; 

構造器

view plain

public DatabaseHelper(Context context) { 

  super(context, dbName, null,33);  

  } 

創建庫中的表,視圖和觸發器

view plain

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())});    

  } 

 

刪除數據

view plain

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; 

  } 

 

取得部門內雇員信息

view plain

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

www.aiwalls.com

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"));   

  } 

 

上面部門和雇員信息的表因為實現的關聯,所以更新和刪除都會對對應的信息更新。

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

 

摘自 kangkangz4的專欄

發佈留言