Mybatis的使用(3)

1:动态sql:

意义:可以定义代码片段,可以进行逻辑判断,可以进行循环或批量处理,使条件判断更为简单

1.1:定义代码片段简化代码:

1.2:多条件查询: 

<where> <if test=”>的用法:用于条件判断查询:

List<User> selectCondition(User user);


<select id="selectCondition" resultType="user" parameterType="user">
select <include refid="c"></include> from mybatis.user
<where>
<if test="name!=null and name!=''">
and name like concat('%',#{name},'%')
</if>
<if test="pwd!=null and pwd!=''">
and pwd =#{pwd}
</if>
</where>

</select>


   User user=new User();
user.setName("张");
user.setPwd("111111");
List<User> users = usermapper.selectCondition(user);
for (User user1 : users) {
System.out.println(user1);
}

}

1.3:条件修改:

<set> <if test=”>的用法:用于条件判断更新:

int updatecondition(User user);

<update id="updatecondition" parameterType="user">
update mybatis.user
<set>
<if test="name!=null and name!=''">
name=#{name},
</if>
<if test="pwd!=null and pwd!=''">
pwd=#{pwd},
</if>

</set>
where id=#{id}
</update>


User user=new User();
user.setId(2);
user.setName("李四");
int i = usermapper.updatecondition(user);
sqlSession.commit();

注意:使用<set>至少更新一列

 

 

1.3:循环查询,批量删除,批量增加,批量更新:

循环查询<foreach>的用法:

List<User> selectForEach(Integer[] integers);
<select id="selectForEach"  resultType="user">
select <include refid="c"></include> from mybatis.user
where id in
<foreach collection="array" item="id" separator="," open="(" close=")">
#{id}
</foreach>

</select>

Integer[] arr={2,4,6};
List<User> users = usermapper.selectForEach(arr);
for (User user1 : users) {
System.out.println(user1);
}

 

<foreach>参数讲解:

collection:用来指定参数的类型,如果是List集合,则为list,如果是Map集合,则为map,如果为数组,则为array

item:每次循环遍历出来的值或对象

separator:多个值或对象之间的分隔符

open:整个循环外面的前括号

close:整个循环外面的后括号

 

批量删除<foreach>的用法:

int deleteForeach(Integer[] integers);接口
<delete id="deleteForeach">
delete from mybatis.user
where id in
<foreach collection="array" item="id" separator="," open="(" close=")">
#{id}
</foreach>
</delete> xml文件
Integer[] arr={1,2};
int i = usermapper.deleteForeach(arr);
sqlSession.commit();测试

批量增加<foreach>的用法:

int insertForeach(List<User> list);

<insert id="insertForeach" parameterType="user">
insert into mybatis.user(id,name,pwd)
values
<foreach collection="list" item="u" separator=",">
(#{u.id},#{u.name},#{u.pwd})
</foreach>
</insert>
注意:item="u",增加的对象为u,取出成员变量的形式应该为u.name等
List<User> list=new ArrayList<User>();
list.add(new User(1,"张三","1111111"));
list.add(new User(2,"张三","1111111"));
list.add(new User(10,"张三","1111111"));
int i = usermapper.insertForeach(list);
sqlSession.commit();

批量更新<foreach>的用法:

int updateForeach(List<User> list);



<update id="updateForeach" parameterType="user">
<foreach collection="list" item="u" separator=";">
update mybatis.user
<set>
<if test="u.name!=null and u.name!=''">
name=#{u.name},
</if>
<if test="u.pwd!=null and u.pwd!=''">
pwd=#{u.pwd},
</if>

</set>
where id=#{u.id}
</foreach>

</update>

 

 

List<User> list=new ArrayList<User>();
list.add(new User(1,"王五","1111111"));
list.add(new User(2,"李四","22222222"));
list.add(new User(10,"张三","3333333"));
int i = usermapper.updateForeach(list);
sqlSession.commit();

注意:需要在dabase.properties中加入&allowMultiQueries=true,即允许多行操作

 

 

指定参数位置下标查询:

《Mybatis的使用(3)》

 

《Mybatis的使用(3)》

 

 

 

入参是map:

《Mybatis的使用(3)》

 

 《Mybatis的使用(3)》

 

 

返回值是一行的map:

Map usermap(Integer id);

<select id="usermap" parameterType="int" resultType="map">
select name,pwd from mybatis.user where id=#{id}
</select>

Map usermap = usermapper.usermap(1);
System.out.println(usermap);
System.out.println(usermap.get("name"));

返回值是多行的map:

List<Map> getmap();

<select id="getmap" resultType="map">
select name,pwd from mybatis.user
</select>

List<Map> getmap = usermapper.getmap();
for (Map map : getmap) {
System.out.println(map);
}
    原文作者:Sunward阳
    原文地址: https://www.cnblogs.com/zhangtaibing/p/16538447.html
    本文转自网络文章,转载此文章仅为分享知识,如有侵权,请联系博主进行删除。
点赞