动态sql的实现（五）

最新推荐文章于 2024-04-16 10:00:21 发布

阿川xiang

最新推荐文章于 2024-04-16 10:00:21 发布

阅读量602

点赞数

分类专栏： mybatis

本文链接：https://blog.csdn.net/weixin_43054590/article/details/88392495

版权

mybatis 专栏收录该内容

8 篇文章 0 订阅

订阅专栏

一、动态sql实现模糊查询

在第三节中我们完成了mybatis动态代理mapper实现类的实例，本篇文章我们将以继续以user数据库表来实现动态sql语句的模糊查询，动态sql只要需要使用以下四种标签：1、if ；2、choose，when，otherwise 3、where，set 4、foreach

1.1、if标签：通过姓名查找指定的男性用户

1、数据库插入数据

insert  into `user`(`id`,`user_name`,`telephone`,`imax_email`,`sex`,`age`) values 
(3,'阿帕奇','45436547','3214215@160.com','男',34),
(4,'斯纳克','15265472311','3214215@160.com','男',54),
(6,'张益达','431231542645','3214215@qq.com','男',56),
(7,'胡一菲','52627','dwq214@qq.com','女',12),
(10,'张益达','431231542645','3214215@qq.com','男',45),
(11,'张达','542353','3214215@qq.com','女',34);

2、UserMapper中定义查询方法

List<User> selectManUserByName(@Param("username") String username);

3、UserMapper.xml文件中实现

<resultMap id="user" type="com.achuan.demo.entity.User">
        <id column="id" property="id" jdbcType="INTEGER"/>
        <result column="user_name" property="username" jdbcType="VARCHAR"/>
        <result column="telephone" property="telephone" jdbcType="VARCHAR"/>
        <result column="imax_email" property="iMaxEmail" jdbcType="VARCHAR"/>
        <result column="sex" property="sex" jdbcType="VARCHAR"/>
        <result column="age" property="age" jdbcType="INTEGER"/>
    </resultMap>
 <select id="selectManUserByName" parameterType="string" resultMap="user">
        select * from user where sex = "男"
        <if test="username != null and username.trim() != ''">
            and  user_name = #{username}
        </if>

     </select>

4、测试查询结果

    @Test
    public void test07(){
        List<User> userList = userMapper.selectManUserByName("张益达");
        for (User user : userList) {
            System.out.println(user);
        }
    }

14:07:42,370 DEBUG - Logging initialized using 'class org.apache.ibatis.logging.slf4j.Slf4jImpl' adapter.
14:07:42,395 DEBUG - PooledDataSource forcefully closed/removed all connections.
14:07:42,395 DEBUG - PooledDataSource forcefully closed/removed all connections.
14:07:42,395 DEBUG - PooledDataSource forcefully closed/removed all connections.
14:07:42,395 DEBUG - PooledDataSource forcefully closed/removed all connections.
14:07:42,594 DEBUG - Opening JDBC Connection
14:07:42,797 DEBUG - Created connection 1353070773.
14:07:42,799 DEBUG - ==>  Preparing: select * from user where sex = "男" and user_name = ? 
14:07:42,836 DEBUG - ==> Parameters: 张益达(String)
14:07:42,875 DEBUG - <==      Total: 2
User{id=6, username='张益达', iMaxEmail='3214215@qq.com', telephone='431231542645', sex='男', age=56}
User{id=10, username='张益达', iMaxEmail='3214215@qq.com', telephone='431231542645', sex='男', age=45}

1.2、choose，when，otherwise标签：通过用户姓名或者年龄来获取用户列表

1、UserMapper中定义查询方法

 List<User> selectUserByArgs(String username,int age);

2、UserMapper.xml中实现


    <select id="selectUserByArgs" resultMap="user">
        select * from user
        <choose>
            <when test="param1!=null and param1.trim()!=''">
                where user_name = #{param1}
            </when>
            <when test="param2 &lt; 40">
                where age = #{param2}
            </when>
            <otherwise>
                where user_name = "张益达"
            </otherwise>
        </choose>
    </select>

3、测试结果

    @Test
    public void test08(){
        List<User> userList = userMapper.selectUserByArgs(null,34);
        for (User user : userList) {
            System.out.println(user);
        }
    }

14:13:45,746 DEBUG - Logging initialized using 'class org.apache.ibatis.logging.slf4j.Slf4jImpl' adapter.
14:13:45,777 DEBUG - PooledDataSource forcefully closed/removed all connections.
14:13:45,777 DEBUG - PooledDataSource forcefully closed/removed all connections.
14:13:45,777 DEBUG - PooledDataSource forcefully closed/removed all connections.
14:13:45,777 DEBUG - PooledDataSource forcefully closed/removed all connections.
14:13:45,966 DEBUG - Opening JDBC Connection
14:13:46,167 DEBUG - Created connection 1353070773.
14:13:46,169 DEBUG - ==>  Preparing: select * from user where age = ? 
14:13:46,208 DEBUG - ==> Parameters: 34(Integer)
14:13:46,238 DEBUG - <==      Total: 2
User{id=3, username='阿帕奇', iMaxEmail='3214215@160.com', telephone='45436547', sex='男', age=34}
User{id=11, username='张达', iMaxEmail='3214215@qq.com', telephone='542353', sex='女', age=34}

1.3、where、if标签：通过性别或者姓名获取指定的用户列表

1、UserMapper中定义查询方法

 List<User> selectUsersByArgs(String sex,String username);

2、UserMapper.xml中实现

<!--#{}是占位符，可以防止sql语句侵入，与参数名字无关，'${}'是取出参数参数值信息，不能防止sql语句侵入-->
    <select id="selectUsersByArgs" resultMap="user">
        select * from user
        <where>
            <if test="param1 != null and param1.trim() != ''">
                and sex like #{param1}
            </if>
            <if test="param2 != null and param2.trim() != ''">
                and user_name like #{param2}
            </if>
        </where>
    </select>

3、测试结果

   @Test
    public void test09(){
        List<User> userList = userMapper.selectUsersByArgs("男",null);
        for (User user : userList) {
            System.out.println(user);
        }
    }

14:19:27,761 DEBUG - Logging initialized using 'class org.apache.ibatis.logging.slf4j.Slf4jImpl' adapter.
14:19:27,784 DEBUG - PooledDataSource forcefully closed/removed all connections.
14:19:27,784 DEBUG - PooledDataSource forcefully closed/removed all connections.
14:19:27,784 DEBUG - PooledDataSource forcefully closed/removed all connections.
14:19:27,784 DEBUG - PooledDataSource forcefully closed/removed all connections.
14:19:27,980 DEBUG - Opening JDBC Connection
14:19:28,254 DEBUG - Created connection 404214852.
14:19:28,256 DEBUG - ==>  Preparing: select * from user WHERE sex like ? 
14:19:28,295 DEBUG - ==> Parameters: 男(String)
14:19:28,338 DEBUG - <==      Total: 4
User{id=3, username='阿帕奇', iMaxEmail='3214215@160.com', telephone='45436547', sex='男', age=34}
User{id=4, username='斯纳克', iMaxEmail='3214215@160.com', telephone='15265472311', sex='男', age=54}
User{id=6, username='张益达', iMaxEmail='3214215@qq.com', telephone='431231542645', sex='男', age=56}
User{id=10, username='张益达', iMaxEmail='3214215@qq.com', telephone='431231542645', sex='男', age=45}

二、#{}占位符与${}

2.1、二者之间的区别

#{} 只是替换？，相当于PreparedStatement使用占位符去替换参数，可以防止sql注入。#{} 只是表示占位，与参数的名字无关，如果只有一个参数，会自动对应。
${} 是进行字符串拼接，相当于sql语句中的Statement，使用字符串去拼接sql；$可以是sql中的任一部分传入到Statement中，不能防止sql注入。使用${} 去取出参数值信息，必须使用${value}。

2.2、举例说明

如本篇1.1节实例所示，当方法中只有一个参数时，使用#{0}，#{username}，${username}时，UserMapper均可以获取目标的传参并将目标参数的值传递给statement执行数据库语句。注意当时用${username}时，无论是一个参数还是多个参数都必须在每个参数之前用@Param("username")标签声明一个别名，#{}在一个参数的时候不需要。

以本篇1.2节实例为例，当mapper接口方法中需要传递多个参数时，${“参数名”}对应使用@Param(“参数名”)即可解决，#{}则有两种不同的情况：

情况一：不使用${“参数名”},Mapper.xml直接按#{param1}，#{param2}配置

<select id="selectUserByArgs" resultMap="user">
        select * from user
        <choose>
            <when test="param1!=null and param1.trim()!=''">
                where user_name = #{param1}
            </when>
            <when test="param2 &lt; 40">
                where age = #{param2}
            </when>
            <otherwise>
                where user_name = "张益达"
            </otherwise>
        </choose>
    </select>

情况二：使用${“参数名”}

List<User> selectUserByArgs(@Param("username") String username,@Param("age") int age);

 <select id="selectUserByArgs" resultMap="user">
        select * from user
        <choose>
            <when test="username!=null and username.trim()!=''">
                where user_name = #{username}
            </when>
            <when test="age &lt; 40">
                where age = #{age}
            </when>
            <otherwise>
                where user_name = "张益达"
            </otherwise>
        </choose>
    </select>

阿川xiang

关注

0
点赞
踩
1

收藏

觉得还不错? 一键收藏
0
评论
动态sql的实现（五）

一、动态sql实现模糊查询在第三节中我们完成了mybatis动态代理mapper实现类的实例，本篇文章我们将以继续以user数据库表来实现动态sql语句的模糊查询，动态sql只要需要使用以下四种标签：1、if ；2、choose，when，otherwise 3、where，set 4、foreach 1.1、if标签：通过姓名查找指定的男性用户 ...
复制链接

扫一扫