对于相同类型的连接表,MyBatis多次包含相同的<sql>片段

Question

问

对于相同类型的连接表,MyBatis多次包含相同的<sql>片段

心碎的醉鬼发布于 2023-02-06 17:10

这是目前可能的(不确定自哪个版本):

定义它:


    ${alias}.id                 ${prefix}id,
    ${alias}.created_at         ${prefix}created_at,
    ${alias}.street_address     ${prefix}street_address,
    ${alias}.street_address_two ${prefix}street_address_two,
    ${alias}.city               ${prefix}city,
    ${alias}.country            ${prefix}country,
    ${alias}.region             ${prefix}region,
    ${alias}.sub_region         ${prefix}sub_region,
    ${alias}.postal_code        ${prefix}postal_code



选择它:


SELECT
purchase.*,

    
    
,

    
    

FROM purchase
LEFT JOIN address billing_address ON purchase.billing_address_id = billing_address.id
LEFT JOIN address shipping_address ON purchase.shipping_address_id = shipping_address.id




映射它:

    
    
    
    
        


        
          
        

      

    
  

  
  

    

      

        Bogdan..
         5
      
      
不幸的是你不能这样做,其他人已经尝试过了(在这里或这里看到一些问题).包含内联并且不带参数.

我头顶的一个解决方案是这样的:


  
    ${alias}.id                  ${alias}_person_id,
    ${alias}.created_at          ${alias}_person_created_at,
    ${alias}.email_address       ${alias}_person_email_address,
    ${alias}.first_name          ${alias}_person_first_name,
    ${alias}.last_name           ${alias}_person_last_name,
    ${alias}.middle_name         ${alias}_person_middle_name
  



包括它只是一次像:




并parameterType="java.util.List"从mapper界面发送:

public interface PersonMapper {
  public List getPersons(List aliases); 
  // called with aliases = ["per1", "per2"]
}


这很难看,因为你的(更高级别)代码必须知道(较低)查询中使用的别名,并且还使用字符串替换片段(${...}而不是#{...}),如果处理不当可能会有危险...但是如果你能和那个一起生活......



        
        2 个回答
        
                        
                不幸的是你不能这样做,其他人已经尝试过了(在这里或这里看到一些问题).包含内联并且不带参数.

我头顶的一个解决方案是这样的:

<sql id="fragment">
  <foreach collection="list" separator="," item="alias">
    ${alias}.id                  ${alias}_person_id,
    ${alias}.created_at          ${alias}_person_created_at,
    ${alias}.email_address       ${alias}_person_email_address,
    ${alias}.first_name          ${alias}_person_first_name,
    ${alias}.last_name           ${alias}_person_last_name,
    ${alias}.middle_name         ${alias}_person_middle_name
  </foreach>
</sql>


包括它只是一次像:

<select id="getPersons" parameterType="java.util.List" ... >
  SELECT
  <include refid="fragment"/>
  FROM Person per1
  JOIN Person per2 ON per2.parent_id = per1.id
</select>


并parameterType="java.util.List"从mapper界面发送:

public interface PersonMapper {
  public List<String> getPersons(List<String> aliases); 
  // called with aliases = ["per1", "per2"]
}


这很难看,因为你的(更高级别)代码必须知道(较低)查询中使用的别名,并且还使用字符串替换片段(${...}而不是#{...}),如果处理不当可能会有危险...但是如果你能和那个一起生活......

        

                
                    2023-02-06 17:13  回答
                       全球不懂我_934
                
            

                        
                这是目前可能的(不确定自哪个版本):

定义它:

<sql id="AddressFields">
    ${alias}.id                 ${prefix}id,
    ${alias}.created_at         ${prefix}created_at,
    ${alias}.street_address     ${prefix}street_address,
    ${alias}.street_address_two ${prefix}street_address_two,
    ${alias}.city               ${prefix}city,
    ${alias}.country            ${prefix}country,
    ${alias}.region             ${prefix}region,
    ${alias}.sub_region         ${prefix}sub_region,
    ${alias}.postal_code        ${prefix}postal_code
</sql>


选择它:

<sql id="PurchaseSelect">
SELECT
purchase.*,
<include refid="foo.bar.mapper.entity.AddressMapper.AddressFields">
    <property name="alias" value="billing_address"/>
    <property name="prefix" value="billing_address_"/>
</include>,
<include refid="foo.bar.mapper.entity.AddressMapper.AddressFields">
    <property name="alias" value="shipping_address"/>
    <property name="prefix" value="shipping_address_"/>
</include>
FROM purchase
LEFT JOIN address billing_address ON purchase.billing_address_id = billing_address.id
LEFT JOIN address shipping_address ON purchase.shipping_address_id = shipping_address.id

</sql>


映射它:

<resultMap id="PurchaseResult" type="foo.bar.entity.sales.Purchase">    
    <id property="id" column="id"/>
    <!-- any other purchase fields -->
    <association property="billingAddress" columnPrefix="billing_address_" resultMap="foo.bar.mapper.entity.AddressMapper.AddressResult"/>
    <association property="shippingAddress" columnPrefix="shipping_address_" resultMap="foo.bar.mapper.entity.AddressMapper.AddressResult"/>    
</resultMap>

        

                
                    2023-02-06 17:14  回答
                       xuzhaotong
                
            

                    

    

    
    
        撰写答案
        
            
                
                    回答问题...