0

I'm having some trouble making MongoDB Date queries using @Query annotation on SpringDataMongoDB on a project created using JHipster.

Since JHipster was used to create the project most of the queries were created using Spring Data query builder mechanism and for more refined queries, instead of using Type-safe Query methods I decided to stick with JHipster's standard configuration and make personalized queries using @Query annotation that allows the creation of MongoDBJSON queries.

However, I can't reference in my Json queries any entity field of type Date or LocalDate.

I tried to adopt as a solution the answer from this thread without success.

Query attempts

@Repository
public interface CourseClassRepository extends MongoRepository<CourseClass, String> {

    // WORKS - query with `endDate` directly constructed by Spring Data
    // This solution however isn't enough, since 'experience_enrollments.device_id' cannot be used as a parameter
    List<CourseClass> findAllByInstitutionIdAndEndDateIsGreaterThanEqual(Long institutionId, LocalDate dateLimit);

    // Using @Query to create a JSON query doesn't work.
    // apparently data parameter cannot be found. This is weird, considering that in any other @Query created the parameter is found just fine.
    // ERROR: org.bson.json.JsonParseException: Invalid JSON input. Position: 124. Character: '?'
    @Query(" { 'experience_enrollments.device_id' : ?0, 'institution_id': ?1, 'end_date': { $gte: { $date: ?2 } } } ")
    List<CourseClass> findAllByExperienceDeviceAndInstitutionIdAndEndDate(String deviceId, Long institutionId, Date dateLimit);

    // Adopting the stackoverflow answer mentioned above also throws an error. I belive that this error is related to the fact that '?2' is being interpreted as a String value and not as reference to a parameter
    // ERROR: org.bson.json.JsonParseException: Failed to parse string as a date
    @Query(" { 'experience_enrollments.device_id' : ?0, 'institution_id': ?1, 'end_date': { $gte: { $date: '?2' } } } ")
    List<CourseClass> findAllByExperienceDeviceAndInstitutionIdAndEndDate(String deviceId, Long institutionId, Date dateLimit);

    // Even hardcoding the date parameter, the query throws an error
    // ERROR: org.bson.codecs.configuration.CodecConfigurationException: Can't find a codec for class java.time.ZonedDateTime.
    @Query(" { 'experience_enrollments.device_id' : ?0, 'institution_id': ?1, 'end_date': { '$gte': { '$date': '2015-05-16T07:55:23.257Z' } } }")
    List<CourseClass> findAllByExperienceDeviceAndInstitutionIdAndEndDate(String deviceId, Long institutionId);
}

Database Configurations

@Configuration
@EnableMongoRepositories("br.com.pixinside.lms.course.repository")
@Profile("!" + JHipsterConstants.SPRING_PROFILE_CLOUD)
@Import(value = MongoAutoConfiguration.class)
@EnableMongoAuditing(auditorAwareRef = "springSecurityAuditorAware")
public class DatabaseConfiguration {
     @Bean
        public MongoCustomConversions customConversions() {
            List<Converter<?, ?>> converters = new ArrayList<>();
            converters.add(DateToZonedDateTimeConverter.INSTANCE);
            converters.add(ZonedDateTimeToDateConverter.INSTANCE);
            return new MongoCustomConversions(converters);
        }
}

Date converters

    public static class DateToZonedDateTimeConverter implements Converter<Date, ZonedDateTime> {

        public static final DateToZonedDateTimeConverter INSTANCE = new DateToZonedDateTimeConverter();

        private DateToZonedDateTimeConverter() {
        }

        @Override
        public ZonedDateTime convert(Date source) {
            return source == null ? null : ZonedDateTime.ofInstant(source.toInstant(), ZoneId.systemDefault());
        }
    }

    public static class ZonedDateTimeToDateConverter implements Converter<ZonedDateTime, Date> {

        public static final ZonedDateTimeToDateConverter INSTANCE = new ZonedDateTimeToDateConverter();

        private ZonedDateTimeToDateConverter() {
        }

        @Override
        public Date convert(ZonedDateTime source) {
            return source == null ? null : Date.from(source.toInstant());
        }
    }
marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Tin Megali
  • 771
  • 5
  • 25
  • 2
    There's a bug in `ParameterBindingJsonReader.visitDateTimeExtendedJson`. I opened [DATAMONGO-2315](https://jira.spring.io/browse/DATAMONGO-2315) to tackle the issue. – Christoph Strobl Jul 04 '19 at 08:57
  • Thank you Christoph! After a time I decided to go with another solution since it wasn't possible to make the query using MongoDBJson. I'll post my answer later and reference the bug for future reference. Again, thank you for your time. – Tin Megali Jul 05 '19 at 14:07

1 Answers1

3

Turns out that, as mentioned by Christoph Strobl, the behavior was, in fact, a bug. So it won't be necessary to worry about that in a future version of Spring Data MongoDB. Until there, I'm sharing my solution.

Since I was unable to use MongoDBJSon to create the query, I used the MongoTemplate and everything was just fine.

import org.springframework.data.mongodb.core.MongoTemplate;
import static org.springframework.data.mongodb.core.query.Criteria.where;
import static org.springframework.data.mongodb.core.query.Query.query;   

    @Autowired
    public MongoTemplate mongoTemplate;

    public List<CourseClass> findEnrolledOnExperienceDeviceWithMaxEndDateAndInstitutionId(String deviceId, LocalDate endDate, Long institutionId) {
        return mongoTemplate.find(query(
            where("experience_enrollments.device_id").is(deviceId)
                .and("institution_id").is(institutionId)
                .and("end_date").gte(endDate)), CourseClass.class);
    }
Tin Megali
  • 771
  • 5
  • 25