RE: dynamic order by clause

[Gentile, Wayne \(Hitachi Consulting\)]

Here is an example of how I did it with multiple sort fields:   (SortCriterion has 2 properties: Column and Order)   DAO code:         /// <summary>         /// Gets a list of CrossReference objects for the given table name.         /// The list will be sorted according to the specified sort criteria.         /// </summary>         /// <param name="tableName">The name of the CrossReference table</param>         /// <param name="sortCriteria">         /// A list of SortCriterion specifying how the list should be sorted         /// </param>         /// <returns>         /// A list of CrossReference objects for the specified table         /// </returns>         public IList<CrossReference> GetCrossReferencesByTableName(             string tableName,             IList<SortCriterion> sortCriteria)         {             // Remove criteria duplicates             if (sortCriteria != null)             {                 IDictionary<string, SortCriterion> criteriaMap =                     new Dictionary<string, SortCriterion>(sortCriteria.Count);                 foreach (SortCriterion criterion in sortCriteria)                 {                     if (!criteriaMap.ContainsKey(criterion.Column))                     {                         criteriaMap.Add(criterion.Column, criterion);                     }                 }                   sortCriteria = new List<SortCriterion>(criteriaMap.Values);             }               IDictionary args = new Hashtable();             args.Add("tableName", tableName);   // the keys will be used as the names in the mapping             args.Add("SortCriteria", sortCriteria);               IList<CrossReference> crossReferenceList =                 SqlMapper.QueryForList<CrossReference>(                     "CrossReference.SelectWithSort", args);             return crossReferenceList;         }     Mapping: [some names removed]       <select id="Select"       resultMap="CrossReference.Result"       parameterClass="string"       cacheModel="CrossReferenceCache">         SELECT A, B, C         FROM             Xra.Xra$tableName$Xref     </select>       <select id="SelectWithSort"             resultMap="CrossReference.Result"             parameterClass="map" <!-- parameter class must be a map so that the table name can be used in the map above -->             extends="Select"             cacheModel="CrossReferenceCache">       <isNotNull property="SortCriteria">         <iterate property="SortCriteria" conjunction="," prepend=" ORDER BY ">           $SortCriteria[].Column$ $SortCriteria[].Order$         </iterate>       </isNotNull>            </select>   Does this make sense?   You only need a map if you have other parameters to pass in besides the sort criteria, such as a value in the where clause.   From: Stief Dirckx [mailto:[EMAIL PROTECTED] Sent: Wednesday, August 23, 2006 6:13 AM To: user-cs@ibatis.apache.org Subject: RE: dynamic order by clause   Hmmm, I feel like a real rookie now. 1. You're talking about a parameterClass not a parameterMap? Is this map an hashtable or something then? 2. So If I want to sort on multiple fields I have to make a sortcriteria class with 2 properties (sortfield and sorttype) and than make an ilist with multiple sort expressions? How does this work with the first step? Hope you can give some more information. "Gentile, Wayne \(Hitachi Consulting\)" <[EMAIL PROTECTED]> 21/08/2006 16:01 Please respond to user-cs@ibatis.apache.org To <user-cs@ibatis.apache.org> cc   Subject RE: dynamic order by clause   You have a couple of options.   1.        Pass a map as the parameterClass and add your User object, SortField, and SortType to the map before calling the statement. The key names of the map will match the names in the map. 2.        Create a SortCriterion class with a SortField and SortType property and pass multiple instances using a map (if you want to sort by multiple fields).   The map then looks like this:   <select id="SelectUser" parameterClass="map" resultMap="UserResult">    ...    <dynamic prepend="ORDER BY ">        <isNotEmpty property="SortField">            ORDER BY $SortField$ $SortType$        </isNotEmpty>    </dynamic> </select>