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 class Two { static Connection conn; static Statement stmt; static ResultSet rs; static String url = "jdbc:sqlserver://10.86.57.121:1433;DatabaseName=ZXRIS_ITDP;"; static String aaa = "abc"; //插入原始数据 public static void InsertBegin(){ 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 < insertCount) { 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 = new StringBuffer(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 void OneQuery(){ 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 void MoreQuery(){ 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 void SortQuery(){ 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 void Count(){ 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 void main(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("数据库连接失败"); } } }