Spring Data MongoDB @Query Annotation

By Arvind Rai, October 23, 2019

This page will walk through Spring Data MongoDB @Query annotation examples. The @Query annotation declares finder queries directly on repository methods. It also allows using placeholder notation of ?0, ?1 and so on. Find the elements of @Query annotation.
value: Takes query as JSON string.
sort: Defines a default sort order.
fields: Defines the fields that will be returned by query.
exists: Boolean value to decide whether query should be executed as exists projection.
delete: Boolean value to decide whether query should delete matching documents.
count: Boolean value to decide whether query should be executed as count projection.
collation: Defines the collation to apply when executing query.

Here on this page we will discuss using elements of @Query in detail with examples.

1. Technologies Used
2. value
- 2.1 Using gt (>) and lt (<)
- 2.2 Operator: AND, OR
3. fields
4. count
5. exists
6. sort
7. delete
8. collation
9. Complete Example
10. References
11. Download Source Code

1. Technologies Used

Find the technologies being used in our example.
1. Java 11
2. Spring 5.1.9.RELEASE
3. Spring Data 2.1.10.RELEASE
4. Spring Boot 2.1.7.RELEASE
5. MongoDB Server 4.0
6. Maven 3.5.2

2. value

The value needs a query in JSON format. Find a method annotated with @Query that will fetch data for the given id.

@Component
public interface BookRepository extends MongoRepository<Book, Integer> {
	@Query(value = "{id : ?0}")
	Book findBookById(int id);
}

It can also be written as following.

@Component
public interface BookRepository extends MongoRepository<Book, Integer> {
	@Query("{id : ?0}")
	Book findBookById(int id);
}

Find one more example.

@Component
public interface BookRepository extends MongoRepository<Book, Integer> {
        @Query("{writer : ?0, category : ?1}")
        Stream<Book> findBooksByWriterAndCategory(String writer, String category);
}

The above query will fetch data as Java Stream for matching 'writer' and 'category'. While fetching data from Stream, we need to close it. For convenience we can use Java Try-With-Resources that will close Stream automatically after use.

try (Stream<Book> stream = repository.findBooksByWriterAndCategory("Krishna", "Frontend")) {
     stream.forEach(book -> System.out.println(book));
}

2.1 Using gt (>) and lt (<)

We can use gt (>) and lt (<) with @Query in JSON query. For gt (>) we need to use $gt and for lt (<), we need to use $lt.
Find the example for $gt.

@Query("{noOfPages : {$gt: ?0}}")
Stream<Book> findBooksGtThanNoOfPages(int noOfPages);

The above method will return such data whose noOfPages are greater than the given noOfPages.
Find the example for $lt.

@Query("{writer : ?0, noOfPages : {$lt: ?1}}")
Stream<Book> findBooksByWriterAndLtThanNoOfPages(String writer, int noOfPages);

The above method will return such data whose writer property matches the given writer and noOfPages are lesser than the given noOfPages.

2.2 Operator: AND, OR

The @Query in MongoDB uses $or for OR operator and it uses $and for AND operator. The syntax for $or is as following.

{$or : [{}, {}]}

The result will be populated first by using first {} condition and if there is no data then $or will use second {} condition.
Find the example for $or operator.

@Query("{$or : [{writer: ?0}, {category : ?1}]}")
Stream<Book> findBooksByWriterOrCategory(String writer, String category);

Find the example for $and operator.

@Query("{$and : [{$or : [{noOfPages: {$gt: 275}}, {noOfPages : {$lt: 200}}]}, {$or : [{id: {$gt: 103}}, {id : {$lt: 102}}]}]}")
Stream<Book> findBestBooks();

3. fields

The fields element of the @Query defines the fields to be returned for the given query. The domain object will keep value only in specified fields and @Id field and rest of the fields will be null. We need to set fields value with 1 to populate its value. The fields of domain which has been set to 0 or not specified will have null value. Find the example.

@Query(value = "{writer : ?0, category : ?1}", fields = "{ 'title' : 1, 'noOfPages' : 1, 'writer' : 1}")
Stream<Book> findBooksWithCertainFields(String writer, String category);

4. count

The count element of the @Query decides whether the query defined should be executed as count projection. count is assigned as Boolean value. When we specify true, the method returns count of the query result. Find the example.

@Query(value = "{category : ?0}", count = true)
Integer findBookCountByCategory(String category);

5. exists

The exists element of the @Query decides whether the query defined should be executed as exists projection. exists is assigned as Boolean value. When we specify true, the method returns true/false value. If methods returns false, it means query result count is zero.

@Query(value = "{writer : ?0}", exists = true)
Boolean isBooksAvailableByWriter(String writer);

6. sort

The sort element of the @Query defines the default sort order for the given query. The value -1 is used for descending order and 1 is used for ascending order. Find the example.
a. Sort data in ascending order of title.

@Query(value = "{writer : ?0}", sort = "{title : 1}")
Stream<Book> findBooksByWriter(String writer);

b. Sort data in descending order of title.

@Query(value = "{category : ?0}", sort = "{title : -1}")
Stream<Book> findBooksByCategory(String category);

We can also change the default behavior of sorting by passing Spring Data Sort object as an argument of the method.

7. delete

The delete element of the @Query decides whether the query should delete matching documents. delete is assigned as Boolean value. When we specify true, the method deletes the query matching data and returns count of deleted rows.

@Query(value = "{category : ?0}", delete = true)
Long deleteBooksByCategory(String category);

8. collation

The collation element of the @Query defines the collation to apply when executing the query. Find the example.

@Query(value = "{category : ?0}", collation = "en_US")  
Stream<Book> findBooksByCategory(String category);

@Query(value = "{writer : ?0}", collation = "{ 'locale' : '?1' }") 
Stream<Book> findBooksByWriter(String writer, String collation);

9. Complete Example

Find the project structure in Eclipse.

pom.xml

<parent>
	<groupId>org.springframework.boot</groupId>
	<artifactId>spring-boot-starter-parent</artifactId>
	<version>2.1.7.RELEASE</version>
	<relativePath />
</parent>
<dependencies>
	<dependency>
		<groupId>org.springframework.boot</groupId>
		<artifactId>spring-boot-starter-data-mongodb</artifactId>
	</dependency>
</dependencies>

MongoDBConfig.java

package com.concretepage.config;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.data.mongodb.config.AbstractMongoConfiguration;
import org.springframework.data.mongodb.repository.config.EnableMongoRepositories;
import com.mongodb.MongoClient;
import com.mongodb.MongoClientOptions;
import com.mongodb.MongoCredential;
import com.mongodb.ServerAddress;

@Configuration
@EnableMongoRepositories(basePackages = "com.concretepage.repository")
public class MongoDBConfig extends AbstractMongoConfiguration {
	@Override
	public String getDatabaseName() {
		return "myMongoDB";
	}
	@Override
	@Bean
	public MongoClient mongoClient() {
		ServerAddress address = new ServerAddress("127.0.0.1", 27017);
		MongoCredential credential = MongoCredential.createCredential("mdbUser", getDatabaseName(), "cp".toCharArray());
		MongoClientOptions options = new MongoClientOptions.Builder().build();
        
		MongoClient client = new MongoClient(address, credential, options);
		return client;
	}
}

Book.java

package com.concretepage.entity;
import org.springframework.data.annotation.Id;
public class Book { 
	@Id
	private Integer id;
	private String title;
	private Integer noOfPages;
	private String writer;
	private String category;	
	public Book(Integer id, String title, Integer noOfPages, String writer, String category){
		this.id = id;
		this.title=title;
		this.noOfPages=noOfPages;
		this.writer = writer;
		this.category = category;
	}
        //Setters and Getters
}

Populate data for test
PrepareDataForQuery.java

package com.concretepage;
import java.util.ArrayList;
import java.util.List;
import org.springframework.context.annotation.AnnotationConfigApplicationContext;
import com.concretepage.config.MongoDBConfig;
import com.concretepage.entity.Book;
import com.concretepage.repository.BookRepository;

public class PrepareDataForQuery {
	public static void main(String[] args) {
		AnnotationConfigApplicationContext ctx = new AnnotationConfigApplicationContext();
		ctx.register(MongoDBConfig.class);
		ctx.refresh();
		BookRepository repository = ctx.getBean(BookRepository.class);

		repository.deleteAll();

		Book b1 = new Book(101, "Angular Tutorials", 200, "Krishna", "Frontend");
		Book b2 = new Book(102, "JavaScript Tutorials", 200, "Krishna", "Frontend");		
		Book b3 = new Book(103, "Spring Tutorials", 300, "Mahesh", "Backend");
		Book b4 = new Book(104, "Java Tutorials", 250, "Krishna", "Backend");
		Book b5 = new Book(105, "Hibernate Tutorials", 150, "Mahesh", "Backend");
		List<Book> list = new ArrayList<>();
		
		list.add(b1);
		list.add(b2);
		list.add(b3);
		list.add(b4);
		list.add(b5);		
		
		List<Book> obj = repository.saveAll(list);
		
		ctx.registerShutdownHook();
		ctx.close();
	}
}

We will get following data in our MongoDB repository.

{ "_id" : 101, "title" : "Angular Tutorials", "noOfPages" : 200, "writer" : "Krishna", "category" : "Frontend", "_class" : "com.concretepage.entity.Book" }
{ "_id" : 102, "title" : "JavaScript Tutorials", "noOfPages" : 200, "writer" : "Krishna", "category" : "Frontend", "_class" : "com.concretepage.entity.Book" }
{ "_id" : 103, "title" : "Spring Tutorials", "noOfPages" : 300, "writer" : "Mahesh", "category" : "Backend", "_class" : "com.concretepage.entity.Book" }
{ "_id" : 104, "title" : "Java Tutorials", "noOfPages" : 250, "writer" : "Krishna", "category" : "Backend", "_class" : "com.concretepage.entity.Book" }
{ "_id" : 105, "title" : "Hibernate Tutorials", "noOfPages" : 150, "writer" : "Mahesh", "category" : "Backend", "_class" : "com.concretepage.entity.Book" }

Now find the repository class that is using @Query annotation.
BookRepository.java

package com.concretepage.repository;
import java.util.stream.Stream;
import org.springframework.data.mongodb.repository.MongoRepository;
import org.springframework.data.mongodb.repository.Query;
import org.springframework.stereotype.Component;
import com.concretepage.entity.Book;

@Component
public interface BookRepository extends MongoRepository<Book, Integer> {
	@Query("{id : ?0}")
	Book findBookById(int id);
	
	@Query("{writer : ?0, category : ?1}")
	Stream<Book> findBooksByWriterAndCategory(String writer, String category);
	
	@Query("{noOfPages : {$gt: ?0}}")
	Stream<Book> findBooksGtThanNoOfPages(int noOfPages);
	
	@Query("{writer : ?0, noOfPages : {$lt: ?1}}")
	Stream<Book> findBooksByWriterAndLtThanNoOfPages(String writer, int noOfPages);	
	
	@Query("{$or : [{writer: ?0}, {category : ?1}]}")
	Stream<Book> findBooksByWriterOrCategory(String writer, String category);
	
	@Query("{$and : [{$or : [{noOfPages: {$gt: 275}}, {noOfPages : {$lt: 200}}]}, {$or : [{id: {$gt: 103}}, {id : {$lt: 102}}]}]}")
	Stream<Book> findBestBooks();	
	
	@Query(value = "{writer : ?0, category : ?1}", fields = "{ 'title' : 1, 'noOfPages' : 1, 'writer' : 1}")
	Stream<Book> findBooksWithCertainFields(String writer, String category);
	
	@Query(value = "{category : ?0}", count = true)
	Integer findBookCountByCategory(String category);
	
	@Query(value = "{writer : ?0}", exists = true)
	Boolean isBooksAvailableByWriter(String writer);	
	
	@Query(value = "{writer : ?0}", sort = "{title : 1}") //sorting order by title ascending 
	Stream<Book> findBooksByWriter(String writer);
	
	@Query(value = "{category : ?0}", sort = "{title : -1}") //sorting order by title descending
	Stream<Book> findBooksByCategory(String category);	
	
	@Query(value = "{category : ?0}", delete = true)
	Long deleteBooksByCategory(String category);		
}

QueryTest.java

package com.concretepage;
import java.util.stream.Stream;
import org.springframework.context.annotation.AnnotationConfigApplicationContext;
import com.concretepage.config.MongoDBConfig;
import com.concretepage.entity.Book;
import com.concretepage.repository.BookRepository;

public class QueryTest {
	public static void main(String[] args) {
		AnnotationConfigApplicationContext ctx = new AnnotationConfigApplicationContext();
		ctx.register(MongoDBConfig.class);
		ctx.refresh();
		BookRepository repository = ctx.getBean(BookRepository.class);
		
		System.out.println("--- findBookById() ---");
		Book myBook = repository.findBookById(102);
		System.out.println(myBook);
		
		System.out.println("--- findBooksByWriterAndCategory() ---");		

		try (Stream<Book> stream = repository.findBooksByWriterAndCategory("Krishna", "Frontend")) {
			  stream.forEach(book -> System.out.println(book));
		}	
		
		System.out.println("--- findBooksGtThanNoOfPages() ---");		
                int noOfPages = 200;
		try (Stream<Book> stream = repository.findBooksGtThanNoOfPages(noOfPages)) {
			  stream.forEach(book -> System.out.println(book));
		}	
		
		System.out.println("--- findBooksByWriterAndLtThanNoOfPages() ---");		
		try (Stream<Book> stream = repository.findBooksByWriterAndLtThanNoOfPages("Mahesh", 250)) {
			  stream.forEach(book -> System.out.println(book));
		}		
		
		System.out.println("--- findBooksByWriterOrCategory() : Ex-1---");		
		try (Stream<Book> stream = repository.findBooksByWriterOrCategory("Mahesh", "Graphics")) {
			  stream.forEach(book -> System.out.println(book));
		}	
		
		System.out.println("--- findBooksByWriterOrCategory() : Ex-2---");		
		try (Stream<Book> stream = repository.findBooksByWriterOrCategory("Shiva", "Backend")) {
			  stream.forEach(book -> System.out.println(book));
		}		
		
		System.out.println("--- findBestBooks() ---");		
		try (Stream<Book> stream = repository.findBestBooks()) {
			  stream.forEach(book -> System.out.println(book));
		}		
		
		System.out.println("--- findBooksWithCertainFields() ---");		
		try (Stream<Book> stream = repository.findBooksWithCertainFields("Mahesh", "Backend")) {
			  stream.forEach(book -> System.out.println(book));
		}
		
		System.out.println("--- findBookCountByCategory() ---");		
		Integer bookCount = repository.findBookCountByCategory("Frontend");	
		System.out.println("bookCount: " + bookCount);
		
		System.out.println("--- isBooksAvailableByWriter() ---");		
		Boolean isBooksAvailable = repository.isBooksAvailableByWriter("Krishna");	
		System.out.println("isBooksAvailable: " + isBooksAvailable);	
		
		System.out.println("--- findBooksByWriter() ---");		
		try (Stream<Book> stream = repository.findBooksByWriter("Mahesh")) {
			  stream.forEach(book -> System.out.println(book));
		}	
		
		System.out.println("--- findBooksByCategory() ---");		
		try (Stream<Book> stream = repository.findBooksByCategory("Backend")) {
			  stream.forEach(book -> System.out.println(book));
		}			
		
		System.out.println("--- deleteBooksByCategory() ---");		
		Long deletedCount = repository.deleteBooksByCategory("Frontend");
                System.out.println("deletedCount: " + deletedCount);
		
		ctx.registerShutdownHook();
		ctx.close();
	}
}

Output

--- findBookById() ---
102 - JavaScript Tutorials - 200 - Krishna - Frontend
--- findBooksByWriterAndCategory() ---
101 - Angular Tutorials - 200 - Krishna - Frontend
102 - JavaScript Tutorials - 200 - Krishna - Frontend
--- findBooksGtThanNoOfPages() ---
103 - Spring Tutorials - 300 - Mahesh - Backend
104 - Java Tutorials - 250 - Krishna - Backend
--- findBooksByWriterAndLtThanNoOfPages() ---
105 - Hibernate Tutorials - 150 - Mahesh - Backend
--- findBooksByWriterOrCategory() : Ex-1---
103 - Spring Tutorials - 300 - Mahesh - Backend
105 - Hibernate Tutorials - 150 - Mahesh - Backend
--- findBooksByWriterOrCategory() : Ex-2---
103 - Spring Tutorials - 300 - Mahesh - Backend
104 - Java Tutorials - 250 - Krishna - Backend
105 - Hibernate Tutorials - 150 - Mahesh - Backend
--- findBestBooks() ---
105 - Hibernate Tutorials - 150 - Mahesh - Backend
--- findBooksWithCertainFields() ---
103 - Spring Tutorials - 300 - Mahesh - null
105 - Hibernate Tutorials - 150 - Mahesh - null
--- findBookCountByCategory() ---
bookCount: 2
--- isBooksAvailableByWriter() ---
isBooksAvailable: true
--- findBooksByWriter() ---
105 - Hibernate Tutorials - 150 - Mahesh - Backend
103 - Spring Tutorials - 300 - Mahesh - Backend
--- findBooksByCategory() ---
103 - Spring Tutorials - 300 - Mahesh - Backend
104 - Java Tutorials - 250 - Krishna - Backend
105 - Hibernate Tutorials - 150 - Mahesh - Backend
--- deleteBooksByCategory() ---
deletedCount: 2

10. References

Spring Data MongoDB : @Query
Spring Data MongoDB Reference

11. Download Source Code

spring-data-mongodb-query.zip

POSTED BY

ARVIND RAI

Popular Tutorials: Java 8 | Spring 5 | Angular | Android