android SQLite 使用

参考文档： 6.3.1 数据存储与访问之——初见SQLite数据库 | 菜鸟教程

自定义 SQLiteOpenHelper 实现对数据库增删改查：

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteException;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;
import android.widget.Toast;

import com.example.esp8266.activity.bean.Product;

import java.util.ArrayList;

/**
 * @Author lgd
 * @Date 2024/5/6 9:59
 */
public class MyOpenHelper extends SQLiteOpenHelper {

    public Context context;
    //上面的建表建错了，要有下面的这建表，必须主键为_id,_id,_id重要的事情说三遍。这里设置主键_id自增
    private final static String SQL_PERSON_id="create table product" +
            "(_id integer primary key autoincrement,productName text,productPrice text,productWeight text,productNumber integer,productTotal text)";
    //通过调用父类的构造方法完成数据库的创建
    public MyOpenHelper(Context context){
        super(context,"Product.db",null,1);
    }
    @Override
    public void onCreate(SQLiteDatabase sqLiteDatabase) {
        //在本方法中完成数据库对象的创建
        sqLiteDatabase.execSQL(SQL_PERSON_id);
    }

    @Override
    public void onUpgrade(SQLiteDatabase sqLiteDatabase, int i, int i1) {
        //在本方法中进行数据库的升级操作
    }
    //   添加 数据
    public void add(Product product){
        SQLiteDatabase db = this.getWritableDatabase();
        ContentValues values = new ContentValues();
        values.put("productName",product.getProductName());
        values.put("productPrice",product.getProductPrice());
        values.put("productNumber",product.getProductNumber());
        values.put("productTotal",product.getProductTotal());
        db.insert("product",null,values);
        db.close();
    }
//    ArrayList<Product> productList = null;
    //  读取 所有 数据
    public ArrayList<Product> readAllData() {
        ArrayList<Product> productList = new ArrayList<>();
        SQLiteDatabase db = null;
        Cursor cursor = null;
        try {
            db = this.getReadableDatabase();
            cursor = db.query("product", null, null, null, null, null, null);
            if (cursor.moveToFirst()) {
                do {
                    Integer id = cursor.getInt(cursor.getColumnIndex("_id"));
                    String productName = cursor.getString(cursor.getColumnIndex("productName"));
                    String productPrice = cursor.getString(cursor.getColumnIndex("productPrice"));
                    String productWeight = cursor.getString(cursor.getColumnIndex("productWeight"));
                    Integer productNumber = cursor.getInt(cursor.getColumnIndex("productNumber"));
                    String productTotal = cursor.getString(cursor.getColumnIndex("productTotal"));

                    productList.add(new Product(id, productName, productPrice, productWeight, productNumber, productTotal));
                    Log.d("Database", "----productName------" + productName + ", Price: " + productPrice);
                } while (cursor.moveToNext());
            }
        } catch (SQLiteException e) {
            // 处理SQLite相关的异常
            Log.e("Database", "Error reading data from database", e);
        } finally {
            if (cursor != null) cursor.close();
            if (db != null && db.isOpen()) db.close();
        }
        return productList;
    }
    //  更新 数据
    public void updateProduct(Product product){
        SQLiteDatabase db3 = this.getWritableDatabase();
        ContentValues values3 = new ContentValues();
        values3.put("productName",product.getProductName());
        values3.put("productPrice",product.getProductPrice());
        values3.put("productNumber",product.getProductNumber());
        values3.put("productTotal",product.getProductTotal());
        db3.update("product",values3,"productName = ?",new String[]{String.valueOf(product.getProductName())});
        db3.close();
    }
    //  根据 商品id 查询
    public ArrayList<Product> selectByProductName(Product product){
        ArrayList<Product> productList = new ArrayList<>();
        SQLiteDatabase db = null;
        Cursor cursor = null;
        String query = "SELECT * FROM product WHERE _id = ?";
        try {
            db = this.getReadableDatabase();
            cursor = db.rawQuery(query,new String[]{product.getProductName()});
            if (cursor.moveToFirst()) {
                do {
                    Integer id = cursor.getInt(cursor.getColumnIndex("_id"));
                    String productName = cursor.getString(cursor.getColumnIndex("productName"));
                    String productPrice = cursor.getString(cursor.getColumnIndex("productPrice"));
                    String productWeight = cursor.getString(cursor.getColumnIndex("productWeight"));
                    Integer productNumber = cursor.getInt(cursor.getColumnIndex("productNumber"));
                    String productTotal = cursor.getString(cursor.getColumnIndex("productTotal"));

                    productList.add(new Product(id, productName, productPrice, productWeight, productNumber, productTotal));
                    Log.d("Database", "----productName------" + productName + ", Price: " + productPrice);
                } while (cursor.moveToNext());
            }
        }catch (SQLiteException e){
            Log.e("Database", "Error reading data from database", e);
        }finally {
            if (cursor != null) cursor.close();
            if (db != null && db.isOpen()) db.close();
        }
        return productList;
    }
    // 根据 商品id  进行删除
    public void delectProject(Product product){
        SQLiteDatabase db = this.getWritableDatabase();
        String id = String.valueOf(product.getProductId());
        db.delete("product","_id = ?",new String[]{id});
        db.close();
    }


}

实例化：在onCreateView调用

myOpenHelper = new MyOpenHelper(getContext());
//打开数据库
SQLiteDatabase sqLiteDatabase = myOpenHelper.getReadableDatabase();
//关闭数据库
sqLiteDatabase.close();