日常水文章之SQLite数据库的增删改查

上一节我创建了一个叫ShopStore.db的db数据库,这一节我们就来看看怎么使用这个数据库

1.增加数据:

/**
 * 增加数据
 * 参数介绍
 * @param pid       唯一id
 * @param trayNo    托盘编号
 * @param id        商品id
 * @param name      商品名称
 * @param image     商品图片地址
 * @param weight    重量(参考或者对比使用)
 * @param salePrice 零售价
 * @param stock 库存
 */
fun setCommodity(
    trayNo: String, id: String,
    name: String, image: String,
    weight: String, salePrice: String, stock: String
) {
    db!!.beginTransaction()//以独占模式开始事务
    val contentValues = ContentValues()
    contentValues.put("trayNo", trayNo)
    contentValues.put("id", id)
    contentValues.put("name", name)
    contentValues.put("image", image)
    contentValues.put("weight", weight)
    contentValues.put("salePrice", salePrice)
    contentValues.put("stock", stock)
    db!!.insert("Shop", null, contentValues)
    db!!.setTransactionSuccessful()//将当前事务标记为成功
    db!!.endTransaction()//结束交易
}

此处的db是上一节的SQLiteDatabase,调用方式:

SQLiteDAO.instance!!.setCommodity(
    "trayNo",
    "id",
    "name",
    "image",
    "weight",
    "salePrice",
    "stock"
)

 

2.删除

 (1).

/*
* 清空数据库表Shop的所有数据
* */
fun deleteTable(tableName: String) {
    val sql = "delete from " + tableName
    db!!.execSQL(sql)
}

(2).

 /*
  *删除某个商品  因为pid是唯一主键 这个刚好可以判断 java代码
  */
 public int getDeleteShop(String pid) {
     // DELETE FROM 表名称 WHERE 列名称 = 值
        return db!!.delete("Shop", "pid=?", new String[]{String.valueOf(pid)});
  }

 

3.改:

 (1)

/**
 * 修改商品数据 所有数据
 * @param pid       唯一id
 * @param trayNo    托盘编号
 * @param id        商品id
 * @param name      商品名称
 * @param image     商品图片地址
 * @param weight    重量(参考或者对比使用)
 * @param salePrice 零售价
 * @param stock      库存
 * */
fun updateUsers(
    trayNo: String?, id: String?,
    name: String?, image: String?, weight: String?,
    salePrice: String?, stock: String?,
    pid: String
) {
    Log.e("TAG", "修改状态的id:$pid")
    val contentValues = ContentValues()
    contentValues.put("trayNo", trayNo)
    contentValues.put("id", id)
    contentValues.put("name", name)
    contentValues.put("image", image)
    contentValues.put("weight", weight)
    contentValues.put("salePrice", salePrice)
    contentValues.put("stock", stock)
    var ret: Int = -1
    do {
        ret = db!!.update("Shop", contentValues, "pid=?", arrayOf(pid))
    } while (ret < 0)
}

 (2):

/**
 * 修改商品数据 只修改库存
 * @param pid       唯一id
 * @param stock      库存
 * */
fun updateUsers(
    stock: String?, pid: String
) {
    Log.e("TAG", "修改状态的id:$pid"+"\n库存修改====="+stock)
    val contentValues = ContentValues()
    contentValues.put("stock", stock)
    var ret: Int = -1
    do {
        ret = db!!.update("Shop", contentValues, "pid=?", arrayOf(pid))
    } while (ret < 0)
 
}

修改可以全部修改,也可以修改单个属性,这里主要根据pid去对应修改内容

4.查:

/**
 * 查询
 * */
fun getProduct(): List<ProductEntity> {
    val list: MutableList<ProductEntity> = ArrayList<ProductEntity>()
    val sql = "select * from Shop"
    try {
        val cursor: Cursor = db!!.rawQuery(sql, null)
        if (cursor != null) {
            while (cursor.moveToNext()) {
                val content = ProductEntity()
                content.pid = cursor.getInt(cursor.getColumnIndex("pid"))
                content.id = cursor.getString(cursor.getColumnIndex("id"))
                content.name = cursor.getString(cursor.getColumnIndex("name"))
                content.image = cursor.getString(cursor.getColumnIndex("image"))
                content.salePrice = cursor.getString(cursor.getColumnIndex("salePrice"))
                content.trayNo = cursor.getString(cursor.getColumnIndex("trayNo"))
                content.weight = cursor.getString(cursor.getColumnIndex("weight"))
                content.stock = cursor.getString(cursor.getColumnIndex("stock"))
                list.add(content)
            }
            cursor.close()
        }
    } catch (e: Exception) {
        Log.e("TAG", "内容获取失败=============")
    }
    Log.v(TAG, "获取数据==select * from Shop===" + list.size)
    return list
}

 这里的ProductEntity是实体类:

class ProductEntity {
    var pid = 0//唯一ID
    var trayNo: String? = null //托盘
    var id: String? = null  //物品id
    var name: String? = null    //物品名字
    var image: String? = null   //物品图片地址
    var weight: String? = null  //物品重量 单位g
    var salePrice: String? = null   //物品单价 单位元
    var stock: String? = null   //物品库存
}

这里查询主要看 val sql = "select * from Shop"这句命令,这里可以使用where去查询,也可以全查!