[Node.js 8-2강] 다중쿼리 처리 방법, sql에 파라미터 매핑하는 다양한 방법

SQL에 파라미터 매핑하는 방법

1. 파라미터를 직접 하드코딩(비권장)

직관적이라 설명드릴 건 없네요. 하지만 보안 문제(SQL Injection)로 인해 비 권장합니다. 

var clubSn = req.params.clubSn;
var sql = 'SELECT * FROM CLUB WHERE CLUB_SN = "' + clubSn + '";';
dbconn.query(sql, function(err, results, field){
	....
});

 

2. 파라미터 1개 자동 매핑

query() 함수의 두번째 인자에 파라미터 변수를 추가해주면 됩니다.  그러면 자동으로 sql의 ' ? ' 에 매핑되어 sql이 실행되는 거죠

var clubSn = req.params.clubSn;
var sql = 'SELECT * FROM CLUB WHERE CLUB_SN = ?';
dbconn.query(sql, clubSn, function(err, results, field){
	...
});

 

3. 파라미터 2개 이상 자동 매핑

query() 함수의 두 번째 인자에 파라미터 변수 배열 형태로 추가해주면 됩니다.  그러면 자동으로 sql의 ' ? ' 의 순서와 배열 순서가 일치하도록 매핑되어 sql이 실행되는 거죠

var clubSn = req.params.clubSn;
var clubNm = req.params.clubNm;
var sql = 'SELECT * FROM CLUB WHERE CLUB_SN = ? AND CLUB_NM = ?';
dbconn.query(sql, [clubSn, clubNm], function(err, results, field){
	...
});

insert 구문의 경우는 아래와 같이 사용하면 됩니다. VALUES 뒤를 ? 로 해주면 알아서 sql이 만들어집니다.

var insertValArr = ['a','b','c','d'];
var sql = "INSERT INTO CLUB_MBER (CLUB_SN, MBER_NM, MBER_SN, MNGR_YN) VALUES ?";
dbconn.query(sql, insertValArr, function(err, result){
	...
});

 

 

4. insert SQL을 좀 더 쉽게 만드는 방법

insert 대상이 되는 테이블의 컬럼이 많으면 일일이 변수명을 입력하고 순서가 맞는지 확인하기 힘듭니다. 이런 경우는 아래와 같이 파라미터를 객체로 넘기면 쉽습니다. 객체 변수명은 insert 테이블의 컬럼명과 동일하게 작성해야 합니다. 

var clubMberObj = {
  CLUB_SN : clubSn,
  MBER_NM : mberNm,
  MBER_SN : mberSn,
  MNGR_YN : 'Y',
  PROFILE_IMAGE : profileImage
};

//클럽회원 저장
var sql = "INSERT INTO CLUB_MBER SET ? "

dbconn.query(sql, clubMberObj, function(err, result){
	...
});

 

 

5. mysql 모듈의 escape(), format()를 이용해서 SQL 만드는 방법

하나의 파라미터를 하드코딩 형태로 매핑할 경우 SQL 인젝션의 위험을 피하기 위해 mysql의 escape()를 이용합니다. 

특히 다중 쿼리를 만들 때는 앞에서 본 파라미터 매핑방법을 사용하기 애매한 경우가 있기 때문에 파라미터를 직접 하드코딩 하거나 mysql의 format() 기능을 사용해서 sql을 만듭니다. 

var  mysql = require('mysql');

var clubSn = req.params.clubSn;
var sql = 'SELECT * FROM CLUB WHERE CLUB_SN = ' + mysql.escape(clubSn) + '; ';
dbconn.query(sql, function(err, results){
	....
});

 

mysql의 format을 이용해서 sql에 파라미터를 매핑하는 방법도 있습니다. (mysql 모듈 import 해야 함)

var  mysql = require('mysql');

var sql = "UPDATE CNFRNC_GG SET A_TEAM_SCORE = ?, B_TEAM_SCORE = ? WHERE CNFRNC_SN = ?";
var item = [aTeamScore, bTeamScore, cnfrncSn];
sql = mysql.format(sql, item);

 

앞에 여러 파라미터 매핑하는 방법이 있기 때문에 굳이 mysql.format을 사용할 일은 별로 없었습니다. 

하지만 동일한 구조의 update를 여러 개 실행해야 할 경우, 다중 쿼리를 처리해야할 경우 유용하게 사용할 수 있습니다. 

var updateValArr = [];
for (var i = 0; i < aTeamScoreArr.length; i++) {
  var updateVal = [aTeamScore, bTeamScore, cnfrncSn, groupNmArr[i], ggOrdrArr[i]];
  updateValArr.push(updateVal);
}

var sql = "UPDATE CNFRNC_GG SET A_TEAM_SCORE = ?, B_TEAM_SCORE = ? WHERE CNFRNC_SN = ? AND GROUP_NM = ? AND GG_ORDR = ?;";
var sqls = "";
updateValArr.forEach(function(item){
	sqls += mysql.format(sql, item);
});

dbconn.query(sqls, function(err, result){
	...
});

 

 

Node.js에서 mysql 다중쿼리 처리 방법

처음엔 비동기 방식에서 여러 sql을 조회해서 클라이언트에 어떻게 넘겨줘야 하나 고민이 많았습니다. 콜백 함수를 계속 사용해야 하나 아니면 클라이언트에서 ajax로 여러 번 호출해야 하나..

 

그런데 역시나 mysql 모듈에서 한 번에 여러 쿼리를 실행하고 결과값을 리턴하는 기능이 있었습니다. 

사용방법도 쉽더군요. 실행할 쿼리를 ';'로 구분해서 넘겨주면 결과값을 배열 형태로 리턴해줍니다. 

var sql1 = 'SELECT CLUB_NM FROM CLUB; '; // 클럽목록
var sql2 = 'SELECT MBER_NM FROM CLUB_MBER; '; // 클럽회원

dbconn.query(sql1 + sql2, function(err, results, field){
  var sql1_result = results[0];	//sql1 의 결과값
  var sql2_result = results[1];	//sql2 의 결과값

	...
});

 

한 번에 여러 파라미터를 매핑해서 다중 쿼리를 처리하는 예제

//대회생성
insert : function(req, res){

  var cnfrncInfo = req.body.cnfrncInfo;	//대회정보
  var partcptMberArr = req.body.partcptMberArr; //대회참가자정보
  var cnfrncGgArr = req.body.cnfrncGgArr;	//대회경기정보

  var sql1 = 'INSERT INTO CNFRNC SET ? ;';
  var sql1s = mysql.format(sql1, cnfrncInfo); 

  var sql2 = 'INSERT INTO CNFRNC_PARTCPT_MBER SET ?;';
  var sql2s = "";
  partcptMberArr.forEach(function(item){
  	sql2s += mysql.format(sql2, item);
  });  

  var sql3 = 'INSERT INTO CNFRNC_GG SET ?;';
  var sql3s = "";
  cnfrncGgArr.forEach(function(item){
  	sql3s += mysql.format(sql3, item);
  });  

  dbconn.query(sql1s + sql2s + sql3s, function(err, result){

    if(err){
      console.error(err);
      res.send({resultCd:'E', msg: "예기치 않은 오류가 발생하여 대회 생성에 실패하였습니다."});
      throw err;
    }

    if(result[0].affectedRows > 0){
      res.send({resultCd:'S', msg:'정상적으로 대회가 만들어졌습니다.'});
      
    }else{
      console.error(result.message);
      res.send({resultCd:'E', msg: "예기치 않은 오류가 발생하여 대회 생성에 실패하였습니다. " + result.message});
    }

  });

}

 

 

혹시 다중 쿼리에 대해 좀 더 자세한 사항은 아래 링크를 참고하세요. 

https://github.com/mysqljs/mysql#multiple-statement-queries

mysqljs/mysql

 

 

다음강좌 

2019/04/02 - [Node.js] - [Node.js 9강] 외부서비스 API(네이버밴드) 적용하기