合抱之木,生于毫末;九层之台,起于垒土;千里之行,始于足下。-----------送给在码农之路上搬砖的自己。
2018-1-6杭州象上网络科技有限公司
1.mybatis篇
1.mybatis中查询sql的优化:
当需要进行链表查询的时候,我们需要查询条件的字段可能就不在当前xml对应的DO上面,这时我们需要新new一个包含查询条件的QueryDO,且使之继承BaseQuery(BaseQuery主要包含分页查询)。如下:
package com.aifocus.wxhc.query; import java.io.Serializable; public class BaseQuery implements Serializable { /** * 页码 */ protected Integer pageNo = 1; /** * 每页个数 */ protected Integer pageSize = 10; /** * 偏移量 */ private Integer offset; /** * 分页数据偏移量 */ public Integer getOffset() { computeOffset(); return offset; } /** * 计算分页偏移量 */ public Integer computeOffset(){ if (null == pageNo || null == pageSize) { return null; } offset = (pageNo - 1) * pageSize; return offset; } public Integer getPageNo() { return pageNo; } public void setPageNo(Integer pageNo) { this.pageNo = pageNo; computeOffset(); } public Integer getPageSize() { return pageSize; } public void setPageSize(Integer pageSize) { this.pageSize = pageSize; computeOffset(); } }
做好这些,相当于将查询条件封装为一个对象,以后需要添加新的查询条件。一般情况下,我们只需要在这个对象上新增一个私有属性在sql语句上添加一个if标签就可以了。这样我们只需要一条sql语句就能满足许多的查询条件
<select id="selectByPrimarySupplierIdList" resultMap="ResultWithLeftJoinData" parameterType="com.aifocus.wxhc.query.WxhcTeamQuery"> select team_id, wxhc_team.supplier_id, wxhc_team.supplier_cuser_id, team_master_id, team_master_cuser_id, team_name, level, logo, wxhc_team.status, team_power, distrobutor_default_level, wxhc_team.attributes, wxhc_team.is_delete, wxhc_team.gmt_create, wxhc_team.gmt_modify,contact,contact_mobile from wxhc_team LEFT JOIN wxhc_distributor on wxhc_team.team_master_id = wxhc_distributor.distributor_id where 1=1 <if test="supplierId != null"> and wxhc_team.supplier_id = #{supplierId} </if> <if test="teamId != null"> and wxhc_team.team_id = #{teamId} </if> <if test="supplierCuserId != null"> and wxhc_team.supplier_cuser_id = #{supplierCuserId} </if> <if test="teamMasterId != null"> and wxhc_team.team_master_id = #{teamMasterId} </if> <if test="teamMasterCuserid != null"> and wxhc_team.team_master_cuser_id = #{teamMasterCuserid} </if> <if test="teamName != null and teamName != ''"> and wxhc_team.team_name LIKE '%${teamName}%' </if> <if test="masterName != null and masterName != ''"> AND wxhc_distributor.distributor_name LIKE '%${masterName}%' </if> <if test="isDelete != null"> and wxhc_team.is_delete = #{isDelete} </if> order by wxhc_team.gmt_create limit #{offset},#{pageSize} </select>
这条sql语句中的if标签表示,如果该字段存在参数,我们的sql语句就会将该字段加入判断条件查询。这样做的好处:避免在xml中书写许多slq语句,使之臃肿。坏处:我们许多的查询条件返回的结果只有一个,但是在dao层中不得不返回list<Object>
2.mybatis中的链表查询:
链表查询涉及到两张表的字段属性,通常情况下一张表对应的xml配置文件只有对应表的一个DO和对应DO的resultMap。而链表查询中涉及到了两张表的字段DO。所有我们需要将在链表查询的主表上新增一个resultMap。这个resultMap对应的是两个表我们需要的附表的字段ADO。其中附表的ADO需要继承主表的DO,如下:
public class ADO extends WxhcTeamDO { /** * 分销商联系人 */ private String contact; /** * 联系人电话 */ private Long contactMobile;
然后在主表mybatis的xml配置上新增一个resultMap,且这个resultMap对应的DO就是这个ADO,ADO继承了主表的DO,我们resultMap也需要继承主表中的resultMap,也就是说主表中有两个resultMap。如下:
而我们的链表查询返回的resultMap也需要改成这个新的resultMap,如下:<resultMap id="ResultWithLeftJoinData" extends="BaseResultMap" type="com.aifocus.wxhc.dao.DO.WxhcTeamDetailDO"> <result column="contact" property="contact"/> <result column="contact_mobile" property="contactMobile"/> </resultMap>
3mybatis中一些标签的使用:<select id="selectByPrimaryTeamIdList" resultMap="ResultWithLeftJoinData"> select team_id, wxhc_team.supplier_id, wxhc_team.supplier_cuser_id, team_master_id, team_master_cuser_id, team_name, level, logo, wxhc_team.status, team_power, distrobutor_default_level, wxhc_team.attributes, wxhc_team.is_delete, wxhc_team.gmt_create, wxhc_team.gmt_modify,contact,contact_mobile from wxhc_team LEFT JOIN wxhc_distributor on wxhc_team.team_master_id =wxhc_distributor.distributor_id where team_id IN <foreach collection="list" item="item" open="(" close=")" separator=","> #{item} </foreach> </select>
LIKE:
<if test="masterName != null and masterName != ''"> AND wxhc_distributor.distributor_name LIKE '%${masterName}%' </if>
for循环: where team_id IN
COUNT: select COUNT(1) from wxhc_team where 1=1<foreach collection="list" item="item" open="(" close=")" separator=","> #{item} </foreach>
不等于: where team_id != #{teamId,jdbcType=BIGINT}
mybatis中左连接/有链接/内链接查询的区别:左连接以左表为主,右表没有时用null填充字段;右链接以右表为主,左表没有时用null填充;内链接只展示两个表都有的情况。