1.Android 数据库使用
2.SQLite 数据库触发器 介绍
3. SQL 语句删除超过限制行数
本文主要从以上三点解释一下 Android 中如何限制SQLite 数据库作为缓存时,数据库表的行数。
1.Android 数据库使用
继承 SQLiteOpenHelper 抽象类
通过继承抽象类分别实现 构造函数以及 两个虚函数,当新建 SQLiteOpenHelper 对象时,根据传入的 version 参数,如果是第一次创建对象,则会自动回调 onCreate 函数,用于创建数据库,若第二次创建 SQLiteOpenHelper 对象,且 version 参数大于之前对象,则会自动回调 onUpgrade 函数,用于更新数据库结构。public DataBaseOpenHelper(Context context, String name, int version) { super(context, name, null, version); } @Override public void onCreate(SQLiteDatabase db){ } @Override public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) { }
下面我给出我自己的 Helper 类代码:
import android.content.Context; import android.database.sqlite.SQLiteDatabase; import android.database.sqlite.SQLiteOpenHelper; /** * Created by cugxy on 2018/4/27. * file: DataBaseOpenHelper .java */ public class DataBaseOpenHelper extends SQLiteOpenHelper { private static final String TAG = "DataBaseOpenHelper"; public static final String AVATAR_TABLE_NAME = "avatar"; public static final String COL_ID_NAME = "id"; public static final String COL_IMG_NAME = "img_data"; public static final String COL_DATETIME_NAME = "time"; private static final String TRIGGER_NAME = "trigger_delete_top"; private static final String COUNT = "500"; public DataBaseOpenHelper(Context context, String name, int version) { super(context, name, null, version); } @Override public void onCreate(SQLiteDatabase db) { //建表 // CREATE TABLE avatar ( id INTEGER PRIMARY KEY, img_data BLOB, time DATETIME); String strCreateSQL = "CREATE TABLE "; strCreateSQL = strCreateSQL + AVATAR_TABLE_NAME; strCreateSQL = strCreateSQL + " ( " + COL_ID_NAME + " INTEGER PRIMARY KEY, "; strCreateSQL = strCreateSQL + COL_IMG_NAME + " BLOB, "; strCreateSQL = strCreateSQL + COL_DATETIME_NAME + " DATETIME);"; // 触发器 //CREATE TRIGGER trigger_delete_top AFTER insert ON avatar //BEGIN // delete from avatar where (select count(id) from avatar) > 2 and id in (select id from avatar order by time desc limit (select count(id) from avatar) offset 2); //END; String strTriggerSQL = "CREATE TRIGGER "; strTriggerSQL = strTriggerSQL + TRIGGER_NAME; strTriggerSQL = strTriggerSQL + " AFTER INSERT ON "; strTriggerSQL = strTriggerSQL + AVATAR_TABLE_NAME; strTriggerSQL = strTriggerSQL + " BEGIN delete from "; strTriggerSQL = strTriggerSQL + AVATAR_TABLE_NAME; strTriggerSQL = strTriggerSQL + " WHERE ( SELECT COUNT( "; strTriggerSQL = strTriggerSQL + COL_ID_NAME; strTriggerSQL = strTriggerSQL + " ) FROM "; strTriggerSQL = strTriggerSQL + AVATAR_TABLE_NAME; strTriggerSQL = strTriggerSQL + " ) > "; strTriggerSQL = strTriggerSQL + COUNT; strTriggerSQL = strTriggerSQL + " AND "; strTriggerSQL = strTriggerSQL + COL_ID_NAME; strTriggerSQL = strTriggerSQL + " IN ( SELECT "; strTriggerSQL = strTriggerSQL + COL_ID_NAME; strTriggerSQL = strTriggerSQL + " FROM "; strTriggerSQL = strTriggerSQL + AVATAR_TABLE_NAME; strTriggerSQL = strTriggerSQL + " ORDER BY "; strTriggerSQL = strTriggerSQL + COL_DATETIME_NAME; strTriggerSQL = strTriggerSQL + " DESC LIMIT (SELECT COUNT( "; strTriggerSQL = strTriggerSQL + COL_ID_NAME; strTriggerSQL = strTriggerSQL + " ) FROM "; strTriggerSQL = strTriggerSQL + AVATAR_TABLE_NAME; strTriggerSQL = strTriggerSQL + " ) OFFSET "; strTriggerSQL = strTriggerSQL + COUNT; strTriggerSQL = strTriggerSQL + " ); END;"; db.beginTransaction(); try{ db.execSQL(strCreateSQL); db.execSQL(strTriggerSQL); db.setTransactionSuccessful(); }catch(Exception e){ e.printStackTrace(); } finally { //结束事务 db.endTransaction(); } } @Override public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) { } }
编写 DataBaseUtil 类,用于封装数据库操作,总不能在 avtivity 中去拼写 SQL 语句吧,那样代码太丑了,直接贴出代码
import android.content.Context; import android.database.Cursor; import android.database.sqlite.SQLiteDatabase; import android.graphics.Bitmap; import android.graphics.BitmapFactory; import java.io.ByteArrayOutputStream; /** * Created by cugxy on 2018/4/27. * */ public class DataBaseUtil { private static final String TAG = "DataBaseUtil"; public static final String DATABASE_NAME = "data.db"; public static final String AVATAR_TABLE_NAEM = "avatar"; public static final String COL_ID_NAME = "id"; public static final String COL_IMG_NAME = "img_data"; public static final String COL_DATETIME_NAME = "img_data"; private DataBaseOpenHelper mDBHelper; public DataBaseUtil(Context context){ mDBHelper = new DataBaseOpenHelper(context, DATABASE_NAME, 1); } public void add(int id, Bitmap bitmap){ SQLiteDatabase db = mDBHelper.getWritableDatabase(); String strSQL = "INSERT INTO " + AVATAR_TABLE_NAEM + " ( " + COL_ID_NAME + ", " + COL_IMG_NAME + ", "+ COL_DATETIME_NAME +" ) VALUES(?, ?, ?)"; ByteArrayOutputStream baos = new ByteArrayOutputStream(); bitmap.compress(Bitmap.CompressFormat.PNG, 100, baos); byte[] bytes = baos.toByteArray(); DateTimeUtil now = new DateTimeUtil(); db.execSQL(strSQL, new Object[]{id, bytes, now.toString()}); db.close(); } public Bitmap find(int id){ SQLiteDatabase db = mDBHelper.getWritableDatabase(); Bitmap bitmap = null; String strSQL = "SELECT * FROM " + AVATAR_TABLE_NAEM +" WHERE " + COL_ID_NAME + " = ? "; Cursor cursor = db.rawQuery(strSQL, new String[]{String.valueOf(id)}); if(cursor.moveToFirst()){ // 本地找到图片 直接赋值 byte[] bytes = cursor.getBlob(cursor.getColumnIndex(DataBaseUtil.COL_IMG_NAME)); bitmap = BitmapFactory.decodeByteArray(bytes, 0, bytes.length); } return bitmap; } public void remove(int id){ SQLiteDatabase db = mDBHelper.getWritableDatabase(); String strSQL = "DELETE FROM " + AVATAR_TABLE_NAEM + " WHERE " + COL_ID_NAME + " = ? "; db.execSQL(strSQL, new Object[]{id}); } public void clear(){ SQLiteDatabase db = mDBHelper.getWritableDatabase(); String strSQL = "DELETE FROM " + AVATAR_TABLE_NAEM; db.execSQL(strSQL); } }
2.SQLite 触发器介绍
SQLite 触发器 创建语法
CREATE TRIGGER trigger_name [BEFORE|AFTER] event_name
ON table_name
BEGIN
-- Trigger logic goes here....
END;
trigger_name 触发器唯一标识,
event_name 可选 update insert delete
table_name 表名称
先写这些 后面我会在 sqlite 中实验,并详细补充有关触发器与所绑定 event 之间的执行是否为事务(在 MySQL 的大部分版本中都是,意思是不管是 BEFORE|AFTER ,当触发器或SQL语句执行失败时,触发器与SQL都会回滚!!!)(如果有大神能够通过源码给我解释一下的话 感激不尽!!!)
3. SQL 语句删除超过限制行数
直接给出SQL 语句吧!因为上面 onCreate 函数中以及给出来了!这个是蛮长的,导致我发现一个问题
(我用 Android Studio 去拼写这条语句的时候,(当然包括前面的 create table 和 create trigger,更长了! 嗯!····) Android Studio 会变得非常卡顿,基本没法使用,所以我把他们拆成了两条语句去执行)
delete from avatar where (select count(id) from avatar) > 2 and id in (select id from avatar order by time desc limit (select count(id) from avatar) offset 2);