mybatis-mapper

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

Downloads in past

Stats

StarsIssuesVersionUpdatedCreatedSize
mybatis-mapper
8060.8.02 months ago6 years agoMinified + gzip package size for mybatis-mapper in KB

Popular Searches

Readme

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 - 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 
SELECT
name,
category,
price
FROM
fruits 
WHERE
category = 'apple' AND
<![CDATA[ price < 500 ]]>
``` - 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 ####

```javascript 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 
SELECT
name,
category,
price
FROM
fruits 
WHERE
category = #{category}
AND price > ${price}
```

fruits.js ####

```javascript 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 ####

```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 
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>
```

fruits.js ####

```javascript 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 ####

```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 
SELECT
name,
category,
price
FROM
fruits 
<trim prefix="WHERE" prefixOverrides="AND|OR">
OR category = 'apple'
OR price = 200
</trim>
```

fruits.js ####

```javascript 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 ####

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

5) <where> element ###

fruits.xml ####

```xml 
SELECT
name,
category,
price
FROM
fruits 
<where>
AND category = 'apple'
<if test="price != null and price !=''">
AND price = ${price}
</if>
AND
</where>
```

fruits.js ####

```javascript 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 ####

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

6) <set> element ###

fruits.xml ####

```xml 
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}
```

fruits.js ####

```javascript 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 ####

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

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

fruits.xml ####

```xml 
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>
```

fruits.js ####

```javascript 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 ####

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

7) <foreach> element - Basic ###

fruits.xml ####

```xml 
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>
```

fruits.js ####

```javascript 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 ####

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

8) <foreach> element - Advanced ###

fruits.xml ####

```xml 
INSERT INTO
fruits
(
name,
category,
price      
)
VALUES
<foreach collection="fruits" item="fruit"  separator=",">
(
#{fruit.name},
#{fruit.category},
${fruit.price}
)
</foreach>
```

fruits.js ####

```javascript 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 ####

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

10) <bind> element ###

fruits.xml ####

```xml 
<bind name="likeName" value="'%' + name + '%'"/>
SELECT
name,
category,
price
FROM
fruits 
WHERE
name like #{likeName}
```

fruits.js ####

```javascript 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 ####

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

11) <include> element ###

fruits.xml ####

```xml 
fruits
WHERE
category = #{category}
FROM
<include refid="${include_target}"/>
<include refid="somewhere"/>
SELECT
name,
category,
price
<include refid="someinclude">
<property name="prefix" value="Some"/>
<property name="include_target" value="sometable"/>
</include>
```

fruits.js ####

```javascript 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 ####

```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.
  • Bug fix

0.3.0 ###

  • Support CDATA section
  • Bug fix & Error Handling

0.2.0 ###

  • 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