mybatis中`${}`使用【特殊SQL】

由于模糊查询传入是一个字符串

方式一：注意是一定不能使用#{}没有拼接字符串的作用，会直接当作为%#{值}%一个字符串，所以会报错

select * from t_user where username like '%${username}%';

使用concat函数，来拼接字符串

 select * from t_user where username like concat('%',#{username},'%');

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

案例：

Mapper接口

// 模糊查询,由于模糊查询返回的值可能为多个所以用List接收
    List getUserLike(@Param("username")String username);

Mapper.xml文件

    
        select * from t_user where username like '%${username}%';
        select * from t_user where username like concat('%',#{username},'%');
        select * from t_user where username like "%"#{username}"%";
    

test类

// 模糊查询
    @Test
    public void testGetUserLike(){
        SqlSession sqlSession = SqlSessionUtils.getSqlSession();
        SQLMapper mapper = sqlSession.getMapper(SQLMapper.class);
        List list = mapper.getUserLike("明");
        System.out.println(list);
    }

注意是：如果在批量删除时，传入的参数是拼接的字符串，则只能使用${}方式，并且不要添加单引号

案例：

Mapper接口

// 批量删除
    int deleteMoreByID(@Param("ids") String ids);

Mapper.xml文件

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

test类

 // 批量删除
    @Test
    public void testDeleteMoreByID(){
        SqlSession sqlSession = SqlSessionUtils.getSqlSession();
        SQLMapper mapper = sqlSession.getMapper(SQLMapper.class);
        int result = mapper.deleteMoreByID("3,5,6");
        System.out.println(result);
    }

动态设置表名查询数据

Mapper接口

// 动态设置表名，由于查询所有用户信息List getAllUserByTableName(@Param("tableName")String tableName);

Mapper.xml文件

    
        select * from ${tableName};
    
 
test类
 
 // 动态设置表名查询信息
    @Test
    public void testGetAllUserByTableName(){
        SqlSession sqlSession = SqlSessionUtils.getSqlSession();
        SQLMapper mapper = sqlSession.getMapper(SQLMapper.class);
        List list = mapper.getAllUserByTableName("t_user");
        System.out.println(list);
    }
 
4.添加功能获取自增的主键【非常重要】 
应用场景：
 
t_clazz(clazz_id,class_name):班级表
 
t_studen(student_id,student_name,clazz_id):学生表
 
例如：
 
在添加班级信息
获取新添加班级的id
为班级分配学生，即将某学生的班级id修改为新添加的班级的id
 
添加功能获取自增的主键需要在mapper.xml文件的insert标签中设置两个属性：
 
 
useGeneratedKeys：表示设置当前标签中的sql使用了自增的主键，设置值为true
 
 
keyProperty：表示将自增的主键的值赋值给传入到映射文件中的参数的某个属性这【意思就是将获取的主键值，为传入的某个属性赋值】【如下案例：】
 
例如：获取到的主键值，为属性`id`赋值

	
        insert into t_user values(null,#{username},#{password},#{age},#{sex},#{email})
    
 
 
Mapper接口
 
// 添加功能获取自增的主键
    int insertUser(User user);
 
Mapper.xml文件
 

    
        insert into t_user values (null,#{username},#{password},#{age},#{sex},#{email});
    
 
test类
 
// 添加功能获取自增的主键
    @Test
    public void testInsertUser(){
        SqlSession sqlSession = SqlSessionUtils.getSqlSession();
        SQLMapper mapper = sqlSession.getMapper(SQLMapper.class);
        User user = new User(null,"西安","123",22,"男","123@qq.com");
        mapper.insertUser(user);
        System.out.println(user.getId());//可以看到传入的id为null,keyProperty赋值后,id就有值了
    }
 
综合 
Mapper接口
 
public interface SQLMapper {
    // 模糊查询,由于模糊查询返回的值可能为多个所以用List接收
    List getUserLike(@Param("username")String username);


    // 批量删除
    int deleteMoreByID(@Param("ids") String ids);

    // 动态设置表名，由于查询所有用户信息
    List getAllUserByTableName(@Param("tableName")String tableName);

    // 添加功能获取自增的主键
    int insertUser(User user);
}

 
Mapper.xml文件
 


    
        select * from t_user where username like '%${username}%';
        select * from t_user where username like concat('%',#{username},'%');
        select * from t_user where username like "%"#{username}"%";
    

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

    
        select * from ${tableName};
    


    
        insert into t_user values (null,#{username},#{password},#{age},#{sex},#{email});
    

test类

public class TestSQLMapper {
    // 添加功能获取自增的主键
    @Test
    public void testInsertUser(){
        SqlSession sqlSession = SqlSessionUtils.getSqlSession();
        SQLMapper mapper = sqlSession.getMapper(SQLMapper.class);
        User user = new User(null,"西安","123",22,"男","123@qq.com");
        mapper.insertUser(user);
        System.out.println(user.getId());//可以看到传入的id为null,keyProperty赋值后,id就有值了
    }

    // 动态设置表名查询信息
    @Test
    public void testGetAllUserByTableName(){
        SqlSession sqlSession = SqlSessionUtils.getSqlSession();
        SQLMapper mapper = sqlSession.getMapper(SQLMapper.class);
        List list = mapper.getAllUserByTableName("t_user");
        System.out.println(list);
    }

    // 批量删除
    @Test
    public void testDeleteMoreByID(){
        SqlSession sqlSession = SqlSessionUtils.getSqlSession();
        SQLMapper mapper = sqlSession.getMapper(SQLMapper.class);
        int result = mapper.deleteMoreByID("3,5,6");
        System.out.println(result);
    }

    // 模糊查询
    @Test
    public void testGetUserLike(){
        SqlSession sqlSession = SqlSessionUtils.getSqlSession();
        SQLMapper mapper = sqlSession.getMapper(SQLMapper.class);
        List list = mapper.getUserLike("明");
        System.out.println(list);
    }
}