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.models.Student;

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) {
        SQLiteUtils instance = SQLiteUtils.getInstance(BaseForm.curActivity());
        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));
            break;
        }
        instance.closeLink();
        return stu;
    }

    public int getMaxStuId() {
        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) {
        //获取写入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());
//        writableDatabase.insert(<1>,<2>,<3>);
//        参数分别为：表名，第三个参数为空时的默认字段名，ContentValues对象。
        long num = mWDB.insert(TABLE_NAME, null, values);
        instance.closeLink();
        return num;
    }

    public long deleteByName(String name) {
        // 删除所有
//        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) {
        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("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) {
        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;
    }
}