重用动态SQL片段 [英] Reusing dynamic sql fragments
问题描述
在那儿,我正在开发Primefaces应用,作为持久层,我选择了 Mybatis .
Hei there, I'm working on a Primefaces app and as a persistence layer I chose Mybatis.
这是常规sql在我的映射器中的外观:
This is how a regular sql would look in my mapper:
<select id="getAllTransportUnit" resultMap="TransportUnitMap">
SELECT * FROM SSLS_GUI.VW_TU
<if test="( hasFilters == 'yes' ) and ( parameters != null )">
<where>
<foreach item="clause" collection="parameters" separator=" AND "
open="(" close=")">
UPPER(${clause.column}) ${clause.operator} #{clause.value}
</foreach>
</where>
</if>
<if test="sort == 'true'">
ORDER BY ${sortField}
<if test="sortOrder == 'DESC'"> DESC</if>
<if test="sortOder == 'ASC'"> ASC</if>
</if>
</select>
几乎所有我的查询都使用从<if test...>
开始的动态sql部分.是否可以将其放在单独的文件中,然后在我的所有查询中重复使用?
Almost all my queries use the dynamic sql part starting from the <if test...>
. Is it possible to put it in a separate file and then reuse it all over my queries?
推荐答案
有几种方法可以重用sql代码段.
There are several options how to reuse sql snippets.
第一个正在使用include
.创建单独的映射器Common.xml:
The first one is using include
. Create separate mapper Common.xml:
<mapper namespace="com.company.project.common">
<sql id="orderBy>
<if test="sort == 'true'">
ORDER BY ${sortField}
<if test="sortOrder == 'DESC'"> DESC</if>
<if test="sortOder == 'ASC'"> ASC</if>
</if>
</sql>
<sql id="filters">
<if test="( hasFilters == 'yes' ) and ( parameters != null )">
<where>
<foreach item="clause" collection="parameters" separator=" AND "
open="(" close=")">
UPPER(${clause.column}) ${clause.operator} #{clause.value}
</foreach>
</where>
</if>
</sql>
</mapper>
并在其他映射器MyMapper.xml
中使用它:
And the use it in other mappers MyMapper.xml
:
<select id="getAllTransportUnit" resultMap="TransportUnitMap">
SELECT * FROM SSLS_GUI.VW_TU
<include refid="com.company.project.common.filters"/>
<include refid="com.company.project.common.orderBy"/>
</select>
为避免每个副本中的名称空间重复,您可以在MyMapper.xml
中创建快捷方式片段:
To avoid duplicating namespace in every include you can create shortcut snippets in MyMapper.xml
:
<sql id="orderBy">
<include refid="com.company.project.common.orderBy"/>
</sql>
<select id="getAllTransportUnit" resultMap="TransportUnitMap">
SELECT * FROM SSLS_GUI.VW_TU
<include refid="orderBy"/>
</select>
Mybatis速度宏
另一个可能的选择是使用mybatis 脚本.使用mybatis-velocity脚本引擎,您可以定义速度宏并像这样重用它.
Mybatis-velocity macro
Another possible option is to use mybatis scripting. Using mybatis-velocity scripting engine you can define velocity macro and reusing it like this.
在Commons.xml
中:
<sql id="macros"
#macro(filters)
#if ( $_parameter.hasFilters )
#repeat( $_parameter.parameters $clause "AND" " (" ")" )
${clause.column} ${clause.operator} @{clause.value}
#end
#end
#end
#macro(order_by)
..
#end
</sql>
在MyMapper.xml
中:
<select id="getAllTransportUnit" resultMap="TransportUnitMap">
<include refid="macros"/>
SELECT * FROM SSLS_GUI.VW_TU
#filters()
#order_by()
</select>
通过sql片段包含宏不是重用宏的最干净方法.只是一个想法,如何使用.
Including macros via sql snippet is not the most clean way to reuse macros. It is just an idea how this is used.
更好的选择是配置mybatis-velocity并指定可用的全局宏.在这种情况下,无需包含macros
代码段,结果查询将如下所示:
Much better option is to configure mybatis-velocity and specify what global macros are available. In this case there will be no need to do include of macros
snippet and result query will be like this:
<select id="getAllTransportUnit" resultMap="TransportUnitMap">
SELECT * FROM SSLS_GUI.VW_TU
#filters()
#order_by()
</select>
这篇关于重用动态SQL片段的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!