ibatismybatis

mybatis ordering by many fields with dynamic sql


Is there a cleaner way of doing the next :

<select id="getWithQueryData" resultMap="usuarioResult" parameterType="my.QueryData" >
select * from t_user 
 <if test="fieldName != null and ascDesc != null">
    order by
        <choose>  
            <when test="fieldName == 'name'">
                c_name 
            </when>         
            <when test="fieldName == 'lastName'">
                c_last_name 
            </when> 
            <when test="fieldName == 'email'">
                c_email 
            </when> 
            <when test="fieldName == 'password'">
                c_password 
            </when> 
            <when test="fieldName == 'age'">
                i_age
            </when>                                                             
        </choose>               
        <if test="ascDesc == 'asc'">
            asc 
        </if>            
        <if test="ascDesc == 'desc'">
            desc 
        </if>   
 </if>
limit #{limit} offset #{offset};

As you could infer, QueryData looks like:

public class FiltroBusquedaVO {

private Integer offset;
private Integer limit;
private String fieldName;
private String ascDesc; ... }

Would be nice if I could get a column name given a fieldName. I mean, the result maps have that info. But it seems I can not get it from the xml.

My example has just 5 fields, but what about 20 fields? Is there another way around of doing this less verbose?


Solution

  • See my answer on a similar problem.

    Mybatis map property to column

    But with that the only downside is you java code will know about the column names.