MyBatis' Example is a tool for dynamic query construction that allows developers to generate SQL statements through simple Java objects. Using Example, you can set query conditions flexibly, such as specifying fields, conditions, and sorting using chain calls. It supports a variety of query methods, such as fuzzy queries, precise queries and scope queries, making it more convenient and intuitive to build complex queries. Through Example, developers can reduce the workload of handwritten SQL and improve the readability and maintenance of code.
1. Mapper's crud method:
1. insert method
insert(User user)
Insert a data, the return value is id
(User user)
insertSelective(User user)
Insert a data, a field with a value of null will perform a null operation and will not be added (recommended)
(user)
2. Select method
selectByPrimaryKey(id)
According to the primary key query, the returned object is
(id)
selectByExample(example)
According to the conditional query, the returned list is
(example)
selectCountByExample(example)
Counting according to the condition query, the returned int
(example)
3. Update method
updateByPrimaryKey(User user)
Modify according to the primary key, the return is int
(user)
updateByPrimaryKeySelective(User user)
Modify fields that are not null according to the primary key, and the return is int (recommended)
(user)
updateByExample(User user, Example example)
Modify according to the conditions, the return is int. Note: the previous parameter user is the content to be modified, the subsequent example is the query condition. After finding the result, modify the result according to the user's value.
(user, example)
updateByExampleSelective(User user, Example example)
Modify the field that is not null according to the conditions, and the return is int (recommended)
(user, example)
4. delete method
deleteByPrimaryKey(id)
Delete according to the primary key, the return is int
(id)
deleteByExample(example)
Delete according to the condition, the return is int
(example)
2. and/or method
and method
1. andEqualTo(“field”, value)
Indicates that the condition is the entity class field "field" equals the value value
Example example = new Example();
().andEqualTo("createUserId","1").andEqualTo("isDelete",0);
List<WorkGuideModel> list = (example);
return list;
Execute sql:
select * from tb_work_guide where( ( create_user_id = ? and is_delete = ? ) )
I don't know why there are two more layers of brackets after where... Anyway, the query result is correct. For the sake of aesthetics and convenience, I manually removed the brackets.
Another single parameter writing method: the parameter is map
Map<String, String> param = new HashMap<>();
("createUserId","1");
("isDelete","0");
Example example = new Example();
().andEqualTo(param);
List<WorkGuideModel> list = (example);
return list;
2. andAllEqualTo（param）
andandEqualToThe single parameter form is the same, the parameter is map
3. andNotEqualTo(“field”, value)
In contrast to andEqualTo, the condition is that the entity class field "field" does not equal the value value, and this method does not have a single parameter
Example example = new Example();
().andNotEqualTo("createUserId","1");
List<WorkGuideModel> list = (example);
return list;
Execute sql:
select * from tb_work_guide where create_user_id <> ?
4. andIn(“field”, list)
The value indicating that the condition is the "field" field of the entity class contains the value in ids, the same as in() in the sql statement
List<Integer> ids = new ArrayList<>();
(1);
(2);
Example example = new Example(); ().andIn("createUserId",ids);
List<WorkGuideModel> list = (example);
return list;
Execute sql:
select * from tb_work_guide where create_user_id in ( ? , ? )
The list does not have to be Integer type, but it can also be String
List<String> titles = new ArrayList<>();
("Title 1");
("Title 2");
Example example = new Example();
().andIn("title",titles);
List<WorkGuideModel> list = (example);
return list;
learn by analogy:
With the SQL statementFIND_IN_SET]Same
It should be noted that, FIND_IN_SET(str,strList), here str isField names in the database, such as create_user_id, instead of the createUserId of the entity class
Execute sql:
select * from tb_work_guide where FIND_IN_SET (create_user_id , ‘1,2’)
5. andNotIn(“field”, list)
Contrary to andIn(), querying the result of the "field" field does not contain the value in the list
Execute sql:
select * from tb_work_guide where create_user_id not in ( ? , ? )
6. andIsNull(“field”)
Indicates that the entity class "field" field is null
Example example = new Example();
().andIsNull("createUserId");
List<WorkGuideModel> list = (example);
return list;
Execute sql:
select * from tb_work_guide where create_user_id is null
7. andIsNotNull(“field”)
In contrast to andIsNull(), it means that the entity class "field" field is not null
Example example = new Example();
().andIsNotNull("createUserId");
List<WorkGuideModel> list = (example);
return list;
Execute sql:
select * from tb_work_guide where create_user_id is not null
8. andBetween(“field”, value1, value2)
The value indicating the "field" field is between value1 and value2, note:This range is at the beginning and end of the bag, 1 <= field <= 7
Example example = new Example();
().andBetween("createUserId",1,7);
List<WorkGuideModel> list = (example);
return list;
Execute sql:
select * from tb_work_guide where create_user_id between ? and ?
9. andNotBetween(“field”, value1, value2)
In contrast to andBetween(), the value of the "field" field is not between value1 and value2. Note:This range is also at the beginning and end of the bag, field < 1 or field >7
Example example = new Example();
().andNotBetween("createUserId",1,7);
List<WorkGuideModel> list = (example);
return list;
Execute sql:
select * from tb_work_guide where create_user_id not between ? and ?
10. andLike(“field”, str)
Indicates a fuzzy query, note: you need to splice % or _ by yourself
String str = "Pay the fees";
Example example = new Example();
().andLike("title","%"+str+"%");
List<WorkGuideModel> list = (example);
return list;
Execute sql:
select * from tb_work_guide where title like ?
11. andNotLike(“field”, str)
In the opposite query to andLike, you also need to splice % or _
String str = "Pay the fees";
Example example = new Example();
().andNotLike("title","%"+str+"%");
List<WorkGuideModel> list = (example);
return list;
Execute sql:
select * from tb_work_guide where title not like ?
12. andGreaterThan(“field”, value)
Indicates that the query value is greater than value in the "field" field
Example example = new Example();
().andGreaterThan("age",20);
List<WorkGuideModel> list = (example);
return list;
Execute sql:
select * from tb_work_guide where age > ?
13. andGreaterThanOrEqualTo(“field”, value)
It is similar to andGreaterThan(), which means that the value in the "field" field is greater than or equal to value.
Execute sql:
select * from tb_work_guide where age >= ?
14. andLessThan(“field”, value)
Indicates that the value in the query "field" field is smaller than value
Example example = new Example();
().andLessThan("age",20);
List<WorkGuideModel> list = (example);
return list;
Execute sql:
select * from tb_work_guide where age < ?
15. andLessThanOrEqualTo(“field”, value)
It is similar to andLessThan(), indicating that the value in the "field" field is less than or equal to value
Execute sql:
select * from tb_work_guide where age <= ?
16. andCondition(condition)
Direct splicing conditions after whereNote: At this time, the field name of the spliced field is the column name of the database
Writing method 1:
Directly write the conditional statement after where, and automatically splice create_user_id = 1 to where
Example example = new Example();
().andCondition("create_user_id = 1");
List<WorkGuideModel> list = (example);
return list;
Execute sql:
select * from tb_work_guide where create_user_id = 1
Writing 2:
andCondition(condition,value)，conditionWrite the query field (note that you must remember to add =, otherwise an error will be reported), and pass the value to the value, so that the value can be written alive
Example example = new Example();
().andCondition("create_user_id = ",1);
List<WorkGuideModel> list = (example);
return list;
Execute sql:
select * from tb_work_guide where create_user_id = ?
There are too few big guys who write about the andCondition() method. At present, I only know that this is the splicing effect.
or method
The or method is actually similar to the and method, but the and method is splicing and in the middle of multiple conditions, and the or method is splicing or. Let me give you a few examples to deepen your impression. The main thing is to use it together with or
1. orEqualTo(param)
Map<String, String> param = new HashMap<>();
("createUserId","1");
("isDelete","0");
Example example = new Example();
().orEqualTo(param);
List<WorkGuideModel> list = (example);
return list;
Execute sql:
select * from tb_work_guide where create_user_id = ? or is_delete = ?
2. orIn(“field”, list)
List<String> titles = new ArrayList<>();
("Title 1");
("Title 2");
Example example = new Example();
().orIn("title", titles);
List<WorkGuideModel> list = (example);
return list;
Execute sql:
select * from tb_work_guide where title in ( ? , ? )
From SQL, we can see that the execution of SQL between orIn and andIn is the same, and the other methods will not be tested. It is probably the same. When there are only multiple conditions, the connection symbols in the middle are different.
as follows:
Example example = new Example();
().orLike("title","%Title 1%").orLike("createUserName","%Xiao Ming%");
List<WorkGuideModel> list = (example);
return list;
Execute sql:
select * from tb_work_guide where title like ? or create_user_name like ?
List<String> titles = new ArrayList<>();
("Title 1");
("Title 2");
Example example = new Example();
().orIn("title",titles).orLike("createUserName","%Xiao Ming%");
List<WorkGuideModel> list = (example);
return list;
Execute sql:
select * from tb_work_guide where title in ( ? , ? ) or create_user_name like ?
and and or
a and ( b or c)
Example example = new Example();
criteria = ();
("userId",1);
criteria2 = ();
("title","%title%").orBetween("age",1,5);
(criteria2);
List<WorkGuideModel> list = (example);
return list;
Execute sql:
select * from tb_work_guide where ( ( user_id = ? ) and ( title like ? or age between ? and ? ) )
(a and b) or (c or d)
List<String> titles = new ArrayList<>();
("Title 1");
("Title 2");
Example example = new Example();
criteria = ();
("userId",1).andIn("title",titles);
criteria2 = ();
("name","%Xiao Ming%").orBetween("age",1,5);
(criteria2);
List<WorkGuideModel> list = (example);
return list;
Execute sql:
select * from tb_work_guide where ( create_user_id = ? and title in ( ? , ? ) ) or ( title like ? or open between ? and ? )
After changing (criteria2); in the code to (criteria2); you will find that SQL has become:
select * from tb_work_guide where ( create_user_id = ? and title in ( ? , ? ) ) and ( title like ? or open between ? and ? )
Summarize:
When querying multi-conditions, you can directly use the same condition constructor and add conditions directly into it. When querying multiple conditions, if the conditions in the two brackets above must be met separately, you can create a condition constructor separately and then add the conditions into it separately.
criteria = (); criteria2 = ();
Use example and or or to connect between two brackets (that is, between two condition constructors)
(criteria2); (criteria2);
This is the article about the specific use of the() method in MyBatis. For more related MyBatis content, please search for my previous articles or continue browsing the related articles below. I hope everyone will support me in the future!