ReactNative 对接SQLite3的通用增删改查工具类以及使用方法

import SQLite from "react-native-sqlite-storage";
import md5 from "../Utils/md5.js";

let db;

// 初始化数据库连接
export function initializeDatabase() {
  db = SQLite.openDatabase({ name: "sbcoin.db", location: "default" }, () => {
    console.log("Database initialized successfully");
  });
}

// 关闭数据库连接
export function closeDatabase() {
  if (db) {
    db.close(() => {
      console.log("Database connection closed");
    });
  }
}

// 根据表名和条件构建 SQL 查询语句
function buildQueryWithParams(tableName, conditions) {
  let query = `SELECT * FROM ${tableName}`;
  const params = [];

  if (conditions && Object.keys(conditions).length > 0) {
    const conditionKeys = Object.keys(conditions);
    const conditionClauses = conditionKeys.map((key) => {
      params.push(conditions[key]); // 将条件值添加到参数数组
      return `${key} = ?`;
    });
    query += " WHERE " + conditionClauses.join(" AND ");
  }

  return { query, params };
}
// 通用的插入或更新数据方法
export function insertOrUpdateData(tableName, data, condition, primaryKey) {
  return new Promise((resolve, reject) => {
    db.transaction((tx) => {
      if (!condition) {
        // 如果没有传入 condition，即新增
        const insertQuery = `INSERT INTO ${tableName} (${Object.keys(data).join(
          ", "
        )}) VALUES (${Object.keys(data).fill("?").join(", ")})`;
        const insertParams = Object.values(data);
        // 执行插入
        tx.executeSql(
          insertQuery,
          insertParams,
          (tx, insertResult) => {
            // 获取插入的行的主键 ID
            const insertedId = insertResult.insertId;
            // 返回插入的行的主键 ID
            resolve(insertedId);
          },
          (error) => {
            reject(error);
          }
        );
      } else {
        // 如果传入了 condition，即更新或插入
        // 构建查询的 SQL 语句
        const selectIdQuery = `SELECT ${primaryKey} FROM ${tableName} WHERE ${condition}`;
        // 查询数据
        tx.executeSql(
          selectIdQuery,
          [],
          (tx, selectIdResult) => {
            const rows = selectIdResult.rows;
            if (rows.length > 0) {
              // 如果查询到数据，执行更新
              const updateQuery = `UPDATE ${tableName} SET ${Object.keys(data)
                .map((key) => `${key} = ?`)
                .join(", ")} WHERE ${condition}`;
              const updateParams = Object.values(data);
              tx.executeSql(
                updateQuery,
                updateParams,
                (tx, updateResult) => {
                  // 如果更新的行数为1，返回更新的行的主键 ID
                  if (updateResult.rowsAffected === 1) {
                    const updatedId = rows.item(0)[primaryKey];
                    resolve(updatedId);
                  } else {
                    // 如果更新的行数不为1，返回更新的行的主键 ID 数组
                    const updatedIds = [];
                    for (let i = 0; i < rows.length; i++) {
                      updatedIds.push(rows.item(i)[primaryKey]);
                    }
                    resolve(updatedIds);
                  }
                },
                (error) => {
                  reject(error);
                }
              );
            } else {
              // 如果没有查询到数据，执行插入
              const insertQuery = `INSERT INTO ${tableName} (${Object.keys(
                data
              ).join(", ")}) VALUES (${Object.keys(data)
                .fill("?")
                .join(", ")})`;
              const insertParams = Object.values(data);
              tx.executeSql(
                insertQuery,
                insertParams,
                (tx, insertResult) => {
                  // 获取插入的行的主键 ID
                  const insertedId = insertResult.insertId;
                  // 返回插入的行的主键 ID
                  resolve(insertedId);
                },
                (error) => {
                  reject(error);
                }
              );
            }
          },
          (error) => {
            reject(error);
          }
        );
      }
    });
  });
}

// 通用的删除数据方法
export function deleteData(tableName, conditions) {
  return new Promise((resolve, reject) => {
    db.transaction((tx) => {
      const conditionKeys = Object.keys(conditions);
      const conditionString = conditionKeys
        .map((key) => `${key} = ?`)
        .join(" AND ");
      const conditionValues = conditionKeys.map((key) => conditions[key]);

      const query = `DELETE FROM ${tableName} WHERE ${conditionString}`;
      debugger;
      tx.executeSql(
        query,
        conditionValues,
        (tx, result) => {
          // 获取受影响的行数
          const rowsAffected = result.rowsAffected || 0;
          resolve(rowsAffected);
        },
        (error) => {
          console.log(`Error deleting data from ${tableName}:`, error);
          reject(error);
        }
      );
    });
  });
}

// 根据表名和条件查询信息
export function getByConditions(tableName, conditions) {
  return new Promise((resolve, reject) => {
    // 构建 SQL 查询语句和参数
    const { query, params } = buildQueryWithParams(tableName, conditions);
    db.transaction((tx) => {
      tx.executeSql(
        query,
        params,
        (tx, result) => {
          const data = [];
          for (let i = 0; i < result.rows.length; i++) {
            data.push(result.rows.item(i));
          }
          resolve(data);
        },
        (error) => {
          reject(error);
        }
      );
    });
  });
}

将这个配置好后，查询的代码如下：

 getByConditions(MatcheckrecordTable, {
//这里是查询的条件,用对象形式
      Inspectbillmain_id: inspectBillMainId,
    })
      .then((mainTableData) => {
        // 遍历主表数据
        // 检查是否有主表数据
        if (mainTableData.length === 0) {
          // 主表没有数据，可以根据需求处理
          console.log("主表没有数据");
          return;
        }
        const promises = mainTableData.map((mainRecord) => {
          // 根据主表记录的matcheckrecord_id去查询子表数据
          return getByConditions(MatcheckrecorditemTable, {
            matcheckrecord_id: mainRecord.matcheckrecord_id,
          })
            .then((itemTableData) => {
              // 将子表数据封装到主表记录中
              mainRecord.components = itemTableData;
              return mainRecord;
            })
            .catch((error) => {
              console.error("Error fetching itemTableData:", error);
              return mainRecord; // 返回主表记录，但没有子表数据
            });
        });

        // 所有的子表查询都完成后更新state
        Promise.all(promises)
          .then((dataList) => {
            this.setState({ dataList });
          })
          .catch((error) => {
            console.error("Error fetching data:", error);
          });
      })
      .catch((error) => {
        console.error("Error fetching data:", error);
      });

以及新增、修改、查询的代码使用案例。

  handleSave = () => {
    const matcheckrecordData = {
      Inspectbillmain_id: receivedData.Inspectbillmain_id,
      sampledate: new Date().toISOString().split("T")[0], // 当前日期
      batchno: this.state.batchNumber,
      status: "已检",
      // 其他默认为空的字段
    };
    // 插入或更新 td_qc_matcheckrecord 表
    insertOrUpdateData(
      MatcheckrecordTable,
      matcheckrecordData,
      `batchno = ${matcheckrecordData.batchno}`,
      "matcheckrecord_id"
    )
      .then((matcheckrecordId) => {
        // 获取返回的 matcheckrecordId
        // 构建要插入到 td_qc_matcheckrecorditem 表的数据
        const matcheckrecorditemData = this.state.components.map(
          (component) => ({
            matcheckrecord_id: matcheckrecordId,
            itemid: component.itemid, // 从数据库中获取的 itemid
            itemname: component.itemname,
            itemvalue: component.value,
            Extend1: component.itemtype, // type 对应的是 Extend1
            // 其他字段根据 component 对象中的值设置
            // 比如其他字段名字和值
          })
        );
        debugger;
        // 删除 td_qc_matcheckrecorditem 表中的数据
        deleteData(MatcheckrecorditemTable, {
          matcheckrecord_id: matcheckrecordId,
        })
          .then(() => {
            // 插入 td_qc_matcheckrecorditem 表
            Promise.all(
              matcheckrecorditemData.map((itemData) =>
                insertOrUpdateData(MatcheckrecorditemTable, itemData)
              )
            )
              .then(() => {
                // 所有插入操作成功后，可以执行一些操作或者导航到其他页面
                console.log("保存成功");
                navigation.goBack();
              })
              .catch((error) => {
                console.error("Error inserting matcheckrecorditem:", error);
              });
          })
          .catch((error) => {
            console.error("Error deleting matcheckrecorditem:", error);
          });
      })
      .catch((error) => {
        console.error("Error inserting or updating matcheckrecord:", error);
      });
  };