Improvement for column selection and column aliases

classic Classic list List threaded Threaded
1 message Options
Reply | Threaded
Open this post in threaded view
|

Improvement for column selection and column aliases

Bertrand Guay-Paquet
Hello,

I often need an <sql> fragment which lists column names to be used with
or without a column prefix and with or without a table alias. Here is an
example of this situation:

# [1] Message result map
<resultMap id="message" type="Message">
     <id property="id" column="id" />
     <result property="body" column="body" />
     <collection property="attachments" ofType="Attachment"
         resultMap="attachment" columnPrefix="att_" />
</resultMap>

# [2] Attachment result map
<resultMap id="attachment" type="Attachment">
     <id property="id" column="id" />
     <result property="filename" column="filename" />
     <result property="messageId" column="message_id" />
</resultMap>

# [3] Message attachment columns with "att_" prefix
<sql id="attachmentColumnsAssociation">
     ${alias}.id as att_id, ${alias}.filename as att_filename,
     ${alias}.message_id as att_message_id
</sql>

# [4] Select query for messages with attachments
<select id="selectMessage" resultMap="message">
     SELECT M.id, M.body,
         <include refid="attachmentColumnsAssociation">
             <property name="alias" value="A"/>
         </include>
     FROM messages M
     LEFT JOIN attachments A ON M.id = A.message_id
     WHERE M.id = #{id}
</select>

# [5] Select query for message attachment only
# Note: cannot reuse sql fragment [3] as-is. Could be reused
# if it had a second parameter for column prefix.
<select id="selectAttachment" resultMap="attachment">
     SELECT id, filename, message_id
     FROM attachments M
     WHERE id = #{id}
</select>

I propose adding a new element which would be specialized for column
name lists. This would make what is probably the biggest usage of <sql>
elements much more straightforward.

The new elements could be <columnList> and <columns>, the latter having
the following properties:
-ref: id of the referenced <columnList>
-alias: the name of the table alias to use. Empty for none.
-prefix: the column prefix as used in the result maps

Some usage examples:
<columnList id="colList">id, body</columnList>

<select id="...">SELECT <columns id="colList"> FROM MyTable</select>
=> SELECT id, body FROM MyTable

<select id="...">SELECT <columns id="colList" alias="T">
FROM MyTable AS T</select>
=> SELECT T.id, T.body FROM MyTable AS T

<select id="...">SELECT <columns id="colList" alias="T" prefix="att_">
FROM MyTable AS T</select>
=> SELECT T.id AS att_id, T.body AS att_body FROM MyTable AS T

What do you think? Would this be useful to anyone else?

Regards,
Bertrand

--
You received this message because you are subscribed to the Google Groups "mybatis-user" group.
To unsubscribe from this group and stop receiving emails from it, send an email to [hidden email].
For more options, visit https://groups.google.com/d/optout.