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

This article mainly introduces the dynamic SQL statement parsing in Mybatis, which is introduced in great detail through the sample code, which has a certain reference value for everyone's study or work. Friends who need it can refer to it.

There are two ways to configure SQL in Mybatis, one is to use xml for configuration, and the other is to use annotations for configuration.

Mybatis uses annotations to configure SQL, but it is rarely used because of its limited configuration capabilities and poor readability for complex SQL.

Mybatis commonly used xml configuration way, using a few simple elements of xml, you can complete the function of dynamic SQL, a large number of judgments can be configured in the mybaties mapping xml, to achieve many functions that require a lot of code to achieve, greatly reduce the amount of code, reflecting the flexibility, high degree of configuration and maintainability of Mybatis.

If judgment statement single conditional branch judgment choose (when,otherwise) is equivalent to switch and case sentences in Java multiple conditional branch judgment trim auxiliary elements, used to deal with specific SQL assembly problems used to deal with SQL assembly problems foreach loop statements are commonly used in list conditions such as in statements

If element

The if element is the most commonly used judgment statement, which is equivalent to the if statement in Java China, and is often used in conjunction with the test attribute.

Select role_no, role_name, note from t_role where 1, 1 and role_name like concat ('%', # {roleName},'%')

When the parameter roleName is passed into the mapper, if the parameter is not empty, construct a fuzzy query to roleName, otherwise do not construct this condition. Through the if element of Mybaties, a lot of work of splicing SQL is saved, and maintenance is concentrated in xml.

Choose, when, otherwise elements

If there are more choices in judgment, there are not only two choices, but also similar to switch...case...default... The statement of function. In the mapped SQL statement, use choose, when, and otherwise elements to assume this function.

Select role_no, role_name, note from t_role where 1, AND role_no = # {roleNo} AND role_name like concat ('%', # {roleName},'%') AND note is not null

The above scenario is:

First, if the role number is not empty, only the role number is used as a conditional query.

When the role number is empty and the role name is not empty, a fuzzy query is made using the role name as a condition.

When both the role number and the role number are empty, the role comment is required to be not empty.

Trim, where, set elements

In the previous SQL statement, you added "1 to 1" to achieve its functionality, but a better implementation is to use where. When the condition in the where element is true, the SQL keyword where will be added to the assembled SQL, otherwise it will not be added.

Select role_no, role_name, note from t_role and role_name like concat ('%', # {roleName},'%') and note like concat ('%', # {note},'%')

Sometimes you need to remove some special SQL syntax, such as the common and, or, and so on. You can also use the trim element to achieve the desired results. Where prefix represents the prefix of the statement, and prefixOverrides represents what kind of string needs to be removed. Is equivalent to the previous where statement.

Select role_no, role_name, note from t_role and role_name like concat ('%', # {roleName},'%')

In hibernate, if you have to send all the fields to the persistent object because of updating a field, this affects the execution efficiency of the SQL statement. The best way is to pass the values of the primary key and update fields to SQL for update. The set element can do this. The set element encounters a comma and it automatically removes the corresponding comma.

Update t_role role_name = # {roleName}, note = # {note} where role_no = # {roleNo}

Foreach element

Foreach element is a circular statement, its function is to traverse the collection, it can well support the collection of arrays and List, Set interfaces, to provide traversal function, it is often used in the in keyword in SQL.

Select role_no, role_name, note from t_role where role_no in # {roleNo}

The roleNoList configured by collection is the parameter name passed in, which can be a collection of arrays, List, Set, and so on.

Item configures the current element in the loop.

Index configures the subscript of the current element at the location of the collection.

Open and close configure what symbols wrap these collection elements.

Separator is the delimiter of each element.

Judging a string by the attributes of test

Test is used in conditional judgment statements, which is equivalent to judging whether it is true or not. In most scenarios, it is mainly used to judge empty and non-empty ones.

Select role_no, role_name, note from t_role where 1 # 1

If you pass type='Y' to SQL, you can realize that Mybatis adds the condition where 1 to 1, so for string judgment, you can add the method of toString () for comparison.

Bind element

The purpose of the bind element is to define a context variable through an OGNL expression, which makes it easier to use.

For example, in a fuzzy query, if it is a MySQL database, a concat is often used, which is connected to the parameter with%. However, not in the Oracle database, the Oracle database uses the connection symbol "| |", so SQL needs to be implemented in two forms, using the bind element, instead of using the database language.

SELECT role_no, role_name, note FROM t_role where role_name like # {pattern}

The above is my summary of the common knowledge points of dynamic SQL sentences in Mybatis in the learning process. I hope you can learn and make progress together!

The above is the whole content of this article, I hope it will be helpful to your study, and I also hope that you will support it.

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.