编程知识 cdmana.com

Mybatis dynamic SQL

dynamic SQL


What is dynamic SQL?

MyBatis It's described in the official documents of ?

dynamic SQL yes MyBatis One of the powerful features of . If you have JDBC Or something like that , You should be able to understand how to splice according to different conditions SQL How painful is the sentence , For example, when splicing, make sure you don't forget to add the necessary spaces , Also notice to remove the comma from the last column name of the list . Using dynamics SQL, You can get rid of this pain .

Usage dynamics SQL It's not easy , But the aid can be used for any SQL Powerful dynamics in mapping statements SQL Language ,MyBatis Significantly improves the ease of use of this feature .

If you used it before JSTL Or any class based XML Language's text processor , What's going on with you SQL Elements may feel familiar . stay MyBatis In the previous version , It takes time to understand a lot of elements . With the help of powerful based on OGNL The expression of ,MyBatis 3 Replaced most of the previous elements , Greatly reduced the types of elements , Now there are fewer kinds of elements to learn than half of the original .

let me put it another way , We can vary according to the input parameters , To execute different query conditions .

IF label :

How to use ?

So let's create one first Mapper Interface , The name for :UserMapper , And add a method

public interface UserMapper {
    public List<User> findByCondition(User user);
}

Create a xml file , The name for UserMapper.xml Then write SQL

<mapper namespace="com.dxh.dao.UserMapper">
    <select id="findByCondition" parameterType="com.dxh.pojo.User" resultType="com.dxh.pojo.User">
        SELECT * FROM user where 1=1
        <where>
            <if test="id != null">
                id = #{id}
            </if>
        </where>
    </select>
</mapper>

This SQL It means :

  • When id Not for null Is executed when SQL yes :SELECT * FROM user where id = #{id}
  • When id by null Is executed when SQL yes SELECT * FROM user where 1=1

Obviously we can see where 1=1 It's redundant , So we can write :

    <select id="findByCondition" parameterType="com.dxh.pojo.User" resultType="com.dxh.pojo.User">
        SELECT * FROM user where 1=1
        <where>
            <if test="id != null">
                id = #{id}
            </if>
        </where>
    </select>

test :

Write a test class :

package com.dxh.test;

import com.dxh.dao.UserMapper;
import com.dxh.pojo.User;
import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
import org.junit.Test;

import java.io.IOException;
import java.io.InputStream;
import java.util.List;

public class TestMain {
    @Test
    public void test1() throws IOException {
        InputStream resourceAsStream = Resources.getResourceAsStream("sqlMapConfig.xml");
        SqlSessionFactory build = new SqlSessionFactoryBuilder().build(resourceAsStream);
        SqlSession sqlSession = build.openSession();
        UserMapper mapper = sqlSession.getMapper(UserMapper.class);
        User user = new User();
        user.setId(1);
        List<User> byCondition = mapper.findByCondition(user);
        for (User user1 : byCondition) {
            System.out.println(user1);
        }
        System.out.println("======");
        User user2 = new User();
        List<User> byCondition2 = mapper.findByCondition(user2);
        for (User user3 : byCondition2) {
            System.out.println(user3);
        }

    }
}

We did it twice mapper.findByCondition(), Pass in separately user and user2, One of them id There are assigned values , A no , The final result is :

User{id=1, username='lucy'}
======
User{id=1, username='lucy'}
User{id=2, username=' Li Si '}
User{id=3, username='zhaowu'}

foreach label :

When we need to find out id by 1、2、3 What to do when ? SQL It should be written like this :SELECT * FROM user where id in (1,2,3). So use mybatis Of foreach How labels should be used ?

How to use ?

stay UserMapper Add a method to the interface : List<User> findByIds(int[] arr);

  public List<User> findByIds(int[] arr);

stay UserMapper.xml Written in :

    <select id="findByIds" parameterType="list" resultType="com.dxh.pojo.User">
        SELECT * FROM user
        <where>
            <foreach collection="array" open="id in (" close=")" item="id" separator=",">
                #{id}
            </foreach>
        </where>
    </select>

We can see ,foreach We used 5 It's worth :

  • collection Here's the type we're passing in , If it's an array, it's array , If it's a collection, it's list
  • open We said before SELECT * FROM user where id in (1,2,3) Correct SQL It should be written like this , that open It's to fill in the first half of what we need to splice
  • close Fill in the second half that we need to splice
  • item The values we need to traverse are id, So fill in id
  • separator ......where id in (1,2,3) 1,2,3 In between , Division .

test :

    @Test
    public void test2() throws IOException {
        InputStream resourceAsStream = Resources.getResourceAsStream("sqlMapConfig.xml");
        SqlSessionFactory build = new SqlSessionFactoryBuilder().build(resourceAsStream);
        SqlSession sqlSession = build.openSession();
        UserMapper mapper = sqlSession.getMapper(UserMapper.class);
        int[] arr={1,3};
        List<User> byCondition = mapper.findByIds(arr);
        for (User user1 : byCondition) {
            System.out.println(user1);
        }
    }

Output results :

User{id=1, username='lucy'}
User{id=3, username='zhaowu'}

correct ~


Last

Here are just two tags that are often used ,mybatis There are also a lot of labels in , such as choose、when、otherwise、trim、set wait

It's worth mentioning Mybatis Chinese is already supported on the official website of , Mother tongue looks more comfortable ~

https://mybatis.org/mybatis-3/zh/

版权声明
本文为[IsDxh]所创,转载请带上原文链接,感谢

Scroll to Top