一、映射文件
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<!--绑定一个mapper接口-->
<mapper namespace="com.xawl.mybatis.dao.UserMapper">
......
</mapper>
二、增删改查
<!-- 条件查询-->
<select id="getUsers" resultType="user">
select `id`,`name`,`sex`,`age`,`phone`,`pwd`,`time`,`courseid` from user
where courseid=#{courseid};
</select>
<!-- user表插入数据-->
<insert id="insertAll" parameterType="user">
insert into user(`id`,`name`,`sex`,`age`,`phone`,`pwd`,`time`,`courseid`)
values (#{id},#{name},#{sex},#{age},#{phone},#{pwd},#{time},#{courseid});
</insert>
<!-- 修改数据 -->
<update id="updateUser" parameterType="user">
update `user`
set `name`=#{name},`sex`=#{sex},`age`=#{age},`phone`=#{phone},`pwd`=#{pwd},`time`=#{time},`courseid`=#{courseid})
where `id`=#{id};
</update>
<!-- 删除数据 -->
<delete id="deleteUser">
delete from `user` where `id`=#{id};
</delete>
三、动态SQL
根据不同的条件,生成不同的SQL语句
常用标签
1.< if >:符合条件执行
2.< where >:有条件加where,无条件去where,若后面直接跟and、or,自动去除
3.< set >:若没有更改的值自动去除后面的逗号
4.< chooes >:根据顺序执行,只执行第一个符合条件的语句
<!--多条件结合查询-->
<select id="toif" resultType="user">
select * from user
<where>
<if test="sex!=null">
sex = #{sex}
</if>
<if test="courseid!=0">
and courseid = #{courseid}
</if>
<if test="age!=0">
and age = #{age}
</if>
</where>
</select>
和上面的区别,只选择一个符合条件的执行
<!-- choose选择语句的执行 -->
<select id="tochoose" resultType="user">
select * from user
<where>
<choose>
<when test="sex!=null">
sex = #{sex}
</when>
<when test="courseid!=0">
and courseid = #{courseid}
</when>
<when test="age!=0">
and age = #{age}
</when>
</choose>
</where>
</select>
解决没传值的字段会被修改为null的现象
<!-- set标签选择修改数值 -->
<update id="goset" parameterType="user">
update user
<set>
<if test="name!=null">
name = #{name},
</if>
<if test="sex!=null">
sex = #{sex},
</if>
<if test="age!=null">
age = #{age},
</if>
<if test="phone!=null">
phone = #{phone},
</if>
<if test="pwd!=null">
pwd = #{pwd},
</if>
<if test="courseid!=null">
courseid = #{courseid},
</if>
<if test="time!=null">
time = #{time}
</if>
</set>
<where>
id = #{id}
</where>
</update>
四、SQL复用
< sql >标签,可以定义一些公共使用的SQL,如字段名、常用条件等
<sql id="allname">
`id`,`name`,`age`,`sex`
</sql>
<select id="test" resultType="user">
select
<include refid="allname"></include>
from user;
</select>
总结:可以根据动态SQL生成更复杂、功能更强大的SQL语句