spring boot - $filter not working in JPA/Olingo 2.0.11 with MySQL

Question

Olingo2 version 2.0.11 has problem with spring-boot-starter-web version 2.0.0 or above!

I made an odata service with olingo2, jpa and spring-boot based on this GitHub repository.

I've set up the project to use MariaDB database and it works quiet good.

However, the project is a little bit old and I tried to upgrade it!

If you check its pom.xml in GitHub you will see the following details:

...
    <parent>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-parent</artifactId>
        <version>1.3.2.RELEASE</version>
    </parent>

    <properties>
        <java.version>1.7</java.version>
        <cxf.version>3.1.5</cxf.version>
        <olingo.version>2.0.6</olingo.version>
    </properties>
...

In the first step I tried to update the libraries versions like this:

    <parent>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-parent</artifactId>
        <version>2.3.0.RELEASE</version>
    </parent>

    <properties>
        <java.version>1.8</java.version>
        <cxf.version>3.3.6</cxf.version>
        <olingo.version>2.0.11</olingo.version>
    </properties>

As soon as I do this upgrade, I needed to update two imports in the CxfServletRegister.java and Application.java files as following:

/* In file: odata-boilerplate/src/main/java/com/penninkhof/odata/utils/CxfServletRegister.java
*/
// import org.springframework.boot.context.embedded.ServletRegistrationBean; <- old class replace with
import org.springframework.boot.web.servlet.ServletRegistrationBean; // <- new address

and

/* In file: odata-boilerplate/src/main/java/com/penninkhof/odata/Application.java
*/
//import org.springframework.boot.context.web.SpringBootServletInitializer; <-- old class replace with
import org.springframework.boot.web.servlet.support.SpringBootServletInitializer; // <- new address

By these simple changes I could run the app again with command mvn spring-boot:run -P jar.

However I understood in the latest version of spring boot which is version 2.3.0.RELEASE it will run a new instance of the server each time a request is arrived.

Until here also everything seems fine. However there is a small issue!

While I have set the dialect value in the odata-boilerplate/src/main/resources/application.properties file like this:

# WEB SERVER 
server.port=9090

# MARIADB DATA SOURCE
spring.datasource.url = jdbc:mariadb://localhost:3306/cimply_ask?useUnicode=yes&characterEncoding=UTF-8
spring.datasource.driver-class-name=org.mariadb.jdbc.Driver
spring.datasource.username = root
spring.datasource.password = 
spring.datasource.testWhileIdle = false
spring.datasource.validationQuery = SELECT 1

# JPA / HIBERNATE
spring.jpa.database-platform=org.hibernate.dialect.MariaDBDialect
spring.jpa.properties.hibernate.dialect = org.hibernate.dialect.MariaDBDialect
spring.jpa.show-sql = true
spring.jpa.hibernate.ddl-auto = update
spring.jpa.hibernate.naming-strategy = org.hibernate.cfg.ImprovedNamingStrategy
spring.jpa.hibernate.auto_quote_keyword=true

it is only set in a thread (or task) that is responsible for initiating the database initial values! Here is part of the console output. As it can be seen the logs are belonged to thread task-1 and in the line 6 it has set the correct dialect for the thread.

1- 22:22:29.075 [task-1] INFO  org.hibernate.jpa.internal.util.LogHelper - HHH000204: Processing PersistenceUnitInfo [name: default]
2- 22:22:29.177 [task-1] INFO  org.hibernate.Version - HHH000412: Hibernate ORM core version 5.4.15.Final
3- 22:22:29.459 [task-1] INFO  org.hibernate.annotations.common.Version - HCANN000001: Hibernate Commons Annotations {5.1.0.Final}
4- 22:22:29.715 [task-1] INFO  com.zaxxer.hikari.HikariDataSource - HikariPool-1 - Starting...
5- 22:22:29.817 [task-1] INFO  com.zaxxer.hikari.HikariDataSource - HikariPool-1 - Start completed.
6- 22:22:29.840 [task-1] INFO  org.hibernate.dialect.Dialect - HHH000400: Using dialect: org.hibernate.dialect.MariaDBDialect
7- 22:22:31.067 [task-1] INFO  o.h.e.t.jta.platform.internal.JtaPlatformInitiator - HHH000490: Using JtaPlatform implementation: [org.hibernate.engine.transaction.jta.platform.internal.NoJtaPlatform]
8- 22:22:31.317 [task-1] INFO  o.s.orm.jpa.LocalContainerEntityManagerFactoryBean - Initialized JPA EntityManagerFactory for persistence unit 'default'

But what would happen when a request is arrived?

For example when I try the following request that I use a filtering on a column of type string:

http://localhost:9090/odata.svc/Members?$format=json&$filter=FirstName eq 'Jack'

I receive an error message in response to my request in postman or browser, like this:

{
    "error": {
        "code": null,
        "message": {
            "lang": "en",
            "value": "org.hibernate.exception.SQLGrammarException: could not extract ResultSet"
        }
    }
}

It actually generates a wrong query on database as it does not set the correct dialect. Here is the console output in my application:

22:56:52.593 [http-nio-9090-exec-1] INFO  org.apache.cxf.endpoint.ServerImpl - Setting the server's publish address to be /
22:57:22.605 [http-nio-9090-exec-1] WARN  org.hibernate.engine.jdbc.spi.SqlExceptionHelper - SQL Error: 1064, SQLState: 42000
22:57:22.606 [http-nio-9090-exec-1] ERROR org.hibernate.engine.jdbc.spi.SqlExceptionHelper - You have an error in your SQL syntax; check the manual that corresponds
to your MariaDB server version for the right syntax to use near '''' at line 1

What is the problem here? It uses escape '' instead of escape '\' in the query as it does not set the correct dialect based on the database type!

What I need to do? I need to know how can I set the correct dialect for the instance that is responsible for responding a request in this app?

Please take these points also in to account:

1. By upgrading this app to use the latest version of spring-boot-starter it will make a new thread each time a new request is arrived. You can send the same request and you will see the thread name will change as follow: [http-nio-9090-exec-2], [http-nio-9090-exec-3], [http-nio-9090-exec-1], ... sometimes even the old threads are used!

2. Each time a new request will be arrived the following part of the code is the entry point for processing the request. Maybe this is the place that I must set the dialect for the thread!

/* File: odata-boilerplate/src/main/java/com/penninkhof/odata/utils/JPAServiceFactory.java
*/

import javax.persistence.EntityManagerFactory;

import org.apache.olingo.odata2.jpa.processor.api.ODataJPAContext;
import org.apache.olingo.odata2.jpa.processor.api.ODataJPAServiceFactory;
import org.apache.olingo.odata2.jpa.processor.api.exception.ODataJPARuntimeException;

public class JPAServiceFactory extends ODataJPAServiceFactory {
    public static final String DEFAULT_ENTITY_UNIT_NAME = "Model";
    public static final String ENTITY_MANAGER_FACTORY_ID = "entityManagerFactory";

    @Override
    public ODataJPAContext initializeODataJPAContext() throws ODataJPARuntimeException {
        ODataJPAContext oDataJPAContext = getODataJPAContext();

        EntityManagerFactory factory = (EntityManagerFactory) SpringContextsUtil.getBean(ENTITY_MANAGER_FACTORY_ID);

        oDataJPAContext.setEntityManagerFactory(factory);
        oDataJPAContext.setPersistenceUnitName(DEFAULT_ENTITY_UNIT_NAME);
        oDataJPAContext.setJPAEdmExtension(new JPAEdmExtension());
        ODataContextUtil.setODataContext(oDataJPAContext.getODataContext());

        return oDataJPAContext;
    }
}

This question is a little bit long but I tried to explained all my investigations and work around. I need to know how can I set a default dialect that all threads in the application use that! As I mentioned earlier I tried to do it in the application.properties file but it seems it will be ignored in the time of request processing!

Answer 1

Does not solve the original issue and is not the most efficient way.. but here is a workaround for how to remove the incorrect 'escape' statement with only one slash:

public class SqlStatementInspector implements StatementInspector {

    private static final long serialVersionUID = 1L;
    private static final Logger LOG = Logger.getLogger(SqlStatementInspector.class);

    @Override
    public String inspect(String sql) {
        if (!sql.contains("escape '\'")) {
            return sql;
        }
        // OData JPA query correction -> current version (2.0.11) contains
        // the invalid 'escape ""' statement that delivers no results
        LOG.info("Replacing invalid statement: escape """);
        return sql.replace("escape '\'", "");
    }
}

This overwrites the inspect method and you can modify the generated sql query when using hibernate

in my persistence.xml file i then need to set the property "hibernate.session_factory.statement_inspector" to connect my StatementInspector implementation with my hibernate session factory

<property
                name="hibernate.session_factory.statement_inspector"
                value="SqlStatementInspector" />

i don't know how exactly this would work with spring-boot but maybe there is a similiar property for your application.properties?