经常为了测试性能必不可少的就是要向数据库中插入大批量的数据,很多人可能写一个存贮过程,但有时会发现这并不灵活.所以写了个jsp来实现批量插入数据的功能.
insertdata.jsp
<%@page import="com.metasoft.framework.db.ResManager"%>
<%@page import="java.sql.Connection"%>
<%@ page contentType="text/html;charset=UTF-8"%>
<%
//
request.setCharacterEncoding("UTF-8");
%>
<html>
<body>
<br/>
<form name="formcoalitiondata" method="post" action ="execinsert.jsp">
需要执行的SQL,不包括需要变化的字段:<br/>
<textarea rows="12" cols="140" name="sql"></textarea>
<br/>
执行次数:<input type="text" name="execnum" >
<br/>
第一个需要变化的字段:<input type="text" name="firstfield"/>
第一个需要变化的字段值:
<select name="firstfieldvalue">
<option value="" selected="selected">请选择</option>
<option value="byte32">32位随机数</option>
<option value="randomint">随机整数(1-100000)</option>
<option value="randomname">随机中文名称(3-6字)</option>
<option value="curtime">当前时间(长整型)</option>
<option value="randomdate">随机日期</option>
</select>
<br/>
第二个需要变化的字段:<input type="text" name="secondfield"/>
第二个需要变化的字段值:
<select name="secondfieldvalue">
<option value="" selected="selected">请选择</option>
<option value="byte32">32位随机数</option>
<option value="randomint">随机整数(1-100000)</option>
<option value="randomname">随机中文名称(3-6字)</option>
<option value="curtime">当前时间(长整型)</option>
<option value="randomdate">随机日期</option>
</select>
<br/>
第三个需要变化的字段:<input type="text" name="thirdfield"/>
第三个需要变化的字段值:
<select name="thirdfieldvalue">
<option value="" selected="selected">请选择</option>
<option value="byte32">32位随机数</option>
<option value="randomint">随机整数(1-100000)</option>
<option value="randomname">随机中文名称(3-6字)</option>
<option value="curtime">当前时间(长整型)</option>
<option value="randomdate">随机日期</option>
</select>
<br/>
<button type="submit">提交</button>
</form>
<br>
</body>
</html>
execinsert.jsp
<%@ page contentType="text/html;charset=UTF-8"%>
<%
//
request.setCharacterEncoding("UTF-8");
String resultStr="";
if(us==null){
return;
}else{
Connection conn=//获得连接
try{
String sql=request.getParameter("sql");
int execnum=Integer.parseInt(request.getParameter("execnum"));
String firstfield=request.getParameter("firstfield");
String firstfieldvalue=request.getParameter("firstfieldvalue");
String secondfield=request.getParameter("secondfield");
String secondfieldvalue=request.getParameter("secondfieldvalue");
String thirdfield=request.getParameter("thirdfield");
String thirdfieldvalue=request.getParameter("thirdfieldvalue");
if(sql==nu||sql.equal("")llexecnum==null||execnum.equal("")){
resultStr="执行语句和执行次数不能为空!";
}else{
CommonPO po=new CommonPO(conn,us);
for(int i=0;i<execnum;i++){
String newSql="";
newSql=addValue(sql.toLowerCase(),firstfield,getRealValue(firstfieldvalue),firstfieldvalue);
newSql=addValue(newSql,secondfield,getRealValue(secondfieldvalue),secondfieldvalue);
newSql=addValue(newSql,thirdfield,getRealValue(thirdfieldvalue),thirdfieldvalue);
if(!StringUtil.isEmpty(newSql)){
po.update(newSql);
}
}
conn.commit();
resultStr="语句执行完毕!";
}
}catch(Exception e){
resultStr=e.getMessage();
}finally{
freeConnection(us.getCorpName(),conn);
}
}
%>
<html>
<body>
<%=resultStr %>
<br>
</body>
</html>
<%!
private static Object getRealValue(String selectValue){
Object obj=null;
if("byte32".equals(selectValue)){
obj=Oid.getOid();//生成32位随机数
}else if("randomint".equals(selectValue)){
obj=(new Random(100000)).nextInt();
}else if("randomname".equals(selectValue)){
Random ran = new Random();
String name="";
int namelength=3+ran.nextInt(3);
obj=getCharacter(namelength,"c300");
}else if("curtime".equals(selectValue)){
obj=DateUtil.getLongDate();
}else if("randomdate".equals(selectValue)){
long time=Date.parse(DateUtil.getCurDate())-(new Random(100000)).nextInt();
obj=DateUtil.getShortDate(time);
}else {
obj=null;
}
return obj;
}
private static String getCharacter(int num,String type){
Random ran = new Random();
String name="";
String c300="一二三四五六七八九十百千加减乘除大小多少长短方圆前后左右上下里外远近高矮出入开关起落来去轻重快慢有无空满凉热冷暖真假好坏生熟湿干粗细软硬横竖直弯正斜薄厚宽窄深浅春夏秋冬阴晴早晚东南西北酸甜苦辣日月水火山石土田天地星云风雨雷电衣帽鞋袜巾帕枕垫杯瓶壶桶钟表灯扇勺筷刀叉锅碗盘盆镜梳床凳钉铲针线琴棋书画笔纸尺盒伞烛鼓铃球筒箱包瓜果梨桃饭菜米面葱姜蒜椒糕饼蛋糖柿茄菠豆莓李橙蕉红黄蓝绿紫粉白黑花草树木梅兰竹菊冰雪沙虹江河海瀑桌椅门窗机车船帆枝叶芽苗杨柳松桦哥姐弟妹爷奶爸妈你我他她男女老幼眼耳口鼻手足牙脸人身头发师生工医哭笑喜怒吃喝醒睡走跑坐卧摸爬站立折剪编涂滑骑抱玩写指举拍看想拿洗鸟兽虫鱼猫狗兔雁猪马牛羊鸡鸭鹅燕";
if("c300".equals(type)){
for(int i=0;i<num;i++){
int namelength=ran.nextInt(300);
name+=c300.charAt(namelength);
}
}else{
int delta = 0x5980 - 0x4f00 + 1;
for(int i=0;i<num;i++){
name+=(char)(0x4e00 + ran.nextInt(delta));
}
}
return name;
}
private static String addValue(String sql,String field,Object fieldValue,String selectValue){
String newsql="";
if(StringUtil.isEmpty(sql)){
}else{
if(StringUtil.isEmpty(field)||StringUtil.isEmpty(fieldValue)){
newsql=sql;
}else{
sql=sql.toLowerCase();
int sqlIndex=sql.indexOf("insert");
int valueIndex=sql.indexOf("values");
int fieldFromIndex=sql.indexOf("(");
int fieldEndIndex=sql.indexOf(")");
int valueFromIndex=sql.indexOf("(",valueIndex);
int valueEndIndex=sql.lastIndexOf(")");
if(sqlIndex==-1||valueIndex==-1||fieldFromIndex==-1||fieldEndIndex==-1||valueFromIndex==-1||valueEndIndex==-1){
return "";
}
String fieldStr=sql.substring(fieldFromIndex+1,fieldEndIndex);
String valueStr=sql.substring(valueFromIndex+1,valueEndIndex);
fieldStr+=","+field;
if("byte32".equals(selectValue)||"randomname".equals(selectValue)){
valueStr+=",\'"+fieldValue+"\'";
}else{
valueStr+=","+fieldValue+"";
}
newsql=sql.substring(0,fieldFromIndex)+"("+fieldStr+") values ("+valueStr+");";
}
}
return newsql;
}
%>