oracle使用函数in解决不能超过1000条数据的限制

使用in函数查询时，如果数据超过1000条，就会报错。但是如果用or in把数据分割，就可以一次性查询超过1000条数据。

下面是分割数据的代码。

1.filereader类，用来读取文件并存到list集合

package link;

import java.io.BufferedReader;
import java.io.File;
import java.io.FileReader;
import java.io.IOException;
import java.util.LinkedList;
import java.util.List;

public class filereader {
	public List<String> read(String filename) {
		File file = new File(filename);
		LinkedList<String> list = new LinkedList<String>();
		if (file.exists() && file.isFile()) {
			try {
				BufferedReader input = new BufferedReader(new FileReader(file));
				String text;
				while ((text = input.readLine()) != null)
					list.add(text);
				input.close();
			} catch (IOException ioException) {
				System.err.println("File Error!");

			}

		}
		return list;
	}

}

2.pinjie类，遍历list集合并用or连接

package link;

import java.util.List;

import org.apache.commons.lang3.StringUtils;

public class Pinjie {
	public String getOracleSQLIn(List<?> ids, int count, String field) {//count：每count条加一个or  field：字段名
		count = Math.min(count, 1000);
		int len = ids.size();
		int size = len % count;
		if (size == 0) {
			size = len / count;
		} else {
			size = (len / count) + 1;
		}
		StringBuilder builder = new StringBuilder();
		for (int i = 0; i < size; i++) {
			int fromIndex = i * count;
			int toIndex = Math.min(fromIndex + count, len);
			String productId = StringUtils
					.defaultIfEmpty(StringUtils.join(ids.subList(fromIndex, toIndex), "'," + "\r\n" + "'"), "");
			if (i != 0) {
				builder.append(" or ");
			}
			builder.append(field).append(" in ('").append(productId).append("')");
		}

		return StringUtils.defaultIfEmpty(builder.toString(), field + " in ('')");
	}
}

3.execute类，main函数

package link;
import java.io.FileWriter;
import java.io.IOException;

public class Execute {

	public static void main(String[] args) {
		String filelocation = "D:/";// 读取文件目录
		String outlocation = "D:/";// 输出文件目录
		String file = "read";// 读取文件名
		String outfile = "write";// 输出文件名
		String columnName = "no";// 字段名
		int count = 1000;// 每1000条一个or
		filereader rf = new filereader();
		Pinjie ap = new Pinjie();
		// ap.getOracleSQLIn(rf.read(file), 1000, fileName);
		String out = ap.getOracleSQLIn(rf.read(filelocation + file + ".txt"), count, columnName);
		FileWriter fw;
		try {
			fw = new FileWriter(outlocation + outfile + ".txt", true);
			fw.flush();
			fw.write(out);
			fw.close();
		} catch (IOException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

	}
}

测试数据：read.txt,共10001条数据

运行之后的write.txt

之所以一行一条数据，是为了避免粘贴到pl/sql卡死