前端之家

  1. 首页
  2. 问答

如何通过条件查询调用使用mysql关键字作为参数的函数？

• 问答

我正在将mysql与jpa规范查询一起使用。 我想知道如何调用使用mysql关键字作为参数的函数。

这里是示例：

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

使用convert和using关键字作为参数的GBK方法。

我想通过条件查询来调用convert函数。

我尝试了以下操作，但对我不起作用。

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

和

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

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

变量countryNameAlias为空，因此它不起作用。

这是错误：

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_,'$.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]

谢谢大家。

kuerrcn 回答：如何通过条件查询调用使用mysql关键字作为参数的函数？

void OLList::copy(const OLList& source) { Node ** tmp = &headM; // we'll put the nodes with this ptr Node * toCopy = source.headM; while (toCopy != nullptr) { Node * node = new Node; // create new node if (node == nullptr) exit(1); *tmp = node; // put the node to its place (headM at first,then next member of prev node) node->item = toCopy->item; // copy the value toCopy = toCopy->next; tmp = &(node->next); // update where we'll put the next node } *tmp = nullptr; // that was the last one } 注释不适用于本机查询。 这是链接：I am the link.

我对jpa不太熟悉，但这是比使用@formula注释更好的解决方案。

首先，您需要创建自定义方言 这是示例：

@Formula

然后向方言注册一个功能 这是代码：

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

    public CustomMariaDB53Dialect() {
        super();
}

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)")); } } 方法的第一个参数是函数名称。

第二个是registerFunction

的实现

SqlFunction和?1表示函数的参数 所以功能模板是?2

完成上述步骤之后。

您应该告诉休眠状态您正在使用新的方言

配置路径为convert(?1 using ?2)

这里是示例：

hibernate.dialect

如果您使用的是Spring Boot jpa。

这是配置：

hibernate.dialect=xxx.xxx.CustomMariaDB53Dialect

最后一步是在查询中使用该函数。

这里是示例：

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

这是最终的sql：

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

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

干杯！

,

我将sql更改为以下内容：

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

所以下一个问题是如何将计算列添加到选择中。 我们可以在国家模型中添加一列。 字段名称为nameGBK，并使用@Formula批注； 这是代码

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

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

，然后在条件查询中，我们可以按nameGBK进行排序。 这是代码：

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

但是我正在使用的jpa无法解析关键字using和GBK，因此我们必须扩展 您正在使用的方言并注册关键字。

代码如下：

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");
    }
}

并更改配置以告诉休眠您正在使用新的方言。 如果您正在使用spring数据jpa。 然后是配置：

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

如果这可以帮助您投票给我。谢谢。

criteria-apijavajpa
本文链接：https://www.f2er.com/3160223.html

大家都在问