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; } }