摩诘

我思故我在 常辨而常新

  博客园 :: 首页 :: 博问 :: 闪存 :: 新随笔 :: 联系 :: 订阅 订阅 :: 管理 ::
  23 随笔 :: 2 文章 :: 470 评论 :: 14 引用

公告

 

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

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


问题是这样的:


我在项目中使用了
IBatis.Net,数据库使用的是 MS Access。因为Access数据库没有floatdouble类型,只有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自动支持从.NETdouble类型到数据库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

posted on 2006-09-01 21:29 sema 阅读(...) 评论(...) 编辑 收藏