MyBatis中动态Sql实现增删改查

package com.dao;

import java.util.List;
import java.util.Map;

import org.apache.ibatis.annotations.Param;

import com.pojo.Role;
import com.pojo.User;

public interface UserMapper {
	// 使用where标签多条件查询
	List<User> findUserByNameAndRoleIdAndPhone(@Param("uname") String name,
			@Param("roleId") Integer roleId, @Param("phone") String phone);
	//使用trim标签多条件查询
	List<User> findUserByNameAndRoleIdAndPhone1(@Param("uname") String name,
			@Param("roleId") Integer roleId, @Param("phone") String phone);

	// 使用set标签修改
	int updateRole(Role role);

	// 删除
	int deleteUser(int id);

	// 添加
	int addRole(Role role);

	// 用户传递过来的是一个数组,根据数组进行查询
	List<User> findUserAndRoleByIds(Integer[] ids);

	// 用户传递过来的是一个数组,根据数组进行查询
	List<User> findUserAndRoleByNames(List<String> names);

	// 使用map集合进行入参查询
	List<User> findUserAndRoleByMap(Map<String, Object> map);
	
	//使用choose标签确定只能以某一个作为条件进行查询
	List<User> findUserByOne(User user);
}

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">

<mapper namespace="com.dao.UserMapper">
	<!-- 开启二级缓存 -->
	<cache></cache>
	
	<!-- 使用if对参数进行判断,多条件查询(根据不为空的条件进行查询) -->
	<select id="findUserByNameAndRoleIdAndPhone" resultType="User">
		SELECT * FROM `smbms_user` 
			<!-- WHERE 1=1 这样写就不用使用where标签 -->
		<!-- 使用where标签的特性就是会自动的根据条件是否加and -->
		<where>
			<if test="uname!=null and uname!=''">
				AND userName LIKE CONCAT('%',#{uname},'%')
			</if>
			<if test="roleId!=null">
				AND userRole=#{roleId}
			</if>
			<if test="phone!=null and phone!=''">
				AND phone=#{phone}
			</if>
		</where>
	</select>
	
	<!-- 使用trim进行sql语句拼接 -->
	<select id="findUserByNameAndRoleIdAndPhone1" resultType="User">
		SELECT * FROM smbms_user 
		<trim prefix="where" prefixOverrides="and">
			<if test="uname!=null">
				AND userName LIKE CONCAT('%',#{uname},'%') 
			</if>
			<if test="roleId!=null">
				AND userRole=#{roleId}
			</if>
			<if test="phone!=null and phone!=''">
				AND phone=#{phone}
			</if>
		</trim>
	</select>
	
	<!-- 修改  -->
	<update id="updateRole">
		 UPDATE `smbms_role` 
		 <!-- 使用set进行拼接 -->
		<!--  <set>
		 	<if test="roleCode!=null">roleCode=#{roleCode},</if>
		 	<if test="roleName!=null">roleName=#{roleName},</if>
		 	<if test="createdBy!=null">createdBy=#{createdBy},</if>
		 	<if test="modifyBy!=null">modifyBy=#{modifyBy},</if>
		 </set>
		where id=#{id} -->
	
		<!-- 使用trim进行拼接 -->
		<trim prefix="set" suffix="where id=#{id}" suffixOverrides="," >
			<if test="roleCode!=null">roleCode=#{roleCode},</if>
		 	<if test="roleName!=null">roleName=#{roleName},</if>
		 	<if test="createdBy!=null">createdBy=#{createdBy},</if>
		 	<if test="modifyBy!=null">modifyBy=#{modifyBy},</if>
		</trim>
	</update>

	<!-- 删除 -->	
	<delete id="deleteUser">
		DELETE FROM `smbms_user`
		<where>
			id=#{id}
		</where>
	</delete>

	<!-- 添加数据 -->
	<insert id="addRole">
		INSERT INTO `smbms_role` 
		<trim prefix="(" suffix=")" suffixOverrides=",">
			<if test="id!=null">id,</if>
			<if test="roleCode!=null">roleCode,</if>
		 	<if test="roleName!=null">roleName,</if>
		 	<if test="createdBy!=null">createdBy,</if>
		 	<if test="creationDate!=null">creationDate,</if>
		 	<if test="modifyBy!=null">modifyBy,</if>
		 	<if test="modifyDate!=null">modifyDate,</if>
		</trim>
		<trim prefix="values(" suffix=")" suffixOverrides=",">
			<if test="id!=null">#{id},</if>
			<if test="roleCode!=null">#{roleCode},</if>
		 	<if test="roleName!=null">#{roleName},</if>
		 	<if test="createdBy!=null">#{createdBy},</if>
		 	<if test="creationDate!=null">#{creationDate},</if>
		 	<if test="modifyBy!=null">#{modifyBy},</if>
		 	<if test="modifyDate!=null">#{modifyDate},</if>
		</trim>
	</insert>
	
	<!-- 根据用户传递过来的是一个数组,根据数组进行查询 -->
	<select id="findUserAndRoleByIds" resultMap="userList">
		SELECT u.*,r.`roleName` FROM `smbms_user` u INNER JOIN
		`smbms_role` r ON u.`userRole`=r.`id`
		WHERE	u.`id` 
		<!--collection:类型   item:当前的元素 open:前缀  separator:分隔 close:后缀-->
		<foreach collection="array" item="id" open="in(" separator="," close=")" >
			#{id}
		</foreach>
	</select>
	<resultMap type="User" id="userList">
		<id column="id" property="id"/>
		<result column="roleName" property="userRoleName"/>
	</resultMap>
	
	<!-- 使用foreach进行list集合参数的遍历  collection是写list-->
	<select id="findUserAndRoleByNames" resultType="User">
		SELECT * FROM `smbms_user` WHERE userName in
		<foreach collection="list" item="name" open="(" separator="," close=")">
			#{name}
		</foreach>
	</select>
	
	<!-- 使用foreach进行map集合参数的遍历  collection是写的集合的key名 -->
	<select id="findUserAndRoleByMap" resultType="User">
		SELECT * FROM `smbms_user` WHERE userName in
		<foreach collection="nameKey" item="name" open="(" separator="," close=")">
			#{name}
		</foreach>
		and phone in
		<foreach collection="phoneKey" item="phone" open="(" separator="," close=")">
			#{phone}
		</foreach>
	</select>
	
	<!-- 值根据一个条件进行查询,相当于switch -->
	<select id="findUserByOne" resultType="User">
		SELECT * FROM `smbms_user` WHERE 
		<choose>
			<when test="userName!=null">userName like CONCAT('%',#{userName},'%')</when>
			<when test="userRole!=null">userRole like CONCAT('%',#{userRole},'%')</when>
			<when test="phone!=null">phone like CONCAT('%',#{phone},'%')</when>
			<otherwise>
				id!=10
			</otherwise>
		</choose>
	</select>
</mapper>

package com.test;

import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.ibatis.annotations.Select;
import org.apache.ibatis.session.SqlSession;
import org.apache.log4j.Logger;
import org.junit.Test;

import com.dao.UserMapper;
import com.pojo.Role;
import com.pojo.User;
import com.util.MyBatisUtil;

public class MyBatisTest {
	Logger logger=Logger.getLogger(MyBatisTest.class);
	
	//使用where多条件查询
	@Test
	public void testWhere(){
		SqlSession sqlSession = MyBatisUtil.openSession();
		List<User> users = sqlSession.getMapper(UserMapper.class).findUserByNameAndRoleIdAndPhone("王洋", null, null);
		for (User u : users) {
			logger.debug(u);
		}
	}
	
	//使用trim多条件查询
	@Test
	public void testTrim(){
		SqlSession sqlSession = MyBatisUtil.openSession();
		List<User> users = sqlSession.getMapper(UserMapper.class).findUserByNameAndRoleIdAndPhone1("张", null, null);
		for (User u : users) {
			logger.debug(u);
		}
	}
	
	//修改操作
	@Test
	public void testSet(){
		SqlSession sqlSession = MyBatisUtil.openSession();
		Role role=new Role();
		role.setId(3);
		role.setRoleName("员工");
		role.setModifyBy(2);
		int count = sqlSession.getMapper(UserMapper.class).updateRole(role);
		sqlSession.commit();
		logger.debug(count);
	}
	
	//删除操作
	@Test
	public void testDelete(){
		SqlSession sqlSession = MyBatisUtil.openSession();
		int count = sqlSession.getMapper(UserMapper.class).deleteUser(17);
		sqlSession.commit();
		logger.debug(count);
	}
	
	//添加操作
	@Test
	public void testInsert(){
		SqlSession sqlSession = MyBatisUtil.openSession();
		Role role=new Role();
		role.setRoleName("员工");
		int count = sqlSession.getMapper(UserMapper.class).addRole(role);
		sqlSession.commit();
		logger.debug(count);
	}
	
	//根据数组进行查询 
	@Test
	public void testArray(){
		SqlSession sqlSession = MyBatisUtil.openSession();
		Integer[] ids=new Integer[]{6,8,10};
		List<User> users = sqlSession.getMapper(UserMapper.class).findUserAndRoleByIds(ids);
		for (User u : users) {
			logger.debug(u+","+u.getUserRoleName());
		}
	}
	//根据list集合入参查询
	@Test
	public void testList(){
		SqlSession sqlSession = MyBatisUtil.openSession();
		List<String> names=new ArrayList<String>();
		names.add("张华");
		names.add("张晨");
		names.add("asd");
		names.add("赵燕");
		List<User> users = sqlSession.getMapper(UserMapper.class).findUserAndRoleByNames(names);
		for (User u : users) {
			logger.debug(u+","+u.getUserRoleName());
		}
	}
	
	//根据map集合入参查询
	@Test
	public void testMap(){
		SqlSession sqlSession = MyBatisUtil.openSession();
		Map<String, Object> map=new HashMap<String, Object>();
		String[] a={"张三","lisi","张华","张晨"};
		List<String> phoneList=new ArrayList<String>();
		phoneList.add("18098765434");
		phoneList.add("13544561111");
		phoneList.add("123");
		map.put("nameKey", a);
		map.put("phoneKey", phoneList);
		List<User> users = sqlSession.getMapper(UserMapper.class).findUserAndRoleByMap(map);
		for (User u : users) {
			logger.debug(u+","+u.getUserRoleName());
		}
	}
	
	//测试 choose
	@Test
	public void testChoose() {
		SqlSession sqlSession = MyBatisUtil.openSession();
		User user=new User();
		//user.setId(2);
		//user.setUserName("张");
		//user.setPhone("13544561111");
		List<User> users = sqlSession.getMapper(UserMapper.class).findUserByOne(user);
		for (User u : users) {
			logger.debug(u);
		}
	}
	
}