目录
1，:用来定义代码片段，可以将所有的列名，或复杂的条件定义为代码片段，供使用时调用。
5，标签：有选择的进行更新处理，至少更新一列，其余列如果为空，则保持原来的值
一，业务功能
1，返回主键的业务
public void testReturnKey() throws ParseException{
User user = new User("kaka",format.parse("2002-20-02"),"1","上海");
userMapper.insert(user);
System.out.println("没有给新插入的数据的ID赋值，selectKey标签注入主键到user.id："+user.getId());
} <insert id="insert" parameterType="user" >
<selectKey keyProperty="id" resultType="int" order="AFTER">
select last_insert_id();
</selectKey>
insert into user (username,birthday,sex,address) values (#{userName},#{birthday},#{sex},#{address});
</insert>
selectKey参数解读
2，UUID生成唯一的字符串
生成全球唯一的字符串，有36个数字字母中划线组成
Java生成UUID
@Test
public void testUUID(){
UUID uuid = UUID.randomUUID();
System.out.println(uuid);
}SQL也存在UUID
select uuid();二，动态sql的意义
动态sql：
可以定义代码片段，可以定义逻辑判断，可以进行循环处理（批量处理），是条件判断更为简单。
1，<sql>:用来定义代码片段，可以将所有的列名，或复杂的条件定义为代码片段，供使用时调用。
2<include>:用来引用<sql>定义的代码片段
将所有的列定义为代码片段，后面查询时直接使用id查询
<!--定义代码片段-->
<sql id="allColumns">
id,username,birthday,sex,address
</sql>查询使用include根据sql的id查询
<select id="getAll" resultType="user">
select <include refid="allColumns"></include>
from user;
</select>3，if 标签 ： 条件判断
4，where标签：多条件拼接使用
//动态sql查询
List<User> getByCondition(User user); <!--
//动态sql查询
List<User> getByCondition(User user);
private String userName;
private Date birthday;
private String sex;
private String address;
-->
<select id="getByCondition" parameterType="user" resultType="user">
select <include refid="allColumns"></include>
from user
<where>
<if test="userName != null and userName != ''">
and username like concat('%',#{userName},'%')
</if>
<if test="birthday != null">
and birthday = #{birthday}
</if>
<if test="sex != null and sex != ''">
and sex = #{sex}
</if>
<if test="address != null and address != ''">
and address like connect ('%',#{address},'%')
</if>
</where>
</select> @Test
public void testGetByCondition() throws ParseException {
// User user = new User("",format.parse("2001-02-02"),"2","");
User user = new User();
user.setSex("2");
List<User> list = userMapper.getByCondition(user);
list.forEach((user1 -> System.out.println(user1)));
}
查询成功，通过where和if通过多条件进行拼接查询。
动态sql的底层是进行sql拼接
5，<set>标签：有选择的进行更新处理，至少更新一列，其余列如果为空，则保持原来的值
如果一列都没有更改，Mybatis就会报错，所以一般在业务处理时，如果没有更改数据，是不允许尽心提交的。
@Test
public void testUpdateBySet(){
User user = new User();
user.setId(12);
user.setUserName("yyds");
userMapper.updateBySet(user);
sqlSession.commit();
} //有选择的更新
int updateBySet(User user); <!--
//有选择的更新
int updateBySet(User user);
private String userName;
private Date birthday;
private String sex;
private String address;
-->
<update id="updateBySet" parameterType="user">
update user
<set>
<if test="userName != null and userName != ''">
username = #{userName},
</if>
<if test="birthday != null">
birthday = #{birthday},
</if>
<if test="sex != null and sex != ''">
sex = #{sex},
</if>
<if test="address != null and address != ''">
address = #{address},
</if>
</set>
where id = #{id}
</update>6，<forEach>标签：循环遍历，批量处理标签
6.1 批量查询
//批量查询
List<User> getByForEach(Integer[] arr); <!--
//批量查询
List<User> getByForEach(Integer[] arr);
-->
<select id="getByForEach" resultType="user">
select <include refid="allColumns"></include>
from user
where id in
<foreach collection="array" item="id" close=")" open="(" separator=",">
#{id}
</foreach>
</select> @Test
public void testGetByForEach(){
Integer[] arr = {1,2,3,4};
List<User> byForEach = userMapper.getByForEach(arr);
byForEach.forEach(user -> System.out.println(user));
}
6.2批量删除
//批量删除
int deleteByForEach(Integer[] arr);
<!--
//批量删除
int deleteByForEach(Integer[] arr);
-->
<delete id="deleteByForEach">
delete from user
where id in
<foreach collection="array" open="(" close=")" separator="," item="id">
#{id}
</foreach>
</delete> @Test
public void testdeleteByForEach(){
Integer[] arr = {1,2,3,4};
System.out.println(userMapper.deleteByForEach(arr));
sqlSession.commit();
}删除后的结果：
6.3 批量增加
//批量插入
int insertByForEach(List<User> list); <!--
//批量插入
int insertByForEach(List<User> list);
private String userName;
private Date birthday;
private String sex;
private String address;
-->
<insert id="insertByForEach">
insert into user(username, birthday, sex, address)
values
<foreach collection="list" item="user" separator=",">
(#{user.userName},#{user.birthday},#{user.sex},#{user.address})
</foreach>
</insert> @Test
public void testInsertByForEach() throws ParseException {
List<User> list = new ArrayList<>();
User u1 = new User("kk",format.parse("2008-01-03"),"1","西安1");
User u2 = new User("dd",format.parse("2008-01-03"),"1","西安2");
User u3 = new User("gg",format.parse("2008-01-03"),"1","西安3");
User u4 = new User("ll",format.parse("2008-01-03"),"1","西安4");
list.add(u1);
list.add(u2);
list.add(u3);
list.add(u4);
userMapper.insertByForEach(list);
sqlSession.commit();
}结果：
6.4 批量更新
设置批量更新，这里批量在于批量执行update的sql语句，不能使用类似的where in 查询条件，否则会将不同id的行的属性值修改为完全一样的数据
并且如果要进行批量的更新操作需要在jdbc.properties中添加允许批量操作的编码，否则批量插入会失败
jdbc.url=jdbc:mysql://localhost:3306/ssm?useUnicode=true&characterEncoding=utf8&allowMultiQueries=true
//批量更新
int updateByForEach(List<User> list); <!--
//批量更新
int updateByForEach(List<User> list);
private String userName;
private Date birthday;
private String sex;
private String address;
-->
<update id="updateByForEach">
<foreach collection="list" item="u" separator=";">
update user
<set>
<if test="u.userName != null and u.userName != ''">
username = #{u.userName},
</if>
<if test="u.birthday != null">
birthday = #{u.birthday},
</if>
<if test="u.sex != null and u.sex != ''">
sex = #{u.sex},
</if>
<if test="u.address != null and u.address != ''">
address = #{u.address},
</if>
</set>
where id = #{u.id}
</foreach>
</update> //批量更新
@Test
public void testUpdateByForEach() throws ParseException {
List<User> list = new ArrayList<>();
User u1 = new User(14,"kkkk",format.parse("2008-01-03"),"1","888");
User u2 = new User(15,"ddkk",format.parse("2008-01-03"),"2","666");
User u3 = new User(16,"gggg",format.parse("2008-01-03"),"2","777");
User u4 = new User(17,"llgg",format.parse("2008-01-03"),"2","xianxianxianxian");
list.add(u1);
list.add(u2);
list.add(u3);
list.add(u4);
userMapper.updateByForEach(list);
sqlSession.commit();
}编译后的sql语句：
7，指定参数位置
如果入参数多个，可以通过指定参数位置，来进行参数传递。
实体类只能封装住成员变量的条件，如果某个变量又区间范围内的判断或者有两个值要进行判断，那么一个实体类就包不住，就需要使用指定参数位置，来进行范围查询。
查询生日在2000-01-01到2010-01-01之间的数据
//指定日期范围内查询
List<User> getByBetween(Date begin,Date end);<!--
//指定日期范围内查询
List<User> getByBetween(Date begin,Date end);
-->
<select id="getByBetween" resultType="user">
select <include refid="allColumns"></include>
from user
where birthday
between #{arg0} and #{arg1}
</select> @Test
public void testGetBetween() throws ParseException {
Date begin = format.parse("2000-01-01");
Date end = format.parse("2010-01-01");
List<User> users = userMapper.getByBetween(begin, end);
users.forEach(user -> System.out.println(user));
}
8，入参是map
若传入的参数有多个，使用map方式，更省事，更明确，更具语义，在实体类封装不住的情况下
//入参数map的查找
List<User> getByMap(Map map); <!--
//入参数map的查找
List<User> getByMap(Map map);
-->
<select id="getByMap" resultType="user">
select <include refid="allColumns"></include>
from user
where birthday
between #{birthBegin} and #{birthEnd}
</select> @Test
public void testGetByMap() throws ParseException {
Date begin = format.parse("2000-01-01");
Date end = format.parse("2010-01-01");
Map<String,Date> map = new HashMap<>();
map.put("birthBegin",begin);
map.put("birthEnd",end);
List<User> users = userMapper.getByMap(map);
users.forEach(user -> System.out.println(user));
}
9，返回值是map
9.1 返回值单行map
返回值保存在map中，通过map.get("列名")得到查询的值
//返回值是单行map
Map getSimpMap(Integer id); <!--
//返回值是单行map
Map getSimpMap(Integer id);
-->
<select id="getSimpMap" parameterType="int" resultType="map">
select username,address
from user
where id = #{id}
</select> @Test
public void testGetSimpMap() throws ParseException {
Map map = userMapper.getSimpMap(5);
System.out.println(map.get("username"));//查询到的值保存在了对应的列名中，或者列名的别名中
}
9.2 返回值是多行map
@Test
public void testGetSimpMap() throws ParseException {
Map map = userMapper.getSimpMap(5);
System.out.println(map.get("username"));//查询到的值保存在了对应的列名中，或者列名的别名中
} <!--
//返回值是多行map
List<Map> getMulMap();
返回多行map时，使用list集合接受，但是查询到的数据还是以map形式表现，
所以还需要使用map来接受返回值
-->
<select id="getMulMap" resultType="map">
select username,address
from user;
</select> @Test
public void testGetMulMap() throws ParseException {
List<Map> mulMap = userMapper.getMulMap();
mulMap.forEach(map -> System.out.println(map));
}
10，表的列名和实体属性值不一致的解决方案
方案一：给列名取别名和成员变量保持一致
方案二：通过resultMap手工完成映射，
6218
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
