在nodejs的单个api调用中执行两个sql查询

ht4b089n  于 2021-08-09  发布在  Java
关注(0)|答案(0)|浏览(243)

这是我的第一张table

CREATE TABLE `shop` (
 `id` int(10) NOT NULL AUTO_INCREMENT,
 `name` varchar(255) NOT NULL,
 `shopId` varchar(10) DEFAULT NULL,
 `shopPassword` varchar(255) NOT NULL,
 `shopStatus` tinyint(4) DEFAULT '1',
 `phone` int(10) NOT NULL,
 PRIMARY KEY (`id`),
 UNIQUE KEY `shopId` (`shopId`)
) ENGINE=InnoDB AUTO_INCREMENT=36 DEFAULT CHARSET=latin1

这是第二个表shopaddress

CREATE TABLE `shopaddress` (
 `id` int(10) NOT NULL AUTO_INCREMENT,
 `shopNo` int(10) NOT NULL,
 `complex` varchar(50) NOT NULL,
 `landmark` varchar(50) DEFAULT NULL,
 `street` varchar(50) NOT NULL,
 `area` varchar(50) NOT NULL,
 `city` varchar(50) NOT NULL,
 `shop_id` int(10) DEFAULT NULL,
 PRIMARY KEY (`id`),
 KEY `shop_id` (`shop_id`),
 CONSTRAINT `shopaddress_ibfk_1` FOREIGN KEY (`shop_id`) REFERENCES `shop` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

我想使用nodejs在一个api调用中在两个表中添加行。另外,我想在第二个表中添加外键引用,这个地址属于这个商店。
这是我当前抛出错误的代码,我可以得到一个正确的方法来处理这种情况吗

module.exports = {
    add: (data, callBack) => {
        // Begin Transaction  
        pool.getConnection((err, connection) => {
            connection.beginTransaction(err => {
                if (err) {
                    throw err
                }
                var shop_query = 'INSERT INTO shop (name, shopId, shopPassword, shopStatus, phone) VALUES (?,?,?,?,?)';

                connection.query(shop_query, [data.name, shortid.generate(), shortid.generate(), data.shopStatus, data.phone], (err, results) => {
                    if (err) {
                        return connection.rollback(_ => {
                            throw err
                        })
                    }
                    console.log(results.insertId)
                    let lastId = results.insertId

                    var shopaddress_query = 'INSERT INTO shopaddress (shopNo, complex, landmark, street, area, city, shop_id) VALUES ((?,?,?,?,?,?,?)';

                    connection.query(shopaddress_query, [data.shopNo, data.complex, data.landmark, data.street, data.area, data.city, lastId], (err, results) => {
                        if (err) {
                            return connection.rollback(_ => {
                                throw err
                            })
                        }
                        connection.commit(err => {
                            if (err) {
                                connection.rollback(_ => {
                                    throw err
                                })
                            }
                            connection.end()
                            callBack(null, results)
                        })
                    })
                })
            })
        })
    },

暂无答案!

目前还没有任何答案,快来回答吧!

相关问题