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

最新推荐文章于 2025-07-16 07:00:00 发布

cug_xy

最新推荐文章于 2025-07-16 07:00:00 发布

阅读量2.3k

点赞数

CC 4.0 BY-SA版权

分类专栏： Android 数据库学习文章标签： Android SQLite 触发器

本文链接：https://blog.youkuaiyun.com/Bear_861110453/article/details/80118563

学习同时被 3 个专栏收录

32 篇文章

订阅专栏

Android

2 篇文章

订阅专栏

数据库

2 篇文章

订阅专栏

本文介绍了如何在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);