Custom Query Methods in MongoDB – Spring Boot

Custom Query Methods in MongoDB – Spring Boot | @Query: This annotation provides the custom query for MongoDB using abstract methods defined in the Repository interface.

Create a spring starter project with dependencies:- Lombok, Spring Data MongoDB.

In application.properties:-

spring.data.mongodb.host=localhost
spring.data.mongodb.port=27017
# if given database not exist then auto-created
spring.data.mongodb.database=kp

@Data
@AllArgsConstructor
@Document
public class Book {
    @Id
    private Integer id;
    private String title;
    private Integer noOfPages;
    private String writer;
    private String category;
}

public interface BookRepository extends MongoRepository<Book, Integer> {
    // custom query methods will be placed here
}

@Component
@Order(1)
public class DataInsertRunner implements CommandLineRunner {

    @Autowired
    private BookRepository bookRepository;

    @Override
    public void run(String... args) throws Exception {
        bookRepository.deleteAll();
        bookRepository.saveAll(
            List.of(
                new Book(10, "Core Java", 250, "SAM", "BackEnd"),
                new Book(11, "Adv Java", 260, "SYED", "BackEnd"), 
                new Book(12, "Angular", 350, "SAM", "FrontEnd"),
                new Book(13, "HTML", 120, "SYED", "FrontEnd"), 
                new Book(14, "Spring Boot", 850, "SYED", "BackEnd"),
                new Book(15, "Microservices", 350, "SAM", "BackEnd"), 
                new Book(16, "ReactJS", 180, "RAM", "FrontEnd")
            )
        );
    }

}

When we are fetching one JSON doc data it may return null also, then use a wrapper Optional to avoid null pointer exception.

// bad Practice (but works)
@Query("{ id : ?0 }")
Book getBookById(Integer id);

// good Practice
@Query("{ id : ?0 }")
Optional<Book> getBookById(Integer id);

Here { } inside Query indicates where condition and ? indicates data comes at runtime.

Operators and Symbols

Symbol	Operator
<	$lt
<=	$lte
>	$gt
>=	$gte

@Query("{ noOfPages : { $gte : ?0 } }")
// List<Book> getBooksByNoOfPages(Integer noOfPages);
Stream<Book> getBooksByNoOfPages(Integer noOfPages);

repo.getBooksByNoOfPages(150)
    .filter(ob->ob.getCategory().equals("BackEnd"))
    .sorted((ob1,ob2)-> ob1.getId().compareTo(ob2.getId()))
    .map(ob->ob.getTitle()+" is written by :" +ob.getWriter())
    .forEach(System.out::println);

Writing AND/OR

$or: [ { condition#1 }, { condition#2 }, { condition#3 }, … ]
$and: [ { condition#1 }, { condition#2 }, { condition#3 }, … ]

SQL: where sid=? or sname=?
$or: [{sid:?0}, {sname:?1}]

SQL: where sfee=? and (sid=? or sname=?)
$and: [{sfee:?0}, $or: [{sid:?0}, {sname:?1}]]

SQL: where sid>? and sfee<=?
$and : [ { sid : { $gt: ?0 } }, { sfee: { $lte: ?1 } } ]

// SQL: select * from book where writer=? and category=?
// @Query("{ writer : ?0, category : ?1}")
// or
@Query("{ $and: [ {writer: ?0}, {category: ?1} ] }")
List<Book> getBooksByWriterAndCategory(String writer, String category);

// SQL: select * from book where writer=? or category=?
@Query("{ $or: [ {writer: ?0}, {category: ?1} ] }")
List<Book> getBooksByWriterOrCategory(String writer, String category);

Between using > and < Symbols

// SQL: select * from book where id>? and (writer=? or category=?)
@Query("{ $and: [ {id: {$gt: ?0}}, {$or: [ {writer: ?1}, {category: ?2} ]}] }")
List<Book> getBooksByDataA(Integer id, String writer, String category);

{
  $and: [
    {
      id: {
        $gt: ?0
      }
    },
    {
      $or: [
        {
          writer: ?1
        },
        {
          category: ?2
        }
      ]
    }
  ]
}

import org.springframework.data.mongodb.repository.MongoRepository;
import org.springframework.data.mongodb.repository.Query;

public interface BookRepository extends MongoRepository<Book, Integer> {

    // custom query methods
    // SQL: select * from book where id=?
    @Query("{ id: ?0 }")
    Optional<Book> getBooksById(Integer id);

    // SQL: select * from book where writer=? and category=?
    // @Query("{ writer : ?0, category : ?1}")
    // or
    @Query("{ $and: [ {writer: ?0}, {category: ?1} ] }")
    List<Book> getBooksByWriterAndCategory(String writer, String category);

    // SQL: select * from book where noOfPages>?
    @Query("{noOfPages : { $gt: ?0 } }")
    // we can use List<Book> or Stream<Book> in return type
    Stream<Book> getBooksByNoOfPages(Integer noOfPages);

    // SQL: select * from book where writer=? and noOfPages<?
    @Query("{ writer : ?0,  noOfPages : { $lt: ?1 } }")
    List<Book> getBookdByWriterAndNoOfPages(String writer, Integer noOfPages);

    // SQL: select * from book where writer=? or category=?
    @Query("{ $or: [ {writer: ?0}, {category: ?1} ] }")
    List<Book> getBooksByWriterOrCategory(String writer, String category);

    // SQL: select * from book where id>? and (writer=? or category=?)
    @Query("{ $and: [ {id: {$gt: ?0}}, {$or: [ {writer: ?1}, {category: ?2} ]}] }")
    List<Book> getBooksByDataA(Integer id, String writer, String category);
}

@Component
@Order(2)
class TestQueryRunner implements CommandLineRunner {

    @Autowired
    private BookRepository repo;

    @Override
    public void run(String... args) throws Exception {

        Optional<Book> book = repo.getBooksById(15);

        if (book.isPresent()) {
            System.out.println(book);
        } else {
            System.out.println("No Data Found");
        }

        repo.getBooksByWriterAndCategory("SAM", "BackEnd")
            .forEach(System.out::println);
        
        repo.getBooksByNoOfPages(200)
            .forEach(System.out::println);
        
        repo.getBooksByNoOfPages(200)
            .filter(obj -> obj.getCategory().equals("BackEnd"))
            .sorted((obj1, obj2) -> obj1.getId().compareTo(obj2.getId()))
            .map(obj -> obj.getTitle() + " is writtern by " + obj.getWriter())
            .forEach(System.out::println);

        repo.getBookdByWriterAndNoOfPages("SAM", 500)
            .forEach(System.out::println);
        
        repo.getBooksByWriterOrCategory("SAM", "BackEnd")
            .forEach(System.out::println);
        
        repo.getBooksByDataA(12, "SAM", "BackEnd")
            .forEach(System.out::println);

    }

}

If you enjoyed this post, share it with your friends. Do you want to share more information about the topic discussed above or do you find anything incorrect? Let us know in the comments. Thank you!

Search