使用Java的PreparedStatement将数组传递给SQL查询 [英] Passing an Array to a SQL query using Java's PreparedStatement

问题描述

使用Java编写的语句将数组传递给SQL查询时遇到了一些麻烦。我首先尝试了sourceforge驱动程序，但是当我调用setArray方法时，我会得到AbstractMethodError。不知道我交换到Microsoft sqlserver驱动程序的解决方案，但现在我完全得到一个不同的错误，即java.sql.SQLFeatureNotSupportedException：不支持此操作。尝试了很多东西来尝试解决这个问题，但似乎没有任何工作可行。

I've been having a little trouble passing an array into a SQL query using Java's prepared statements. I had first tried the sourceforge driver, however I would get the AbstractMethodError when I call setArray method. Not knowing the solution to that I swapped to the Microsoft sqlserver driver, but now I get a different error entirely, which is "java.sql.SQLFeatureNotSupportedException: This operation is not supported." Tried a whole bunch of things to try and resolve this but nothing appears to work.

我的Java代码看起来类似于我在这里和互联网上看到的例子，如下所示，

My Java code looks similar to examples I've seen here and on the internet, and is as follows,

PreparedStatement ps = connection.prepareStatement(query);
String[] suppliers = {"21","2774","120563","3714","59"};
ps.setArray(1, connection.createArrayOf("text", suppliers));
ResultSet rs = ps.executeQuery();

我的SQL查询示例。真正感兴趣的唯一一行是前一行，我添加了'？'字符，据我所知，这是我如何将参数传递给SQL查询。

Example of my SQL queries. The only line of real interest is the line before last where I have added the '?' character, which as I understand it is how I pass a parameter to a SQL Query.

productsPrices.query = select contract.supplierid as 'hotelid' \
  , round((rate.s1/money.buy)*euro.sell,0) as "single" \
  , round((rate.s2/money.buy)*euro.sell,0) as "double" \
  ,service.Name as 'roomtype' \
  ,stock.alloc - stock.taken as 'stock.available' \
  , contract.notes as 'boardType' \
  , object.name as 'occupancy' \
  ,object.cap as 'capacity' \
  ,object.mincap as 'min capacity' \
  ,stock.date as 'date' \
from stock stock \
  inner join rate rate on stock.rateid = rate.id \
  inner join contract contract on rate.contractid = contract.id \
  inner join service service on contract.serviceid = service.ID \
  inner join object object on service.objectid = object.ID \
  inner join band band on contract.termsid = band.ID \
  inner join Money money on band.moneyid = money.id \
  inner join Money euro on euro.Name  = 'Euros' \
where stock.date > getdate() \
and stock.closed = 0 \
and (stock.alloc - stock.taken) > 0 \
and stock.date > getdate() \
      and contract.supplierid in (?) \
      and contract.Finish > GETDATE()

推荐答案

我通过动态生成字符串解决了同样的问题带有所需的问号数量。
这是一个片段： -

I solved same issue by dynamically generating string with required number of question marks. Here's a snippet:-

String param = "(";
for(int i=0;i<suppliers.length;i++){
param = param+"?,";
}
param = param.substring(0,param.length()-1);
param=param+")";

query = query + param;

PreparedStatement ps = connection.prepareStatement(query);

for(int i=0;i<suppliers.length;i++){
ps.setString(i+1,suppliers[i]);
}

这篇关于使用Java的PreparedStatement将数组传递给SQL查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！