Skip to main content

mybatis-mapper2sql

Build Status codecov image image image

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

https://github.com/OldBlackJoe/mybatis-mapper

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:

Metadata

Release files for mybatis-mapper2sql 0.1.9

For a detailed explanation of source distributions (sdists) and built distributions (wheels), please see the package formats documentation.

Source distribution (sdist)

Source distribution for mybatis-mapper2sql 0.1.9
File Size Uploaded Provenance Actions
mybatis-mapper2sql-0.1.9.tar.gz 8.7 kB Without provenance Details for mybatis-mapper2sql-0.1.9.tar.gz

Release files / mybatis-mapper2sql-0.1.9.tar.gz

Download URL mybatis-mapper2sql-0.1.9.tar.gz
Size 8.7 kB
Tags Source
SHA-256 checksum
How to use checksums
18e3c0fd69f49a90932c7133abf333905b53e0a5affdd1a811a6af35bf4b3396
BLAKE2b-256 checksum
How to use checksums
da22015884abeea5e9c27e5b6c466d7a9a07f5a4b6fc117b74a75f65a8b02103
Upload date
Uploaded using Trusted Publishing?
What is trusted publishing?
No
Uploaded via twine/1.13.0 pkginfo/1.5.0.1 requests/2.21.0 setuptools/39.1.0 requests-toolbelt/0.9.1 tqdm/4.31.1 CPython/3.6.8

Security for mybatis-mapper2sql 0.1.9

Provenance

Provenance describes where a file came from. On PyPI, provenance is shared via attestations, which provide a verifiable record of the build or publishing details. View details, limitations and caveats.

No provenance found

None of the files in this release were uploaded with provenance metadata.

A note on package security

PyPI is a repository for distributing packages, not a service for auditing them. While third-party security researchers and our staff do their best to detect and remove malicious packages, PyPI cannot guarantee the quality, security or suitability of any given package.

To learn more about protecting yourself from malicious actors, visit our package security documentation.

Anthropic, PBC Visionary sponsor Bloomberg Visionary sponsor Hudson River Trading Visionary sponsor Meta Visionary sponsor NVIDIA Visionary sponsor Microsoft Sustainability sponsor Depot Continuous Integration AWS Cloud computing and Security Sponsor Datadog Monitoring Fastly CDN Google Download Analytics Sentry Error logging StatusPage Status page