20210123 Sqlit数据库

示例

public class MyDbHelper extends SQLiteOpenHelper {

    //数据库名字
    public static final String DB_NAME = "my.db";

    //数据库版本
    public static final int DB_VERSION = 1;

    public MyDbHelper(Context context) {
        super(context, DB_NAME, null, DB_VERSION);
    }

    /**
     * 在数据库首次创建的时候调用，创建表以及可以进行一些表数据的初始化
     *
     * @param db
     */
    @Override
    public void onCreate(SQLiteDatabase db) {
        //创建表
        //_id为主键并且自增长一般命名为_id
        String sql = "create table person(_id integer primary key autoincrement,name text)";
        db.execSQL(sql);

        //可以给表初始化一些数据
        //db.execSQL("insert into person(name) values('小米')");
    }

    /**
     * 数据库升级的时候回调该方法，在数据库版本号DB_VERSION升级的时候才会调用
     *
     * @param db
     * @param oldVersion
     * @param newVersion
     */
    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {

        //给表添加一个字段
        //db.execSQL("alter table person add age integer");
    }

    /**
     * 数据库打开的时候回调该方法
     *
     * @param db
     */
    @Override
    public void onOpen(SQLiteDatabase db) {
        super.onOpen(db);
        Log.e("xyh", "onOpen: ");
    }
}

数据库操作类，封装数据库的增删改查方法

public class DataBaseDao {

    private final MyDbHelper mDbHelper;


    public DataBaseDao(Context context) {
        //实例化数据库管理类
        mDbHelper = new MyDbHelper(context);
    }

    /**
     * 插入
     *
     * @param list
     */
    public void insert(List<PersonBean> list) {
        SQLiteDatabase db = mDbHelper.getWritableDatabase();
        if (db.isOpen()) {
            for (int i = 0; i < list.size(); i++) {
                db.execSQL("insert into person(name) values(" + "'" + list.get(i).getName() + "'" + ")");
            }
            db.close();
        }
    }

    /**
     * 更新
     *
     * @param id
     * @param personBean
     */
    public void update(int id, PersonBean personBean) {
        SQLiteDatabase db = mDbHelper.getWritableDatabase();
        if (db.isOpen()) {
            db.execSQL("update person set name=? where _id=?", new Object[]{personBean.getName(), id});
            db.close();
        }
    }

    /**
     * 根据id删除
     */
    public void delete(int id) {
        SQLiteDatabase db = mDbHelper.getWritableDatabase();
        if (db.isOpen()) {
            db.execSQL("delete from person where _id=?", new Object[]{id});
            db.close();
        }
    }

    /**
     * 删除所有
     */
    public void deleteAll() {
        SQLiteDatabase db = mDbHelper.getWritableDatabase();
        if (db.isOpen()) {
            db.execSQL("delete from person");
            //设置id从1开始（sqlite默认id从1开始），若没有这一句，id将会延续删除之前的id
            db.execSQL("update sqlite_sequence  set seq=0 where name='person'");
            db.close();
        }
    }


    /**
     * 查询所有
     */
    public List<PersonBean> queryAll() {
        List<PersonBean> list = new ArrayList<>();
        SQLiteDatabase db = mDbHelper.getReadableDatabase();
        if (db.isOpen()) {
            Cursor cursor = db.rawQuery("select * from person", null);
            while (cursor.moveToNext()) {
                //int id = cursor.getInt(0);
                int _id = cursor.getInt(cursor.getColumnIndex("_id"));
                String name = cursor.getString(cursor.getColumnIndex("name"));
                PersonBean personBean = new PersonBean(_id, name);
                list.add(personBean);
            }
            cursor.close();
            db.close();
        }
        return list;
    }

    /**
     * 根据id查询
     */
    public List<PersonBean> query(int id) {
        List<PersonBean> list = new ArrayList<>();
        SQLiteDatabase db = mDbHelper.getReadableDatabase();
        if (db.isOpen()) {
            Cursor cursor = db.rawQuery("select * from person where _id=?", new String[]{id + ""});
            while (cursor.moveToNext()) {
                //int id = cursor.getInt(0);
                int _id = cursor.getInt(cursor.getColumnIndex("_id"));
                String name = cursor.getString(cursor.getColumnIndex("name"));
                PersonBean dataBean = new PersonBean(_id, name);
                list.add(dataBean);
            }
            cursor.close();
            db.close();
        }
        return list;
    }

}



原文链接：https://blog.csdn.net/qq_36699930/article/details/78939698