Hibernate search querying in MYSQL DB

Query is running on hibernate search and its giving proper response but while constructing response in spring boot application , its querying in the mysql DB as well, how to avoid this ? one thing i understood is using projections but any simple way to avoid this.

If you don’t want the data of matching documents to be extracted from the database, then indeed, use projections:

SearchSession searchSession = Search.session( entityManager );

List<String> result = searchSession.search( Book.class ) 
        .select( f -> f.field( "title", String.class ) ) 
        .where( f -> f.matchAll() )
        .fetchHits( 20 );

This won’t hit the database at all.

In Hibernate Search 6.2, there’s a more convenient, annotation-based projection definition:

@ProjectionConstructor 
public record MyBookProjection(String title, 
        List<MyBookProjection.Author> authors) { 
    @ProjectionConstructor 
    public record Author(String firstName, String lastName) {
    }
}

SearchSession searchSession = Search.session( entityManager );

List<MyBookProjection> hits = searchSession.search( Book.class )
        .select( MyBookProjection.class )
        .where( f -> f.matchAll() )
        .fetchHits( 20 ); 

It is, however, still in Alpha and currently has limitations, such as being limited to indexed fields and not supporting ID/score projections.

In this case any ideal way to add fields into select dynamically, For example fields to be sent as response should be dynamic instead of manually adding like this –

Search.session(entityManager).search(ProductOrderEntity.class)
.extension(ElasticsearchExtension.get())
.select(f → f.composite(f.field(“directorName”, String.class),
f.field(“movies.title”, String.class).multi()))
.where(f → f.fromJson(json)).fetch(offset, limit);

If you mean “dynamically based on request parameters”, that’ll probably be easier using the object-based syntax instead of the lambda-based syntax:

SearchSession searchSession = Search.session( entityManager );

SearchScope<Book> scope = searchSession.scope( Book.class );

SearchProjectionFactory<?, ?> f = scope.projection();
SearchProjection<?> projection;
if ( some condition ) {
     projection = f.field( "directorName", String.class ).toProjection();
}
else {
    projection = f.composite(
                f.field("directorName"),
                f.field("movies.title").multi()
             ).toProjection();
}

List<?> result = searchSession.search( scope )
        .select( projection )
        .where( scope.predicate().matchAll().toPredicate() )
        .fetchHits( 20 );

If you mean “dynamically for each document”, then no, that’s not possible. You’ll have to do that using post-processing.