MyBatis框架学习-特殊SQL的执行(处理模糊查询、批量删除、动态设置表明、获取添加功能自增的主键)-5

1、处理模糊查询

package com.rjs.mybatis.mapper;

import com.rjs.mybatis.pojo.User;
import org.apache.ibatis.annotations.Param;

import java.util.List;

/**
 * @author: 软件手
 * @date: 2022/7/23 23:52
 * @description:
 */
public interface SQLMapper {

    // 1、根据用户名模糊查询用户信息
    List<User> getUserByLike(@Param("username") String username);
}

import com.rjs.mybatis.mapper.SQLMapper;
import com.rjs.mybatis.pojo.User;
import com.rjs.mybatis.utils.SqlSessionUtils;
import org.apache.ibatis.session.SqlSession;
import org.junit.Test;

import java.util.List;

/**
 * @author: 软件手
 * @date: 2022/7/23 23:55
 * @description:
 */
public class SQLMapperTest {

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

占位符失效

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

第一种方式

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

第二种方式

    <select id="getUserByLike" resultType="User">
        <!--select * from t_user where username like '%${username}%'-->
        select * from t_user where username like concat('%',#{username},'%')
    </select>

第三种方式

<!--List<User> getUserByLike(@Param("username") String username);-->
    <select id="getUserByLike" resultType="User">
        <!--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}"%"
    </select>

2、批量删除

package com.rjs.mybatis.mapper;

import com.rjs.mybatis.pojo.User;
import org.apache.ibatis.annotations.Param;

import java.util.List;

/**
 * @author: 软件手
 * @date: 2022/7/23 23:52
 * @description:
 */
public interface SQLMapper {

    // 1、根据用户名模糊查询用户信息
    List<User> getUserByLike(@Param("username") String username);

    // 2、批量删除
    int deleteMore(@Param("ids") String ids);
}

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

不能使用# 因为 SQL 的 in 会自动加上 " "

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

使用${}
删除 id 为 1，2，3的，因为只有3，所以只删除了一条数据

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

3、动态设置表明

  // 3、查询指定表中的数据
    List<User> getUserByTableName(@Param("tableName") String tableName);

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

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

表名不能加’’ 单引号

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

用useGeneratedKeys表示能返回主键,用keyProperty表示主键返回到什么属性上
useGeneratedKeys:设置当前标签总的sql使用了自增的主键
keyProperty:将自增的主键的值赋值给传输到映射文件中参数的某个属性

 // 4、添加用户信息
    void insertUser(User user);

<!--
        void insertUser(User user);
        用useGeneratedKeys表示能返回主键,用keyProperty表示主键返回到什么属性上
        useGeneratedKeys:设置当前标签总的sql使用了自增的主键
        keyProperty:将自增的主键的值赋值给传输到映射文件中参数的某个属性
    -->
    <insert id="insertUser" useGeneratedKeys="true" keyProperty="id">
        insert into t_user values(null,#{username},#{password},#{age},#{sex},#{email})
    </insert>

 @Test
    public void testInsertUser(){
        SqlSession sqlSession = SqlSessionUtils.getSqlSession();
        SQLMapper mapper = sqlSession.getMapper(SQLMapper.class);
        User user = new User(null,"dark","123",23,"a","8512@qq.com");
        mapper.insertUser(user);
        System.out.println(user);
    }

去调 useGeneratedKeys=“true” keyProperty=“id” ，返回的id 为 空

<insert id="insertUser" >
        insert into t_user values(null,#{username},#{password},#{age},#{sex},#{email})
    </insert>