mybatis增删改操作

知识点:

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

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);
	}
}






  • 0
    点赞
  • 1
    收藏
    觉得还不错? 一键收藏
  • 0
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值