1

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());
        }
    }
2
  • 2
    There's a bug in ParameterBindingJsonReader.visitDateTimeExtendedJson. I opened DATAMONGO-2315 to tackle the issue. Commented Jul 4, 2019 at 8: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. Commented Jul 5, 2019 at 14:07

1 Answer 1

4

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);
    }
Sign up to request clarification or add additional context in comments.

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.