Android——SQLiteOpenHelper

使用步骤：

1. 新建一个继承自SQLiteOpenHelper的数据库操作类，提示重写onCreate和OnUpgraed两个方法。其中，onCreate方法只在第一次打开数据库时执行，在此可进行 表结构创建的操作；onUpgrade方法在数据库版本升高时执行，因此可以在onUpgraed函数内部根据新旧版本号进行表结构变更处理
2. 封装保证数据库安全的必要方法，包括获取单例对象、打开数据库连接、关闭数据库连接
3. 获取单例对象：确保App运行时数据库只被打开一次，避免重复打开引起错误
4. 打开数据库连接：SQLite有锁机制，即读锁和写锁的处理；故而数据库连接也分两种，读连接可调用getReadableDatabase，写连接可调用getWritableDatabase
5. 关闭数据库连接：数据库操作完毕后，应当调用SQLiteDatabase对象的close方法关闭连接
6. 提供对表记录进行增加、删除、修改、查询的操作方法
7. 可被SQLite直接使用的数据结构是ContentValues类，类似于映射Map，提供put和get方法来 存取键值对。
8. 对于查询操作来说，使用的是另一个游标类Cursor。调用SQLiteDatabase的query和rawQuery方法时，返回的都是Cursor对象，因此获取查询结果要根据游标的指示一条一条遍历结果集合。

Cursor的常用方法可分为3类：

1. 游标控制类方法，用于指定游标的状态
2. close：关闭游标
3. isClosed：判断游标是否关闭
4. isFirst：判断游标是否在开头
5. isLast：判断游标是否在末尾
6. 游标移动类方法，把游标移动到指定位置
7. moveToFirst：移动游标到开头
8. moveToLast：移动游标到末尾
9. moveToNext：移动游标到下一条记录
10. moveToPrevious：移动游标到上一条记录
11. move：往后移动游标若干条记录
12. moveToPosition：移动游标到指定位置的记录
13. 获取记录类方法，可获取记录的数量、类型以及取值
14. getCount：获取结果记录的数量
15. getInt：获取指定字段的整型值
16. getFloat
17. getString
18. getType

public class MyDatabaseHelper extends SQLiteOpenHelper {

    private static final String TAG = "MyDatabaseHelper";
    private static final String DB_NAME = "myDB.db";
    private static final int DB_VERSION = 1;
    private static MyDatabaseHelper mHelper = null;
    private SQLiteDatabase mDB = null;
    private static final String TABLE_NAME = "my_info";

    private MyDatabaseHelper(Context context) {
        super(context, DB_NAME, null, DB_VERSION);
    }

    private MyDatabaseHelper(Context context, int version) {
        super(context, DB_NAME, null, version);
    }

    public static MyDatabaseHelper getInstance(Context context, int version) {
        if (version > 0 && mHelper == null) {
            mHelper = new MyDatabaseHelper(context, version);
        } else if (mHelper == null) {
            mHelper = new MyDatabaseHelper(context);
        }
        return mHelper;
    }

    public SQLiteDatabase openReadLink() {
        if (mDB == null || mDB.isOpen() != true) {
            mDB = mHelper.getReadableDatabase();
        }
        return mDB;
    }

    public SQLiteDatabase openWriteLink() {
        if (mDB == null || mDB.isOpen() != true) {
            mDB = mHelper.getWritableDatabase();
        }
        return mDB;
    }

    public void closeLink() {
        if (mDB != null && mDB.isOpen() == true) {
            mDB.close();
            mDB = null;
        }
    }

    public String getDBName() {
        if (mHelper != null) {
            return mHelper.getDatabaseName();
        } else {
            return DB_NAME;
        }
    }

    @Override
    public void onCreate(SQLiteDatabase db) {

        Log.d(TAG, "onCreate");

        String drop_sql = "DROP TABLE IF EXISTS " + TABLE_NAME + ";";
        Log.d(TAG, "drop_sql:" + drop_sql);

        db.execSQL(drop_sql);

        String create_sql = "CREATE TABLE IF NOT EXISTS " + TABLE_NAME + " ("
                + "_id INTEGER PRIMARY KEY  AUTOINCREMENT NOT NULL,"
                + "name VARCHAR NOT NULL," + "age INTEGER NOT NULL,"
                + "height LONG NOT NULL," + "weight FLOAT NOT NULL,"
                + "married INTEGER NOT NULL," + "update_time VARCHAR NOT NULL"

                + ",phone VARCHAR" + ",password VARCHAR"
                + ");";
        Log.d(TAG, "create_sql:" + create_sql);

        db.execSQL(create_sql);

    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        Log.d(TAG, "onUpgrade oldVersion="+oldVersion+", newVersion="+newVersion);
        if (newVersion > 1) {

            String alter_sql = "ALTER TABLE " + TABLE_NAME + " ADD COLUMN " + "phone VARCHAR;";
            Log.d(TAG, "alter_sql:" + alter_sql);
            db.execSQL(alter_sql);
            alter_sql = "ALTER TABLE " + TABLE_NAME + " ADD COLUMN " + "password VARCHAR;";
            Log.d(TAG, "alter_sql:" + alter_sql);
            db.execSQL(alter_sql);
        }
    }
    public int delete(String condition) {
        int count = mDB.delete(TABLE_NAME, condition, null);
        return count;
    }

    public int deleteAll() {
        int count = mDB.delete(TABLE_NAME, "1=1", null);
        return count;
    }

    public long insert(UserInfo info) {
        ArrayList<UserInfo> infoArray = new ArrayList<UserInfo>();
        infoArray.add(info);
        return insert(infoArray);
    }

    public long insert(ArrayList<UserInfo> infoArray) {
        long result = -1;
        for (int i = 0; i < infoArray.size(); i++) {
            UserInfo info = infoArray.get(i);
            ArrayList<UserInfo> tempArray = new ArrayList<UserInfo>();

            if (info.name!=null && info.name.length()>0) {
                String condition = String.format("name='%s'", info.name);
                tempArray = query(condition);
                if (tempArray.size() > 0) {
                    update(info, condition);
                    result = tempArray.get(0).rowid;
                    continue;
                }
            }

            if (info.phone!=null && info.phone.length()>0) {
                String condition = String.format("phone='%s'", info.phone);
                tempArray = query(condition);
                if (tempArray.size() > 0) {
                    update(info, condition);
                    result = tempArray.get(0).rowid;
                    continue;
                }
            }

            ContentValues cv = new ContentValues();
            cv.put("name", info.name);
            cv.put("age", info.age);
            cv.put("height", info.height);
            cv.put("weight", info.weight);
            cv.put("married", info.married);
            cv.put("update_time", info.update_time);
            cv.put("phone", info.phone);
            cv.put("password", info.password);
            result = mDB.insert(TABLE_NAME, "", cv);

            if (result == -1) {
                return result;
            }
        }
        return result;
    }

    public int update(UserInfo info, String condition) {
        ContentValues cv = new ContentValues();
        cv.put("name", info.name);
        cv.put("age", info.age);
        cv.put("height", info.height);
        cv.put("weight", info.weight);
        cv.put("married", info.married);
        cv.put("update_time", info.update_time);
        cv.put("phone", info.phone);
        cv.put("password", info.password);
        int count = mDB.update(TABLE_NAME, cv, condition, null);
        return count;
    }

    public int update(UserInfo info) {
        return update(info, "rowid="+info.rowid);
    }

    public ArrayList<UserInfo> query(String condition) {
        String sql = String.format("select rowid,_id,name,age,height,weight,married,update_time," +
                "phone,password from %s where %s;", TABLE_NAME, condition);
        Log.d(TAG, "query sql: "+sql);
        ArrayList<UserInfo> infoArray = new ArrayList<UserInfo>();

        Cursor cursor = mDB.rawQuery(sql, null);
        if (cursor.moveToFirst()) {
            for (;; cursor.moveToNext()) {
                UserInfo info = new UserInfo();
                info.rowid = cursor.getLong(0);
                info.xuhao = cursor.getInt(1);
                info.name = cursor.getString(2);
                info.age = cursor.getInt(3);
                info.height = cursor.getLong(4);
                info.weight = cursor.getFloat(5);

                info.married = (cursor.getInt(6)==0)?false:true;
                info.update_time = cursor.getString(7);
                info.phone = cursor.getString(8);
                info.password = cursor.getString(9);
                infoArray.add(info);
                if (cursor.isLast() == true) {
                    break;
                }
            }
        }
        cursor.close();
        return infoArray;
    }

}

public class UserInfo {
    public long rowid;
    public int xuhao;
    public String name;
    public int age;
    public long height;
    public float weight;
    public boolean married;
    public String update_time;
    public String phone;
    public String password;

    public UserInfo() {
        rowid = 0l;
        xuhao = 0;
        name = "";
        age = 0;
        height = 0l;
        weight = 0.0f;
        married = false;
        update_time = "";
        phone = "";
        password2 = "";
    }
}

Original: https://blog.csdn.net/weixin_45688141/article/details/126353414
Author: 七七r
Title: Android——SQLiteOpenHelper