CODEWITHSUNDEEP
javasqlibatis
saptarshi
saptarshi

Reputation: 97

Prepare ibatis query with dynamic alias

I need to create a query using ibatis which will some what look like this:

SELECT code,name,info0,info1,.... FROM TABLENAME;

info0,info1,.... could be any number and this I need to prepare dynamically.

code,name,info0,info1.... are all alias.

IBATIS QUERY

<select id="getReferenceDataListByReferenceMasterInfo"
    parameterType="com.util.ReferenceMetaData" resultMap="refdatalist-result">

    SELECT code,name,

    <if test="infoNameList != null">
        <foreach item="item" index="index" separator="," collection="infoNameList">
            #{'info'${index}}
        </foreach>
    </if>

    FROM
    (
        SELECT
        trim(${codeColumnName}) as code,

        <choose>
            <when test="nameColumnName != null">
                ${nameColumnName} as name,
            </when>
            <otherwise>
                null as name,
            </otherwise>
        </choose>
        <choose>
            <when test="infoNameList != null">
                <foreach item="item" index="index" collection="infoNameList">
                    ${item} as info${index},
                </foreach>
            </when>
        </choose>
        row_number() over (order by ${codeColumnName}) as ROWNUM

        FROM UREF.${tableName}

    )
</select>

The above query is working perfectly if we specify the alias explicitly as:

SELECT code,name,info0,info1,info2 FROM (...

Upvotes: 1

Views: 1570

Answers (1)

saptarshi
saptarshi

Reputation: 97

Got the answer.....

the code will look like this,

<if test="infoNameList != null">
    <foreach item="item" index="index" separator="," collection="infoNameList">
        info${index}
    </foreach>
</if>

Upvotes: 1

Related Questions