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
}
],
}
]
-------------------------------------------结果借鉴使用一下