Splicing of SQL Statements
In the previous configuration, whether basic additions, deletions, modifications, or association nesting, set nested queries, the use of SQL statements are very basic, data operations between tables. Turn back to the code you wrote in your freshman year and splice the SQL statements directly from the beginning, such as:
String sql = "Update user Set username = " + username + ", gender = " + gender + " Where id = " + id;
Such splicing of SQL statements will inevitably lead to unnecessary errors such as missing commas due to carelessness. Later, the use of string.Format formatting to splice the SQL statements, the statement readability is much better:
string.Format("Update FoodItem Set FoodName='{0}',FoodStyle='{1}',Price='{2}' where FoodID='{3}'", FoodName, FoodStyle, Price, FoodId);
Traditional JDBC is not convenient in dynamically configuring SQL statements according to different conditions, except that it has a little trouble in concatenating statements. If we want to inquire about the user information in a user table with the gender of "male" and the name of "Zhang San", we can easily write such a query statement:
SELECT * FROM user WHERE gender = male'AND username = Zhang San';
At this time, if we want to query the user information of "male" and "Guangzhou" in the city field, we have to rewrite a query sentence:
SELECT * FROM user WHERE gender = male'AND city = Guangzhou';
Obviously, the amount of code will become very large. If you are not sure about the parameters of some query requirements, you may have to write a lot of query statements to deal with different queries.
There are better solutions in configuring Mapper (SQL Mapper Profile) to reduce the amount of code (for example, the configuration of its mapping statements is said to reduce the amount of code by up to 95 percent). Because MyBatis provides some elements for dynamically configuring SQL statements, there are still many pits in the process of configuring -,, but at last it has been successfully configurated and run perfectly! This log summarizes its own configuration of if, choose and foreach elements, as well as the < where > tag, are very powerful!
if tag - Select Find Text
Basic Usage
When you look at the "if" element, you must all guess that its function is to make judgments. Put the "if" tag first, because it is the most basic and one of the most familiar elements for programmers. Yes, its role is conditional judgment, select different SQL statements to execute, see the examples:
With JavaBean, the following other examples use this entity class:
public class User implements Serializable {
/**
*
*/
private static final long serialVersionUID = 1L;
private int id;
private String username;
private String password;
private String gender;
private String email;
private String province;
private String city;
private Date birthday;
private int age;
public User() {
System.out.println("A Parametric-free Construction Method for Entering the Target Class....");
}
// Constructor initialization
public User(String username, String password, String gender, String email, String province,
String city, Date birthday) {
super();
System.out.println("A Parametric Construction Method for Entering the Target Class....");
this.username = username;
this.password = password;
this.gender = gender;
this.email = email;
this.province = province;
this.city = city;
this.birthday = birthday;
}
// Omit get() and set() methods
}SQL mapping configuration file:
<select id="UserActiveSearch_if" parameterType="User" resultType="User">
SELECT
U.username,
U.gender,
U.email,
U.city
FROM User U WHERE gender = 'male'
<if test="username != null">
AND U.username like #{username}
</if>
</select>In the configuration of the SQL statement above, the input parameters and mapping types are all User entity classes. As you can see, we need to query the information (username, gender, e-mail and city) of the users whose gender is "male" in the table, and when the username in the input parameters is familiar, we also use this username as a query bar. One of them is that we turn into user information in the query table with the gender of "male" and the name of "username" passed in. If the username in the instance object of the incoming parameter User class is null, instead of using the condition in the if tag as the query condition, you can write a simple test case to see the results of the run:
@Test
// if expression
public void TestActiveIf() throws IOException {
SqlSession sqlSession = dataConn.getSqlSession();
StringBuffer result = new StringBuffer();
User newUser = new User();
newUser.setUsername("Zhang San");
List<User> resultList = sqlSession.selectList("UserActiveSearch_if", newUser);
for(int i=0; i<resultList.size(); i++) {
User user = resultList.get(i);
result.append("User name: " + user.getUsername() + "\r\n");
result.append("Gender: " + user.getGender() + "\r\n");
result.append("Mail box: " + user.getEmail() + "\r\n");
result.append("City: " + user.getCity() + "\r\n");
System.out.println(result.toString());
result.setLength(0);
}
sqlSession.close();
}In the test case, we instantiate a User instance newUser and add the username attribute to it as "Zhang San", then the instance object is passed in as a parameter to query. According to the select statement configuration in the SQL mapping configuration file, we can query some information of the user's "Zhang San", such as e-mail, city, etc. The result is the same:
Successful query to get some information of user "Zhang San".
As mentioned above, if the username is empty in the User instance object we pass in, the condition in the if tag will not be valid. What kind of results will be queried?
@Test
// if expression
public void TestActiveIf() throws IOException {
SqlSession sqlSession = dataConn.getSqlSession();
StringBuffer result = new StringBuffer();
User newUser = new User();
// New User. setUsername ("Zhang San");
List<User> resultList = sqlSession.selectList("UserActiveSearch_if", newUser);
for(int i=0; i<resultList.size(); i++) {
User user = resultList.get(i);
result.append("User name: " + user.getUsername() + "\r\n");
result.append("Gender: " + user.getGender() + "\r\n");
result.append("Mail box: " + user.getEmail() + "\r\n");
result.append("City: " + user.getCity() + "\r\n");
System.out.println(result.toString());
result.setLength(0);
}
sqlSession.close();
}In the test case above, we annotate the sentence of setting username to "Zhang San". That is to say, in the incoming User instance object, the values of all attributes are null. Let's look at the running results again:
From the results of the query, we can see that because the conditional query does not enter the if tag, the entire query statement helps us to query all the gender information in the data table as "male".
Of course, we can also configure multiple if conditions in a select tag pair of SQL statements, and if one of the if conditions is not satisfied, it will not be used as a query condition.
Use the < where > tag pair
As we said above, we can configure multiple if condition judgments in a select tag pair, that is, a select statement, for example:
<select id="UserActiveSearch_if2" parameterType="User" resultType="User">
SELECT
U.username,
U.gender,
U.email,
U.city
FROM User U WHERE
<if test="city != null">
U.city = #{city}
</if>
<if test="username != null">
AND U.username = #{username}
</if>
</select>In this configuration, even if the username is empty, one of the if conditions is not valid, but the other if condition is valid, such as the incoming City attribute is "Guangzhou", the query statement can correctly query all the users whose city is "Guangzhou":
That's all right. Next, let's look at a special case. Suppose we pass in a User class instance object with null attributes in this SQL configuration statement query. What happens?
As a result, the program will go wrong! Why, because if all the attributes in the object we pass in are null, which means that the query statement configured in the select tag and all if conditions will not be used as query conditions, then the whole SQL statement becomes:
SELECT U.username, U.gender, U.email, U.city FROM User U WHERE;
That's right. Such an SQL statement must be a query error. Another hidden dangerous error that is not easy to detect is that if our first if condition "city!= null" is not valid, but the second if condition is valid, the query will also make a mistake. Why? Let's see what happens to the entire SQL statement if the first if fails but the second if does:
SELECT U.username, U.gender, U.email, U.city FROM User U WHERE AND U.username = username;
Such SQL statements, "WHERE AND" are also incorrect, which can lead to query failure. What can we do? The solution is to use < where > tag pairs to wrap up if condition judgments that we need to use:
<select id="UserActiveSearch_if" parameterType="User" resultType="User">
SELECT
U.username,
U.gender,
U.email,
U.city
FROM User U
<where>
<if test="city != null">
U.city = #{city}
</if>
<if test="username != null">
AND U.username like #{username}
</if>
</where>
</select>After wrapping, the "where" clause will be inserted into the SQL statement only if at least one sub-element if tag pair is correct in the < where > tag pair, and the "AND" and "OR" at the beginning of the if tag pair will be removed automatically (otherwise the spliced SQL statement will be wrong). Let's look at the test results after using the < where > tag pair. First, the input parameters are given to the user name only. See if < where > helps us ignore the "AND" in front of the clause.
As we expected, next to the test passing in objects with null attributes, <where> tag pairs did not have an if condition judgment.
Since none of the if tags are valid, "where" is not spliced into the SQL statement, the entire SQL statement becomes:
SELECT U.username, U.gender, U.email, U.city FROM User U
This is to help us query all the user data in the data table.
choose Label - Multiple Choices
The above < where >, < if > tag pairs are simple to configure and powerful to query multiple conditions. As long as the < if > tag pairs satisfy the conditions, they will be stitched into the SQL statements. Next, the label is: choose, when, otherwise. The function is to select one of the multiple when tag conditional statements as the query condition and to stitch it into the SQL statement. It is similar to the switch statement. Choose corresponds to switch, when corresponds to case, and other wise corresponds to default. Let's see how it is configured:
<select id="UserActiveSearch_choose" parameterType="User" resultMap="UserActiveResultMap">
SELECT
U.username as user_name,
U.gender as user_gender,
U.email as user_email,
U.city as user_city
FROM User U
<where>
<choose>
<when test="username != null and username != ''">
username = #{username}
</when>
<when test="email != null and email != ''">
AND email = #{email}
</when>
<otherwise>
AND id = 6
</otherwise>
</choose>
</where>
</select>Each when tag gives a conditional judgement. According to this example, if username is not empty, select it as the query condition. When the when tag in the choose tag pair matches a condition successfully, it will exit the choose tag. If the query conditions in the two when tags do not meet, select the conditions in other wise as the query conditions. You can write a simple test case to see its execution:
In the first test, username is given, which is no problem. Choose will choose it as a query condition to be spliced into the SQL statement. Next, if we give username and email, and this email attribute does not belong to the user of the username, see how the choose tag can help us to query:
As you can see, it abandons the second conditional query. After choosing the first when tag condition username, the following email condition is abandoned. As mentioned above, choose is to select one of several conditions to execute.
The third test is to import instance objects with empty attributes. According to the configuration, the conditions in the two when tags do not meet. Then the final test is to select the conditions in the other wise tag, that is, to query the user information with id=6:
That's exactly what happened.
foreach tag - traversal collection
Both C# and Java have been exposed to foreach loop statements. Compared with the common for loop, the common for loop must determine the number of times the loop is executed, while the foreach loop is not required, and it is suitable for loop traversal with uncertain number of cycles. There is also a foreach tag in MyBatis. Its function is to facilitate a collection. Suppose we have an example where we need to query user information with IDs of 1, 3 and 5 in the data table. The SQL statement can be written as follows:
SELECT * FROM User WHERE id in(1，3，5)；
perhaps
SELECT * FROM User WHERE id=1 or id=3 or id=5；
This is OK, but there is a small disadvantage that we hard-coded these id "collections" into the SQL statement. If we want to query the user information of another group of IDS next time, we have to change the SQL statement, and if there are more ids, the SQL statement will become very long. The solution is to use the foreach tag, put a set of IDS into the collection, and then use the collection as an input parameter, use the foreach tag to traverse the collection, and take out one of the elements as a query condition, which can greatly reduce the amount of code. Take a look at the configuration of the foreach tag pair:
<!-- Splicing WHERE (id=1 or id=3 or id=5) -->
<select id="UserActive_foreach1" resultMap="UserActiveResultMap">
SELECT
U.id as user_id,
U.username as user_name,
U.gender as user_gender,
U.email as user_email,
U.city as user_city
FROM User U
<where>
<foreach collection="list" item="user_id" open="(" separator="or" close=")">
<!-- Strings spliced together at each traversal -->
id=#{user_id}
</foreach>
</where>
</select>In this example, we configure the following SQL statements with the foreach tag:
SELECT * FROM User WHERE id in(1，3，5)；
In the foreach tag pair, the type collection= "list" of the specified collection is specified, and item is the object name of the collection item obtained by each iteration. The next three attributes are important! open specifies the splicing prefix, which is equal to "("); separator specifies that the splicing string between two traversal objects, such as id1 and 3, should be spliced well ","; and close specifies the string to be spliced at the end of the traversal. Finally, the label specifies what each traversal should do, which is id = traversal object, that is, traversal id=1, id=3, id=5.
After the SQL mapping configuration file is configured, you can write a simple test case test result.
@Test
// foreach expression
public void TestActiveForeach() throws IOException {
SqlSession sqlSession = dataConn.getSqlSession();
StringBuffer result = new StringBuffer();
List<Integer> idNum = new ArrayList<Integer>();
for(int j=1; j<6; j+=2) {
idNum.add(j);
}
List<User> resultList = sqlSession.selectList("UserActive_foreach1", idNum);
for(int i=0; i<resultList.size(); i++) {
User user = resultList.get(i);
result.append("user id: " + user.getId() + "\r\n");
result.append("User name: " + user.getUsername() + "\r\n");
result.append("Gender: " + user.getGender() + "\r\n");
result.append("Mail box: " + user.getEmail() + "\r\n");
result.append("City: " + user.getCity() + "\r\n");
System.out.println(result.toString());
result.setLength(0);
}
sqlSession.close();
}As you can see, we initialized an Integer generic collection idNum and passed it into the query as a parameter.
The test results are as we expected. To master the foreach tag pair, the key is to configure the three attributes of open, separator and close. We can configure another traversal statement above with it:
SELECT * FROM User WHERE id=1 or id=3 or id=5；
<!-- Splicing WHERE id in (1,3,5) -->
<select id="UserActive_foreach2" resultMap="UserActiveResultMap">
SELECT
U.id as user_id,
U.username as user_name,
U.gender as user_gender,
U.email as user_email,
U.city as user_city
FROM User U
<where>
<foreach collection="list" item="user_id" open="id in (" separator="," close=")">
<!-- Strings spliced together at each traversal -->
#{user_id}
</foreach>
</where>
</select>Simply change the stitching string between traversing objects to "or".
The complete code has been uploaded to GitHub:
https://github.com/justinzengtm/SSM-Framework