分片
<sql id="search">
<where>
<if test="queryParams.username != null and queryParams.username != ''">
username like concat('%',#{queryParams.username},'%')
</if>
</where>
</sql>
调用: <include refid="search"/>
主键回填
<!--以添加用户为列-->
<insert id="addUser">
<selectKey keyColumn="id" keyProperty="id" resultType="int"
order="AFTER">
SELECT LAST_INSERT_ID()
</selectKey>
insert into t_user values(null,#{username},#{password},#{remark},#{email},null,null);
</insert>
<!--
useGeneratedKeys:表示开启使用主键回填的功能,默认不开启
keyColumn:指定主键列的名称
keyProperty:指定主键对应实体类中属性的名称
-->
<insert id="addUserAndGetPk2" useGeneratedKeys="true" keyColumn="id" keyProperty="id">
insert into user values(null,#{username},#{birthday},#{sex},#{address})
</insert>
动态sql语句
if标签
<if test="ognl条件表达式">
sql片段
</if>
作用:满足条件,sql片段就参与sql的拼接,否则丢弃了;
定义方法:
/**
* 动态SQL,如果输入用户名称,则根查询性别为男的基础上,模糊搜搜
* 否则,仅仅查询性别为男的用户信息
* @param name
* @return
*/
List<User> findUsersByUserName(@Param("name") String name);
xml绑定:
<!--
在test属性的添加中可以直接获取入参对应的值,无需使用#{}
说明:test属性指定的条件被满足时,if标签表过的sql才能参与sql的拼接
如果不满足,那么不参与拼接
-->
<select id="findUsersByUserName" resultMap="userMap">
select * from user where sex='男'
<if test="name!=null">
and user_name like concat('%',#{name},'%');
</if>
</select>
<!-- <select id="findUsersByUserName" resultMap="userMap">-->
<!-- select * from user where sex='男'-->
<!-- <if test="name!=null">-->
<!-- and user_name like "%"#{name}"%"-->
<!-- </if>-->
<!-- </select>-->
测试:
/**
* @Description 测试if标签实现动态SQL
*/
@Test
public void test19(){
SqlSession session = MybatisUtil.getSession();
UserMapper mapper = session.getMapper(UserMapper.class);
//sql:select * from user where sex='男'
// List<User> users = mapper.findUsersByUserName(null);
//sql:select * from user where sex='男' and user_name like concat('%',?,'%')
List<User> users = mapper.findUsersByUserName("净");
System.out.println(users);
session.close();
}
choose when otherwise多选一标签
定义接口方法:
/**
* 根据用户名和地址动态查询
* 如果输入了用户名,则不论是否输入了地址,仅仅按照用户名模糊查找;
* 否则,如果输入了地址,则按照住址查找;
* 如果两者都没有输入则查找用户名为“孙悟空”的用户;
* @param name
* @param address
* @return
*/
List<User> findUsersByUserNameAndAddress(@Param("name") String name,@Param("address") String address);
定义xml:
<!--
choose :多选一标签,当有when中条件满足时,则其他的when或者otherwise不执行
when
属性:test ,填写条件表达式
otherwise
兜底方案
-->
<select id="findUsersByUserNameAndAddress" resultMap="userMap">
select * from user where sex='男'
<choose>
<when test="name!=null">
and user_name like concat('%',#{name},'%')
</when>
<when test="address!=null">
and address=#{address}
</when>
<otherwise>
and user_name='猪八戒'
</otherwise>
</choose>
</select>
测试:
/**
* @Description 测试choose多选一标签实现动态SQL
*/
@Test
public void test2(){
SqlSession session = MybatisUtil.getSession();
UserMapper mapper = session.getMapper(UserMapper.class);
//sql:select * from user where sex='男' and user_name like concat('%',?,'%')
// List<User> users = mapper.findUsersByUserNameAndAddress("净", null);
//select * from user where sex='男' and address=?
// List<User> users = mapper.findUsersByUserNameAndAddress(null, "上海");
//select * from user where sex='男' and user_name='猪八戒'
List<User> users = mapper.findUsersByUserNameAndAddress(null, null);
System.out.println(users);
session.close();
}
where标签
作用:1.合理添加where关键字;
2.去除被where标签包裹的sql中多余的and或者or关键字;
1.定义接口方法:
/**
* 根据用户名和地址综合查询
* 使用where标签
* @param name
* @param address
* @return
*/
List<User> getUsersByNameAndAddress(@Param("name") String name,@Param("address") String address);
2.定义xml:
<!--
where标签作用:
1.合理条件where关键字:有条件就添加,没有添加,就不添加;
2.去除被where标签包裹的sql中多余的and或者or关键字
-->
<select id="getUsersByNameAndAddress" resultMap="userMap">
select * from user
<where>
<if test="name!=null">
user_name like concat('%',#{name},'%')
</if>
<if test="address!=null">
and address=#{address}
</if>
</where>
</select>
3.测试
/**
* @Description 测试where标签
*/
@Test
public void test(){
SqlSession session = MybatisUtil.getSession();
UserMapper mapper = session.getMapper(UserMapper.class);
//select * from user
// List<User> users = mapper.getUsersByNameAndAddress(null, null);
//sselect * from user WHERE user_name like concat('%',?,'%')
//List<User> users = mapper.getUsersByNameAndAddress("净", null);
//sselect * from user WHERE address=?
// List<User> users = mapper.getUsersByNameAndAddress(null, "上海");
//sselect * from user WHERE user_name like concat('%',?,'%') and address=?
List<User> users = mapper.getUsersByNameAndAddress("净", "上海");
System.out.println(users);
}
set标签
set标签作用:
1.添加set关键字,一般配置update语句使用
2.去除多余的逗号;
1)定义方法:
/**
* 仅仅更新出入对象中属性不为null的值
* @param user
* @return
*/
Integer updateUserByExistAttribute(User user);
2)定xml:
<!--
set:1.添加set关键字
2.去除多余逗号
-->
<update id="updateUserByExistAttribute">
update user
<set>
<if test="username!=null">
user_name=#{username},
</if>
<if test="birthday!=null">
birthday=#{birthday},
</if>
<if test="sex!=null">
sex=#{sex},
</if>
<if test="address!=null">
address=#{address}
</if>
</set>
where id=#{id}
</update>
3)测试
/**
* @Description 测试where标签
*/
@Test
public void test4(){
SqlSession session = MybatisUtil.getSession();
UserMapper mapper = session.getMapper(UserMapper.class);
User user = new User();
user.setId(8);
user.setAddress("花果山");
user.setUsername("孙悟空");
//update user set user_name=?,address=? where id=?
Integer count = mapper.updateUserByExistAttribute(user);
System.out.println(count);
}
foreach循环标签
定义方法:
/**
* 根据id集合批量查询用户信息
* @param ids
* @return
*/
List<User> findByIds(@Param("ids") List<Integer> ids);
定义xml:
<!--
需要动态拼接的sql片段:(1,2,3)
说明:
foreach:作用是遍历集合或者数组
属性:
collection:指定集合类型,如果集合没有使用@Param注解,可以使用list,
如果使用@Param注解,则使用注解指定的集合名称
item:集合循环过程中的每一个元素
separator:集合元素拼接过程中的分隔符
open:拼接字符串时以指定的前缀字符拼接
close:拼接字符串时,以指定的字符结尾
集合中有多少个元素,就会产生多少个占位符
-->
<select id="findByIds" resultMap="userMap">
select * from user where id in
<foreach collection="ids" item="userId" separator="," open="(" close=")">
#{userId}
</foreach>
</select>
测试:
/**
* @Description 测试根据id批量查询
*/
@Test
public void test5(){
SqlSession session = MybatisUtil.getSession();
UserMapper mapper = session.getMapper(UserMapper.class);
List<Integer> ids = Arrays.asList(1, 3, 5);
//select * from user where id in ( ? , ? , ? )
List<User> users = mapper.findByIds(ids);
System.out.println(users);
}
其它批量操作:
1)批量删除和插入
/**
* 根据id集合批量删除
* @param ids
* @return
*/
Integer deleteByIds(@Param("ids") List<Integer> ids);
/**
* 批量插入用户数据
* @param users
* @return
*/
Integer insertUsers(@Param("users") List<User> users);
2)定义xml
<!--
delete form user where id in (1,3,5)
-->
<delete id="deleteByIds">
delete from user where id in
<foreach collection="ids" item="userId" separator="," open="(" close=")">
#{userId}
</foreach>
</delete>
<!--
批量插入:insert into user values (null,#{username},#{birthday},...),(),();
分析:
传入多少个用户对象,就有多少个(null,#{username},#{birthday},...)
因为拼接过程中open开头字符和结尾的字符没有,所以无需配置
循环过程中通过u这个对象调用u.属性名称,来获取属性值
-->
<insert id="insertUsers">
insert into user values
<foreach collection="users" item="u" separator=",">
(null,#{u.username},#{u.birthday},#{u.sex},#{u.address})
</foreach>
</insert>
3)测试
/**
* @Description 测试根据id批量查询
* delete from user where id in (?,?,?);
*/
@Test
public void test6(){
SqlSession session = MybatisUtil.getSession();
UserMapper mapper = session.getMapper(UserMapper.class);
List<Integer> ids = Arrays.asList(1, 3, 5);
Integer count = mapper.deleteByIds(ids);
System.out.println(count);
}
/**
* @Description 测试批量插入用户信息
*
*/
@Test
public void test7(){
SqlSession session = MybatisUtil.getSession();
UserMapper mapper = session.getMapper(UserMapper.class);
User user = new User();
user.setBirthday(Date.valueOf("2020-08-20"));
user.setAddress("上海");
user.setSex("男");
user.setUsername("沙悟净3");
User user2 = new User();
user2.setBirthday(Date.valueOf("2020-08-20"));
user2.setAddress("上海");
user2.setSex("男");
user2.setUsername("沙悟净4");
List<User> users = Arrays.asList(user, user2);
// insert into user values (null,?,?,?,?) , (null,?,?,?,?)
Integer count = mapper.insertUsers(users);
System.out.println(count);
}
定义实体映射
一对一
<!--定义映射规则-->
<resultMap id="orderMap" type="com.itheima.pojo.Order">
<!--主键映射-->
<id column="order_id" property="id"/>
<!--非主键字段映射-->
<result column="order_number" property="orderNumber"/>
<!--一对一标签
association应用场景:
如果一个类中包含了另一个类的属性,那么使用association
如果一个类中包含一个List集合,集合的泛型是另一个类,那么映射时,用collection;
属性:
property:指定Order映射类的属性 tbUser,
javaType:指定属性的类型,然后标签内的映射都针对这个指定的类型进行映射
-->
<association property="tbUser" javaType="com.itheima.pojo.User" autoMapping="true">
<!--内部的映射规则,都是针对User-->
<!--用户主键映射-->
<id column="id" property="id"/>
<!--非主键字段映射-->
<result column="user_name" property="userName"/>
</association>
</resultMap>
一对多
<!--定义一对多映射规则-->
<resultMap id="userMap" type="com.itheima.pojo.User" autoMapping="true">
<!--主键映射-->
<id column="id" property="id"/>
<!--非主键映射-->
<result column="user_name" property="userName"/>
<!--user中其他属性与查询字段名称一致,使用自动映射即可
collection:如果换一个类中含有一个List类型的属性,那么就使用collection标签
属性:
property:指定在主类中的属性名称,orderList成员属性名称;
javaType:指定属性对应的类型,一般是list
ofType:指定集合的泛型,内部的映射规则都围绕这个泛型进行映射
-->
<collection property="orderList" javaType="list" ofType="com.itheima.pojo.Order">
<!--内部映射都针对ofType指定的类型进行映射-->
<!--主键映射-->
<id column="order_id" property="id"/>
<result column="order_number" property="orderNumber"/>
</collection>
</resultMap>
<select id="findUserInfoByUserId" resultMap="userMap">
select
u.*,
o.id as order_id,
o.order_number
from tb_user as u,
tb_order as o
where u.id = o.user_id
and u.id = #{id}
</select>