Android使用SQLite数据库SQLiteOpenHelper

判断表字段是否已存在：SELECT sql FROM sqlite_master WHERE name = 'table_name' AND (sql LIKE '%(column_name %' OR sql LIKE '%,column_name %');
判断表是否存在：SELECT * FROM sqlite_master WHERE type='table' AND tbl_name='table_name';
排除掉sqlite系统表：SELECT tbl_name FROM sqlite_master WHERE type='table' AND `name` NOT IN('android_metadata','sqlite_stat1','sqlite_stat2','nearexpired_settings','sqlite_sequence');
Utils，onUpgrade是在数据库版本号改变(+1)的时候会调用，一般里面放的是执行增加表和字段的sql语句。sqLiteDatabase.execSQL("alter table student add address varchar(200) null");

package com.jay.common;

import android.content.Context;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;

import androidx.annotation.Nullable;

/**
 * 参考：
 * https://blog.csdn.net/fenghuochengshan/article/details/129348726
 * https://blog.csdn.net/M_Nobody/article/details/126100662
 */
public class SQLiteUtils extends SQLiteOpenHelper {
    private static final String DB_NAME = "school.db";
    private static final int DB_VERSION = 1;
    private static SQLiteUtils mHelper = null;
    private static SQLiteDatabase mReadDatabase = null;
    private static SQLiteDatabase mWriteDatabase = null;

    //单例模式
    public static SQLiteUtils getInstance(@Nullable Context context) {
        if (mHelper == null) {
            synchronized (SQLiteUtils.class) {
                if (mHelper == null) {
                    mHelper = new SQLiteUtils(context);
                }
            }
        }
        return mHelper;
    }
    public static SQLiteUtils getInstance(@Nullable Context context, @Nullable String name, @Nullable SQLiteDatabase.CursorFactory factory, int version) {
        if (mHelper == null) {
            synchronized (SQLiteUtils.class) {
                if (mHelper == null) {
                    mHelper = new SQLiteUtils(context, name, factory, version);
                }
            }
        }
        return mHelper;
    }

    //构造方法，必须调用父类的构造方法，单例模式的构造方法需要是私有的。
    private SQLiteUtils(Context context) {
        super(context, DB_NAME, null, DB_VERSION);
    }
    private SQLiteUtils(@Nullable Context context, @Nullable String name, @Nullable SQLiteDatabase.CursorFactory factory, int version) {
        super(context, name, factory, version);
    }

    //打开写链接
    public SQLiteDatabase openWriteLink() {
        if (mWriteDatabase == null || !mWriteDatabase.isOpen()) {
            mWriteDatabase = mHelper.getWritableDatabase();
        }
        return mWriteDatabase;
    }

    //打开读链接
    public SQLiteDatabase openReadLink() {
        if (mReadDatabase == null || !mReadDatabase.isOpen()) {
            mReadDatabase = mHelper.getReadableDatabase();
        }
        return mReadDatabase;
    }

    //关闭链接
    public void closeLink() {
        if (mReadDatabase != null && mReadDatabase.isOpen()) {
            mReadDatabase.close();
            mReadDatabase = null;
        }
        if (mWriteDatabase != null && mWriteDatabase.isOpen()) {
            mWriteDatabase.close();
            mWriteDatabase = null;
        }
    }

    //数据库初始化时需要进行的一些操作，比如创建数据表
    @Override
    public void onCreate(SQLiteDatabase db) {
        String sql = "CREATE TABLE IF NOT EXISTS student (" +
                "id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL," +
                "name VARCHAR(50) NOT NULL," +
                "age INTEGER NOT NULL);";
        db.execSQL(sql);
    }

    @Override
    public void onUpgrade(SQLiteDatabase sqLiteDatabase, int i, int i1) {

    }
}

示例，要加上同步代码，多线程并发执行会报错。一个关闭了数据库，一个还在执行。

package com.jay.BL;

import android.content.ContentValues;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;

import com.jay.common.BaseForm;
import com.jay.common.SQLiteUtils;
import com.jay.common.SysApplication;
import com.jay.models.Student;

import java.math.BigDecimal;
import java.util.ArrayList;
import java.util.List;

/**
 * 原文链接：https://blog.csdn.net/M_Nobody/article/details/126100662
 */
public class StuBL {
    public static final String TABLE_NAME = "student";

    public Student getStuById(int id) {
        synchronized (this.getClass()) {
            //BaseForm.curActivity()
            SQLiteUtils instance = SQLiteUtils.getInstance(SysApplication.getContext());
            SQLiteDatabase mRDB = instance.openReadLink();
            // 执行记录查询动作，该语句返回结果集的游标
            Cursor cursor = mRDB.query(TABLE_NAME, null, "id=?", new String[]{String.valueOf(id)}, null, null, null);
            Student stu = null;
            // 循环取出游标指向的每条记录
            while (cursor.moveToNext()) {
                stu = new Student();
                stu.setId(cursor.getInt(0));
                stu.setName(cursor.getString(1));
                stu.setAge(cursor.getInt(2));

                String str3 = cursor.getString(3);// 会四舍五入
                String purchase_price = String.valueOf(cursor.getDouble(3));
                stu.setPurchase_price(new BigDecimal(purchase_price));
                break;
            }
            instance.closeLink();
            return stu;
        }
    }

    public int getMaxStuId() {
        synchronized (this.getClass()) {
            SQLiteUtils instance = SQLiteUtils.getInstance(BaseForm.curActivity());
            SQLiteDatabase mRDB = instance.openReadLink();
            // 执行记录查询动作，该语句返回结果集的游标
            Cursor cursor = mRDB.query(TABLE_NAME, null, "", null, null, null, "id desc");
            int id = 0;
            // 循环取出游标指向的每条记录
            while (cursor.moveToNext()) {
                id = cursor.getInt(0);
                break;
            }
            instance.closeLink();
            return id;
        }
    }

    public long addStu(Student stu) {
        synchronized (this.getClass()) {
            //获取写入database对象
            SQLiteUtils instance = SQLiteUtils.getInstance(BaseForm.curActivity());
            SQLiteDatabase mWDB = instance.openWriteLink();
            //获取写入参数对象
            ContentValues values = new ContentValues();
            //将要传入的参数以键值对的方式写入参数对象
//            values.put("id", stu.getId());
            values.put("name", stu.getName());
            values.put("age", stu.getAge());
            values.put("purchase_price", stu.getPurchase_price().toString());
//        writableDatabase.insert(<1>,<2>,<3>);
//        参数分别为：表名，第三个参数为空时的默认字段名，ContentValues对象。
            long num = mWDB.insert(TABLE_NAME, null, values);
            instance.closeLink();
            return num;
        }
    }

    public long deleteByName(String name) {
        synchronized (this.getClass()) {
            // 删除所有
//        mWDB.delete(TABLE_NAME,"1=1", null);
            SQLiteUtils instance = SQLiteUtils.getInstance(BaseForm.curActivity());
            SQLiteDatabase mWDB = instance.openWriteLink();
            int num = mWDB.delete(TABLE_NAME, "name=?", new String[]{name});
            instance.closeLink();
            return num;
        }
    }

    public long update(Student stu) {
        synchronized (this.getClass()) {
            SQLiteUtils instance = SQLiteUtils.getInstance(BaseForm.curActivity());
            SQLiteDatabase mWDB = instance.openWriteLink();
            ContentValues values = new ContentValues();
            values.put("name", stu.getName());
            values.put("age", stu.getAge());
            values.put("purchase_price", stu.getPurchase_price().toString());
            values.put("id", stu.getId());
            int num = mWDB.update(TABLE_NAME, values, "id=?", new String[]{String.valueOf(stu.getId())});
            instance.closeLink();
            return num;
        }
    }

    public List<Student> queryByName(String name) {
        synchronized (this.getClass()) {
            SQLiteUtils instance = SQLiteUtils.getInstance(BaseForm.curActivity());
            SQLiteDatabase mRDB = instance.openReadLink();
            List<Student> list = new ArrayList<>();
            // 执行记录查询动作，该语句返回结果集的游标
            Cursor cursor = mRDB.query(TABLE_NAME, null, "name=?", new String[]{name}, null, null, null);
            // 循环取出游标指向的每条记录
            while (cursor.moveToNext()) {
                Student stu = new Student();
                stu.setId(cursor.getInt(0));
                stu.setName(cursor.getString(1));
                stu.setAge(cursor.getInt(2));
                // SQLite没有布尔类型，用0表示false，用1表示true
                list.add(stu);
            }
            instance.closeLink();
            return list;
        }
    }
}