javascript 获取node.js中的最后一个插入id并将其传递给第二个查询

bvn4nwqk  于 2023-06-04  发布在  Java
关注(0)|答案(4)|浏览(232)

我在我的sql db2 表,一个表名为俱乐部和一个表名为球员,他们是由一对多的关系连接,在node.js的查询工作正常,但我不能得到最后插入表俱乐部,我需要它插入表球员的外键

here what i have tried in node.js:


       module.exports={
 create:(data,callback)=>{
  var myArray = new Array();
 /* for(let item of data.players) {
    console.log(item.firstname);
}*/

data.players.forEach((player) => {
  console.log(player.id);
  console.log(player);
  var playerModel ={
    id : player.id,
    firstname : player.firstname,
    lastname : player.lastname,
    position : player.position,
    price : player.price,
    appearences : player.appearences,
    goals : player.goals,
    assists : player.assists,
    cleansheets : player.cleansheets,
    redcards : player.redcards,
    yellowcards : player.yellowcards,
    image : player.image,
    clubid : player.clubid,
  };
  console.log("model"+playerModel.position);
  myArray.push(playerModel);
});
var id;
 
 pool.query(
      'insert into club(userid,name,price) values(?,?,?)',
   [
        data.userid,
        data.name,
        data.price
      ],
    
      (error,result) => {
        if(error){
          callback(error);
         
        }
     /*   id = result.insertId;
        console.error(result);
        console.log(result+"  result");*/
        
        console.log(result.insertId);
        return callback(null,result.insertId);
      },

      
    );


    for(var item of myArray){
    pool.query(
      
      'insert into players(id,firstname,lastname,position,price,appearences,goals,assists,cleansheets,redcards,yellowcards,image,clubid) values (?,?,?,?,?,?,?,?,?,?,?,?,?)',
       [ 
       item.id,
       item.firstname,
       item.lastname,
       item.position,
       item.price,
       item.appearences,
       item.goals,
       item.assists,
       item.cleansheets,
       item.redcards,
       item.yellowcards,
       item.image,
       (
        'select top 1 id from club order by id desc'
        )
       ],
      
 
     (error,results,fields)=>{
       if(error){
         callback(error);
       }
       return callback(null,results);
     },
    );
    }
  },

我不知道该怎么做

wko9yo5t

wko9yo5t1#

如果我理解正确的话,这里应该有一个子查询。

-- first insert the club from paraterized query
insert into club (clubid, name, price) 
values (? , ? , ?);

-- then use a subquery to find the last inserted club id
insert into
   players (id, firstname, lastname, position, price, appearences, goals, assists, cleansheets, redcards, yellowcards, image, clubid) 
values
   (
       ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , 
      (
         select clubid 
         from club 
         order by clubid desc
         limit 1
      )
   )
;

除此之外,insert语句不会给予任何数据。如果你想从NodeJS的第一次调用中获取ID,你需要运行一个批处理语句。在发送到SQL Server的同一批语句中执行1个insert和1个select。查看更多关于多语句配置的信息。node-mysql multiple statements in one query

const pool = mysql.createConnection({multipleStatements: true});
pool.query(`
  insert into club(userid,name,price) values(?,?,?);
  select clubid from club order by clubid desc limit 1;
  `
  , params
  , function(err, results) {
  if (err) throw err;
    // now the id will be the second item of the batch statement result
    const myId = results[1]
});
)

基于这两点,你可以把它们结合起来,实际上。

pool.query(`
  insert into club(userid,name,price) values(?,?,?);
  insert into players (id, firstname, lastname, position, price, appearences, goals, assists, cleansheets, redcards, yellowcards, image, clubid) 
  values
   (
       ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , 
      (
         select clubid 
         from club 
         order by clubid desc
         limit 1
      )
   );
  `
  , params
)

您也可以为此目的创建一个存储过程。

xyhw6mcr

xyhw6mcr2#

创建多查询批处理,如

INSERT INTO table1 (column1, column2, column3) VALUES (?, ?, ?);
INSERT INTO table2 (column1, reference_column, column3) VALUES (?, LAST_INSERT_ID(), ?);

使用以下方法执行:
1.支持多查询批处理执行
1.将批处理作为事务执行
参数作为一个实体数据数组提供给该方法(对于所示的代码,它将包含5个值,前3个将被插入到第一个查询中,所有其他的将被插入到第二个查询中)。第一次查询分配的id值将由函数自动检索并插入到第二次查询中。
我不知道Node.JS中描述属性的方法是什么。但它必须存在。

qyyhg6bp

qyyhg6bp3#

解决这个问题的另一种方法是将包含第一个INSERT语句的初始查询放置到一个变量中:
const first_insert = db.query('INSERT')...
然后在第一个promise的末尾返回变量,然后找到变量的属性值(因为它毕竟返回了一个JavaScript对象):

.then((first_insert) => {
   console.log(Object.entries(first_insert)); //returns array
   console.log(first_insert[0]); 
/*returns contents of first index of previously returned array, you'll see an object with a insertId property, thats what you need*/
   console.log(first_insert[0].insertId) 
/*this is the value you need to pass to each following statement*/
/*store that value in a variable that you declarre before any of the db methods, I named mine pk*/
   pk = first_insert[0].insertId
/*Now use that variable for the foreign key and to correspond with the placeholder in whatever queries you use*/
}
vyswwuz2

vyswwuz24#

如何在NodeJS,oracle中使用它?我的错误是错误:ORA-00933:SQL命令未正确结束。
--首先从parameterized query插入club(clubid,name,price)值(?,?,?);
--然后使用子查询查找最后插入的俱乐部id插入到球员(id,firstname,lastname,position,price,appearances,goals,assists,cleansheets,redcards,yellowcards,image,clubid)值(????????????????????????????????????????????????????????????????????????????,?,?,(通过clubid desc limit 1从俱乐部订单中选择clubid));

相关问题