Generate SQL Statements from the MyBatis3 Mapper XML file
Project description
mybatis-mapper2sql
Generate SQL Statements from the MyBatis3 Mapper XML file
Just for SQL Review https://github.com/hhyo/archery/issues/3
Installation
pip install mybatis-mapper2sql
Usage
import mybatis_mapper2sql # Parse Mybatis Mapper XML files mapper, xml_raw_text = mybatis_mapper2sql.create_mapper(xml='mybatis_mapper.xml') # Get All SQL Statements from Mapper statement = mybatis_mapper2sql.get_statement(mapper) # Get SQL Statement By SQLId statement = mybatis_mapper2sql.get_child_statement(mapper, sql_id)
Examples
test.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="Test"> <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="testParameters"> SELECT name, category, price FROM fruits WHERE category = #{category} AND price > ${price} </select> <select id="testInclude"> SELECT name, category, price <include refid="someinclude"> <property name="prefix" value="Some"/> <property name="include_target" value="sometable"/> </include> </select> <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> <select id="testTrim"> SELECT name, category, price FROM fruits <trim prefix="WHERE" prefixOverrides="AND|OR"> OR category = 'apple' OR price = 200 </trim> </select> <select id="testWhere"> SELECT name, category, price FROM fruits <where> AND category = 'apple' <if test="price != null and price !=''"> AND price = ${price} </if> </where> </select> <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> <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> <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> <insert id="testInsertMulti"> INSERT INTO fruits ( name, category, price ) VALUES <foreach collection="fruits" item="fruit" separator=","> ( #{fruit.name}, #{fruit.category}, ${fruit.price} ) </foreach> </insert> <select id="testBind"> <bind name="likeName" value="'%' + name + '%'"/> SELECT name, category, price FROM fruits WHERE name like #{likeName} </select> </mapper>
test.py
Get All SQL Statements from Mapper
import mybatis_mapper2sql mapper, 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)
SELECT name, category, price FROM fruits WHERE category = ? AND price > ?; SELECT name, category, price FROM fruits WHERE category = ?; SELECT name, category, price FROM fruits WHERE 1=1 AND category = ? AND price = ? AND name = 'Fuji'; SELECT name, category, price FROM fruits WHERE category = 'apple' OR price = 200; SELECT name, category, price FROM fruits WHERE category = 'apple' AND price = ?; UPDATE fruits SET category = ?, price = ? WHERE name = ?; SELECT name, category, price FROM fruits WHERE name = ? AND category = ? AND price = ? AND category = 'apple'; SELECT name, category, price FROM fruits WHERE categy = 'apple' AND (name = ? OR name = ?); INSERT INTO fruits (name, category, price) VALUES (?, ?, ?) , (?, ?, ?); SELECT name, category, price FROM fruits WHERE name like ?;
Get SQL Statement By SQLId
import mybatis_mapper2sql mapper, 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)
SELECT name, category, price FROM fruits WHERE categy = 'apple' AND ( name = ? -- if(name == 'Jonathan' or name == 'Fuji') OR name = ? -- if(name == 'Jonathan' or name == 'Fuji') )
Running the tests
python setup.py test
Known Limitations
- Doesn't support custom parameters
- All sql parameters will be replace to '?'
- All of the conditionals to apply in <if> <choose> <when> <otherwise> element
Acknowledgments
This project was inspired by the following projects and websites:
Project details
Download files
Download the file for your platform. If you're not sure which to choose, learn more about installing packages.