# mybatis-mapper-myyrakle

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

Latest version **0.8.0** (published 2021-03-15) · Apache-2.0 license · 0 weekly downloads

## Install

```sh
npm install mybatis-mapper-myyrakle
pnpm add mybatis-mapper-myyrakle
yarn add mybatis-mapper-myyrakle
bun add mybatis-mapper-myyrakle
```

## Health

**Score 25/100 (F)** — status: abandoned.

Positive: has types; no vulnerabilities; high quality score.

Warnings: low downloads; no esm support; pre 1.0.

Negative: abandoned; low maintenance score.

## Facts

| | |
|---|---|
| Version | 0.8.0 |
| Published | 2021-03-15 |
| First published | 2020-06-16 |
| Weekly downloads | 0 |
| License | Apache-2.0 |
| TypeScript types | bundled |
| Module format | CommonJS |
| Dependencies | 2 |
| Unpacked size | 101.9 KB |
| Known vulnerabilities | 0 (+1 in 1 direct dependencies) |
| Install scripts | no |
| GitHub stars | 1 |
| Author | myyrakle |
| Maintainers | myyrakle |
| Keywords | node.js, mybatis, mysql, postgresql |

## Links

- npm: https://www.npmjs.com/package/mybatis-mapper-myyrakle
- Repository: https://github.com/myyrakle/mybatis-mapper
- Homepage: https://github.com/myyrakle/mybatis-mapper#readme
- Issues: https://github.com/myyrakle/mybatis-mapper/issues
- npm.io page: https://npm.io/package/mybatis-mapper-myyrakle

## Dependencies (2)

- [sql-formatter](https://npm.io/package/sql-formatter.md) 2.3.2
- [html-parse-stringify2](https://npm.io/package/html-parse-stringify2.md) 2.0.1

## Recent versions

- 0.8.0 (latest) — 2021-03-15
- 0.7.0 — 2020-08-24
- 0.6.7 — 2020-06-16
- 0.6.6 — 2020-06-16
- 0.6.5 — 2020-06-16

## README

# mybatis-mapper

[![CircleCI](https://circleci.com/gh/OldBlackJoe/mybatis-mapper.svg?style=svg)](https://circleci.com/gh/OldBlackJoe/mybatis-mapper)

This module is managed separately by fork from OldBlackJoe's [mybatis-mapper](https://www.npmjs.com/package/mybatis-mapper).
Unlike the original version, which is only for mysql, this version is postgresql compatible.

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, &lt;if&gt;, &lt;where&gt;, &lt;foreach&gt;.

**Table of contents**

-   [Installation](#installation)
-   [Usage](#usage)
-   [Change Log](#change-log)

## Installation

```
npm install --save mybatis-mapper-myyrakle
```

## Usage

mybatis-mapper supports all of dynamic SQL elements.<br>

-   &lt;if&gt;
-   &lt;choose&gt;, &lt;when&gt;, &lt;otherwise&gt;
-   &lt;trim&gt;, &lt;where&gt;, &lt;set&gt;
-   &lt;foreach&gt;
-   &lt;bind&gt;
-   &lt;include&gt;

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

```javascript
const mysql = require("mysql2");
const mybatisMapper = require("mybatis-mapper-myyrakle");

// 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
<?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

```javascript
var mybatisMapper = require("mybatis-mapper-myyrakle");
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) &lt;if&gt; element

#### fruits.xml

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

```javascript
var mybatisMapper = require("mybatis-mapper-myyrakle");
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, &lt;if&gt;&lt;if&gt;&lt;/if&gt;&lt;/if&gt;

### 4) &lt;trim&gt; element

#### fruits.xml

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

```javascript
var mybatisMapper = require("mybatis-mapper-myyrakle");
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) &lt;where&gt; element

#### fruits.xml

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

```javascript
var mybatisMapper = require("mybatis-mapper-myyrakle");
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) &lt;set&gt; element

#### fruits.xml

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

```javascript
var mybatisMapper = require("mybatis-mapper-myyrakle");
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) &lt;choose&gt; &lt;when&gt; &lt;otherwise&gt; element

#### fruits.xml

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

```javascript
var mybatisMapper = require("mybatis-mapper-myyrakle");
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) &lt;foreach&gt; element - Basic

#### fruits.xml

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

```javascript
var mybatisMapper = require("mybatis-mapper-myyrakle");
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) &lt;foreach&gt; element - Advanced

#### fruits.xml

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

```javascript
var mybatisMapper = require("mybatis-mapper-myyrakle");
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) &lt;bind&gt; element

#### fruits.xml

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

```javascript
var mybatisMapper = require("mybatis-mapper-myyrakle");
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) &lt;include&gt; element

#### fruits.xml

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

```javascript
var mybatisMapper = require("mybatis-mapper-myyrakle");
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.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 &lt;foreach&gt; element.

### 0.6.1

-   Improved parameter conversion logic.
-   Bug fix for &lt;trim&gt; &lt;where&gt; elements.

### 0.6.0

-   Added typings for use with TypeScript.

### 0.5.3

-   Hot fix for &lt;include&gt; element.

### 0.5.2

-   Error Handling

### 0.5.1

-   Hot fix for &lt;foreach&gt; element.

### 0.5.0

-   Support &lt;include&gt; element.
-   Do not formatting SQL when 'format' parameter is null
-   Bug fix

### 0.4.0

-   Support &lt;set&gt; element.
-   Support &lt;bind&gt; element.
-   SQL formatting using [sql-formatter](https://www.npmjs.com/package/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](https://www.npmjs.com/package/html-parse-stringify2).
-   Dynamic SQL elements can use repeatedly. for example, &lt;if&gt;&lt;if&gt;&lt;/if&gt;&lt;/if&gt;
-   Support &lt;choose&gt; &lt;when&gt; &lt;otherwise&gt; element.
-   Support &lt;trim&gt; element.

### 0.1.0

-   Initial Version

---
_Source: https://npm.io/package/mybatis-mapper-myyrakle · Machine-readable twin of the npm.io package page. Health data is recomputed on every publish._