特殊SQL的执行

彭于晏689

已于 2023-06-02 11:05:04 修改

阅读量55

点赞数

分类专栏： SpringBoot 文章标签： sql java 数据库

于 2022-10-05 17:23:48 首次发布

本文链接：https://blog.csdn.net/qq_63140630/article/details/127174631

版权

SpringBoot 专栏收录该内容

12 篇文章 0 订阅

订阅专栏

1.模糊查询

 /**
     * 通过用户名模糊查询用户信息
     * @param mohu
     * @return
     */
    List<User> getUserByLike(@Param("mohu") String mohu);


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

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

2.批量删除

 /**
     * 批量删除
     * @param ids
     */
    void deleteMoreUser(@Param("ids") String ids);

  <!--void deleteMoreUser(@Param("ids") String ids);//ids:9,10-->
    <delete id="deleteMoreUser">
        delete from t_user where id in(${ids})
    </delete>

  @Test
    public void testDeleteMoreUser(){
        SqlSession sqlSession = SqlSessionUtil.getSqlSession();
        SpecialSQLMapper mapper = sqlSession.getMapper(SpecialSQLMapper.class);
        mapper.deleteMoreUser("9,10");
    }

3.动态设置表名

    /**
     * 动态设置表名，查询用户信息
     * @param tableName
     * @return
     */
    List<User> getUserList(@Param("tableName") String tableName);

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

   @Test
    public void testGetUserList(){
        SqlSession sqlSession = SqlSessionUtil.getSqlSession();
        SpecialSQLMapper mapper = sqlSession.getMapper(SpecialSQLMapper.class);
        List<User> list = mapper.getUserList("t_user");
        list.forEach(System.out::println);
    }

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

    /**
     * 添加用户信息并获取自增的主键
     * @param user
     */
    void insertUser(User user);

    <!--void insertUser(User user);-->
    <!--
        useGeneratedKeys:表示当前添加功能使用自增的主键
        keyProperty:将添加的数据的自增主键为实体类类型的参数的属性赋值
    -->
    <insert id="insertUser" useGeneratedKeys="true" keyProperty="id">
        insert into t_user values(null,#{username},#{password},#{age},#{gender},#{email})
    </insert>

 @Test
    public void testInsertUser(){
        SqlSession sqlSession = SqlSessionUtil.getSqlSession();
        SpecialSQLMapper mapper = sqlSession.getMapper(SpecialSQLMapper.class);
        User user = new User(null,"xiaochen","123456789",22,"男","12306@qq.com");
        mapper.insertUser(user);
        System.out.println(user);
    }