android 数据库sqllite示例

这篇文章写得好, 行文简洁, 清晰, 内容详尽. 一个例子可将你一脚踢进android数据库大门. 剩下的就是搭积木了.

Objectives:

How to start using SQLite API?
How to create new database & database tables?
How to perform CRUD “Create, Read, Update and Delete” operations?

Environment & Tools:

Android Developer Tools (ADT) (or Eclipse + ADT plugin)
AVD Nexus S Android 4.3 “emulator”
Min SDK 8

What we are building here?

We will build an App that can store & retrieve books title and author name.

( 1 ) Create Android Application

File >> New >> Android Application
Enter App name: SQLite App
Enter Project name: android-sqlite
Pakcage: com.hmkcode.android
Keep other defualt selections, click Next until you reach Finish

( 2 ) Data Model Design “Table Structure”

We want to create the following:

One Database instance: “BookDB“.
One Table: “books” with three columns id, title & author

( 3 ) Object Model “Book.java”

Create one Java bean class: Book.java

/src/com/hmkcode/android/model/Book.java

package com.hmkcode.android.model;

public class Book {

private int id;

private String title;

private String author;

public Book(){}

public Book(String title, String author) {

super();

this.title = title;

this.author = author;

}

//getters & setters

@Override

public String toString() {

return "Book [id=" + id + ", title=" + title + ", author=" + author

+ "]";

}

( 4 ) extends SQLiteOpenHelper

The recommended method to create a new SQLite database is to create a subclass of SQLiteOpenHelper and override the onCreate() method, in which you can execute a SQLite command to create tables in the database.

This is the main step.

Create a new class MySQLiteHelper extends SQLiteOpenHelper.
MySQLiteHelper constructor must call the super class constructor.
Override onCreate() method to create the table(s)
Override onUpgrade() to drop old tables and create new ones.

/src/com/hmkcode/android/sqlite/MySQLiteHelper.java

package com.hmkcode.android.sqlite;

import android.content.Context;

import android.database.sqlite.SQLiteDatabase;

import android.database.sqlite.SQLiteOpenHelper;

public class MySQLiteHelper extends SQLiteOpenHelper {

// Database Version

private static final int DATABASE_VERSION = 1;

// Database Name

private static final String DATABASE_NAME = "BookDB";

public MySQLiteHelper(Context context) {

super(context, DATABASE_NAME, null, DATABASE_VERSION);

}

@Override

public void onCreate(SQLiteDatabase db) {

// SQL statement to create book table

String CREATE_BOOK_TABLE = "CREATE TABLE books ( " +

"id INTEGER PRIMARY KEY AUTOINCREMENT, " +

"title TEXT, "+

"author TEXT )";

// create books table

db.execSQL(CREATE_BOOK_TABLE);

}

@Override

public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {

// Drop older books table if existed

db.execSQL("DROP TABLE IF EXISTS books");

// create fresh books table

this.onCreate(db);

}

This class MySQLiteHelper will just create for us Database “BookDB” with one empty table “books“.
Next, we will create methods to help us populate “insert”, read “select”, update and delete book(s) from this table.

( 5 ) Add, Get, Update & Delete a Book

In the same file “MySQLiteHelper.java” we will add 5 methods

addBook(Book book)
getBook(int id)
getAllBooks()
update(Book book)
delete(Book book)

Some static constants

Define static constants for table & columns names;

    // Books table name    private static final String TABLE_BOOKS = "books";    // Books Table Columns names    private static final String KEY_ID = "id";    private static final String KEY_TITLE = "title";    private static final String KEY_AUTHOR = "author";    private static final String[] COLUMNS = {KEY_ID,KEY_TITLE,KEY_AUTHOR};

1. addBook(Book book)

Notice:

ConentValues this class is used to store a set of values.
Log.d() just for logging, so we can see the result later on LogCat when we run the App.

public void addBook(Book book){

//for logging

Log.d("addBook", book.toString());

// 1. get reference to writable DB

SQLiteDatabase db = this.getWritableDatabase();

// 2. create ContentValues to add key "column"/value

ContentValues values = new ContentValues();

values.put(KEY_TITLE, book.getTitle()); // get title

values.put(KEY_AUTHOR, book.getAuthor()); // get author

// 3. insert

db.insert(TABLE_BOOKS, // table

null, //nullColumnHack

values); // key/value -> keys = column names/ values = column values

// 4. close

db.close();

}

2. getBook(int id)

public Book getBook(int id){

// 1. get reference to readable DB

SQLiteDatabase db = this.getReadableDatabase();

// 2. build query

Cursor cursor =

db.query(TABLE_BOOKS, // a. table

COLUMNS, // b. column names

" id = ?", // c. selections

new String[] { String.valueOf(id) }, // d. selections args

null, // e. group by

null, // f. having

null, // g. order by

null); // h. limit

// 3. if we got results get the first one

if (cursor != null)

cursor.moveToFirst();

// 4. build book object

Book book = new Book();

book.setId(Integer.parseInt(cursor.getString(0)));

book.setTitle(cursor.getString(1));

book.setAuthor(cursor.getString(2));

//log

Log.d("getBook("+id+")", book.toString());

// 5. return book

return book;

}

3. getAllBooks()

public List<Book> getAllBooks() {

List<Book> books = new LinkedList<Book>();

// 1. build the query

String query = "SELECT * FROM " + TABLE_BOOKS;

// 2. get reference to writable DB

SQLiteDatabase db = this.getWritableDatabase();

Cursor cursor = db.rawQuery(query, null);

// 3. go over each row, build book and add it to list

Book book = null;

if (cursor.moveToFirst()) {

do {

book = new Book();

book.setId(Integer.parseInt(cursor.getString(0)));

book.setTitle(cursor.getString(1));

book.setAuthor(cursor.getString(2));

// Add book to books

books.add(book);

} while (cursor.moveToNext());

}

Log.d("getAllBooks()", books.toString());

// return books

return books;

}

4. update(Book book)

public int updateBook(Book book) {

// 1. get reference to writable DB

SQLiteDatabase db = this.getWritableDatabase();

// 2. create ContentValues to add key "column"/value

ContentValues values = new ContentValues();

values.put("title", book.getTitle()); // get title

values.put("author", book.getAuthor()); // get author

// 3. updating row

int i = db.update(TABLE_BOOKS, //table

values, // column/value

KEY_ID+" = ?", // selections

new String[] { String.valueOf(book.getId()) }); //selection args

// 4. close

db.close();

return i;

}

5. delete(Book book)

public void deleteBook(Book book) {

// 1. get reference to writable DB

SQLiteDatabase db = this.getWritableDatabase();

// 2. delete

db.delete(TABLE_BOOKS, //table name

KEY_ID+" = ?", // selections

new String[] { String.valueOf(book.getId()) }); //selections args

// 3. close

db.close();

//log

Log.d("deleteBook", book.toString());

}

Complete MySQLiteHelper.java Code:

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

164

165

166

167

168

169

170

171

172

173

174

175

176

177

178

179

180

181

182

183

184

185

package com.hmkcode.android.sqlite;

import java.util.LinkedList;

import java.util.List;

import com.hmkcode.android.model.Book;

import android.content.ContentValues;

import android.content.Context;

import android.database.Cursor;

import android.database.sqlite.SQLiteDatabase;

import android.database.sqlite.SQLiteOpenHelper;

import android.util.Log;

public class MySQLiteHelper extends SQLiteOpenHelper {

// Database Version

private static final int DATABASE_VERSION = 1;

// Database Name

private static final String DATABASE_NAME = "BookDB";

public MySQLiteHelper(Context context) {

super(context, DATABASE_NAME, null, DATABASE_VERSION);

}

@Override

public void onCreate(SQLiteDatabase db) {

// SQL statement to create book table

String CREATE_BOOK_TABLE = "CREATE TABLE books ( " +

"id INTEGER PRIMARY KEY AUTOINCREMENT, " +

"title TEXT, "+

"author TEXT )";

// create books table

db.execSQL(CREATE_BOOK_TABLE);

}

@Override

public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {

// Drop older books table if existed

db.execSQL("DROP TABLE IF EXISTS books");

// create fresh books table

this.onCreate(db);

}

//---------------------------------------------------------------------

/**

* CRUD operations (create "add", read "get", update, delete) book + get all books + delete all books

*/

// Books table name

private static final String TABLE_BOOKS = "books";

// Books Table Columns names

private static final String KEY_ID = "id";

private static final String KEY_TITLE = "title";

private static final String KEY_AUTHOR = "author";

private static final String[] COLUMNS = {KEY_ID,KEY_TITLE,KEY_AUTHOR};

public void addBook(Book book){

Log.d("addBook", book.toString());

// 1. get reference to writable DB

SQLiteDatabase db = this.getWritableDatabase();

// 2. create ContentValues to add key "column"/value

ContentValues values = new ContentValues();

values.put(KEY_TITLE, book.getTitle()); // get title

values.put(KEY_AUTHOR, book.getAuthor()); // get author

// 3. insert

db.insert(TABLE_BOOKS, // table

null, //nullColumnHack

values); // key/value -> keys = column names/ values = column values

// 4. close

db.close();

}

public Book getBook(int id){

// 1. get reference to readable DB

SQLiteDatabase db = this.getReadableDatabase();

// 2. build query

Cursor cursor =

db.query(TABLE_BOOKS, // a. table

COLUMNS, // b. column names

" id = ?", // c. selections

new String[] { String.valueOf(id) }, // d. selections args

null, // e. group by

null, // f. having

null, // g. order by

null); // h. limit

// 3. if we got results get the first one

if (cursor != null)

cursor.moveToFirst();

// 4. build book object

Book book = new Book();

book.setId(Integer.parseInt(cursor.getString(0)));

book.setTitle(cursor.getString(1));

book.setAuthor(cursor.getString(2));

Log.d("getBook("+id+")", book.toString());

// 5. return book

return book;

}

// Get All Books

public List<Book> getAllBooks() {

List<Book> books = new LinkedList<Book>();

// 1. build the query

String query = "SELECT * FROM " + TABLE_BOOKS;

// 2. get reference to writable DB

SQLiteDatabase db = this.getWritableDatabase();

Cursor cursor = db.rawQuery(query, null);

// 3. go over each row, build book and add it to list

Book book = null;

if (cursor.moveToFirst()) {

do {

book = new Book();

book.setId(Integer.parseInt(cursor.getString(0)));

book.setTitle(cursor.getString(1));

book.setAuthor(cursor.getString(2));

// Add book to books

books.add(book);

} while (cursor.moveToNext());

}

Log.d("getAllBooks()", books.toString());

// return books

return books;

}

// Updating single book

public int updateBook(Book book) {

// 1. get reference to writable DB

SQLiteDatabase db = this.getWritableDatabase();

// 2. create ContentValues to add key "column"/value

ContentValues values = new ContentValues();

values.put("title", book.getTitle()); // get title

values.put("author", book.getAuthor()); // get author

// 3. updating row

int i = db.update(TABLE_BOOKS, //table

values, // column/value

KEY_ID+" = ?", // selections

new String[] { String.valueOf(book.getId()) }); //selection args

// 4. close

db.close();

return i;

}

// Deleting single book

public void deleteBook(Book book) {

// 1. get reference to writable DB

SQLiteDatabase db = this.getWritableDatabase();

// 2. delete

db.delete(TABLE_BOOKS,

KEY_ID+" = ?",

new String[] { String.valueOf(book.getId()) });

// 3. close

db.close();

Log.d("deleteBook", book.toString());

}

( 6 ) Using MySQLiteHelper in Activity

/src/com/hmkcode/android/MainActivity.java

package com.hmkcode.android;

import java.util.List;

import com.hmkcode.android.model.Book;

import com.hmkcode.android.sqlite.MySQLiteHelper;

import android.os.Bundle;

import android.app.Activity;

public class MainActivity extends Activity {

@Override

protected void onCreate(Bundle savedInstanceState) {

super.onCreate(savedInstanceState);

setContentView(R.layout.activity_main);

MySQLiteHelper db = new MySQLiteHelper(this);

/**

* CRUD Operations

* */

// add Books

db.addBook(new Book("Android Application Development Cookbook", "Wei Meng Lee"));

db.addBook(new Book("Android Programming: The Big Nerd Ranch Guide", "Bill Phillips and Brian Hardy"));

db.addBook(new Book("Learn Android App Development", "Wallace Jackson"));

// get all books

List<Book> list = db.getAllBooks();

// delete one book

db.deleteBook(list.get(0));

// get all books

db.getAllBooks();

}

Deploy the App on the emulator and run it.
Look for the results in LogCat!

( 7 ) Where to See the Results? LogCat!

Log function Log.d() used within the addBook(), getBook()…etc. will log database operations on LogCat Eclipse (ADT) window.

If you don’t see LogCat window, you need to add it.
go to Window >> Show View >> Others… (on Show View window) Android >> LogCat

LogCat view

转自http://hmkcode.com/android-simple-sqlite-database-tutorial/

Objectives:

Environment & Tools:

( 1 ) Create Android Application

( 2 ) Data Model Design “Table Structure”

( 3 ) Object Model “Book.java”

( 4 ) extends SQLiteOpenHelper

( 5 ) Add, Get, Update & Delete a Book

Some static constants

1. addBook(Book book)

2. getBook(int id)

3. getAllBooks()

4. update(Book book)

5. delete(Book book)

Complete MySQLiteHelper.java Code:

( 6 ) Using MySQLiteHelper in Activity

( 7 ) Where to See the Results? LogCat!

今日热点

一周热点