从mybatis3映射器xml文件生成sql语句
mybatis-mapper2sql的Python项目详细描述
mybatis-mapper2sql
从mybatis3映射器xml文件生成sql语句
仅用于SQL审查https://github.com/hhyo/archery/issues/3
安装
pip install mybatis-mapper2sql
用法
importmybatis_mapper2sql# Parse Mybatis Mapper XML filesmapper,xml_raw_text=mybatis_mapper2sql.create_mapper(xml='mybatis_mapper.xml')# Get All SQL Statements from Mapperstatement=mybatis_mapper2sql.get_statement(mapper)# Get SQL Statement By SQLIdstatement=mybatis_mapper2sql.get_child_statement(mapper,sql_id)
示例
测试.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"><mappernamespace="Test"><sqlid="sometable">
fruits
</sql><sqlid="somewhere">
WHERE
category = #{category}
</sql><sqlid="someinclude">
FROM
<includerefid="${include_target}"/><includerefid="somewhere"/></sql><selectid="testParameters">
SELECT
name,
category,
price
FROM
fruits
WHERE
category = #{category}
AND price > ${price}
</select><selectid="testInclude">
SELECT
name,
category,
price
<includerefid="someinclude"><propertyname="prefix"value="Some"/><propertyname="include_target"value="sometable"/></include></select><selectid="testIf">
SELECT
name,
category,
price
FROM
fruits
WHERE
1=1
<iftest="category != null and category !=''">
AND category = #{category}
</if><iftest="price != null and price !=''">
AND price = ${price}
<iftest="price >= 400">
AND name = 'Fuji'
</if></if></select><selectid="testTrim">
SELECT
name,
category,
price
FROM
fruits
<trimprefix="WHERE"prefixOverrides="AND|OR">
OR category = 'apple'
OR price = 200
</trim></select><selectid="testWhere">
SELECT
name,
category,
price
FROM
fruits
<where>
AND category = 'apple'
<iftest="price != null and price !=''">
AND price = ${price}
</if></where></select><updateid="testSet">
UPDATE
fruits
<set><iftest="category != null and category !=''">
category = #{category},
</if><iftest="price != null and price !=''">
price = ${price},
</if></set>
WHERE
name = #{name}
</update><selectid="testChoose">
SELECT
name,
category,
price
FROM
fruits
<where><choose><whentest="name != null">
AND name = #{name}
</when><whentest="category == 'banana'">
AND category = #{category}
<iftest="price != null and price !=''">
AND price = ${price}
</if></when><otherwise>
AND category = 'apple'
</otherwise></choose></where></select><selectid="testForeach">
SELECT
name,
category,
price
FROM
fruits
<where>
category = 'apple' AND
<foreachcollection="apples"item="name"open="("close=")"separator="OR"><iftest="name == 'Jonathan' or name == 'Fuji'">
name = #{name}
</if></foreach></where></select><insertid="testInsertMulti">
INSERT INTO
fruits
(
name,
category,
price
)
VALUES
<foreachcollection="fruits"item="fruit"separator=",">
(
#{fruit.name},
#{fruit.category},
${fruit.price}
)
</foreach></insert><selectid="testBind"><bindname="likeName"value="'%' + name + '%'"/>
SELECT
name,
category,
price
FROM
fruits
WHERE
name like #{likeName}
</select></mapper>
测试.py
从映射器获取所有SQL语句
importmybatis_mapper2sqlmapper,xml_raw_text=mybatis_mapper2sql.create_mapper(xml='test.xml')statement=mybatis_mapper2sql.get_statement(mapper,result_type='raw',reindent=True,strip_comments=True)print(statement)
SELECTname,category,priceFROMfruitsWHEREcategory=?ANDprice>?;SELECTname,category,priceFROMfruitsWHEREcategory=?;SELECTname,category,priceFROMfruitsWHERE1=1ANDcategory=?ANDprice=?ANDname='Fuji';SELECTname,category,priceFROMfruitsWHEREcategory='apple'ORprice=200;SELECTname,category,priceFROMfruitsWHEREcategory='apple'ANDprice=?;UPDATEfruitsSETcategory=?,price=?WHEREname=?;SELECTname,category,priceFROMfruitsWHEREname=?ANDcategory=?ANDprice=?ANDcategory='apple';SELECTname,category,priceFROMfruitsWHEREcategy='apple'AND(name=?ORname=?);INSERTINTOfruits(name,category,price)VALUES(?,?,?),(?,?,?);SELECTname,category,priceFROMfruitsWHEREnamelike?;
按sqlid获取sql语句
importmybatis_mapper2sqlmapper,xml_raw_text=mybatis_mapper2sql.create_mapper(xml='test.xml')statement=mybatis_mapper2sql.get_child_statement(mapper,'testForeach',reindent=True,strip_comments=False)print(statement)
SELECTname,category,priceFROMfruitsWHEREcategy='apple'AND(name=?-- if(name == 'Jonathan' or name == 'Fuji')ORname=?-- if(name == 'Jonathan' or name == 'Fuji'))
运行测试
python setup.py test
已知限制
- 不支持自定义参数
- 所有SQL参数都将替换为“？”
- 要在<；if>；<；choose>；<；when>；<；others>；元素中应用的所有条件
致谢
本项目的灵感来自以下项目和网站：
- https://github.com/OldBlackJoe/mybatis-mapper
- http://www.mybatis.org/mybatis-3/dynamic-sql.html
- http://www.enmoedu.com/article-205.html
欢迎加入QQ群-->： 979659372
