真理并无尽头

动态SQL之IF语句

HeJin大约 5 分钟数据库技术Mybatis

1、接口类BlogMapper.java新增方法

/**
 * 查询信息
 * @param map
 * @return
 */
List<Blog> queryBlog(Map map);

2、mapper文件BlogMapper.xml修改

<select id="queryBlog" parameterType="map" resultType="Blog">
    select * from blog where 1=1
    <if test="title != null">
        and title = #{title}
    </if>
    <if test="author != null">
        and author = #{author}
    </if>
</select>

3、测试

不传任何条件

@Test
public void test02(){
    SqlSession sqlSession = MybatisUtils.getSqlSession();
    BlogMapper blogMapper = sqlSession.getMapper(BlogMapper.class);
    /**
     * map无内容
     */
    HashMap<Object, Object> map = new HashMap<>();
    List<Blog> blogs = blogMapper.queryBlog(map);
    for (Blog blog : blogs) {
        System.out.println(blog);
    }

    sqlSession.close();
}

结果

Opening JDBC Connection
Created connection 515715487.
==>  Preparing: select * from blog where 1=1 
==> Parameters: 
<==    Columns: id, title, author, create_time, views
<==        Row: e1eb3ed0c6c9458b8d0779ae9062c754, Mybatis如此简单, 狂神说, 2020-11-29 08:28:30, 22222
<==        Row: a7c9288aaf894872b0ad282bfd5cadfc, Java如此简单, 狂神说, 2020-11-29 08:28:30, 33333
<==        Row: 51561ae07e6c48629b5536b873ab47f5, Spring如此简单, 狂神说, 2020-11-29 08:28:30, 44444
<==        Row: cc6626f2912342358609b7ae035a7bb5, 微服务如此简单, 狂神说, 2020-11-29 08:28:30, 99999
<==        Row: a4a36cb8722b492e96919a64dae72ff5, Mybatis如此简单, 狂神说, 2020-11-29 08:33:23, 99999
<==        Row: a230ab751c8b4a7f9e2072bf9d6c18e1, Java如此简单, 狂神说, 2020-11-29 08:33:23, 99999
<==        Row: f1d89e894ff64098be569769e62e82f9, Spring如此简单, 狂神说, 2020-11-29 08:33:23, 99999
<==        Row: ab9f312552964bb39500dda06ca2ae61, 微服务如此简单, 狂神说, 2020-11-29 08:33:23, 99999
<==      Total: 8
Blog(id=e1eb3ed0c6c9458b8d0779ae9062c754, title=Mybatis如此简单, author=狂神说, createTime=Sun Nov 29 16:28:30 CST 2020, views=22222)
Blog(id=a7c9288aaf894872b0ad282bfd5cadfc, title=Java如此简单, author=狂神说, createTime=Sun Nov 29 16:28:30 CST 2020, views=33333)
Blog(id=51561ae07e6c48629b5536b873ab47f5, title=Spring如此简单, author=狂神说, createTime=Sun Nov 29 16:28:30 CST 2020, views=44444)
Blog(id=cc6626f2912342358609b7ae035a7bb5, title=微服务如此简单, author=狂神说, createTime=Sun Nov 29 16:28:30 CST 2020, views=99999)
Blog(id=a4a36cb8722b492e96919a64dae72ff5, title=Mybatis如此简单, author=狂神说, createTime=Sun Nov 29 16:33:23 CST 2020, views=99999)
Blog(id=a230ab751c8b4a7f9e2072bf9d6c18e1, title=Java如此简单, author=狂神说, createTime=Sun Nov 29 16:33:23 CST 2020, views=99999)
Blog(id=f1d89e894ff64098be569769e62e82f9, title=Spring如此简单, author=狂神说, createTime=Sun Nov 29 16:33:23 CST 2020, views=99999)
Blog(id=ab9f312552964bb39500dda06ca2ae61, title=微服务如此简单, author=狂神说, createTime=Sun Nov 29 16:33:23 CST 2020, views=99999)
Closing JDBC Connection [com.mysql.cj.jdbc.ConnectionImpl@1ebd319f]
Returned connection 515715487 to pool.

Process finished with exit code 0

传title

@Test
public void test02(){
    SqlSession sqlSession = MybatisUtils.getSqlSession();
    BlogMapper blogMapper = sqlSession.getMapper(BlogMapper.class);
    HashMap<Object, Object> map = new HashMap<>();
    map.put("title","Mybatis如此简单");
    List<Blog> blogs = blogMapper.queryBlog(map);
    for (Blog blog : blogs) {
        System.out.println(blog);
    }

    sqlSession.close();
}

结果

Opening JDBC Connection
Created connection 515715487.
==>  Preparing: select * from blog where 1=1 and title = ? 
==> Parameters: Mybatis如此简单(String)
<==    Columns: id, title, author, create_time, views
<==        Row: e1eb3ed0c6c9458b8d0779ae9062c754, Mybatis如此简单, 狂神说, 2020-11-29 08:28:30, 22222
<==        Row: a4a36cb8722b492e96919a64dae72ff5, Mybatis如此简单, 狂神说, 2020-11-29 08:33:23, 99999
<==      Total: 2
Blog(id=e1eb3ed0c6c9458b8d0779ae9062c754, title=Mybatis如此简单, author=狂神说, createTime=Sun Nov 29 16:28:30 CST 2020, views=22222)
Blog(id=a4a36cb8722b492e96919a64dae72ff5, title=Mybatis如此简单, author=狂神说, createTime=Sun Nov 29 16:33:23 CST 2020, views=99999)
Closing JDBC Connection [com.mysql.cj.jdbc.ConnectionImpl@1ebd319f]
Returned connection 515715487 to pool.

Process finished with exit code 0

传author

@Test
public void test02()
    SqlSession sqlSession = MybatisUtils.getSqlSession();
    BlogMapper blogMapper = sqlSession.getMapper(BlogMapper.class);
    HashMap<Object, Object> map = new HashMap<>();
    map.put("author","狂神说");
    List<Blog> blogs = blogMapper.queryBlog(map);
    for (Blog blog : blogs) {
        System.out.println(blog);
    }

    sqlSession.close();
}

结果

Opening JDBC Connection
Created connection 515715487.
==>  Preparing: select * from blog where 1=1 and author = ? 
==> Parameters: 狂神说(String)
<==    Columns: id, title, author, create_time, views
<==        Row: e1eb3ed0c6c9458b8d0779ae9062c754, Mybatis如此简单, 狂神说, 2020-11-29 08:28:30, 22222
<==        Row: a7c9288aaf894872b0ad282bfd5cadfc, Java如此简单, 狂神说, 2020-11-29 08:28:30, 33333
<==        Row: 51561ae07e6c48629b5536b873ab47f5, Spring如此简单, 狂神说, 2020-11-29 08:28:30, 44444
<==        Row: cc6626f2912342358609b7ae035a7bb5, 微服务如此简单, 狂神说, 2020-11-29 08:28:30, 99999
<==        Row: a4a36cb8722b492e96919a64dae72ff5, Mybatis如此简单, 狂神说, 2020-11-29 08:33:23, 99999
<==        Row: a230ab751c8b4a7f9e2072bf9d6c18e1, Java如此简单, 狂神说, 2020-11-29 08:33:23, 99999
<==        Row: f1d89e894ff64098be569769e62e82f9, Spring如此简单, 狂神说, 2020-11-29 08:33:23, 99999
<==        Row: ab9f312552964bb39500dda06ca2ae61, 微服务如此简单, 狂神说, 2020-11-29 08:33:23, 99999
<==      Total: 8
Blog(id=e1eb3ed0c6c9458b8d0779ae9062c754, title=Mybatis如此简单, author=狂神说, createTime=Sun Nov 29 16:28:30 CST 2020, views=22222)
Blog(id=a7c9288aaf894872b0ad282bfd5cadfc, title=Java如此简单, author=狂神说, createTime=Sun Nov 29 16:28:30 CST 2020, views=33333)
Blog(id=51561ae07e6c48629b5536b873ab47f5, title=Spring如此简单, author=狂神说, createTime=Sun Nov 29 16:28:30 CST 2020, views=44444)
Blog(id=cc6626f2912342358609b7ae035a7bb5, title=微服务如此简单, author=狂神说, createTime=Sun Nov 29 16:28:30 CST 2020, views=99999)
Blog(id=a4a36cb8722b492e96919a64dae72ff5, title=Mybatis如此简单, author=狂神说, createTime=Sun Nov 29 16:33:23 CST 2020, views=99999)
Blog(id=a230ab751c8b4a7f9e2072bf9d6c18e1, title=Java如此简单, author=狂神说, createTime=Sun Nov 29 16:33:23 CST 2020, views=99999)
Blog(id=f1d89e894ff64098be569769e62e82f9, title=Spring如此简单, author=狂神说, createTime=Sun Nov 29 16:33:23 CST 2020, views=99999)
Blog(id=ab9f312552964bb39500dda06ca2ae61, title=微服务如此简单, author=狂神说, createTime=Sun Nov 29 16:33:23 CST 2020, views=99999)
Closing JDBC Connection [com.mysql.cj.jdbc.ConnectionImpl@1ebd319f]
Returned connection 515715487 to pool.

Process finished with exit code 0

同时传title和author

@Test
public void test02(){
    SqlSession sqlSession = MybatisUtils.getSqlSession();
    BlogMapper blogMapper = sqlSession.getMapper(BlogMapper.class);
    HashMap<Object, Object> map = new HashMap<>();
    map.put("title","Mybatis如此简单");
    map.put("author","狂神说");
    List<Blog> blogs = blogMapper.queryBlog(map);

    for (Blog blog : blogs) {
        System.out.println(blog);
    }

    sqlSession.close();
}

结果

Opening JDBC Connection
Created connection 2063763486.
==>  Preparing: select * from blog where 1=1 and title = ? and author = ?
==> Parameters: Mybatis如此简单(String), 狂神说(String)
<==    Columns: id, title, author, create_time, views
<==        Row: e1eb3ed0c6c9458b8d0779ae9062c754, Mybatis如此简单, 狂神说, 2020-11-29 08:28:30, 22222
<==        Row: a4a36cb8722b492e96919a64dae72ff5, Mybatis如此简单, 狂神说, 2020-11-29 08:33:23, 99999
<==      Total: 2
Blog(id=e1eb3ed0c6c9458b8d0779ae9062c754, title=Mybatis如此简单, author=狂神说, createTime=Sun Nov 29 16:28:30 CST 2020, views=22222)
Blog(id=a4a36cb8722b492e96919a64dae72ff5, title=Mybatis如此简单, author=狂神说, createTime=Sun Nov 29 16:33:23 CST 2020, views=99999)
Closing JDBC Connection [com.mysql.cj.jdbc.ConnectionImpl@7b02881e]
Returned connection 2063763486 to pool.

Process finished with exit code 0
Copyright © 2023 HeJin