Android数据库SQLiteDatabase的使用

来源：互联网发布：各国网站域名后缀编辑：程序博客网时间：2024/05/29 09:23

大家好，好久没有更新博客了，最近由于身体不适让大家久等了，好了，直接进入主题。

Android提供了三种数据存储方式：
第一种是文件存储。
第二种是SharedPreferences存储。
第三种是数据库SQLiteDatabase存储。

文件存储我就不多说了，而SharedPreferences可以存取简单的数据(int,double,float.etc)，它经常用于数据缓存，因为它读取存储简单。详细可以参见本系列《Android高手进阶教程》7.Android Preferences的使用

今天我们将讲一下Android SQLiteDatabase的使用。而掌握SqliteDatabase，将会我们接下来掌握ContentProvider打下良好的基石。

为了让大家更好的掌握，我们手把手完成该节的Demo。

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

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
71
72
73
74
75
packagecom.android.tutor; 
  
importandroid.content.ContentValues; 
importandroid.content.Context; 
importandroid.database.Cursor; 
importandroid.database.sqlite.SQLiteDatabase; 
importandroid.database.sqlite.SQLiteOpenHelper; 
  
publicclassBooksDB extendsSQLiteOpenHelper { 
    privatefinalstaticString DATABASE_NAME = "BOOKS.db";
    privatefinalstaticintDATABASE_VERSION = 1;
    privatefinalstaticString TABLE_NAME = "books_table";
    publicfinalstaticString BOOK_ID = "book_id";
    publicfinalstaticString BOOK_NAME = "book_name";
    publicfinalstaticString 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
<?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 
        android:id="@+id/author" 
        android:layout_width="fill_parent"
        android:layout_height="wrap_content" 
        />
    <ListView 
        android:id="@+id/bookslist" 
        android:layout_width="fill_parent"
        android:layout_height="wrap_content" 
        />
</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
156
157
158
159
160
161
162
163
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 implements
        AdapterView.OnItemClickListener {
    privateBooksDB mBooksDB; 
    privateCursor mCursor; 
    privateEditText BookName; 
    privateEditText BookAuthor; 
    privateListView BooksList; 
  
    privateintBOOK_ID = 0;
    protectedfinalstaticintMENU_ADD = Menu.FIRST; 
    protectedfinalstaticintMENU_DELETE = Menu.FIRST + 1;
    protectedfinalstaticintMENU_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; 
        }
  
    }
}