特殊Sql执行

• 1. 模糊查询
• 2. 批量删除
• 3. 动态设置表名
• 4. 添加功能获取自增的主键
• 5. 整体代码

1. 模糊查询

  /*** 根据用户名进行模糊查询* @param moHu* @return*/List getUserByLike(@Param("moHu") String moHu);

   

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

注意：‘${name}’ = ‘name’, #{name} = name, 一个加引号，一个不加，要求sql功底一定要好，知道哪些添加引号，哪些不可以添加。

测试方法

 @Testpublic void testGetUserByLike(){SqlSession sqlSession = SqlSessionFactoryUtils.getSqlSession();SqlMapper sqlMapper = sqlSession.getMapper(SqlMapper.class);List userList = sqlMapper.getUserByLike("sm");for(User user : userList){System.out.println(user);}sqlSession.close();}

2. 批量删除

只能使用${}，如果使用#{}，则解析后的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 deleteMore(@Param("ids") String ids);

    


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

测试方法

    @Testpublic void testDeleteMore() throws IOException {InputStream is = Resources.getResourceAsStream("mybatis-config.xml");SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(is);SqlSession sqlSession = sqlSessionFactory.openSession(true);SqlMapper sqlMapper = sqlSession.getMapper(SqlMapper.class);int count = sqlMapper.deleteMore("22, 23, 24");System.out.println("影响行数：" + count);sqlSession.close();}

3. 动态设置表名

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

    /*** 查询指定表中的数据* @param tableName* @return*/List getUserByTable(@Param("tableName") String tableName);

    select * from ${tableName}

测试方法

 @Testpublic void testGetUserByTable() throws IOException {InputStream is = Resources.getResourceAsStream("mybatis-config.xml");SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(is);SqlSession sqlSession = sqlSessionFactory.openSession(true);SqlMapper sqlMapper = sqlSession.getMapper(SqlMapper.class);// 查询t_user表的数据List userList = sqlMapper.getUserByTable("t_user");for(User user : userList){System.out.println(user);}sqlSession.close();}

4. 添加功能获取自增的主键

功能：获取插入数据自增主键的id，便于后续程序的操作

• 在mapper.xml中设置两个属性

• useGeneratedKeys：设置使用自增的主键

• keyProperty：因为增删改有统一的返回值是受影响的行数，因此只能将获取的自增的主键放在传输的参数user对象的某个属性中

    /*** 添加用户信息* @param user*/void insert(User user);

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

测试方法

    @Testpublic void testInsert(){SqlSession sqlSession = SqlSessionFactoryUtils.getSqlSession();SqlMapper mapper = sqlSession.getMapper(SqlMapper.class);// 可以获取插入数据的idUser user = new User(null, "Tom", "123", 23, "男", "1235@qq.com");mapper.insert(user);System.out.println(user);sqlSession.close();}

进入数据库中进行查看

5. 整体代码

SqlMapper接口

package com.atguigu.mapper;import com.atguigu.pojo.User;
import org.apache.ibatis.annotations.Param;import java.util.List;/*** @Author Mr.Lu* @Date 2022/12/2 15:20* @ClassName SqlMapper* @Version 1.0*/
public interface SqlMapper {/*** 根据用户名进行模糊查询* @param moHu* @return*/List getUserByLike(@Param("moHu") String moHu);/*** 根据id进行批量删除* @param ids* @return*/int deleteMore(@Param("ids") String ids);/*** 查询指定表中的数据* @param tableName* @return*/List getUserByTable(@Param("tableName") String tableName);/*** 添加用户信息* @param user*/void insert(User user);
}

SqlMapper接口对应的SqlMapper.xml

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




delete from t_user where id in (${ids})select * from ${tableName}insert into t_user values(null, #{username}, #{password}, #{age}, #{sex}, #{email})

MybatisSqlTest测试类

package com.atguigu.test;import com.atguigu.mapper.SqlMapper;
import com.atguigu.pojo.User;
import com.atguigu.util.SqlSessionFactoryUtils;
import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
import org.junit.Test;import java.io.IOException;
import java.io.InputStream;
import java.util.List;/*** @Author Mr.Lu* @Date 2022/12/2 15:21* @ClassName MybatisSqlTest* @Version 1.0*/
public class MybatisSqlTest {@Testpublic void testGetUserByLike(){SqlSession sqlSession = SqlSessionFactoryUtils.getSqlSession();SqlMapper sqlMapper = sqlSession.getMapper(SqlMapper.class);List userList = sqlMapper.getUserByLike("sm");for(User user : userList){System.out.println(user);}sqlSession.close();}@Testpublic void testDeleteMore() throws IOException {InputStream is = Resources.getResourceAsStream("mybatis-config.xml");SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(is);SqlSession sqlSession = sqlSessionFactory.openSession(true);SqlMapper sqlMapper = sqlSession.getMapper(SqlMapper.class);int count = sqlMapper.deleteMore("7,8,9");System.out.println("影响行数：" + count);sqlSession.close();}@Testpublic void testGetUserByTable() throws IOException {InputStream is = Resources.getResourceAsStream("mybatis-config.xml");SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(is);SqlSession sqlSession = sqlSessionFactory.openSession(true);SqlMapper sqlMapper = sqlSession.getMapper(SqlMapper.class);List userList = sqlMapper.getUserByTable("t_user");for(User user : userList){System.out.println(user);}sqlSession.close();}@Testpublic void testInsert(){SqlSession sqlSession = SqlSessionFactoryUtils.getSqlSession();SqlMapper mapper = sqlSession.getMapper(SqlMapper.class);// 可以获取插入数据的idUser user = new User(null, "ton", "123", 23, "男", "1235@qq.com");mapper.insert(user);System.out.println(user);sqlSession.close();}
}