oracle in查询 1000条限制的解决方案

1. 首先要创建一张session级别的临时表

-- Create table
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. 写公用类

 

A.定义一个javabean

 

package com.mike.orderconfirm.utils;
/**
 *
 * @author MIKE
 *
 */
public class Conditions {
 
 private long id;// 毫秒级别的时间搓
 private String key;// 对应数据库的列名
 private String value;// 对应key的value值
 private String attr1;// 备注1
 private String attr2;// 备注2
 private String attr3;// 备注3
 private String attr4;// 备注4
 private String attr5;// 备注5

 public long getId() {
  return id;
 }

 public void setId(long id) {
  this.id = id;
 }

 public String getKey() {
  return key;
 }

 public void setKey(String key) {
  this.key = key;
 }

 public String getValue() {
  return value;
 }

 public void setValue(String value) {
  this.value = value;
 }

 public String getAttr1() {
  return attr1;
 }

 public void setAttr1(String attr1) {
  this.attr1 = attr1;
 }

 public String getAttr2() {
  return attr2;
 }

 public void setAttr2(String attr2) {
  this.attr2 = attr2;
 }

 public String getAttr3() {
  return attr3;
 }

 public void setAttr3(String attr3) {
  this.attr3 = attr3;
 }

 public String getAttr4() {
  return attr4;
 }

 public void setAttr4(String attr4) {
  this.attr4 = attr4;
 }

 public String getAttr5() {
  return attr5;
 }

 public void setAttr5(String attr5) {
  this.attr5 = attr5;
 }
}

B.定义一个公用方法操作类

 

package com.mke.orderconfirm.utils;

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 {

 /**
  * 添加数据关联条件
  * @param con 数据库连接
  * @param conditions 条件集合
  * @return 影响的条数
  */
 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;
 }

 /**
  * 根据系统时间搓查询当前session的约束条件
  * @param con 数据库连接
  * @param id 时间搓
  * @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.系统中添加关联

 

select * from table1 t1,conditions t2 where

t1.column1=t2.value

and t2.key='column1'

and t2.id=''

 

现在在一个连接里边就可以实现联合查询了。

 

试试看吧，体验一下速度！