<?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">
<!-- 命名空间 test作区别时 使用原始Dao开发 使用Mapper代理开发 namespace: 对此接口进行绑定 -->
<mapper namespace="com.mybatis.mapper.UserMapper">
<!-- // 查询用户表数据条数 -->
<!-- public Integer countUser(); -->
<select id="countUser" resultType="Integer">
select count(1) from user
</select>
<!-- public User findUserByIdWithQueryVo(QueryVo vo); -->
<select id="findUserByIdWithQueryVo" parameterType="QueryVo"
resultType="User">
select * from user where id = #{user.id}
</select>
<!-- id:findUserById 找到此sql parameterType : 传入参数的类型 parameterMap : 官方 文档
已经废除 #{id} : mybatis #{} 表示占位符 resultMap : 手动映射 半自动的手动映射 不同写上 相同不用写 会自动映射的
resultType : 自动映射 -->
<resultMap type="User" id="user">
<result column="username_" property="username" />
</resultMap>
<select id="findUserById" parameterType="Integer" resultMap="user">
select id,username username_,sex,address,birthday from user where id =
#{vvv}
</select>
<!-- 根据用户名称模糊查询用户列表 List<User> #{} : 占位符 select * from user where username
like '明' '明' == ? 防 ${} : 字符串拼接 select * from user where username like 明
不防 select * from user where username like "%"'明'"%" 此种方式 占位符还能模糊查询 -->
<select id="findUserListByUsername" parameterType="String"
resultType="com.mybatis.pojo.User">
select * from user where username like "%"#{vvv}"%"
</select>
如果parameterType传输单个简单类型值,${}括号中只能是value
select t.empuid from employee t where cnname like '%'||#{cnname}||'%'
<!-- 添加 order="AFTER" mysql 自增长ID 先保存的数据 再生成的ID order="BEFORE" oracle 自增长
UUID 先生成ID 再保存数据 order="BEFORE" mysql UUID 先生成ID 再保存数据 -->
<!-- <insert id="addUser" parameterType="com.mybatis.pojo.User">
<selectKey keyProperty="id" resultType="Integer" order="AFTER"> select LAST_INSERT_ID()
</selectKey>
</insert> -->
<insert id="addUser" parameterType="com.mybatis.pojo.User"
useGeneratedKeys="true" keyProperty="id">
insert into user
(username,sex,birthday,address)
values
(#{username},#{sex},#{birthday},#{address})
</insert>
<!-- 更新 -->
<update id="updateUserById" parameterType="com..mybatis.pojo.User">
update user set
username=#{username},
sex=#{sex},
birthday=#{birthday},
address=#{address}
where id = #{id}
</update>
<!-- 删除 -->
<delete id="deleteUserById" parameterType="Integer">
delete from user
where id = #{vvvvv}
</delete>
Sql中可将重复的sql提取出来,使用时用include引用即可,最终达到sql重用的目的。
<!-- sql片断 -->
<sql id="selector">
select * from user
</sql>
如果要使用别的Mapper.xml配置的sql片段,可以在refid前面加上对应的Mapper.xml的namespace
动态sql:
<!-- // 需求:根据性别和名字查询用户 -->
<select id="findUserListByusernameAndSex" parameterType="User"
resultType="User">
<include refid="selector" />
<where>
<if test="username != null and username != ''">
and username like "%"#{username}"%"
</if>
<if test="sex != null and sex != ''">
and sex = #{sex}
</if>
</where>
</select>
<!-- // 根据多个id查询用户信息 select * from user where id in (1,10,29) -->
<!-- public List<User> findUserListByList(List<Integer> ids); <foreach collection="list"
跟 入参List<Integer> ids 名称 ids 没有关系 必须List list -->
<!-- foreach标签,进行遍历 -->
<!-- collection:遍历的集合 这里是QueryVo的ids属性 -->
<!-- item:遍历的项目,可以随便写,,但是和后面的#{}里面要一致 -->
<!-- open:在前面添加的sql片段 -->
<!-- close:在结尾处添加的sql片段 -->
<!-- separator:指定遍历的元素之间使用的分隔符 -->
<!-- // 根据多个id查询用户信息 -->
<select id="findUserListByList" parameterType="Integer"
resultType="User">
<include refid="selector" />
<where>
<foreach collection="list" item="id" separator="," open="id in ("
close=")">
#{id}
</foreach>
</where>
</select>
<!-- public List<User> findUserListByQueryVo(QueryVo vo); -->
<select id="findUserListByQueryVo" parameterType="QueryVo"
resultType="User">
<include refid="selector" />
<where>
<foreach collection="ids" item="id" separator="," open="id in ("
close=")">
#{id}
</foreach>
</where>
</select>
<!-- 案例:查询所有用户信息及用户关联的订单信息。 -->
<resultMap type="User" id="userAndOrder">
<id column="id" property="id"/>
<result column="username" property="username"/>
<result column="birthday" property="birthday"/>
<result column="sex" property="sex"/>
<result column="address" property="address"/>
<!-- 一对多映射 标签 -->
<collection property="orders" javaType="list" ofType="order">
<!-- 配置主键,是关联Order的唯一标识 -->
<id property="id" column="oid" />
<result property="number" column="number" />
<result property="createtime" column="createtime" />
<result property="note" column="note" />
</collection>
</resultMap>
<select id="findUserListAndOrder" resultMap="userAndOrder">
SELECT
u.id,
u.username,
u.birthday,
u.sex,
u.address,
o.id oid,
o.number,
o.createtime
FROM
`user` u
LEFT JOIN `orders` o ON u.id = o.user_id
</select>
<!-- 案例:查询订单信息及订单关联的用户信息。 -->
<resultMap type="Orders" id="orderAndUser">
<id column="id" property="id"/>
<result column="userId" property="userId"/>
<result column="number" property="number"/>
<result column="createtime" property="createtime"/>
<!-- 一 对一映射标签 -->
<association property="user" javaType="User">
<result column="username" property="username"/>
<result column="address" property="address"/>
</association>
</resultMap>
<select id="findUserListAndOrder" resultMap="orderAndUser">
SELECT
o.id oid,
o.number,
o.createtime
u.id,
u.username,
u.birthday,
u.sex,
u.address,
FROM
'order' 0
LEFT JOIN 'user'u ON o.user_id = u.id
</select>
</mapper>