特殊SQL的执行

冷不防 2024-04-06 14:05 85阅读 0赞

#### 1、模糊查询   select \* from users where name like '%$\{name\}%'   select \* from users where name like concat('%',\#\{name\},'%')   select \* from users where name like '%'\#\{name\}'%' ####

/*
         * 根据用户名模糊查询用户信息
         * */
        List<Users> getUserByLike(@Param("name") String name);
    
    
    
    
    
        <select id="getUserByLike" resultType="Users">
            select * from users where name like '%${name}%'
        </select>
    
    
    //模糊查询
        @Test
        public void  testGetUserByLike(){
            SqlSession  sqLsession = SQLsession.getSqlsession();
            SQLMapper mapper = sqLsession.getMapper(SQLMapper.class);
            List<Users> users = mapper.getUserByLike("T");
            System.out.println(users);
        }

#### 2、批量删除 不能使用\#\{\} delete from users where name in($\{name\}) ####

/*
        * 批量删除
        * */
        int deleteUserByName(@Param("id") String id);
    
    
    
    <delete id="deleteUserByName">
            delete from users where id in(${id})
    </delete>
    
    
    
    
    //批量删除
        @Test
        public void  testdeleteUserByName(){
            SqlSession  sqLsession = SQLsession.getSqlsession();
            SQLMapper mapper = sqLsession.getMapper(SQLMapper.class);
             int re = mapper.deleteUserByName("1548237766262165505,3");
            System.out.println(re);
        }

#### 3、动态查询表名 select \* from $\{tablename\} ####

/*
        * 动态获取表名
        * */
        List<Users> getUserByTableName(@Param("tablename")String tablename);
    
    
    
    <select id="getUserByTableName" resultType="Users">
            select * from  ${tablename}
    </select>
    
    
    
    
    //动态查询表名
        @Test
        public void  testgetUserByTableName(){
            SqlSession  sqLsession = SQLsession.getSqlsession();
            SQLMapper mapper = sqLsession.getMapper(SQLMapper.class);
            List<Users> re = mapper.getUserByTableName("Users");
            System.out.println(re);
        }

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

#### 应用场景： ####

#### 添加班级信息， ####

#### 获取新加的班级的id， ####

#### 为班级分配学生，即将某学的班级id修改为新添加的班级的id ####

/*
        主键自增测试
        * 添加用户信息
        * */
        int insertUser(Users users);
    
    
    <insert id="insertUser" useGeneratedKeys="true" keyProperty="id">
            insert into  users values ('2022-08-16 12:15:23',null ,18,'小双双',22,'helen@sina.com',1,0)
    </insert>
    
    
     @Test
        public void testinsertUser(){
            SqlSession sqlsession = SQLsession.getSqlsession();
            UserMapper mapper = sqlsession.getMapper(UserMapper.class);
            int user = mapper.insertUser(new Users(null,null ,0,"shang",12,"12233",0,0));
            System.out.println(user);
    
        }