特殊SQL语句

提示：文章写完后，目录可以自动生成，如何生成可参考右边的帮助文档

---

前言

mybatis学习记录
05. 特殊SQL语句
使用 ${} 的几种情况：

---

提示：以下是本篇文章正文内容，下面案例可供参考

一、模糊查询

like后面三种方式
  “%”#{username}"%"（最常用）
  ‘%${username}%’
  concat(’%’,#{username},’%’)
测试方法：

	@Test
    public void testGetUserByLike(){
        SqlSession sqlSession = SqlSessionUtils.getSqlSession();
        SQLMapper mapper = sqlSession.getMapper(SQLMapper.class);
        List<User> list = mapper.getUserByLike("邓");
        System.out.println(list);
    }

mapper接口：

public interface SQLMapper {
	List<User> getUserByLike(@Param("username") String username);
}

映射文件：

<?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="study.mapper.SQLMapper">

	<!--List<User> getUserByLike(@Param("username") String username);-->
    <select id="getUserByLike" resultType="User">
        select * from t_user where username like "%"#{username}"%"
    </select>
    
</mapper>

---

二、批量删除

测试方法：

 	@Test
    public void testDeleteMore(){
        SqlSession sqlSession = SqlSessionUtils.getSqlSession();
        SQLMapper mapper = sqlSession.getMapper(SQLMapper.class);
        int result = mapper.deleteMore("10,12,14");
        System.out.println(result);
    }

mapper接口：

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

映射文件：

    <!--int deleteMore(@Param("ids") String ids);-->
    <delete id="deleteMore">
        delete from t_user where id in (${ids})
    </delete>

---

三、设置动态表名查询数据

测试方法：

	@Test
    public void testGetUserByTableName(){
        SqlSession sqlSession = SqlSessionUtils.getSqlSession();
        SQLMapper mapper = sqlSession.getMapper(SQLMapper.class);
        List<User> list = mapper.getUserByTableName("t_user");
        System.out.println(list);
    }

mapper接口：

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

映射文件：

    <!--List<User> getUserByTableName(@Param("tableName") String tableName);-->
    <select id="getUserByTableName" resultType="User">
        select * from ${tableName}
    </select>

四、添加功能获取自增主键

void insertUser(User user);
 useGeneratedKeys:设置当前标签中的sql使用了自增的主键
 keyProperty：将自增的主键的值赋值给传输到映射文件中参数的某个属性
测试方法：

	@Test
    public void testInsertUser(){
        SqlSession sqlSession = SqlSessionUtils.getSqlSession();
        SQLMapper mapper = sqlSession.getMapper(SQLMapper.class);
        User user = new User(null,"jack","36952",20,"女","123456481358");
        mapper.insertUser(user);
        System.out.println(user);
    }

mapper接口：

void insertUser(User user);

映射文件：

    <!--void insertUser(User user);-->
    <insert id="insertUser" useGeneratedKeys="true" keyProperty="id">
        insert into t_user values(null,#{username},#{password},#{age},#{sex},#{phone})
    </insert>

---

总结