Ionic7/Angular18中SQLite的last_insert_rowid()返回0问题

问题描述 #

在Ionic 7+Angular 18项目中，需要向SQLite数据库保存销售记录（ventas）及关联产品（productos_ventas），流程为先保存venta再保存关联产品。使用SELECT last_insert_rowid() as lastId获取刚插入的idventa时，始终返回0，导致productos_ventas表的idventa字段值为0，虽然两张表数据都能存入，但关联关系失效。

数据库表结构 #

[
  {
    "name": "ventas",
    "schema": [
      {"column": "idventa","value": "INTEGER PRIMARY KEY AUTOINCREMENT"},
      {"column": "idcliente","value": "INTEGER NOT NULL"},
      {"column": "idusuario","value": "INTEGER"},
      {"column": "monto_total","value": "REAL"},
      {"column": "fecha","value": "TEXT NOT NULL"}
    ]
  },
  {
    "name": "productos_ventas",
    "schema": [
      {"column": "idproducto_venta","value": "INTEGER PRIMARY KEY AUTOINCREMENT"},
      {"column": "idproducto","value": "INTEGER NOT NULL"},
      {"column": "idventa","value": "INTEGER NOT NULL"},
      {"column": "cantidad","value": "INTEGER NOT NULL"},
      {"column": "precio","value": "REAL NOT NULL"},
      {"column": "monto","value": "REAL"}
    ]
  }
]

原实现代码 #

async saveVentaL(venta: { idcliente: number, idusuario: number, monto_total: number, fecha: string },productos_ventas: { idproducto: number, cantidad: number, precio: number, monto: number }[]): Promise<{ ventaId: number; changes: capSQLiteChanges }> {
  console.log("Starting to save venta locally: ", venta, productos_ventas);
  const ventaSql = 'INSERT INTO ventas (idcliente, idusuario, monto_total, fecha) VALUES (?, ?, ?, ?)';
  const productosVentasSql = 'INSERT INTO productos_ventas (idventa, idproducto, cantidad, precio, monto) VALUES (?, ?, ?, ?, ?)';
  const dbName = await this.getDbName();
  
  const ventaChanges: capSQLiteChanges = await CapacitorSQLite.run({
    database: dbName,
    statement: ventaSql,
    values: [venta.idcliente, venta.idusuario, venta.monto_total, venta.fecha]
  });
  
  if (this.isWeb) {
    await CapacitorSQLite.saveToStore({ database: dbName });
  }
  
  const ventaId = await this.queryLastInsertRowId();
  console.log("Venta ID: ", ventaId);
  
  const statements: any[] = productos_ventas.map(pv => ({
    statement: productosVentasSql,
    values: [ventaId, pv.idproducto, pv.cantidad, pv.precio, pv.monto]
  }));
  
  const productosVentasChanges: capSQLiteChanges = await CapacitorSQLite.executeSet({
    database: dbName,
    set: statements
  });
  
  if (this.isWeb) {
    await CapacitorSQLite.saveToStore({ database: dbName });
  }
  
  console.log("Changes after saving productos_ventas locally: ", productosVentasChanges);
  return { ventaId, changes: productosVentasChanges };
}

private async queryLastInsertRowId(): Promise<number> {
  const dbName = await this.getDbName();
  const sql = 'SELECT last_insert_rowid() as lastId';
  return CapacitorSQLite.query({
    database: dbName,
    statement: sql,
    values: [],
  }).then((response: capSQLiteValues) => {
    const lastId = response.values?.[0]?.lastId;
    if (lastId !== undefined) {
      return lastId;
    } else {
      throw new Error('Failed to retrieve last insert row ID.');
    }
  });
}

日志信息 #

Starting to save venta locally:{idcliente: 1, idusuario: 2, monto_total: 240, fecha: '2024-07-09T07:32:15.541Z'} [{…}]
sqlite.service.ts:279Venta ID:  0
sqlite.service.ts:297Changes after saving productos_ventas locally:  {changes: {…}}
venta.page.ts:96Venta saved locally successfully: {ventaId: 0, changes: {…}}
venta.page.ts:102idventa is undefined. Full response: {ventaId: 0, changes: {…}}
changes: {changes:    {…}}
ventaId: 0
[[Prototype]]: Object
(anonymous) @ venta.page.ts:102
venta.page.ts:107Venta save operation completed.

解决方案 #

核心原因 #

last_insert_rowid()依赖当前数据库连接的会话上下文，而CapacitorSQLite的run和query操作可能在不同的连接会话中执行，导致无法获取到正确的插入ID。更可靠的方式是直接使用run方法返回的结果中的insertId字段，该字段是CapacitorSQLite内部捕获的插入记录ID。

修改后的代码 #

删除单独的queryLastInsertRowId方法（或保留但不再使用），直接从run的返回值中获取insertId：

async saveVentaL(venta: { idcliente: number, idusuario: number, monto_total: number, fecha: string },productos_ventas: { idproducto: number, cantidad: number, precio: number, monto: number }[]): Promise<{ ventaId: number; changes: capSQLiteChanges }> {
  console.log("Starting to save venta locally: ", venta, productos_ventas);
  const ventaSql = 'INSERT INTO ventas (idcliente, idusuario, monto_total, fecha) VALUES (?, ?, ?, ?)';
  const productosVentasSql = 'INSERT INTO productos_ventas (idventa, idproducto, cantidad, precio, monto) VALUES (?, ?, ?, ?, ?)';
  const dbName = await this.getDbName();
  
  const ventaChanges: capSQLiteChanges = await CapacitorSQLite.run({
    database: dbName,
    statement: ventaSql,
    values: [venta.idcliente, venta.idusuario, venta.monto_total, venta.fecha]
  });
  
  // 直接从run操作的返回结果中获取插入的ID
  const ventaId = ventaChanges.insertId;
  console.log("Venta ID: ", ventaId);

  if (this.isWeb) {
    await CapacitorSQLite.saveToStore({ database: dbName });
  }

  const statements: any[] = productos_ventas.map(pv => ({
    statement: productosVentasSql,
    values: [ventaId, pv.idproducto, pv.cantidad, pv.precio, pv.monto]
  }));
  
  const productosVentasChanges: capSQLiteChanges = await CapacitorSQLite.executeSet({
    database: dbName,
    set: statements
  });
  
  if (this.isWeb) {
    await CapacitorSQLite.saveToStore({ database: dbName });
  }
  
  console.log("Changes after saving productos_ventas locally: ", productosVentasChanges);
  return { ventaId, changes: productosVentasChanges };
}

验证效果 #

修改后，ventaId将获取到正确的idventa值，productos_ventas表的idventa字段会正确关联对应的销售记录，不再出现值为0的情况。

内容的提问来源于stack exchange，提问作者Eduardo Renteria Blanco