原文: Android SQLite Database with Multiple Tables
在上一篇教程Android SQLite Database Tutorial中,解释了如何在你的Android应用中使用SQLite数据库。但它只涵盖了你的数据库中只有一个表的情景。有很多人询问当数据库中有多个表时如何处理。
以下解释了当有多个表存在时如何处理SQLite数据库。
用例: Todo应用
为了便于理解,我在这一教程中用了一个真实的用例,一个TODO应用的数据库方案。本文不会涵盖如何设计应用的内容,但解释了数据库的设计,准备数据库辅助类和模型。
数据库设计
我考虑了一个基本Todo应用,提供最小化的功能,像创建一个todo note和赋予它一个tag(s)(类别)。因此我们的数据库只需要三个表。
这三个表是:
todos
- 存储所有的todo notes
tags
- 存储tags的列表
todo_tags
- 存储赋给todo的tags
下图解释了表的结构和表之间的关系:
开始一个新项目
在Eclipse中创建一个新项目:
1. 在Eclipse中通过File ⇒ New ⇒ Android ⇒ Application Project创建一个新项目。我将包命名为info.androidhive.sqlite
并将主活动命名为MainActivity.java
。
2. 我们需要额外两个包存放辅助类和模型类。右键src ⇒ New ⇒ Package并将它命名为info.androidhive.sqlite.helper
和info.androidhive.sqlite.model
。
为表创建模型类
下一步是为我们的数据库表创建模型类,将每一单行作为一个对象即可。我们只需要todos和tags两个模型。对于todo_tags我们不需要一个模型类。
3. 在info.androidhive.sqlite.helper
中创建一个新的类,代码如下。这是todos表的模型类。
Todo.java
package info.androidhive.sqlite.model; public class Todo { int id; String note; int status; String created_at; // constructors public Todo() { } public Todo(String note, int status) { this.note = note; this.status = status; } public Todo(int id, String note, int status) { this.id = id; this.note = note; this.status = status; } // setters public void setId(int id) { this.id = id; } public void setNote(String note) { this.note = note; } public void setStatus(int status) { this.status = status; } public void setCreatedAt(String created_at){ this.created_at = created_at; } // getters public long getId() { return this.id; } public String getNote() { return this.note; } public int getStatus() { return this.status; } }
4. 在同一个包中为tags表创建另一个模型类,命名为Tags.java。
Tag.java
package info.androidhive.sqlite.model; public class Tag { int id; String tag_name; // constructors public Tag() { } public Tag(String tag_name) { this.tag_name = tag_name; } public Tag(int id, String tag_name) { this.id = id; this.tag_name = tag_name; } // setter public void setId(int id) { this.id = id; } public void setTagName(String tag_name) { this.tag_name = tag_name; } // getter public int getId() { return this.id; } public String getTagName() { return this.tag_name; } }
数据库辅助类
数据库辅助类包含所有执行数据库操作的方法,例如打开连接,关闭连接,插入,更新,读取,删除等等。因为这个类是个辅助类,将它放在helper包下。
5. 在info.androidhive.sqlite.helper
创建另一个类,命名为DatabaseHelper.java
,继承自SQLiteOpenHelper。
public class DatabaseHelper extends SQLiteOpenHelper {
6. 添加所需的变量,例如数据库名称,数据库版本号,列名称。我还在onCreate()方法中执行了建表语句。在DatabaseHelper.java
中写如下代码:
DatabaseHelper.java
public class DatabaseHelper extends SQLiteOpenHelper { // Logcat tag private static final String LOG = "DatabaseHelper"; // Database Version private static final int DATABASE_VERSION = 1; // Database Name private static final String DATABASE_NAME = "contactsManager"; // Table Names private static final String TABLE_TODO = "todos"; private static final String TABLE_TAG = "tags"; private static final String TABLE_TODO_TAG = "todo_tags"; // Common column names private static final String KEY_ID = "id"; private static final String KEY_CREATED_AT = "created_at"; // NOTES Table - column nmaes private static final String KEY_TODO = "todo"; private static final String KEY_STATUS = "status"; // TAGS Table - column names private static final String KEY_TAG_NAME = "tag_name"; // NOTE_TAGS Table - column names private static final String KEY_TODO_ID = "todo_id"; private static final String KEY_TAG_ID = "tag_id"; // Table Create Statements // Todo table create statement private static final String CREATE_TABLE_TODO = "CREATE TABLE " + TABLE_TODO + "(" + KEY_ID + " INTEGER PRIMARY KEY," + KEY_TODO + " TEXT," + KEY_STATUS + " INTEGER," + KEY_CREATED_AT + " DATETIME" + ")"; // Tag table create statement private static final String CREATE_TABLE_TAG = "CREATE TABLE " + TABLE_TAG + "(" + KEY_ID + " INTEGER PRIMARY KEY," + KEY_TAG_NAME + " TEXT," + KEY_CREATED_AT + " DATETIME" + ")"; // todo_tag table create statement private static final String CREATE_TABLE_TODO_TAG = "CREATE TABLE " + TABLE_TODO_TAG + "(" + KEY_ID + " INTEGER PRIMARY KEY," + KEY_TODO_ID + " INTEGER," + KEY_TAG_ID + " INTEGER," + KEY_CREATED_AT + " DATETIME" + ")"; public DatabaseHelper(Context context) { super(context, DATABASE_NAME, null, DATABASE_VERSION); } @Override public void onCreate(SQLiteDatabase db) { // creating required tables db.execSQL(CREATE_TABLE_TODO); db.execSQL(CREATE_TABLE_TAG); db.execSQL(CREATE_TABLE_TODO_TAG); } @Override public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) { // on upgrade drop older tables db.execSQL("DROP TABLE IF EXISTS " + TABLE_TODO); db.execSQL("DROP TABLE IF EXISTS " + TABLE_TAG); db.execSQL("DROP TABLE IF EXISTS " + TABLE_TODO_TAG); // create new tables onCreate(db); }
CRUD (Create, Read, Update and Delete) 操作
现在起我们将一个方法一个方法地完善DatabaseHelper.class。
1. 创建一个Todo
这个方法将会在todos表中创建一个todo条目。在同一个方法中,我们还会赋予todo一个tag名称,这会在todo_tags表中插入一行。
/* * Creating a todo */ public long createToDo(Todo todo, long[] tag_ids) { SQLiteDatabase db = this.getWritableDatabase(); ContentValues values = new ContentValues(); values.put(KEY_TODO, todo.getNote()); values.put(KEY_STATUS, todo.getStatus()); values.put(KEY_CREATED_AT, getDateTime()); // insert row long todo_id = db.insert(TABLE_TODO, null, values); // assigning tags to todo for (long tag_id : tag_ids) { createTodoTag(todo_id, tag_id); } return todo_id; }
2. 获取一个Todo
以下将会从todos表中获取一个todo对象。
SELECT * FROM todos WHERE id = 1;
/* * get single todo */ public Todo getTodo(long todo_id) { SQLiteDatabase db = this.getReadableDatabase(); String selectQuery = "SELECT * FROM " + TABLE_TODO + " WHERE " + KEY_ID + " = " + todo_id; Log.e(LOG, selectQuery); Cursor c = db.rawQuery(selectQuery, null); if (c != null) c.moveToFirst(); Todo td = new Todo(); td.setId(c.getInt(c.getColumnIndex(KEY_ID))); td.setNote((c.getString(c.getColumnIndex(KEY_TODO)))); td.setCreatedAt(c.getString(c.getColumnIndex(KEY_CREATED_AT))); return td; }
3. 获取所有Todo对象
获取所有的todo对象包含读取所有的todo行,并添加到一个列表中。
SELECT * FROM todos;
/* * getting all todos * */ public List<Todo> getAllToDos() { List<Todo> todos = new ArrayList<Todo>(); String selectQuery = "SELECT * FROM " + TABLE_TODO; Log.e(LOG, selectQuery); SQLiteDatabase db = this.getReadableDatabase(); Cursor c = db.rawQuery(selectQuery, null); // looping through all rows and adding to list if (c.moveToFirst()) { do { Todo td = new Todo(); td.setId(c.getInt((c.getColumnIndex(KEY_ID)))); td.setNote((c.getString(c.getColumnIndex(KEY_TODO)))); td.setCreatedAt(c.getString(c.getColumnIndex(KEY_CREATED_AT))); // adding to todo list todos.add(td); } while (c.moveToNext()); } return todos; }
4. 获取一个Tag标签下的所有Todo对象
这和读取所有行相同,但要按照Tag标签过滤todo对象。查看下面的查询语句,它按照tag标签的监视列表来获取todo对象。
SELECT * FROM todos td, tags tg, todo_tags tt WHERE tg.tag_name = ‘Watchlist’ AND tg.id = tt.tag_id AND td.id = tt.todo_id;
/* * getting all todos under single tag * */ public List<Todo> getAllToDosByTag(String tag_name) { List<Todo> todos = new ArrayList<Todo>(); String selectQuery = "SELECT * FROM " + TABLE_TODO + " td, " + TABLE_TAG + " tg, " + TABLE_TODO_TAG + " tt WHERE tg." + KEY_TAG_NAME + " = ‘" + tag_name + "‘" + " AND tg." + KEY_ID + " = " + "tt." + KEY_TAG_ID + " AND td." + KEY_ID + " = " + "tt." + KEY_TODO_ID; Log.e(LOG, selectQuery); SQLiteDatabase db = this.getReadableDatabase(); Cursor c = db.rawQuery(selectQuery, null); // looping through all rows and adding to list if (c.moveToFirst()) { do { Todo td = new Todo(); td.setId(c.getInt((c.getColumnIndex(KEY_ID)))); td.setNote((c.getString(c.getColumnIndex(KEY_TODO)))); td.setCreatedAt(c.getString(c.getColumnIndex(KEY_CREATED_AT))); // adding to todo list todos.add(td); } while (c.moveToNext()); } return todos; }
5. 更新一个Todo
下面的方法将会更新一个todo。它只会更新todo的值,不包括tag标签。
/* * Updating a todo */ public int updateToDo(Todo todo) { SQLiteDatabase db = this.getWritableDatabase(); ContentValues values = new ContentValues(); values.put(KEY_TODO, todo.getNote()); values.put(KEY_STATUS, todo.getStatus()); // updating row return db.update(TABLE_TODO, values, KEY_ID + " = ?", new String[] { String.valueOf(todo.getId()) }); }
6. 删除一个Todo
将todo的id传给下面的方法,从数据库中删除一项todo。
/* * Deleting a todo */ public void deleteToDo(long tado_id) { SQLiteDatabase db = this.getWritableDatabase(); db.delete(TABLE_TODO, KEY_ID + " = ?", new String[] { String.valueOf(tado_id) }); }
到目前为止我们已经给todos表创建了CRUD方法。现在我们开始给tags表创建所需的方法。
7. 创建一个Tag
下面的方法将会给tags表插入一行。
/* * Creating tag */ public long createTag(Tag tag) { SQLiteDatabase db = this.getWritableDatabase(); ContentValues values = new ContentValues(); values.put(KEY_TAG_NAME, tag.getTagName()); values.put(KEY_CREATED_AT, getDateTime()); // insert row long tag_id = db.insert(TABLE_TAG, null, values); return tag_id; }
8. 获取所有的Tag标签
在tags表中执行查询所有的语句将会给你一个tag标签的列表。
SELECT * FROM tags;
/** * getting all tags * */ public List<Tag> getAllTags() { List<Tag> tags = new ArrayList<Tag>(); String selectQuery = "SELECT * FROM " + TABLE_TAG; Log.e(LOG, selectQuery); SQLiteDatabase db = this.getReadableDatabase(); Cursor c = db.rawQuery(selectQuery, null); // looping through all rows and adding to list if (c.moveToFirst()) { do { Tag t = new Tag(); t.setId(c.getInt((c.getColumnIndex(KEY_ID)))); t.setTagName(c.getString(c.getColumnIndex(KEY_TAG_NAME))); // adding to tags list tags.add(t); } while (c.moveToNext()); } return tags; }
9. 更新Tags
下面的方法将会更新tag。
/* * Updating a tag */ public int updateTag(Tag tag) { SQLiteDatabase db = this.getWritableDatabase(); ContentValues values = new ContentValues(); values.put(KEY_TAG_NAME, tag.getTagName()); // updating row return db.update(TABLE_TAG, values, KEY_ID + " = ?", new String[] { String.valueOf(tag.getId()) }); }
10. 删除一个Tag标签下的所有Tag和Todo对象。
下面的方法将会从数据库中删除一个tag。它也会删除该tag标签下的所有todo对象,但这一行为是可选的。
should_delete_all_tag_todos
= 传递true代表将会删除tag标签下的所有todo对象。
/* * Deleting a tag */ public void deleteTag(Tag tag, boolean should_delete_all_tag_todos) { SQLiteDatabase db = this.getWritableDatabase(); // before deleting tag // check if todos under this tag should also be deleted if (should_delete_all_tag_todos) { // get all todos under this tag List<Todo> allTagToDos = getAllToDosByTag(tag.getTagName()); // delete all todos for (Todo todo : allTagToDos) { // delete todo deleteToDo(todo.getId()); } } // now delete the tag db.delete(TABLE_TAG, KEY_ID + " = ?", new String[] { String.valueOf(tag.getId()) }); }
下面是访问todo_tags表中数据的方法。
11. 给Todo对象分配一个Tag
下面的方法将会给一个todo对象赋予一个tag标签。你也可以通过调用这个函数多次将多个tag赋予同一个todo对象。
/* * Creating todo_tag */ public long createTodoTag(long todo_id, long tag_id) { SQLiteDatabase db = this.getWritableDatabase(); ContentValues values = new ContentValues(); values.put(KEY_TODO_ID, todo_id); values.put(KEY_TAG_ID, tag_id); values.put(KEY_CREATED_AT, getDateTime()); long id = db.insert(TABLE_TODO_TAG, null, values); return id; }
12. 移除Todo的Tag
下面的方法将会移除赋予给一个todo的tag。
/** * Deleting a todo tag */ public void deleteToDoTag(long id) { SQLiteDatabase db = this.getWritableDatabase(); db.delete(TABLE_TODO, KEY_ID + " = ?", new String[] { String.valueOf(id) }); }
13. 改变todo的tag
下面的方法简单地替换了一个todo的tag标签。
/* * Updating a todo tag */ public int updateNoteTag(long id, long tag_id) { SQLiteDatabase db = this.getWritableDatabase(); ContentValues values = new ContentValues(); values.put(KEY_TAG_ID, tag_id); // updating row return db.update(TABLE_TODO, values, KEY_ID + " = ?", new String[] { String.valueOf(id) }); }
14. 关闭数据库连接
重要的一点一旦你使用完了数据库,不要忘了关闭数据库连接。当你无需再访问数据库时,调用下面的方法。
//closing database public void closeDB() { SQLiteDatabase db = this.getReadableDatabase(); if (db != null && db.isOpen()) db.close(); }