0

I am using mysql with jpa specification query. I want to know how can i call the function that was using mysql keywords as parameters.

Here is the example:

select * from schema3.countries order by convert(name using GBK);

The convert method using the using and GBK keywords as paramters.

I want to call the convert function by the criteria query.

I tried the below but it does not working for me.

Expression expression = join.get(Country_.NAME);
                Expression orderExpression = builder.function(
                        "convert",
                        String.class,
                        expression,
                        builder.literal("USING GBK")
                );

and

Path path = join.get(Country_.NAME);

                String countryNameAlias = path.getAlias();
                Expression orderExpression = builder.function(
                        "convert",
                        String.class,
                        builder.literal(countryNameAlias + " USING GBK")
                );

The variable countryNameAlias is null so it`s not working.

Here is the error :

Hibernate: select expert0_.id as id1_14_, expert0_.code as code2_14_, expert0_.created_at as created_3_14_, expert0_.expert_information as expert_i4_14_, expert0_.meta_data_of_the_expert_information as meta_dat5_14_, expert0_.motherland as motherla8_14_, expert0_.number_of_applications as number_o6_14_, expert0_.updated_at as updated_7_14_, JSON_EXTRACT(expert0_.expert_information, '$.basicInformation.birthDate') as formula4_, case
           when
               JSON_EXTRACT(expert0_.expert_information, '$.basicInformation.gender') = 'MALE'
            then 0
else 1 end as formula5_, JSON_EXTRACT(expert0_.expert_information, '$.basicInformation.nameEN') as formula6_, convert(JSON_EXTRACT(expert0_.expert_information, '$.basicInformation.nameZH') using GBK) as formula7_ from expert expert0_ left outer join expert_application_record expertappl1_ on expert0_.id=expertappl1_.expert_id left outer join countries country2_ on expert0_.motherland=country2_.id where expertappl1_.latest=? order by convert(?) desc limit ?
2019-11-05 18:58:41.281 TRACE 15252 --- [nio-8080-exec-2] o.h.type.descriptor.sql.BasicBinder      : binding parameter [1] as [BOOLEAN] - [true]
2019-11-05 18:58:41.281 TRACE 15252 --- [nio-8080-exec-2] o.h.type.descriptor.sql.BasicBinder      : binding parameter [2] as [VARCHAR] - [null USING GBK]
2019-11-05 18:58:41.282  WARN 15252 --- [nio-8080-exec-2] o.h.engine.jdbc.spi.SqlExceptionHelper   : SQL Error: 1064, SQLState: 42000
2019-11-05 18:58:41.282 ERROR 15252 --- [nio-8080-exec-2] o.h.engine.jdbc.spi.SqlExceptionHelper   : You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ') desc limit 10' at line 5
2019-11-05 18:58:41.285 ERROR 15252 --- [nio-8080-exec-2] o.a.c.c.C.[.[.[/].[dispatcherServlet]    : Servlet.service() for servlet [dispatcherServlet] in context with path [] threw exception [Request processing failed; nested exception is org.springframework.dao.InvalidDataAccessResourceUsageException: could not extract ResultSet; SQL [n/a]; nested exception is org.hibernate.exception.SQLGrammarException: could not extract ResultSet] with root cause

java.sql.SQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ') desc limit 10' at line 5
    at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:118) ~[mysql-connector-java-8.0.11.jar:8.0.11]
    at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:95) ~[mysql-connector-java-8.0.11.jar:8.0.11]
    at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:122) ~[mysql-connector-java-8.0.11.jar:8.0.11]
    at com.mysql.cj.jdbc.ClientPreparedStatement.executeInternal(ClientPreparedStatement.java:960) ~[mysql-connector-java-8.0.11.jar:8.0.11]
    at com.mysql.cj.jdbc.ClientPreparedStatement.executeQuery(ClientPreparedStatement.java:1019) ~[mysql-connector-java-8.0.11.jar:8.0.11]
    at com.zaxxer.hikari.pool.ProxyPreparedStatement.executeQuery(ProxyPreparedStatement.java:52) ~[HikariCP-2.7.9.jar:na]

Thanks you all.

snieguu
  • 2,073
  • 2
  • 20
  • 39
zheng ma
  • 83
  • 2
  • 9

2 Answers2

2

The @formula anotation cannot work with native query. Here is the link: I am the link.

I am not familiar the jpa too much but here is the better solution than using the @Formula annotation.

First you need to create a custom Dialect Here is the example:

public class CustomMariaDB53Dialect extends MariaDB53Dialect {
    private static final Logger LOG = LoggerFactory.getLogger(CustomMariaDB53Dialect.class);

    public CustomMariaDB53Dialect() {
        super();
}

And then register a function to the Dialect Here is the code:

public class CustomMariaDB53Dialect extends MariaDB53Dialect {
    private static final Logger LOG = LoggerFactory.getLogger(CustomMariaDB53Dialect.class);

    public CustomMariaDB53Dialect() {
        super();
        registerFunction("convertEncode", new SQLFunctionTemplate(StandardBasicTypes.STRING, "convert(?1 using ?2)"));
    }
}

The first param of the registerFunction method is function name.

The second is a implement of SqlFunction

The ?1 and ?2 means the args of the function So the function template is convert(?1 using ?2)

After above stpes done.

You should tell the hibernate you are using a new Dialect

The path of the config is hibernate.dialect

Here is the example:

hibernate.dialect=xxx.xxx.CustomMariaDB53Dialect

If you are using spring boot jpa.

Here is the config:

spring:
  jpa:
    hibernate:
    properties:
      hibernate:
        dialect: xxx.xxx.CustomMariaDB53Dialect

The last step is to use the function in your query.

Here is the example:

Expression<String> countryName = builder().function(
                        "convertEncode", 
                        String.class,
                        join.get(Country_.NAME),
                        builder().literal("gbk")
                );

                return direction.isDescending() ? builder().desc(countryName ) : builder().asc(countryName );

Here is the final sql:

order by convert(expert0_.name_zh using ?) asc limit ?

Cheers!!!

OnTheRoad
  • 527
  • 6
  • 24
0

I changed the sql to below :

select *, convert(name using GBK) as nameGBK from schema3.countries order by nameGBK;

So the next question is how to add computed column to the selection. We can add a column to the country model. The field name is nameGBK and use the @Formula annotation; here is the code

    @Column(nullable = false, unique = true)
    private String name;

    @Formula("convert(name using GBK)")
    private String nameGBK;

and then in the criteria query we can order by the nameGBK. Here is the code:

builder.desc(join.get(Country_.NAME_GBK));

But the jpa I am using cannot parse the keywords using and GBK so we must extend the Dialect of which you are using and register the keywords.

Here is the code:

public class CustomMariaDB53Dialect extends MariaDB53Dialect {
    private static final Logger LOG = LoggerFactory.getLogger(CustomMariaDB53Dialect.class);

    public CustomMariaDB53Dialect() {
        super();
        registerKeyword("using");
        registerKeyword("USING");
        registerKeyword("GBK");
        registerKeyword("gbk");
    }
}

And change the config to tell the hibernate you are using a new Dialect. If you are using spring data jpa. Then here is the config:

spring:
  h2:
    console:
      enabled: true
  datasource:
    url: jdbc:mysql://dev:13306/schema3?useSSL=false&allowPublicKeyRetrieval=true&serverTimezone=GMT%2B8&zeroDateTimeBehavior=CONVERT_TO_NULL&nullCatalogMeansCurrent=true
    username: root
    password: root
    driver-class-name: com.mysql.cj.jdbc.Driver
    initialization-mode: always
  liquibase:
    enabled: false
  jpa:
    hibernate:
      ddl-auto: update
    show-sql: true
    properties:
      hibernate:
        order_inserts: true
        #here to tell the hibernate you are using a new dialect.
        dialect: com.hide.hide.CustomMariaDB53Dialect
        jdbc:
          batch_size: 100
General Grievance
  • 4,555
  • 31
  • 31
  • 45
zheng ma
  • 83
  • 2
  • 9