ibatis-user-cs mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Daniel Miller" <daniel.mil...@corp.idt.net>
Subject RE: dynamic order by clause
Date Thu, 24 Aug 2006 15:12:48 GMT
Here's another approach I used recently. 

 

I wanted to avoid giving the GUI any knowledge of the backend i.e. the database field names
 So I use a class called "Filter," which defines the criteria according to which the data
are selected. This class has properties based on enums for SortType (the field or set of fields)
and SortDirection (ascending or descending).

 

    public class AliasListFilter {

        public enum SortTypes : int {

            ALIAS = 0,

            ACCOUNT_NAME = 1,

            ACCOUNT_ID = 2,

            LOCATION_TYPE = 3

        };

        public enum SortOrders : int {

            ASCENDING = 0,

            DESCENDING = 1

        };

 

        private SortTypes sortType = SortTypes.ALIAS;

        private SortOrders sortOrder = SortOrders.ASCENDING;

 

        public SortTypes SortType {

            get { return sortType; } set { sortType = value; }

        }

        public SortOrders SortOrder {

            get { return sortOrder; } set { sortOrder = value; }

        }

 

    }

 

Then I use this class as the parameter to the SqlMap:

 

 

    <select id="SelectAccountAliasesByFilter" parameterClass="AccountAliasFilter" resultMap="AccountAliasMap">

      SELECT

      ac.account_id,

      ac.name,

      al.alias,

      al.loc_def_field_lookup,

      al.update_uid,

      al.update_date

      FROM DEBIT.debit_account ac, DEBIT.debit_alias al

      WHERE ac.account_id = al.account_id

      <dynamic prepend="ORDER BY ">

        <isEqual property="SortType" compareValue="ALIAS">

          UPPER(al.alias)

          <isEqual property="SortOrder" compareValue="DESCENDING">DESC</isEqual>

        </isEqual>

        <isEqual property="SortType" compareValue="ACCOUNT_NAME">

          UPPER(ac.name)

          <isEqual property="SortOrder" compareValue="DESCENDING">DESC</isEqual>

        </isEqual>

        <isEqual property="SortType" compareValue="ACCOUNT_ID">

          UPPER(ac.account_id)

          <isEqual property="SortOrder" compareValue="DESCENDING">DESC</isEqual>

        </isEqual>

        <isEqual property="SortType" compareValue="LOCATION_TYPE">

          UPPER(al.loc_def_field_lookup)

          <isEqual property="SortOrder" compareValue="DESCENDING">DESC</isEqual>

        </isEqual>

      </dynamic>

    </select>

 

Advantages:

 

1.	Encapsulation of back-end details. GUI uses the filter class to determine sorting order
by Type
2.	Flexibility – often you don't want to sort by one field only. You may want to sort by
field A DESC and field B ASC or other weird combinations. It also allows you to specify table
prefixes in the case of complicated joins.

 

Hope this helps

 

Daniel Miller

 

  _____  

From: Gentile, Wayne (Hitachi Consulting) [mailto:wayne.gentile-eds@eds.com] 
Sent: Wednesday, August 23, 2006 9:55 PM
To: user-cs@ibatis.apache.org
Subject: RE: dynamic order by clause

 

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