使用Node.js调用Sqlite3模块写的大数据查询接口

项希盛 / 2023-05-06 / 原文

const sqlite3 = require('sqlite3');
const http = require('http');
const url = require('url');

const SqliteDb = async (dbFile) => {

  const pri = {};
  pri.db = new sqlite3.Database(dbFile);

  const pub = {};

  pub.prepare = (sql) => {
    return pri.db.prepare(sql);
  };

  pub.fStmtDo = (stmt, type = 'all', v = []) => {
    return new Promise((resolve, reject) => {
      v.push((err, rows) => {
        if (err) {
          reject(err);
          return;
        }
        resolve(rows);
      });
      stmt[type](...v);
    });
  };

  pub.fSelect = async (sTableName, mWhere = {}, iLimit = 10) => {
    // 查找数据
    const aWhere = [];
    const aValues = [];
    for (const k in mWhere) {
      aWhere.push(`"${k}"=?`);
      aValues.push(mWhere[k]);
    }
    const sWhere = aWhere ? ' WHERE (' + aWhere.join(')AND(') + ')' : '';
    const sql = `SELECT * FROM "${sTableName}"${sWhere} LIMIT ${iLimit}`;
    const stmt = pri.db.prepare(sql);
    return await pub.fStmtDo(stmt, 'all', aValues);
  };

  return pub;
};

function pushData(data, rows) {
  for (const row of rows) {
    data.push(row);
  }
}

async function main() {
  const hostname = '0.0.0.0';
  const port = 58158;
  const db1 = await SqliteDb('xxxxx.sqlite3.db');
  const server = http.createServer(async (req, res) => {
    const queryObject = url.parse(req.url, true).query;
    const cx = queryObject.cx;

    const json = {};
    json.data = [];
    pushData(json.data, await db1.fSelect('data', {'account': cx}));
    pushData(json.data, await db1.fSelect('data', {'phone': cx}));

    // 设置响应头
    res.statusCode = 200;
    res.setHeader('Content-Type', 'application/json');
    // 发送响应内容
    res.end(JSON.stringify(json));
  });
  server.listen(port, hostname, () => {
    console.log(`Server running at http://${hostname}:${port}/`);
  });
}
main();