Mybatis 3.2.7 +Mysql 做一个小项目
现在需求是图书和作者多对多关系,配置了一个中间表 图书的resultMap中有一个作者的collection,现在有条件查询,需要根据作者查书,又涉及到分页。。
直接多表联合查询,因为图书和作者是多对多关系,有重复数据,分页做不好,就考虑用嵌套查询。嵌套查询作者的id不知道怎么传递给collection了,各位帮帮忙,看看有什么办法结局~谢谢。
附上源码:
<resultMap id="BaseResultMap" type="cn.edu.bjut.zj.tims.entity.BookExtend" > <id column="BOOKID" property="bookid" jdbcType="INTEGER" />
<result column="TYPEID" property="typeid" jdbcType="INTEGER" />
<result column="PUBLISHERID" property="publisherid" jdbcType="INTEGER" />
<result column="BOOKNAME" property="bookname" jdbcType="VARCHAR" />
<result column="NUM" property="num" jdbcType="INTEGER" />
<result column="ISBN" property="isbn" jdbcType="VARCHAR" />
<result column="PRICE" property="price" jdbcType="INTEGER" />
<result column="PUBLISHERNAME" property="publishername" javaType="string" />
<result column="TYPENAME" property="typename" javaType="string" />
<result column="PRICE" property="price" jdbcType="INTEGER" />
<collection property="alist" ofType="cn.edu.bjut.zj.tims.entity.Author" column={bookid=book_id,authorid=authorid}" select="queryAuthorByBookid"/>
</resultMap>
<select id="queryAuthorByBookid" parameterType="java.util.HashMap" resultType="cn.edu.bjut.zj.tims.entity.Author"> SELECT t_abref.BOOKID,
t_author.AUTHORID,
t_author.AUTHORNAME FROM t_author INNER JOIN t_abref ON t_author.AUTHORID = t_abref.AUTHORID Where bookid=#{bookid} and t_author.AUTHORID, =#{authorid} </select>
<select id="queryAllBook" parameterType="cn.edu.bjut.zj.tims.entity.BookVo" resultMap="BaseResultMap"> SELECT t.TYPENAME,
p.PUBLISHERNAME,
b.BOOKID book_id,
b.TYPEID,
b.PUBLISHERID,
b.BOOKNAME,
b.NUM,
b.ISBN,
b.PRICE FROM t_publisher p INNER JOIN t_book b ON p.PUBLISHERID = b.PUBLISHERID INNER JOIN t_type t ON t.TYPEID = b.TYPEID <where> <if test="typeid!=0"> and b.TYPEID=#{typeid} </if> <if test="bookname!=null and bookname !=''"> and bookname like #{bookname} </if> </where> <!-- Limit #{page.firstResult},#{page.maxResults} --> </select>
兄弟,解决了吗?我现在也遇到这个问题了。######回复 @闫小军 : 换个方法吧######回复 @Eviltuzki : 我也同样的问题,有没有方法......######@Eviltuzki 嵌套查询是不是不能传页面上的参数?######@Eviltuzki 什么方法,说说,感谢。######换了个方法解决了。。######我也同样的问题,有没有方法...######主sql用
SELECT tcs.id
, tcs.key Key, IFNULL(#{code},'') Code from table
<collection property="tab" ofType="map" javaType="ArrayList" column="{Key=Key,Code=Code}" select="searchBykey">
<select id="searchBykey" resultType="map" parameterType="java.util.Map">
SELECT
tcs.id,
tp.name,
tcs.remark,
tcs.gmt_create gmtCreate,
tcs.gmt_modified gmtModified,
tcs.is_delete
FROM
t_custom_setting tcs
LEFT JOIN t_setting ts ON tcs.setting_id = ts.id AND ts.is_delete = 0
LEFT JOIN t_param tp on tp.value = tcs.set_code and tp.obj = 'setting' and tp.is_delete = 0
WHERE tcs.is_delete = 0
and tcs.set_key = #{Key}
<if test="Code != '' and Code != null"> and tcs.set_code = #{Code}</if>
</select>
######讲道理,Mybatis虽然支持这种关系查询,但是实际项目中最好别用,效率低版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。