You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user-cs@ibatis.apache.org by Stief Dirckx <St...@icasa-consulting.com> on 2006/08/21 08:45:14 UTC
dynamic order by clause
Hi
I'm having some questions about the dynamic sorting example in the iBatis
datamapper manual on page 51. What I want to do is is to pass an object to
an select statement but I also want to make the sorting dynamic (sort
field and sort type). But how can I pass these 2 extra parameters using
the QueryForList method?
This is what I have up to now:
<select id="SelectUser" parameterClass="User" resultMap="UserResult">
...
<dynamic prepend="ORDER BY ">
<isNotEmpty property="SortField">
ORDER BY $SortField$ $SortType$
</isNotEmpty>
</dynamic>
</select>
But with this solution I have to add 2 properties to my user class
(sortfield and sorttype). How can I avoid doing that?
Stief.
RE: dynamic order by clause
Posted by Daniel Miller <da...@corp.idt.net>.
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\)" <wa...@eds.com>
21/08/2006 16:01
Please respond to
user-cs@ibatis.apache.org
To
<us...@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>
_____
RE: dynamic order by clause
Posted by "Gentile, Wayne (Hitachi Consulting)" <wa...@eds.com>.
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\)" <wa...@eds.com>
21/08/2006 16:01
Please respond to
user-cs@ibatis.apache.org
To
<us...@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>
________________________________
RE: dynamic order by clause
Posted by Stief Dirckx <St...@icasa-consulting.com>.
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\)" <wa...@eds.com>
21/08/2006 16:01
Please respond to
user-cs@ibatis.apache.org
To
<us...@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>
RE: dynamic order by clause
Posted by "Gentile, Wayne (Hitachi Consulting)" <wa...@eds.com>.
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>
________________________________
From: Stief Dirckx [mailto:Stief.Dirckx@icasa-consulting.com]
Sent: Monday, August 21, 2006 1:45 AM
To: user-cs@ibatis.apache.org
Subject: dynamic order by clause
Hi
I'm having some questions about the dynamic sorting example in the
iBatis datamapper manual on page 51. What I want to do is is to pass an
object to an select statement but I also want to make the sorting
dynamic (sort field and sort type). But how can I pass these 2 extra
parameters using the QueryForList method?
This is what I have up to now:
<select id="SelectUser" parameterClass="User" resultMap="UserResult">
...
<dynamic prepend="ORDER BY ">
<isNotEmpty property="SortField">
ORDER BY $SortField$ $SortType$
</isNotEmpty>
</dynamic>
</select>
But with this solution I have to add 2 properties to my user class
(sortfield and sorttype). How can I avoid doing that?
Stief.