mybatis中一对多关系sql的写法

首先是两个实体如下:

public class Vouchers implements Serializable{
    private String id;//id

    private String voucherNumber;//凭证号

    private Integer year;//凭证年份

    private Integer month;//凭证月份

    private Integer number;//凭证数字

    private Date voucherTime;//凭证时间

    private Date createTime;//创建时间

    private Integer orgId;//单位id
    
    private List<VoucherDetail01> children=new ArrayList<VoucherDetail01>();//一对多
                                                       一个凭证号对应多个凭证明细记录

 另一个实体是:

public class VoucherDetail implements Serializable {
    private Integer id;

    private String type;//excel名字

    private String subjectName;//科目名字

    private String subjectNumber;//科目编码

    private String remark;//备注

    private String assist;//摘要

    private String dr;//借方

    private String cr;//贷方

    private String balance;//余额

    private Integer year;//年

    private Integer month;//月

    private Integer orgId;//单位id

    private Date voucherTime;//凭证时间

    private Date createTime;//创建时间

    private Integer number;//凭证数字号

    private String voucherNumber;//凭证字符号

    private Integer entryNumber;//会计分录
    private Integer count;//会计凭证个数
    private Integer projectId;//项目id
    private String voucherBelongId;//凭证字符号
    //get set省略。。。。。。

 首先是一个voucher下面可能对应多个或者一个或者零个voucherDetail明细

我们想要的效果是如下所示的:

 然后我们在mapper.xml文件中配置如下:

<resultMap type="cn.zkhh.pojo.Vouchers" id="VouchersMap">
   <id column="id" property="id" jdbcType="VARCHAR"/>
    <result column="year" property="year" />
     <result column="month" property="month"  />
     <result column="number" property="number"  /> 
	 <result column="voucherNumber" property="voucherNumber" />
  <collection property="children" javaType="java.util.List"  ofType="cn.zkhh.pojo.VoucherDetail01" column="id" select="selectDetailById">
    <result column="id" property="id"  jdbcType="INTEGER" />
     <result column="type" property="type" />   
     <result column="subject_name" property="subjectName"  />  
     <result column="subject_number" property="subjectNumber" />  
     <result column="remark" property="remark" />  
     <result column="assist" property="assist"  /> 
     <result column="dr" property="dr"  />  
     <result column="cr" property="cr"  />  
     <result column="balance" property="balance"  />  
     <result column="year" property="year"  />  
     <result column="month" property="month" /> 
     <result column="org_id" property="orgId" />
     <result column="project_id" property="projectId" />  
     <result column="voucher_time" property="voucherTime"  /> 
     <result column="create_time" property="createTime" />  
     <result column="number" property="number" />  
     <result column="voucher_number" property="voucherNumber" />  
     <result column="jzr" property="jzr"  />  
     <result column="fhr" property="fhr"  />  
     <result column="zdr" property="zdr" />   
    <result column="entry_number" property="entryNumber" />
    </collection>
	</resultMap>
  <select id="selectVoucherAndChildren" resultMap="VouchersMap">
  select <include refid="Base_Column_List"/> from Vouchers
  <where>
  1=1
      <if test="year != null" >
      and  year=#{year,jdbcType=INTEGER}
      </if>
      <if test="month != null" >
       AND  month=#{month,jdbcType=INTEGER}
      </if>
  </where>
  order by number asc
<!--   LIMIT #{offSet}, #{pageRow} -->
  </select>
  
  <sql id="Base_Column_List02" >
    id, type, subject_name as subjectName, subject_number as subjectNumber, remark, assist, dr, cr, balance, year, month, 
    org_id as orgId, voucher_time as voucherTime, create_time as createTime, number, voucher_number as voucherNumber,
     entry_number as entryNumber,project_id as projectId,jzr,fhr,zdr,voucher_belong_id as voucherBelongId
  </sql>
  
  <select id="selectDetailById" resultType="cn.zkhh.pojo.VoucherDetail01">
  select <include refid="Base_Column_List02"/> from
   voucher_detail vd  where vd.voucher_belong_id=#{id}
   order by number, entry_number asc
  </select>

这个是单个表单个表查询后关联出来的结果,此查询结果使用分页插件pagehelper分页是没有问题的,其中的子集不影响主的对象分页。如果使用嵌套结果映射,由于嵌套结果方式会导致结果集被折叠,因此分页查询的结果在折叠后总数会减少,所以无法保证分页结果数量正确,可能结果是不完整的。做法如下(在不使用分页的情况下是可以的)

<resultMap type="cn.zkhh.pojo.Vouchers" id="VouchersMap"> 
    <id column="id" property="id" jdbcType="VARCHAR"/> 
 	 <result column="voucher_number" property="voucherNumber" /> 
     <result column="year" property="year" /> 
     <result column="month" property="month"  /> 
      <result column="number" property="number"  />  
      <result column="voucher_time" property="voucherTime"  />  
       <result column="create_time" property="createTime"  />   
       <result column="org_id" property="orgId"  />  
 	<collection property="children" javaType="java.util.List"  ofType="cn.zkhh.pojo.VoucherDetail01"> -->
  	 <result column="id" property="id"  jdbcType="INTEGER" />  
       <result column="type" property="type" />   
      <result column="subject_name" property="subjectName"  /> 
      <result column="subject_number" property="subjectNumber" />  
      <result column="remark" property="remark" />  
      <result column="assist" property="assist"  />   
      <result column="dr" property="dr"  />  
      <result column="cr" property="cr"  />  
      <result column="balance" property="balance"  />
      <result column="year" property="year"  />  
      <result column="month" property="month" />   
      <result column="org_id" property="orgId" />   
     <result column="project_id" property="projectId" /> 
      <result column="voucher_time" property="voucherTime"  />  
      <result column="create_time" property="createTime" /> 
      <result column="number" property="number" />  
      <result column="voucher_number" property="voucherNumber" />  
      <result column="jzr" property="jzr"  />   
      <result column="fhr" property="fhr"  />   
      <result column="zdr" property="zdr" />   
     <result column="entry_number" property="entryNumber" /> 
      <result column="voucher_belong_id" property="voucherBelongId" jdbcType="VARCHAR" />  
     </collection> 
 	</resultMap> 
   <select id="selectVoucherAndChildren" resultMap="VouchersMap"> 
    SELECT v.*, 
    vd.entry_number from vouchers v LEFT join voucher_detail vd  
    on v.id=vd.voucher_belong_id 
    where v.year=#{year,jdbcType=INTEGER} 
   </select> 
  

之前看过一个写的差不多

<resultMap id="findCountryListMap" type="map">
    <result property="countryId" column="countryId"/>
    <result property="country" column="country"/>
    <collection property="cityList" column="countryId" ofType="map" javaType="java.util.List" 
                select="getCityByCountryId">
        <result property="city" column="city"/>
        <result property="cityId" column="cityId"/>
    </collection>
</resultMap>

<select id="getCityByCountryId" parameterType="long" resultType="map">
    SELECT
        ci.city,
        ci.city_id cityId
    FROM
        city ci
    WHERE
        //countryId这里传值进来的是 findCountryList中countryId的列名,
        //即findCountryListMap中的column属性而不是property属性名
        ci.country_id=#{countryId}
    order by ci.city_id
</select>

<select id="findCountryList" resultMap="findCountryListMap">
    SELECT
        cy.country_id countryId,
        cy.country
    FROM
        country cy
    ORDER BY cy.country_id
</select>

测试结果:

start page(1,2):  第一页
-------------------------------------------
[
    {
        "countryId": 1
        "country": "Afghanistan",
        "cityList": [{
                "city": "Kabul",
                "cityId": 251
            }
        ],
    },
    {
        "countryId": 2
        "country": "Algeria",
        "cityList": [{
                "city": "Batna",
                "cityId": 59
            }, {
                "city": "Bchar",
                "cityId": 63
            }, {
                "city": "Skikda",
                "cityId": 483
            }
        ],
    }
]
-------------------------------------------

start page(2,2): 第2页
-------------------------------------------
[
    {
        "countryId": 3
        "country": "American Samoa",
        "cityList": [{
                "city": "Tafuna",
                "cityId": 516
            }
        ],
    },
    {
        "countryId": 4
        "country": "Angola",
        "cityList": [{
                "city": "Benguela",
                "cityId": 67
            }, {
                "city": "Namibe",
                "cityId": 360
            }
        ],
    }
]
-------------------------------------------

结果借鉴使用一下

没有更多推荐了,返回首页