1.sql公共语句块标签(重要)
把映射文件中的公共的sql语句提取到sql标签中,通过id属性命名;
<sql id="allFields"> id,uname,upwd,uphone,uemail,uwechat,usex,create_time,update_time </sql> <sql id="basicQuery"> select <include refid="allFields"></include> from sys_user </sql>
其他标签内通过include标签来引用sql语句块。
<select id="getById" resultType="com.javasm.sys.entity.Sysuser"> <include refid="basicQuery"></include> where id=#{id} </select>
2.resultMap结果集映射标签(重要)
当数据库列名与实体类的属性名不一致时,使用resultMap标签对属性名与列名做一一映射。
<!--自定实体类的属性与查询列名得到映射关系--> <resultMap id="sysuserMap" type="com.javasm.sys.entity.Sysuser"> <!--主键列与属性映射--> <id property="id" column="id"></id> <!--非主键列映射--> <result property="username" column="uname"></result> <!-- 一般主键列用id标签;非主键用result; 一般就算同名,我们也会做好映射配置,不会省略;这个代码后期可以生成。 --> </resultMap> <!--在select标签中,使用resultMap属性引用到resultMap标签做结果处理--> <select id="getById" resultMap="sysuserMap"> <include refid="basicQuery"></include> where id=#{id} </select>
3.association持有关系映射标签(重要)
3.1 sql级联查询(重要)
<!--自定实体类的属性与查询列名得到映射关系--> <resultMap id="sysuserMap" type="com.javasm.sys.entity.Sysuser"> <!-- 一般主键列用id标签;非主键用result; 一般就算同名,我们也会做好映射配置,不会省略;这个代码后期可以生成。 --> <!--简单类型,主键列与属性映射--> <id property="id" column="id"></id> <!--简单类型,非主键列映射--> <result property="username" column="uname"></result> <result property="uphone" column="uphone"></result> <result property="uemail" column="uemail"></result> <result property="upwd" column="upwd"></result> <result property="uwechat" column="uwechat"></result> <result property="usex" column="usex"></result> <result property="deptId" column="dept_id"></result> <result property="createTime" column="create_time"></result> <result property="updateTime" column="update_time"></result> <!--复杂对象持有关系映射--> <association property="sdept" javaType="com.javasm.sys.entity.Sysdept"> <id property="id" column="dept_id"></id> <result property="dname" column="dname"></result> </association> </resultMap> <select id="listUserAndDept" resultMap="sysuserMap"> select u.* ,d.dname from sys_user u left join sys_dept d on u.dept_id=d.id </select>
3.2 多次单表查询(不重要)
如果进行列表查询,这种方法会进行非常多次二次单表查询,效率不高。听听即可。
<resultMap id="sysuserMap2" type="com.javasm.sys.entity.Sysuser"> <id property="id" column="id"></id> <result property="username" column="uname"></result> <result property="uphone" column="uphone"></result> <result property="uemail" column="uemail"></result> <result property="upwd" column="upwd"></result> <result property="uwechat" column="uwechat"></result> <result property="usex" column="usex"></result> <result property="deptId" column="dept_id"></result> <result property="createTime" column="create_time"></result> <result property="updateTime" column="update_time"></result> <association property="sdept" javaType="com.javasm.sys.entity.Sysdept" column="dept_id" select="com.javasm.sys.dao.SysdeptMapper.getById"> </association> </resultMap> <select id="listUserAndDept2" resultMap="sysuserMap2"> select * from sys_user </select>
4.colleciton聚合关系映射标签(不重要)
在实际业务开发中,不存在这种使用方式。
4.1 sql级联
<resultMap id="sysdeptMap" type="com.javasm.sys.entity.Sysdept"> <id column="id" property="id"></id> <result property="dname" column="dname"></result> <result property="daddress" column="daddress"></result> <result property="createTime" column="create_time"></result> <result property="updateTime" column="update_time"></result> <collection property="users" ofType="com.javasm.sys.entity.Sysuser"> <id property="id" column="uid"></id> <result property="username" column="uname"></result> <result property="uphone" column="uphone"></result> <result property="uemail" column="uemail"></result> <result property="upwd" column="upwd"></result> <result property="uwechat" column="uwechat"></result> <result property="usex" column="usex"></result> <result property="deptId" column="dept_id"></result> <result property="createTime" column="create_time"></result> <result property="updateTime" column="update_time"></result> </collection> </resultMap> <select id="getDeptAndUsersByDeptId" resultMap="sysdeptMap"> select d.*,u.id as uid,u.uname,u.uphone,u.uwechat,u.usex,uemail from sys_dept d left join sys_user u on d.id=u.dept_id where d.id=#{id} </select>
4.2 多次单表查询
<resultMap id="sysdeptMap2" type="com.javasm.sys.entity.Sysdept"> <id column="id" property="id"></id> <result property="dname" column="dname"></result> <result property="daddress" column="daddress"></result> <result property="createTime" column="create_time"></result> <result property="updateTime" column="update_time"></result> <collection property="users" ofType="com.javasm.sys.entity.Sysuser" column="id" select="com.javasm.sys.dao.SysuserMapper.listUserByDeptId"></collection> </resultMap> <select id="getDeptAndUsersByDeptId2" resultMap="sysdeptMap2"> select * from sys_dept where id=#{id} </select>
5.动态sql语句(很重要)
简而言之就是实现在映射文件中进行sql语句拼接的一种实现方式。
if:if标签:做条件判断
where:自动生成where关键字,并忽略紧跟其后的and或or;前提是中间有内容,一般用在查询,修改,删除上。
<select id="list" resultMap="sysuserMap"> <include refid="basicQuery"></include> <where> <if test="username!=null and username!=''"> and uname=#{username} </if> <if test="uphone!=null and uphone!=''"> and uphone like concat("%",#{uphone},"%") </if> <if test="uwechat!=null and uwechat!=''"> and uwechat like concat("%",#{uwechat},"%") </if> <if test="uemail!=null and uemail!=''"> and uemail like concat("%",#{uemail},"%") </if> </where> order by update_time desc </select>
set:用在update语句中,生成set关键字,并忽略最后的逗号。
<!--update语句的if条件只判断不为nul即可; set标签:生成set关键字,并忽略最后的逗号 --> <update id="updateById"> update sys_user <set> <if test="username!=null"> uname=#{username}, </if> <if test="upwd!=null"> upwd=#{upwd}, </if> <if test="uphone!=null"> uphone=#{uphone}, </if> <if test="uemail!=null"> uemail=#{uemail}, </if> <if test="uwechat!=null"> uwechat=#{uwechat}, </if> </set> where id=#{id} </update>
foreach:批量插入,批量删除操作
批量插入:
int insertBatch(List<Sysuser> users);//数组与集合,也属于多参数传递的范畴,mybatis底层把数组封装Map,map.put("list",users)
<!--批量插入语句--> <insert id="insertBatch"> insert into sys_user(uname,upwd,uphone,uemail,uwechat,usex) values <foreach collection="list" item="i" separator=","> (#{i.username},#{i.upwd},#{i.uphone},#{i.uemail},#{i.uwechat},#{i.usex}) </foreach> </insert>
批量删除
int delByIds(Integer[] ids);//数组与集合,也属于多参数传递的范畴,mybatis底层把数组封装Map,map.put("array",ids) int delByIds2(List<Integer> ids);//map.put("list",ids); int delByIds3(@Param("ids") List<Integer> ids);//map.put("ids",ids);
<delete id="delByIds" parameterType="map"> delete from sys_user where id in <foreach collection="array" open="(" close=")" item="i" separator=","> #{i} </foreach> </delete> <delete id="delByIds2" parameterType="map"> delete from sys_user where id in <foreach collection="list" open="(" close=")" item="i" separator=","> #{i} </foreach> </delete> <delete id="delByIds3" parameterType="map"> delete from sys_user where id in <foreach collection="ids" open="(" close=")" item="i" separator=","> #{i} </foreach> </delete>