The right expression is not a valid expression when using replace in mysema query

Question

The following works:

QTransaction.transaction.postingText.lower().like("%" + filter.getPostingText().toLowerCase() + "%"))

I am trying the similar query but replace x with u:

Expressions.stringTemplate("replace({0},'x','u')", QTransaction.transaction.postingText.lower()).like("%" + filter.getPostingText().toLowerCase() + "%"));

the log shows:

replace(lower(transaction.postingText),'x','u') like ?5 escape '!']. 
The right expression is not a valid expression.

What am I doing wrong?

    @Bean
    @DependsOn("jmsConnectionFactory")
    public LocalContainerEntityManagerFactoryBean entityManagerFactory() throws Exception {
        EclipseLinkJpaVendorAdapter vendorAdapter = new EclipseLinkJpaVendorAdapter();
        vendorAdapter.setGenerateDdl(false);
        vendorAdapter.setDatabasePlatform("org.eclipse.persistence.platform.database.PostgreSQLPlatform");
        vendorAdapter.setShowSql(false);
        LocalContainerEntityManagerFactoryBean factory = new LocalContainerEntityManagerFactoryBean();
        factory.setJpaVendorAdapter(vendorAdapter);
        List<String> packages = new ArrayList<>();
        packages.addAll(Arrays.asList(PACKAGES_TO_SCAN_DATA_ENTITIES));
        packages.addAll(additionalPackagesToScan);
        factory.setPackagesToScan(packages.toArray(new String[] {}));
        factory.setDataSource(dataSource());
        factory.setJpaDialect(new EclipseLinkJpaDialect());
        Properties props = jpaProperties();
        factory.setJpaProperties(props);
        return factory;
    }

A simplified example looks like this:

BooleanExpression boolExpr = Expressions.stringTemplate("replace({0},'x','u')", QTransaction.transaction.postingText).like("%" + filter.getPostingText() + "%");
transactionRepository.findAll(boolExpr);

with log showing:

An exception occurred while creating a query in EntityManager: 
Exception Description: Syntax error parsing [select transaction
from Transaction transaction
where replace(transaction.postingText,'x','u') like ?1 escape '!']. 
[54, 115] The expression is not a valid conditional expression.

What RDBMS are you using? Do you have the querydsl dialect set correctly? — Robert Bain, Sep 18 '20 at 19:05
Also can we see the use of the Expression in a minimal reproducible example please? I.E. a SELECT statement. — Robert Bain, Sep 18 '20 at 19:06
We use Spring/Eclipselink/Postgresql. I have added how the entitManagerFactory is created in the question, hence showing setJpaDialect(..) and setDatabasePlatform(..) — Alex K., Sep 21 '20 at 22:22
Have you tried binding in x and u using replace({0},{1},{2}), etc.? — Robert Bain, Sep 22 '20 at 10:26

The right expression is not a valid expression when using replace in mysema query

0 Answers0