用户动态生成sql语句 java 动态sql写法

转载

IT独行侠 2023-12-24 12:53:43

文章标签 用户动态生成sql语句 java java mysql mybatis 数据库 文章分类 Java 后端开发

文章目录

前言
一、环境搭建
二、if
三、choose - when - otherwise
四、set
五、foreach
六、SQL片段

前言

1）什么是动态SQL？
答：动态SQL就是根据不同的条件生成不同的SQL语句。

2）优点：可摆脱SQL语句拼接带来的繁琐。

接下来，将以一个实例介绍动态sql用到的标签。

一、环境搭建

1）创建一个测试用的到数据库中的表blog，表中的数据可任意添加。

CREATE TABLE `blog`(
`id` VARCHAR(50) NOT NULL COMMENT '博客id',
`title` VARCHAR(100) NOT NULL COMMENT '博客标题',
`author` VARCHAR(30) NOT NULL COMMENT '博客作者',
``blog`` DATETIME NOT NULL COMMENT '创建时间',
`views` INT(30) NOT NULL COMMENT '浏览量'
)ENGINE=INNODB DEFAULT CHARSET=utf8

2）创建实体类Blog。（此处省略了get/set/toString方法，练习是需添加）

public class Blog {
    private String id;
    private String title;
    private String author;
    private Date createTime;  // 属性名和字段名不一致，设置里面开启驼峰命名法
    private int views;
}

3）创建实体的接口BlogMapper

public interface BlogMapper {

}

4）设置mybatis-congfig.xml配置文件。
注意：1）中表的字段名为create_time，而2）中实体类的属性名为createTime，因此须在设置中配置表中字段名转java的驼峰命名法。

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE configuration
        PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>
    <settings>
        <setting name="logImpl" value="STDOUT_LOGGING"/>
        <setting name="mapUnderscoreToCamelCase" value="true"/>
    </settings>

    <environments default="development">
        <environment id="development">
            <transactionManager type="JDBC"/>
            <dataSource type="POOLED">
                <property name="driver" value="com.mysql.jdbc.Driver"/>
                <property name="url" value="jdbc:mysql://localhost:3306/jdbcstudy?useUnicode=true&characterEncoding=utf8&useSSl=true"/>
                <property name="username" value="root"/>
                <property name="password" value="12345"/>
            </dataSource>
        </environment>
    </environments>

    <mappers>
        <mapper class="com.ali.mapper.BlogMapper"></mapper>
    </mappers>

</configuration>

5）创建sqlSession的工具类

public class MybatisUtils {
    private static SqlSessionFactory sqlSessionFactory;
    static {
        try {
            String resource = "mybatis-config.xml";
            InputStream inputStream = Resources.getResourceAsStream(resource);
            sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    // 获得处理sql语句的对象
    public static SqlSession getSqlSession(){
        return sqlSessionFactory.openSession();
    }
}

6）创建一个验证结果的测试类Test。

public class UserMapperTest {
	@Test
    public void test(){
    }
}

二、if

1）<if> ：根据条件匹配where中子内容。
<where>：只会在子元素返回任何内容的情况下才插入 “WHERE” 子句。而且，若子句的开头为 “AND” 或 “OR”，where 元素也会将它们去除。

2）代码实现：
目标：查找符合条件的title和author信息。
①在BlogMapper中：

public interface BlogMapper {
    List<Blog> quarrBlogs(Map map);
}

②在BlogMapper.xml中：

<select id="quarrBlogs" parameterType="map" resultType="com.ali.pojo.Blog">
        select * from mybatis.blog
        <where>
            <if test="title != null">
            and title = #{title}
        </if>
        <if test="author != null">
            and author = #{author}
        </if>
        </where>
</select>

③测试：

public class UserMapperTest {
    @Test
    public void test(){
        SqlSession sqlSession = MybatisUtils.getSqlSession();
        BlogMapper mapper = sqlSession.getMapper(BlogMapper.class);
        HashMap map = new HashMap();
        map.put("title", "mybatis");
        map.put("author", "hi");
        List<Blog> blogs = mapper.quarrBlogs(map);
        for (Blog blog : blogs) {
            System.out.println(blogs);
        }
        sqlSession.commit();
        sqlSession.close();
    }
}

三、choose - when - otherwise

1）功能：从多个条件中选择一个使用。类似于Java 中的 switch 语句。

<when>：在什么条件下执行什么语句。

<otherwise>最后一个条件下执行什么语句

2）代码实现：
目标：查找符合条件的title或者author信息。
①在BlogMapper中：

public interface BlogMapper {
    List<Blog> quarrBlogs2(Map map);
}

②在BlogMapper.xml中：
目标：按title或者author信息查找内容。

<select id="quarrBlogs2" parameterType="map" resultType="com.ali.pojo.Blog">
     select * from mybatis.blog
     <where>
         <choose>
             <when test="title != null">
                 title = #{title};
             </when>
             <otherwise >
                 and author = #{author}
             </otherwise>
         </choose>
     </where>
 </select>

③测试：

public class UserMapperTest {
    @Test
    public void test(){
        SqlSession sqlSession = MybatisUtils.getSqlSession();
        BlogMapper mapper = sqlSession.getMapper(BlogMapper.class);
        HashMap map = new HashMap();
        map.put("title", "mybatis");  
        List<Blog> blogs = mapper.quarrBlogs2(map);
        for (Blog blog : blogs) {
            System.out.println(blogs);
        }
        sqlSession.commit();
        sqlSession.close();
    }
}

四、set

1）<set> ：用于动态更新语句。可以用于动态包含需要更新的列，忽略其它不更新的列。

2）代码实现：
目标：更新title或author信息。
①在BlogMapper中：

public interface BlogMapper {
    int updateBlog(Map map);
}

②在BlogMapper.xml中：

<update id="updateBlog" parameterType="com.ali.pojo.Blog">
    update mybatis.blog
    <set>
        <if test="title != null">
            title = #{title},
        </if>
        <if test="author != null" >
            author = #{author}
        </if>
    </set>
    where id = #{id}
</update>

③测试：

public class UserMapperTest {
    @Test
    public void test(){
        SqlSession sqlSession = MybatisUtils.getSqlSession();
        BlogMapper mapper = sqlSession.getMapper(BlogMapper.class);
        HashMap map = new HashMap();
        map.put("title", "java");
        mapper.updateBlog(map);
        sqlSession.commit();
        sqlSession.close();
    }
}

五、foreach

1）<for> ：对集合进行遍历，尤其是在构建 IN 条件语句的时候。

<!-- 类似于下面sql语句 -->
select * from table where (id=1 or id=2 or id=3)
list = [1, 2, 3]

<foreach item="item" index="index" collection="list"
   open="(" separator="or" close=")">
     id = #{item}
</foreach>

2）代码实现：
目标：查找id集合中的所有信息。
①在BlogMapper中：

public interface BlogMapper {
    List<Blog> quarrBlogs3(Map map);
}

②在BlogMapper.xml中：

<select id="quarrBlogs3" parameterType="map" resultType="com.ali.pojo.Blog">
    select * from mybatis.blog
    <where>
        <foreach collection="ids" item="id" open="(" close=")" separator="or">
            id = #{id}
        </foreach>
    </where>
</select>

③测试：

public class UserMapperTest {
@Test
    public void test(){
        SqlSession sqlSession = MybatisUtils.getSqlSession();
        BlogMapper mapper = sqlSession.getMapper(BlogMapper.class);
        HashMap map = new HashMap();
        ArrayList<Integer> ids= new ArrayList<>();
        ids.add(1);
        ids.add(2);
        map.put("ids", ids);
        List<Blog> blogs = mapper.quarrBlogs3(map);
        for (Blog blog : blogs) {
            System.out.println(blogs);
        }
        sqlSession.commit();
        sqlSession.close();
    }
}

六、SQL片段

1）功能：将重复的sql语句提出来模块化，类似于java中的将常用代码功能提出来变成函数，使用的时候直接调用，减少代码冗余。

适用于：在单表中操作，并且该sql片段不存在where标签。

2）代码实现：
在上述例子中，我们发现关于判断title和author是否为空的代码经常使用，因此可以将该部分代码提取出来变成sql块。在原代码中用include标签调用。

<sql id="fragment">
    <if test="title != null">
        and title = #{title}
    </if>
    <if test="author != null">
        and author = #{author}
    </if>
</sql>

<select id="quarrBlogs" parameterType="com.ali.pojo.Blog" resultType="com.ali.pojo.Blog">
    select * from mybatis.blog
    <where>
        <include refid="fragment"></include>
    </where>
</select>

本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。