SQLite | 数据库adapter框架 | 查询语句

一、创建一个辅助类来简化数据库交互

通用的做法是创建一个数据库adapter辅助类，这个类封装了与数据库交互的细节。它提供了数据库的常用操作：增、删、改、查、数据库创建、打开、关闭的方法。它包含了一个SQLiteOpenHelper类的实例，这个类用来简化数据库创建、打开、升级。

 

adapter辅助类就象是一个大号黑盒子，SQLiteOpenHelper类就像是一个在大号黑盒子里面的小号黑盒子。最终的目的是为了实现更好的封装。

public class ToDoDBAdapter { ... private SQLiteDatabase db; private final Context context; private toDoDBOpenHelper dbHelper; ... public ToDoDBAdapter(Context _context) { this.context = _context; dbHelper = new toDoDBOpenHelper(context, DATABASE_NAME, null, DATABASE_VERSION); } public void close() { db.close(); } public void open() throws SQLiteException { try { db = dbHelper.getWritableDatabase(); } catch (SQLiteException ex) { db = dbHelper.getReadableDatabase(); } } //Insert a new task public long insertTask(ToDoItem _task) { ... } // Remove a task based on its index public boolean removeTask(long _rowIndex) { ... } // Update a task public boolean updateTask(long _rowIndex, String _task) { ... } public Cursor getAllToDoItemsCursor() { ... } public Cursor setCursorToToDoItem(long _rowIndex) throws SQLException { ... } public ToDoItem getToDoItem(long _rowIndex) throws SQLException { ... } private static class toDoDBOpenHelper extends SQLiteOpenHelper { public toDoDBOpenHelper(Context context, String name, CursorFactory factory, int version) { super(context, name, factory, version); } @Override public void onCreate(SQLiteDatabase _db) { ... } @Override public void onUpgrade(SQLiteDatabase _db, int _oldVersion, int _newVersion) { ... } } }

 

如果有一天你要做一个数据库相关的操作，我建议是现找一个参考的代码实例，仿照里面的框架代码来实现需求是很简单的呢。

 

二、查询语句

public Cursor query (String table, String[] columns, String selection, String[] selectionArgs,String groupBy, String having, String orderBy, String limit)

如果不明白这些参数是什么意思，那看看这个例子应该多点明了。呵呵。

mysql> select * from Item;
+----+-----------+---------+---------+----------+------------+----------+---------------------+
| ID | Name      | InStock | OnOrder | Reserved | Department | Category | RowUpdate           |
+----+-----------+---------+---------+----------+------------+----------+---------------------+
|  1 | Bloodshot |      10 |       5 |        1 | Popular    | Rock     | 2005-10-09 09:19:49 |
|  2 | Most      |      10 |       5 |        2 | Classical  | Opera    | 2005-10-09 09:19:49 |
|  3 | Jazz      |      17 |       4 |        3 | Popular    | Jazz     | 2005-10-09 09:19:49 |
|  4 | Class     |       9 |       4 |        4 | Classical  | Dance    | 2005-10-09 09:19:49 |
|  5 | Violin    |      24 |       2 |        5 | Classical  | General  | 2005-10-09 09:19:49 |
|  6 | Cha Cha   |      16 |       6 |        6 | Classical  | Vocal    | 2005-10-09 09:19:49 |
|  7 | Blues     |       2 |      25 |        7 | Popular    | Blues    | 2005-10-09 09:19:49 |
|  8 | Pure      |      32 |       3 |       18 | Popular    | Jazz     | 2005-10-09 09:19:49 |
|  9 | Mud       |      12 |      15 |       19 | Popular    | Country  | 2005-10-09 09:19:49 |
| 10 | The       |       5 |      20 |       11 | Popular    | New Age  | 2005-10-09 09:19:49 |
| 11 | Embrace   |      24 |      11 |       12 | Popular    | New Age  | 2005-10-09 09:19:49 |
| 12 | Magic     |      42 |      17 |       13 | Classical  | General  | 2005-10-09 09:19:49 |
| 13 | Lake      |      25 |      44 |       24 | Classical  | Dance    | 2005-10-09 09:19:49 |
| 14 | LaLala    |      20 |      10 |        5 | Classical  | Opera    | 2005-10-09 09:19:49 |
| 15 | Soul      |      15 |      30 |       16 | Popular    | Blues    | 2005-10-09 09:19:49 |
| 16 | Stages    |      42 |       0 |        7 | Popular    | Blues    | 2005-10-09 09:19:49 |
| 17 | Six       |      16 |       8 |        6 | Classical  | General  | 2005-10-09 09:19:49 |
+----+-----------+---------+---------+----------+------------+----------+---------------------+
17 rows in set (0.00 sec)

mysql> SELECT  ID, Name, InStock
     -> FROM  Item
     -> WHERE  Department='Classical'
     -> ORDER  BY ID DESC
     -> LIMIT  4;
+----+--------+---------+
| ID | Name   | InStock |
+----+--------+---------+
| 17 | Six    |      16 |
| 14 | LaLala |      20 |
| 13 | Lake   |      25 |
| 12 | Magic  |      42 |
+----+--------+---------+

 

 

 

[参考]

sql查询

http://www.java2s.com/Code/SQL/Select-Clause/ORDERandlimit.htm

http://notfatboy.iteye.com/blog/653357