Android数据库SQLite的使用示例

Android 提供了三种数据存储方式，第一种是文件存储;第二种是SharedPreferences存储;第三种就是数据库SQLiteDatabase存储。文件存储我就不用多说了，而SharedPreferences可以存取简单的数据(int,double,float.etc)，它经常用于数据缓存，因为它读取存储简单。详细可以参见本系列。Android高手进阶教程(七)之----Android 中Preferences的使用!

今天我们将讲一下SQLiteDatabase的使用。而掌握SqliteDatabase，将会我们接下来掌握ContentProvider打下良好的基石。为了让大家更好的掌握，我们手把手完成该节的Demo。

第一步：新建一个Android工程，命名为SQLiteDatabaseDemo.

 

第二步:创建一个新的类BooksDB.java这个类要继承于android.database.sqlite.SQLiteOpenHelper抽象类，我们要实现其中两个方法：onCreate(),onUpdate.具体代码如下：

?

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

packagecom.android.tutor;

importandroid.content.ContentValues;

importandroid.content.Context;

importandroid.database.Cursor;

importandroid.database.sqlite.SQLiteDatabase;

importandroid.database.sqlite.SQLiteOpenHelper;

publicclassBooksDB extendsSQLiteOpenHelper {

privatefinalstatic String DATABASE_NAME = "BOOKS.db";

privatefinalstatic int DATABASE_VERSION = 1;

privatefinalstatic String TABLE_NAME = "books_table";

publicfinalstatic String BOOK_ID = "book_id";

publicfinalstatic String BOOK_NAME = "book_name";

publicfinalstatic String BOOK_AUTHOR = "book_author";

 

publicBooksDB(Context context) {

// TODO Auto-generated constructor stub

super(context, DATABASE_NAME, null, DATABASE_VERSION);

}

//创建table

@Override

publicvoidonCreate(SQLiteDatabase db) {

String sql = "CREATE TABLE " + TABLE_NAME + " (" + BOOK_ID

+ " INTEGER primary key autoincrement, " + BOOK_NAME + " text, "+ BOOK_AUTHOR +" text);";

db.execSQL(sql);

}

@Override

publicvoidonUpgrade(SQLiteDatabase db, intoldVersion, intnewVersion) {

String sql = "DROP TABLE IF EXISTS " + TABLE_NAME;

db.execSQL(sql);

onCreate(db);

}

 

publicCursor select() {

SQLiteDatabase db = this.getReadableDatabase();

Cursor cursor = db

.query(TABLE_NAME, null, null, null, null, null, null);

returncursor;

}

//增加操作

publiclonginsert(String bookname,String author)

{

SQLiteDatabase db = this.getWritableDatabase();

/* ContentValues */

ContentValues cv = newContentValues();

cv.put(BOOK_NAME, bookname);

cv.put(BOOK_AUTHOR, author);

longrow = db.insert(TABLE_NAME, null, cv);

returnrow;

}

//删除操作

publicvoiddelete(intid)

{

SQLiteDatabase db = this.getWritableDatabase();

String where = BOOK_ID + " = ?";

String[] whereValue ={ Integer.toString(id) };

db.delete(TABLE_NAME, where, whereValue);

}

//修改操作

publicvoidupdate(intid, String bookname,String author)

{

SQLiteDatabase db = this.getWritableDatabase();

String where = BOOK_ID + " = ?";

String[] whereValue = { Integer.toString(id) };

 

ContentValues cv = newContentValues();

cv.put(BOOK_NAME, bookname);

cv.put(BOOK_AUTHOR, author);

db.update(TABLE_NAME, cv, where, whereValue);

}

}

第三步:修改main.xml布局如下，由两个EditText和一个ListView组成，代码如下:

?

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

<?xmlversion="1.0"encoding="utf-8"?>

<LinearLayoutxmlns:android="http://schemas.android.com/apk/res/android"

 

android:orientation="vertical"

android:layout_width="fill_parent"

android:layout_height="fill_parent"

>

<EditText

android:id="@+id/bookname"

android:layout_width="fill_parent"

android:layout_height="wrap_content"

 

>

</EditText>

<EditText

android:id="@+id/author"

android:layout_width="fill_parent"

android:layout_height="wrap_content"

>

</EditText>

<ListView

android:id="@+id/bookslist"

 

android:layout_width="fill_parent"

android:layout_height="wrap_content"

>

</ListView>

</LinearLayout>

第四步:修改SQLiteDatabaseDemo.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

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

packagecom.android.tutor;

importandroid.app.Activity;

importandroid.content.Context;

importandroid.database.Cursor;

importandroid.os.Bundle;

importandroid.view.Menu;

importandroid.view.MenuItem;

importandroid.view.View;

importandroid.view.ViewGroup;

importandroid.widget.AdapterView;

importandroid.widget.BaseAdapter;

importandroid.widget.EditText;

importandroid.widget.ListView;

importandroid.widget.TextView;

importandroid.widget.Toast;

publicclassSQLiteDatabaseDemo extendsActivity implementsAdapterView.OnItemClickListener {

privateBooksDB mBooksDB;

privateCursor mCursor;

privateEditText BookName;

privateEditText BookAuthor;

privateListView BooksList;

 

privateintBOOK_ID = 0;

protectedfinalstatic int MENU_ADD = Menu.FIRST;

protectedfinalstatic int MENU_DELETE = Menu.FIRST + 1;

protectedfinalstatic int MENU_UPDATE = Menu.FIRST + 2;

 

publicvoidonCreate(Bundle savedInstanceState) {

super.onCreate(savedInstanceState);

setContentView(R.layout.main);

setUpViews();

}

 

publicvoidsetUpViews(){

mBooksDB = newBooksDB(this);

mCursor = mBooksDB.select();

 

BookName = (EditText)findViewById(R.id.bookname);

BookAuthor = (EditText)findViewById(R.id.author);

BooksList = (ListView)findViewById(R.id.bookslist);

 

BooksList.setAdapter(newBooksListAdapter(this, mCursor));

BooksList.setOnItemClickListener(this);

}

 

@Override

publicbooleanonCreateOptionsMenu(Menu menu) {

super.onCreateOptionsMenu(menu);

 

menu.add(Menu.NONE, MENU_ADD, 0, "ADD");

menu.add(Menu.NONE, MENU_DELETE, 0, "DELETE");

menu.add(Menu.NONE, MENU_DELETE, 0, "UPDATE");

returntrue;

}

 

publicbooleanonOptionsItemSelected(MenuItem item)

{

super.onOptionsItemSelected(item);

switch(item.getItemId())

{

caseMENU_ADD:

add();

break;

caseMENU_DELETE:

delete();

break;

caseMENU_UPDATE:

update();

break;

}

returntrue;

}

 

publicvoidadd(){

String bookname = BookName.getText().toString();

String author = BookAuthor.getText().toString();

//书名和作者都不能为空，或者退出

if(bookname.equals("") || author.equals("")){

return;

}

mBooksDB.insert(bookname, author);

mCursor.requery();

BooksList.invalidateViews();

BookName.setText("");

BookAuthor.setText("");

Toast.makeText(this, "Add Successed!", Toast.LENGTH_SHORT).show();

}

 

publicvoiddelete(){

if(BOOK_ID == 0) {

return;

}

mBooksDB.delete(BOOK_ID);

mCursor.requery();

BooksList.invalidateViews();

BookName.setText("");

BookAuthor.setText("");

Toast.makeText(this, "Delete Successed!", Toast.LENGTH_SHORT).show();

}

 

publicvoidupdate(){

String bookname = BookName.getText().toString();

String author = BookAuthor.getText().toString();

//书名和作者都不能为空，或者退出

if(bookname.equals("") || author.equals("")){

return;

}

mBooksDB.update(BOOK_ID, bookname, author);

mCursor.requery();

BooksList.invalidateViews();

BookName.setText("");

BookAuthor.setText("");

Toast.makeText(this, "Update Successed!", Toast.LENGTH_SHORT).show();

}

 

@Override

publicvoidonItemClick(AdapterView<?> parent, View view, intposition, longid) {

 

mCursor.moveToPosition(position);

BOOK_ID = mCursor.getInt(0);

BookName.setText(mCursor.getString(1));

BookAuthor.setText(mCursor.getString(2));

 

}

 

publicclassBooksListAdapter extendsBaseAdapter{

privateContext mContext;

privateCursor mCursor;

publicBooksListAdapter(Context context,Cursor cursor) {

 

mContext = context;

mCursor = cursor;

}

@Override

publicintgetCount() {

returnmCursor.getCount();

}

@Override

publicObject getItem(intposition) {

returnnull;

}

@Override

publiclonggetItemId(intposition) {

return0;

}

@Override

publicView getView(intposition, View convertView, ViewGroup parent) {

TextView mTextView = newTextView(mContext);

mCursor.moveToPosition(position);

mTextView.setText(mCursor.getString(1) + "___"+ mCursor.getString(2));

returnmTextView;

}

 

}

}

第五步:运行程序效果如下:

 

 

 

 

 

第六步：查看我们所建的数据库。有两种方法：第一种用命令查看：adb shell ls data/data/com.android.tutor/databases。

另一种方法是用DDMS查看，在data/data下面对应的应用程序的包名 下会有如下数据库，如图所示：