Explain the basic usage of dynamic SQL in Java’s mybatis framework

Sometimes, @ r_ 419_ 2451 @ in the where condition of the statement, some safety judgment is required. For example, when querying according to a certain condition, if the incoming parameter is empty, the query result is likely to be empty. Maybe we need to find out all the information when the parameter is empty. Use Oracle sequence and MySQL function to generate ID. At this time, we can use dynamic @ R_ 419_ 2451@。 The following uses MySQL syntax and functions (such as string link function concat).

Selectkey tag in the insert statement, sequences are often used in Oracle and functions are used in MySQL to automatically generate the primary key of the inserted table, and methods are required to return the generated primary key. This effect can be achieved by using the selectkey tag of mybatis. The following example uses the MySQL database custom function nextval ('student ') to generate a key and set it to the studentID attribute in the passed in entity class. Therefore, after executing this method, the edge can obtain the generated key through this entity class.

Call the interface method and get the automatically generated key

Selectkey statement attribute configuration details:

If tag the if tag can be used in many types of @ R_ 419_ 2451 @ statement, we take query as an example. First, let's look at a very common query:

However, if studentname or studentsex is null at this time, this sentence is likely to report an error or the query result is empty. At this point, we use if dynamic @ R_ 419_ 2451 @ statement first judge. If the value is null or equal to an empty string, we will not judge this condition to increase flexibility. The parameter is the entity class studententity. Judge all the attributes in the entity class. If it is not empty, execute the judgment condition.

It is flexible to use. For a new entity class, we need to limit that condition. We only need to attach the corresponding value to the where condition. On the contrary, we can not judge in where without assigning a value.

Conditional judgment of IF + where when there are many if tags used for conditions in where, such a combination may lead to errors. We take the query statement in 3.1 as an example. When the Java code is called as follows:

If the parameter studentname in the above example is null, student will not be executed_ The judgment of the name column will directly lead to the redundant error @ r of the "where and" keyword_ 419_ 2451@。 At this time, we can use the where dynamic statement to solve the problem. The "where" tag will know that if it contains a return value in the tag, it will insert a "where". In addition, if the content returned by the tag starts with and or, it will be eliminated. The above example is modified as follows:

If + set UPDATE statement when the if tag is not used in the update statement, an error will be caused if one of the parameters is null. When using the if tag in the update statement, if the previous if is not executed, it may cause a comma redundant error. Use the set tag to dynamically configure the set keyword and eliminate any irrelevant commas appended to the end of the condition. After modification with the IF + set tag, if an item is null, it will not be updated, but the original value of the database will be maintained. Examples are as follows:

If + trim replaces the where / set tag. Trim is a more flexible tag to remove redundant keywords. It can practice the effects of where and set. Trim instead of where

Trim instead of set

Choose (when, otherwise) sometimes we don't want to apply all the conditions, but just want to choose one from multiple options. When using the if tag, as long as the expression in test is true, the conditions in the if tag will be executed. Mybatis provides the choose element. The if tag is related to (and), And choose is or (or). The choose tag determines whether the test conditions in its internal when tag are valid in order. If one of them is true, choose ends. When all the when conditions in choose are not satisfied, execute @ r_419)u 2451 @ in otherwise. Similar to the switch sentence in Java, choose is switch, when is case, and otherwise is default. For example, the following example is the same Write down all the conditions that can be restricted and use them. Choose will select a @ r whose when tag test is true from top to bottom_ 419_ 2451 @ execute. For security reasons, we use where to package choose and place more keywords than errors.

Foreach for dynamic @ r_ 419_ 2451 @ it is very necessary to iterate a set, usually for in conditions. The list instance will use "list" as the key, and the array instance will use "array" as the key. The foreach element is very powerful. It allows you to specify a collection, declare collection items and index variables, which can be used in the element body. It also allows you to specify open and closed strings and place separators between iterations. This element is smart, and it doesn't accidentally attach extra delimiters. Note: you can pass a list instance or array as a parameter object to mybatis. When you do this, mybatis will automatically wrap it in a map with the name as the key. The list instance will take "list" as the key, and the array instance will take "array" as the key. This section discusses about XML configuration files and XML mapping files. The next section will discuss the Java API in detail, so you can get the most effective mapping you have created.

1. The parameter is the method declaration of the writing interface of the array example:

Dynamic @ r_ 419_ 2451 @ statement:

Test code, query students in 20000001 and 20000002 classes:

2. The parameter is the method declaration of the writing interface of the list example:

Dynamic @ r_ 419_ 2451 @ statement:

Test code, query students in 20000001 and 20000002 classes:

3. Encapsulate the parameters into map type

The value of the above collection is IDS, which is the key of the passed in parameter map, and the corresponding mapper Code:

Corresponding test code: