package InsertTest;/** 单客户端:批量插入*/import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.DecimalFormat;
import java.text.NumberFormat;
import org.apache.commons.lang.StringUtils;public classTwo {staticConnection conn;staticStatement stmt;staticResultSet rs;static String url = "jdbc:sqlserver://10.86.57.121:1433;DatabaseName=ZXRIS_ITDP;";static String aaa = "abc";//插入原始数据
public static voidInsertBegin(){
String sb1= "insert into VEHICLE_PASS_RECORD(RECORD_ID ,CARD_ID, SOURCE"
+ ",COLLECT_TIME ,FIRST_DISCOVER_TIME ,LAST_DISCOVER_TIME ,RECEIVED_TIME ,DEVICE_DESCRIPTION"
+ ",CONFIDENCE ,ATTACHMENT ,LOCATION ,ARCHIVE_ID ,VEHICLE_PLATE_NUM ,EID"
+ ",VEHICLE_TYPE ,VEHICLE_USER_TYPE ,PLATE_TYPE ,FACTORY_DATE ,POWER_RATING"
+ ",DISPLACEMENT ,EXAMINE_EXPIRE_DATE ,FORCE_SCRAP_DATE ,VEHICLE_COLOR"
+ ",CARRY_CAPACITY ,SECTION_NAME ,DIRECTION ,LANE_NUMBER ,DEVICE_TYPE"
+ ",DEVICE_ID ,DISCOVER_TYPE ,TID ,PROTOCOL) VALUES(";int record_id = 0; //记录ID
long collect_time = System.currentTimeMillis(); //记录时间
long spendtime = 0;//add
Long insertCount = 10000000L; //插入100万
Long currentCount = 0L; //当前值
Long sectionCount = 2000L; //断面数量
Long maxPlateNumCount = 1000000L;//车牌号最大量
String sectionNamePre = "断面";
String[] citys= {"津","京","宁"};while (currentCount
String sectionName= sectionNamePre + (currentCount % sectionCount); //断面:循环0-1999
Long plateNum = currentCount %maxPlateNumCount;
String city= citys[Integer.valueOf(String.valueOf((currentCount / maxPlateNumCount) %citys.length))];
String plateNumStr= city + StringUtils.leftPad(String.valueOf(plateNum), 6, "0"); //车牌号:循环300万
currentCount++; //当前值 +1
record_id ++ ; //record_id +1
collect_time = collect_time + 4; //collect_time +4
StringBuffer sb2= newStringBuffer(sb1);
sb2.append(record_id).append(",2, 3,").append(collect_time).append(",5,6,7,8,9")
.append(",10,11,12,").append("'").append(plateNumStr).append("'")
.append(",14,15,16,17,18,19")
.append(",20,21,22,23,24,")
.append("'").append(sectionName).append("'")
.append(",26,27,28,29")
.append(",30,31,32)");try{
stmt.addBatch(sb2.toString());if(currentCount % 5000 == 0){long startTime=System.currentTimeMillis();
stmt.executeBatch();long endTime = System.currentTimeMillis()-startTime;
spendtime= spendtime +endTime;
}
}catch(SQLException e) {
e.printStackTrace();
}
}
System.out.println( "插入处理花费时间:"+spendtime);//end
}//单查询
public static voidOneQuery(){
String sqlQuery= "select top 10 VEHICLE_PLATE_NUM, COLLECT_TIME,VEHICLE_TYPE,SECTION_NAME from [ZXRIS_ITDP].[dbo].[VEHICLE_PASS_RECORD] v where v.SECTION_NAME = '断面1999'";try{long starttime=System.currentTimeMillis();
rs=stmt.executeQuery(sqlQuery);//while(rs.next()){//System.out.println(rs.getInt("RECORD_ID"));//}
long spendtime=System.currentTimeMillis()-starttime;
System.out.println( "查询花费时间:"+spendtime);
}catch(SQLException e) {
e.printStackTrace();
}
}//多查询
public static voidMoreQuery(){
String sqlQuery= "select top 10 VEHICLE_PLATE_NUM, COLLECT_TIME,VEHICLE_TYPE,SECTION_NAME from [ZXRIS_ITDP].[dbo].[VEHICLE_PASS_RECORD] v where v.SECTION_NAME = '断面1999'";try{long starttime=System.currentTimeMillis();
rs=stmt.executeQuery(sqlQuery);long spendtime=System.currentTimeMillis()-starttime;
System.out.println( "查询花费时间:"+spendtime);
}catch(SQLException e) {
e.printStackTrace();
}
}//排序查询
public static voidSortQuery(){
String sqlQuery= "select top 10 VEHICLE_PLATE_NUM, COLLECT_TIME,VEHICLE_TYPE,SECTION_NAME from [ZXRIS_ITDP].[dbo].[VEHICLE_PASS_RECORD] v where v.SECTION_NAME = '断面1999'";try{long starttime=System.currentTimeMillis();
rs=stmt.executeQuery(sqlQuery);long spendtime=System.currentTimeMillis()-starttime;
System.out.println( "查询花费时间:"+spendtime);
}catch(SQLException e) {
e.printStackTrace();
}
}//排序查询
public static voidCount(){
String sqlQuery= "select count(*) from [ZXRIS_ITDP].[dbo].[VEHICLE_PASS_RECORD] v where v.SECTION_NAME = '断面1999'";try{long starttime=System.currentTimeMillis();
rs=stmt.executeQuery(sqlQuery);long spendtime=System.currentTimeMillis()-starttime;
System.out.println( "查询花费时间:"+spendtime);
}catch(SQLException e) {
e.printStackTrace();
}
}public static voidmain(String[] args) {try{
conn= DriverManager.getConnection(url, "sa", "Rfid123456");
stmt=conn.createStatement();
InsertBegin();if (rs != null) {
rs.close();
rs= null;
}if (stmt != null) {
stmt.close();
stmt= null;
}if (conn != null) {
conn.close();
conn= null;
}
}catch(SQLException e) {
e.printStackTrace();
System.out.println("数据库连接失败");
}
}
}