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("数据库连接失败");
         }

        
        
    }
    
}

 

 posted on 2017-09-27 15:42  布鲁布鲁sky  阅读(1671)  评论(0编辑  收藏  举报