MyBatis中example.createCriteria()方法的具體使用

更新時間：2024年10月27日 10:18:15 作者：????????一枚碼仔

本文詳細(xì)介紹了MyBatis的Example工具的使用方法,包括鏈?zhǔn)秸{(diào)用指定字段、設(shè)置查詢條件、支持多種查詢方式等,還介紹了mapper的crud方法、and/or方法的使用,以及如何進(jìn)行多條件和多重條件查詢,感興趣的可以了解一下

MyBatis 的 Example 是一種用于動態(tài)查詢構(gòu)造的工具，允許開發(fā)者通過簡單的 Java 對象來生成 SQL 語句。使用 Example，可以靈活地設(shè)置查詢條件，例如使用鏈?zhǔn)秸{(diào)用指定字段、條件和排序等。它支持多種查詢方式，如模糊查詢、精確查詢和范圍查詢，使得構(gòu)建復(fù)雜的查詢變得更加方便和直觀。通過 Example，開發(fā)者可以減少手寫 SQL 的工作量，提高代碼的可讀性和維護(hù)性。

一、mapper的crud方法:

1. insert方法

insert(User user)

插入一條數(shù)據(jù)，返回值是id

mapper.insert(User user)

insertSelective(User user)

插入一條數(shù)據(jù)，值為null的字段會做判空操作，不會添加 (推薦使用)

mapper.insertSelective(user)

2. select方法

selectByPrimaryKey(id)

根據(jù)主鍵查詢，返回的是個對象

mapper.selectByPrimaryKey(id)

selectByExample(example)

根據(jù)條件查詢，返回的是一個list

mapper.selectByExample(example)

selectCountByExample(example)

根據(jù)條件查詢后計數(shù)，返回的是int

mapper.selectCountByExample(example)

3. update方法

updateByPrimaryKey(User user)

根據(jù)主鍵修改，返回的是int

mapper.updateByPrimaryKey(user)

updateByPrimaryKeySelective(User user)

根據(jù)主鍵修改不為null的字段，返回的是int (建議使用)

mapper.updateByPrimaryKeySelective(user)

updateByExample(User user, Example example)

根據(jù)條件修改，返回的是int，注意:前面的參數(shù)user是要修改的內(nèi)容，后面的example是查詢條件，查到結(jié)果后將結(jié)果按user的值修改

mapper.updateByExample(user, example)

updateByExampleSelective(User user, Example example)

根據(jù)條件修改不為null的字段，返回的是int (建議使用)

mapper.updateByExampleSelective(user, example)

4. delete方法

deleteByPrimaryKey(id)

根據(jù)主鍵刪除，返回的是int

mapper.deleteByPrimaryKey(id)

deleteByExample(example)

根據(jù)條件刪除，返回的是int

mapper.deleteByExample(example)

二、and / or方法

and方法

1. andEqualTo(“field”, value)

表示條件為實體類字段"field"等于value值

Example example = new Example(WorkGuideModel.class);    
example.createCriteria().andEqualTo("createUserId","1").andEqualTo("isDelete",0);
List<WorkGuideModel> list = mapper.selectByExample(example);
return list;

執(zhí)行sql:

select * from tb_work_guide where( ( create_user_id = ? and is_delete = ? ) )

where后為什么會多兩層括號我也不知道…反正查詢結(jié)果是對的，下面為了美觀和方便，就手動把括號去掉了

另一種單參數(shù)寫法: 參數(shù)為map

Map<String, String> param = new HashMap<>();
param.put("createUserId","1"); 
param.put("isDelete","0");    

Example example = new Example(WorkGuideModel.class);  
example.createCriteria().andEqualTo(param);      
List<WorkGuideModel> list = mapper.selectByExample(example);   
return list;

2. andAllEqualTo（param）

與andEqualTo的單參數(shù)形式一樣，參數(shù)為map

3. andNotEqualTo(“field”, value)

與andEqualTo相反，條件為實體類字段"field"不等于value值，同時此方法沒有單參數(shù)

Example example = new Example(WorkGuideModel.class); 
example.createCriteria().andNotEqualTo("createUserId","1"); 
List<WorkGuideModel> list = mapper.selectByExample(example);    
return list;

執(zhí)行sql:

select * from tb_work_guide where create_user_id <> ?

4. andIn(“field”, list)

表示條件為實體類"field"字段的值包含ids里的值，與sql語句中的in()相同

List<Integer> ids = new ArrayList<>(); 
ids.add(1); 
ids.add(2);

Example example = new Example(WorkGuideModel.class);        example.createCriteria().andIn("createUserId",ids);    
List<WorkGuideModel> list = mapper.selectByExample(example);   
return list;

執(zhí)行sql:

select * from tb_work_guide where create_user_id in ( ? , ? )

list中不一定要是Integer類型，也可以是String

List<String> titles = new ArrayList<>();  
titles.add("標(biāo)題1"); 
titles.add("標(biāo)題2");   

Example example = new Example(WorkGuideModel.class);   
example.createCriteria().andIn("title",titles);   
List<WorkGuideModel> list = mapper.selectByExample(example);  
return list;

舉一反三:

與sql語句中的FIND_IN_SET]也相同

需要注意的是，F(xiàn)IND_IN_SET(str,strList)，這里的str為數(shù)據(jù)庫中的字段名，如create_user_id，而不是實體類的createUserId

執(zhí)行sql:

select * from tb_work_guide where FIND_IN_SET (create_user_id , ‘1,2’)

5. andNotIn(“field”, list)

與andIn()相反，查詢"field"字段不包含list中的值的結(jié)果

執(zhí)行sql:

select * from tb_work_guide where create_user_id not in ( ? , ? )

6. andIsNull(“field”)

表示實體類"field"字段為null

Example example = new Example(WorkGuideModel.class);
example.createCriteria().andIsNull("createUserId");
List<WorkGuideModel> list = mapper.selectByExample(example);
return list;

執(zhí)行sql:

select * from tb_work_guide where create_user_id is null

7. andIsNotNull(“field”)

與andIsNull()相反，表示實體類"field"字段不為null

Example example = new Example(WorkGuideModel.class);
example.createCriteria().andIsNotNull("createUserId");
List<WorkGuideModel> list = mapper.selectByExample(example);
return list;

執(zhí)行sql:

select * from tb_work_guide where create_user_id is not null

8. andBetween(“field”, value1, value2)

表示"field"字段的值在value1和value2之間，注意:這個區(qū)間是包頭包尾的，1 <= field <= 7

Example example = new Example(WorkGuideModel.class);
example.createCriteria().andBetween("createUserId",1,7);
List<WorkGuideModel> list = mapper.selectByExample(example);
return list;

執(zhí)行sql:

select * from tb_work_guide where create_user_id between ? and ?

9. andNotBetween(“field”, value1, value2)

與andBetween()相反，表示"field"字段的值不在value1和value2之間,注意:這個區(qū)間也是包頭包尾的，field < 1 或 field >7

Example example = new Example(WorkGuideModel.class);
example.createCriteria().andNotBetween("createUserId",1,7);
List<WorkGuideModel> list = mapper.selectByExample(example);
return list;

執(zhí)行sql:

select * from tb_work_guide where create_user_id not between ? and ?

10. andLike(“field”, str)

表示模糊查詢，注意:需要自己拼接%或_

String str = "繳費";   
Example example = new Example(WorkGuideModel.class);   
example.createCriteria().andLike("title","%"+str+"%");    
List<WorkGuideModel> list = mapper.selectByExample(example);  
return list;

執(zhí)行sql:

select * from tb_work_guide where title like ?

11. andNotLike(“field”, str)

與andLike相反的查詢，也需要自己拼接%或_

String str = "繳費";     
Example example = new Example(WorkGuideModel.class);
example.createCriteria().andNotLike("title","%"+str+"%");
List<WorkGuideModel> list = mapper.selectByExample(example);
return list;

執(zhí)行sql:

select * from tb_work_guide where title not like ?

12. andGreaterThan(“field”, value)

表示查詢"field"字段中大于value的值

Example example = new Example(WorkGuideModel.class);
example.createCriteria().andGreaterThan("age",20);
List<WorkGuideModel> list = mapper.selectByExample(example);
return list;

執(zhí)行sql:

select * from tb_work_guide where age > ?

13. andGreaterThanOrEqualTo(“field”, value)

與andGreaterThan()差不多，表示"field"字段中大于等于value的值

執(zhí)行sql:

select * from tb_work_guide where age >= ?

14. andLessThan(“field”, value)

表示查詢"field"字段中小于value的值

Example example = new Example(WorkGuideModel.class);
example.createCriteria().andLessThan("age",20);
List<WorkGuideModel> list = mapper.selectByExample(example);
return list;

執(zhí)行sql:

select * from tb_work_guide where age < ?

15. andLessThanOrEqualTo(“field”, value)

與andLessThan()差不多，表示"field"字段中小于等于value的值

執(zhí)行sql:

select * from tb_work_guide where age <= ?

16. andCondition(condition)

在where后直接拼接條件，注意:此時拼接的field字段名是數(shù)據(jù)庫的列名

寫法1:

直接寫完全where后面的條件語句，會自動將create_user_id = 1拼接到where后

Example example = new Example(WorkGuideModel.class);
example.createCriteria().andCondition("create_user_id = 1");
List<WorkGuideModel> list = mapper.selectByExample(example);
return list;

執(zhí)行sql:

select * from tb_work_guide where create_user_id = 1

寫法2:

andCondition(condition,value)，condition寫查詢字段(注意要記得加上=，不然會報錯)，value傳值，這樣value值就可以寫活了

Example example = new Example(WorkGuideModel.class);
example.createCriteria().andCondition("create_user_id = ",1);
List<WorkGuideModel> list = mapper.selectByExample(example);
return list;

執(zhí)行sql:

select * from tb_work_guide where create_user_id = ?

andCondition()這個方法寫的大佬都太少了，目前我只知道這是拼接作用的

or方法

or方法跟and方法其實差不多，只是and方法是在多條件中間拼接and，or方法是拼接or，下面就舉幾個例子加深印象，主要還是and和or一同使用

1. orEqualTo(param)

Map<String, String> param = new HashMap<>();
param.put("createUserId","1");
param.put("isDelete","0");

Example example = new Example(WorkGuideModel.class);
example.createCriteria().orEqualTo(param);
List<WorkGuideModel> list = mapper.selectByExample(example);
return list;

執(zhí)行sql:

select * from tb_work_guide where create_user_id = ? or is_delete = ?

2. orIn(“field”, list)

List<String> titles = new ArrayList<>();  
titles.add("標(biāo)題1");  
titles.add("標(biāo)題2");

Example example = new Example(WorkGuideModel.class);     
example.createCriteria().orIn("title", titles);  
List<WorkGuideModel> list = mapper.selectByExample(example);   
return list;

執(zhí)行sql:

select * from tb_work_guide where title in ( ? , ? )

從sql中可以看到，orIn和andIn的執(zhí)行sql是一樣的，其余方法就不測試了，大概是一樣的，只有多個條件時，中間的連接符號不同

如下:

Example example = new Example(WorkGuideModel.class);
example.createCriteria().orLike("title","%標(biāo)題1%").orLike("createUserName","%小明%");    
List<WorkGuideModel> list = mapper.selectByExample(example);
return list;

執(zhí)行sql:

select * from tb_work_guide where title like ? or create_user_name like ?

List<String> titles = new ArrayList<>();  
titles.add("標(biāo)題1");  
titles.add("標(biāo)題2"); 

Example example = new Example(WorkGuideModel.class);  
example.createCriteria().orIn("title",titles).orLike("createUserName","%小明%");  
List<WorkGuideModel> list = mapper.selectByExample(example);   
return list;

執(zhí)行sql:

select * from tb_work_guide where title in ( ? , ? ) or create_user_name like ?

and和or一同使用

a and ( b or c)

Example example = new Example(WorkGuideModel.class);   
Example.Criteria criteria = example.createCriteria();   
criteria.andEqualTo("userId",1);

Example.Criteria criteria2 = example.createCriteria();
criteria2.orLike("title","%標(biāo)題%").orBetween("age",1,5);
example.and(criteria2);

List<WorkGuideModel> list = mapper.selectByExample(example);   
return list;

執(zhí)行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<>();
titles.add("標(biāo)題1");    
titles.add("標(biāo)題2");    

Example example = new Example(WorkGuideModel.class);     
Example.Criteria criteria = example.createCriteria();      
criteria.andEqualTo("userId",1).andIn("title",titles);

Example.Criteria criteria2 = example.createCriteria();    
criteria2.orLike("name","%小明%").orBetween("age",1,5);       
example.or(criteria2);

List<WorkGuideModel> list = mapper.selectByExample(example);
return list;

執(zhí)行sql:

select * from tb_work_guide where ( create_user_id = ? and title in ( ? , ? ) ) or ( title like ? or open between ? and ? )

將代碼中的example.or(criteria2);改成example.and(criteria2);后會發(fā)現(xiàn)sql變成了:

select * from tb_work_guide where ( create_user_id = ? and title in ( ? , ? ) ) and ( title like ? or open between ? and ? )

總結(jié):

多條件查詢時，可以直接使用同一個條件構(gòu)造器，往里面直接添加條件就行。多重條件查詢時，如上面的兩個括號中的條件都要分別滿足時，可以分別創(chuàng)建條件構(gòu)造器，然后分別往里添加條件

Example.Criteria criteria = example.createCriteria();
Example.Criteria criteria2 = example.createCriteria();

兩個括號之間(也就是兩個條件構(gòu)造器之間)使用example的and或or來連接

example.and(criteria2);
example.or(criteria2);

到此這篇關(guān)于MyBatis中example.createCriteria()方法的具體使用的文章就介紹到這了,更多相關(guān)MyBatis example.createCriteria內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

您可能感興趣的文章: