Shulou(Shulou.com)06/03 Report--

This article introduces the relevant knowledge of "the method of realizing dynamic SQL by MyBatis". In the operation of actual cases, many people will encounter such a dilemma, so let the editor lead you to learn how to deal with these situations. I hope you can read it carefully and be able to achieve something!

One of the most powerful features of MyBatis is its dynamic statement function. If you've used JDBC or similar frameworks before, you can see how painful it is to concatenate the conditions of SQL statements, make sure you don't forget spaces or omit a comma after the columns column, and so on. Dynamic statements can completely solve these pains.

Although working with dynamic SQL is not opening a party, MyBatis does improve these conditions by using powerful dynamic SQL in any mapping SQL statement.

If element

According to the conditional judgment of if elements, the most common thing that dynamic SQL does is to conditionally include where clauses. For example:

SELECT * FROM BLOG WHERE state = 'ACTIVE' AND title like # {title}

Where element

The where element knows to insert "where" if something is returned in the tag it contains. In addition, if the returned content starts with "AND" or "OR", it will remove "AND" or "OR".

Select * from t_student and name=# {name} and age=# {age}

Choose element

Sometimes we don't want to apply all the conditions, but we want to choose one from multiple options. Similar to the switch statement in java, MyBatis provides a choose element.

When element

When the condition in when is true, execute the statement in the when tag

Select * from t_student and name=# {name} and age=# {age}

Otherwise element

When all when is not set up, execute otherwise

Select * from t_student and name=# {name} and age=# {age} and name='jim'

Trim element

If the where element does not behave exactly as you think, you can also use the trim element to customize it.

If the if in trim is established, add a prefix with the definition of prefix= "". If you don't, you won't add it.

The trim element can also remove the keywords and or or specified after where and define it with prefixOverrides= "".

Select * from t_student and name=# {name} and age=# {age}

Set element

A similar solution in a dynamic update statement is called set, a set element that updates columns dynamically.

The set element dynamically configures the set keyword and is also used to eliminate any irrelevant commas appended to the end of the condition.

Update t_student name=# {name}, age=# {age}, where id=# {id}

Of course, smart you must want to know how to write the equivalent trim element, which looks like this:

Update t_student name=# {name}, age=# {age}, where id=# {id}

Note that in this case, we removed a suffix and appended a prefix.

Foreach element

Another frequently used function of dynamic SQL is set iteration, which is usually used in in conditionals

The foreach element is very powerful, allowing you to specify a collection that declares items and index variables that can be used inside the element. It also allows you to specify the start and end characters, or you can add a delimiter to the iterator. The smart thing about this element is that it doesn't accidentally append extra delimiters.

Select * from t_student where age in # {item}

The test method is as follows:

Public void findStudentByAge () {SqlSession sqlSession = null; try {sqlSession = MyBatisUtil.getsqlSession (); StudentDao studentDao = sqlSession.getMapper (StudentDao.class); List list= new ArrayList (); list.add (21); list.add (23); List liststudent = studentDao.findStudentByAge (list); System.out.println (liststudent);} catch (Exception e) {e.printStackTrace ();}}

The output sql result: select * from t_student where age in (item,item), showing student information with age of 21 and 23.

Settings element

Under the Setting element are some very important setting options for setting and changing the behavior of MyBatis in operation. The following table lists the attributes, default values, and functions supported by the Setting element.

[external link image transfer failed. The origin server may have a hotlink protection mechanism. It is recommended to save the image and upload it directly (img-vuQc9cUw-1576746278488) (E:\ javaEE Notes\ img\ QQ Browser screenshot 20191218153217.png)]

Complete configuration example:

Special characters in XML

If MyBatis is configured with XML, you will inevitably encounter some characters that are special to XML. Such as the less than sign "= 1 and authorId

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.