美文网首页
100、【JavaEE】【Mybatis】动态 SQL

100、【JavaEE】【Mybatis】动态 SQL

作者: yscyber | 来源:发表于2021-10-16 02:52 被阅读0次

1、概述

  • 动态 SQL，MyBatis 可以根据实际情况生成不同的 SQL 语句。

2、<where>、<if>

  • <where>相当于 SQL 中的WHERE 1=1

  • <if>表示符合条件就将其中包裹的 SQL 语句拼接上去，否则直接忽略。

import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;
import lombok.ToString;

@Data
@NoArgsConstructor
@AllArgsConstructor
@ToString
public class Xxx {

    private Integer id;

    private String name;

}

import com.yscyber.mybatis.one.Xxx;

import java.util.List;

public interface XxxRepo {

    List<Xxx> listXxxByXxx(Xxx xxx);

}

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">

<mapper namespace="com.yscyber.mybatis.one.repo.XxxRepo">

    <select id="listXxxByXxx" parameterType="Xxx" resultType="Xxx">
        SELECT id, name
        FROM xxx
        <where>
            <if test="id != null">
                AND id=#{id}
            </if>
            <if test="name != null">
                AND name LIKE #{name}
            </if>
        </where>
    </select>

</mapper>

<?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>
    
    <properties resource="jdbc.properties"/>

    <typeAliases>
        <typeAlias type="com.yscyber.mybatis.one.Xxx" alias="Xxx"/>
    </typeAliases>
    
    <environments default="dev">
        <!-- 配置数据源 -->
        <environment id="dev">
            <transactionManager type="JDBC" />
            <dataSource type="POOLED">
                <property name="driver" value="${jdbc_driver}"/>
                <property name="url" value="${jdbc_url}"/>
                <property name="username" value="${jdbc_username}"/>
                <property name="password" value="${jdbc_password}"/>
            </dataSource>
        </environment>
    </environments>

    <!-- 加载映射文件 -->
    <mappers>
        <mapper class="com.yscyber.mybatis.one.repo.XxxRepo"/>
    </mappers>
    
</configuration>

    @Test
    public void test7() {
        try {
            SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(Resources.getResourceAsStream("mybatis.xml"));
            SqlSession sqlSession = sqlSessionFactory.openSession();

            XxxRepo xxxRepo = sqlSession.getMapper(XxxRepo.class);
            List<Xxx> xxxList = xxxRepo.listXxxByXxx(new Xxx(2, "%2%"));
            for (Xxx val : xxxList) {
                System.out.println(val);
            }

            sqlSession.close();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    @Test
    public void test8() {
        try {
            SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(Resources.getResourceAsStream("mybatis.xml"));
            SqlSession sqlSession = sqlSessionFactory.openSession();

            XxxRepo xxxRepo = sqlSession.getMapper(XxxRepo.class);

            Xxx xxx = new Xxx();
            xxx.setName("%5%");
            List<Xxx> xxxList = xxxRepo.listXxxByXxx(xxx);
            for (Xxx val : xxxList) {
                System.out.println(val);
            }

            sqlSession.close();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

3、<where>、<choose>、<when>、<otherwise>

  • 这三个相当于 Java 语言中的switch-case-default语句
import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;
import lombok.ToString;

@Data
@NoArgsConstructor
@AllArgsConstructor
@ToString
public class Xxx {

    private Integer id;

    private String name;

}

import com.yscyber.mybatis.one.Xxx;

import java.util.List;

public interface XxxRepo {

    List<Xxx> listXxxByXxx(Xxx xxx);

}

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">

<mapper namespace="com.yscyber.mybatis.one.repo.XxxRepo">

    <select id="listXxxByXxx" parameterType="Xxx" resultType="Xxx">
        SELECT id, name
        FROM xxx
        <where>
            <choose>
                <when test="id != null">
                    AND id=#{id}
                </when>
                <when test="name != null">
                    AND name LIKE #{name}
                </when>
                <otherwise>
                    AND 1=1
                </otherwise>
            </choose>
        </where>
    </select>

</mapper>

<?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>
    
    <properties resource="jdbc.properties"/>

    <typeAliases>
        <typeAlias type="com.yscyber.mybatis.one.Xxx" alias="Xxx"/>
    </typeAliases>
    
    <environments default="dev">
        <!-- 配置数据源 -->
        <environment id="dev">
            <transactionManager type="JDBC" />
            <dataSource type="POOLED">
                <property name="driver" value="${jdbc_driver}"/>
                <property name="url" value="${jdbc_url}"/>
                <property name="username" value="${jdbc_username}"/>
                <property name="password" value="${jdbc_password}"/>
            </dataSource>
        </environment>
    </environments>

    <!-- 加载映射文件 -->
    <mappers>
        <mapper class="com.yscyber.mybatis.one.repo.XxxRepo"/>
    </mappers>
    
</configuration>

    @Test
    public void test8() {
        try {
            SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(Resources.getResourceAsStream("mybatis.xml"));
            SqlSession sqlSession = sqlSessionFactory.openSession();

            XxxRepo xxxRepo = sqlSession.getMapper(XxxRepo.class);

            Xxx xxx = new Xxx();
            xxx.setName("%5%");
            List<Xxx> xxxList = xxxRepo.listXxxByXxx(xxx);
            for (Xxx val : xxxList) {
                System.out.println(val);
            }

            sqlSession.close();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

4、<set> 

import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;
import lombok.ToString;

@Data
@NoArgsConstructor
@AllArgsConstructor
@ToString
public class Xxx {

    private Integer id;

    private String name;

}

import com.yscyber.mybatis.one.Xxx;
import org.apache.ibatis.annotations.Param;

public interface XxxRepo {

    int updateXxxById(@Param("oid") Integer originalId, @Param("obj") Xxx xxx);

}

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">

<mapper namespace="com.yscyber.mybatis.one.repo.XxxRepo">

    <update id="updateXxxById">
        UPDATE xxx
        <set>
            <if test="obj.id != null">
                id=#{obj.id},
            </if>
            <if test="obj.name != null">
                name=#{obj.name},
            </if>
        </set>
        WHERE id=#{oid}
    </update>

</mapper>

<?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>
    
    <properties resource="jdbc.properties"/>

    <typeAliases>
        <typeAlias type="com.yscyber.mybatis.one.Xxx" alias="Xxx"/>
    </typeAliases>
    
    <environments default="dev">
        <!-- 配置数据源 -->
        <environment id="dev">
            <transactionManager type="JDBC" />
            <dataSource type="POOLED">
                <property name="driver" value="${jdbc_driver}"/>
                <property name="url" value="${jdbc_url}"/>
                <property name="username" value="${jdbc_username}"/>
                <property name="password" value="${jdbc_password}"/>
            </dataSource>
        </environment>
    </environments>

    <!-- 加载映射文件 -->
    <mappers>
        <mapper class="com.yscyber.mybatis.one.repo.XxxRepo"/>
    </mappers>
    
</configuration>

    @Test
    public void test8() {
        try {
            SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(Resources.getResourceAsStream("mybatis.xml"));
            SqlSession sqlSession = sqlSessionFactory.openSession();

            XxxRepo xxxRepo = sqlSession.getMapper(XxxRepo.class);

            int originalId = 1;
            Xxx xxx = new Xxx(10, "wow");

            xxxRepo.updateXxxById(originalId, xxx);

            sqlSession.commit();

            sqlSession.close();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    @Test
    public void test9() {
        try {
            SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(Resources.getResourceAsStream("mybatis.xml"));
            SqlSession sqlSession = sqlSessionFactory.openSession();

            XxxRepo xxxRepo = sqlSession.getMapper(XxxRepo.class);

            int originalId = 10;
            Xxx xxx = new Xxx();
            xxx.setName("hah");

            xxxRepo.updateXxxById(originalId, xxx);

            sqlSession.commit();
            sqlSession.close();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

5、<foreach>

  • <foreach>主要是用来做数据的循环遍历。
    例如：select * from user where id in (1,2,3)在这样的语句中，传入的参数部分必须依靠<foreach>遍历才能实现。

  • List：

import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;
import lombok.ToString;

@Data
@NoArgsConstructor
@AllArgsConstructor
@ToString
public class Xxx {

    private Integer id;

    private String name;

}

import com.yscyber.mybatis.one.Xxx;

import java.util.List;

public interface XxxRepo {

    List<Xxx> listXxxById(List<Integer> ids);

}

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">

<mapper namespace="com.yscyber.mybatis.one.repo.XxxRepo">

    <select id="listXxxById" resultType="Xxx" parameterType="list">
        SELECT id, name
        FROM xxx
        <where>
            <!-- 如果接口参数是 List，collection 属性值为：collection 或 list -->
            <foreach collection="list" item="id" open="id IN (" close=")" separator=",">
                #{id}
            </foreach>
        </where>
    </select>

</mapper>

<?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>
    
    <properties resource="jdbc.properties"/>

    <typeAliases>
        <typeAlias type="com.yscyber.mybatis.one.Xxx" alias="Xxx"/>
    </typeAliases>
    
    <environments default="dev">
        <!-- 配置数据源 -->
        <environment id="dev">
            <transactionManager type="JDBC" />
            <dataSource type="POOLED">
                <property name="driver" value="${jdbc_driver}"/>
                <property name="url" value="${jdbc_url}"/>
                <property name="username" value="${jdbc_username}"/>
                <property name="password" value="${jdbc_password}"/>
            </dataSource>
        </environment>
    </environments>

    <!-- 加载映射文件 -->
    <mappers>
        <mapper class="com.yscyber.mybatis.one.repo.XxxRepo"/>
    </mappers>
    
</configuration>

    @Test
    public void test8() {
        try {
            SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(Resources.getResourceAsStream("mybatis.xml"));
            SqlSession sqlSession = sqlSessionFactory.openSession();

            XxxRepo xxxRepo = sqlSession.getMapper(XxxRepo.class);

            List<Integer> ids = new ArrayList<>();
            ids.add(2);
            ids.add(3);
            List<Xxx> xxxList = xxxRepo.listXxxById(ids);
            for (Xxx val : xxxList) {
                System.out.println(val);
            }

            sqlSession.close();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }
  • array：
import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;
import lombok.ToString;

@Data
@NoArgsConstructor
@AllArgsConstructor
@ToString
public class Xxx {

    private Integer id;

    private String name;

}

import com.yscyber.mybatis.one.Xxx;

import java.util.List;

public interface XxxRepo {

    List<Xxx> listXxxById(Integer[] ids);

}

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">

<mapper namespace="com.yscyber.mybatis.one.repo.XxxRepo">

    <select id="listXxxById" resultType="Xxx" parameterType="list">
        SELECT id, name
        FROM xxx
        <where>
            <!-- 如果接口参数是数组，collection 属性值为：array -->
            <foreach collection="array" item="id" open="id IN (" close=")" separator=",">
                #{id}
            </foreach>
        </where>
    </select>

</mapper>

<?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>
    
    <properties resource="jdbc.properties"/>

    <typeAliases>
        <typeAlias type="com.yscyber.mybatis.one.Xxx" alias="Xxx"/>
    </typeAliases>
    
    <environments default="dev">
        <!-- 配置数据源 -->
        <environment id="dev">
            <transactionManager type="JDBC" />
            <dataSource type="POOLED">
                <property name="driver" value="${jdbc_driver}"/>
                <property name="url" value="${jdbc_url}"/>
                <property name="username" value="${jdbc_username}"/>
                <property name="password" value="${jdbc_password}"/>
            </dataSource>
        </environment>
    </environments>

    <!-- 加载映射文件 -->
    <mappers>
        <mapper class="com.yscyber.mybatis.one.repo.XxxRepo"/>
    </mappers>
    
</configuration>

    @Test
    public void test8() {
        try {
            SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(Resources.getResourceAsStream("mybatis.xml"));
            SqlSession sqlSession = sqlSessionFactory.openSession();

            XxxRepo xxxRepo = sqlSession.getMapper(XxxRepo.class);

            Integer[] arr = {10};
            List<Xxx> xxxList = xxxRepo.listXxxById(arr);
            for (Xxx val : xxxList) {
                System.out.println(val);
            }

            sqlSession.close();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

6、<sql>与<include>

  • <sql>可以将重复的 SQL 语句提取出来，然后使用<include>引用即可，达到 SQL 复用的目的。
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">

<mapper namespace="com.yscyber.mybatis.one.repo.XxxRepo">

    <sql id="selectAll">
        SELECT id, name
        FROM xxx
    </sql>
    
    <select id="listXxxById" resultType="Xxx" parameterType="list">
        <include refid="selectAll"/>
        <where>
            <!-- 如果接口参数是数组，collection 属性值为：array -->
            <foreach collection="array" item="id" open="id IN (" close=")" separator=",">
                #{id}
            </foreach>
        </where>
    </select>

</mapper>

相关文章

网友评论

      本文标题：100、【JavaEE】【Mybatis】动态 SQL

      本文链接：https://www.haomeiwen.com/subject/adnuoltx.html

      延伸阅读

      深度阅读

      • 您也可以注册成为美文阅读网的作者，发表您的原创作品、分享您的心情！

      栏目导航

      热点阅读

      关于我们|服务条款|联系我们|100、【JavaEE】【Mybatis】动态 SQL|投稿指南|网站地图|RSS订阅|排版工具|手机版

      提供经典美文摘抄,优美散文欣赏,现代诗歌精选,短篇小说,心情随笔,表白情书范文,故事会在线阅读欣赏

      Copyright © 2014-2023 Haomeiwen.com All Rights Reserved. 好美文阅读网 版权所有

      备案信息：桂公网安备 45052102000051号 · 桂ICP备13007215号-3

      本站所收录作品、热点评论等信息部分来源互联网，目的只是为了系统归纳学习和传递资讯

      所有作品版权归原创作者所有，与本站立场无关，如不慎侵犯了你的权益，请联系我们告知，我们将做删除处理！