前言
項(xiàng)目使用批量粘貼excel的單號(hào)進(jìn)行查詢弹澎，因業(yè)務(wù)量大芽隆，查詢數(shù)量遠(yuǎn)超1000似袁，為解決Oracle的使用限制淋叶，提供以下解決方法，經(jīng)驗(yàn)證解決了使用限制雕拼、并且查詢高效纵东、穩(wěn)定、簡(jiǎn)單啥寇。
1. 首先要?jiǎng)?chuàng)建一張session級(jí)別的臨時(shí)表
create global temporary table CONDITIONS
(
id NUMBER,
key VARCHAR2(256),
value VARCHAR2(256),
attr1 VARCHAR2(256),
attr2 VARCHAR2(256),
attr3 VARCHAR2(256),
attr4 VARCHAR2(256),
attr5 VARCHAR2(256)
)
on commit preserve rows;
-- Create/Recreate indexes
create index KEY_INDEX1 on CONDITIONS(KEY);
create index KEY_INDEX2 on CONDITIONS(ID);
2. 寫公用類
2.1. 定義一個(gè)javabean
package com.newkdd.common;
/**
*
* @author MIKE
*
*/
public class Conditions {
private long id;// 毫秒級(jí)別的時(shí)間搓
private String key;// 對(duì)應(yīng)數(shù)據(jù)庫(kù)的列名
private String value;// 對(duì)應(yīng)key的value值
private String attr1;// 備注1
private String attr2;// 備注2
private String attr3;// 備注3
private String attr4;// 備注4
private String attr5;// 備注5
//此處省略get偎球、setter方法
}
2.2. 定義一個(gè)公用方法操作類
package com.newkdd.common;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
/**
*
* @author MIKE
*
*/
public class ConditionUtil {
/**
* 添加數(shù)據(jù)關(guān)聯(lián)條件
* @param con 數(shù)據(jù)庫(kù)連接
* @param conditions 條件集合
* @return 影響的條數(shù)
*/
public static int addCondition(Connection con, List<Conditions> conditions) {
int result=0,count = 0;
PreparedStatement pst = null;
String sql = "insert into conditions(id,key,value,attr1,attr2,attr3,attr4,attr5) values(?,?,?,?,?,?,?,?)";
try {
pst = con.prepareStatement(sql);
for (Conditions condition : conditions) {
count++;
result++;
pst.setLong(1, condition.getId());
pst.setString(2, condition.getKey());
pst.setString(3, condition.getValue());
pst.setString(4, condition.getAttr1());
pst.setString(5, condition.getAttr2());
pst.setString(6, condition.getAttr3());
pst.setString(7, condition.getAttr4());
pst.setString(8, condition.getAttr5());
pst.addBatch();
if (count > 600) {
count=0;
pst.executeBatch();
}
}
if(count>0){
pst.executeBatch();
}
} catch (Exception e) {
e.printStackTrace();
}
return result;
}
/**
* 根據(jù)系統(tǒng)時(shí)間搓查詢當(dāng)前session的約束條件
* @param con 數(shù)據(jù)庫(kù)連接
* @param id 時(shí)間搓
* @return 約束條件
*/
public static List<Conditions> getConditions(Connection con, long id) {
List<Conditions> conditions = new ArrayList<Conditions>();
String sql = "select * from conditions t where t.id=?";
PreparedStatement pst = null;
try {
pst = con.prepareStatement(sql);
pst.setString(1, String.valueOf(id));
ResultSet rs = pst.executeQuery();
Conditions condition = null;
while (rs.next()) {
condition = new Conditions();
condition.setId(rs.getLong("id"));
condition.setKey(rs.getString("key"));
condition.setValue(rs.getString("value"));
condition.setAttr1(rs.getString("Attr1"));
condition.setAttr2(rs.getString("Attr2"));
condition.setAttr3(rs.getString("Attr3"));
condition.setAttr4(rs.getString("Attr4"));
condition.setAttr5(rs.getString("Attr5"));
conditions.add(condition);
}
} catch (SQLException e) {
e.printStackTrace();
}
return conditions;
}
}
3. 使用表關(guān)聯(lián)替換‘in’操作
select * from table1 t1,conditions t2 where
t1.column1=t2.value
and t2.key='column1'
and t2.id=''
現(xiàn)在在一個(gè)Session連接里邊就可以實(shí)現(xiàn)聯(lián)合查詢了,即解決了“in”的限制，又提升了查詢的速度辑甜。
