Problem in upgrading the project

[mjza]

I tried to upgrade the project to use the latest version of the libraries.

However I experiences some issues with spring-boot-starter-parent version 1.5 or above!

Please check here for the full explanation of the issue.

https://stackoverflow.com/questions/62122778/set-databes-dialect-in-spring-boot-starter-web-version-2-3-0-for-hibernate

Any help is appreciated.

[mjza]

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!

[mjza]

The latest versions of the libraries that can be used without any changes on the code are:

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

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

[mjza]

I found a solution for this issue, However it is not the best solution. Add the following class to com.penninkhof.odata.utils package:

// File: SqlStatementInspector.java
package com.penninkhof.odata.utils;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.hibernate.resource.jdbc.spi.StatementInspector;

public class SqlStatementInspector implements StatementInspector {

    private static final long serialVersionUID = 1L;
    private static final Logger LOG = LoggerFactory.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 \'\\'", "escape \'\\\\'");
    }
}

And then inside the application.properties file add this line at the end of the file:

spring.jpa.properties.hibernate.session_factory.statement_inspector = com.penninkhof.odata.utils.SqlStatementInspector

[mjza]

It seems this is a bug in olingo version 2.0.11.

The reasons are as following.

First I could test the dialect of the thread by changing the code in JPAServiceFactory.java file:

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;
import org.hibernate.SessionFactory;
import org.hibernate.dialect.Dialect;
import org.hibernate.internal.SessionFactoryImpl;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

public class JPAServiceFactory extends ODataJPAServiceFactory {
    private static final Logger log = LoggerFactory.getLogger(JPAServiceFactory.class);
    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);
        SessionFactoryImpl sessionFactory = (SessionFactoryImpl) factory.unwrap(SessionFactory.class);
        Dialect dialect = sessionFactory.getJdbcServices().getDialect();
        log.info(dialect.toString()); //<-- Here it will print the dialect name
        oDataJPAContext.setEntityManagerFactory(factory);
        oDataJPAContext.setPersistenceUnitName(DEFAULT_ENTITY_UNIT_NAME);
        oDataJPAContext.setJPAEdmExtension(new JPAEdmExtension());

        ODataContextUtil.setODataContext(oDataJPAContext.getODataContext());

        return oDataJPAContext;
    }
}

And as I saw the dialect is set correctly, something like this:

11:13:30.256 [http-nio-9090-exec-1] INFO  me.cimply.ask.odata.utils.JPAServiceFactory - org.hibernate.dialect.MariaDB103Dialect

2. The second reason is if I send a request that have the both $filter and $expand at the same time, then it will not inject escape '\' in the query anymore.

Therefore at the moment the only solution for solving this issue is to modify the query.

[mjza]

I will close this ticket. If you are looking for the solution check here.