前天做性能测试的时候,需要生成大量的数据,通过BCP命令有时候会出现些问题,所以写了一个批量生成SQL的小工具,运行很简单,但有一个小缺点,生成1,000,000或以上数据的时候,通过数据库执行生成的SQL脚本,会花费比较长的时间。
1,Generat.java,程序文件
import java.io.BufferedInputStream;
import java.io.BufferedReader;
import java.io.ByteArrayInputStream;
import java.io.File;
import java.io.FileOutputStream;
import java.io.FileReader;
import java.io.IOException;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.List;
import java.util.Properties;
import java.text.SimpleDateFormat;
/**
* 数据生成工具
* @author Civvy.wu@gmail.com
*
* @version 0.2
*
* 将需要生成的SQL的标准格式,内容需要变化的字段和总记录数配置在文件中,运行生成。
*/
public class Generat {
private String fileName = "generat";
private String sql;
private String fields;
private int counter;
/**
* 数据生成方法
*
* @param config String 配置文件名称
*/
public boolean execute(String config) {
init(config);
if (sql == null || sql.trim().equals(""))
return false;
if (counter == 0)
return false;
FileOutputStream os = null;
BufferedInputStream is = null;
SimpleDateFormat style = new SimpleDateFormat("yyyy-MM-dd HH-mm-ss");
try {
File file = new File(fileName + ".SQL");
if (file.exists()) {
file.renameTo(new File(fileName + "." + style.format(Calendar.getInstance().getTime()) + ".SQL"));
file.delete();
}
file.createNewFile();
os = new FileOutputStream(file, true);
StringBuffer buffer = new StringBuffer();
List list = execute();
int length = Integer.toString(counter).length();
for (int i=0; i
buffer.append(execute(list, length, i) + "\r\n");
if (buffer.length() >= 1200000) {
buffer.append("commit;\r\n\r\n");
is = new BufferedInputStream(
new ByteArrayInputStream(buffer.toString().getBytes()));
append(is, os);
buffer.delete(0, buffer.length());
}
}
if (buffer.length() > 0 && buffer.length() < 1200000) {
buffer.append("commit;");
is = new BufferedInputStream(
new ByteArrayInputStream(buffer.toString().getBytes()));
append(is, os);
buffer.delete(0, buffer.length());
}
os.flush();
return true;
} catch (IOException ex) {
System.out.println("调用Generat.execute(String s1)方法时出现异常:" + ex.getMessage());
} finally {
try {
if (is != null)
is.close();
if (os != null)
os.close();
} catch (Exception e) {}
}
return false;
}
/**
* 追加记录到生成的文件中
* @param is BufferedInputStream 文件输入流
* @param os FileOutputStream 文件输出流
*/
private void append(BufferedInputStream is, FileOutputStream os) {
try {
int c;
byte[] cache = new byte[2048];
while ((c = is.read(cache)) != -1) {
os.write(cache, 0, c);
}
} catch (IOException ex) {
System.out.println("调用Generat.write(BufferedInputStream b1, FileOutputStream f1)方法时出现异常:" + ex.getMessage());
}
}
/**
* 获取标准SQL语句,并进行需要改变的内容的格式替换
* @return List 返回包含标准SQL语句和原始值的对象
*/
private List execute() {
StringBuffer buff = new StringBuffer();
buff.append(sql);
List former = new ArrayList();
if (fields == null || fields.trim().equals("")) {
former.add(buff.toString());
return former;
}
int index = sql.indexOf("(");
int place = sql.indexOf(")");
if (index != -1 && place != -1) {
String prefix = sql.substring(index+1, place);
String[] field = prefix.split(",");
if (field != null && field.length > 0) {
index = sql.lastIndexOf("(");
place = sql.lastIndexOf(")");
if (index != -1 && place != -1) {
String suffix = sql.substring(index+1, place);
String[] column = suffix.split(",");
if (column != null && column.length > 0) {
List list = new ArrayList();
String[] serial = fields.split(";");
for (int i=0; i
for (int j=0; j
if (serial[i].equalsIgnoreCase(field[j].trim())) {
list.add(column[j]);
break;
}
}
}
if (list.size() > 0) {
String replace = null;
for (int i=0; i
replace = (String) list.get(i);
former.add(replace);
this.sql = sql.replaceAll(replace, "@Regex@");
}
buff = new StringBuffer();
buff.append(sql);
former.add(buff.toString());
}
}
}
}
}
return former;
}
/**
* 根据包含标准SQL在内的对象列表,总记录数和当前循环值生成内容替换后的SQL语句
* @param list List 包含标准SQL在内的对象列表
* @param length int 总记录数的长度
* @param value int 循环中的当前值
* @return String 返回内容替换后的SQL语句
*/
private String execute(List list, int length, int value) {
if (list != null && list.size() > 0) {
int size = list.size();
String db = (String) list.get(size-1);
String replace;
for (int i=0; i
replace = (String) list.get(i);
db = db.replaceAll("@Regex@", full(replace, length, value));
}
return db;
}
return null;
}
/**
* 根据原始内容,最大长度和当前值返回长度统一的字符串
* @param former String 原始内容
* @param length int 最大长度
* @param value int 当前值
* @return String 返回长度统一的字符串
*/
private String full(String former, int length, int value) {
former = former.substring(0, former.length()-1);
int current = Integer.toString(value).length();
int fill = length - current;
if (fill > 0) {
String target = "";
while (fill > 0) {
target += "0";
fill--;
}
return former + target + value + "'";
}
return former + Integer.toString(value) + "'";
}
/**
* 获取配置文件内容
*
* @param config String 配置文件名称
*/
private void init(String config) {
if (config == null || config.trim().equals(""))
return;
Properties prop = new Properties();
try {
//jdk1.4版本不支持Properties.load(Reader)方法,需要构造InputStream
BufferedReader reader = new BufferedReader(new FileReader("./" + config));
String line;
StringBuffer buffer = new StringBuffer();
while ((line = reader.readLine()) != null) {
if (!line.startsWith("#") && !line.trim().equals("")) {
buffer.append(line + "\r\n");
}
}
prop.load(new BufferedInputStream(new ByteArrayInputStream(buffer.toString().getBytes())));
//jdk1.5或以上版本支持Properties.load(Reader)方法,则不需要构造InputStream
//prop.load(new FileReader("./" + config));
this.sql = prop.getProperty("generat.sql");
this.fields = prop.getProperty("generat.fields");
String count = prop.getProperty("generat.counter");
if (count == null || count.trim().equals("")) {
this.counter = 0;
return;
}
this.counter = Integer.parseInt(count);
} catch (IOException ex) {
System.out.println("调用Generat.init(String s1)方法时出现异常:" + ex.getMessage());
} catch (NumberFormatException ex) {
this.counter = 0;
System.out.println("调用Generat.init(String s1)方法时出现异常:" + ex.getMessage());
}
}
/**
* 程序入口
* 需要指定配置文件的名称
*/
public static void main(String[] args) {
Generat generat = new Generat();
if (args != null && args.length > 0) {
generat.execute(args[0]);
return;
} else {
generat.execute("generat.properties");
}
}
}
2,generat.properties,配置文件
# 需要生成的标准SQL语句
generat.sql = INSERT INTO IWB_FRAME_EMPLOYEE(EMPL_CODE, EMPL_NAME, EMPL_TYPE, INST_CODE) VALUES('empl', 'user', '1', '000000');
# 需要改变内容的字段名称
# 如果有多个需要改变内容的字段,用;号间隔
generat.fields = EMPL_CODE
# 需要生记的总记录数
generat.counter = 10000