JPA Criteria API调用SQL Server数据库存在函数 [英] JPA Criteria API calling SQL Server database exist function

问题描述

我使用JPA的 CriteriaBuilder 构建查询来调用SQL Server exists 函数来查找基于一个XML字段，并且由于而无法运行。XML数据类型方法exists的参数1必须是字符串文本。错误。

I am building a query using the JPA's CriteriaBuilder to call the SQL Server exist function to find data based on an XML field and it is failing to run due to a The argument 1 of the XML data type method "exist" must be a string literal. error.

当我在SQL Server Management Studio中尝试查询时，我追溯了生成的SQL并得到相同的错误。我已经简化了SQL以供参考

I traced the SQL generated and get the same error when I try the query in SQL Server Management Studio. I've simplified the SQL to the following for reference

declare @p1 int
set @p1=NULL
exec sp_prepexec @p1 output,
     N'@P0 varchar(8000)',
     N'select id, name from mytable where xmlfield.exist(@P0)=1 order by id desc',
     'true()'
select @p1

有趣的是，

select id, name from mytable where xmlfield.exist('true()')=1 order by id desc;

有关为什么生成的生成的参数化SQL语句不起作用的想法？

Any thoughts on why the generated parameterized SQL statement generated does not work?

推荐答案

如错误消息所示，传递给exists函数的值必须是字符串文字。给出的例子是试图将一个变量传递给函数，但是当你自己运行查询时，它会工作得很好，因为你正在传递一个字符串文本。

As the error message states, the value passed into the "exist" function must be a string literal. The example given is trying to pass a variable into the function, but then when you're running the query by itself it works just fine because you're passing in a string literal.

例如...

For example...

DECLARE @xml XML = '';
DECLARE @P0 NVARCHAR(500) = 'true()';

SELECT @xml.exist(@P0) -- will not work
SELECT @xml.exist('true()') -- will work

最长的时间我认为这使得这些函数变得毫无价值，因为我不知道你可以在字符串中使用变量。这是一个例子：

For the longest time I thought this made these functions worthless because I didn't know you could use variables within the string literal. Here is an example:

DECLARE @xml XML = '<root><item id="1" /><item id="2"><test /></item></root>';
DECLARE @item_id INT;

SET @item_id = 1; -- "test" element does not exist
SELECT @xml.exist('/root/item[@id=sql:variable("@item_id")]/test')

SET @item_id = 2; -- "test" element does exist
SELECT @xml.exist('/root/item[@id=sql:variable("@item_id")]/test')

这篇关于JPA Criteria API调用SQL Server数据库存在函数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！