oldblackjoe/mybatis-mapper

generate SQL statements from the MyBatis3 Mapper XML file in Node.js

mybatis-mapper

mybatis-mapper can generate SQL statements from the MyBatis3 Mapper XML file in node.js.
You can also use Dynamic SQL elements, for example, <if>, <where>, <foreach>.

Table of contents

• Installation
• Usage
• Change Log

Installation

npm install --save mybatis-mapper

Usage

mybatis-mapper supports all of dynamic SQL elements.

• <if>
• <choose>, <when>, <otherwise>
• <trim>, <where>, <set>
• <foreach>
• <bind>
• <include>

You can see description of Dynamic SQL of MyBatis3 in the link below.
http://www.mybatis.org/mybatis-3/dynamic-sql.html

1) Basic

First, prepare XML file(s) written in MyBatis3 syntax like below.

fruits.xml

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="fruit">  
  <select id="testBasic">
    SELECT
      name,
      category,
      price
    FROM
      fruits 
    WHERE
      category = 'apple' AND
      <![CDATA[ price < 500 ]]>
  </select>
</mapper>

• The XML file must have one 'mapper' element, which must have the 'namespace' attribute.
• mybatis-mapper recognizes and parses the 'select', 'insert', 'update', and 'delete' elements in the 'mapper' element as SQL statements.
• You can use CDATA section in xml for well-formed XML.
• other attributes are ignored.

Second, writing Node.js codes.

fruits.js

const mysql = require('mysql2');
const mybatisMapper = require('mybatis-mapper');

// create the connection to database
const connection = mysql.createConnection({
  host: 'localhost',
  user: 'root',
  database: 'test'
});

// create the myBatisMapper from xml file
mybatisMapper.createMapper([ './fruits.xml' ]);

// SQL Parameters
var param = {
    category : 'apple',
    price : 100
}

// Get SQL Statement
var format = {language: 'sql', indent: '  '};
var query = mybatisMapper.getStatement('fruit', 'testBasic', param, format);

// Do it!
connection.query(query, function(err, results, fields) {
  console.log(results); 
  console.log(fields);
});

createMapper( [XML Files] )

• This method takes Array of XML files as a arguments.
• Reads and parses the specified xml file to prepare the SQL statements.

getStatement(Namespace, SqlID, Parameters, format)

• This method takes Namespace, SQL ID, and Parameters as a arguments.
• Create SQL statement from XML using Parameters and return it.
• You can use this SQL string for Node.js MySQL Clients like mysql2.
• "format" argument is Optional, it can set the format of the SQL language and indent.
  For more information, see https://www.npmjs.com/package/sql-formatter

2) Parameters ( #{...}, ${...} )

fruits.xml

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="fruit">  
  <select id="testParameters">
    SELECT
      name,
      category,
      price
    FROM
      fruits 
    WHERE
      category = #{category}
      AND price > ${price}
  </select>
</mapper>

fruits.js

var mybatisMapper = require('mybatis-mapper');
mybatisMapper.createMapper([ './fruits.xml' ]);
var param = {
    category : 'apple',
    price : 100
}
    
var query = mybatisMapper.getStatement('fruit', 'testParameters', param, {language: 'sql', indent: '  '});
console.log(query);

result SQL

SELECT
  name,
  category,
  price
FROM
  fruits
WHERE
  category = 'apple'
  AND price > 100

• As in the example above, if a variable is enclosed in #{ }, the variable is wrapped in quotation marks.
• The other side, if the variable is enclosed in ${ }, the variable is converted as it is.
• In general, you can use #{ } for a String variable, and ${ } for a numeric value.

3) <if> element

fruits.xml

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="fruit">  
  <select id="testIf">
    SELECT
      name,
      category,
      price
    FROM
      fruits 
    WHERE
      1=1
      <if test="category != null and category !=''">
        AND category = #{category}
      </if>
      <if test="price != null and price !=''">
        AND price = ${price}
        <if test="price >= 400">
          AND name = 'Fuji'
        </if>
      </if>
  </select>
</mapper>

fruits.js

var mybatisMapper = require('mybatis-mapper');
mybatisMapper.createMapper([ './fruits.xml' ]);
var param = {
    category : 'apple',
    price : 500
}

var query = mybatisMapper.getStatement('fruit', 'testIf', param, {language: 'sql', indent: '  '});
console.log(query);

result SQL

SELECT
  name,
  category,
  price
FROM
  fruits
WHERE
  1 = 1
  AND category = 'apple'
  AND price = 500
  AND name = 'Fuji'

• You can use dynamic SQL elements repeatedly. for example, <if><if></if></if>

4) <trim> element

fruits.xml

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="fruit">  
  <select id="testTrim">
    SELECT
      name,
      category,
      price
    FROM
      fruits 
    <trim prefix="WHERE" prefixOverrides="AND|OR">
        OR category = 'apple'
        OR price = 200
    </trim>
  </select>
</mapper>

fruits.js

var mybatisMapper = require('mybatis-mapper');
mybatisMapper.createMapper([ './fruits.xml' ]);
var param = null;

var query = mybatisMapper.getStatement('fruit', 'testTrim', param, {language: 'sql', indent: '  '});
console.log(query);

result SQL

SELECT
  name,
  category,
  price
FROM
  fruits
WHERE
  category = 'apple'
  OR price = 200

5) <where> element

fruits.xml

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="fruit">  
  <select id="testWhere">
    SELECT
      name,
      category,
      price
    FROM
      fruits 
    <where>
        AND category = 'apple'
        <if test="price != null and price !=''">
          AND price = ${price}
        </if>
        AND
    </where>
  </select>
</mapper>

fruits.js

var mybatisMapper = require('mybatis-mapper');
mybatisMapper.createMapper([ './fruits.xml' ]);
var param = {
    price : 500
}

var query = mybatisMapper.getStatement('fruit', 'testWhere', param, {language: 'sql', indent: '  '});
console.log(query);

result SQL

SELECT
  name,
  category,
  price
FROM
  fruits
WHERE
  category = 'apple'
  AND price = 500

6) <set> element

fruits.xml

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="fruit">  
  <update id="testSet">
    UPDATE
      fruits
    <set>
      <if test="category != null and category !=''">
        category = #{category},
      </if>
      <if test="price != null and price !=''">
        price = ${price},    
      </if>
    </set>
    WHERE
      name = #{name}
  </update>
</mapper>

fruits.js

var mybatisMapper = require('mybatis-mapper');
mybatisMapper.createMapper([ './fruits.xml' ]);
var param = {
    name : 'Fuji',
    category : 'apple',
    price : 300          
}

var query = mybatisMapper.getStatement('fruit', 'testSet', param, {language: 'sql', indent: '  '});
console.log(query);

result SQL

UPDATE
  fruits
SET
  category = 'apple',
  price = 300
WHERE
  name = 'Fuji'

6) <choose> <when> <otherwise> element

fruits.xml

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="fruit">  
  <select id="testChoose">
    SELECT
      name,
      category,
      price
    FROM
      fruits 
    <where>
      <choose>
        <when test="name != null">
          AND name = #{name}
        </when>
        <when test="category == 'banana'">
          AND category = #{category}
          <if test="price != null and price !=''">
            AND price = ${price}          
          </if>
        </when>
        <otherwise>
          AND category = 'apple'
        </otherwise>
      </choose>
    </where>
  </select>
</mapper>

fruits.js

var mybatisMapper = require('mybatis-mapper');
mybatisMapper.createMapper([ './fruits.xml' ]);
var param = {
    name : null,
    category : 'banana',
    price : 300
}

var query = mybatisMapper.getStatement('fruit', 'testChoose', param, {language: 'sql', indent: '  '});
console.log(query);

result SQL

SELECT
  name,
  category,
  price
FROM
  fruits
WHERE
  category = 'banana'
  AND price = 300

7) <foreach> element - Basic

fruits.xml

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="fruit">  
  <select id="testForeach">
    SELECT
      name,
      category,
      price
    FROM
      fruits 
    <where>
      category = 'apple' AND
      <foreach collection="apples" item="name"  open="(" close=")" separator="OR">
        <if test="name == 'Jonathan' or name == 'Fuji'">
          name = #{name}
        </if>        
      </foreach>
    </where>
  </select>
</mapper>

fruits.js

var mybatisMapper = require('mybatis-mapper');
mybatisMapper.createMapper([ './fruits.xml' ]);
var param = {
    apples : [ 'Jonathan', 'Mcintosh', 'Fuji' ]        
}

var query = mybatisMapper.getStatement('fruit', 'testForeach', param, {language: 'sql', indent: '  '});
console.log(query);

result SQL

SELECT
  name,
  category,
  price
FROM
  fruits
WHERE
  category = 'apple'
  AND (
    name = 'Jonathan'
    OR name = 'Fuji'
  )

8) <foreach> element - Advanced

fruits.xml

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="fruit">  
  <insert id="testInsertMulti">
    INSERT INTO
      fruits
    (
      name,
      category,
      price      
    )
    VALUES
    <foreach collection="fruits" item="fruit"  separator=",">
    (
      #{fruit.name},
      #{fruit.category},
      ${fruit.price}
    )
    </foreach>
  </insert>
</mapper>

fruits.js

var mybatisMapper = require('mybatis-mapper');
mybatisMapper.createMapper([ './fruits.xml' ]);
var param = {
  fruits : [
    {
      name : 'Jonathan',
      category : 'apple',
      price : 100        
    },
    {
      name : 'Mcintosh',
      category : 'apple',
      price : 500
    }
  ]
}
var query = mybatisMapper.getStatement('fruit', 'testInsertMulti', param, {language: 'sql', indent: '  '});
console.log(query);

result SQL

INSERT INTO
  fruits (
    name,
    category,
    price
  )
VALUES
  (
    'Jonathan',
    'apple',
    100
  ),
  (
    'Mcintosh',
    'apple',
    500
  )

10) <bind> element

fruits.xml

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="fruit">  
  <select id="testBind">
    <bind name="likeName" value="'%' + name + '%'"/>
      SELECT
        name,
        category,
        price
      FROM
        fruits 
      WHERE
        name like #{likeName}
  </select>
</mapper>

fruits.js

var mybatisMapper = require('mybatis-mapper');
mybatisMapper.createMapper([ './fruits.xml' ]);
var param = {
  name : 'Mc'
}

var query = mybatisMapper.getStatement('fruit', 'testBind', param, {language: 'sql', indent: '  '});
console.log(query);

result SQL

SELECT
  name,
  category,
  price
FROM
  fruits
WHERE
  name like '%Mc%'

11) <include> element

fruits.xml

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="fruit">  
  <sql id="sometable">
    fruits
  </sql>
  
  <sql id="somewhere">
    WHERE
      category = #{category}
  </sql>
  
  <sql id="someinclude">
    FROM
      <include refid="${include_target}"/>
    <include refid="somewhere"/>
  </sql>
  
  <select id="testInclude">
    SELECT
      name,
      category,
      price
    <include refid="someinclude">
      <property name="prefix" value="Some"/>
      <property name="include_target" value="sometable"/>
    </include>
  </select>
</mapper>

fruits.js

var mybatisMapper = require('mybatis-mapper');
mybatisMapper.createMapper([ './fruits.xml' ]);
var param = {
    category : 'apple'
}

var query = mybatisMapper.getStatement('fruit', 'testInclude', param, {language: 'sql', indent: '  '});
console.log(query);

result SQL

SELECT
  name,
  category,
  price
FROM
  fruits
WHERE
  category = 'apple'

Change Log

0.8.0

• Fix match with sql-formatter's placeholder types
• Fix function scoping of findMapper & replaceCdata
• Add equalsIgnoreCase replacer

0.7.1

• create namespace if only not exists

0.7.0

• Escape param key for sql string
• Add suffix feature for TRIM
• Fix trim suffix may be empty and param may have underscore
• Solve grave accent issue

0.6.8

• Use escape dollar sign when using replace method

0.6.7

• Fix query with an apostrophe results in an error

0.6.6

• Update dependencies for fix issue #13

0.6.5

• Fix Unexpected end of input error

0.6.4

• Fix JSON data type parsing (arrays/objects)

0.6.3

• Fix bug that Null parameter was not converted.

0.6.2

• Hot fix for <foreach> element.

0.6.1

• Improved parameter conversion logic.
• Bug fix for <trim> <where> elements.

0.6.0

• Added typings for use with TypeScript.

0.5.3

• Hot fix for <include> element.

0.5.2

• Error Handling

0.5.1

• Hot fix for <foreach> element.

0.5.0

• Support <include> element.
• Do not formatting SQL when 'format' parameter is null
• Bug fix

0.4.0

• Support <set> element.
• Support <bind> element.
• SQL formatting using sql-formatter.
• Bug fix

0.3.0

• Support CDATA section
• Bug fix & Error Handling

0.2.0

• Change XML parsing library xml2js to html-parse-stringify2.
• Dynamic SQL elements can use repeatedly. for example, <if><if></if></if>
• Support <choose> <when> <otherwise> element.
• Support <trim> element.

0.1.0

• Initial Version

mybatis-mapper's Issues

use foreach

  <sql id="63a16fa3372bf76f801cce0d">
    INSERT INTO 
fs (dasd,sad,dsa) VALUES

(#{item.dasd},#{item.sad},#{item.dsa} )

error: Error occurred during convert element.

Initialise namespace if not exist

Namespace is overrided if there is two files with same namespace
Is it possible to add this condition before initialise namespace ?

here is the source code below :

=> file and line

if(!myBatisMapper[children.attrs.namespace]) {
      myBatisMapper[children.attrs.namespace] = {};
}

Could you please permit the pull-request.

Thank you

Query with an apostrophe results in an error

Trying to query with an apostrophe is throwing an error: SQL0010N The string constant beginning with "'))" does not have an ending string delimiter. SQLSTATE=42603

Setup:
getStatement('test', 'statement', {'name': "broken'string"}, { language: 'db2', indent: ' ' })
Mapper:
<mapper namespace="test"> <select id="statement"> SELECT * FROM TABLE t WHERE t.property > 0 <if test="name != null> AND UPPER(t.name) LIKE UPPER(#{name}) </if> </select> </mapper>

mybatis-mapper/lib/convert.js

Line 105 in f15d173

tempParamKey = tempParamKey.replace(/'/g, '\\\'');

Changing this from '\\\'' to "''" is fixing the issue above and is the preferred way to escape the apostrophe.
Note - same behaviour if we switch language to sql
http://www.michael-thomas.com/tech/db2/db2_survival_guide.htm

My SQL statement was truncated.

Thank you very much for bringing us such a powerful library.
Recently, I encountered a issues while using it.

My version is as follows：
"version": "0.6.8"

My xml is like this:

 <select id="queryOrder">
    SELECT COUNT(*) AS orderNo FROM installation WHERE updateTime <= (
	    SELECT updateTime FROM installation WHERE deviceId = #{deviceId} AND signId = ${signId}
    ) AND signId = ${signId}
  </select>

const sql2 = mybatisMapper.getStatement('installation_mapper', 'queryOrder', param2, sqlFormat)
logger.debug(sql2)
The output of the log is as follows：

SELECT
COUNT(*) AS orderNo
FROM
installation
WHERE
updateTime

From the above results, it can be seen that the SQL statement has been truncated, I found that the problem occurred at <=.
If I change <= to =, then the output will be normal.

Could you please help to check what the problem is?

Is not jdbcType supported?

Is not jdbcType supported?

What if you want to enter a null value?

include does not work inside where tag

Hi!

I have an issue with <include refid="..."/> tag (at least I think so).
I have store.xml with includable access section so I can use it in many queries.

store.xml

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="store"> 
    <sql id="access">
        <if test="userRole == 'partner'">
            AND s.brand_id IN (
              SELECT id
              FROM brands
              WHERE company_id = #{companyId}
            )
        </if>
        <if test="userRole == 'agent'">
            AND s.agent_id = #{userId}
        </if>
        <if test="userRole == 'contractor' or userRole == 'technician'">
            AND s.id IN (
              SELECT DISTINCT store_id
              FROM requests
              WHERE contractor_company_id = #{companyId}
            )
        </if>
    </sql>

    <select id="getList">
        SELECT s.*

        <if test="withBrand">, br.name AS brand_name</if>
        <if test="withBuilding">, bu.name AS building_name</if>
        <if test="withCity">, c.id AS city_id, c.name AS city_name</if>

        FROM stores s

        INNER JOIN brands br ON br.id=s.brand_id
        INNER JOIN buildings bu ON bu.id=s.building_id
        INNER JOIN cities c ON c.id=bu.city_id

        <where>
            <include refid="access"/>
        </where>

        ORDER BY c.name, bu.name, br.name
    </select>
</mapper>

My store.js looks like this one:

store.js

var mybatisMapper = require('mybatis-mapper');
mybatisMapper.createMapper([ './store.xml' ]);
var param = {
    userId : 16,
    userRole : 'partner',
    companyId : 10
}

var query = mybatisMapper.getStatement('store', 'getList', param, {language: 'sql', indent: '  '});
console.log(query);

And when I firing the script I getting the following error:

Error: Error occurred during convert <where> element.
    at convertTrimWhere (/Users/ovsyannikov/projects/stores/node_modules/mybatis-mapper/lib/convert.js:269:11)
    at Object.convertChildren (/Users/ovsyannikov/projects/stores/node_modules/mybatis-mapper/lib/convert.js:23:16)
    at MybatisMapper.getStatement (/Users/ovsyannikov/projects/stores/node_modules/mybatis-mapper/index.js:93:28)

But if I move the access sql part from include directrly to where section - it works!

store.xml

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="store"> 
    <select id="getList">
        SELECT s.*

        <if test="withBrand">, br.name AS brand_name</if>
        <if test="withBuilding">, bu.name AS building_name</if>
        <if test="withCity">, c.id AS city_id, c.name AS city_name</if>

        FROM stores s

        INNER JOIN brands br ON br.id=s.brand_id
        INNER JOIN buildings bu ON bu.id=s.building_id
        INNER JOIN cities c ON c.id=bu.city_id

        <where>
            <if test="userRole == 'partner'">
                AND s.brand_id IN (
                SELECT id
                FROM brands
                WHERE company_id = #{companyId}
                )
            </if>
            <if test="userRole == 'agent'">
                AND s.agent_id = #{userId}
            </if>
            <if test="userRole == 'contractor' or userRole == 'technician'">
                AND s.id IN (
                SELECT DISTINCT store_id
                FROM requests
                WHERE contractor_company_id = #{companyId}
                )
            </if>
        </where>

        ORDER BY c.name, bu.name, br.name
    </select>
</mapper>

Then it produces sql as I expected:

SELECT
  s.*
FROM
  stores s
  INNER JOIN brands br ON br.id = s.brand_id
  INNER JOIN buildings bu ON bu.id = s.building_id
  INNER JOIN cities c ON c.id = bu.city_id
WHERE
  s.brand_id IN (
    SELECT
      id
    FROM
      brands
    WHERE
      company_id = '16'
  )
ORDER BY
  c.name,
  bu.name,
  br.name

I am trying to migrate my Spring Framework Application (+MyBatis) to nodejs stack using your library and I believe that all xml are valid because they work pretty well with Javas Mybatis.

Please help me find a way =D

Are there any plans to support postgresql?

i need it...

Dollar sign is not replaced properly

Prerequisites

Javascript has some replacement patterns(related to the dollar sign) when the replacement value in String.replace function is a string(https://developer.mozilla.org/en-US/docs/Web/JavaScript/Reference/Global_Objects/String/replace#specifying_a_string_as_a_parameter)

Issue

In our case when we try to search something with a dollar sign it is not substituted properly. Below screenshot is for reference of that behaviour.

Solution

Using a replacement value as a function rather than a string(https://developer.mozilla.org/en-US/docs/Web/JavaScript/Reference/Global_Objects/String/replace#specifying_a_function_as_a_parameter) because those replacement patterns won't be applied in that case

Can you add a suffix in TRIM tag?

some tools generate mybatis template sql like this:
So I need to add suffix and suffixOverrides in TRIM tag, I already changed code in my machine, and it run smoothly.
can you update this code?

var convertTrimWhere = function(children, param, namespace, myBatisMapper) {  
  var convertString = '';
  var prefix = null;
  var prefixOverrides = null;
  var suffix = null;
  var suffixOverrides = null;
  var globalSet = null;
  
  try{
    switch (children.name.toLowerCase()) {
    case 'trim':
      prefix = children.attrs.prefix;
      prefixOverrides = children.attrs.prefixOverrides;
      suffix = children.attrs.suffix;
      suffixOverrides = children.attrs.suffixOverrides;
      globalSet = 'g';
      break;
    case 'where':    
      prefix = 'WHERE';
      prefixOverrides = 'and|or';
      globalSet = 'gi';
      break;
    default:
      throw new Error("Error occurred during convert <trim/where> element.");
    }
    
    // Convert children first.
    for (var j=0, nextChildren; nextChildren=children.children[j]; j++){
      convertString += convertChildren(nextChildren, param, namespace, myBatisMapper);
    }
    
    // Remove prefixOverrides
    var trimRegex = new RegExp('(^)([\\s]*?)(' + prefixOverrides + ')', globalSet);
    convertString = convertString.replace(trimRegex, '');
    // Remove suffixOverrides
    trimRegex = new RegExp('(' + suffixOverrides + ')([\\s]*?)($)', globalSet);
    convertString = convertString.replace(trimRegex, '');
	
    if (children.name.toLowerCase() != 'trim'){
      var trimRegex = new RegExp('(' + prefixOverrides + ')([\\s]*?)($)', globalSet);
      convertString = convertString.replace(trimRegex, '');
    } 
    
    // Add Prefix if String is not empty.
    var trimRegex = new RegExp('([a-zA-Z])', 'g');
    var w = convertString.match(trimRegex);
  
    if (w != null && w.length > 0) {
      convertString = prefix + ' '+ convertString + ' ' + suffix;
    }
    
    // Remove comma(,) before WHERE
    if (children.name.toLowerCase() != 'where'){
      var regex = new RegExp('(,)([\\s]*?)(where)', 'gi');
      convertString = convertString.replace(regex, ' WHERE ');
    }
    
    return convertString;
  } catch (err) {
    throw new Error("Error occurred during convert <" + children.name.toLowerCase() + "> element.");
  }
}

about single quote escaping

hi
@OldBlackJoe

mybatis-mapper/lib/convert.js

Lines 96 to 99 in 48942ab

	tempParamKey = mysqlRealEscapeParam(tempParamKey);
	}
	tempParamKey = tempParamKey.replace(/'/g, "''");

ex)

tempParamKey = `인천광역시 서구 (가정동, 루원시티 SK Leaders' VIEW)`

result

'인천광역시 서구 (가정동, 루원시티 SK Leaders\'' VIEW)'

i want result

'인천광역시 서구 (가정동, 루원시티 SK Leaders\' VIEW)'
or
'인천광역시 서구 (가정동, 루원시티 SK Leaders'' VIEW)'

mysqlRealEscapeParam

Is it mandatory to use it?

or not

Can I leave out line 99?

Please confirm.

ubuntu mapper error

Hi,

First of all, I appreciate for your library.
It is really helpful for my project.

I found an error while using your library on node.js + express + ubuntu(aws lightsail) environment.

SyntaxError: Invalid regular expression: /^([\p{Alphabetic}\p{Mark}\p{Decimal_Number}\p{Connector_Punctuation}\p{Join_Control}]+)/: Invalid escape
    at new RegExp (<anonymous>)
    at Object.createWordRegex (/home/minieyes85/node_modules/sql-formatter/lib/core/regexFactory.js:38:10)
    at new Tokenizer (/home/minieyes85/node_modules/sql-formatter/lib/core/Tokenizer.js:74:36)
    at StandardSqlFormatter.tokenizer (/home/minieyes85/node_modules/sql-formatter/lib/languages/StandardSqlFormatter.js:57:14)
    at StandardSqlFormatter.format (/home/minieyes85/node_modules/sql-formatter/lib/core/Formatter.js:87:26)
    at Object.format (/home/minieyes85/node_modules/sql-formatter/lib/sqlFormatter.js:74:29)
    at MybatisMapper.getStatement (/home/minieyes85/node_modules/mybatis-mapper/index.js:109:32)
    at router.get (/home/minieyes85/routes/main.js:51:28)
    at Layer.handle [as handle_request] (/home/minieyes85/node_modules/express/lib/router/layer.js:95:5)
    at next (/home/minieyes85/node_modules/express/lib/router/route.js:144:13)

This error occured on

let format = {language:'sql', indent: ' ']
let query = mapper.getStatement("processMapper", "selectReport", param, format);

I found to solve this problem as delete format
like below

let query = mapper.getStatement("processMapper", "selectReport", param);

This problem is not occuring on vscode + window environment.

please check this error.

Thank you

Error occured during open XML file

eg: mybatisMapper.createMapper(['./mapper/users.xml']); is not work
`/node_modules/mybatis-mapper/index.js:20
throw new Error("Error occured during open XML file [" + xml + "]");
^

Error: Error occured during open XML file [./mapper/users.xml]`

You might need update your demo, xml is not found.
fixed：
var path = require('path');
mybatisMapper.createMapper([path.resolve(__dirname, './mapper/users.xml')]);

about single quote escaping

mybatis-mapper/lib/convert.js

Line 105 in f15d173

tempParamKey = tempParamKey.replace(/'/g, '\\\'');

I think replacing '\'' with '''' is more standard and better.

question mark error

Hi
I use mybatis-mapper to input only 1 question mark and save it, an SQL error occurs.
When I input 2 question marks and save it, the data is saved as '[Object Object]'.

Can you support mybatis resultMap?

There is a problem with parameter matching with a special name and list.

There is a problem with parameter matching with a special name and list.

I wrote it to execute a multi-query.

The same where statement was used twice.

The condition has some text and some list.

after excute foreach, some parameter(suffix is 'name') have disappeared.

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="test">  
    <sql id="test1">
        <if test="item_name!=''">
        and item_name = #{item_name}
        </if>
        <foreach collection="main_name" item="name"  open="and (" close=")" index="index" separator="or">
        main_name like CONCAT('%', #{name}, '%')
        </foreach>
        <if test="item_name!=''">
        and item_name = #{item_name}
        </if>
    </sql>

    <sql id="test2">
        <if test="category!=''">
        and category = #{category}
        </if>
        <foreach collection="main_name" item="name"  open="and (" close=")" index="index" separator="or">
        main_name like CONCAT('%', #{name}, '%')
        </foreach>
        <if test="category!=''">
        and category = #{category}
        </if>
    </sql>

    <sql id="test3">
        <if test="cate_name!=''">
        and cate_name = #{cate_name}
        </if>
        <foreach collection="main_na" item="name"  open="and (" close=")" index="index" separator="or">
        main_name like CONCAT('%', #{name}, '%')
        </foreach>
        <if test="cate_name!=''">
        and cate_name = #{cate_name}
        </if>
    </sql>
</mapper>

const mybatisMapper = require('mybatis-mapper');
mybatisMapper.createMapper(['./config/test.xml']);

var format = { language: 'sql' };

var param = {
    item_name: 'a',
    main_name: [ 'b', 'c' ],
}

var param2 = {
    category: 'a',
    main_name: [ 'b', 'c' ],
}

var param3 = {
    cate_name: 'a',
    main_na: [ 'b', 'c' ],
}


var sql1 = mybatisMapper.getStatement('test', 'test1', param, format);
var sql2 = mybatisMapper.getStatement('test', 'test2', param2, format);
var sql3 = mybatisMapper.getStatement('test', 'test3', param3, format);

console.log(sql1)
console.log('--------------------------------------')
console.log(sql2)
console.log('--------------------------------------')
console.log(sql3)

result is

and item_name = 'a'
and (
  main_name like CONCAT('%', 'b', '%')
  or main_name like CONCAT('%', 'c', '%')
)
--------------------------------------
and category = 'a'
and (
  main_name like CONCAT('%', 'b', '%')
  or main_name like CONCAT('%', 'c', '%')
)
and category = 'a'
--------------------------------------
and cate_name = 'a'
and (
  main_name like CONCAT('%', 'b', '%')
  or main_name like CONCAT('%', 'c', '%')
)

Parameters are missing in first and third result.
Parameter(name ends with 'name') is missing....

Only the second result is a success.

I think that "if test" has some bug.
If remove the "if test" in id=test1 (like below), return the collect result.

<sql id="test1">
and item_name = #{item_name}
<foreach collection="main_name" item="name"  open="and (" close=")" index="index" separator="or">
main_name like CONCAT('%', #{name}, '%')
</foreach>
and item_name = #{item_name}	
</sql>	

If 0 is used for parameters, no value is stored in <foreach> tag

I put array in parameters with 0 value.

code.js

var parameters = {
  IDs: [0, 1, 2, 3]
};
mybatisMapper.getStatement(Namespace, SqlID, Parameters, format)

and use <foreach> in xml.

sql.xml

<mapper namespace="test">
  <select id="test_1">
    select
    from
      user
    where
    in
    (
      <foreach collection="IDs" item="ID" separator=",">
      #{ID}
      </foreach>
    )
  </select>
</mapper>

but no ID is stored in <foreach>.

Result

select
from
  user
where
in
(



)

@mybatis-mapper/lib/convert.js, line 138~147

var convertForeach = function (children, param, namespace, myBatisMapper) {
  try{
    var collection = eval('param.' + children.attrs.collection);
    var item = children.attrs.item;
    var open = (children.attrs.open == null)? '' : children.attrs.open;
    var close = (children.attrs.close == null)? '' : children.attrs.close;
    var separator = (children.attrs.separator == null)? '' : children.attrs.separator;
    
    var foreachTexts = [];
    for (var j=0, coll; coll=collection[j]; j++){

line 140 : collection = [0, 1, 2, 3] is assigned.
but in javascript, collection[0] = 0 is false.
so for-loop are failed in line 147, and no value is stored <foreach>.

This means that if the parameter array contains 0, the index in the for statement will be false and the loop will be terminated.

Error in SET Syntax for Variable Assignment

v0.7 has some error.

this is the part of XML

...

set @out_uuid = '0';

...

node_modules\mybatis-mapper\index.js:112
throw err
^

Error: Parse error: Unexpected "@out_uuid " at line 2 column 13
at iR.createParseError (\node_modules\sql-formatter\dist\sql-formatter.min.cjs:1:191496)
at iR.tokenize (\node_modules\sql-formatter\dist\sql-formatter.min.cjs:1:191282)
at YR.tokenize (\node_modules\sql-formatter\dist\sql-formatter.min.cjs:1:192672)
at JR.tokenize (\node_modules\sql-formatter\dist\sql-formatter.min.cjs:1:225108)
at JR.reset (\node_modules\sql-formatter\dist\sql-formatter.min.cjs:1:197640)
at T.I.feed (\node_modules\sql-formatter\dist\sql-formatter.min.cjs:1:4085)
at Object.parse (\node_modules\sql-formatter\dist\sql-formatter.min.cjs:1:225263)
at PA.parse (\node_modules\sql-formatter\dist\sql-formatter.min.cjs:1:225458)
at PA.format (\node_modules\sql-formatter\dist\sql-formatter.min.cjs:1:224971)
at rA (\node_modules\sql-formatter\dist\sql-formatter.min.cjs:1:229759)

please add the "mysql" in Format language parameter

Hi guys.
I found a small bug while using your module.
If a query include '!=' and invoke getStatement with Format paramet (language "sql") , it's transpiled to "! =".
So I met the syntax error because of '! ='. it should be '!=' (the space between ! and = should be removed) in mysql.
I traced what happen if I pass "sql" in language parameter. and the sql formatter module in dependency list transpiled it like that.
and I figure out that it's transpiled well if I pass "mysql" in langauge parameter by sql formatter.
Simply, this bug will be gone if you guys put "mysql" keyword in language parameter whitelist.
Thanks.

Error in recognizing inequality as "tag".

Hi, I am a user using Mybartis.
An unexpected error occurred while using "Mybatis" and found the following errors.

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
</mapper>
<mapper namespace="errorexample">
<select id="errorSql">
SELECT VNS_IDX
FROM
TB_VNS_IBIZ_NUMBER
WHERE
VNS_NUMBER <= #{vnsNumber} and VNS_NUMBER >= #{vnsNumber} 
</select>
</mapper>

In the above sql statement, when inequality ex) "<", ">" is used, there is an error in recognizing it as tag.

The code above is an example.

Later, as a result of looking a little further, it was predicted that the error occurred in "html-parse-stringfy2" used by "Mybatis."

select return data available in lowercase?

return data is upper case, but is it possible in lower case?