7、特殊SQL的执行

7、特殊SQL的执行

7.1、模糊查询

    //模糊查询
    List<User> getUserByLike(@Param("mohu") String mohu);

    <!--List<User> getUserByLike(@Param("username") String mohu);-->
    <select id="getUserByLike" resultType="user">
        <!--select * from t_user where username like '%#{mohu}%'  字符串拼接报错-->
        <!--正确方式如下：-->
        <!--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 GetUserByLike(){
        SqlSession sqlSession = sqlSessionUtil.getSqlSession();
        SpecialSQLMapper mapper = sqlSession.getMapper(SpecialSQLMapper.class);
//        for (User user : mapper.getUserByLike("a")) {
//            System.out.println(user);
//        }
        List<User> a = mapper.getUserByLike("a");
        a.forEach(System.out::println);

    }

7.2、批量删除

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

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

    @Test
    public void DeleteMoreUser(){
        SqlSession sqlSession = sqlSessionUtil.getSqlSession();
        SpecialSQLMapper mapper = sqlSession.getMapper(SpecialSQLMapper.class);
        mapper.deleteMoreUser("5,6");

    }

7.3、动态设置表名

    //动态设置表名
    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);
//        for (User t_user : mapper.getUserList("t_user")) {
//            System.out.println(t_user);
//        }
        List<User> t = mapper.getUserList("t_user");
        t.forEach(System.out::println);

    }

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

JDBC的功能：

    @Test
    public void testJDBC(){
        try {
            Class.forName("");
            Connection connection = DriverManager.getConnection("", "", "");
//            String sql = "";
//            PreparedStatement preparedStatement = connection.prepareStatement(sql);
//            preparedStatement.setString(1,"a");

          String sql = "insert into t_user values()";
            PreparedStatement ps = connection.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
            ps.executeUpdate();

            ResultSet resultSet = ps.getGeneratedKeys();
            resultSet.next();
            int id = resultSet.getInt(1);

        } catch (Exception e) {
            e.printStackTrace();
        }
    }

---

    //添加用户信息
    void InsertUser(User user);

    <!--void InsertUser(User user);-->
    <!--
        useGeneratedKeys：表示当前添加功能使用自增的主键
        keyProperty：因为增删改有统一的返回值是受影响的行数，因此只能将获取的自增的主键放在传输的参
数user对象的某个属性中
    -->
    <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, "张三", "2222", 18, "男", "21221@qq.com");
        mapper.InsertUser(user);
        System.out.println(user);
    }