特殊SQL的执行

模糊查询

/**

 * 根据用户名进行模糊查询

 * @param username

 * @return java.util.List<com.atguigu.mybatis.pojo.User>

 * @date 2022/2/26 21:56

 */

List<User> getUserByLike(@Param("username") String username);

<!--List<User> getUserByLike(@Param("username") String username);-->

<select id="getUserByLike" resultType="User">

	<!--select * from t_user where username like '%${mohu}%'-->

	<!--select * from t_user where username like concat('%',#{mohu},'%')-->

	select * from t_user where username like "%"#{mohu}"%"

</select>

其中select * from t_user where username like "%"#{mohu}"%"是最常用的

批量删除

只能使用${}，如果使用#{}，则解析后的sql语句为delete from t_user where id in ('1,2,3')，这样是将1,2,3看做是一个整体，只有id为1,2,3的数据会被删除。正确的语句应该是delete from t_user where id in (1,2,3)，或者delete from t_user where id in ('1','2','3')

/**

 * 根据id批量删除

 * @param ids

 * @return int

 * @date 2022/2/26 22:06

 */

int deleteMore(@Param("ids") String ids);

<delete id="deleteMore">

	delete from t_user where id in (${ids})

</delete>

//测试类

@Test

public void deleteMore() {

	SqlSession sqlSession = SqlSessionUtils.getSqlSession();

	SQLMapper mapper = sqlSession.getMapper(SQLMapper.class);

	int result = mapper.deleteMore("1,2,3,8");

	System.out.println(result);

}

动态设置表名

只能使用${}，因为表名不能加单引号

/**

 * 查询指定表中的数据

 * @param tableName

 * @return java.util.List<com.atguigu.mybatis.pojo.User>

 * @date 2022/2/27 14:41

 */

List<User> getUserByTable(@Param("tableName") String tableName);

<!--List<User> getUserByTable(@Param("tableName") String tableName);-->

<select id="getUserByTable" resultType="User">

	select * from ${tableName}

</select>

测试类代码

package com.atguigu.mybatis.test;

import com.atguigu.mybatis.mapper.SqlMapper;

import com.atguigu.mybatis.pojo.user;

import com.atguigu.mybatis.utils.sqlSessionUtils;

import org.apache.ibatis.annotations.Param;

import org.apache.ibatis.session.SqlSession;

import org.junit.Test;

import java.util.List;

public class SQLMapperTest {

    @Test

    public void testGetUserByLike(){

        SqlSession sqlSession = sqlSessionUtils.GetSqlSession();

        SqlMapper mapper = sqlSession.getMapper(SqlMapper.class);

        List<user> a = mapper.getUserByLike1("a");

        System.out.println(a);

    }

    @Test

    public void testDeleteMore(){

        SqlSession sqlSession = sqlSessionUtils.GetSqlSession();

        SqlMapper mapper = sqlSession.getMapper(SqlMapper.class);

        int resoult = mapper.deleteMore("1,2,3");

        System.out.println(resoult);

    }

    @Test

    public void testGetUserByTableName(){

        SqlSession sqlSession = sqlSessionUtils.GetSqlSession();

        SqlMapper mapper = sqlSession.getMapper(SqlMapper.class);

        List<user> t_user = mapper.getUserByTableName("t_user");

        System.out.println(t_user);

    }

}

巴特西

mybatis-特殊查询

特殊SQL的执行

模糊查询

批量删除

动态设置表名

测试类代码

最新文章

热门文章