📄 benchtest.java
字号:
}
}
/**
* 10. Test
* Update rows with a PreparedStatement and Batch.
*/
static void test_UpdateRowsPrepareBatch(Connection con){
int batchSize = 10;
int batches = rowCount / batchSize;
System.out.println();
System.out.println( "Test update rows with PreparedStatement and Batches: " + batches + " batches, " + batchSize + " batch size");
try{
PreparedStatement pr = con.prepareStatement( "UPDATE " + tableName + " SET bi=?,c=?,d=?,de=?,f=?,im=?,i=?,m=?,n=?,r=?,sd=?,si=?,sm=?,sy=?,t=?,ti=?,vb=?,vc=? WHERE i=?" );
long time = -System.currentTimeMillis();
for (int i=0; i<batches; i++){
for (int r=0; r<batchSize; r++){
pr.setBytes ( 1, byteArray );
pr.setString( 2 , "Test" );
pr.setDate ( 3 , new Date( System.currentTimeMillis() ) );
pr.setFloat ( 4, (float)1234.56789 );
pr.setFloat ( 5 , (float)9876.54321 );
pr.setBytes ( 6, largeByteArray );
pr.setInt ( 7 , i*batchSize + r );
pr.setDouble( 8 , 23.45 );
pr.setDouble( 9 , 567.45 );
pr.setFloat ( 10 , (float)78.89 );
pr.setTime ( 11, new Time( System.currentTimeMillis() ) );
pr.setShort ( 12, (short)23456 );
pr.setFloat ( 13, (float)34.56 );
pr.setString( 14, "sysname (30) NULL" );
pr.setString( 15 , "text NULL" );
pr.setByte ( 16, (byte)28 );
pr.setBytes ( 17, byteArray );
pr.setString( 18, "varchar (255) NULL" );
pr.setInt ( 19 , i );
pr.addBatch();
}
int[] updateCount = pr.executeBatch();
if (updateCount.length != batchSize){
System.out.println( " Failed: Update count size should be " + batchSize + " but it is " + updateCount.length + ".");
return;
}
}
time += System.currentTimeMillis();
System.out.println( " Test time: " + time + " ms");
pr.close();
}catch(Exception e){
System.out.println(" Failed:"+e);
}finally{
System.out.println();
System.out.println("===================================================================");
}
}
/**
* 11. Test
* Scroll and call the getXXX methods for every columns.
*/
static void test_Scroll_getXXX(Connection con){
System.out.println();
System.out.println( "Test scroll and call the getXXX methods for every columns: " + rowCount + " rows");
try{
Statement st = con.createStatement();
long time = -System.currentTimeMillis();
ResultSet rs = st.executeQuery("SELECT * FROM " + tableName);
for (int i=0; i<rowCount; i++){
rs.next();
rs.getInt ( 1 );
rs.getBytes ( 2 );
rs.getString( 3 );
rs.getDate ( 4 );
rs.getFloat ( 5 );
rs.getFloat ( 6 );
rs.getBytes ( 7 );
rs.getInt ( 8 );
rs.getDouble( 9 );
rs.getDouble( 10 );
rs.getFloat ( 11 );
rs.getTime ( 12 );
rs.getShort ( 13 );
rs.getFloat ( 14 );
rs.getString( 15 );
rs.getString( 16 );
rs.getByte ( 17 );
rs.getBytes ( 18 );
rs.getString( 19 );
}
time += System.currentTimeMillis();
System.out.println( " Test time: " + time + " ms");
st.close();
}catch(Exception e){
System.out.println(" Failed:"+e);
}finally{
System.out.println();
System.out.println("===================================================================");
}
}
/**
* 12. Test
* Update large binary data.
*/
static void test_UpdateLargeBinary(Connection con){
System.out.println();
System.out.println( "Test update large binary data: " + rowCount + "KB bytes");
try{
java.io.FileOutputStream fos = new java.io.FileOutputStream(tableName+".bin");
byte bytes[] = new byte[1024];
for(int i=0; i<rowCount; i++){
fos.write(bytes);
}
fos.close();
java.io.FileInputStream fis = new java.io.FileInputStream(tableName+".bin");
long time = -System.currentTimeMillis();
PreparedStatement pr = con.prepareStatement("Update " + tableName + " set im=? WHERE pr=1");
pr.setBinaryStream( 1, fis, rowCount*1024 );
pr.execute();
pr.close();
time += System.currentTimeMillis();
System.out.println( " Test time: " + time + " ms");
fis.close();
java.io.File file = new java.io.File(tableName+".bin");
file.delete();
}catch(Exception e){
System.out.println(" Failed:"+e);
}finally{
System.out.println();
System.out.println("===================================================================");
}
}
/**
* 12. Test
* Update large binary data with a SP.
*/
static void test_UpdateLargeBinaryWithSP(Connection con){
System.out.println();
System.out.println( "Test update large binary data with a SP: " + rowCount + "KB bytes");
try{
java.io.FileOutputStream fos = new java.io.FileOutputStream(tableName+".bin");
byte bytes[] = new byte[1024];
for(int i=0; i<rowCount; i++){
fos.write(bytes);
}
fos.close();
java.io.FileInputStream fis = new java.io.FileInputStream(tableName+".bin");
long time = -System.currentTimeMillis();
Statement st = con.createStatement();
st.execute("CREATE PROCEDURE #UpdateLargeBinary(@im image) as Update " + tableName + " set im=@im WHERE pr=2");
PreparedStatement pr = con.prepareStatement("exec #UpdateLargeBinary ?");
pr.setBinaryStream( 1, fis, rowCount*1024 );
pr.execute();
st.execute("DROP PROCEDURE #UpdateLargeBinary");
st.close();
pr.close();
time += System.currentTimeMillis();
System.out.println( " Test time: " + time + " ms");
fis.close();
java.io.File file = new java.io.File(tableName+".bin");
file.delete();
}catch(Exception e){
System.out.println(" Failed:"+e);
}finally{
System.out.println();
System.out.println("===================================================================");
}
}
/**
* Create a new Table for testing
*/
static void createTestTable(Connection con) throws SQLException{
Statement st;
st = con.createStatement();
//delete old table
dropTestTable( con );
//create table
st.execute(
"CREATE TABLE " + tableName + " ("+
" pr numeric IDENTITY,"+
" bi binary (255) NULL ,"+
" c nchar (255) NULL ,"+
" d datetime NULL ,"+
" de decimal(18, 0) NULL ,"+
" f float NULL ,"+
" im image NULL ,"+
" i int NULL ,"+
" m money NULL ,"+
" n numeric(18, 0) NULL ,"+
" r real NULL ,"+
" sd smalldatetime NULL ,"+
" si smallint NULL ,"+
" sm smallmoney NULL ,"+
" sy sysname NULL ,"+
" t ntext NULL ,"+
" ti tinyint NULL ,"+
" vb varbinary (255) NULL ,"+
" vc nvarchar (255) NULL, "+
"CONSTRAINT PK_BenchTest2 PRIMARY KEY CLUSTERED (pr) "+
")");
st.close();
}
static void deleteTestTable(Connection con){
try{
Statement st = con.createStatement();
st.execute("DELETE FROM " + tableName);
st.close();
}catch(Exception e){/* ignore it */}
}
static void dropTestTable(Connection con){
try{
Statement st = con.createStatement();
st.execute("drop table " + tableName);
st.close();
}catch(Exception e){/* ignore it */}
}
// create test data after the insert test is failed
static void createTestDataWithClassicInsert(Connection con) throws SQLException{
String sql = "INSERT INTO " + tableName + "(bi,c,d,de,f,im,i,m,n,r,si,sd,sm,sy,t,ti,vb,vc) VALUES(0x172243,'Test','20010101',1234.56789,9876.54321,0x";
for(int i=0; i<largeByteArray.length; i++){
sql += "00";
}
Statement st = con.createStatement();
for (int i=0; i<rowCount; i++){
st.execute(sql + ","+i+",23.45,567.45,78.89,"+i+",'11:11:11',34.56,'sysname (30) NULL','ntext NULL, sample to save in the field',"+(i & 0xFF)+",0x172243,'nvarchar (255) NULL')" );
}
st.close();
}
}⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -