小知识，大挑战！本文正在参与“ 程序员必备小知识 ”创作活动
本文同时参与 「掘力星计划」 ，赢取创作大礼包，挑战创作激励金
Mybatis的CRUD操作
<!-- 添加 -->
<insert id="insertUser" parameterType="User">
insert into user values(null,#{name},#{age},#{birthday})
</insert>
parameterType 参数类型
#{name} #{age} #{birthday}
name,age,birthday 是 User的属性
<!-- 更新 -->
<update id="updateUser" parameterType="User">
update user set name=#{name},age=#{age},birthday=#{birthday} where id=#{id}
</update>
<!-- 删除 -->
<delete id="deleteUser" parameterType="User">
delete from user where id=#{id}
</delete>
<!-- 根据主键查询 -->
<select id="selectUser" parameterType="int" resultType="User">
select * from user where id = #{id}
</select>
#{id} ---- 这个括号中不是一定写 id，因为参数是 int类型
resultType 查询结果的类型
关于参数类型：
对于普通的 Java 类型，有许多内建的类型别名。它们都是大小写不敏感的，由于重载
的名字，要注意原生类型的特殊处理。
模糊查询like 用法
<!-- 根据名字查询 使用like -->
<select id="selectUserByName" parameterType="string" resultType="User">
select * from user where name like "%"#{name}"%"
</select>
动态SQL
<!-- 条件查询 -->
<select id="selectUserByCondition" parameterType="User" resultType="User">
<!--
select * from user where 1=1
<if test='name !=null and name!=""'>
and name like "%"#{name}"%"
</if>
<if test="age !=0">
and age = #{age}
</if>
<if test="birthday !=null">
and birthday = #{birthday}
</if>
-->
select * from user
<where>
<if test='name !=null and name!=""'>
name like "%"#{name}"%"
</if>
<if test="age !=0">
and age = #{age}
</if>
<if test="birthday !=null">
and birthday = #{birthday}
</if>
</where>
</select>
<!-- 也可以使用
<choose> <when> <otherwise>
-->
<!-- 动态更新-->
<update id="dynamicUpdate" parameterType="User">
update user
<set>
<if test="name != null">
name = #{name},
</if>
<if test="age != 0">
age = #{age},
</if>
<if test="birthday != birthday">
birthday = #{ birthday }
</if>
</set>
where id = #{id}
</update>
循环操作
foreach的主要用在构建in条件中，它可以在SQL语句中进行迭代一个集合。foreach元素的属性主要有item，index，collection，open，separator，close。item表示集合中每一个元素进行迭代时的别名，index指定一个名字，用于表示在迭代过程中，每次迭代到的位置，open表示该语句以什么开始，separator表示在每次进行迭代之间以什么符号作为分隔符，close表示以什么结束，在使用foreach的时候最关键的也是最容易出错的就是collection属性，该属性是必须指定的，但是在不同情况下，该属性的值是不一样的，主要有一下3种情况：
-
如果传入的是单参数且参数类型是一个List的时候，collection属性值为list
-
如果传入的是单参数且参数类型是一个array数组的时候，collection的属性值为array
-
如果传入的参数是多个的时候，我们就需要把它们封装成一个Map了，当然单参数也可以封装成map，实际上如果你在传入参数的时候，在MyBatis里面也是会把它封装成一个Map的，map的key就是参数名，所以这个时候collection属性值就是传入的List或array对象在自己封装的map里面的key
1 参数是list
select * from user where id in <foreach collection="list" index="index" item="item" open="(" separator="," close=")"> #{item} </foreach>2 参数是数组
select * from user where id in <foreach collection="array" index="index" item="item" open="(" separator="," close=")"> #{item} </foreach>参数Map select * from user where name like "%"#{name}"%" and id in <foreach collection="ids" index="index" item="item" open="(" separator="," close=")"> #{item} </foreach>
字段别名用法
解决方式一：起别名
<!-- 根据主键查询 字段名和属性名不同 -->
<select id="selectPerson" parameterType="int" resultType="Person">
select id personId,name personName,age personAge from person where id = #{id}
</select>
解决方式二: 使用resultMap
<select id="selectPerson2" parameterType="int" resultMap="personMap">
select * from person where id = #{id}
</select>
<resultMap type="Person" id="personMap">
<id property="personId" column="id"/>
<result property="personName" column="name"/>
<result property="personAge" column="age"/>
</resultMap>
关联关系映射
一对多
<!-- one2many 方式一：使用连接查询 -->
<select id="selectDeptWithEmp1" resultMap="deptMapWithEmp1" parameterType="int">
select d.id as dept_id,d.name as dept_name,e.id emp_id,e.name emp_name,dept_id from dept d,emp e where d.id = e.dept_id and d.id=#{id}
</select>
<resultMap type="Dept" id="deptMapWithEmp1">
<id property="id" column="dept_id" />
<result property="name" column="dept_name" />
<collection property="empList" ofType="Emp">
<id property="id" column="emp_id" />
<result property="name" column="emp_name" />
</collection>
</resultMap>
<!-- one2many 方式一：使用连接查询 -->
<!-- one2many 方式二：使用两次查询 -->
<select id="selectDeptWithEmp2" resultMap="deptMapWithEmp2" parameterType="int">
select deptno ,dname from dept where deptno =#{id}
</select>
<resultMap type="Dept" id="deptMapWithEmp2">
<id property="deptno" column="deptno" />
<result property="dname" column="dname" />
<collection property="empList" column="deptno" select="selectEmpForDept"></collection>
</resultMap>
<select id="selectEmpForDept" resultType="Emp" parameterType="int">
select * from emp where deptno=#{id}
</select>
<!-- one2many 方式二：使用两次查询 -->
多对一
<!-- many2one 方式一：使用连接查询-->
<select id="selectEmpWithDept1" parameterType="int" resultMap="empMapWithDept1">
select e.id emp_id,e.name emp_name,d.id dept_id,d.name dept_name,dept_id from emp e,dept d where e.dept_id=d.id and e.id=#{id}
</select>
<resultMap type="Emp" id="empMapWithDept1">
<id property="id" column="emp_id" />
<result property="name" column="emp_name" />
<association property="dept" javaType="Dept">
<id property="id" column="dept_id" />
<result property="name" column="dept_name" />
</association>
</resultMap>
<!-- many2one 方式一：使用连接查询-->
<!-- many2one 方式二：使用两次查询-->
<select id="selectEmpWithDept2" parameterType="int" resultMap="empMapWithDept2">
select e.id emp_id,e.name emp_name,dept_id from emp e where e.id=#{id}
</select>
<resultMap type="Emp" id="empMapWithDept2">
<id property="id" column="emp_id" />
<result property="name" column="emp_name" />
<association property="dept" column="dept_id" select="selectDeptForEmp"></association>
</resultMap>
<select id="selectDeptForEmp" parameterType="int" resultType="Dept">
select * from dept where id=#{id}
</select>
<!-- many2one 方式二：使用两次查询-->
一对一
<!-- 1:1 连接查询-->
<select id="selectHusbandWithWife" parameterType="int" resultMap="HusbandMapWithWife">
select h.id husband_id,h.name husband_name,w.id wife_id, w.name wife_name from husband h,wife w
where h.id =w.id and h.id=#{id}
</select>
<resultMap id="HusbandMapWithWife" type="Husband">
<id property="id" column="husband_id"></id>
<result property="name" column="husband_name"></result>
<association property="wife" javaType="Wife">
<id property="id" column="wife_id"></id>
<result property="name" column="wife_name"></result>
</association>
</resultMap>
<!-- 1:1 连接查询-->
<!-- 1：1 两次查询 -->
<select id="selectHusbandWithWife2" parameterType="int" resultMap="HusbandMapWithWife2">
select * from husband where id=#{id}
</select>
<resultMap id="HusbandMapWithWife2" type="Husband">
<id property="id" column="id"></id>
<result property="name" column="name"></result>
<association property="wife" column="id" select="selectWifeForHusband">
</association>
</resultMap>
<select id="selectWifeForHusband" parameterType="int" resultType="Wife">
select * from wife where id=#{id}
</select>
<!-- 1：1 两次查询 -->
多对多
<!-- N:N 连接查询 -->
<select id="selectStudentByIdWithCourse" parameterType="int" resultMap="StudentMapWithCourse">
select s.id stu_id,s.name stu_name,c.id course_id,c.name course_name from student s ,course c,student_course sc
where s.id = sc.student_id and sc.course_id=c.id and s.id = #{id}
</select>
<resultMap type="Student" id="StudentMapWithCourse">
<id property="id" column="stu_id" />
<result property="name" column="stu_name" />
<collection property="courseList" ofType="Course">
<id property="id" column="course_id" />
<result property="name" column="course_name" />
</collection>
</resultMap>
<!-- N:N 连接查询 -->
<!-- N:N 两次查询 -->
<select id="selectStudentByIdWithCourse2" parameterType="int" resultMap="StudentMapWithCourse2">
select * from student where id=#{id}
</select>
<resultMap type="Student" id="StudentMapWithCourse2">
<id property="id" column="id" />
<result property="name" column="name" />
<collection property="courseList" select="selectCourseForStudent" column="id"></collection>
</resultMap>
<select id="selectCourseForStudent" resultType="Course" parameterType="int">
select c.id id,c.name name from course c,student_course sc
where c.id = sc.course_id and sc.student_id = #{id}
</select>
<!-- N:N 两次查询 -->
