Android SQLite数据库操作实例

Android的自带数据库SQLite小巧且功能强大，Android提供了两种方式去操作数据库，第一种是用SQL语句去操作数据，SQLite支持标准的SQL，其分页等操作与Mysql一样，以下是利用SQL操作SQLite：

import java.util.ArrayList;import java.util.List;import android.content.Context;import android.database.Cursor;import android.database.sqlite.SQLiteDatabase;public class PersonDaoClassic {private DBOpenHelper helper;public PersonDaoClassic(Context context) {helper = new DBOpenHelper(context);}public void insert(Person p) {// 打开可写数据库SQLiteDatabase db = helper.getWritableDatabase();// 执行SQL语句, 替换占位符db.execSQL("INSERT INTO person(name, balance) VALUES(?, ?)", new Object[] { p.getName(), p.getBalance() });// 释放资源db.close();}public void delete(int id) {SQLiteDatabase db = helper.getWritableDatabase();db.execSQL("DELETE FROM person WHERE id=?", new Object[] { id });db.close();}public void update(Person p) {SQLiteDatabase db = helper.getWritableDatabase();db.execSQL("UPDATE person SET name=?, balance=? WHERE id=?", new Object[] { p.getName(), p.getBalance(), p.getId() });db.close();}public Person query(int id) {SQLiteDatabase db = helper.getReadableDatabase();// 执行原始查询, 得到一个Cursor(类似ResultSet)Cursor c = db.rawQuery("SELECT name, balance FROM person WHERE id=?", new String[] { String.valueOf(id) });Person p = null;// 判断Cursor是否有下一条记录if (c.moveToNext())// 从Cursor中获取数据, 创建Person对象p = new Person(id, c.getString(0), c.getInt(1));// 释放资源c.close();db.close();return p;}public List queryAll() {SQLiteDatabase db = helper.getReadableDatabase();Cursor c = db.rawQuery("SELECT id, name, balance FROM person", null);List persons = new ArrayList();while (c.moveToNext())persons.add(new Person(c.getInt(0), c.getString(1), c.getInt(2)));c.close();db.close();return persons;}public List queryPage(int pageNum, int capacity) {// 开始索引String start = String.valueOf((pageNum - 1) * capacity);// 查询的个数String length = String.valueOf(capacity);SQLiteDatabase db = helper.getReadableDatabase();// 翻页查询语句, 和MySQL中相同Cursor c = db.rawQuery("SELECT id, name, balance FROM person LIMIT ?,?", new String[]{start, length});List persons = new ArrayList();while (c.moveToNext())persons.add(new Person(c.getInt(0), c.getString(1), c.getInt(2)));c.close();db.close();return persons;}public int queryCount() {SQLiteDatabase db = helper.getReadableDatabase();// 查询记录条数Cursor c = db.rawQuery("SELECT COUNT(*) FROM person", null);c.moveToNext();int count =c.getInt(0); c.close();db.close();return count;}}

除上述方法以外，android还给我们带来了另外一种更加简单，也是android推荐使用的一种方式，此种方式把数据封装在ContentValues中，因为android编程过程中经常会使用到已经封装好了数据的ContentValues，所以使用第二种方式在有些时候更加便捷，以下是代码：

package cn.itcast.sqlite;import java.util.ArrayList;import java.util.List;import android.content.ContentValues;import android.content.Context;import android.database.Cursor;import android.database.sqlite.SQLiteDatabase;public class PersonDao {private DBOpenHelper helper;public PersonDao(Context context) {helper = new DBOpenHelper(context);}public void remit(int from, int to, int amount) {SQLiteDatabase db = helper.getWritableDatabase();// 开启事务db.beginTransaction();try {db.execSQL("UPDATE person SET balance=balance-? WHERE id=?", new Object[]{amount, from});db.execSQL("UPDATE person SET balance=balance+? WHERE id=?", new Object[]{amount, to});db.setTransactionSuccessful();} catch (Exception e) {e.printStackTrace();}// 结束事务, 将事务成功点前面的代码提交db.endTransaction();db.close();}public void insert(Person p) {SQLiteDatabase db = helper.getWritableDatabase();// 准备数据ContentValues values = new ContentValues();values.put("name", p.getName());values.put("balance", p.getBalance());// 通过ContentValues中的数据拼接SQL语句, 执行插入操作, id为表中的一个列名db.insert("person", "id", values);db.close();}public void delete(int id) {SQLiteDatabase db = helper.getWritableDatabase();// 执行删除操作, 在person表中删除id为指定值的记录db.delete("person", "id=?", new String[]{String.valueOf(id)});db.close();}public void update(Person p) {SQLiteDatabase db = helper.getWritableDatabase();// 要更新的数据ContentValues values = new ContentValues();values.put("name", p.getName());values.put("balance", p.getBalance());// 更新person表中id为指定值的记录db.update("person", values, "id=?", new String[] { String.valueOf(p.getId()) });db.close();}public Person query(int id) {SQLiteDatabase db = helper.getReadableDatabase();// 执行查询: 不去重复, 表是person, 查询name和balance两列, Where条件是"id=?", 占位符是id, 不分组, 没有having, 不排序, 没有分页Cursor c = db.query(false, "person", new String[]{"name", "balance"}, "id=?", new String[]{String.valueOf(id)}, null, null, null, null); Person p = null;// 判断Cursor是否有下一条记录if (c.moveToNext())// 从Cursor中获取数据, 创建Person对象p = new Person(id, c.getString(0), c.getInt(1));// 释放资源c.close();db.close();return p;}public List queryAll() {SQLiteDatabase db = helper.getReadableDatabase();// 查询所有记录, 倒序Cursor c = db.query(false, "person", new String[]{"id","name", "balance"}, null, null, null, null, "id DESC", null);List persons = new ArrayList();while (c.moveToNext())persons.add(new Person(c.getInt(0), c.getString(1), c.getInt(2)));c.close();db.close();return persons;}public List queryPage(int pageNum, int capacity) {// 开始索引String start = String.valueOf((pageNum - 1) * capacity);// 查询的个数String length = String.valueOf(capacity);SQLiteDatabase db = helper.getReadableDatabase();// 翻页查询Cursor c = db.query(false, "person", new String[]{"id","name", "balance"}, null, null, null, null, null, start + "," + length);List persons = new ArrayList();while (c.moveToNext())persons.add(new Person(c.getInt(0), c.getString(1), c.getInt(2)));c.close();db.close();return persons;}public int queryCount() {SQLiteDatabase db = helper.getReadableDatabase();// 查询记录条数Cursor c = db.query(false, "person", new String[]{"COUNT(*)"}, null, null, null, null, null, null);c.moveToNext();int count =c.getInt(0); c.close();db.close();return count;}}

更多相关文章

随机推荐