`springdata-jpa` 普通查询方法示例

浅浅的花香味﹌ 2023-10-05 21:44 10阅读 0赞

### 等于 `=` ###

Query query = new Query();
    if(age!=null){
        
    Criteria criteria = Criteria.where("age").is(age);
    query.addCriteria(criteria)
    }
    if(name!=null){
         Criteria criteria = Criteria.where("name").is(name); query.addCriteria(criteria) }
    userRepository.findAll(mongoTemplate, query, User.class);

**注意** 非查询 `!=`  
`Criteria criteria = Criteria.where("age").is(age).not();`

### 并查询 `and` ###

上一条其实就包含 `and` 查询，为了明显有 `and` 痕迹，可以如下写：

Query query = new Query(); 
    if(age!=null && name!=null){
         
    	Criteria criteria1 = Criteria.where("age").is(age); 
    	Criteria criteria2 = Criteria.where("name").is(name);
    	query.addCriteria(criteria1).addCriteria(criteria2); 
    } 
    userRepository.findAll(mongoTemplate, query, User.class);

### 或查询 `or` ###

Query query = new Query(); 
    if(name!=null){
         
    	Criteria criteria = new Criteria(); 
    	Criteria criteria1 = Criteria.where("firstName").is(name); 
    	Criteria criteria2 = Criteria.where("lastName").is(name); 
    	query.addCriteria(criteria .orOperator(criteria1,criteria2 )); 
    }
    userRepository.findAll(mongoTemplate, query, User.class);

### 模糊查询 `like` ###

至少匹配一次

Query query = new Query(); 
    if(name!=null){
         
    	//至少匹配一次 
    	Criteria criteria = Criteria.where("name").regex(name + "{1}"); 
    	query.addCriteria(criteria) 
    } 
    userRepository.findAll(mongoTemplate, query, User.class);

左匹配

Query query = new Query(); 
    if(name!=null){
         
    	Criteria criteria = Criteria.where("name").regex("^" + name + "{1}.*"); 
    	//左匹配 
    	query.addCriteria(criteria) 
    } 
    userRepository.findAll(mongoTemplate, query, User.class);

右匹配

Query query = new Query(); 
    if(name!=null){
         
    	Criteria criteria = Criteria.where("name").regex(".*" +name + "{1}$"); 
    	//右匹配 
    	query.addCriteria(criteria) 
    } 
    userRepository.findAll(mongoTemplate, query, User.class);

**模糊查询的另一类写法**  
使用 `Pattern` 类

Query query = new Query(); 
    if (StringUtils.isNotBlank(searchText)) {
         
    	Criteria criteria = new Criteria(); 
    	Pattern pattern = Pattern.compile("^.*" + searchText + ".*$", Pattern.CASE_INSENSITIVE);
    	criteria.orOperator(Criteria.where("number").regex(pattern), Criteria.where("name").regex(pattern)); 
    	query.addCriteria(criteria); 
    	List<User>= userRepository.findAll(mongoTemplate, query, User.class); 
    }

### 排序 `sort` ###

Query query = new Query(); 
    String orderProperty = "name"; 
    Order order = new Order(Direction.DESC,orderProperty); 
    Sort sort = new Sort(order); 
    query.with(sort); 
    if(age!=null){
         
    	Criteria criteria = Criteria.where("age").is(age);
    	query.addCriteria(criteria) 
    } 
    if(name!=null){
         
    	Criteria criteria = Criteria.where("name").is(name);
    	query.addCriteria(criteria) 
    } 
    userRepository.findAll(mongoTemplate, query, User.class);

排序也可以用下面方式简单书写:  
`query.with(new Sort(new Order(Direction.ASC,"name")));`  
或  
`query.with(new Sort(new Sort.Order(Sort.Direction.ASC,"name")));`

### in查询 `in` ###

Query query = new Query(); 
    List<String> nameList = new ArrayList<>(); 
    nameList.add("aaa"); 
    nameList.add("aaa"); 
    if(nameList!=null && nameList.size()>0){
         
    	Criteria criteria = Criteria.where("name").in(nameList);
    	 //右匹配
    	 query.addCriteria(criteria) 
     } 
     userRepository.findAll(mongoTemplate, query, User.class);

### between ###

![在这里插入图片描述][20210715172554397.png]  
错误的做法是：用 `or` 代替  
![在这里插入图片描述][2021071517262386.png]

[20210715172554397.png]: https://img-blog.csdnimg.cn/20210715172554397.png
[2021071517262386.png]: https://img-blog.csdnimg.cn/2021071517262386.png