知识点:
foreach的主要用在构建in条件中,它可以在SQL语句中进行迭代一个集合。foreach元素的属性主要有 item,index,collection,open,separator,close。item表示集合中每一个元素进行迭代时的别名,index指 定一个名字,用于表示在迭代过程中,每次迭代到的位置,open表示该语句以什么开始,separator表示在每次进行迭代之间以什么符号作为分隔 符,close表示以什么结束,在使用foreach的时候最关键的也是最容易出错的就是collection属性,该属性是必须指定的,但是在不同情况 下,该属性的值是不一样的,主要有一下3种情况:
1. 如果传入的是单参数且参数类型是一个List的时候,collection属性值为list
2. 如果传入的是单参数且参数类型是一个array数组的时候,collection的属性值为array
3. 如果传入的参数是多个的时候,我们就需要把它们封装成一个Map了,当然单参数也可以封装成map,实际上如果你在传入参数的时候,在breast里面也是会把它封装成一个Map的,map的key就是参数名,所以这个时候collection属性值就是传入的List或array对象在自己封装的map里面的key
configuration.xml 配置文件
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE configuration
PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>
<!-- 加载资源配置文件 -->
<properties resource="db.properties" />
<!-- 给实体类起别名 -->
<typeAliases>
<typeAlias type="com.mybatis.test.po.User" alias="User"/>
<typeAlias type="com.mybatis.test.po.Work" alias="Work"/>
</typeAliases>
<!-- 数据源配置,使用Oracle -->
<environments default="development">
<environment id="development">
<transactionManager type="jdbc"></transactionManager>
<dataSource type="POOLED">
<property name="driver" value="${db.driver}" />
<property name="url" value="${db.url}" />
<property name="username" value="${db.username}" />
<property name="password" value="${db.password}" />
</dataSource>
</environment>
</environments>
<!-- userMapper.xml装载进来,就把等于"dao"的实现类装载进来,告诉请求去哪里能找到映射信息 -->
<mappers>
<mapper resource="com/mybatis/test/mapper/userMapper.xml"/>
</mappers>
</configuration>
userMapping.xml
<!-- 在select,delete,update,insert 标签中编写的SQL语句, 设置标签的id属性为getUser,id属性值必须是唯一的,不能够重复
使用parameterType属性指明查询时使用的参数类型 -->
1、增加
1)单个
<insert id="addOneUser" parameterType="User">
insert into mybatis_user(t_id,t_name,t_password)
values (#{id},#{name},#{password})
</insert>
2)批量
Oracle:
<pre name="code" class="html"><span style="white-space:pre"> </span><insert id="batchAddUser" parameterType="java.util.List">
insert into mybatis_user(t_id,t_name,t_password)
<foreach collection="list" item="item" separator="union all">
select #{item.id},#{item.name},#{item.password} from dual
</foreach>
</insert>
mysql:
<insert id="batchAddUser" parameterType="java.util.List"> insert into mybatis_user(t_id,t_name,t_password) VALUES <foreach collection="list" item="item" index="index" separator="," > (#{item.id},#{item.name},#{item.password}) </foreach> </insert>
2、删除
1)单个
<delete id="deleteOneUser" parameterType="User">
2)批量
<span style="white-space:pre"> </span><delete id="batchDeleteUser" parameterType="java.util.Map">
delete from mybatis_user where t_id in
<foreach collection="list" item="item" open="(" separator="," close=")">
#{item}
</foreach>
</delete>
3、更新
1)单个
<span style="white-space:pre"> </span><update id="updateOneUser" parameterType="User">
update mybatis_user set t_name =#{name},t_password=#{password} where t_id =#{id}
</update>
2)批量
根据id批量更新(参数是Map)
<span style="white-space:pre"> </span><update id="batchUpdateUser" parameterType="java.util.Map">
update mybatis_user set t_password=#{password} where t_id in
<foreach collection="ids" item="item" open="(" separator="," close=")">
#{item}
</foreach>
</update>
根据Model批量更新(参数List)
下面三个相同(此处要注意不同之处的细节):
<pre name="code" class="html"> <update id="batchUpdateUserList" parameterType="java.util.List">
<foreach collection="list" item="item"<span style="background-color: rgb(51, 204, 0);"> open="begin" close=";end;" separator=";"></span>
update mybatis_user
<set>
<if test="item.name!=null and item.name!=''">
t_name=#{item.name},
</if>
<if test="item.password!=null and item.password!=''">
t_password=#{item.password},
</if>
</set>
<span style="background-color: rgb(51, 204, 0);">where t_id=#{item.id}</span>
</foreach>
</update>
<span style="white-space:pre"> </span><update id="batchUpdateUserList" parameterType="java.util.List">
<foreach collection="list" item="item"<span style="background-color: rgb(51, 204, 0);"> open="begin" close="end;" separator=""</span>>
update mybatis_user
<set>
<if test="item.name!=null and item.name!=''">
t_name=#{item.name},
</if>
<if test="item.password!=null and item.password!=''">
t_password=#{item.password},
</if>
</set>
<span style="background-color: rgb(51, 204, 0);">where t_id=#{item.id};</span>
</foreach>
</update>
<update id="batchUpdateUserList" parameterType="java.util.List">
<span style="white-space:pre"> </span><span style="background-color: rgb(51, 204, 0);">begin</span>
<span style="white-space:pre"> </span><foreach collection="list" <span style="background-color: rgb(51, 204, 0);">item="item" </span>>
<span style="white-space:pre"> </span>update mybatis_user
<span style="white-space:pre"> </span><set>
<span style="white-space:pre"> </span><if test="item.name!=null and item.name!=''">
<span style="white-space:pre"> </span>t_name=#{item.name},
<span style="white-space:pre"> </span></if>
<span style="white-space:pre"> </span><if test="item.password!=null and item.password!=''">
<span style="white-space:pre"> </span>t_password=#{item.password},
<span style="white-space:pre"> </span></if>
<span style="white-space:pre"> </span></set>
<span style="white-space:pre"> </span><span style="background-color: rgb(51, 204, 0);">where t_id=#{item.id}</span>
<span style="white-space:pre"> </span></foreach>
<span style="white-space:pre"> </span><span style="background-color: rgb(51, 204, 0);">;end;</span>
<span style="white-space:pre"> </span></update>
UserMapper.java 接口
注://注解 @Param("name") 为参数别名,可以修改参数名称,在mapper.xml中使用
public interface UserMapper {
//根据id(主键)查询单个对象
User queryUserById(Integer id);
//根据条件查询List<Object>类型对象
List<User> queryUserByName(@Param("name")String name);
//根据条件查询List<Map<String,Object>>类型对象
List<Map<String, Object>> queryUserMapByName(@Param("name")String name);
//关联查询
List<User> queryUserAndWorkById(Integer userId);
//添加用户
int addOneUser(User user);
//批量插入
int batchAddUser(List<User> listUser);
//根据主键删除用户
int deleteOneUser(int id);
//批量删除
int batchDeleteUser(List<Integer> ids);
//更新用户
int updateOneUser(User user);
//批量更新1
int batchUpdateUser(Map<String, Object> map);
//批量更新2
//如果返回值为-1说明更新成功,我也不知道为什么,反正更新成功的时候,返回值就是-1,可能是游标的特性吧,如果返回值为-2说明更新失败
int batchUpdateUserList(List<User> userList);
}
UserMapperImpl.java 实现类
public class UserMapperImpl implements UserMapper{
private SqlSession sqlSession;
private UserMapper userMapper;
public UserMapperImpl() {
sqlSession = LoadSqlSession.getSqlSession();
userMapper = sqlSession.getMapper(UserMapper.class);
}
public List<User> queryUserByName(String name) {
List<User> listUser = new ArrayList<User>();
listUser = userMapper.queryUserByName(name);
sqlSession.close();
return listUser;
}
public User queryUserById(Integer id) {
User user = new User();
user = userMapper.queryUserById(id);
sqlSession.close();
return user;
}
public List<Map<String, Object>> queryUserMapByName(String name) {
List<Map<String, Object>> listUserMap = new ArrayList<Map<String,Object>>();
listUserMap = userMapper.queryUserMapByName(name);
sqlSession.close();
return listUserMap;
}
public int addOneUser(User user) {
int count = 0;
try {
count = userMapper.addOneUser(user);
sqlSession.commit();
} catch (Exception e) {
count = -1;
sqlSession.rollback();
e.printStackTrace();
} finally {
sqlSession.close();
}
return count;
}
public int batchAddUser(List<User> listUser) {
int count = 0;
try {
count = userMapper.batchAddUser(listUser);
sqlSession.commit();
} catch (Exception e) {
count = -1;
sqlSession.rollback();
e.printStackTrace();
} finally {
sqlSession.close();
}
return count;
}
public int updateOneUser(User user) {
int count = 0;
try {
count = userMapper.updateOneUser(user);
sqlSession.commit();
} catch (Exception e) {
count = -1;
sqlSession.rollback();
e.printStackTrace();
} finally {
sqlSession.close();
}
return count;
}
public int batchUpdateUser(Map<String, Object> map) {
int count = 0;
try {
count = userMapper.batchUpdateUser(map);
sqlSession.commit();
} catch (Exception e) {
count = -1;
sqlSession.rollback();
e.printStackTrace();
} finally {
sqlSession.close();
}
return count;
}
public int deleteOneUser(int user) {
int count = 0;
try {
count = userMapper.deleteOneUser(user);
sqlSession.commit();
} catch (Exception e) {
count = -1;
sqlSession.rollback();
e.printStackTrace();
} finally {
sqlSession.close();
}
return count;
}
public int batchDeleteUser(List<Integer> ids) {
int count = 0;
try {
count = userMapper.batchDeleteUser(ids);
sqlSession.commit();
} catch (Exception e) {
count = -1;
sqlSession.rollback();
e.printStackTrace();
} finally {
sqlSession.close();
}
return count;
}
public int batchUpdateUserList(List<User> userList) {
int count = 0;
try {
count = userMapper.batchUpdateUserList(userList);
sqlSession.commit();
} catch (Exception e) {
count = -2;
sqlSession.rollback();
e.printStackTrace();
} finally {
sqlSession.close();
}
return count;
}
public List<User> queryUserAndWorkById(Integer userId) {
List<User> list = new ArrayList<User>();
list = userMapper.queryUserAndWorkById(userId);
sqlSession.close();
return list;
}
}
Test0.java junit测试类
public class Test0 {
//查询
public void test(){
UserMapper userMapper = new UserMapperImpl();
int id = 4;
//根据id(主键)查询单个对象
User user = new User();
// user = userMapper.queryUserById(id);
// System.out.println(user);
//根据条件查询List<Object>类型对象
List<User> listUser = new ArrayList<User>();
// listUser = userMapper.queryUserByName("guowm");
// System.out.println(listUser);
//根据条件查询List<Map<String,Object>>类型对象
List<Map<String, Object>> listUserMap = new ArrayList<Map<String,Object>>();
listUserMap = userMapper.queryUserMapByName("guowm");
System.out.println(listUserMap);
}
//添加
public void test2(){
UserMapper userMapper = new UserMapperImpl();
// UserService userService = new UserServiceImpl();
List<User> listUser = new ArrayList<User>();
User user = new User(11, "lihui", "789456");
User user2 = new User(12, "guowm", "789456");
//插入一条记录
int i = 0;
// i = userMapper.addOneUser(user);
//批量插入
listUser.add(user);
listUser.add(user2);
i = userMapper.batchAddUser(listUser);
System.out.println(i);
}
//更新
public void test3(){
UserMapper userMapper = new UserMapperImpl();
Map<String, Object> userMap = new HashMap<String, Object>();
List<Integer> ids = new ArrayList<Integer>();
List<User> listUser = new ArrayList<User>();
User user = new User(7, "zhaosh", "000000");
User user2 = new User(8, "zhaosh", "00000");
User user3 = new User(9, "zhaosh", "0000");
listUser.add(user);
listUser.add(user2);
listUser.add(user3);
int count = 0;
for(int index=7; index<10; index++){
ids.add(index);
}
// count = userMapper.updateOneUser(user);
//批量更新1
// userMap.put("ids", ids);
// userMap.put("password", "000000");
// count = userMapper.batchUpdateUser(userMap);
//批量更新2
count = userMapper.batchUpdateUserList(listUser);
System.out.println(count);
}
//删除
public void test4(){
UserMapper userMapper = new UserMapperImpl();
List<Integer> idList = new ArrayList<Integer>();
int id = 3;
int count = 0;
for(int index=4; index<7; index++){
idList.add(index);
}
// count = userMapper.deleteOneUser(id);
//批量删除
count = userMapper.batchDeleteUser(idList);
System.out.println(count);
}
//关联查询
@Test
public void test5(){
UserMapper userMapper = new UserMapperImpl();
List<User> list = new ArrayList<User>();
list = userMapper.queryUserAndWorkById(10);
System.out.println(list);
}
}