值得一看——Mybatis动态sql的使用——不是很深但是很细

最新推荐文章于 2024-04-21 09:42:23 发布

仰望天花板

最新推荐文章于 2024-04-21 09:42:23 发布

阅读量1.1k

点赞数

分类专栏： javaEE互联网轻量级开发笔记文章标签： java 开发语言后端 mybatis mysql

本文链接：https://blog.csdn.net/m0_56058975/article/details/122674566

版权

javaEE互联网轻量级开发笔记专栏收录该内容

14 篇文章

订阅专栏

在实际开发时，如需要根据姓名或者备注去查询时，当姓名为空再去使用姓名作为条件查询时就不太合适了，这时候通常其他框架或java代码去判断，但可读性差。Mybatis提供了动态sql，仅仅需要几个XML的元素即可完成，虽然Mybatis也提供了注解的形式，但对于较为复杂的sql而言可读性太差，所以不推荐使用。

动态SQL实际使用的元素并不多，但是它们带来了灵活性，减少许多工作量的同时，也在很大程度上提高了程序的可读性和可维护性。

1.创建POJO及Mapper

@Data
public class Role implements Serializable{
	private Long id;
	private String roleName;
	private String note;
}

public interface RoleMapper {
   Role getRole(long id);
   Role FindRole(String roleName);
   Role FindRole01(Role role);
   Role FindRole02(Role role);
   Role FindRole03(Role role);
   int UpdateRole(Role role);
   List<Role> findRoleByNums(@Param("roleList") List<Long> roleList);
   List<Role> getRoleTest();
   List<Role> BindRole(@Param("name") String name,@Param("note") String note);
}

2.if元素

if元素是经常使用的元素之一，假如我们需要一个名字去查询，当传入参数值为空则不构成条件，不为空则进行模糊查询

 <select id="FindRole" resultMap="RoleMap">
        select id,role_name,note from t_role where 1=1
        <if test="roleName !=null and roleName !=''">
            and role_name like concat('%',#{roleName},'%')
        </if>
    </select>

执行：

public static void FindRole(){
        SqlSession sqlSession=null;
        try {
            Logger logger=Logger.getLogger(Mybatis04Test.class);
            sqlSession=SqlSessionFactoryUtils.openSqlSession();
            RoleMapper roleMapper=sqlSession.getMapper(RoleMapper.class);
            Role role=roleMapper.FindRole("role_name_1");
            logger.debug(role);
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            if (sqlSession!=null){
                sqlSession.close();
            }
        }
    }

但是需要注意的是如参数值为空不构成条件又返回多条，或模糊查询到多条则会报：Expected one result (or null) to be returned by selectOne(), but found: 43

org.apache.ibatis.exceptions.TooManyResultsException: Expected one result (or null) to be returned by selectOne(), but found: 4
	at org.apache.ibatis.session.defaults.DefaultSqlSession.selectOne(DefaultSqlSession.java:80)
	at org.apache.ibatis.binding.MapperMethod.execute(MapperMethod.java:87)
	at org.apache.ibatis.binding.MapperProxy$PlainMethodInvoker.invoke(MapperProxy.java:152)
	at org.apache.ibatis.binding.MapperProxy.invoke(MapperProxy.java:85)
	at com.sun.proxy.$Proxy5.FindRole(Unknown Source)

3.choose、when、otherwise元素

if承担了是或不是两种条件的判断（如java的if），而choose、when、otherwise则承担了分支条件的判断（如switch...case...default）

假如需要查询一条用户数据，如id不为空则用id查询，如为空则用户名字查询，如名字为空则备注不为空

<choose>元素是开始，<when>元素判断不成功，则条件不会构造，而<otherwise>则是默认

<select id="FindRole01" resultMap="RoleMap">
        select id,role_name,note from t_role where 1=1
        <choose>
            <when test="id !=null and id !=''">
                and id=#{id}
            </when>
            <when test="roleName !=null and roleName !=''">
                and role_name like concat('%',#{roleName},'%')
            </when>
            <otherwise>
                and note is not null
            </otherwise>
        </choose>
    </select>

测试：

//动态sql - choose when otherwise
    public static void FindRole01(){
        SqlSession sqlSession=null;
        try {
            Role role=new Role();
            Logger logger=Logger.getLogger(Mybatis04Test.class);
            sqlSession=SqlSessionFactoryUtils.openSqlSession();
            RoleMapper roleMapper=sqlSession.getMapper(RoleMapper.class);
            role.setRoleName("role_name_1");
           // role.setId(1l); //id为空
            Role role1=roleMapper.FindRole01(role);
            logger.debug(role1);
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            if (sqlSession!=null){
                sqlSession.close();
            }
        }
    }

日志：如id为空则用姓名查询

4.trim、where、

在之前的XML中我们都使用了where1=1的条件，使用会看着别扭，但不使用则会报错，而XML中的where元素可以达到我们预期效果

 <select id="FindRole02" resultMap="RoleMap">
        select id,role_name,note from t_role
        <where>
            <if test="roleName !=null and roleName !=''">
                and role_name like concat('%',#{roleName},'%')
            </if>
            <if test="note !=null and note !=''">
                and note like concat('%',#{note},'%')
            </if>
        </where>
    </select>

测试：

 //动态sql - where
    public static void FindRole02(){
        SqlSession sqlSession=null;
        try {
            Role role=new Role();
            Logger logger=Logger.getLogger(Mybatis04Test.class);
            sqlSession=SqlSessionFactoryUtils.openSqlSession();
            RoleMapper roleMapper=sqlSession.getMapper(RoleMapper.class);
            role.setRoleName("role_name_1");
            role.setNote("1111");
            Role role1=roleMapper.FindRole02(role);
            logger.debug(role1);
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            if (sqlSession!=null){
                sqlSession.close();
            }
        }
    }

日志：

注意：我们虽然在XML中的where元素后加了and元素，但是<where>元素会自己判断，条件值不为空才会使用<where>，如果<where>元素跟的是and或者or，<where>元素会自己去除，如果特殊情况没有去除，可以使用自定义元素<trim>去除

<select id="FindRole03" resultMap="RoleMap">
        select id,role_name,note from t_role
        <trim prefix="where" prefixOverrides="and">
            <if test="roleName !=null and roleName !=''">
                and role_name like concat('%',#{roleName},'%')
            </if>
        </trim>
    </select>

trim 元素意味着要去掉一些特殊的字符串，当时 prefix 代表的是语句的前缀，而prefixOverrides代表的是需要去掉哪种字符串。上面的写法基本与where是等效的。

5.set元素

当想更新某值时可以使用<set>元素

 <update id="UpdateRole">
        update t_role
    <set>
        <if test="roleName !=null and roleName !=''">
            role_name=#{roleName},
        </if>
        <if test="note !=null and note !=''">
            note=#{note},
        </if>
    </set>
     where id=#{id}
    </update>

测试：

//动态sql - set
    public static void updateRole(){
        SqlSession sqlSession=null;
        try {
            Role role=new Role();
            Logger logger=Logger.getLogger(Mybatis04Test.class);
            sqlSession=SqlSessionFactoryUtils.openSqlSession();
            RoleMapper roleMapper=sqlSession.getMapper(RoleMapper.class);
            role.setRoleName("111");
            role.setNote("123");
            role.setId(3l);
            int role1=roleMapper.UpdateRole(role);
            sqlSession.commit();
            logger.debug(role1);
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            if (sqlSession!=null){
                sqlSession.close();
            }
        }
    }

日志：

<set>元素如果遇到逗号会自动去除，如果只想更新某一个字段则只需传入需要更新的参数即可

6.foreach元素

foreach元素是一个循环语句，它的作用是遍历集合，它能够很好地支持数组和List、Set接口的集合，对此提供遍历功能。它往往用于SQL中的in关键字。

<select id="findRoleByNums" resultMap="RoleMap">
        select id,role_name,note from t_role where id in
    <foreach collection="roleList" index="index" item="id" open="(" separator="," close=")">
        #{id}
    </foreach>

    </select>

注意：

●collection配置的roleNoList是传递进来的参数名称，它可以是一个数组、List、Set等集合。
●item配置的是循环中当前的元素。
●index配置的是当前元素在集合的位置下标

●open和close配置的是以什么符号将这些集合元素包装起来。
●separator是各个元素的间隔符。

有大量数据使用in需要注意，in会消耗大量性能

测试：

//动态sql - foreach
    public static void findRoleByNums(){
        SqlSession sqlSession=null;
        try {

            Role role=new Role();
            List<Long> roleList=new ArrayList<>();
            roleList.add(1l);
            roleList.add(2l);
            roleList.add(3l);
            roleList.add(4l);
            System.out.println(roleList.toArray());
            Logger logger=Logger.getLogger(Mybatis04Test.class);
            sqlSession=SqlSessionFactoryUtils.openSqlSession();
            RoleMapper roleMapper=sqlSession.getMapper(RoleMapper.class);
            List<Role> roleList1=roleMapper.findRoleByNums(roleList);
            logger.debug(roleList1);
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            if (sqlSession!=null){
                sqlSession.close();
            }
        }
    }

日志：

7.用test的属性判断字符串

test在大多数时候判断空或非空，有时候需要判断字符串，数字，枚举等

<select id="getRoleTest" resultMap="RoleMap">
        select id,role_name,note from t_role
        <if test="type=='Y'.toString()">
            where 1=1
        </if>
    </select>

注意：判断字符串需要toString()方法,如果两者相等将会加上where1=1的条件，如果是枚举类型则取决使用哪种typeHander，有兴趣可以看看一分钟明白：mybatis自定义别名的三种方式和自定义TypeHandler类型转换器

8.bind元素

在进行模糊查询时，如果是MySQL数据库，常常用到的是一个concat，它用“%”和参数相连。然而在Oracle数据库则没有，Oracle数据库用连接符号“||”，这样SQL就需要提供两种形式去实现。而<bind>元素可以不再使用数据库语言

<select id="BindRole" resultMap="RoleMap">
        <bind name="name" value="'%' + name + '%'"/>
        <bind name="note" value="'%' + note + '%'"/>
        select id,role_name,note from t_role where role_name like #{name} and note like #{note}
    </select>

注意：values中的name是参数传递进来的值，与'%'连接后赋值给'name'，这样就提高了代码的移植性