nodejs+express+postgresql做分页展示的逻辑

首先简单封装下pg模块

1、PG_DIY.js

const { Client } = require('pg')

// 数据库配置
let config = {
    host: '192.***.**.**',
    port: 5432,
    user: "postgres",
    password: "abc123456",
    database: "canonchain_explorer",
    // 扩展属性
    max: 20, // 连接池最大连接数
    idleTimeoutMillis: 3000, // 连接最大空闲时间 3s
}
let client = new Client(config);

let PG = function () {
    console.log("准备数据库连接...");
};
PG.prototype.getConnection = function () {
    client.connect(function (err) {
        if (err) {
            return console.error('数据库链接失败:', err);
        }
        client.query('SELECT NOW() AS "theTime"', function (err, result) {
            if (err) {
                return console.error('error running query', err);
            }
            console.log("数据库连接成功...");
        });
    });
};
PG.prototype.query = function (sqlStr, cb) {
    client.query(sqlStr, function (err, result) {
        // console.log("result",result)
        if (err) {
            cb("error");
        } else {
            if (result.rows != undefined) {
                cb(result.rows);
            } else {
                cb();
            }
        }
    });
};
module.exports = new PG();

2、写api接口

var express = require("express");
var router = express.Router();

var Czr = require('../czr');
let czr = new Czr();

var pgclient = require('../PG_DIY');// 引用上述文件
pgclient.getConnection();

var responseData = null;
router.use(function (req, res, next) {
    responseData = {
        code: 0,
        message: ""
    }
    next();
})

// http://localhost:3000/api/get_accounts
router.get("/get_accounts", function (req, res, next) {
    var queryPage = req.query.page;// ?page=2
    var page, //当前页数
        pages, // 合计总页数
        count; //总条数

    var OFFSETVAL;//前面忽略的条数
    var LIMITVAL = 2;//每页显示条数
    if (typeof Number(queryPage) !== "number") {
        page = 1;
    } else {
        page = Number(queryPage) || 1;
    }
    pgclient.query("Select COUNT(1) FROM accounts", (count) => {
        console.log("count>>", count[0].count);
        count = count[0].count;
        pages = Math.ceil(count / LIMITVAL);
        //paga 不大于 pages
        page = Math.min(pages, page);
        //page 不小于 1
        page = Math.max(page, 1);
        OFFSETVAL = (page - 1) * LIMITVAL;
        // *,balance/sum(balance) 
        pgclient.query("Select * FROM accounts ORDER BY balance DESC LIMIT " + LIMITVAL + " OFFSET " + OFFSETVAL, (data) => {
            //改造数据 排名 , 金额,占比
            var basePage = Number(queryPage) - 1; // 1 2
            var accounts = data;
            accounts.forEach((element, index) => {
                //占比 element.balance / 1618033988*1000000000000000000
                element.proportion = ((1000000000000000000 / (1618033988 * 1000000000000000000)) * 100).toFixed(10) + "%";
                //改成CZR单位,并保留6位精度
                let tempVal = czr.utils.fromWei(
                    element.balance,
                    "czr"
                );
                var reg = /(\d+(?:\.)?)(\d{0,4})/;
                var regAry = reg.exec(tempVal);
                var integer = regAry[1];
                var decimal = regAry[2];
                if (decimal) {
                    while (decimal.length < 6) {
                        decimal += "0";
                    }
                }
                element.balance = integer + decimal; //TODO Keep 6 decimal places
                element.rank = LIMITVAL * basePage + (index + 1);
            });

            responseData.account = accounts;
            responseData.page = Number(queryPage);
            responseData.count = Number(count);
            responseData.code = 0;
            responseData.message = "success";
            res.json(responseData);
        });
    });



})


module.exports = router;

~~~~~

未经允许不得转载:朱邦邦的博客 »  nodejs+express+postgresql做分页展示的逻辑

赞 (0)

评论 0

  • 昵称 (必填)
  • 邮箱 (必填)
  • 网址