IBatis一对多查询

 

 public  class User
    {
      public int UserId { get; set; }
      public string UserName { get; set; }

      
    }

    public class UserRight
    {
        public int UserRightId { get; set; }
        public int UserId { get; set; }
        public int RightId { get; set; }
        public string RightName { get; set; }
    }

    public class UserRightJoin
    {
        public int UserId { get; set; }
        public string UserName { get; set; }

        public IList<UserRight> UserRights { get; set; }
    }

　　在Mapper的UserRight.xml中

<?xml version="1.0" encoding="utf-8" ?>
<!--<!DOCTYPE sqlMap PUBLIC "-//ibatis.apache.org//DTD SQL Map 2.0//EN" "http://ibatis.apache.org/dtd/sql-map-2.dtd">-->
<sqlMap namespace="User"  xmlns="http://ibatis.apache.org/mapping"  xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
  <alias>
    <typeAlias alias="User" type="IBatis.User"/>
    <typeAlias alias="UserRight" type="IBatis.UserRight"/>
    <typeAlias alias="UserRightJoin" type="IBatis.UserRightJoin"/>
    
  </alias>
  <resultMaps>
    <resultMap id="UserRightReslut" class="UserRight">
      <result property="UserRightId" column="UserRightId"/>
      <result property="UserId" column="UserId"/>
      <result property="RightId" column="RightId"/>
      <result property="RightName" column="RightName"/>
    </resultMap>
    <resultMap id="UserReslut" class="User">
      <result property="UserId" column="UserId"/>
      <result property="UserName" column="UserName"/>
      
    </resultMap>
  
    <resultMap id="UserRightJoinReslut" class="UserRightJoin" extends="UserReslut" groupBy="UserId">
      <result property="UserRights" resultMapping="User.UserRightReslut"  />
    </resultMap>

  
    
  </resultMaps>
  <statements>
    <select id="selectAllUserRight" resultMap="UserRightJoinReslut">
      select A.*,b.*
      from  [dbo].[User] a join [userright] b on a.userid=b.userid
    </select>
   
  </statements>
</sqlMap>

　　

在 sqlmap.config

<?xml version="1.0" encoding="utf-8" ?>
<!--<!DOCTYPE sqlMap PUBLIC "-//ibatis.apache.org//DTD SQL Map 2.0//EN" "http://ibatis.apache.org/dtd/sql-map-2.dtd">-->
<sqlMap namespace="User"  xmlns="http://ibatis.apache.org/mapping"  xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
  <alias>
    <typeAlias alias="User" type="IBatis.User"/>
    <typeAlias alias="UserRight" type="IBatis.UserRight"/>
    <typeAlias alias="UserRightJoin" type="IBatis.UserRightJoin"/>
    
  </alias>
  <resultMaps>
    <resultMap id="UserRightReslut" class="UserRight">
      <result property="UserRightId" column="UserRightId"/>
      <result property="UserId" column="UserId"/>
      <result property="RightId" column="RightId"/>
      <result property="RightName" column="RightName"/>
    </resultMap>
    <resultMap id="UserReslut" class="User">
      <result property="UserId" column="UserId"/>
      <result property="UserName" column="UserName"/>
      
    </resultMap>
  
    <resultMap id="UserRightJoinReslut" class="UserRightJoin" extends="UserReslut" groupBy="UserId">
      <result property="UserRights" resultMapping="User.UserRightReslut"  />
    </resultMap>

  
    
  </resultMaps>
  <statements>
    <select id="selectAllUserRight" resultMap="UserRightJoinReslut">
      select A.*,b.*
      from  [dbo].[User] a join [userright] b on a.userid=b.userid
    </select>
   
  </statements>
</sqlMap>

　　

 

然后是 DAO 

    public class BaseDao
    {
        public static ISqlMapper _sqlMap = null;
        static BaseDao()
        {
              _sqlMap = Mapper.Instance();
        }
    }

public class UserDao : BaseDao
    {
        public IList<UserRightJoin> GetList()
        {
            ISqlMapper mapper = _sqlMap;
            IList<UserRightJoin> ListPerson = mapper.QueryForList<UserRightJoin>("selectAllUserRight", null);  //这个"SelectAllPerson"就是xml映射文件的Id
            return ListPerson;
        }
 
        
        public decimal GetAmount()
        {
            ISqlMapper mapper = _sqlMap;
            decimal r = mapper.QueryForObject<decimal>("selectAmount", null);  //这个"SelectAllPerson"就是xml映射文件的Id
            return r;
        }

    }

　　

 

最后是调用 

 

static void Main(string[] args) 
{ 
UserDao ud=new UserDao(); 
var lst= ud.GetList(); 
}