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

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

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

public class ToDoDBAdapter { ... private <a href="https://www.jb51.cc/tag/sql/" target="_blank" class="keywords">sql</a>iteDatabase 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 <a href="https://www.jb51.cc/tag/sql/" target="_blank" class="keywords">sql</a>iteException { try { db = dbHelper.getWritableDatabase(); } catch (<a href="https://www.jb51.cc/tag/sql/" target="_blank" class="keywords">sql</a>iteException 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 <a href="https://www.jb51.cc/tag/sql/" target="_blank" class="keywords">sql</a>Exception { ... } public ToDoItem getToDoItem(long _rowIndex) throws <a href="https://www.jb51.cc/tag/sql/" target="_blank" class="keywords">sql</a>Exception { ... } private static class toDoDBOpenHelper extends <a href="https://www.jb51.cc/tag/sql/" target="_blank" class="keywords">sql</a>iteOpenHelper { public toDoDBOpenHelper(Context context,String name,CursorFactory factory,int version) { super(context,name,factory,version); } @Override public void onCreate(<a href="https://www.jb51.cc/tag/sql/" target="_blank" class="keywords">sql</a>iteDatabase _db) { ... } @Override public void onUpgrade(<a href="https://www.jb51.cc/tag/sql/" target="_blank" class="keywords">sql</a>iteDatabase _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*fromItem;
+----+-----------+---------+---------+----------+------------+----------+---------------------+
|ID|Name|InStock|OnOrder|Reserved|Department|Category|RowUpdate|
+----+-----------+---------+---------+----------+------------+----------+---------------------+
|1|Bloodshot|10|5|1|Popular|Rock|2005-10-0909:19:49|
|2|Most|10|5|2|Classical|Opera|2005-10-0909:19:49|
|3|Jazz|17|4|3|Popular|Jazz|2005-10-0909:19:49|
|4|Class|9|4|4|Classical|Dance|2005-10-0909:19:49|
|5|Violin|24|2|5|Classical|General|2005-10-0909:19:49|
|6|ChaCha|16|6|6|Classical|Vocal|2005-10-0909:19:49|
|7|Blues|2|25|7|Popular|Blues|2005-10-0909:19:49|
|8|Pure|32|3|18|Popular|Jazz|2005-10-0909:19:49|
|9|Mud|12|15|19|Popular|Country|2005-10-0909:19:49|
|10|The|5|20|11|Popular|NewAge|2005-10-0909:19:49|
|11|Embrace|24|11|12|Popular|NewAge|2005-10-0909:19:49|
|12|Magic|42|17|13|Classical|General|2005-10-0909:19:49|
|13|Lake|25|44|24|Classical|Dance|2005-10-0909:19:49|
|14|LaLala|20|10|5|Classical|Opera|2005-10-0909:19:49|
|15|Soul|15|30|16|Popular|Blues|2005-10-0909:19:49|
|16|Stages|42|0|7|Popular|Blues|2005-10-0909:19:49|
|17|Six|16|8|6|Classical|General|2005-10-0909:19:49|
+----+-----------+---------+---------+----------+------------+----------+---------------------+
17rowsinset(0.00sec)

MysqL>SELECT ID,Name,InStock
->FROM Item
->WHERE Department='Classical'
->ORDER BYIDDESC
->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