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

1、概述

2、`<where>`、`<if>`

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

4、`<set>`

5、`<foreach>`

6、`<sql>`与`<include>`

相关文章

网友评论

延伸阅读

深度阅读

栏目导航

热点阅读

100、【JavaEE】【Mybatis】动态 SQL

1、概述

2、<where>、<if>

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

4、<set>

5、<foreach>

6、<sql>与<include>

相关文章

网友评论

延伸阅读

深度阅读

栏目导航

热点阅读

2、`<where>`、`<if>`

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

4、`<set>`

5、`<foreach>`

6、`<sql>`与`<include>`