MyBatis dynamic SQL is the most favorite feature

Before you can understand dynamic SQL, you need to know an expression OGNL, this is the foundation!

  • What is the difference between $and # in Mybatis?
  • Select id,name,age from test where id =#{id} Select id,name,age from test where id = 1
  • Select id,name,age from test where id = “select id,name,age from test where id =” S e l e c t I d, n a m e, a g e f r o m t e s t w h e re I d = {id}, Select id,name,age from test where id = 1.
  • Using # prevents SQL injection to a large extent. (Statement concatenation)

If the label

  • mapperselect from test where 1=1 and username like concat(‘%’, #{username}, ‘%’) and ip=#{ip}
  • Map this method to the Mapper interface List selectByTestSelective(Test example);

There are corresponding methods for each label below, but they are not listed below. Please refer to them as follows

List<Test> selectByExample(TestExample example);
List<Test> selectByTestSelective(Test example);
List<Test> selectByIdOrUserName(Test example);
List<Test> selectByTestSelectiveWhereTag(Test example);
List<Test> selectByTestIdList(List<Integer> ids);
int insertList(List<Test> students);
int updateTestSetTag(Test example);
int selectSelectiveTrim(Test example);
Copy the code
  • test
@RequestMapping(value = "/dongtaiSql") @ResponseBody public void dongtaiSql() { Test example = new Test(); Example. SetUsername (" weeks "); List<Test> selectByTestSelective = testMapper.selectByTestSelective(example); for (Test test : selectByTestSelective) { System.out.println(test.getUsername()); }}Copy the code
  • Print the result

  • That is, it will concatenate the SQL based on what value you pass, and it won’t concatenate the SQL based on what value you pass, which is relatively simple and easy to understand.

[Note] All requests below are made through Postman.

The include tag

  • A very useful auxiliary tag, used to put some common return result set, easy to use other query methods, such as in mapper use as follows: username, lastloginTime select from test where id = #{id,jdbcType=BIGINT}

Use the choose label together with the WHEN and otherwise labels

The Choose When Otherwise tag helps us implement the if else logic. A choose tag has at least one when,

An otherwise at most.

  • mapperselect from test where 1=1 and id=#{id} and username=#{username} and 1=2
  • Print the result

I can’t find Zhou, because I only have Jay or Jay. The function of “choose” is similar to that of “if”, but different from “if”, “choose” is similar to “if”, and “IF” is that you pass all the conditions, and I judge your conditions one by one and then check them for you. If is more applicable to form queries. And choose more… In fact, these two achieve the same purpose, I prefer to use choose.

Where the label

  • mapperselect from test and username like concat(‘%’, #{username}, ‘%’) and ip=#{ip}
  • The results of

  • I did not pass any conditions, he could not find matching conditions in where, so he looked up all the conditions and gave me, this is actually similar to the last condition of the above choose, the effect is the same as 1=1.

The foreach tag

  • mapperselect from test where id in #{id}

code

@RequestMapping(value = "/dongtaiSql3") @ResponseBody public void dongtaiSql3() { ArrayList<Integer> arrayList = new ArrayList<Integer>(); arrayList.add(6); arrayList.add(5); List<Test> selectByTestSelective = testMapper.selectByTestIdList(arrayList); for (Test test : selectByTestSelective) { System.out.println(test.getUsername()); }}Copy the code
  • The results of

  • Foreach can also be used to insert data in batches, such as:
  • mapperinsert into test(username, gender, ip) values ( #{test.username}, #{test.gender},#{test.ip} )
  • code
Test example2 = new Test(); Example2.setusername (" Aaron Kwok "); example2.setGender(1); example2.setIp("123232113122"); Test example3 = new Test(); Example 3.setUsername(" qiu Shuzhen "); example3.setGender(0); example3.setIp("123232113333"); ArrayList<Test> arrayList = new ArrayList<Test>(); arrayList.add(example); arrayList.add(example2); arrayList.add(example3); int selectByTestSelective = testMapper.insertList(arrayList); If (selectByTestSelective == 1) {system.out.println (" batch Insert: "+arrayList.size()+" data "); }}Copy the code
  • The results of

  • The mapper here has to be restarted every time it is modified, which is very troublesome. #{test.username}, #{test.gender},#{test. IP} ParameterType is not required to be declared when passing non-entity class parameters such as list.
  • Foreach variable description Collection: Mandatory, collection/array /Map name item: variable name That is, each value fetched from the iterated object index: the attribute name of the index. Separator: The separator for each loop. Separator: The separator for each loop

The bind tag

  • Use BIND to enable the SQL to support both databases
  • mapperselect from test where 1=1 and username like #{nameLike} and ip=#{ip}
  • code
@RequestMapping(value = "/dongtaiSql5") @ResponseBody public void dongtaiSql5() { Test example = new Test(); Example. SetUsername (" weeks "); example.setIp("cium"); example.setId(27); int selectByTestSelective = testMapper.updateTestSetTag(example); System.out.println(selectByTestSelective); }Copy the code
  • The results of

Discovery can still be. If you want to use a fuzzy search for username, you can use it if you want to use it elsewhere. Use #{nameLike} after like.

The set tag

This tag is often used for modifying statements such as

  • mapperUPDATE Products username = #{username}, ip = #{ip}, id = #{id}
  • code
@RequestMapping(value = "/dongtaiSql5") @ResponseBody public void dongtaiSql5() { Test example = new Test(); Example. SetUsername (" weeks "); example.setIp("cium"); example.setId(27); int selectByTestSelective = testMapper.updateTestSetTag(example); System.out.println(selectByTestSelective); }Copy the code
  • The results of

  • This set is essentially a flexible operation of the UPDATE statement set.

Trim tabs

  • mapperselect * from test AND username=#{username} AND ip=#{ip}
  • 【 note 】
  • There are many holes here. First, mybatis- Plus is not PrefixOverride but prefixOverrides
  • Then “AND | OR” must have a space, causes the diagram below
  • The length() method cannot be used if IP is not a string

This section describes the trim label parameters

Prefix: Prefix SQL statements within the trim tag. Suffix: Suffix SQL statements within the trim tag. SuffixOverrides: removes redundant suffixes, for example, suffixOverrides=",", to remove redundant suffixes "," in the SQL statement in the trim tag.Copy the code

However, WHEN I am in configuration, I encountered more pit problems, which can not be solved tardy… Welcome interested friends to communicate with us to solve the last problem.

Source: www.tuicool.com/articles/36…