android开发步步为营之21：Sqlite经典写法

SQLite是一个运行在客户端的小型数据库，开发的时候要特别注意一定记得关闭SQLiteDatabase和Cursor，否则会报SQLiteDatabase not close, Cursor not close的错误，因为使用完了，不关闭那么这两个对象就会一直占用手机内存，一般手机内存就100多M，所以一定要记得及时关闭，最近我写了一个缓存操作的类，其实就是将服务器拉过来的数据写进用户手机sd卡中去，增删改查都全了，经测试不会再报QLiteDatabase not close, Cursor not close的错误，因为数据操作完成之后，我记得及时关闭了，给出整个类的写法，供大家参考。
 /**
 * 缓存数据库操作类
 */
package com.snda.wallet.db;
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;
import com.snda.wallet.cache.CacheModel;
 
/**
 * SqlLite数据库操作类
 *
 * @author zhuzhifei 20120321
 */
public class SqlLiteForCache extends SQLiteOpenHelper {
    private String tag = "SqlLiteUtil";
    private final static String database_name = "cache_db";
 
    private final static int database_version = 1;
 
    private final static String table_name = "cache_table";
 
    public final static String field_id = "id";
 
    public final static String field_url = "url";
 
    public final static String field_key = "key";
 
    public final static String field_value = "value";
 
    public final static String field_inserttime = "inserttime";
 
    public SqlLiteForCache(Context context) {
       super(context, database_name, null, database_version);
 
       // TODO Auto-generated constructor stub
    }
 
    // 创建数据表
 
    @Override
    public void onCreate(SQLiteDatabase db) {
 
       // TODO Auto-generated method stub
       try {
           String sql = "create table " + table_name + "(" + field_id
 
           + " integer primary key autoincrement not null," + field_url
                  + " text," + field_key + " text ,"
 
                  + field_value + " text," + field_inserttime + " text)";
 
           db.execSQL(sql);
       } catch (Exception e) {
           // TODO: handle exception
           Log.i(tag, "onCreate:" + e);
       } finally {
         //       if (db != null) {
//     db.close();
//     }//这里不能关闭，否则报database not open
}
       }
 
    }
 
    // 有了新版本之后更新数据库
 
    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
 
       try {
           String sql = "drop table if exists " + this.table_name;
           db.execSQL(sql);
 
       } catch (Exception e) {
           // TODO: handle exception
           Log.i(tag, "onUpgrade:" + e);
       } finally {
           if (db != null) {
              db.close();
           }
       }
 
    }
 
    // 增
 
    public long insert(String url, String key, String value, String inserttime) {
 
       long row = 0;
       SQLiteDatabase db = this.getWritableDatabase();
       try {
           ContentValues cv = new ContentValues();
 
           cv.put(field_url, url);
 
           cv.put(field_key, key);
 
           cv.put(field_value, value);
 
           cv.put(field_inserttime, inserttime);
 
           row = db.insert(table_name, null, cv);
 
       } catch (Exception e) {
           // TODO: handle exception
           Log.i(tag, "insert:" + e);
       } finally {
           db.close();
       }
       return row;
 
    }
 
    // 事务增，先删除原纪录，插入新纪录
 
    public long insertTransaction(String url, String key, String value,
           String inserttime) {
 
       long row = 0;
       SQLiteDatabase db = this.getWritableDatabase();
       try {
 
           db.beginTransaction();
 
           // 先删老数据
           String where = this.field_key + "=?";
 
           String[] wherevalue = { key };
 
           db.delete(table_name, where, wherevalue);
           // 再插入新数据
           ContentValues cv = new ContentValues();
 
           cv.put(field_url, url);
 
           cv.put(field_key, key);
 
           cv.put(field_value, value);
 
           cv.put(field_inserttime, inserttime);
 
           row = db.insert(table_name, null, cv);
 
           db.setTransactionSuccessful();
 
       } catch (Exception e) {
           // TODO: handle exception
           Log.i(tag, "insertTransaction:" + e);
       } finally {
           db.endTransaction();
           db.close();
           return row;
       }
 
    }
 
    // 根据key删
 
    public void delete(String key) {
 
       SQLiteDatabase db = this.getWritableDatabase();
       try {
           String where = this.field_key + "=?";
           String[] wherevalue = { key };
           db.delete(table_name, where, wherevalue);
           db.close();
       } catch (Exception e) {
           // TODO: handle exception
           Log.i(tag, "delete:" + e);
       } finally {
           db.close();
       }
 
    }
 
    // 改
 
    public void update(String key, String value, String inserttime) {
 
       SQLiteDatabase db = this.getWritableDatabase();
       try {
 
           String where = this.field_key + "=?";
 
           String[] wherevalue = { key };
 
           ContentValues cv = new ContentValues();
 
           cv.put(field_value, value);
 
           cv.put(field_inserttime, inserttime);
 
           db.update(table_name, cv, where, wherevalue);
       } catch (Exception e) {
           // TODO: handle exception
           Log.i(tag, "update:" + e);
       } finally {
           db.close();
       }
    }
 
    // 查全部
 
    @SuppressWarnings("finally")
    public Cursor select() {
       Cursor cursor = null;
       SQLiteDatabase db = this.getReadableDatabase();
       try {
 
           cursor = db
 
           .query(table_name, null, null, null, null, null, null);
 
       } catch (Exception e) {
           // TODO: handle exception
           Log.i(tag, "select:" + e);
       } finally {
           db.close();
           return cursor;
 
       }
 
    }
 
    // 根据key查
 
    public CacheModel select(String key) {
       CacheModel cachemodel = new CacheModel();
       Cursor cursor = null;
       StringBuffer buffer = new StringBuffer();
       SQLiteDatabase db = this.getReadableDatabase();
       try {
           String selection = "key=?";
           String[] selectionArgs = { key };
           cursor = db.query(table_name, null, selection, selectionArgs, null,
                  null, null);
           if (cursor != null && cursor.getCount() > 0
                  && cursor.getColumnCount() > 0) {
 
              cursor.moveToPosition(0);// 一定要加上这句，否则系统不知道到读取的是哪一行，然后报错
              cachemodel.setId(cursor.getString(0));
              cachemodel.setUrl(cursor.getString(1));
              cachemodel.setKey(cursor.getString(2));
              cachemodel.setValue(cursor.getString(3));
              cachemodel.setInserttime(cursor.getString(4));
           }
           // cursor.close();// 一定记得关闭
 
       } catch (Exception e) {
           // TODO: handle exception
           Log.e(tag, "select:" + e);
       } finally {
           if (cursor != null) {
              cursor.close();// 一定记得关闭
           }
           db.close();
       }
       return cachemodel;
 
    }
 
    // 事务处理删除多条数据
 
    public void transaction(String[] ids) {
 
       SQLiteDatabase db = this.getWritableDatabase();
 
       try {
 
           db.beginTransaction();
 
           for (String id : ids) {
 
              String where = this.field_id + "=?";
 
              String[] wherevalue = { id };
 
              db.delete(table_name, where, wherevalue);
 
           }
 
           db.setTransactionSuccessful();
 
       } catch (Exception e) {
 
           Log.e(tag, "transaction:" + e);
 
       } finally {
 
           db.endTransaction();
           db.close();
       }
 
    }
 
    // 直接通过拼写的sql和传入的参数来更改数据库
 
    public void execSql(String sql, Object[] bindArgs) {
 
       SQLiteDatabase db = this.getWritableDatabase();
       try {
 
           db.execSQL(sql, bindArgs);
 
           // for example
 
           // db.execSQL(
 
           // "update todo_table set todo_text=? where id=?",
 
           // new Object[] { "周六去买个手机", 1});
       } catch (Exception e) {
           // TODO: handle exception
           Log.e(tag, "execSql:" + e);
       } finally {
           db.close();
       }
 
    }
 
    // 直接通过拼写的sql和传入的参数来查询数据库
 
    public Cursor rawQuery(String sql, String[] selectionArgs)
 
    {
       Cursor cursor = null;
       SQLiteDatabase db = this.getWritableDatabase();
       try {
 
           cursor = db.rawQuery(sql, selectionArgs);
           // for example
 
           // db.rawQuery(
 
           // "select * todo_table where id=?",
 
           // new Object[] {1});
 
       } catch (Exception e) {
           // TODO: handle exception
           Log.e(tag, "rawQuery:" + e);
       } finally {
           db.close();
       }
       return cursor;
 
    }
 
}