1. DbHelper类，继承系统的SQLiteOpenHelper类，创建，更新，获取游标
1 package tool;
2
3 import android.content.Context;
4 import android.database.Cursor;
5 import android.database.sqlite.SQLiteDatabase;
6 import android.database.sqlite.SQLiteOpenHelper;
7
8 public class DbHelper extends SQLiteOpenHelper {
9
10 public final static String DATABASE_NAME = "photo_db";
11 public final static String TABLE_NAME = "photo_table";
12 public final static String FIELD_ID = "id";
13 public final static String FIELD_NAME = "name";
14 public final static String FIELD_SCORE = "score";
15
16 public DbHelper(Context context) {
17 super(context, DATABASE_NAME, null, 1);
18 // TODO Auto-generated constructor stub
19 }
20
21 @Override
22 public void onCreate(SQLiteDatabase db) {
23 // TODO Auto-generated method stubsss
24 String sql = "create table " + TABLE_NAME + "(" + FIELD_ID
25 + " integer primary key autoincrement," + FIELD_NAME + " text,"
26 + FIELD_SCORE + " float);";
27 db.execSQL(sql);
28 }
29
30 @Override
31 public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
32 // TODO Auto-generated method stub
33 String sql = "drop table if exists " + TABLE_NAME;
34 db.execSQL(sql);
35 onCreate(db);
36 }
37
38 public Cursor select() {
39 SQLiteDatabase db = this.getReadableDatabase();
40 Cursor cursor = db.query(TABLE_NAME, null, null, null, null, null,
41 " id asc");
42 return cursor;
43 }
44
45 }
2. ObjectService类，封装操作数据库的常用方法。Object这里用的是具体的Photo类来保存对象
1 package tool;
2
3 import java.util.ArrayList;
4
5 import android.content.Context;
6 import android.database.Cursor;
7 import android.database.sqlite.SQLiteDatabase;
8 import android.util.Log;
9
10 public class PhotoService {
11 public static final String TAG = "dbtest";
12 DbHelper testdb;
13
14 public PhotoService(Context context) {
15 testdb = new DbHelper(context);
16
17 }
18
19 public void save(Photo photo) {// test ok
20
21 SQLiteDatabase db = testdb.getWritableDatabase();
22 // ContentValues cv = new ContentValues();
23 // cv.put(FIELD_NAME, photo.getName());
24 // cv.put(FIELD_SCORE, photo.getScore());
25 // long row = db.insert(TABLE_NAME, null, cv);
26 db.execSQL("insert into photo_table(name,score) values(?,?)",
27 new Object[] { photo.getName(), photo.getScore() });
28 }
29
30 public void saveAll(ArrayList<Photo> photos) {// test ok
31 for (Photo photo : photos) {
32 save(photo);
33 }
34 }
35
36 public void delete(Photo photo) {// test ok
37 SQLiteDatabase db = testdb.getWritableDatabase();
38 // String where = FIELD_ID + "=?";
39 // String[] whereValue = { Integer.toString(photo.getId()) };
40 // db.delete(TABLE_NAME, where, whereValue);
41 db.execSQL("delete from photo_table where id=?",
42 new Object[] { photo.getId() });
43 }
44
45 public void update(Photo photo) {// test ok
46 SQLiteDatabase db = testdb.getWritableDatabase();
47 // String where = FIELD_ID + "=?";
48 // String[] whereValue = { Integer.toString(photo.getId()) };
49 // ContentValues cv = new ContentValues();
50 // cv.put(FIELD_SCORE, score);
51 // db.update(TABLE_NAME, cv, where, whereValue);
52 db.execSQL(
53 "update photo_table set name=?,score=? where id=?",
54 new Object[] { photo.getName(), photo.getScore(), photo.getId() });
55 }
56
57 public void updateAll(ArrayList<Photo> photos) {// test ok
58 for (Photo photo : photos) {
59 update(photo);
60 }
61 }
62
63 public ArrayList<Photo> initiallize(ArrayList<Photo> filephotos,// test ok
64 ArrayList<Photo> dbphotos) {
65 if (dbphotos.isEmpty()) {
66 saveAll(filephotos);
67 } else {
68 for (Photo filephoto : filephotos) {// 从文件目录中逐一向数据库中查找，没有则向数据库中进行添加
69 for (int i = 0; i < dbphotos.size(); i++) {
70 if (filephoto.equals(dbphotos.get(i))) {
71 break;
72 } else {
73 if (i == dbphotos.size() - 1) {
74 save(filephoto);
75 }
76 }
77 }
78 }
79 for (Photo dbphoto : dbphotos) {// 数据库中逐一向文件目录中查找，没有则向数据库中进行删除
80 for (int i = 0; i < filephotos.size(); i++) {
81 if (dbphoto.equals(filephotos.get(i))) {
82 break;
83 } else {
84 if (i == filephotos.size() - 1) {
85 delete(dbphoto);
86 }
87 }
88 }
89 }
90 }
91 return findAll();
92 }
93
94 public Photo find(String name) {// test ok
95 Photo photo = new Photo();
96 SQLiteDatabase db = testdb.getReadableDatabase();
97 Cursor cursor = db.rawQuery(
98 "select id,name,score from photo_table where name=?",
99 new String[] { name });
100 if (cursor.moveToNext()) {
101 photo.setId(cursor.getInt(0));
102 photo.setName(cursor.getString(1));
103 photo.setScore(cursor.getFloat(2));
104 Log.v(TAG, photo.toString());
105 cursor.close();
106 return photo;
107 }
108 cursor.close();
109 return null;
110 }
111
112 public ArrayList<Photo> findAll() {// test ok
113 ArrayList<Photo> photos = new ArrayList<Photo>();
114 Cursor cursor = testdb.select();
115 while (cursor.moveToNext()) {
116 Photo photo = new Photo(cursor.getInt(0), cursor.getString(1),
117 cursor.getFloat(2));
118 photos.add(photo);
119 Log.v(TAG, photo.toString());
120
121 }
122 cursor.close();
123 return photos;
124 }
125
126 public long getCount() {// test ok
127 SQLiteDatabase db = testdb.getWritableDatabase();
128 Cursor cursor = db.rawQuery("select count(*) from photo_table", null);
129 cursor.moveToFirst();
130 long count = cursor.getLong(0);
131 Log.v(TAG, String.valueOf(count));
132 cursor.close();
133 return count;
134 }
135
136 public void clear() {// test ok
137 SQLiteDatabase db = testdb.getWritableDatabase();
138 db.execSQL("delete from photo_table");
139 }
140 }