select :
<selectid="getById"parameterType="int"resultType="User">
select * from user where id=#{id}
</select>
insert:
<!-- 增删改返回的都是int值 不用写返回值 --><insertid="addUser">
INSERT INTO USER VALUES (null,#{username},#{birthday},#{sex},#{address})
</insert>
insert:(获得自增ID)
<!-- 增删改返回的都是int值 不用写返回值 --><insertid="addUser"parameterType="User"useGeneratedKeys="true"keyProperty="id">
INSERT INTO USER VALUES (null,#{username},#{birthday},#{sex},#{address})
</insert><insertid="addUser"parameterType="User"><selectKeykeyProperty="id"resultType="int"order="AFTER">
SELECT LAST_INSERT_ID()
</selectKey><!--SELECTKEY语句必须和INSERT语句一起使用-->
INSERT INTO USER VALUES (null,#{username},#{birthday},#{sex},#{address})
</insert>
Update:
<updateid="updateUser">
update user set username=#{username},birthday=#{birthday},sex=#{sex},address=#{address} where id=#{id}
</update>
delete:
<deleteid="deleteUser"parameterType="int">
delete from user where id=#{id}
</delete>
三大主要语句:
IF + WHERE:
<selectid="selectUserByUsernameAndSex"resultType="User"parameterType="User">
select * from user
<where><iftest="username != null">
username=#{username}
</if><iftest="sex != null">
and sex=#{sex}
</if></where></select>
IF + SET:
<updateid="updateUserById"parameterType="User">
update user u
<set><iftest="username != null and username != ''">
u.username = #{username},
</if><iftest="sex != null and sex != ''">
u.sex = #{sex},
</if></set>
where id=#{id}
</update>
FOREACH:
(a): 单参数List的类型
<selectid="dynamicForeachTest"resultType="User">
select * from user where id in
<foreachcollection="list"index="index"item="item"open="("separator=","close=")">
#{item}
</foreach></select>
(b): 单参数Array的类型
<selectid="dynamicForeach2Test"resultType="User">
select * from user where id in
<foreachcollection="array"index="index"item="item"open="("separator=","close=")">
#{item}
</foreach></select>
关联映射
一对一:
<?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"><mappernamespace="com.mybatis.mapper.ClassMapper"><!--
select * from class c, teacher t where c.teacher_id=t.t_id and c.c_id=1
--><selectid="getClass1"parameterType="int"resultMap="ClassResultMap1">
select * from class c, teacher t where c.teacher_id=t.t_id and c.c_id=#{id}
</select><!-- 使用resultMap映射实体类和字段之间的一一对应关系 --><resultMaptype="Classes"id="ClassResultMap1"><idproperty="id"column="c_id"/><resultproperty="name"column="c_name"/><associationproperty="teacher"javaType="Teacher"><idproperty="id"column="t_id"/><resultproperty="name"column="t_name"/></association></resultMap></mapper>
property:对象属性的名称
javaType:对象属性的类型
column:所对应的外键字段名称
select:使用另一个查询封装的结果
一对多:
<?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"><mappernamespace="com.bruceliu.mapper.ClassesMapper"><!--配置1对多 结果集映射--><resultMapid="classMap"type="Classes"><!--主键--><idproperty="cId"column="C_ID"/><resultproperty="cName"column="c_name"/><!--配置一个包含关系 “有很多”关系 --><collectionproperty="students"ofType="Student"><idproperty="sId"column="s_id"/><resultproperty="sName"column="s_name"/><resultproperty="sAge"column="s_age"/><resultproperty="sEmail"column="s_email"/><resultproperty="classId"column="class_id"/></collection></resultMap><selectid="getById"resultMap="classMap">
SELECT C.*,S.* FROM classes C INNER JOIN student S on C.c_id=S.class_id where C.c_id=#{classId}
</select></mapper>
MyBatis中使用collection标签来解决一对多的关联查询，ofType属性指定集合中元素的对象类型。
多对多:
<?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" ><mappernamespace="com.bruceliu.mapper.UserMapper"><resultMapid="userMap"type="User"><idproperty="uId"column="u_id"/><resultproperty="uAge"column="u_age"/><resultproperty="uName"column="u_name"/><resultproperty="uSex"column="u_sex"/><!--一个用户多个角色--><collectionproperty="roles"ofType="Role"><idproperty="rId"column="r_id"/><resultproperty="rName"column="r_name"/></collection></resultMap><selectid="getUserByid"resultMap="userMap">
SELECT * FROM `user` U INNER JOIN role_user RU ON U.u_id=RU.uu__id INNER JOIN role R ON RU.rr_id=R.r_id
where U.u_id=#{uid}
</select></mapper>
4353
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
