705
汽車大全
JDBC PreparedStatement 批量查詢 in 的實現 方案
我們經常會有這種業務需求，根據一個條件集合去查詢一張表的數據，比如：
select * from all_element
t where t.task_id in (List <taskids>);
在java語言中，我們需要用到JDBC來和數據庫打交道，那麼在JDBC中該如何處理這種需求呢？我們可以有如下幾種處理方式
方案一：寫一個函數把參數集合轉換成一個or 條件
或 in 條件的字符串，最後拚成一個sql
select * from all_element
t where t.task_id in (123 ,456, 789);
或者是：
select * from all_element
t where t.task_id=123 or t.task_id= 456 t.task_id= 789;
但是這樣效率如何呢？我們知道Oracle對傳過來的SQL是需要事先編譯的，不過是Oracle有個緩存功能可以緩存編譯好的SQL,但前提是傳過來的SQL必須完全一致，很明顯，如果按照以上方式的話，一旦taskid值變化，那麼Oracle的緩存便無法利用。
方案二：使用預編譯的PrepareStatement
為了解決Oracle緩存無法利用問題，Jdbc提供了預編譯的PrepareStatement，對於變化的參數可以用占位符
<?> 來代替，因此我們可以用占位符來減少Oracle編譯次數。
private static final String QUERY = "select
* from all_element where taskId = ?";
ps = con .prepareStatement(QUERY);
for(String taskId : taskIds){
ps.setInt(1, taskId);
rs = ps .executeQuery();
}
這樣做雖然可以很好的利用Oracle的緩存，但缺點也很明顯，就是每一個Id都需要查詢數據庫一次，這樣效率是極低的。
方案三：動態地創建PrepareStatement
雖然變化的參數可以用占位符 <?> 來代替,然而遺憾的是Jdbc隻提供了單一占位符功能即占位符不能是一個可迭代的集合。因此，對於我們傳過來的集合參數，我們可以動態地創建一個PrepareStatement：
拚一個和集合大小相等數量占位符的SQL，再寫一個循環來賦值每一個占位符，這樣就可以解決taskId的值變化而導致Oracle重新編譯SQL問題。
private static void createQuery(List<String>
taskIds) {
String query = "select * from all_element t where t.task_id in (";
StringBuilder queryBuilder = new StringBuilder(query);
for ( int i
= 0; i < taskIds.size(); i++) {
queryBuilder.append( " ?");
if (i != taskIds.size() - 1)
queryBuilder.append( ",");
}
queryBuilder.append( ")");
ps = con .prepareStatement(query);
for ( int i
= 1; i <= taskIds.size(); i++) {
ps.setInt(i, taskIds.get(i - 1));
}
rs = ps .executeQuery();
}
但是這麼做還是存在一個問題，如果集合的值變化不會導致Oracle重新編譯，但是如果集合的大小發生變化，相對應的SQL也就發生了變化，同樣也會導致Oracle重新編譯，那麼該如何解決這個問題呢？
方案四：批量查詢（減少查詢次數並利用到Oracle緩存）
批量查詢兼顧了第二、第三種方案，其思想是預先定義好幾個每次要查詢參數的個數，然後把參數集合按這些定義好的值劃分成小組。比如我們的前台傳過來一個數量為75的taskId的集合，我預定義的批量查詢參數的個數分別為：
SINGLE_BATCH = 1;//注意：為了把參數集合完整劃分，這個值為1的批量數是必須的
SMALL_BATCH = 4;
MEDIUM_BATCH = 11;
LARGE_BATCH = 51;
那麼我們第一次會查詢51條數據，還剩下24個沒有查詢，那麼第二次批量查詢11條數據，還剩下13條未查詢，第三次再批量查詢11條數據，最後還剩2條未查詢，那麼我們再分兩批次，每批次僅查詢一條，這樣，最終一個75條的數據分5批次即可查詢完成，減少了查詢次數，而且還利用到了數據庫緩存。附獲取批量的算法：
public static final int SINGLE_BATCH =
1; //注意：為了把參數集合完整劃分，這個值為1的批量數是必須的
public static final int SMALL_BATCH =
4;
public static final int MEDIUM_BATCH =
11;
public static final int LARGE_BATCH =
51;
static int totalNumberOfValuesLeftToBatch=75;
public static List<Integer>
getBatchSize( int totalNumberOfValuesLeftToBatch){
List<Integer> batches= new ArrayList<Integer>();
while ( totalNumberOfValuesLeftToBatch > 0 ) {
int batchSize = SINGLE_BATCH;
if ( totalNumberOfValuesLeftToBatch >= LARGE_BATCH )
{
batchSize = LARGE_BATCH;
} else if (
totalNumberOfValuesLeftToBatch >= MEDIUM_BATCH ) {
batchSize = MEDIUM_BATCH;
} else if (
totalNumberOfValuesLeftToBatch >= SMALL_BATCH ) {
batchSize = SMALL_BATCH;
}
batches.add(batchSize);
totalNumberOfValuesLeftToBatch -= batchSize;
System. out.println(batchSize);
}
return batches;
}
最後更新：2017-04-03 20:19:25