IBatis.Net学习笔记(三)--常用的查询方式

谁借莪１个温暖的怀抱￠ 2022-03-28 05:45 300阅读 0赞

在项目开发过程中，查询占了很大的一个比重，一个框架的好坏也很多程度上取决于查询的灵活性和效率。  
在IBatis.Net中提供了方便的数据库查询方式。  
  
**在Dao代码部分主要有两种方式：**  
1、查询结果为一个对象：

![None.gif][]                 ISqlMapper sqlMap  =  sqlMapDaoSession.SqlMap;  
![None.gif][]  
![None.gif][]                 return  (Account) sqlMap.QueryForObject( " GetAccountViaColumnName " , accountID);  
![None.gif][]

2、查询结果为一个列表：

![None.gif][]                 ISqlMapper sqlMap  =  sqlMapDaoSession.SqlMap;  
![None.gif][]  
![None.gif][]                 return  (ArrayList)sqlMap.QueryForList( " GetAccountAsHashtableResultClass " ,  1 );  
![None.gif][]

这两种方法同时都提供了面向泛型的重载方法。这两个方法的第一个参数对应配置文件中的select id，第二个参数表示传入查询的条件  
  
**配置文件的写法：**  
在IBatis.Net中提供了多种查询配置的写法，我这里列出几种比较常用的方式：  
1、获得一张表的所有数据

![None.gif][]          < select id = " GetAllAccountsAsHashMapViaResultMap "   
![None.gif][]                        resultMap = " account-hashtable-result " >   
![None.gif][]            select  \*   
![None.gif][]            from Accounts  
![None.gif][]            order by Account\_ID  
![None.gif][]         </ select >

这是最简单的方式，其中resultMap是返回查询结果的形式，需要另外配置：

![None.gif][]          < resultMap id = " account-hashtable-result "   class = " Hashtable " >   
![None.gif][]             < result property = " Id "            column = " Account\_ID " />   
![None.gif][]             < result property = " FirstName "     column = " Account\_FirstName " />   
![None.gif][]             < result property = " LastName "      column = " Account\_LastName " />   
![None.gif][]             < result property = " EmailAddress "  column = " Account\_Email " />   
![None.gif][]         </ resultMap >

表示：得到的结果的每一条记录都映射成一个Hashtable，这个Hashtable中包含四个Key(Id，FirstName......)  
  
2、根据条件查询(简单方式)：

![None.gif][]          < select id = " GetAccountViaColumnIndex "   
![None.gif][]                parameterClass = " int "   
![None.gif][]                resultMap = " indexed-account-result " >   
![None.gif][]            select  
![None.gif][]            Account\_ID,  
![None.gif][]            Account\_FirstName,  
![None.gif][]            Account\_LastName,  
![None.gif][]            Account\_Email  
![None.gif][]            from Accounts  
![None.gif][]            where Account\_ID  =  \#value\#  
![None.gif][]         </ select >

只有一个条件，传入参数的类型是int型，拼写sql时直接用 \#value\#就可以了  
  
3、根据条件查询(较复杂方式)：

![None.gif][]          < select id = " GetAccountsDynamic "  resultMap = " account-result "  parameterClass = " Hashtable "   >   
![None.gif][]            select top $MaximumAllowed$  \*  from Accounts  
![None.gif][]             < dynamic prepend = " where " >   
![None.gif][]                     < isParameterPresent >   
![None.gif][]                     < isNotEmpty prepend = " and "  property = " FirstName "   >   
![None.gif][]                            Account\_FirstName LIKE  ' %$FirstName$% '   
![None.gif][]                     </ isNotEmpty >   
![None.gif][]                     < isNotEmpty prepend = " and "  property = " LastName "   >   
![None.gif][]                            Account\_LastName LIKE  ' %$LastName$% '   
![None.gif][]                     </ isNotEmpty >   
![None.gif][]                     < isNotEmpty prepend = " and "  property = " EmailAddress "    >   
![None.gif][]                            Account\_Email LIKE  ' %$EmailAddress$% '   
![None.gif][]                     </ isNotEmpty >   
![None.gif][]                     </ isParameterPresent >   
![None.gif][]                 </ dynamic >   
![None.gif][]                order by Account\_LastName  
![None.gif][]         </ select >

传入参数是一个Hashtable， MaximumAllowed等表示的是Hashtable里的key值，用$$包含起来。  
并且查询时可以根据条件是否为空动态拼写sql语句  
PS：输入参数同样可以使用Account类，注意对应的键要和类中的属性名一致（大小写也要一样）  
  
4、多表查询  
多表查询时返回参数有三种方式，一种是新建一个类，在这个类中包含这多个表的所有属性，还有一种就是直接返回Hastable就可以了：

![None.gif][]          < select id = " GetAccountAsHashtableResultClass "   
![None.gif][]    resultClass = " HashMap " >   
![None.gif][]            select  
![None.gif][]            a. \* ,b. \*   
![None.gif][]            from a,b  
![None.gif][]            where a.Account\_ID  =  b.Account\_ID         </ select >

PS：这里的HashMap实际上就是Hashtable  
  
第三种方式是使用IBatis中的复杂属性( **感谢** [**Anders Cui**][Anders Cui]  **的提醒**)  
比如现在有两张表Account和Degree，使用Account\_ID关联，那么需要在原有的基础上修改：  
1、修改Account实体类，加入一个属性：

![None.gif][]          private  Degree \_degree;  
![None.gif][]         public  Degree Degree  
![ExpandedBlockStart.gif][]![ContractedBlock.gif][]         ![dot.gif][] \{  
![InBlock.gif][]            get  
![ExpandedSubBlockStart.gif][]![ContractedSubBlock.gif][]            ![dot.gif][]\{  
![InBlock.gif][]                return \_degree;  
![ExpandedSubBlockEnd.gif][]            \}  
![InBlock.gif][]            set  
![ExpandedSubBlockStart.gif][]![ContractedSubBlock.gif][]            ![dot.gif][]\{  
![InBlock.gif][]                \_degree = value;  
![ExpandedSubBlockEnd.gif][]            \}  
![ExpandedBlockEnd.gif][]        \}

这样是一个1:1的关系，也可以加入IList DegreeList的属性，这样查询的结果就是一个1:n的关系  
  
2、修改配置文件：  
在 resultMaps节加入：

![None.gif][]      < resultMap id = " comresult "    class = " Account "   >   
![None.gif][]       < result property = " Id "            column = " Account\_ID " />   
![None.gif][]       < result property = " FirstName "     column = " Account\_FirstName " />   
![None.gif][]       < result property = " LastName "      column = " Account\_LastName " />   
![None.gif][]       < result property = " EmailAddress "  column = " Account\_Email "  nullValue = " no\_email@provided.com " />   
![None.gif][]       < result property = " Degree "  column = " Account\_ID=Account\_ID "   select = " degreeretrive "   />   
![None.gif][]     </ resultMap >   
![None.gif][]

对于Degree属性，还可以加入 lazyLoad=true 延迟加载，优化性能(也就是开始时并没有实际查询数据库，当用到属性Degree时，才实际的查询相应的数据)  
  
在 statements节加入：

![None.gif][]      < statement id = " degreeretrive "   
![None.gif][]      parameterClass = " Hashtable "   
![None.gif][]      resultClass = " Degree " >   
![None.gif][]      select  \*   
![None.gif][]      from Degree  
![None.gif][]      where Account\_id  =  \#Account\_ID\#  
![None.gif][]     </ statement >   
![None.gif][]  
![None.gif][]     < select id = " GetComTables "   
![None.gif][]      resultMap = " comresult " >   
![None.gif][]      select  \*   
![None.gif][]      from Accounts  
![None.gif][]      order by Account\_ID  
![None.gif][]     </ select >

这样可以正确的查询出结果，符合OO，但是也有两个小问题：  
1、比较麻烦，不够灵活  
2、性能受影响：  
    这种方式其实和Hibernet比较类似了，查询时首先执行  
    select \*        from Accounts        order by Account\_ID  
    然后根据这条语句的结果，比如有100条记录，那就要执行100次以下的语句：  
     select \*        from Degree        where Account\_id =  @param0  
  
**关于输入输出：**  
从上面可以看到输入时可以使用： parameterClass和parameterMap，输出时可以使用：resultClass和resultMap  
对于resultMap和parameterMap我们需要另外进行配置(如上所示)  
对于parameterClass和resultClass，如果是C\#固有类型可以直接使用，如果是我们自定义类可以在SqlMap.config中先统一声明一下：

![None.gif][]      < alias >   
![None.gif][]         < typeAlias alias = " Account "  type = " GSpring.Domain.Account " />   
![None.gif][]     </ alias >

[None.gif]: /images/20220328/35396376257448d9a197e5aadabf8761.png
[Anders Cui]: http://anderslly.cnblogs.com/
[ExpandedBlockStart.gif]: https://www.cnblogs.com/Images/OutliningIndicators/ExpandedBlockStart.gif
[ContractedBlock.gif]: https://www.cnblogs.com/Images/OutliningIndicators/ContractedBlock.gif
[dot.gif]: /images/20220328/dca2ae7af9064bbaa34d905912810488.png
[InBlock.gif]: /images/20220328/0233ad5bde4b480dbd07c048cd23f364.png
[ExpandedSubBlockStart.gif]: https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif
[ContractedSubBlock.gif]: https://www.cnblogs.com/Images/OutliningIndicators/ContractedSubBlock.gif
[ExpandedSubBlockEnd.gif]: /images/20220328/1e6308d1479b47a48fd6080cc4a984bb.png
[ExpandedBlockEnd.gif]: /images/20220328/355d01b28cdc410a9a226e96b3838590.png