Perform Native Database Queries

Sample Data

The examples in this guide use the Movie entity, which represents the sample_mflix.movies collection from the Atlas sample datasets. The Movie entity has the following definition:

import com.mongodb.hibernate.annotations.ObjectIdGenerator;
import org.bson.types.ObjectId;
import java.util.List;
import jakarta.persistence.Entity;
import jakarta.persistence.Id;
import jakarta.persistence.Table;
@Entity
@Table(name = "movies")
public class Movie {
	@Id
	@ObjectIdGenerator
	private ObjectId id;
	private String title;
	private String plot;
	private int year;
	private int runtime;
	private List<String> cast;
	public Movie(String title, String plot, int year, int runtime, List<String> cast) {
 		this.title = title;
		this.plot = plot;
		this.year = year;
		this.runtime = runtime;
		this.cast = cast;
 	}
	public Movie() {
	}
	public ObjectId getId() {
		return id;
	}
	public String getTitle() {
		return title;
	}
	public void setTitle(String title) {
		this.title = title;
	}
	public String getPlot() {
		return plot;
	}
	public void setPlot(String plot) {
		this.plot = plot;
	}
	
	public int getYear() {
		return year;
	}
	public void setYear(int year) {
		this.year = year;
	}
	public int getRuntime() {
		return runtime;
	}
	public void setRuntime(int runtime) {
		this.runtime = runtime;
	}
	public List<String> getCast() {
		return cast;
	}
	
	public void setCast(List<String> cast) {
		this.cast = cast;
	}
}

To learn how to create a Java application that uses the MongoDB Extension for Hibernate ORM to interact with this MongoDB sample collection, see the Get Started tutorial.

Filter and Sort Document Fields

This example runs a native query on the sample_mflix.movies collection by passing an MQL statement to the createNativeQuery() method. The code specifies the following aggregation pipeline stages:

• $match: Filters for documents that have a title field value of "The Parent Trap"

• $sort: Sorts the matching documents by their year fields in descending order

• $project: Returns each document field defined in the Movie entity

String nativeQuery = """
{
    aggregate: "movies",
    pipeline: [
        { $match: { title: { $eq: "The Parent Trap" } } },
        { $sort: { year: -1 } },
        { $project: { title: 1, plot: 1, year: 1, runtime: 1, cast: 1 } }
    ]
}
""";
var results = session.createNativeQuery(nativeQuery, Movie.class)
        .getResultList();
for (Movie movie : results) {
    System.out.println("Title: " + movie.getTitle() + ", Year: " + movie.getYear());
}

Title: The Parent Trap, Year: 1998
Title: The Parent Trap, Year: 1961

Use Arithmetic Operators

The Hibernate ORM extension does not currently support HQL or JPQL statements that use arithmetic operators. However, you can perform arithmetic operations on your data by using MongoDB's arithmetic operators in an MQL statement.

The following example runs a native query on the sample_mflix.movies collection that performs the following actions:

• Specifies a $match stage to match documents that have a year value greater than 2000 and a runtime field that exists

• Specifies an $addFields stage to add a new field called runtimeHours

• Uses the $divide arithmetic operator to convert the runtime value from minutes to hours for the new runtimeHours field

• Specifies a $project stage to return each document field defined in the Movie entity, other than the primary key field

• Prints the title value of each updated document

String nativeQuery = """
{
    aggregate: "movies",
    pipeline: [
        { $match: { year: { $gt: 2000 }, runtime: { $exists: true } } },
        { $addFields: {
            runtimeHours: { $divide: [ "$runtime", 60 ] }
        }},
        { $project: {
            title: 1,
            plot: 1,
            year: 1,
            runtime: 1,
            cast: 1,
            runtimeHours: 1
        }}
    ]
}
""";
var results = session.createNativeQuery(nativeQuery, Movie.class)
        .getResultList();
for (Movie result : results) {
    System.out.println("Added field to movie: " + result.getTitle());
}

Added field to movie: Kate & Leopold
Added field to movie: Crime and Punishment
Added field to movie: Glitter
Added field to movie: The Manson Family
Added field to movie: The Dancer Upstairs
Added field to movie: Fantastic Four
...

Run a MongoDB Search Query

You can run native queries to perform MongoDB Search queries on your database, which are fine-grained text searches on your data. These queries provide advanced search functionality, such as matching text phrases, scoring results for relevance, and highlighting matches.

To specify a Search query, create a Search index that covers the fields you want to query. Then, pass an aggregation pipeline to your createNativeQuery() method that includes a $search or $searchMeta stage.

This example runs a Search query by passing the $search pipeline stage to the createNativeQuery() method. The code performs the following actions:

• Specifies the Search index that covers the plot field. Ensure that you replace the <indexName> placeholder with your Search index name.

• Queries for documents whose plot values contain the string "whirlwind romance" with no more than 3 words between them

• Specifies a $project stage to return each document field defined in the Movie entity, other than the primary key field

• Prints the title and plot values of the matching documents

String nativeQuery = """
{
    aggregate: "movies",
    pipeline: [
        {
            $search: {
                index: "<indexName>",
                phrase: {
                    path: "plot",
                    query: "whirlwind romance",
                    slop: 3
                }
            }
        },
        {
            $project: {
                title: 1,
                plot: 1,
                year: 1,
                runtime: 1,
                cast: 1
            }
        }
    ]
}
""";
var results = session.createNativeQuery(nativeQuery, Movie.class)
        .getResultList();
for (Movie result : results) {
    System.out.println("Title: " + result.getTitle() + ", Plot: " + result.getPlot());
}

Title: Tokyo Fiancèe, Plot: A young Japanophile Belgian woman in Tokyo falls into a whirlwind romance with a Francophile Japanese student.
Title: Designing Woman, Plot: A sportswriter and a fashion-designer marry after a whirlwind romance, and discover they have little in common.
Title: Vivacious Lady, Plot: On a quick trip to the city, young university professor Peter Morgan falls in love with nightclub performer Francey Brent and marries her after a whirlwind romance. But when he goes back ...
Title: Ek Hasina Thi, Plot: A woman falls for a charming and mysterious businessman. The whirlwind romance turns sour when she is framed for his underworld crimes. Now, finally out of prison she is ready for sweet revenge.
Title: Kick, Plot: An adrenaline junkie walks away from a whirlwind romance and embraces a new life as a thief, though he soon finds himself pursued by veteran police officer and engaged in a turf war with a local gangster.
Title: A Tale of Winter, Plot: Felicie and Charles have a serious if whirlwind holiday romance. Due to a mix-up on addresses they lose contact, and five years later at Christmas-time Felicie is living with her mother in ...

Create Indexes with the MongoClient

You cannot use the Hibernate ORM extension to create indexes on a collection, but you can instantiate a MongoClient and use the Java driver's createIndex() method. The following code creates a title field index on the sample_mflix.movies collection:

// Replace the <connection URI> placeholder with your MongoDB connection URI
String uri = "<connection URI>";
MongoClient mongoClient = MongoClients.create(uri);
MongoDatabase db = mongoClient.getDatabase("sample_mflix");
MongoCollection<Document> collection = db.getCollection("movies");
String indexResult = collection.createIndex(Indexes.ascending("title"));
System.out.println(String.format("Index created: %s", indexResult));

Index created: title_1

To learn more about how to use the Java driver to create indexes, see the Indexes guide in the Java driver documentation.