SQLite3 触发器、视图的案例

介绍：

安卓默认的数据是SQLite，但SQLite3.6.19之前是不支持外键的，如果有两张表需要关联，用外键是最省事的，但不支持的话怎么办呢？这里就有一个解决办法，就是用事务将两张表关联起来，并且最后生成一张视图。

现有两张表

1. Employees
2. Dept

视图

1. ViewEmps:显示雇员信息和他所在的部门

创建数据库

自定义一个辅助类继承SQLiteOpenHelper类

1. publicclassDatabaseHelperextendsSQLiteOpenHelper{
3. staticfinalStringdbName="demoDB";
4. finalStringemployeeTable="Employees";
5. finalStringcolID="EmployeeID";
6. finalStringcolName="EmployeeName";
7. finalStringcolAge="Age";
8. finalStringcolDept="Dept";
10. finalStringdeptTable="Dept";
11. finalStringcolDeptID="DeptID";
12. finalStringcolDeptName="DeptName";
13. finalStringviewEmps="ViewEmps";

publicDatabaseHelper(Contextcontext){
1. super(context,dbName,null,33);
2. }

voidonCreate(SQLiteDatabasedb){
1. //TODOAuto-generatedmethodstub
2. db.execSQL("CREATETABLE"+deptTable+"("+colDeptID+"INTEGERPRIMARYKEY,"+
3. colDeptName+"TEXT)");
4. db.execSQL("CREATETABLE"+employeeTable+"
5. ("+colID+"INTEGERPRIMARYKEYAUTOINCREMENT,"+
6. colName+"TEXT,"+colAge+"Integer,"+colDept+"
7. INTEGERNOTNULL,FOREIGNKEY("+colDept+")REFERENCES
8. "+deptTable+"("+colDeptID+"));");
9. //创建触发器
10. db.execSQL("CREATETRIGGERfk_empdept_deptid"+
11. "BEFOREINSERT"+
12. "ON"+employeeTable+
13. "FOREACHROWBEGIN"+
14. "SELECTCASEWHEN((SELECT"+colDeptID+"FROM"+deptTable+"
15. WHERE"+colDeptID+"=new."+colDept+")ISNULL)"+
16. "THENRAISE(ABORT,'ForeignKeyViolation')END;"+
17. "END;");
18. //创建视图
19. db.execSQL("CREATEVIEW"+viewEmps+
20. "ASSELECT"+employeeTable+"."+colID+"AS_id,108); list-style:decimal-leading-zero outside; color:inherit; line-height:18px; margin:0px!important; padding:0px 3px 0px 10px!important"> ""+employeeTable+"."+colName+","+
21. ""+employeeTable+"."+colAge+",108); list-style:decimal-leading-zero outside; color:inherit; line-height:18px; margin:0px!important; padding:0px 3px 0px 10px!important"> ""+deptTable+"."+colDeptName+""+
22. "FROM"+employeeTable+"JOIN"+deptTable+
23. "ON"+employeeTable+"."+colDept+"="+deptTable+"."+colDeptID
24. );
25. }

voidonUpgrade(SQLiteDatabasedb,153); background-color:inherit; font-weight:bold">intoldVersion,153); background-color:inherit; font-weight:bold">intnewVersion){
1. db.execSQL("DROPTABLEIFEXISTS"+employeeTable);
2. db.execSQL("DROPTABLEIFEXISTS"+deptTable);
3. db.execSQL("DROPTRIGGERIFEXISTSfk_empdept_deptid");
4. db.execSQL("DROPVIEWIFEXISTS"+viewEmps);
5. onCreate(db);
6. }

1. SQLiteDatabasedb=this.getWritableDatabase();
2. ContentValuescv=newContentValues();
3. cv.put(colDeptID,1);
4. cv.put(colDeptName,"Sales");
5. db.insert(deptTable,colDeptID,cv);
7. 2);
8. "IT");
9. db.close();

intUpdateEmp(Employeeemp)
1. {
2. SQLiteDatabasedb= cv.put(colName,emp.getName());
3. cv.put(colAge,emp.getAge());
4. cv.put(colDept,emp.getDept());
5. returndb.update(employeeTable,cv,colID+"=?",
6. newString[]{String.valueOf(emp.getID())});
7. }

voidDeleteEmp(Employeeemp)
1. db.delete(employeeTable,colID+"=?",153); background-color:inherit; font-weight:bold">newString[]{String.valueOf(emp.getID())});
2. db.close();
3. }

CursorgetAllDepts()
1. this.getReadableDatabase();
2. Cursorcur=db.rawQuery("SELECT"+colDeptID+"as_id,108); list-style:decimal-leading-zero outside; color:inherit; line-height:18px; margin:0px!important; padding:0px 3px 0px 10px!important"> "+colDeptName+"from"+deptTable,153); background-color:inherit; font-weight:bold">newString[]{});
3. returncur;
4. }

publicCursorgetEmpByDept(StringDept)
1. String[]columns=newString[]{"_id",colName,colAge,colDeptName};
2. Cursorc=db.query(viewEmps,columns,colDeptName+"=?",
3. newString[]{Dept},153); background-color:inherit; font-weight:bold">null);
4. returnc;
5. }

intGetDeptID(StringDept)
1. Cursorc=db.query(deptTable,newString[]{colDeptID+"as_id",colDeptName},108); list-style:decimal-leading-zero outside; color:inherit; line-height:18px; margin:0px!important; padding:0px 3px 0px 10px!important"> colDeptName+"=?",153); background-color:inherit; font-weight:bold">null);
2. //Cursorc=db.rawQuery("SELECT"+colDeptID+"as_idFROM"+deptTable+"
3. //WHERE"+colDeptName+"=?",newString[]{Dept});
4. c.moveToFirst();
5. returnc.getInt(c.getColumnIndex("_id"));
6. }

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!