testsqlite.java

来自「主要对各种数据库性能进行测试」· Java 代码 · 共 582 行 · 第 1/2 页

	             st.setInt(9, i); 
	             st.executeUpdate();
	             long lend2 = 0;
	             if((i+1)%inscommit==0){ 
		            conn.commit();
		            lend2=System.currentTimeMillis();
		            //System.out.println("第"+(i+1)+"行提交！耗时："+(lend2 - lstart2)+"ms.");
		            lstart2=System.currentTimeMillis();
	             }
	        }   
	        conn.commit(); 
	        if (st != null) st.close();
	        lEnd = System.currentTimeMillis();
	        lFee = lEnd - lStart;
	        
	        in = "sql="+sql;
	        System.out.println(in);
	        file.aLine(in);
	        
	        in = "插入"+insertnum+"条记录，每"+inscommit+"条提交一次，花费时间："+lFee+"ms";
	        System.out.println(in);
	        file.aLine(in); 
	        /*===============================查询记录总条数===============================*/  
	         sql="select count(*) from person"; 
	        rset = stmt.executeQuery(sql); 
	        while (rset.next()){ 
	        	in = "插入后总记录数:"+rset.getInt(1);
	            System.out.println(in);
	            file.aLine(in);
	        }  
	        if (rset!=null){ 
	            rset.close(); 
	            rset = null; 
	        }   
	        
	        /*===============================更新数据===============================*/  
	        lStart = System.currentTimeMillis();
	        if(stmt!=null) stmt.close();
	        stmt = conn.createStatement(); 
	        for(int i=0;i<updatetime;i++){
	        	int iid =  rand.nextInt(updatetime); 
	        	sql="update person set name4='asdfasdfsa' where id between " +iid +" and "+(iid+updatenum);
	        	
	        	stmt.executeUpdate(sql);
	        	if(i%updcommit==0)
           	 		conn.commit();
	        }
	        //sql="update person set name4='oooooooooooo' where id between ? and ?";
/*	        sql="INSERT INTO person(name,name3,name4,name5,name6,name7,name8,name9,name10) VALUES('abcdefghijklmnopqrst','abcdefghijklmnopqrst','abcdefghijklmnopqrst','abcdefghijklmnopqrst','abcdefghijklmnopqrst','abcdefghijklmnopqrst','abcdefghijklmnopqrst','abcdefghijklmnopqrst',666666)";
	        System.out.println(sql);
	        st = conn.prepareStatement(sql);
	        System.out.println("dddddd");
            for(int i=0; i<updatetime; i++) {
            	st.execute();
            	if(i%updcommit==0)
           	 		conn.commit(); 
            }*/
            
	        /*for (int i=0; i<updatetime; i++) {
	        	int iid =  rand.nextInt(updatetime); 
	            st.setInt(1, iid);
	            st.setInt(2, iid+updatenum);
	            //System.out.println(iid+updatenum);
	            //st.executeBatch();
	            st.executeUpdate();
 	            if(i%updcommit==0)
           	 		conn.commit(); 
	        } */  
	        conn.commit(); 
	        if (st != null) st.close();
	        
	        //conn.commit(); 
	        lEnd = System.currentTimeMillis();
	        lFee = lEnd - lStart;
	        
	        in = "sql="+sql;
	        System.out.println(in);
	        file.aLine(in);
	        
	        in = "update"+updatetime+"次数据，每次"+updatenum+"条，每"+updcommit+"条提交一次，花费时间（id为条件）："+lFee+"ms";
	        System.out.println(in);
	        file.aLine(in);
	        
	         
	        /*===============================查询数据（不用索引）========================*/ 
	        lStart = System.currentTimeMillis();
	        for(int i=0;i<selecttime;i++){
	        	/*sql = "select * from person order by id " ;
	        	if(i%2==0){
	        		sql += " desc ";
	        	}*/
	        	int ii = rand.nextInt(10000);
	        	int iii = selectnum - 1;
	        	sql = "select id, name from person where name10 between " + ii + " and " + (ii+iii);
		        rset = stmt.executeQuery(sql); 
		        if(showprint.equalsIgnoreCase("yes")){
			        while (rset.next()){ 
			        	in = "查询结果:"+rset.getInt("id")+"	"+rset.getString("name");
			            System.out.println(in);
			            file.aLine(in);
			        } 
		        }
		        if (rset!=null){ 
		            rset.close(); 
		            rset = null; 
		        } 
	        }
	          
	        lEnd = System.currentTimeMillis();
	        lFee = lEnd - lStart;
	        
	        in = "sql="+sql;
	        System.out.println(in);
	        file.aLine(in); 
	        
	        in = "不在索引字段上查询"+selecttime+"次数，每次查询"+selectnum+"条记录：总共花费时间："+lFee+"ms";
	        System.out.println(in);
	        file.aLine(in);

	        
	        /*===============================查询数据（用索引）========================*/
	        lStart = System.currentTimeMillis();
	        for(int i=0;i<selecttime;i++){ 
	        	int ii = rand.nextInt(10000);
	        	sql = "select id, name from person where id  between " + ii + " and " + (ii+selectnum-1);
		        rset = stmt.executeQuery(sql); 
		        if(showprint.equalsIgnoreCase("yes")){
			        while (rset.next()){ 
			        	in = "查询结果:"+rset.getInt("id")+"	"+rset.getString("name");
			            System.out.println(in);
			            file.aLine(in);
			        } 
		        }
		        if (rset!=null){ 
		            rset.close(); 
		            rset = null; 
		        } 
	        }
	        lEnd = System.currentTimeMillis();
	        lFee = lEnd - lStart;
	        in = "sql="+sql;
	        System.out.println(in);
	        file.aLine(in); 
	        
	        in = "在索引字段上查询"+selecttime+"次数，每次查询"+selectnum+"条记录：总共花费时间："+lFee+"ms";
	        System.out.println(in);
	        file.aLine(in);
	         
	    
	        /*===============================查询数据（排序，每次变化一下顺序）============*/
	        lStart = System.currentTimeMillis();
	        for(int i=0;i<ordertime;i++){
	        	sql = "select * from person order by id " ;
	        	if(i%2==0){
	        		sql += " desc ";
	        	}
		        rset = stmt.executeQuery(sql); 
		        if(showprint.equalsIgnoreCase("yes")){
			        while (rset.next()){ 
			            System.out.println("查询结果:"+rset.getInt("id")+"	"+rset.getString("name")); 
			        } 
		        }
		        if (rset!=null){ 
		            rset.close(); 
		            rset = null; 
		        } 
	        } 
	        lEnd = System.currentTimeMillis();
	        lFee = lEnd - lStart;
	        
	        in = "sql="+sql;
	        System.out.println(in);
	        file.aLine(in); 
	        
	        in = "对数据排序"+ordertime+"次，顺序一次一变，花费时间："+lFee+"ms";
	        System.out.println(in);
	        file.aLine(in);
	        
	        
	        
	        /*===============================查询数据（聚合）========================*/
	        lStart = System.currentTimeMillis();
	        int ii=rand.nextInt(10000);
	        for(int i=0;i<grouptime;i++){
	        	sql = "select sum(id),max(name),min(name3) from person where id between " + ii + " and " + ii + groupnum ;
	 
		        rset = stmt.executeQuery(sql); 
		        if(showprint.equalsIgnoreCase("yes")){
			        while (rset.next()){ 
			            System.out.println("查询结果:"+rset.getInt(1)+"	"+rset.getString(2)+"	"+rset.getString(3)); 
			        }
		        }
		        if (rset!=null){ 
		            rset.close(); 
		            rset = null; 
		        } 
	        }
	         
	        lEnd = System.currentTimeMillis();
	        lFee = lEnd - lStart; 
	        
	        in = "sql="+sql;
	        System.out.println(in);
	        file.aLine(in); 
	        
	        in = "对数据聚合"+grouptime+"次，每次聚合"+groupnum+"条数据，花费时间："+lFee+"ms";
	        System.out.println(in);
	        file.aLine(in);

	        /*===============================删除数据========================*/
	        lEnd = System.currentTimeMillis();
	        lFee = lEnd - lStart;
	        
	        lStart = System.currentTimeMillis(); 
	        for(int i=0;i<deletetime;i++){
	        	//System.out.println(iid);
	        	sql="delete from person where id <= " + (i+1)*deletenum;
	        	stmt.executeUpdate(sql);
	        	if(i%delcommit==0)
           	 	conn.commit();
	        }
	        conn.commit(); 
	        lEnd = System.currentTimeMillis();
	        lFee = lEnd - lStart;  

	        in = "sql="+sql;
	        System.out.println(in);
	        file.aLine(in);
	        
	        file.aLine(in);
	        in = "删除"+deletetime+"次，每次删除"+deletenum+"条数据，每"+delcommit+"条提交一次，花费时间："+lFee+"ms";
	        System.out.println(in);
	        file.aLine(in);
	          
        } catch(Exception e){ 
        	e.printStackTrace();
        	try{
        		conn.rollback(); 
        	}catch(SQLException se){
        		se.printStackTrace();
        	}
        	e.printStackTrace();
            System.exit(-1); 
        }
        finally { 
        	closeall();
        }
    }
    
    void closeall(){
    	try {  
    		
        	conn.rollback();
        	file.finish();
        	if (st!=null){ 
 	            st.close(); 
 	            st = null; 
 	        }   
            if (rset != null) rset.close(); 
            if (stmt != null) stmt.close(); 
            if (conn != null) conn.close(); 
        } catch (SQLException e) { System.out.println("SQLException in finally :" + e.getMessage()); 
        	System.exit(-1);
        }  
    }

	public static void main(String[] args) { 
		while(true){ 
			String sin = "";
			System.out.println("请录入1表示sqlite，2表示把hsql，3表示mysql，4表示oracle，5表示kingbase点回车键结束,录入exit或quit则退出程序");
			BufferedReader br=new BufferedReader(new InputStreamReader(System.in));
			try{
				sin = br.readLine();
			}catch(Exception ex){
				System.out.println(ex.getMessage());
			}
			TestSqlite conn = new TestSqlite();
			if(sin.equalsIgnoreCase("exit")||sin.equalsIgnoreCase("quit")) return ;
			conn.sGet = sin; 
		    conn.test(); 
		    /*Runtime.getRuntime().addShutdownHook(
		    		new Thread(){
		    			public void run(){ 
		    				System.out.println("强行退出");
		    			}  
		    		}
		    	); */
	    } 
	}
}