2

While trying to apply a QueryDSL implementation for search filtering, I have encountered the following issue, which is quite confusing to say the least.

Consider the following properties in a MongoDB collection called Payments:

Object 1 - amountDue (Object) - amount => 106.00 (String)

Object 2 - amountDue (Object) - amount => 58.80 (String)

These values are generated by the system, and the actual data type of the amountDue object is an org.joda.BigMoney object.

These properties are applied in a binding method which is used to provide a QueryDSL predicate such that any Payment objects which have an amountDue.amount property greater than that specified in a search query is returned. Such method is described below:

@Override
public Predicate bind(NumberPath<BigDecimal> bigDecimalNumberPath, Collection<? extends BigDecimal> value) {
        ArrayList<? extends BigDecimal> amounts = new ArrayList<>(value);

        return bigDecimalNumberPath.gt(amounts.get(0));
    }

The following describes the cases which I am testing with, among others, with the respective results:

{URL}/payments/filter?page=0&amountDue.amount=10.00, which internally is converted to a 'amountDue.amount > 10.00' predicate returns both Objects [Correct]

{URL}/payments/filter?page=0&amountDue.amount=20.00, which internally is converted to a 'amountDue.amount > 20.00' predicate returns Only Object 2 [Incorrect]

{URL}/payments/filter?page=0&amountDue.amount=60.00, which internally is converted to a 'amountDue.amount > 60.00' predicate returns no Objects [Incorrect]

{URL}/payments/filter?page=0&amountDue.amount=100.00, which internally is converted to a 'amountDue.amount > 100.00' predicate returns Only Object 2 [Incorrect]

{URL}/payments/filter?page=0&amountDue.amount=150.00, which internally is converted to a 'amountDue.amount > 150.00' predicate returns Only Object 2 [Incorrect]

The moment the amount value of Object 1 is changed to a value less than 100, then all cases return correct results.

What are your suggestions/recommendations please?

Thanks for your time!!

William
  • 55
  • 1
  • 8
  • So you mean something like `'99'` returns "greater than" in all cases? Seems to me that as a "lexical string" `'99'` **is greater than** `'100'`, and that would seem to indicate the `BigDecimal` conversion does not work and it's still being treated as a "string". In truth you likely really mean `Double` here anyway, unless you actually have a MongoDB and Spring-mongo which both support `Decimal128` in the database and driver respectively. And then you need to explicitly cast as `Decimal128`. The default is of course as a "string". – Neil Lunn May 12 '18 at 13:42
  • See: [Spring Data MongoDB BigDecimal support](https://stackoverflow.com/q/37950296/2313887) which covers the "default" mapping as "string". You need to cast the value. There is a `Decimal128()` constructor available with the MongoDB driver in supported versions, or as stated use `Double` instead. – Neil Lunn May 12 '18 at 13:44
  • Hey @NeilLunn many thanks for the lead! Issue has been solved. Code implementation below. – William May 12 '18 at 14:14
  • I faced this issue with mysql also. Any suggestion to eliminate that. Where can I config suggested class implementations? – Sachin Mar 24 '22 at 10:28
  • No luck with 'DECIMAL' column definiton. @Column(name = "start_float", columnDefinition = "DECIMAL") var startFloat: BigDecimal, – Sachin Mar 24 '22 at 11:13

1 Answers1

2

The following has been applied to resolve the issue posted above:

First create a Decimal128 (Bson Type) To Big Decimal class converter:

public class Decimal128ToBigDecimalConverter implements Converter<Decimal128, BigDecimal> {

    @Override
    public BigDecimal convert(Decimal128 source) {
        return source.bigDecimalValue();
    }
}

Then create a Big Decimal to Decimal128 (Bson Type) class converter:

public class BigDecimalToDecimal128Converter implements Converter<BigDecimal, Decimal128> {

    @Override
    public Decimal128 convert(BigDecimal source) {
        return new Decimal128(source);
    }
}

Finally, configure your MongoConfig file to make use of the converters:

@Bean
public MongoTemplate mongoTemplate() throws Exception {

    MongoTemplate mongoTemplate = new MongoTemplate(mongo(), getDatabaseName());
    MappingMongoConverter mongoMapping = (MappingMongoConverter) mongoTemplate.getConverter();
    mongoMapping.setCustomConversions(customConversions()); 
    mongoMapping.afterPropertiesSet();
    return mongoTemplate;

}

public CustomConversions customConversions() {
    return new CustomConversions(Arrays.asList(new Decimal128ToBigDecimalConverter(), new BigDecimalToDecimal128Converter()));
}


/* (non-Javadoc)
 * @see org.springframework.data.mongodb.config.AbstractMongoConfiguration#mongo()
 */
@Bean
@Override
public Mongo mongo() throws Exception
{
    return new MongoClient();
}

The solution has been implemented by following the example listed here: http://ufasoli.blogspot.com.mt/2017/06/custom-converter-for-mongodb-and-spring.html

William
  • 55
  • 1
  • 8