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 smartkid <sk...@gmail.com> on 2008/01/16 17:22:25 UTC
答复: Selecting a key value pair
Quote from MSDN:
============================================================================
==============
public class Dictionary<TKey,TValue> : IDictionary<TKey,TValue>,
ICollection<KeyValuePair<TKey,TValue>>,
IEnumerable<KeyValuePair<TKey,TValue>>, IDictionary, ICollection,
IEnumerable,
ISerializable, IDeserializationCallback
============================================================================
==============
The Dictionary<> class does not implement the IList interface.
May be you can convert your Dictionary<String,String> to an
List<KeyValuePair<String,String>> before calling " UpdateTestLCID":
List<KeyValuePair<String,String>> list = new
List(testObject.Names); //each item in the list is an instance of
KeyValuePair<String,String>
SqlMapper.Update("UpdateTestLCID", list);
But I DON’T think this will solve your business requirement.
Quote from your previous mail:
============================================================================
==============
<select id="SelectDescLCID" parameterClass="string" resultmap="?????">
SELECT LCID as Key, Descr as Value
FROM ITEMDESC
WHERE itemId=#value#
</select>
============================================================================
==============
I assume that each LC pair is stored as a database record.
So the batch of update statements generated by IBatis does not handle those
new or deleted LC pairs.
As the record size in the "ITEMDESC" table is so small, may be you can
delete all existing records for an "ItemId" and let the IBatis generate a
batch of INSERT statements instead.
-----邮件原件-----
发件人: Andrea Tassinari [mailto:andreaml@i-mconsulting.com]
发送时间: 2008年1月16日 23:16
收件人: user-cs@ibatis.apache.org
主题: Re: Selecting a key value pair
At 09.34 11/01/2008, Andrea Tassinari wrote:
>At 17.04 10/01/2008, smartkid wrote:
>
>>Use "System.Collections.IDictionary" as the resultClass for
"SelectDescLCID"
>
>Nahhh, is that so simple and straightforward? I cannot believe it! I should
have tried this before. Let me give it a chance :-)
>
>thanks.
Ok, it is *not* so striaghtforward. Current SVN version of the datamapper
exposes a new set of QueryforDictionary<T,V> methods that help a lot getting
a Dictionary<string,string> from a mapped statement.
So, in order to get an object with a Dictionary<string,string> property the
best solution I found is to run 2 query statements. RIP for the lazy
loading... it is acceptable.
Now I'm facing the reverse problem: unroll the dictionary into an update
statement. It would be better to unroll the dictionary within a mapped sql
statement in the SqlMap file, somethig like this (Names is my
Dictionary<string,string> property for the class Test)
<update id="UpdateTestLCID" parameterClass="Test">
<iterate conjunction=";" open="" close="" property="Names">
UPDATE testLCID
SET NOME='$Names[].Key$'
WHERE refid=1
AND LCID='$Names[].Value$'
</iterate>
</update>
this does not work, the mapper complains about Names is not a list or an
array (which, I understand, it is true);
any idea?
Thanks
Andrea
public sealed class Test
{
private int? _id;
private IDictionary<string,string> _names;
public Test()
{
_id = null;
_names = new Dictionary<string,string>();
}
public int? Id
{
get { return _id; }
set { _id = value; }
}
public IDictionary<string,string> Names
{
get { return _nomi; }
set { _nomi = value; }
}
}
>Andrea
>
>
>
>--
>No virus found in this incoming message.
>Checked by AVG Free Edition.
>Version: 7.5.516 / Virus Database: 269.19.0/1218 - Release Date: 1/10/2008
1:32 PM
>
>
>
>
>--
>No virus found in this incoming message.
>Checked by AVG Free Edition.
>Version: 7.5.516 / Virus Database: 269.19.0/1218 - Release Date: 10/01/2008
13.32
答复: Selecting a key value pair
Posted by smartkid <sk...@gmail.com>.
Another way is to fetch the list from the database records and compare with
the list in memory.
The comparison gives us three list:
A list in both memory and db, update them.
A list in memory only, insert them.
A list in db only, delete them.
The comparison method is something like:
============================================================================
=====
/// <summary>Calculates the difference of two list.</summary>
/// <typeparam name="T">The element type.</typeparam>
/// <param name="a">The first list.</param>
/// <param name="b">The second list.</param>
/// <param name="comparsion">The comparsion for deteminating if an
item in <paramref name="b"/> is the same as an item in <paramref name="a"/>.
Typically compare two items with their primary key values.</param>
/// <param name="aAsBoth">If <c>true</c>, the item in <paramref
name="a"/> will be copied to <paramref name="inBoth"/>
/// when identical item in <paramref name="b"/> is found,
/// otherwise the item in <paramref name="b"/> will be
copied.</param>
/// <param name="inBoth">A list of identical items in both <paramref
name="a"/> and <paramref name="b"/>.</param>
/// <param name="onlyInA">A list of items in <paramref name="a"/>
without identical item in <paramref name="b"/>.</param>
/// <param name="onlyInB">A list of items in <paramref name="b"/>
without identical item in <paramref name="a"/>.</param>
public static void Diff<T>(out List<T> inBoth, out List<T> onlyInA,
out List<T> onlyInB,
IList<T> a, IList<T> b, bool aAsBoth, Comparison<T> comparsion)
{
if (a == null) throw new ArgumentNullException("a");
if (b == null) throw new ArgumentNullException("b");
onlyInA = new List<T>(a);
onlyInB = new List<T>(b);
inBoth = new List<T>();
//Removes the identical items in both listA and B
for (int ia = onlyInA.Count - 1; ia >= 0; ia--) {
T itemA = onlyInA[ia];
//finds corresponding item in list b
int ib = onlyInB.FindIndex(delegate(T itemB) { return
comparsion(itemA, itemB) == 0; });
if (ib >= 0) {
inBoth.Add(aAsBoth ? itemA : onlyInB[ib]);
onlyInA.RemoveAt(ia);
onlyInB.RemoveAt(ib);
}
}
}
============================================================================
======
And a typicall calling example is: ( ISnippet has child snippets in a tree
manner , FindChildSnippets returns a list of direct child snippets)
============================================================================
======
public void Update(ISnippet obj) {
if (obj == null) throw new ArgumentNullException("obj");
SqlMapper.Update("SnippetMap.Update", obj);
if (obj.ChildSnippets != null) {
IList<ISnippet> dbChildren =
FindChildSnippets(obj.ID); //Child snippets exist in the
database
List<ISnippet> toAdd, toDelete, toUpdate;
CollectionUtils.Diff(out toUpdate, out toAdd, out
toDelete,
obj.ChildSnippets, dbChildren, true,
delegate(ISnippet a, ISnippet b) { return (a.ID ==
b.ID) ? 0 : -1; });
toAdd.ForEach(delegate(ISnippet snippet) { Add(snippet);
});
toDelete.ForEach(delegate(ISnippet snippet) {
Remove(snippet); });
toUpdate.ForEach(delegate(ISnippet snippet) {
Update(snippet); });
}
tx.Complete();
}
============================================================================
======
Due to the business requirement originally posted by Andrea Tassinari, store
each localized string as a database record is too fine granulated.
May be better to store LCID pairs for the same locale as an xml in one SQL
Server's Text/XML field~~~
Or use a disk file store the localized strings per locale,
or a .NET resource assembly if they do not change after the software be
deployed.
-----邮件原件-----
发件人: Norman Katz [mailto:normkatz@cox.net]
发送时间: 2008年1月17日 0:34
收件人: user-cs@ibatis.apache.org; smrtk@hotmail.com
主题: RE: Selecting a key value pair
In these key/value pair instances when you have an "attribute" table in your
database, I prefer to create an object with an additional "active" boolean
field and set/clear this in the business logic before going to persistence.
The field does not actually exist in the database table. On Select from the
DB, I set this flag to true for all current records in the table. In the
business logic, I set the flag to true for all inserts and set it to false
for all (pending) deletes. For updates, you leave it alone.
I also implement a stored procedure that checks for existence of the pair
and adds it if it does not exist and active = 1, updates it if it exists and
active = 1 or deletes it if it exists and active = 0. Then, you call the SP
in your mapper update tag and pass it the list of these objects. A tiny bit
of business logic in this type of SP simplifies a lot of things if you're
willing to go that route.
Norm
-----Original Message-----
From: smartkid [mailto:skyaoj@gmail.com]
Sent: Wednesday, January 16, 2008 8:22 AM
To: user-cs@ibatis.apache.org
Subject: 答复: Selecting a key value pair
Quote from MSDN:
============================================================================
==============
public class Dictionary<TKey,TValue> : IDictionary<TKey,TValue>,
ICollection<KeyValuePair<TKey,TValue>>,
IEnumerable<KeyValuePair<TKey,TValue>>, IDictionary, ICollection,
IEnumerable,
ISerializable, IDeserializationCallback
============================================================================
==============
The Dictionary<> class does not implement the IList interface.
May be you can convert your Dictionary<String,String> to an
List<KeyValuePair<String,String>> before calling " UpdateTestLCID":
List<KeyValuePair<String,String>> list = new
List(testObject.Names); //each item in the list is an instance of
KeyValuePair<String,String>
SqlMapper.Update("UpdateTestLCID", list);
But I DON’T think this will solve your business requirement.
Quote from your previous mail:
============================================================================
==============
<select id="SelectDescLCID" parameterClass="string" resultmap="?????">
SELECT LCID as Key, Descr as Value
FROM ITEMDESC
WHERE itemId=#value#
</select>
============================================================================
==============
I assume that each LC pair is stored as a database record.
So the batch of update statements generated by IBatis does not handle those
new or deleted LC pairs.
As the record size in the "ITEMDESC" table is so small, may be you can
delete all existing records for an "ItemId" and let the IBatis generate a
batch of INSERT statements instead.
-----邮件原件-----
发件人: Andrea Tassinari [mailto:andreaml@i-mconsulting.com]
发送时间: 2008年1月16日 23:16
收件人: user-cs@ibatis.apache.org
主题: Re: Selecting a key value pair
At 09.34 11/01/2008, Andrea Tassinari wrote:
>At 17.04 10/01/2008, smartkid wrote:
>
>>Use "System.Collections.IDictionary" as the resultClass for
"SelectDescLCID"
>
>Nahhh, is that so simple and straightforward? I cannot believe it! I should
have tried this before. Let me give it a chance :-)
>
>thanks.
Ok, it is *not* so striaghtforward. Current SVN version of the datamapper
exposes a new set of QueryforDictionary<T,V> methods that help a lot getting
a Dictionary<string,string> from a mapped statement.
So, in order to get an object with a Dictionary<string,string> property the
best solution I found is to run 2 query statements. RIP for the lazy
loading... it is acceptable.
Now I'm facing the reverse problem: unroll the dictionary into an update
statement. It would be better to unroll the dictionary within a mapped sql
statement in the SqlMap file, somethig like this (Names is my
Dictionary<string,string> property for the class Test)
<update id="UpdateTestLCID" parameterClass="Test">
<iterate conjunction=";" open="" close="" property="Names">
UPDATE testLCID
SET NOME='$Names[].Key$'
WHERE refid=1
AND LCID='$Names[].Value$'
</iterate>
</update>
this does not work, the mapper complains about Names is not a list or an
array (which, I understand, it is true);
any idea?
Thanks
Andrea
public sealed class Test
{
private int? _id;
private IDictionary<string,string> _names;
public Test()
{
_id = null;
_names = new Dictionary<string,string>();
}
public int? Id
{
get { return _id; }
set { _id = value; }
}
public IDictionary<string,string> Names
{
get { return _nomi; }
set { _nomi = value; }
}
}
>Andrea
>
>
>
>--
>No virus found in this incoming message.
>Checked by AVG Free Edition.
>Version: 7.5.516 / Virus Database: 269.19.0/1218 - Release Date: 1/10/2008
1:32 PM
>
>
>
>
>--
>No virus found in this incoming message.
>Checked by AVG Free Edition.
>Version: 7.5.516 / Virus Database: 269.19.0/1218 - Release Date: 10/01/2008
13.32
__________ NOD32 2795 (20080116) Information __________
This message was checked by NOD32 antivirus system.
http://www.eset.com
RE: Selecting a key value pair
Posted by Norman Katz <no...@cox.net>.
In these key/value pair instances when you have an "attribute" table in your
database, I prefer to create an object with an additional "active" boolean
field and set/clear this in the business logic before going to persistence.
The field does not actually exist in the database table. On Select from the
DB, I set this flag to true for all current records in the table. In the
business logic, I set the flag to true for all inserts and set it to false
for all (pending) deletes. For updates, you leave it alone.
I also implement a stored procedure that checks for existence of the pair
and adds it if it does not exist and active = 1, updates it if it exists and
active = 1 or deletes it if it exists and active = 0. Then, you call the SP
in your mapper update tag and pass it the list of these objects. A tiny bit
of business logic in this type of SP simplifies a lot of things if you're
willing to go that route.
Norm
-----Original Message-----
From: smartkid [mailto:skyaoj@gmail.com]
Sent: Wednesday, January 16, 2008 8:22 AM
To: user-cs@ibatis.apache.org
Subject: 答复: Selecting a key value pair
Quote from MSDN:
============================================================================
==============
public class Dictionary<TKey,TValue> : IDictionary<TKey,TValue>,
ICollection<KeyValuePair<TKey,TValue>>,
IEnumerable<KeyValuePair<TKey,TValue>>, IDictionary, ICollection,
IEnumerable,
ISerializable, IDeserializationCallback
============================================================================
==============
The Dictionary<> class does not implement the IList interface.
May be you can convert your Dictionary<String,String> to an
List<KeyValuePair<String,String>> before calling " UpdateTestLCID":
List<KeyValuePair<String,String>> list = new
List(testObject.Names); //each item in the list is an instance of
KeyValuePair<String,String>
SqlMapper.Update("UpdateTestLCID", list);
But I DON’T think this will solve your business requirement.
Quote from your previous mail:
============================================================================
==============
<select id="SelectDescLCID" parameterClass="string" resultmap="?????">
SELECT LCID as Key, Descr as Value
FROM ITEMDESC
WHERE itemId=#value#
</select>
============================================================================
==============
I assume that each LC pair is stored as a database record.
So the batch of update statements generated by IBatis does not handle those
new or deleted LC pairs.
As the record size in the "ITEMDESC" table is so small, may be you can
delete all existing records for an "ItemId" and let the IBatis generate a
batch of INSERT statements instead.
-----邮件原件-----
发件人: Andrea Tassinari [mailto:andreaml@i-mconsulting.com]
发送时间: 2008年1月16日 23:16
收件人: user-cs@ibatis.apache.org
主题: Re: Selecting a key value pair
At 09.34 11/01/2008, Andrea Tassinari wrote:
>At 17.04 10/01/2008, smartkid wrote:
>
>>Use "System.Collections.IDictionary" as the resultClass for
"SelectDescLCID"
>
>Nahhh, is that so simple and straightforward? I cannot believe it! I should
have tried this before. Let me give it a chance :-)
>
>thanks.
Ok, it is *not* so striaghtforward. Current SVN version of the datamapper
exposes a new set of QueryforDictionary<T,V> methods that help a lot getting
a Dictionary<string,string> from a mapped statement.
So, in order to get an object with a Dictionary<string,string> property the
best solution I found is to run 2 query statements. RIP for the lazy
loading... it is acceptable.
Now I'm facing the reverse problem: unroll the dictionary into an update
statement. It would be better to unroll the dictionary within a mapped sql
statement in the SqlMap file, somethig like this (Names is my
Dictionary<string,string> property for the class Test)
<update id="UpdateTestLCID" parameterClass="Test">
<iterate conjunction=";" open="" close="" property="Names">
UPDATE testLCID
SET NOME='$Names[].Key$'
WHERE refid=1
AND LCID='$Names[].Value$'
</iterate>
</update>
this does not work, the mapper complains about Names is not a list or an
array (which, I understand, it is true);
any idea?
Thanks
Andrea
public sealed class Test
{
private int? _id;
private IDictionary<string,string> _names;
public Test()
{
_id = null;
_names = new Dictionary<string,string>();
}
public int? Id
{
get { return _id; }
set { _id = value; }
}
public IDictionary<string,string> Names
{
get { return _nomi; }
set { _nomi = value; }
}
}
>Andrea
>
>
>
>--
>No virus found in this incoming message.
>Checked by AVG Free Edition.
>Version: 7.5.516 / Virus Database: 269.19.0/1218 - Release Date: 1/10/2008
1:32 PM
>
>
>
>
>--
>No virus found in this incoming message.
>Checked by AVG Free Edition.
>Version: 7.5.516 / Virus Database: 269.19.0/1218 - Release Date: 10/01/2008
13.32
__________ NOD32 2795 (20080116) Information __________
This message was checked by NOD32 antivirus system.
http://www.eset.com