Android：数据库增删改查、SQLite、ORM、Cursor
1.继承类SQLiteOpenHelper：
|
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
|
public
class
MySQLiteHelper
extends
SQLiteOpenHelper
{
//重写构造方法
public
MySQLiteHelper(Context context, String name, CursorFactory factory,
int
version)
{
super
(context, name, factory, version);
}
//创建表
@Override
public
void
onCreate(SQLiteDatabase db)
{
db.execSQL(
"CREATE TABLE person(personid INTEGER PRIMARY KEY AUTOINCREMENT,name VARCHAR(20),phone VARCHAR(20))"
);
db.execSQL(
"CREATE TABLE student(personid INTEGER PRIMARY KEY AUTOINCREMENT,name VARCHAR(20),phone VARCHAR(20))"
);
}
//升级表（当Database的Version低于当前new里的Version，直接执行下面方法）
@Override
public
void
onUpgrade(SQLiteDatabase db,
int
oldVersion,
int
newVersion)
{
int
v = newVersion - oldVersion;
switch
(v)
{
case
3
:
db.execSQL(
"ALTER TABLE person ADD salary3 VARCHAR(20)"
);
case
2
:
db.execSQL(
"ALTER TABLE person ADD salary2 VARCHAR(20)"
);
case
1
:
db.execSQL(
"ALTER TABLE person ADD salary1 VARCHAR(20)"
);
default
:
break
;
}
}
}
|
2.实现增删改查：
|
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
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
|
/**
*
CREATE TABLE person
(
personid INTEGER PRIMARY KEY AUTOINCREMENT,
name VARCHAR(20),
phone VARCHAR(20)
)
DROP TABLE person //删除表
INSERT INTO person(name,phone) VALUES ("rongqin","1598088588") //插入
UPDATE person SET name="Andfeel" WHERE personid=2 //改
ALTER TABLE person ADD salary //增加列
DELETE FROM person WHERE name="Andfeel" //删除
SELECT * FROM person WHERE name="Andfeel"
SELECT phone,name FROM person
SELECT name,phone FROM person ORDER BY name ASC"//ASC升序 DESC降序
*
*/
public
class
MainActivity
extends
Activity
implements
OnClickListener
{
private
TextView textView;
private
MySQLiteHelper mDBHelper;
private
MySQLiteHelper bHelper;
@Override
protected
void
onCreate(Bundle savedInstanceState)
{
super
.onCreate(savedInstanceState);
setContentView(R.layout.activity_main);
findViewById(R.id.btn__create_table).setOnClickListener(
this
);
findViewById(R.id.btn_delete_table).setOnClickListener(
this
);
findViewById(R.id.btn_alter_table).setOnClickListener(
this
);
findViewById(R.id.btn_insert).setOnClickListener(
this
);
findViewById(R.id.btn_delete).setOnClickListener(
this
);
findViewById(R.id.btn_update).setOnClickListener(
this
);
findViewById(R.id.btn_select).setOnClickListener(
this
);
textView = (TextView) findViewById(R.id.textView1);
//创建数据库
mDBHelper =
new
MySQLiteHelper(
this
,
"one.db"
,
null
,
3
);
}
@Override
public
boolean
onCreateOptionsMenu(Menu menu)
{
getMenuInflater().inflate(R.menu.activity_main, menu);
return
true
;
}
@Override
public
void
onClick(View v)
{
switch
(v.getId())
{
case
R.id.btn__create_table:
btnCreateTable();
break
;
case
R.id.btn_delete_table:
btnDeleteTable();
break
;
case
R.id.btn_alter_table:
btnAlterTable();
break
;
case
R.id.btn_insert:
btnInsert();
break
;
case
R.id.btn_delete:
btnDelete();
break
;
case
R.id.btn_update:
btnUpdate();
break
;
case
R.id.btn_select:
btnSelect();
break
;
default
:
break
;
}
}
private
void
btnSelect()
//查
{
SQLiteDatabase db = mDBHelper.getWritableDatabase();
// Cursor cursor = db.rawQuery("SELECT * FROM person WHERE name = ?", new String[]{"rongqin"});
// cursor.moveToFirst();
Cursor cursor = db.query(
"person"
,
//表名
new
String[]{
"phone"
,
"name"
},
//查询的列名
"name=?"
,
//查询条件
new
String[]{
"huangxx"
},
//查询参数
null
,
null
,
"phone DESC"
);
//排序，升序为ASC，降序为DESC
boolean
hasdata = cursor.moveToFirst();
//游标移到第一行
StringBuilder sb =
new
StringBuilder();
while
(hasdata)
//是否有数据
{
int
columnIndex = cursor.getColumnIndex(
"name"
);
//得到name的列数
String name = cursor.getString(columnIndex);
//得到name这一列的值
int
columnIndex2 = cursor.getColumnIndex(
"phone"
);
String phone = cursor.getString(columnIndex2);
sb.append(
"name:"
).append(name).append(
" phone:"
).append(phone).append(
"--"
);
hasdata = cursor.moveToNext();
//游标移动到下一行，判断是否有值
}
textView.setText(sb.toString());
}
private
void
btnUpdate()
//改
{
SQLiteDatabase db = mDBHelper.getWritableDatabase();
// db.execSQL("UPDATE person SET phone='0592xxxxxx' WHERE personid=5");
ContentValues values =
new
ContentValues();
values.put(
"name"
,
"haoyouduo"
);
values.put(
"phone"
,
"666666"
);
db.update(
"person"
, values ,
"personid=?"
,
new
String[]{
"8"
});
}
private
void
btnDelete()
//删
{
SQLiteDatabase db = mDBHelper.getWritableDatabase();
// db.execSQL("DELETE FROM person WHERE name='Andfeel'");
db.delete(
"person"
,
"name = ? and personid = ?"
,
new
String[]{
"haoyouduo"
,
"5"
});
}
private
void
btnInsert()
//增
{
SQLiteDatabase db = mDBHelper.getWritableDatabase();
// db.execSQL("INSERT INTO person(name,phone) VALUES ('rongqin',1598088588)");
ContentValues values =
new
ContentValues();
values.put(
"name"
,
"huangxx"
);
values.put(
"phone"
,
"158888"
);
db.insert(
"person"
,
null
, values );
}
private
void
btnAlterTable()
//扩展表
{
}
private
void
btnDeleteTable()
//删除表
{
}
private
void
btnCreateTable()
//创建表
{
}
}
|
其他总结:
1.关闭数据源
|
1
2
3
4
5
6
7
8
9
10
|
/**
* 关闭数据源
*/
public
void
closeConnection()
{
if
(mDb !=
null
&& mDb.isOpen())
mDb.close();
if
(mDbHelper !=
null
)
mDbHelper.close();
}
|
本文转自 glblong 51CTO博客，原文链接：http://blog.51cto.com/glblong/1216877，如需转载请自行联系原作者
