Android整合SQLite数据库进行基本的增删改查

热爱音乐的nobody

已于 2022-12-28 11:08:00 修改

阅读量1.1k

点赞数 2

文章标签： android sqlite 数据库

于 2022-12-13 10:35:29 首次发布

本文链接：https://blog.csdn.net/weixin_54029352/article/details/128033080

版权

简言

使用Android整合SQLite数据库进行数据存储，大致可以划分为三步：

①继承 SQLiteOpenHelper，创建数据库

②继承 ContentProvider 类，重写方法

③在清单文件AndroidManifest中声明 ContentProvider

继承 SQLiteOpenHelper类

/**
     * 数据库常量声明
     */
    private SQLiteDatabase db;
    private static final String DATABASE_NAME = "College";
    private static final String STUDENTS_TABLE_NAME = "students";
    private static final int DATABASE_VERSION = 1;
    private static final String CREATE_DB_TABLE =
            "CREATE TABLE " + STUDENTS_TABLE_NAME + "(" +
                    _ID + " INTEGER PRIMARY KEY AUTOINCREMENT," +
                    NAME + " TEXT NOT NULL," +
                    GRADE + " TEXT NOT NULL)";


    private static class DataBaseHelper extends SQLiteOpenHelper {


        public DataBaseHelper(Context context) {
            super(context, DATABASE_NAME, null, DATABASE_VERSION);
        }

        @Override   //创建数据库
        public void onCreate(SQLiteDatabase db) {
            db.execSQL(CREATE_DB_TABLE);
        }

        @Override   //数据库更新会被调用(用的比较少)
        public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
            db.execSQL("DROP TABLE IF EXISTS " + STUDENTS_TABLE_NAME);
        }
    }

实现 ContentProvider 类

public class StudentsProvider extends ContentProvider {
    private static final String TAG = "StudentsProvider";

    private static final String PROVIDER_NAME = "com.example.xxx.StudentsProvider";
    private static final String URL = "content://" + PROVIDER_NAME + "/students";
    //uri:统一资源标识符,用来指定查询某个应用程序下的某一张表, parse():将URL字符串转换成Uri对象
    public static final Uri CONTENT_URI = Uri.parse(URL);
    
    /** 表格字段 */
    public static final String _ID = "_id";
    public static final String NAME = "name";
    public static final String GRADE = "grade";

    private static HashMap<String, String> STUDENTS_PROJECTION_MAP;

    //匹配码,自定义
    private static final int STUDENTS = 1;
    private static final int STUDENTS_ID = 2;

    private static final UriMatcher uriMatcher;

    static {
        uriMatcher = new UriMatcher(UriMatcher.NO_MATCH);
        //添加需要匹配的Uri,如果匹配到就返回匹配码(例如:match()方法匹配到PROVIDER_NAME/students,则返回STUDENTS匹配码)  #是通配符
        uriMatcher.addURI(PROVIDER_NAME, "students", STUDENTS);
        uriMatcher.addURI(PROVIDER_NAME, "students/#", STUDENTS_ID);
    }

    

    @Override
    public boolean onCreate() {
        Context context = getContext();
        DataBaseHelper dbHelper = new DataBaseHelper(context);
        //getWritableDatabase:返会一个读写SQLiteDatabase数据库,磁盘满了之后会报错,getReadableDatabase()不会
        db = dbHelper.getWritableDatabase();
        return (db == null) ? false : true;
    }


    /**
     * 查询语句
     *
     * @param uri           查询哪个应用哪张表
     * @param projection    指定查询的列名
     * @param selection     指定where的约束条件
     * @param selectionArgs 为where中的占位符提供具体的值
     * @param sortOrder     指定查询结果的排序方式
     * @return 查询的结果封装到 Cursor对象返回
     */
    @Nullable
    @Override
    public Cursor query(@NonNull Uri uri, @Nullable String[] projection, @Nullable String selection, @Nullable String[] selectionArgs, @Nullable String sortOrder) {
        SQLiteQueryBuilder qb = new SQLiteQueryBuilder();
        qb.setTables(STUDENTS_TABLE_NAME);

        switch (uriMatcher.match(uri)) {
            case STUDENTS:
                qb.setProjectionMap(STUDENTS_PROJECTION_MAP);
                break;
            case STUDENTS_ID:   //根据ID查询
                qb.appendWhere(_ID + "=" + uri.getPathSegments().get(1));
                break;
            default:
        }

        if (sortOrder == null || sortOrder == "") {
            sortOrder = NAME;   //默认根据name排序
        }

        Cursor c = qb.query(db, projection, selection, selectionArgs, null, null, sortOrder);
        //注册一个监听器,监视 URI 的变化
        c.setNotificationUri(getContext().getContentResolver(), uri);
        Log.d(TAG, " query: 执行了");
        return c;
    }

    @Nullable
    @Override
    public String getType(@NonNull Uri uri) {
        switch (uriMatcher.match(uri)) {
            /**
             * Get all student records
             */
            case STUDENTS:
                return "vnd.android.cursor.dir/vnd.example.students";
            /**
             * Get a particular student
             */
            case STUDENTS_ID:
                return "vnd.android.cursor.item/vnd.example.students";
            default:
                throw new IllegalArgumentException("Unsupported URI: " + uri);
        }
    }


    /**
     * 插入语句
     *
     * @param uri    统一资源标识符
     * @param values 插入的属性值
     * @return
     */
    @Nullable
    @Override
    public Uri insert(@NonNull Uri uri, @Nullable ContentValues values) {
        long rowID = db.insert(STUDENTS_TABLE_NAME, "", values);
        Log.d(TAG, " insert: 执行了");
        /**
         * 判读记录是否添加成功
         */
        if (rowID > 0) {
            //withAppendedId():负责把 CONTENT_URI 和 rowID拼接成一个新的uri
            Uri _uri = ContentUris.withAppendedId(CONTENT_URI, rowID);
            //如果 uri发生变化, null:表示则通知所有人
            getContext().getContentResolver().notifyChange(_uri, null);
            return _uri;
        }
        throw new SQLException("Failed to add a record into " + uri);
    }


    /**
     * 删除语句
     *
     * @param uri           统一资源标识符(说百了就是查询table表的具体位置)
     * @param selection     字段
     * @param selectionArgs 字段值
     * @return 成功条数
     */
    @Override
    public int delete(@NonNull Uri uri, @Nullable String selection, @Nullable String[] selectionArgs) {
        int count = 0;
        Log.d(TAG, " delete: 执行了");
        switch (uriMatcher.match(uri)) {
            case STUDENTS:
                count = db.delete(STUDENTS_TABLE_NAME, selection, selectionArgs);
                break;
            case STUDENTS_ID:
                String id = uri.getPathSegments().get(1);
                db.delete(STUDENTS_TABLE_NAME, _ID + "=" + id +
                        (!TextUtils.isEmpty(selection) ? " AND (" + selection + ')' : ""), selectionArgs);
            default:
                throw new IllegalArgumentException("Unknown URI " + uri);
        }
        getContext().getContentResolver().notifyChange(uri, null);
        return count;
    }


    /**
     * 更新语句
     *
     * @param uri           统一资源标识符
     * @param values        要更新的字段和值
     * @param selection     where字句
     * @param selectionArgs where字句的值
     * @return
     */
    @Override
    public int update(@NonNull Uri uri, @Nullable ContentValues values, @Nullable String selection, @Nullable String[] selectionArgs) {
        int count = 0;
        Log.d(TAG, " update: 执行了");
        switch (uriMatcher.match(uri)) {
            case STUDENTS:
                count = db.update(STUDENTS_TABLE_NAME, values, selection, selectionArgs);
                break;
            case STUDENTS_ID:
                count = db.update(STUDENTS_TABLE_NAME, values,
                        _ID + "=" + uri.getPathSegments().get(1) +
                                (!TextUtils.isEmpty(selection) ? "AND (" + selection + ")" : ""), selectionArgs);
                break;
            default:
                throw new IllegalArgumentException("Unknown URI " + uri);
        }
        getContext().getContentResolver().notifyChange(uri, null);
        return count;
    }

}

在清单文件AndroidManifest中声明 ContentProvider

        <provider
            android:name=".StudentsProvider"
            android:authorities="com.example.xxx.StudentsProvider"/>

调用测试

    public void onClickAddName(View view) {
        ContentValues values = new ContentValues();
        values.put(StudentsProvider.NAME, ((EditText) findViewById(R.id.editText2)).getText().toString());
        values.put(StudentsProvider.GRADE, ((EditText) findViewById(R.id.editText3)).getText().toString());

        Uri uri = getContentResolver().insert(StudentsProvider.CONTENT_URI, values);
        Toast.makeText(getBaseContext(),
                uri.toString(), Toast.LENGTH_LONG).show();
        System.out.println(uri);
    }

后续可以通过：getContentResolver() + 数据库操作来实现调用SQLite进行增删改查

完整代码

package com.example.xxx;

import android.content.ContentProvider;
import android.content.ContentUris;
import android.content.ContentValues;
import android.content.Context;
import android.content.UriMatcher;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.database.sqlite.SQLiteQueryBuilder;
import android.net.Uri;
import android.text.TextUtils;
import android.util.Log;
import android.widget.Toast;

import androidx.annotation.NonNull;
import androidx.annotation.Nullable;

import java.util.HashMap;


/**
 * 内容提供者（提供数据）
 * 该类已经在清单中进行了注册,项目启动的时候,清单中声明的类都会进行自动装载
 */
public class StudentsProvider extends ContentProvider {
    private static final String TAG = "StudentsProvider";

    private static final String PROVIDER_NAME = "com.example.xxx.StudentsProvider";
    private static final String URL = "content://" + PROVIDER_NAME + "/students";
    //uri:统一资源标识符,用来指定查询某个应用程序下的某一张表, parse():将URL字符串转换成Uri对象
    public static final Uri CONTENT_URI = Uri.parse(URL);

    public static final String _ID = "_id";
    public static final String NAME = "name";
    public static final String GRADE = "grade";

    private static HashMap<String, String> STUDENTS_PROJECTION_MAP;

    //匹配码,自定义
    private static final int STUDENTS = 1;
    private static final int STUDENTS_ID = 2;

    private static final UriMatcher uriMatcher;

    static {
        uriMatcher = new UriMatcher(UriMatcher.NO_MATCH);
        //添加需要匹配的Uri,如果匹配到就返回匹配码(例如:match()方法匹配到PROVIDER_NAME/students,则返回STUDENTS匹配码)  #是通配符
        uriMatcher.addURI(PROVIDER_NAME, "students", STUDENTS);
        uriMatcher.addURI(PROVIDER_NAME, "students/#", STUDENTS_ID);
    }

    /**
     * 数据库常量声明
     */
    private SQLiteDatabase db;
    private static final String DATABASE_NAME = "College";
    private static final String STUDENTS_TABLE_NAME = "students";
    private static final int DATABASE_VERSION = 1;
    private static final String CREATE_DB_TABLE =
            "CREATE TABLE " + STUDENTS_TABLE_NAME + "(" +
                    _ID + " INTEGER PRIMARY KEY AUTOINCREMENT," +
                    NAME + " TEXT NOT NULL," +
                    GRADE + " TEXT NOT NULL)";


    private static class DataBaseHelper extends SQLiteOpenHelper {


        public DataBaseHelper(Context context) {
            super(context, DATABASE_NAME, null, DATABASE_VERSION);
        }

        @Override   //创建数据库
        public void onCreate(SQLiteDatabase db) {
            db.execSQL(CREATE_DB_TABLE);
        }

        @Override   //数据库更新会被调用(用的比较少)
        public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
            db.execSQL("DROP TABLE IF EXISTS " + STUDENTS_TABLE_NAME);
        }
    }


    @Override
    public boolean onCreate() {
        Context context = getContext();
        DataBaseHelper dbHelper = new DataBaseHelper(context);
        //getWritableDatabase:返会一个读写SQLiteDatabase数据库,磁盘满了之后会报错,getReadableDatabase()不会
        db = dbHelper.getWritableDatabase();
        return (db == null) ? false : true;
    }


    /**
     * 查询语句
     *
     * @param uri           查询哪个应用哪张表
     * @param projection    指定查询的列名
     * @param selection     指定where的约束条件
     * @param selectionArgs 为where中的占位符提供具体的值
     * @param sortOrder     指定查询结果的排序方式
     * @return 查询的结果封装到 Cursor对象返回
     */
    @Nullable
    @Override
    public Cursor query(@NonNull Uri uri, @Nullable String[] projection, @Nullable String selection, @Nullable String[] selectionArgs, @Nullable String sortOrder) {
        SQLiteQueryBuilder qb = new SQLiteQueryBuilder();
        qb.setTables(STUDENTS_TABLE_NAME);

        switch (uriMatcher.match(uri)) {
            case STUDENTS:
                qb.setProjectionMap(STUDENTS_PROJECTION_MAP);
                break;
            case STUDENTS_ID:   //根据ID查询
                qb.appendWhere(_ID + "=" + uri.getPathSegments().get(1));
                break;
            default:
        }

        if (sortOrder == null || sortOrder == "") {
            sortOrder = NAME;   //默认根据name排序
        }

        Cursor c = qb.query(db, projection, selection, selectionArgs, null, null, sortOrder);
        //注册一个监听器,监视 URI 的变化
        c.setNotificationUri(getContext().getContentResolver(), uri);
        Log.d(TAG, " query: 执行了");
        return c;
    }

    @Nullable
    @Override
    public String getType(@NonNull Uri uri) {
        switch (uriMatcher.match(uri)) {
            /**
             * Get all student records
             */
            case STUDENTS:
                return "vnd.android.cursor.dir/vnd.example.students";
            /**
             * Get a particular student
             */
            case STUDENTS_ID:
                return "vnd.android.cursor.item/vnd.example.students";
            default:
                throw new IllegalArgumentException("Unsupported URI: " + uri);
        }
    }


    /**
     * 插入语句
     *
     * @param uri    统一资源标识符
     * @param values 插入的属性值
     * @return
     */
    @Nullable
    @Override
    public Uri insert(@NonNull Uri uri, @Nullable ContentValues values) {
        long rowID = db.insert(STUDENTS_TABLE_NAME, "", values);
        Log.d(TAG, " insert: 执行了");
        /**
         * 判读记录是否添加成功
         */
        if (rowID > 0) {
            //withAppendedId():负责把 CONTENT_URI 和 rowID拼接成一个新的uri
            Uri _uri = ContentUris.withAppendedId(CONTENT_URI, rowID);
            //如果 uri发生变化, null:表示则通知所有人
            getContext().getContentResolver().notifyChange(_uri, null);
            return _uri;
        }
        throw new SQLException("Failed to add a record into " + uri);
    }


    /**
     * 删除语句
     *
     * @param uri           统一资源标识符(说百了就是查询table表的具体位置)
     * @param selection     字段
     * @param selectionArgs 字段值
     * @return 成功条数
     */
    @Override
    public int delete(@NonNull Uri uri, @Nullable String selection, @Nullable String[] selectionArgs) {
        int count = 0;
        Log.d(TAG, " delete: 执行了");
        switch (uriMatcher.match(uri)) {
            case STUDENTS:
                count = db.delete(STUDENTS_TABLE_NAME, selection, selectionArgs);
                break;
            case STUDENTS_ID:
                String id = uri.getPathSegments().get(1);
                db.delete(STUDENTS_TABLE_NAME, _ID + "=" + id +
                        (!TextUtils.isEmpty(selection) ? " AND (" + selection + ')' : ""), selectionArgs);
            default:
                throw new IllegalArgumentException("Unknown URI " + uri);
        }
        getContext().getContentResolver().notifyChange(uri, null);
        return count;
    }


    /**
     * 更新语句
     *
     * @param uri           统一资源标识符
     * @param values        要更新的字段和值
     * @param selection     where字句
     * @param selectionArgs where字句的值
     * @return
     */
    @Override
    public int update(@NonNull Uri uri, @Nullable ContentValues values, @Nullable String selection, @Nullable String[] selectionArgs) {
        int count = 0;
        Log.d(TAG, " update: 执行了");
        switch (uriMatcher.match(uri)) {
            case STUDENTS:
                count = db.update(STUDENTS_TABLE_NAME, values, selection, selectionArgs);
                break;
            case STUDENTS_ID:
                count = db.update(STUDENTS_TABLE_NAME, values,
                        _ID + "=" + uri.getPathSegments().get(1) +
                                (!TextUtils.isEmpty(selection) ? "AND (" + selection + ")" : ""), selectionArgs);
                break;
            default:
                throw new IllegalArgumentException("Unknown URI " + uri);
        }
        getContext().getContentResolver().notifyChange(uri, null);
        return count;
    }

}

只需要把完整代码拷贝到你的项目中,然后新建一个测试类，通过 getContentResolver() + 数据库操作，就可以对SQLIte数据库进行增删改查了