Android SQLite 数据库利用触发器限制行数

本文介绍了如何在Android应用中利用SQLite数据库的触发器功能,限制数据库表的行数。首先,讲述了Android中数据库的基本使用,包括SQLiteOpenHelper的继承与数据库版本管理。接着,对SQLite触发器进行了简要介绍,提到了触发器的创建语法。最后,讨论了如何通过SQL语句删除超出限制的行,以此来保持数据库表的行数不超过设定值。

摘要生成于 C知道 ,由 DeepSeek-R1 满血版支持, 前往体验 >

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);

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值