IBatis.Net 中的数据类型转换

 

关键词：IBatis.NET Access mdb cast typeHandler 类型转换

这两天被一个问题折磨得死去活来，终于解决了，写下来以备参考：

问题是这样的：

我在项目中使用了IBatis.Net，数据库使用的是 MS Access。因为Access数据库没有float或double类型，只有Currency类型可以用作浮点数。所以我定义了类似如下的对象，表，以及SQL语句：

1．对象

        public class Mark

        {

                public string Subject{...}

                public int Year{...}

                public double Point{...}

        }

 

 

2．数据库Mark

Subject       Text

Year           Number

Point          Currency

 

3．SQL statement

        <statements>

                <select id="Query" parameterMap="pMarkMap" resultMap="rMarkMap">

                        Select Subject, Year, Point

                        From Mark

                        <dynamic prepend ="Where">

                                <isNotNull prepend="AND" property="Subject">

                                        Subject = #Subject#

                                </isNotNull>

                                <isNotNull prepend="AND" property="Year">

                                        Year = #Year#

                                </isNotNull>

                        </dynamic>

                </select>

        </statements>

 

4．查询数据库

        Mark mark = new Mark();

        mark.Year = 2005;

        ISqlMapper mapper = Mapper.Instance();

        Object obj = mapper.QueryForObject("Mark.Query", mark);

        mark = obj as Mark;

        ArrayList list = new ArrayList();

        list.Add(obj);

 

        Grid.DataSource = list;

 

结果抛出异常："Specified cast is not valid."

这个问题困扰了我两天，最后才发现两个解决办法：

1.      将对象中的Point属性的类型改成 decimal. 这种方法固然简单，可是在数据库中使用Currency乃不得已，在SQL Server中却有Float类型可以使用，IBatis自动支持从.NET的double类型到数据库Float类型的转换。所以如果为了Currency而使用decimal,则后台数据库变成SQLServer或是Oracle时，在数据库端不得不使用Decimal/Money等类型。或者修改程序中的decimal定义为double类型，这都不是很合理。所以，下面是一个相对复杂一点却合理的解决方法。

注：IBatis.Net自动支持的类型转换请参阅<<DataMapper Developer Guide>> version 1.5.0 – Chapter 3.6, 3.7: Supported database types

 

2.      使用自定义类型转换函数

 

·          定义类

using System;

using IBatisNet.Common;

using IBatisNet.DataMapper.TypeHandlers;

 

namespace TestIBatis

{

        public class DoubleCurrencyTypeHandler :

                IBatisNet.DataMapper.TypeHandlers.ITypeHandlerCallback

        {

                #region ITypeHandlerCallback Members

 

// 此类型的null值

                public object NullValue

                {

                        get

                        {

                                return null;

                        }

                }

 

                public object ValueOf(string s)

                {

                        // 这个函数用于将nullValue值翻译成要比较的null值

// 如果没有，则推荐返回字符串s

                        return s;

                }

 

                public object GetResult(IResultGetter getter)

                {

                        // 用于将从数据库读取的值转换成.NET中的值

                        // 这里我们知道Currency可以转成decimal类型，

// 再用显示转换将decimal转换成double

                        decimal v1 = Convert.ToDecimal(getter.Value);

                        double v2 = (double)v1;

                        return v2;

                }

 

                public void SetParameter(IParameterSetter setter, object parameter)

                {

                        // TODO:  将.NET中的double型转换成decimal，再转换成Currency

                        decimal v1 = Convert.ToDecimal(parameter);

                        setter.Value = v1;

                }

                #endregion

        }

 

}

 

 

·          定义SQL中的parameterMap 及 resultMap

在SqlMap.config中加入下面的语句

  <alias>

        <typeAlias alias="DoubleCurrency"

type="TestIBatis.DoubleCurrencyTypeHandler, TestIBatis" />

  </alias>

 

  <typeHandlers>

        <typeHandler type="double" dbType="Currency" callback="DoubleCurrency" />

  </typeHandlers>

 

在SQL statement所在的Mark.xml文件里加上如下语句

        <alias>

                <typeAlias alias="Mark" type="TestIBatis.Mark, TestIBatis" />

        </alias>

       

       

        <parameterMaps>

                <parameterMap id="pMarkMap" class="Mark">

                        <parameter property="Subject" column="Subject" />

                        <parameter property="Year" column="Year"

type="Int32" dbType="Integer" />

                        <parameter property="Point" column="Point"

type="double" dbType="Currency" />

                </parameterMap>

        </parameterMaps>

       

        <resultMaps>

                <resultMap id="rMarkMap" class="Mark">

                        <result property="Subject" column="Subject" />

                        <result property="Year" column="Year" type="Int32" dbType="Integer" />

                        <result property="Point" column="Point"

type="double" dbType="Currency" />

                </resultMap>

        </resultMaps>

         

        <statements>

                <select id="Query" parameterMap="pMarkMap" resultMap="rMarkMap">

                        Select Subject, Year, Point

                        From Mark

                        <dynamic prepend ="Where">

                                <isNotNull prepend="AND" property="Subject">

                                        Subject = #Subject#

                                </isNotNull>

                                <isNotNull prepend="AND" property="Year">

                                        Year = #Year#

                                </isNotNull>

                        </dynamic>

                </select>

        </statements>

 

运行程序，一切正常

 

注：关于自定义类型转换，请参阅<<DataMapper Developer Guide>> version 1.5.0 – Chapter 3.5.5 – Custom Type Handlers