Mybatis动态Sql核心

                                 Mybatis动态Sql核心

目录

                                 Mybatis动态Sql核心

1.Mybatis的XML配置文件解析

Mybatis 缺点总结

1.必须传的是类型或者Map集合  无法传入单值   否则报错

2.当使用set必须保证有一个条件成立 否则sql语法异常(有一定的局限性)

3.当使用trim标签必须保证有一个条件成立 否则sql语法异常(有一定的局限性)

2.Demo接口测试代码



1.Mybatis的XML配置文件解析

Mybatis 缺点总结

1.必须传的是类型或者Map集合  无法传入单值   否则报错

<select id="findAllUsers_if"
	        resultMap="user_map"
	        parameterType="User">
		<include refid="select_user"></include>
		where address='北京'
		<if test="name != null">
		   and username =#{name}
		</if>
	</select>
	<!-- choose when otherwise -->
	<select id="findAllUsers_choose"
	        resultMap="user_map"
	        parameterType="java.util.Map">
		<include refid="select_user"></include>
		where address='北京'
		<choose>
			<when test="uname !=null">
				and username =#{uname}
			</when>
			<!-- <otherwise>
			    and userpassword like '%%'
			</otherwise> -->
		</choose>
	</select>

2.当使用set必须保证有一个条件成立 否则sql语法异常(有一定的局限性)

	<!-- update标签 
	第一个if条件成立,第二条件不成立,会自动去掉后面的逗号
	如果两个条件都不成立,set不会出现,但是会报异常,sql语法异常
	至少有一个条件要成立    
	-->
	<update id="updateUser_set"
	        parameterType="java.util.Map">
		update t_user
		<set>
		   <if test="uname!=null">username=#{uname},</if>
		   <if test="upwd!=null">userpassword=#{upwd}</if>
		</set>
		where id=#{uid}
	</update>

3.当使用trim标签必须保证有一个条件成立 否则sql语法异常(有一定的局限性)

<!-- 用trim 替换set
	prefix="SET"  前缀为set
	suffixOverrides="," 后缀覆盖
	第一个if成立,第二 if不成立会去掉后面的逗号
	至少有一个if要成立,否者会语法异常
	 -->
	<update id="updateUser_trim2"
	        parameterType="java.util.Map">
		update t_user
		<trim prefix="SET" suffixOverrides=",">
		   <if test="uname!=null">username=#{uname},</if>
		   <if test="upwd!=null">userpassword=#{upwd}</if>
		</trim>
		where id=#{uid}
	</update>

2.Demo接口测试代码

​
package com.tarena.dao;

import java.security.acl.Group;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

import com.tarena.entity.User;

public interface UserMapper {
	public int addUser(User user);
	public int deleteUser(Integer id);
	public int updateUser(User user);
	
	public User findUserById(Integer id);
	public List<User> findAllUsers();
	
	//动态sql
	public List<User> findAllUsers_if(User user); //不能放单值
	public List<User> findAllUsers_choose(Map data);//Map
	public List<User> findAllUsers_where(Map data);
	public int updateUser_set(Map data);
	public List<User> findAllUsers_trim1(Map data);
	public int updateUser_trim2(Map data);
	public List<User> findAllUsers_foreach(ArrayList ids);
	
	public User findUserById_association(int id);
	public Group findGroupById_collection(int id);
}

​

3.单元测试小案例

package com.tarena.test;

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

import org.apache.ibatis.session.SqlSession;
import org.junit.Test;

import com.tarena.dao.UserMapper;
import com.tarena.entity.Group;
import com.tarena.entity.User;
import com.tarena.util.MyBatisUtil;

public class TestUserMapperClass {
	@Test
	public void testFindUserById(){
		SqlSession sqlSession=null;
		try{
			sqlSession=MyBatisUtil.getSession();
			//用UserMaper接口利用jdk动态代理生成接口儿子代理类,用代理生成代理对象
			//用代理对象调用目标方法,实际上执行的是InvocationHandler中的invoke方法
			//在invoke方法中调用selectOne等原生api方法,来做增删改查
			// 接口类型    jdk动态代理对象=sqlSession.getMapper(接口类型.class);
			UserMapper userMapper=sqlSession.getMapper(UserMapper.class);
			User user=userMapper.findUserById(7);
			System.out.println(user);
			sqlSession.commit();
		}catch(Exception e){
			e.printStackTrace();
			sqlSession.rollback();
		}finally{
			sqlSession.close();
		}
		
	}
	@Test
	public void testFindAllUsers(){
		SqlSession sqlSession=null;
		try{
			sqlSession=MyBatisUtil.getSession();
			UserMapper userMapper=sqlSession.getMapper(UserMapper.class);
			List<User> users=userMapper.findAllUsers();
			for(User  user : users)
			System.out.println(user);
			sqlSession.commit();
		}catch(Exception e){
			e.printStackTrace();
			sqlSession.rollback();
		}finally{
			sqlSession.close();
		}		
	}
	@Test
	public void testAddUser(){
		SqlSession sqlSession=null;
		try{
			sqlSession=MyBatisUtil.getSession();
			UserMapper userMapper=sqlSession.getMapper(UserMapper.class);
			User user=new User();
			user.setName("ff");
			user.setAddress("ff");
			user.setPassword("ff");
			int rowAffect=userMapper.addUser(user);
			System.out.println(rowAffect);
			sqlSession.commit();
		}catch(Exception e){
			e.printStackTrace();
			sqlSession.rollback();
		}finally{
			sqlSession.close();
		}		
	}
	@Test
	public void testDeleteUser(){
		SqlSession sqlSession=null;
		try{
			sqlSession=MyBatisUtil.getSession();
			UserMapper userMapper=sqlSession.getMapper(UserMapper.class);
			
			int rowAffect=userMapper.deleteUser(2);
			System.out.println(rowAffect);
			sqlSession.commit();
		}catch(Exception e){
			e.printStackTrace();
			sqlSession.rollback();
		}finally{
			sqlSession.close();
		}		
	}
	@Test
	public void testUPdateUser(){
		SqlSession sqlSession=null;
		try{
			sqlSession=MyBatisUtil.getSession();
			UserMapper userMapper=sqlSession.getMapper(UserMapper.class);
			User user=new User();
			user.setId(7);
			user.setName("ff");
			user.setAddress("ff");
			user.setPassword("ff");
			int rowAffect=userMapper.updateUser(user);
			System.out.println(rowAffect);
			sqlSession.commit();
		}catch(Exception e){
			e.printStackTrace();
			sqlSession.rollback();
		}finally{
			sqlSession.close();
		}		
	}
	@Test
	public void testFindAllUser_if(){
		SqlSession sqlSession=null;
		try{
			sqlSession=MyBatisUtil.getSession();
			UserMapper userMapper=sqlSession.getMapper(UserMapper.class);
			User user=new User();
			//user.setName("eee");
			List<User> users=userMapper.findAllUsers_if(user);
			for(User u : users){
				System.out.println(u);
			}
			sqlSession.commit();
		}catch(Exception e){
			e.printStackTrace();
			sqlSession.rollback();
		}finally{
			sqlSession.close();
		}		
	}
	@Test
	public void testFindAllUser_choose(){
		SqlSession sqlSession=null;
		try{
			sqlSession=MyBatisUtil.getSession();
			UserMapper userMapper=sqlSession.getMapper(UserMapper.class);
			Map data=new HashMap();
			//data.put("uname", "eee");
			List<User> users=userMapper.findAllUsers_choose(data);
			for(User u : users){
				System.out.println(u);
			}
			sqlSession.commit();
		}catch(Exception e){
			e.printStackTrace();
			sqlSession.rollback();
		}finally{
			sqlSession.close();
		}		
	}
	@Test
	public void testFindAllUser_where(){
		SqlSession sqlSession=null;
		try{
			sqlSession=MyBatisUtil.getSession();
			UserMapper userMapper=sqlSession.getMapper(UserMapper.class);
			Map data=new HashMap();
			//data.put("uname", "eee");
			data.put("uaddress", "北京");
			List<User> users=userMapper.findAllUsers_where(data);
			for(User u : users){
				System.out.println(u);
			}
			sqlSession.commit();
		}catch(Exception e){
			e.printStackTrace();
			sqlSession.rollback();
		}finally{
			sqlSession.close();
		}		
	}
	@Test
	public void testUpdateUser_Set(){
		SqlSession sqlSession=null;
		try{
			sqlSession=MyBatisUtil.getSession();
			UserMapper userMapper=sqlSession.getMapper(UserMapper.class);
			Map data=new HashMap();
			data.put("uid", new Integer(8));
			//data.put("uname", "ffff");
			//data.put("upwd", "ffff");
			int rowAffect=userMapper.updateUser_set(data);
			System.out.println(rowAffect);
			sqlSession.commit();
		}catch(Exception e){
			e.printStackTrace();
			sqlSession.rollback();
		}finally{
			sqlSession.close();
		}		
	}
	@Test
	public void testFindAllUser_trim1(){
		SqlSession sqlSession=null;
		try{
			sqlSession=MyBatisUtil.getSession();
			UserMapper userMapper=sqlSession.getMapper(UserMapper.class);
			Map data=new HashMap();
			//data.put("uname", "eee");
			//data.put("uaddress", "北京");
			List<User> users=userMapper.findAllUsers_trim1(data);
			for(User u : users){
				System.out.println(u);
			}
			sqlSession.commit();
		}catch(Exception e){
			e.printStackTrace();
			sqlSession.rollback();
		}finally{
			sqlSession.close();
		}		
	}
	@Test
	public void testUpdateUser_trim2(){
		SqlSession sqlSession=null;
		try{
			sqlSession=MyBatisUtil.getSession();
			UserMapper userMapper=sqlSession.getMapper(UserMapper.class);
			Map data=new HashMap();
			data.put("uid", new Integer(8));
			//data.put("uname", "f");
			//data.put("upwd", "ffff");
			int rowAffect=userMapper.updateUser_trim2(data);
			System.out.println(rowAffect);
			sqlSession.commit();
		}catch(Exception e){
			e.printStackTrace();
			sqlSession.rollback();
		}finally{
			sqlSession.close();
		}		
	}
	@Test
	public void testFindAllUser_foreach(){
		SqlSession sqlSession=null;
		try{
			sqlSession=MyBatisUtil.getSession();
			UserMapper userMapper=sqlSession.getMapper(UserMapper.class);
			ArrayList ids=new ArrayList();
			ids.add(4);
			ids.add(6);
			ids.add(7);
			List<User> users=userMapper.findAllUsers_foreach(ids);
			for(User u : users){
				System.out.println(u);
			}
			sqlSession.commit();
		}catch(Exception e){
			e.printStackTrace();
			sqlSession.rollback();
		}finally{
			sqlSession.close();
		}		
	}
	@Test
	public void testFindUserById_association(){
		SqlSession sqlSession=null;
		try{
			sqlSession=MyBatisUtil.getSession();
			UserMapper userMapper=sqlSession.getMapper(UserMapper.class);
			
			User user=userMapper.findUserById_association(8);
			
			System.out.println(user);
			
			sqlSession.commit();
		}catch(Exception e){
			e.printStackTrace();
			sqlSession.rollback();
		}finally{
			sqlSession.close();
		}		
	}
	@Test
	public void testFindUserById_collection(){
		SqlSession sqlSession=null;
		try{
			sqlSession=MyBatisUtil.getSession();
			UserMapper userMapper=sqlSession.getMapper(UserMapper.class);
			
			Group group=userMapper.findGroupById_collection(1);
			
			System.out.println(group);
			
			sqlSession.commit();
		}catch(Exception e){
			e.printStackTrace();
			sqlSession.rollback();
		}finally{
			sqlSession.close();
		}		
	}
}

 

 

<?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.tarena.dao.UserMapper">
<!-- namespace必须为包名.接口名 -->
    <!-- resultMap标签 -->
    <resultMap type="User" id="user_map">
    	<!-- 主键 -->
    	<id property="id" column="id"/>
    	<!-- 非主键 -->
    	<result property="name" column="username"/>
    	<result property="password" column="userpassword"/>
    	<result property="address" column="address"/>
    </resultMap>
    <!-- sql标签 -->
    <sql id="select_user" >
        select 
		    id,
		    username,
		    userpassword,
		    address
		from
		t_user
    </sql>
    <!-- 根据id查询一个对象
    id="findUserById"   必须接口中的方法名称
    parameterType="java.lang.Integer"  必须对应接口方法参数类型
    resultType="User"  必须对应接口中的方法的返回类型
     -->
	<select id="findUserById"
	        parameterType="java.lang.Integer"
	        resultMap="user_map" >
		<include refid="select_user"></include>
		   where id=#{id}    
	</select>
	<!-- 查询多个 -->
	<select id="findAllUsers"
	        resultMap="user_map">
		<include refid="select_user"></include>
	</select>
	<insert id="addUser"
	        parameterType="com.tarena.entity.User">
	    insert into t_user
	    (
	    	username,
	    	userpassword,
	    	address
	    )    
	    values
	    (
	    	#{name},
	    	#{password},
	    	#{address}
	    )
	</insert>
	<!-- 删除用户  -->
	<delete id="deleteUser"
	        parameterType="java.lang.Integer">
		delete from t_user where id=#{id}
	</delete>
	<!-- 更新用户信息 -->
	<update id="updateUser"
	        parameterType="com.tarena.entity.User">
		update t_user set
		   username=#{name},
		   userpassword=#{password},
		   address=#{address}
		where 
		   id=#{id}
	</update>
	<!-- 动态sql的开始 -->
	<!-- if标签
	 parameterType="User"  要求是实体对象和map
	 if条件成立连接sql条件
	 -->
	<select id="findAllUsers_if"
	        resultMap="user_map"
	        parameterType="User">
		<include refid="select_user"></include>
		where address='北京'
		<if test="name != null">
		   and username =#{name}
		</if>
	</select>
	<!-- choose when otherwise -->
	<select id="findAllUser s_choose"
	        resultMap="user_map"
	        parameterType="java.util.Map">
		<include refid="select_user"></include>
		where address='北京'
		<choose>
			<when test="uname !=null">
				and username =#{uname}
			</when>
			<otherwise>
			    and userpassword like '%%'
			</otherwise>
		</choose>
	</select>
	<!-- where 标签 
	if的条件,第一个不成,第二成立
	  where and address=#{uaddress}
	  如果mybatis检测到where 后and,则去除and关键字
	   如果if条件都不成立    where关键会去掉
	-->
	<select id="findAllUsers_where"
	        resultMap="user_map"
	        parameterType="java.util.Map">
		<include refid="select_user"></include>
		<where>
			<if test="uname !=null">
				username=#{uname}
			</if>
		    <if test="uaddress !=null">
		    	and address=#{uaddress}
		    </if>
		</where>
		
	</select>
	<!-- update标签 
	第一个if条件成立,第二条件不成立,会自动去掉后面的逗号
	如果两个条件都不成立,set不会出现,但是会报异常,sql语法异常
	至少有一个条件要成立        Mybatis底层写的还是不太好
	-->
	<update id="updateUser_set"
	        parameterType="java.util.Map">
		update t_user
		<set>
		   <if test="uname!=null">username=#{uname},</if>
		   <if test="upwd!=null">userpassword=#{upwd}</if>
		</set>
		where id=#{uid}
	</update>
	<!-- 用trim标签替换where标签 
	  prefix="WHERE" prefix:前缀   where
	  prefixOverrides="AND|OR"    prefixOverrides:前缀覆盖
	  第一个if不成立,第二if成立,会prefixOverrides中设置的值去覆盖
	  都不成立where前缀会去掉
	-->
	<select id="findAllUsers_trim1"
	        resultMap="user_map"
	        parameterType="java.util.Map">
		<include refid="select_user"></include>
		<trim prefix="WHERE" prefixOverrides="AND|OR">
		 	<if test="uname !=null">
				username=#{uname}
			</if>
		    <if test="uaddress !=null">
		    	and address=#{uaddress}
		    </if>
		</trim>
	</select>
	<!-- 用trim 替换set
	prefix="SET"  前缀为set
	suffixOverrides="," 后缀覆盖
	第一个if成立,第二 if不成立会去掉后面的逗号
	至少有一个if要成立,否者会语法异常
	 -->
	<update id="updateUser_trim2"
	        parameterType="java.util.Map">
		update t_user
		<trim prefix="SET" suffixOverrides=",">
		   <if test="uname!=null">username=#{uname},</if>
		   <if test="upwd!=null">userpassword=#{upwd}</if>
		</trim>
		where id=#{uid}
	</update>
	<!-- foreach标签 -->
	<select id="findAllUsers_foreach"
	        parameterType="java.util.ArrayList"
	        resultMap="user_map">
		<include refid="select_user"></include>
		where id in
		<foreach collection="list"
		         item="id"
		         open="("
		         separator=","
		         close=")">
			#{id}
		</foreach>   
	</select>
	<!-- 动态sql的结束 -->
	<!-- mybatis的关联映射查询开始 -->
	<!-- 对一关联 查用户信息 -->
	<resultMap type="User" id="userMap">
		<id property="id" column="uid"/>
		<result property="name" column="username"/>
		<result property="password" column="userpassword"/>
		<result property="address" column="address"/>
		<association property="group" javaType="Group">
			<id property="id" column="gid"/>
			<result property="name" column="groupname"/>
			<result property="loc" column="grouploc"/>
		</association>
	</resultMap>
	<select id="findUserById_association"
	        parameterType="java.lang.Integer"
	        resultMap="userMap">
	   select 
	       u.id uid,
	       u.username,
	       u.userpassword,
	       u.address,
	       g.id gid,
	       g.groupname,
	       g.grouploc 
	   from 
		   (
		   select * from t_user where id=#{id}
		   ) u
	   left join t_group g
	   on u.group_id=g.id
	   
	</select>
	<!--对多关联 查组信息 -->
	<resultMap type="User" id="tempUserMap">
		<id property="id" column="uid"/>
		<result property="name" column="username"/>
		<result property="password" column="userpassword"/>
		<result property="address" column="address"/>
	</resultMap>
	<resultMap type="Group" id="groupMap">
		<id property="id" column="gid"/>
		<result property="name" column="groupname"/>
		<result property="loc" column="grouploc"/>
		<collection property="users"
		            ofType="User"
		            javaType="java.util.List"
		            resultMap="tempUserMap">
			
		</collection>
	</resultMap>
	<select id="findGroupById_collection"
	        parameterType="java.lang.Integer"
	        resultMap="groupMap">
		select 
			g.id gid,
			g.groupname,
			g.grouploc,
			u.id uid,
			u.username,
			u.userpassword,
			u.address
		from
		   (
		      select * from t_group where id=#{id}
		   ) g
		left outer join t_user u
		   on g.id=u.group_id
	</select>
	<!-- mybatis的关联映射查询结束 -->
</mapper>

 

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

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

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

抵扣说明:

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

余额充值