目录
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,表的列名和实体属性值不一致的解决方案
方案一:给列名取别名和成员变量保持一致