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
npm install --save mybatis-mapper
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
First, prepare XML file(s) written in MyBatis3 syntax like below.
<?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.
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);
});
- This method takes Array of XML files as a arguments.
- Reads and parses the specified xml file to prepare the SQL statements.
- 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
<?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>
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);
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.
<?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>
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);
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>
<?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>
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);
SELECT
name,
category,
price
FROM
fruits
WHERE
category = 'apple'
OR price = 200
<?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>
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);
SELECT
name,
category,
price
FROM
fruits
WHERE
category = 'apple'
AND price = 500
<?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>
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);
UPDATE
fruits
SET
category = 'apple',
price = 300
WHERE
name = 'Fuji'
<?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>
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);
SELECT
name,
category,
price
FROM
fruits
WHERE
category = 'banana'
AND price = 300
<?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>
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);
SELECT
name,
category,
price
FROM
fruits
WHERE
category = 'apple'
AND (
name = 'Jonathan'
OR name = 'Fuji'
)
<?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>
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);
INSERT INTO
fruits (
name,
category,
price
)
VALUES
(
'Jonathan',
'apple',
100
),
(
'Mcintosh',
'apple',
500
)
<?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>
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);
SELECT
name,
category,
price
FROM
fruits
WHERE
name like '%Mc%'
<?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>
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);
SELECT
name,
category,
price
FROM
fruits
WHERE
category = 'apple'
- create namespace if only not exists
- 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
- Use escape dollar sign when using replace method
- Fix query with an apostrophe results in an error
- Update dependencies for fix issue #13
- Fix Unexpected end of input error
- Fix JSON data type parsing (arrays/objects)
- Fix bug that Null parameter was not converted.
- Hot fix for <foreach> element.
- Improved parameter conversion logic.
- Bug fix for <trim> <where> elements.
- Added typings for use with TypeScript.
- Hot fix for <include> element.
- Error Handling
- Hot fix for <foreach> element.
- Support <include> element.
- Do not formatting SQL when 'format' parameter is null
- Bug fix
- Support <set> element.
- Support <bind> element.
- SQL formatting using sql-formatter.
- Bug fix
- Support CDATA section
- Bug fix & Error Handling
- 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.
- Initial Version