http://www.androidla.net/index.php/2011/05/01/sqlite-listview-page/
这个小程序是在 Android2.3.3平台上写的,涉及SQLite和ListView的结合使用,以及实现了分页的效果。
先看看最终效果:
程序中主要的Java类:
DBHelper.java 数据库帮助类
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
|
package
net.androidla.db;
import
android.content.Context;
import
android.database.sqlite.SQLiteDatabase;
import
android.database.sqlite.SQLiteDatabase.CursorFactory;
import
android.database.sqlite.SQLiteOpenHelper;
public
class
DBHelper
extends
SQLiteOpenHelper {
public
DBHelper(Context context, String name, CursorFactory factory,
int
version) {
super
(context, name, factory, version);
}
public
DBHelper(Context context, String name,
int
version) {
this
(context, name,
null
, version);
}
@Override
public
void
onCreate(SQLiteDatabase sld) {
sld.execSQL(
"create table person(id integer primary key autoincrement, name varchar(20), age integer)"
);
System.out.println(
"database create ..."
);
}
@Override
public
void
onUpgrade(SQLiteDatabase sld,
int
arg1,
int
arg2) {
sld.execSQL(
"drop table if exists person"
);
System.out.println(
"database upgrade ..."
);
onCreate(sld);
}
}
|
PersonService.java 业务逻辑实现类
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
|
package
net.androidla.service;
import
java.util.ArrayList;
import
java.util.List;
import
net.androidla.db.DBHelper;
import
net.androidla.vo.Person;
import
android.content.Context;
import
android.database.Cursor;
import
android.database.sqlite.SQLiteDatabase;
import
android.database.sqlite.SQLiteDatabase.CursorFactory;
public
class
PersonService {
private
static
DBHelper db;
public
PersonService(Context context, String name, CursorFactory factory,
int
version) {
db =
new
DBHelper(context, name, factory, version);
}
public
void
saveTransaction() {
SQLiteDatabase data = db.getWritableDatabase();
data.beginTransaction();
try
{
data.execSQL(
"insert into person(name, age) values(?,?)"
,
new
Object[]{
"john"
, (
short
)
20
});
data.execSQL(
"insert into person(name, age) values(?,?)"
,
new
Object[]{
"smith"
, (
short
)
30
});
data.setTransactionSuccessful();
}
catch
(Exception e) {
System.out.println(
"error..."
);
}
data.endTransaction();
}
public
void
save(Person person) {
SQLiteDatabase data = db.getWritableDatabase();
data.execSQL(
"insert into person(name, age) values(?,?)"
,
new
Object[]{person.getName(), person.getAge()});
}
@SuppressWarnings
(
"unused"
)
public
void
delete(Integer ... ids) {
if
(ids.length >
0
) {
StringBuffer sb =
new
StringBuffer();
for
(Integer id : ids) {
sb.append(
"?"
).append(
","
);
sb.deleteCharAt(ids.length);
}
SQLiteDatabase data = db.getWritableDatabase();
data.execSQL(
"delete from person where id in("
+ sb.toString() +
")"
, (Object[])ids);
}
}
public
void
update(Person person) {
SQLiteDatabase data = db.getWritableDatabase();
data.execSQL(
"update person set name=?, age=? where id=?"
,
new
Object[]{person.getName(), person.getAge(), person.getId()});
}
public
Person find(Integer id) {
SQLiteDatabase data = db.getReadableDatabase();
Cursor cursor = data.rawQuery(
"select * from person where id=?"
,
new
String[]{String.valueOf(id)});
if
(cursor.moveToNext()) {
return
new
Person(cursor.getInt(
0
), cursor.getString(
1
), cursor.getShort(
2
));
}
return
null
;
}
public
static
List<person> queryList(
int
startIndex,
int
endIndex) {
List</person><person> persons =
new
ArrayList</person><person>();
SQLiteDatabase data = db.getReadableDatabase();
Cursor cursor = data.rawQuery(
"select * from person limit ?,?"
,
new
String[]{String.valueOf(startIndex), String.valueOf(endIndex)});
while
(cursor.moveToNext()) {
persons.add(
new
Person(cursor.getInt(
0
), cursor.getString(
1
), cursor.getShort(
2
)));
}
return
persons;
}
public
static
long
getCount() {
SQLiteDatabase data = db.getReadableDatabase();
Cursor cursor = data.rawQuery(
"select count(*) from person"
,
null
);
if
(cursor.moveToNext()) {
return
cursor.getLong(
0
);
}
return
0
;
}
}
|
PersonServiceTest.java 测试类
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
|
package
net.androidla.sqlite;
import
java.util.List;
import
net.androidla.service.PersonService;
import
net.androidla.util.PageUtil;
import
net.androidla.vo.Person;
import
android.test.AndroidTestCase;
@SuppressWarnings
(
"static-access"
)
public
class
PersonServiceTest
extends
AndroidTestCase {
public
void
testDataBaseUpgrade() {
PersonService ps =
new
PersonService(
this
.getContext(),
"androidla"
,
null
,
2
);
ps.saveTransaction();
}
public
void
testTransaction() {
PersonService ps =
new
PersonService(
this
.getContext(),
"androidla"
,
null
,
1
);
ps.saveTransaction();
}
public
void
testSave() {
PersonService ps =
new
PersonService(
this
.getContext(),
"androidla"
,
null
,
1
);
for
(
int
i =
0
; i <
100
; i ++) {
ps.save(
new
Person(
"androidla"
+ i, (
short
)
10
));
}
}
public
void
testUpdate() {
PersonService ps =
new
PersonService(
this
.getContext(),
"androidla"
,
null
,
1
);
ps.update(
new
Person(
2
,
"androidla"
, (
short
)
10
));
}
public
void
testFind() {
PersonService ps =
new
PersonService(
this
.getContext(),
"androidla"
,
null
,
1
);
Person p = ps.find(
2
);
System.out.println(p.toString());
}
public
void
testDelete() {
PersonService ps =
new
PersonService(
this
.getContext(),
"androidla"
,
null
,
1
);
ps.delete(
1
);
}
public
void
testListPage() {
PersonService ps =
new
PersonService(
this
.getContext(),
"androidla"
,
null
,
1
);
List<Person> list = ps.queryList(
0
, (
int
) ps.getCount());
for
(Person p : list) {
System.out.println(p.toString());
}
}
public
void
testCount() {
PersonService ps =
new
PersonService(
this
.getContext(),
"androidla"
,
null
,
1
);
System.out.println(
"count:"
+ ps.getCount());
}
public
void
testPageUtil() {
List</person><person> list = PageUtil.getEnd();
for
(Person p : list) {
System.out.println(p.toString());
}
}
}
|
SqliteActivity.java 程序入口类
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
|
package
net.androidla.sqlite;
import
java.util.ArrayList;
import
java.util.HashMap;
import
java.util.List;
import
java.util.Map;
import
net.androidla.service.PersonService;
import
net.androidla.util.PageUtil;
import
net.androidla.vo.Person;
import
android.app.Activity;
import
android.os.Bundle;
import
android.view.View;
import
android.view.View.OnClickListener;
import
android.widget.AdapterView;
import
android.widget.AdapterView.OnItemClickListener;
import
android.widget.Button;
import
android.widget.ListView;
import
android.widget.SimpleAdapter;
import
android.widget.TextView;
import
android.widget.Toast;
import
com.chen.sqlite.R;
@SuppressWarnings
(
"unchecked"
)
public
class
SqliteActivity
extends
Activity {
private
ListView listView;
private
PersonService personService;
private
Map<string , String> map;
private
List<map <String, String>> dataList;
private
List<person> personList;
private
Button first;
private
Button last;
private
Button next;
private
Button end;
private
TextView pageInfo;
@SuppressWarnings
(
"static-access"
)
@Override
public
void
onCreate(Bundle savedInstanceState) {
super
.onCreate(savedInstanceState);
setContentView(R.layout.main);
listView = (ListView) findViewById(R.id.listView);
personService =
new
PersonService(
this
,
"androidla"
,
null
,
1
);
personList = personService.queryList(
0
, PageUtil.pagesize);
dataList =
new
ArrayList<map <String, String>>();
for
(Person person : personList) {
map =
new
HashMap<string , String>();
map.put(
"id"
, person.getId().toString());
map.put(
"name"
, person.getName());
map.put(
"age"
, person.getAge().toString());
dataList.add(map);
}
SimpleAdapter adapter =
new
SimpleAdapter(SqliteActivity.
this
, dataList, R.layout.person,
new
String[]{
"id"
,
"name"
,
"age"
},
new
int
[]{R.id.personId, R.id.personName, R.id.personAge});
listView.setAdapter(adapter);
listView.setOnItemClickListener(onItemClickListener);
first = (Button) findViewById(R.id.first);
first.setOnClickListener(onClickListener);
last = (Button) findViewById(R.id.last);
last.setOnClickListener(onClickListener);
last.setEnabled(
false
);
next = (Button) findViewById(R.id.next);
next.setOnClickListener(onClickListener);
end = (Button) findViewById(R.id.end);
end.setOnClickListener(onClickListener);
pageInfo = (TextView) findViewById(R.id.pageInfo);
pageInfo.setText(
"("
+ PageUtil.currentpage +
"/"
+ PageUtil.pagecount +
")"
);
}
private
OnItemClickListener onItemClickListener =
new
OnItemClickListener() {
@Override
public
void
onItemClick(AdapterView< ?> aView, View view,
int
arg2,
long
arg3) {
ListView listView = (ListView) aView;
Map</string><string , String> map = (Map</string><string , String>) listView.getItemAtPosition(arg2);
System.out.println();
Toast.makeText(SqliteActivity.
this
, map.get(
"id"
) +
":"
+ map.get(
"name"
) +
":"
+ map.get(
"age"
), Toast.LENGTH_SHORT).show();
}
};
private
OnClickListener onClickListener =
new
OnClickListener() {
@Override
public
void
onClick(View v) {
Button button = (Button) v;
dataList =
new
ArrayList<map <String, String>>();
listView.setAdapter(
null
);
List<person> personList =
null
;
switch
(button.getId()) {
case
R.id.first:
personList = PageUtil.getFirst();
break
;
case
R.id.last:
personList = PageUtil.getLast();
break
;
case
R.id.next:
personList = PageUtil.getNext();
break
;
case
R.id.end:
personList = PageUtil.getEnd();
break
;
}
pageInfo.setText(
"("
+ PageUtil.currentpage +
"/"
+ PageUtil.pagecount +
")"
);
if
(PageUtil.currentpage ==
1
&& PageUtil.pagecount !=
0
) {
last.setEnabled(
false
);
next.setEnabled(
true
);
}
else
if
(PageUtil.currentpage == PageUtil.pagecount && PageUtil.pagecount !=
0
) {
next.setEnabled(
false
);
last.setEnabled(
true
);
}
else
if
(PageUtil.pagecount ==
0
) {
first.setEnabled(
false
);
last.setEnabled(
false
);
next.setEnabled(
false
);
end.setEnabled(
false
);
}
else
{
last.setEnabled(
true
);
next.setEnabled(
true
);
}
for
(Person person : personList) {
map =
new
HashMap<string , String>();
map.put(
"id"
, person.getId().toString());
map.put(
"name"
, person.getName());
map.put(
"age"
, person.getAge().toString());
dataList.add(map);
}
SimpleAdapter adapter =
new
SimpleAdapter(SqliteActivity.
this
, dataList, R.layout.person,
new
String[]{
"id"
,
"name"
,
"age"
},
new
int
[]{R.id.personId, R.id.personName, R.id.personAge});
listView.setAdapter(adapter);
listView.setOnItemClickListener(onItemClickListener);
}
};
}
|
PageUtil.java 分页帮助类
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
|
package
net.androidla.util;
import
java.util.List;
import
net.androidla.service.PersonService;
import
net.androidla.vo.Person;
public
class
PageUtil {
public
static
int
pagesize =
12
;
public
static
int
currentpage =
1
;
public
static
int
count =
new
Long(PersonService.getCount()).intValue();
public
static
int
pagecount = count / pagesize;
public
static
List<person> getFirst() {
currentpage =
1
;
return
PersonService.queryList(
0
, pagesize);
}
public
static
List</person><person> getEnd() {
currentpage = pagecount;
if
(count % pagesize ==
0
) {
return
PersonService.queryList((pagecount -
1
) * pagesize, pagesize);
}
else
{
return
PersonService.queryList(pagecount * pagesize, pagesize);
}
}
public
static
List</person><person> getLast() {
if
(currentpage ==
1
) {
return
getFirst();
}
else
{
currentpage --;
return
PersonService.queryList(currentpage * pagesize, pagesize);
}
}
public
static
List</person><person> getNext() {
if
(currentpage == pagecount) {
return
getEnd();
}
else
{
currentpage ++;
return
PersonService.queryList((currentpage -
1
) * pagesize, pagesize);
}
}
}
|
Person.java 实体类
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
|
package
net.androidla.vo;
public
class
Person {
private
Integer id;
private
String name;
private
Short age;
public
Person(Integer id, String name, Short age) {
super
();
this
.id = id;
this
.name = name;
this
.age = age;
}
public
Person(String name, Short age) {
this
.name = name;
this
.age = age;
}
public
Short getAge() {
return
age;
}
public
void
setAge(Short age) {
this
.age = age;
}
public
Integer getId() {
return
id;
}
public
void
setId(Integer id) {
this
.id = id;
}
public
String getName() {
return
name;
}
public
void
setName(String name) {
this
.name = name;
}
@Override
public
String toString() {
return
"Person [id="
+ id +
", name="
+ name +
", age="
+ age +
"]"
;
}
}
|