<?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"><!--
namespace: 命名空间,用于隔离sql语句
id:
parameterType: 传递的参数的类型
resultType: 得到的数据的类型
--><mappernamespace="test"><!--1. 通过id查询用户--><selectid="findUserById"parameterType="int"resultType="domain.User">
select * from user where id = #{id}
</select><!--2. 根据用户名查询用户信息, 查询到的结果是集合--><!--<select id="findByUsername" parameterType="string" resultType="domain.User">
select * from user where username like #{username}
</select>
<select id="findUsers" parameterType="string" resultType="domain.User">
select * from user where username like "%${value}%"
</select>--><selectid="findUsers1"parameterType="string"resultType="domain.User">
select * from user where username like concat("%" , #{username}, "%")
</select><!--插入一条新的用户信息--><insertid="saveUser"parameterType="domain.User"><selectKeykeyProperty="id"resultType="java.lang.Integer"order="AFTER">
select LAST_INSERT_ID()
</selectKey>
insert into user(username, birthday, sex, address) values(#{username}, #{birthday}, #{sex}, #{address})
</insert><!--删除数据--><deleteid="delUser"parameterType="int">
delete from user where id = #{id}
</delete><!--修改用户--><updateid="updateUser"parameterType="domain.User">
update user set username = #{username}, sex=#{sex}, birthday=#{birthday}, address=#{address} where id = #{id}
</update></mapper>
<!--动态生成sql语句--><selectid="getUserByUser"parameterType="user"resultType="user">
select * from user
<where><iftest="id != 0">
and id = #{id}
</if><iftest="username != null and username != ''">
and username = #{username}
</if></where></select>
2.2 foreach遍历
<!--
foreach的动态生成sql
collection : 表示要循环遍历的集合或数组
item : 每次遍历得到的元素
open : 循环开始之前添加的内容 只执行一次
close : 循环结束之后添加的内容 只执行一次
separator : 每循环一次就执行一次
--><!--select * from user where id in (1,2,3)--><selectid="getUserInIds"parameterType="queryvo"resultType="user" >
select * from user
<where><foreachcollection="ids"item="id"open="and id in ("close=")"separator=",">
#{id}
</foreach></where></select>
2.3 set的使用
<!--
更新操作
update user set username = ?, password = ? where id = ?
set: 解决了最后一个逗号出现的问题
--><updateid="updateUser"parameterType="user">
update user
<set><iftest="username != null and username != ''">
username = #{username},
</if><iftest="address != null and address != ''">
address = #{address},
</if></set>
where id = #{id}
</update>
<!--
一对一, 关联映射查询.orderUser为新建的po类
--><selectid="findOrderAndUser"resultType="orderUser">
SELECT
o.*, u.username, u.address
FROM
orders o
LEFT JOIN user u ON u.id = o.user_id
</select>
4.2 利用resultMap
<!--
第一步: 在orders实体类中持有对user类的引用
第二步: 配置映射文件, 主要配置resultMap;
注意: 一对一关联映射使用的标签为 association
--><resultMapid="order_user_result_map"type="orders"><!-- 主键 --><idproperty="id"column="id"/><!--普通列--><resultproperty="userId"column="user_id"/><resultproperty="number"column="number"/><resultproperty="createtime"column="createtime"/><resultproperty="note"column="note"/><!-- 一对一关联映射 --><associationproperty="user"javaType="domain.User"><idproperty="id"column="user_id"/><resultcolumn="username"property="username"/><resultcolumn="address"property="address"/></association></resultMap><selectid="findOrderAndUser"resultMap="order_user_result_map">
SELECT
o.*, u.username, u.address
FROM
orders o
LEFT JOIN user u ON u.id = o.user_id
</select>
5.一对多关联映射查询
<!--
一对多关联映射查询
ofType : 代表一对多的关联映射
--><resultMapid="user_orders_result"type="user"><idproperty="id"column="id"/><resultproperty="username"column="username"/><resultproperty="birthday"column="birthday"/><resultproperty="sex"column="sex"/><resultproperty="address"column="address"/><!-- 一对多关联映射 --><collectionproperty="orders"ofType="domain.Orders"><idproperty="id"column="oid"/><resultproperty="number"column="number"/><resultproperty="createtime"column="createtime"/></collection></resultMap><selectid="findUserAndOrders"resultMap="user_orders_result">
SELECT
u.*,
o.id oid,
o.number,
o.createtime
FROM
`user` u
LEFT JOIN orders o ON u.id = o.user_id
</select>