Awesome

mybatis-mapper

mybatis-mapper can generate SQL statements from the MyBatis3 Mapper XML file in node.js. <br> 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.<br>

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

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

1) Basic

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

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. <br>

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.<br> 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