jdbc执行mysql函数,如何通过条件查询调用使用mysql关键字作为参数的函数?

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.

解决方案

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 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!!!