java - JPA2 CreateQuery SQL Server 方言

Question

java.persistence.Query 中的函数 CreateQuery(string) 返回的查询与我请求的查询不同。

我的函数输入字符串是:

from Underlying where (SUBSTRING(ticker, (charindex('.', ticker) + 1), (len(ticker) - charindex('.', ticker))) in (:exchanges) order by ticker

当我在查询上调用 getResultList() 时，我在日志中看到以下 sql:

Hibernate: select underlying0_.id as id63_, underlying0_.updated_date as updated2_63_, underlying0_.updated_user as updated3_63_, underlying0_.version as version63_, underlying0_.adr as adr63_, underlying0_.desk as desk63_, underlying0_.enabled as enabled63_, underlying0_.forward_start_enabled as forward8_63_, underlying0_.ticker as ticker63_, underlying0_.vol_shift_type as vol10_63_ from underlyings underlying0_ where (substring(underlying0_.ticker, charindex('.', underlying0_.ticker)+1) in (?)) order by underlying0_.ticker

此查询不是有效的 SQL Server 2008 查询，正如预期的那样，我收到了错误:

SQL Error: 174, SQLState: S0001 The substring function requires 3 argument(s).

有人知道这是怎么回事吗？

仅供引用，我尝试了以下两种方言1)org.hibernate.dialect.SQLServer2008Dialect和2)org.hibernate.dialect.SQLServer2008Dialect

Answer 1

问题出在函数 charindex 上。它在方言中不可用，因此会导致映射不正确。将其更改为 HQL 函数“locate”(保持参数不变)解决了该问题。我的查询现在可以在 SQL Server 2008 中运行:

...where (substring(underlying0_.ticker, charindex('.', underlying0_.ticker)+1, len(underlying0_.ticker)-charindex('.', underlying0_.ticker)) in (?))...