是否可以在我的Batis“选择"中使用temp变量? [英] Is it possible to work with temp variable in my batis "select"?

问题描述

我现在有这个:

<select id="associations-short-sql" resultMap="associationInfoForListMap">
    <![CDATA[
        select
               a.id as d,
               a.name as name,
               a.somenum as number
        from
               associations a
    ]]>
    <if test="id!= null">
        where a.id = #{id}
    </if>
</select>

但是我需要添加过滤器功能.因此，如果uri查询具有参数名称，我想在查询中添加类似条件的名称. id和name都必须是可选的.因此，如果未在url查询中设置参数，则查询必须为:

But I need to add filter functionality. So if uri query has parameter name I want to add like conditional to the query. Both, id and name must be optional. So if params not set in url query, so query must be:

        select
               a.id as d,
               a.name as name
               a.somenum as number
        from
               associations a

如果设置了id，查询必须为:

If id is set query must be:

        select
               a.id as d,
               a.name as name
               a.somenum as number
        from
               associations a
        where a.id = #{id}

如果设置了名称，则查询必须为

If name is set query must be

        select
               a.id as d,
               a.name as name
               a.somenum as number
        from
               associations a
        where a.name like '%#{name}%'

如果名称和号码集查询必须为:

If name and number set query must be:

        select
               a.id as d,
               a.name as name
               a.somenum as number
        from
               associations a
        where a.name like '%#{name}%' and number like '%#{number}%'

但是，当设置了一些参数时，我必须保持选择语句顺序正确(我的意思是where和and部分).这是非常简单的示例，但是可能要复杂得多.对于参数name，我应该检查是否设置了number，对于参数编号，我应该检查是否设置了name.对于id，我应该检查是否同时设置了姓名和电话号码.如果我有10个参数％！@#！@，该怎么办? :)

But, when some params is set, I must to keep select-statement order correct (I mean where and and parts). This is rather easy examples, but it can be much more complex. For parameter name I should check if number is set, for parameter number I should check if name is set. For id, I should check if both name and number are set. What if I have 10 parameters %!@#!@? :)

是否可以在某些临时变量中存储where条件?保持正确的顺序会容易得多.谢谢.

Is it possible to store where condition in some temp variable? It will be much easier to keep correct order. Thank you.

推荐答案

至少有两个选择可以做到这一点.

There are at least two options to do that.

第一种选择是使用动态SQL ，并且适合许多case是内置的，不需要其他配置:

The first option is to use dynamic sql and suitable for many cases is built-in and doesn't require additional configuration:

<select id="associations-short-sql" resultMap="associationInfoForListMap">
  <![CDATA[
    select
           a.id as d,
           a.name as name,
           a.somenum as number
    from
           associations a
  ]]>
  <where>
    <if test="id != null">
      a.id = #{id}
    </if>
    <if test="id == null">
      <if test="name != null">
        AND a.name = #{name}
      </if>
      <if test="number != null">
        AND a.number = #{number}
      </if>
    </if>
  </where>
</select>

<where>元素处理where子句为空并且不将其插入到结果sql中时的情况.例如，如果仅通过name，它也会在开始时修剪多余的AND.

<where> element handles case when where clause is empty and doesn't insert it to result sql. It also trims extra AND at the beginning if you passed only name for example.

另一个选择是使用脚本. 速度引擎中的脚本更具表现力和功能.通常，符号更紧凑. 您的示例可能如下所示:

Another option is using scripting. Scripting in velocity engine is more expressive and powerful. Usually notation is more compact. Your example might look like:

<select id="associations-short-sql" resultMap="associationInfoForListMap">
  <![CDATA[
    select
           a.id as d,
           a.name as name,
           a.somenum as number
    from
           associations a
  ]]>
  #where()
    #if($_parameter.id)
      a.id = @{id}
    #else
      #if($_parameter.name)
        AND a.name = @{name}
      #end
      #if($_parameter.number)
        AND a.number = @{number}
      #end
    #end
  #end
</select>

另外，速度允许您具有变量，循环等.

Additionally velocity allows you to have variables, loops etc.

这篇关于是否可以在我的Batis“选择"中使用temp变量?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！