Android第十九期 - Sqlite离线存储教你写小米记事本
这期说sqlite离线存储，最近app要加上离线文件下载的功能，自己也是弄了一天，总算是小有成果，要好好感谢老婆的大力支持，帮我测试什么的，都任劳任怨，现在能这样对我这样的好的人只有老婆了。分享代码给大家，看着一些记事本都很实用，所以也就估摸着弄了一个，UI自己设计吧，功能实现了一切都好说。下面开始：
首先，sqlite可以去官网http://www.sqlite.org/lang.html，去查询所有进行的操作，创建表，增删改查等等。
然后就是建一个sqlite的类继承至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
|
package
com.liangxiao.usingsqlite;
import
android.content.Context;
import
android.database.sqlite.SQLiteDatabase;
import
android.database.sqlite.SQLiteDatabase.CursorFactory;
import
android.database.sqlite.SQLiteOpenHelper;
public
class
Db
extends
SQLiteOpenHelper {
public
Db(Context context, String name, CursorFactory factory,
int
version) {
super
(context,
"db"
,
null
,
1
);
// TODO Auto-generated constructor stub
}
@Override
public
void
onCreate(SQLiteDatabase db) {
// TODO Auto-generated method stub
// db.execSQL("CREATE TABLE USER(" + "name TEXT DEFAULT \"\","
// + "sex TEXT DEFAULT \"\")");
db.execSQL(
"CREATE TABLE USER("
+
"_id INTEGER PRIMARY KEY AUTOINCREMENT,"
+
"name TEXT DEFAULT \"\","
+
"sex TEXT DEFAULT \"\")"
);
// db.execSQL("CREATE TABLE USER(" + "name TEXT DEFAULT \"\"");
}
@Override
public
void
onUpgrade(SQLiteDatabase db,
int
oldVersion,
int
newVersion) {
// TODO Auto-generated method stub
}
}
|
注意：这里面的adapter我用了官方自带的方便操作了，所以需求引用表的_id ,就加了一个字段。
然后是我给大家写了两种读取操作界面，一种是textview的读取，一种是listview的读取方式。
先说textview，大家可以发挥想象去构造UI，什么下拉悬停，ScrollView等等。
代码我都注释过PS：跳转窗体到MainActivity_dbRead：
|
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
|
package
com.liangxiao.usingsqlite;
import
android.app.Activity;
import
android.database.Cursor;
import
android.database.sqlite.SQLiteDatabase;
import
android.os.Bundle;
import
android.widget.TextView;
public
class
MainActivity_dbRead
extends
Activity {
private
TextView xianshi;
@Override
protected
void
onCreate(Bundle savedInstanceState) {
super
.onCreate(savedInstanceState);
setContentView(R.layout.activity_main_dbread);
xianshi = (TextView) findViewById(R.id.xianshi);
read_db();
}
/**
* 读取db部分
*/
private
void
read_db() {
Db db =
new
Db(
this
,
"db"
,
null
,
1
);
SQLiteDatabase dbRead = db.getReadableDatabase();
Cursor c = dbRead.query(
"user"
,
null
,
null
,
null
,
null
,
null
,
null
);
while
(c.moveToNext()) {
String name = c.getString(c.getColumnIndex(
"name"
));
String sex = c.getString(c.getColumnIndex(
"sex"
));
db_lastindex_content +=
"心情："
+ name +
" "
+
"当事人："
+ sex +
"\n"
;
}
xianshi.setText(db_lastindex_content.toString());
}
private
String db_lastindex_content =
""
;
}
|
注意：这个textview你可以在当前的页面显示你的操作，也可以跳转操作，都会直接显示在主UI上，没有卡顿的现象，很流畅的。
主窗体MainActivity：
|
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
|
package
com.liangxiao.usingsqlite;
import
android.app.Activity;
import
android.content.ContentValues;
import
android.content.Intent;
import
android.database.Cursor;
import
android.database.sqlite.SQLiteDatabase;
import
android.os.Bundle;
import
android.view.View;
import
android.view.View.OnClickListener;
import
android.widget.Button;
import
android.widget.EditText;
import
android.widget.TextView;
import
android.widget.Toast;
public
class
MainActivity
extends
Activity
implements
OnClickListener {
private
Button btn_fly;
private
TextView xianshi1;
private
EditText add_db_text;
private
EditText add_db_text2;
private
Db db;
private
SQLiteDatabase dbRead, dbWrite;
private
Cursor c;
// private String add_db_text_string;
// private String add_db_text_string2;
@Override
protected
void
onCreate(Bundle savedInstanceState) {
super
.onCreate(savedInstanceState);
setContentView(R.layout.activity_main);
init();
read_db();
}
/**
* 初始化部分
*/
private
void
init() {
xianshi1 = (TextView) findViewById(R.id.xianshi1);
btn_fly = (Button) findViewById(R.id.btn_fly);
add_db_text = (EditText) findViewById(R.id.add_db_text);
add_db_text2 = (EditText) findViewById(R.id.add_db_text2);
btn_fly.setOnClickListener(
this
);
}
/**
* 添加操作部分
*/
private
void
add_db() {
String add_db_text_string = add_db_text.getText().toString();
String add_db_text_string2 = add_db_text2.getText().toString();
Toast.makeText(
this
, add_db_text_string +
","
+ add_db_text_string2,
2
)
.show();
db =
new
Db(
this
,
"db"
,
null
,
1
);
dbWrite = db.getWritableDatabase();
ContentValues cv =
new
ContentValues();
cv.put(
"name"
, add_db_text_string);
cv.put(
"sex"
, add_db_text_string2);
dbWrite.insert(
"user"
,
null
, cv);
dbWrite.close();
}
/**
* 读取db部分
*/
private
void
read_db() {
db =
new
Db(
this
,
"db"
,
null
,
1
);
dbRead = db.getReadableDatabase();
c = dbRead.query(
"user"
,
null
,
null
,
null
,
null
,
null
,
null
);
while
(c.moveToNext()) {
String name = c.getString(c.getColumnIndex(
"name"
));
String sex = c.getString(c.getColumnIndex(
"sex"
));
System.out.println(String.format(
"name%s,sex%s"
, name, sex));
// xianshi1.setText("心情：" + name + " " + "当事人：" + sex + "\n");
db_dateBase +=
"心情："
+ name +
" "
+
"当事人："
+ sex +
"\n"
;
}
xianshi1.setText(db_dateBase.toString());
}
private
String db_dateBase =
""
;
private
void
refresh() {
db =
new
Db(
this
,
"db"
,
null
,
1
);
dbRead = db.getReadableDatabase();
c = dbRead.query(
"user"
,
null
,
null
,
null
,
null
,
null
,
null
);
while
(c.moveToNext()) {
String name = c.getString(c.getColumnIndex(
"name"
));
String sex = c.getString(c.getColumnIndex(
"sex"
));
System.out.println(String.format(
"name%s,sex%s"
, name, sex));
// xianshi1.setText("心情：" + name + " " + "当事人：" + sex + "\n");
db_dateBase =
"心情："
+ name +
" "
+
"当事人："
+ sex +
"\n"
;
}
String xinshi_text_lastindex = xianshi1.getText().toString();
xianshi1.setText(xinshi_text_lastindex+db_dateBase.toString());
}
/**
* click部分
*/
@Override
public
void
onClick(View v) {
add_db();
refresh();
// Intent intent = new Intent();
// intent.setClass(MainActivity.this, MainActivity_dbRead.class);
// MainActivity.this.startActivity(intent);
// MainActivity.this.finish();
}
}
|
注意在onClick中，我注释的部分是跳转的操作，当前的是主窗体的UI显示操作。
两种效果如下：
然后是对于listview的操作，大家一般都是对于这个操作多些，首先是主窗体MainActivity_ListView_db：
|
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
153
154
155
156
157
158
|
package
com.liangxiao.usingsqlite;
import
android.app.AlertDialog;
import
android.app.ListActivity;
import
android.content.ContentValues;
import
android.content.DialogInterface;
import
android.content.Intent;
import
android.database.Cursor;
import
android.database.sqlite.SQLiteDatabase;
import
android.os.Bundle;
import
android.view.KeyEvent;
import
android.view.View;
import
android.view.View.OnClickListener;
import
android.widget.AdapterView;
import
android.widget.AdapterView.OnItemClickListener;
import
android.widget.AdapterView.OnItemLongClickListener;
import
android.widget.Button;
import
android.widget.EditText;
import
android.widget.ListView;
import
android.widget.RelativeLayout;
import
android.widget.RelativeLayout.LayoutParams;
import
android.widget.SimpleCursorAdapter;
import
android.widget.TextView;
import
android.widget.Toast;
public
class
MainActivity_ListView_db
extends
ListActivity
implements
OnClickListener {
private
ListView list;
private
EditText edit_name, edit_sex;
private
TextView tvname, tvsex;
private
Button btn;
private
SimpleCursorAdapter adapter;
private
Db db;
private
SQLiteDatabase dbRead, dbWrite;
private
Cursor c;
private
long
firstime =
0
;
private
RelativeLayout mainLayout;
@Override
protected
void
onCreate(Bundle savedInstanceState) {
// TODO Auto-generated method stub
super
.onCreate(savedInstanceState);
setContentView(R.layout.activity_main_listview);
init();
db_add_read();
refreshListView();
}
private
void
init() {
btn = (Button) findViewById(R.id.btn);
// list = (ListView) findViewById(R.id.list);
edit_name = (EditText) findViewById(R.id.edit_name);
edit_sex = (EditText) findViewById(R.id.edit_sex);
btn.setOnClickListener(
this
);
}
private
void
db_add_read() {
db =
new
Db(
this
,
"db"
,
null
,
1
);
dbRead = db.getReadableDatabase();
// c = dbRead.query("user", null, null, null, null, null, null);
dbWrite = db.getWritableDatabase();
adapter =
new
SimpleCursorAdapter(
this
,
R.layout.activity_main_listview_item,
null
,
new
String[] {
"name"
,
"sex"
},
new
int
[] { R.id.tvname, R.id.tvsex });
// list.setAdapter(adapter);
setListAdapter(adapter);
// 删除操作部分
getListView().setOnItemLongClickListener(
new
OnItemLongClickListener() {
@Override
public
boolean
onItemLongClick(AdapterView<?> parent, View view,
final
int
position,
long
id) {
new
AlertDialog.Builder(MainActivity_ListView_db.
this
)
.setTitle(
"你妹妹"
)
.setMessage(
"Are you sure?"
)
.setNegativeButton(
"No"
,
null
)
.setPositiveButton(
"delete"
,
new
DialogInterface.OnClickListener() {
@Override
public
void
onClick(DialogInterface arg0,
int
arg1) {
// TODO Auto-generated method stub
Cursor c = adapter.getCursor();
c.moveToPosition(position);
int
itemId = c.getInt(c
.getColumnIndex(
"_id"
));
dbWrite.delete(
"user"
,
"_id=?"
,
new
String[] { itemId +
""
});
refreshListView();
}
}).show();
return
true
;
}
});
// 修改部分
getListView().setOnItemClickListener(
new
OnItemClickListener() {
@Override
public
void
onItemClick(AdapterView<?> parent, View view,
int
position,
long
id) {
Cursor c = adapter.getCursor();
c.moveToPosition(position);
int
itemId = c.getInt(c.getColumnIndex(
"_id"
));
Intent intent =
new
Intent();
intent.putExtra(
"itemId"
, itemId +
""
);
intent.setClass(MainActivity_ListView_db.
this
,
MainActivity_ListView_db_detail.
class
);
MainActivity_ListView_db.
this
.startActivity(intent);
MainActivity_ListView_db.
this
.finish();
}
});
}
private
void
refreshListView() {
c = dbRead.query(
"user"
,
null
,
null
,
null
,
null
,
null
,
null
);
adapter.changeCursor(c);
}
@Override
public
void
onClick(View v) {
// TODO Auto-generated method stub
if
(v == btn) {
ContentValues cv =
new
ContentValues();
cv.put(
"name"
, edit_name.getText().toString());
cv.put(
"sex"
, edit_sex.getText().toString());
dbWrite.insert(
"user"
,
null
, cv);
refreshListView();
edit_name.getText().clear();
edit_sex.getText().clear();
}
}
@Override
public
boolean
onKeyDown(
int
keyCode, KeyEvent event) {
if
(keyCode == KeyEvent.KEYCODE_BACK && event.getRepeatCount() ==
0
) {
// RelativeLayout.LayoutParams lp = (LayoutParams) mainLayout
// .getLayoutParams();
long
secondtime = System.currentTimeMillis();
if
(secondtime - firstime >
3000
) {
Toast.makeText(MainActivity_ListView_db.
this
,
"再按一次返回键退出"
,
Toast.LENGTH_SHORT).show();
firstime = System.currentTimeMillis();
return
true
;
}
else
{
MainActivity_ListView_db.
this
.finish();
System.exit(
0
);
android.os.Process.killProcess(android.os.Process.myPid());
}
}
return
super
.onKeyDown(keyCode, event);
}
}
|
然后是编辑MainActivity_ListView_db_detail：
|
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
|
package
com.liangxiao.usingsqlite;
import
android.app.Activity;
import
android.content.ContentValues;
import
android.content.Intent;
import
android.database.Cursor;
import
android.database.sqlite.SQLiteDatabase;
import
android.os.Bundle;
import
android.view.KeyEvent;
import
android.view.View;
import
android.widget.Button;
import
android.widget.EditText;
import
android.widget.Toast;
public
class
MainActivity_ListView_db_detail
extends
Activity {
private
EditText name, sex;
private
Button btn_update;
private
SQLiteDatabase dbRead, dbWrite;
private
String itemId;
private
Cursor c;
private
Db db;
@Override
protected
void
onCreate(Bundle savedInstanceState) {
// TODO Auto-generated method stub
super
.onCreate(savedInstanceState);
setContentView(R.layout.activity_main_listview_detail);
init();
Intent intent = getIntent();
itemId = intent.getStringExtra(
"itemId"
);
c = dbRead.query(
"user"
,
null
,
"_id=?"
,
new
String[] { itemId },
null
,
null
,
null
);
// String name1 = c.getString(c.getColumnName(1));
if
(c.moveToFirst()) {
String name1 = c.getString(c.getColumnIndex(
"name"
));
String sex1 = c.getString(c.getColumnIndex(
"sex"
));
name.setText(name1);
sex.setText(sex1);
}
c.close();
}
private
void
init() {
db =
new
Db(
this
,
"db"
,
null
,
1
);
dbRead = db.getReadableDatabase();
dbWrite = db.getWritableDatabase();
name = (EditText) findViewById(R.id.name);
sex = (EditText) findViewById(R.id.sex);
btn_update = (Button) findViewById(R.id.btn_update);
btn_update.setOnClickListener(
new
View.OnClickListener() {
@Override
public
void
onClick(View arg0) {
ContentValues cv =
new
ContentValues();
cv.put(
"name"
, name.getText().toString());
cv.put(
"sex"
, sex.getText().toString());
// dbWrite.insert("user", null, cv);
dbWrite.update(
"user"
, cv,
"_id=?"
,
new
String[] { itemId });
Intent intent =
new
Intent(
MainActivity_ListView_db_detail.
this
,
MainActivity_ListView_db.
class
);
MainActivity_ListView_db_detail.
this
.startActivity(intent);
MainActivity_ListView_db_detail.
this
.finish();
}
});
}
@Override
public
boolean
onKeyDown(
int
keyCode, KeyEvent event) {
if
(keyCode == KeyEvent.KEYCODE_BACK && event.getRepeatCount() ==
0
) {
Intent intent =
new
Intent(MainActivity_ListView_db_detail.
this
,
MainActivity_ListView_db.
class
);
MainActivity_ListView_db_detail.
this
.startActivity(intent);
MainActivity_ListView_db_detail.
this
.finish();
}
return
super
.onKeyDown(keyCode, event);
}
}
|
注意：编辑详情的有两个操作，一个是查询所在listview的id的一行记录，还有更新操作。而主窗体是读取和添加的操作。代码里面都写的比较详细，自己看吧，PS：这个里面有2个小操作，一个是listeview item点击，一个是长按，自己试试吧，效果如下图：
本文转自 吴雨声 51CTO博客，原文链接：http://blog.51cto.com/liangxiao/1563815，如需转载请自行联系原作者
