You're Invited:Meet the Socket Team at BlackHat and DEF CON in Las Vegas, Aug 7-8.RSVP
Socket
Socket
Sign inDemoInstall

mybatis-mapper

Package Overview
Dependencies
2
Maintainers
1
Versions
19
Alerts
File Explorer

Advanced tools

Install Socket

Detect and block malicious and high-risk dependencies

Install

mybatis-mapper

mybatis-mapper can generate SQL statements from the MyBatis3 Mapper XML file in node.js. You can use Dynamic SQL elements


Version published
Maintainers
1
Created

Changelog

Source

0.8.0

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

Readme

Source

mybatis-mapper

CircleCI

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

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

Keywords

FAQs

Package last updated on 25 Feb 2024

Did you know?

Socket

Socket for GitHub automatically highlights issues in each pull request and monitors the health of all your open source dependencies. Discover the contents of your packages and block harmful activity before you install or update your dependencies.

Install

Related posts

SocketSocket SOC 2 Logo

Product

  • Package Alerts
  • Integrations
  • Docs
  • Pricing
  • FAQ
  • Roadmap
  • Changelog

Packages

Stay in touch

Get open source security insights delivered straight into your inbox.


  • Terms
  • Privacy
  • Security

Made with ⚡️ by Socket Inc