7. ordinary road – dynamic SQL statement

Time:2022-6-5

Dynamic SQL statement is one of the cores. Here we will demonstrate it through several examples

I. multi condition query topics

1. complete dynamic SQL statements through identities

If tag involved

<mapper namespace="com.shxt.model.User">
    <resultMap type="com.shxt.model.User" id="BaseResultMapper">
        <id column="user_id" property="user_id" />
        <result column="user_name" property="user_name" />
        <result column="sex" property="sex" />
        <result column="money" property="money" />
        <result column="birthday" property="birthday" />
    </resultMap>

    <sql id="oa_user_columns">
        user_id,user_name,sex,money,birthday
    </sql>

    <sql id="oa_user_columns_alias">
        ${alias}.user_id,${alias}.user_name,${alias}.sex,${alias}.money,${alias}.birthday
    </sql>
</mapper>
<!--  1. name and gender query -->
    <!--  A. Complete dynamic SQL statements through identities -- >
    <select id="if01" parameterType="map" resultMap="BaseResultMapper">
        SELECT
            <include refid="oa_user_columns" />
        FROM
            oa_user
        WHERE 1=1
            <if test="name != null && name != ''">
                AND user_name LIKE CONCAT('%',#{name},'%')
            </if>
    
            <if test="sex != null and sex != ''">
                AND sex=#{sex}
            </if>

    </select>
  • Java test code
@Test
    Public void if tag 01 (){
        SqlSession sqlSession = null;
        try {
            sqlSession = MyBatisUtils.getSqlSession();
            Map<String,Object> query = new HashMap<>();
            query. Put ("name", "Wu");
            query. Put ("sex", "male");

            List<User> userList =
                    sqlSession.selectList(User.class.getName()+".if01", query);
            System.out.println(userList);


        } finally {
            MyBatisUtils.closeSqlSession(sqlSession);
        }
    }
  • Illustration

2. Where tag and if tag combination

  • If content is found in the tag, the keyword where will be added to the front of the content
  • If there is content, it will check whether there is an and space or an or space in the front of the content, and it will be erased automatically
  • Mapping file
<!--  B. Recommended method where label -- >
    <select id="if02" parameterType="map" resultMap="BaseResultMapper">
        SELECT
            <include refid="oa_user_columns" />
        FROM
            oa_user
        <where>
            <if test="name != null && name != ''">
                AND      user_name LIKE CONCAT('%',#{name},'%')
            </if>
    
            <if test="sex != null and sex != ''">
                AND sex=#{sex}
            </if>
        </where>

    </select>
  • Java test code
@Test
    Public void if tag 02 (){
        SqlSession sqlSession = null;
        try {
            sqlSession = MyBatisUtils.getSqlSession();
            Map<String,Object> query = new HashMap<>();
            query. Put ("name", "Wu");
            query. Put ("sex", "male");

            List<User> userList =
                    sqlSession.selectList(User.class.getName()+".if02", query);
            System.out.println(userList);


        } finally {
            MyBatisUtils.closeSqlSession(sqlSession);
        }
    }
  • Illustration

3.trim tag and if tag

<trim
            Prefix= "what content do you want to add in front of the content when you find it?"
            Prefixoverrides= "what do you want to erase from the front of the content when you find it?"
            Suffix= "what content do you want to add at the end of the content when you find it?"
            Suffixoverrides= "when content is found, what content do you want to erase at the end of the content"
        >
</trim>
  • Mapping file
<!--  C. Trim label -- >
    <select id="if03" parameterType="map" resultMap="BaseResultMapper">
        SELECT
            <include refid="oa_user_columns" />
        FROM
            oa_user
        <trim prefix="WHERE " prefixOverrides="AND |OR ">
            <if test="name != null && name != ''">
                AND user_name LIKE CONCAT('%',#{name},'%')
            </if>
    
            <if test="sex != null and sex != ''">
                AND sex=#{sex}
            </if>
        </trim>
    </select>
  • Java test code
@Test
    Public void trim tag (){
        SqlSession sqlSession = null;
        try {
            sqlSession = MyBatisUtils.getSqlSession();
            Map<String,Object> query = new HashMap<>();
            query. Put ("name", "Wu");
            query. Put ("sex", "male");

            List<User> userList =
                    sqlSession.selectList(User.class.getName()+".if03", query);
            System.out.println(userList);


        } finally {
            MyBatisUtils.closeSqlSession(sqlSession);
        }
    }
  • Illustration

II. Update operation

1.set label

  • When you find content, add set to the front of the content
  • When you find something, check whether there is a comma at the end of the content. If you erase it
  • Mapping file
    <update id="update01" parameterType="com.shxt.model.User">
        UPDATE
            oa_user
        <set>
            <if test="user_name != null and user_name != ''">
                user_name=#{user_name},
            </if>
            <if test="sex != null and sex != ''">
                sex=#{sex},
            </if>
            <if test="money != null">
                money=#{money},
            </if>
            <if test="birthday != null">
                birthday=#{birthday},
            </if>
        </set>
        WHERE
            user_id=#{user_id}
    
    </update>
  • Java test code
@Test
    Public void update operation_ Change database (){
        SqlSession sqlSession = null;
        try {
            sqlSession = MyBatisUtils.getSqlSession();
            //Data
            User user = new User();
            user.setUser_id(3);
            user. setUser_ Name ("Marshal Tianpeng");
            //Date conversion
            String date = "1998-09-09";
            DateFormat df = new SimpleDateFormat("yyyy-MM-dd");

            user.setBirthday(df.parse(date));

            int row =sqlSession.update(User.class.getName()+".update01", user);
            System.out.println(row);

            //Commit of transaction
            sqlSession.commit();

        }catch (Exception ex) {
            ex.printStackTrace();
        }finally {
            MyBatisUtils.closeSqlSession(sqlSession);
        }
    }
  • Illustration

2. Trim label update completed

  • Mapping file
    <update id="update02" parameterType="com.shxt.model.User">
        UPDATE
            oa_user
        <trim prefix="SET " suffixOverrides=",">
            <if test="user_name != null and user_name != ''">
                user_name=#{user_name},
            </if>
            <if test="sex != null and sex != ''">
                sex=#{sex},
            </if>
            <if test="money != null">
                money=#{money},
            </if>
            <if test="birthday != null">
                birthday=#{birthday},
            </if>
       </trim>
        WHERE
            user_id=#{user_id}
    </update>
  • Java test code
@Test
    Public void update operation_ Trim label_ Change database (){
        SqlSession sqlSession = null;
        try {
            sqlSession = MyBatisUtils.getSqlSession();
            //Data
            User user = new User();
            user.setUser_id(3);
            user. setUser_ Name ("Marshal Tianpeng 123");
            //Date conversion
            String date = "1998-10-09";
            DateFormat df = new SimpleDateFormat("yyyy-MM-dd");

            user.setBirthday(df.parse(date));

            int row =sqlSession.update(User.class.getName()+".update02", user);
            System.out.println(row);

            //Commit of transaction
            sqlSession.commit();

        }catch (Exception ex) {
            ex.printStackTrace();
        }finally {
            MyBatisUtils.closeSqlSession(sqlSession);
        }
    }

Three choice tags are easy to use

  • Mapping file
<select id="choose01" parameterType="map" resultMap="BaseResultMapper">
        SELECT
            <include refid="oa_user_columns"/>
        FROM
            oa_user
        WHERE
            <choose>
                <when test='sex !=  Null and sex== "male"'>
                    money>777
                </when>
                <when test='sex !=  Null and sex== "female"'>
                    money>666
                </when>
                <otherwise>
                    1=1
                </otherwise>
            </choose>
    </select>
  • Java test code
@Test
    Public void choose tag (){
        SqlSession sqlSession = null;
        try {
            sqlSession = MyBatisUtils.getSqlSession();
            Map<String,Object> query = new HashMap<>();
            query. Put ("sex", "female 123213");

            List<User> userList =
                    sqlSession.selectList(User.class.getName()+".choose01", query);
            System.out.println(userList);


        } finally {
            MyBatisUtils.closeSqlSession(sqlSession);
        }
    }

Four less than question

  • Mapping file
<select id="less01" parameterType="double" 
        resultMap="BaseResultMapper">
        SELECT
            <include refid="oa_user_columns"/>
        FROM oa_user
        WHERE
            money <= #{money}
    </select>
    <select id="less02" parameterType="double" 
        resultMap="BaseResultMapper">
        SELECT
            <include refid="oa_user_columns"/>
        FROM oa_user
        WHERE
            <!--  It cannot contain labels -- >
            <![CDATA[
              money <= #{money}
            ]]>
          
    </select>
  • Java test code
@Test
    Solution of public void less than sign (){
        SqlSession sqlSession = null;
        try {
            sqlSession = MyBatisUtils.getSqlSession();

            List<User> userList =
                    sqlSession.selectList(User.class.getName()+".less01", 1.0*800);
            System.out.println(userList);

            userList =
                    sqlSession.selectList(User.class.getName()+".less02",  1.0*600);
            System.out.println(userList);


        } finally {
            MyBatisUtils.closeSqlSession(sqlSession);
        }
    }
  • Illustration

Please refer to Appendix 1 for instructions

V. dynamically adding statements

  • Mapping file
    <insert id="add01" parameterType="com.shxt.model.User"
        useGeneratedKeys="true" keyProperty="user_id"
    >
        INSERT INTO oa_user
            <trim prefix="(" suffix=")" suffixOverrides=",">
                <if test="user_name != null and user_name!=''">
                    user_name,
                </if>
                <if test="sex != null and sex!=''">
                    sex,
                </if>
                <if test="money != null">
                    money,
                </if>
                <if test="birthday != null">
                    birthday,
                </if>
            </trim>
            <trim prefix="VALUES (" suffix=")" suffixOverrides=",">
                 <if test="user_name != null and user_name!=''">
                    #{user_name},
                </if>
                <if test="sex != null and sex!=''">
                   #{sex},
                </if>
                <if test="money != null">
                   #{money},
                </if>
                <if test="birthday != null">
                    #{birthday},
                </if>
            </trim>
    </insert>
  • Java test code
@Test
    Public void dynamic add statement (){
        SqlSession sqlSession = null;
        try {
            sqlSession = MyBatisUtils.getSqlSession();
            //Data
            User user = new User();
            user. setUser_ Name ("Liu Bei 12333333");
            //Date conversion
            String date = "1998-10-09";
            DateFormat df = new SimpleDateFormat("yyyy-MM-dd");
            user.setBirthday(df.parse(date));

            user.setMoney(1111.11);

            int row =sqlSession.insert(User.class.getName()+".add01", user);
            System.out.println(row);

            //Commit of transaction
            sqlSession.commit();

            System.out.println(user);

        }catch (Exception ex) {
            ex.printStackTrace();
        }finally {
            MyBatisUtils.closeSqlSession(sqlSession);
        }
    }

Six foreach Tags

1. array method

  • Mapping method
<!--  Array deletion. If an array is deleted, please do not set the paramtertype to make it automatically recognized -- >
    <delete id="delete01" >
        DELETE FROM
            oa_user
        WHERE user_id in 
        <!-- 
            Traverse the array 
            If you just pass an array or a collection
            collection="array|list"
        -->
        <foreach 
            collection="array" item="shxt"
            open="(" close=")" separator=","
        >
            #{shxt}
        </foreach>
    </delete>
  • Java test code
@Test
    Public void pass array deletion rule (){
        SqlSession sqlSession = null;
        try {
            sqlSession = MyBatisUtils.getSqlSession();
            int row = sqlSession.delete(User.class.getName()+".delete01", new int[]{5,6});
            //Commit of transaction
            sqlSession.commit();
            System.out.println(row);
        }catch (Exception ex) {
            ex.printStackTrace();
        }finally {
            MyBatisUtils.closeSqlSession(sqlSession);
        }
    }

2. List mode

  • Mapping file
<!--  List set -- >
    <delete id="delete02" parameterType="list">
        DELETE FROM
            oa_user
        WHERE user_id in 
        <foreach 
            collection="list" item="shxt"
            open="(" close=")" separator=","
        >
            #{shxt}
        </foreach>
    </delete>
  • Java test code
@Test
    Public void delivery collection deletion rule (){
        SqlSession sqlSession = null;
        try {
            sqlSession = MyBatisUtils.getSqlSession();
            List<Integer> tempList = new ArrayList<>();
            tempList.add(8);
            tempList.add(9);
            int row = sqlSession.delete(User.class.getName()+".delete02", tempList);
            //Commit of transaction
            sqlSession.commit();
            System.out.println(row);
        }catch (Exception ex) {
            ex.printStackTrace();
        }finally {
            MyBatisUtils.closeSqlSession(sqlSession);
        }
    }

3. Map method

  • Mapping method
    <delete id="delete03" parameterType="map">
        DELETE FROM
            oa_user
        WHERE user_id in 
        <foreach 
            collection="id_array" item="shxt"
            open="(" close=")" separator=","
        >
            #{shxt}
        </foreach>
    </delete>
  • Java test code
@Test
    Public void delivery map deletion rule (){
        SqlSession sqlSession = null;
        try {
            sqlSession = MyBatisUtils.getSqlSession();
            List<Integer> tempList = new ArrayList<>();
            tempList.add(7);
            tempList.add(10);

            Map<String, Object> map = new HashMap<String, Object>();
            map.put("id_array", tempList);

            int row = sqlSession.delete(User.class.getName()+".delete03", map);
            //Commit of transaction
            sqlSession.commit();
            System.out.println(row);
        }catch (Exception ex) {
            ex.printStackTrace();
        }finally {
            MyBatisUtils.closeSqlSession(sqlSession);
        }
    }
  • Graphic mode

4. batch add

  • Mapping file
    <insert id="add02" parameterType="list">
        INSERT INTO
        oa_user
        VALUES
        <foreach collection="list" item="user" separator=","
        >
        (#{user.user_name},#{user.sex},#{user.money})
        </foreach>
    </insert>

Appendix 1: mybatis’ method for handling greater than and less than signs in XML files

The first method:

Replace > and < with escape characters, and then there is no problem.

SELECT * FROM test WHERE 1 = 1 AND start_date  <= CURRENT_DATE AND end_date >= CURRENT_DATE

Attachment: XML escape character

Second method

Because this is in XML format, characters like “>” are not allowed, but <! [cdata[]] > symbols are used for description, and such symbols are not parsed
You can write this:
Mapper file sample code

<![CDATA[ when min(starttime)<='12:00' and max(endtime)<='12:00' ]]>