如何设计数据库查询的方法

Question

在用node写一个查询商品的方法,用的是mysql

在网上查到的node操作mysql方法:

router.get("/select",(req,res) => {
    let name = req.params.name;
    let seller = req.params.seller;
    let buyer = req.params.buyer;
    let maxPrice = req.params.maxPrice;
    let minPrice = req.params.minPrice;
    db.connection.connect();
    db.connection.query('SELECT * from commoditys WHERE name=? AND seller=? AND buyer=? AND price>? AND price<?', [name,seller,buyer,minPrice,maxPrice], (err, back) => {
        if (err) {
            console.log("select mysql ERR\n" + err.message);
            res.send({status: "fail", message: "unknown err"});
            db.connection.end();
        } else {
            res.send({data: back});
            db.connection.end();
            res.end();
        }
    })
})

但是这样查询就只能所有参数都由才能查

如果我想写一个可以适应严格查询和范围查询的方法该怎么写呢

最开始我想到的是 用if来判断用户get里面传了什么 在拼接sql字符串传到query方法里,但感觉有点麻烦,想问问有没有其他的方法

Answer 1

router.get("/select", (req, res) => {
    let db = new Database('localhost', 'xxx', 'xxxxxx', 'xxxx', 'xxxxserve');
    let name = req.query.name;
    let seller = req.query.seller;
    let buyer = req.query.buyer;
    let maxPrice = req.query.maxPrice;
    let minPrice = req.query.minPrice;
    let sql = 'SELECT * from commoditys WHERE ';
    let params = [];
    let flag = false;
    console.log(name);
    if (name != null) {
        sql += "name=? ";
        params[params.length] = name;
        flag = true;
    }
    if (seller != null) {
        if (flag == true)
            sql += "AND "
        sql += "seller=? ";
        params[params.length] = seller;
        flag = true;
    }
    if (buyer != null) {
        if (flag == true)
            sql += "AND "
        sql += "buyer=? ";
        params[params.length] = buyer;
        flag = true;
    }
    if (maxPrice != null) {
        if (flag == true)
            sql += "AND "
        sql += "price<=? ";
        params[params.length] = maxPrice;
        flag = true;
    }
    if (minPrice != null) {
        if (flag == true)
            sql += "AND "
        sql += "price>=?";
        params[params.length] = minPrice;
    }
    console.log(sql + "  " + params);
    db.connection.connect();
    db.connection.query(sql, params, (err, back) => {
        if (err) {
            console.log("select mysql ERR\n" + err.message);
            res.send({status: "fail", message: "unknown err"});
            db.connection.end();
        } else {
            res.send({data: back});
            db.connection.end();
            res.end();
        }
    })
})

写了个拼串的,感觉太长了