Shulou(Shulou.com)05/31 Report--

This article mainly introduces the relevant knowledge of how to achieve MyBatis dynamic SQL, the content is detailed and easy to understand, the operation is simple and fast, and it has a certain reference value. I believe you will gain something after reading this MyBatis dynamic SQL article. Let's take a look at it.

One of the most powerful features of mybatis is its dynamic sql capability.

To borrow a passage from the official documentation: if you have previously used JDBC or similar frameworks, you will understand how painful it is to concatenate the conditions of SQL statements, and make sure you don't forget spaces or omit a comma after the columns column. Dynamic statements can completely solve these pains.

So how painful would it be without this function? Let's give an example.

This is a table. Imagine that if we query the table information through name and age, there must be where and and sentences in the sql statement, but if name or age has a null or both null, then the where and and will be isolated, so there will certainly be a lot of problems, so the dynamic sql function of mybatis helps us solve it perfectly.

The main elements used to implement dynamic SQL in MyBatis are:

If where trim set choose (when, otherwise) foreach

If and where SELECT e.idpene. Name ename,e.age,d.name dname FROM employee e LEFT JOIN dept d ON e.deptId = d.id e.name = # {name} and e.age = # {age}

Using this tag, dynamic sql can automatically help us improve sql according to conditions.

SqlSession sqlSession= MybatisUtil.getSqlSession (); EmployeeDao mapper=sqlSession.getMapper (EmployeeDao.class); / / create an object with set values Employee employee = new Employee (); employee.setName ("); employee.setAge (null); List employees = mapper.selectAllEmployee1 (employee); System.out.println (employees); sqlSession.commit (); sqlSession.close ()

For the first time, we all set null values, and the data in the table is completely queried.

The second time, we only check the age.

Employee.setName ("); employee.setAge (20)

Query two pieces of data at the age of 20, which is the power of mybatis dynamic sql.

Trim

In the above where and if, we can also use trim instead of where.

SELECT e.idjie. Name ename,e.age,d.name dname FROM employee e LEFT JOIN dept d ON e.deptId = d.id e.name = # {name} and e.age = # {age}

There are two properties, prefix, prefixOverrides

Prefix: stands for prefix. If there is a condition in if, the word where will be spelled before sql.

PrefixOverrides: automatically determines whether to remove or according to if conditions | and words and sentences

Accordingly, there are suffix and suffixOverrides, which represent the judgment of the tail.

Choose

Choose stands for multiple choice (choose one more)

SELECT e.id,e.name ename,e.age D.name dname FROM employee e LEFT JOIN dept d ON e.deptId = d.id and e.name = # {name} and e.age = # {age} and e.name ='Li Lei'

When the conditions are true, the statements in when are not valid.

Set

Set can automatically add set sentences according to conditions, update columns dynamically, or eliminate any irrelevant commas appended to the end of the condition.

Update employee name=# {name}, age=# {age}, where id=# {id} foreach

It is mainly used in building in conditions, which iterate over a collection in a SQL statement. The main attribute of the foreach element is item,index,collection,open,separator,close. Item represents the alias of each element in the collection when iterating. Index specifies a name to indicate the location of each iteration during the iteration. Open indicates where the statement begins, separator indicates what symbol is used as the separator between each iteration, and close indicates what ends with. The most important and error-prone attribute when using foreach is the collection attribute, which must be specified. However, the value of this property is different in different cases.

-if a single parameter is passed and the parameter type is a List, the value of the collection attribute is list

-if a single parameter is passed and the parameter type is an array array, the attribute value of collection is array

/ / create a list collection List list = new ArrayList (); list.add (19); list.add (20); List employees = mapper.selectAllEmployee2 (list)

The API method is as follows:

List selectAllEmployee2 (List list)

The corresponding dynamic sql is as follows:

SELECT e.idjie. Name ename,e.age,d.name dname FROM employee e LEFT JOIN dept d ON e.deptId = d.id where age in # {age}

What we pass in here is a collection, so the parameter is list. Through foreach, we can dynamically query according to the values in the collection.

This is the end of the article on "how to implement MyBatis dynamic SQL". Thank you for reading! I believe that everyone has a certain understanding of the knowledge of "how to achieve MyBatis dynamic SQL". If you want to learn more knowledge, you are welcome to follow the industry information channel.

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.