【MyBatis06】模糊查询 批量删除 动态设置表名 添加功能获取自增的主键

模糊查询

SqlMapper接口

package com.sdjzu.mybatis.mapper;

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

import java.util.List;

public interface SqlMapper {
    /*根据用户名模糊查询*/
    List<User> getUserByLike(@Param("username")String username);
}

SqlMpper.xml

<?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="com.sdjzu.mybatis.mapper.SqlMapper">
    <!--List<User> getUserByLike(String str);-->
    <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>

</mapper>

测试类

package com.sdjzu.mybatis.test;

import com.sdjzu.mybatis.mapper.SqlMapper;
import com.sdjzu.mybatis.pojo.User;
import com.sdjzu.mybatis.utils.SqlSessionUtils;
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.getUserByLike("a");
        System.out.println("a = " + a);
    }
}

批量删除(只能用${})

SqlMapper接口

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

SqlMapper.xml

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

    @Test
    public void testDeleteUsers(){
        SqlSession sqlSession = SqlSessionUtils.getSqlSession();
        SqlMapper mapper = sqlSession.getMapper(SqlMapper.class);
        int i = mapper.deleteUsers("5,9");
        System.out.println("i = " + i);
    }

动态设置表名(只能用${})

SqlMapper接口

    /*
    * 根据表名查询*/
    List<User> getUserByTableName(@Param("TableName") String TableName);

SqlMapper.xml

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

测试类

    @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);
    }

添加功能获取自增的主键

SqlMapper接口

    /*
    * 添加用户*/
    void insertUser(User user);

SqlMapper.xml

  <!-- void insertUser(User user);-->
    <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, "jay", "123", "男", "24", "123@qq.com");
        mapper.insertUser(user);
        System.out.println("user = " + user);
    }