java.sql.SQLException: Prepared or callable statement has more than 2000 parameter markers及解決方案

pengisgood發表於2014-03-16
JavaSQLException

1. 問題

     最近在專案中修bug的時候，碰到這樣一個錯誤：

Caused by: java.sql.SQLException:Prepared or callable statement has more than 2000 parameter markers.    

at net.sourceforge.jtds.jdbc.SQLParser.parse(SQLParser.java:1139)    
at net.sourceforge.jtds.jdbc.SQLParser.parse(SQLParser.java:156)    
at net.sourceforge.jtds.jdbc.JtdsPreparedStatement.<init>(JtdsPreparedStatement.java:107)

Caused by: java.sql.SQLException:Prepared or callable statement has more than 2000 parameter markers.

     出錯的程式碼簡化如下（list中的元素個數可能大於2000）：

1 SELECT * FROM xxx x  
2          WHERE x.date <= :today AND x.id IN (:list)

2. 收穫

     起初並不明白是什麼情況，經過一番google加請教之後，有一些收穫。專案中用的JTDS的driver版本為1.2.4，資料庫為SQL Server。在該driver的原始碼中找到如下一個片段（在net.sourceforge.jtds.jdbc.SQLParser類中）：


     從這段程式碼中可以看出，其實這並不是一個bug，而是work as designed。我們得到的另一個資訊是，對於不同型別的資料庫，不同的版本之間，這種約束是有差別的，分別是255，1000，2000。這樣看來，之前的查詢語句出錯也就很好理解了，在IN子句中的引數個數多於2000是肯定會拋異常的。

3. 解決方案

     主要有兩種解決方案：一種是優化SQL語句，一種是優化查詢邏輯（即分批處理）。
     對於第一種方案，如果IN子句中的引數來自於另一張表的話，可以考慮類似如下的查詢方式：

1 SELECT * FROM xxx x
2          INNER JOIN yyy y ON x.id = y.id   
3          WHERE x.date <= :today

     對於第二種方案，分批來查詢速度沒有第一種快，但是不用修改SQL語句。

1         List<Item> result = Lists.newArrayList();
2         List<List<String>> partitionIds = Lists.partition(ids, BATCH_SIZE);
3         for (List<String> partitionId : partitionIds) {
4             param.put("list", partitionId);
5             List<Item> resultItems = xxxService.query(param);
6             result.addAll(resultItems);
7         }
8         return result;

     這種方案的劣勢在於速度上會有損失，但是可以從Java程式碼的邏輯層避免上面的問題。

     希望這些對大家有所幫助。


 

 

 

相關文章