ibatis-user-cs mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Gentile, Wayne \(Hitachi Consulting\)" <wayne.gentile-...@eds.com>
Subject RE: dynamic order by clause
Date Wed, 23 Aug 2006 18:54:37 GMT
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:Stief.Dirckx@icasa-consulting.com] 
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\)" <wayne.gentile-eds@eds.com> 

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> 
  

 

________________________________

 


Mime
View raw message