介绍
Android默认的数据是SQLite，但SQLite3.6.19之前(在2.2版本中使用的是3.6.22，因此如果你的应用只兼容到2.2版本就可以放心使用外键功能)是不支持外键的，如果有两张表需要关联，用外键是最省事的，但不支持的话怎么办呢？这里就有一个解决办法，就是用事务将两张表关联起来，并且最后生成一张视图。
现有两张表
- Employees
- Dept
视图
ViewEmps:显示雇员信息和他所在的部门
创建数据库
自定义一个辅助类继承SQLiteOpenHelper类.
- onCreate(SQLiteDatabase db): 当数据库被创建的时候，能够生成表，并创建视图跟触发器。
- onUpgrade(SQLiteDatabse db, int oldVersion, int newVersion): 更新的时候可以删除表和创建新的表。
代码如下：
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
|
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
)
{
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
)
{
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
)
;
}
}
|
加入数据
|
1
2
3
4
5
6
7
8
9
10
11
|
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
(
)
;
|
更新数据
|
1
2
3
4
5
6
7
8
9
10
11
|
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
(
)
)
}
)
;
}
|
删除数据
|
1
2
3
4
5
6
7
|
public
void
DeleteEmp
(
Employee
emp
)
{
SQLiteDatabase
db
=
this
.
getWritableDatabase
(
)
;
db
.
delete
(
employeeTable
,
colID
+
"=?"
,
new
String
[
]
{
String
.
valueOf
(
emp
.
getID
(
)
)
}
)
;
db
.
close
(
)
;
}
|
取得所有部门信息
|
1
2
3
4
5
6
7
8
9
|
Cursor
getAllDepts
(
)
{
SQLiteDatabase
db
=
this
.
getReadableDatabase
(
)
;
Cursor
cur
=
db
.
rawQuery
(
"SELECT "
+
colDeptID
+
" as _id,
"
+
colDeptName
+
" from "
+
deptTable
,
new
String
[
]
{
}
)
;
return
cur
;
}
|
取得部门内雇员信息
|
1
2
3
4
5
6
7
8
9
|
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
|
1
2
3
4
5
6
7
8
9
10
11
|
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"
)
)
;
}
|
上面部门和雇员信息的表因为实现的关联，所以更新和删除都会对对应的信息更新。
原文链接 。
1245
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
