driverclasss oracle,JDBC连接mysql、sqlserver、oracle数据库示例，含代码

【JDBC 连接 mysql】 注意：mysql默认关闭了batch处理，只有把rewriteBatchedStatements参数置为true, 驱动才会批量执行SQL；同时，选项对INSERT/UPDATE/DELETE都有效，只不过对INSERT它为会预先重排一下SQL语句；另外，当batchSize <= 3时，驱动会宁愿一条一条地执行SQL；所以，如果你想验证rewriteBatchedStatements在你的系统里是否已经生效，记得要使用较大的batch。

/** JDBC connect to Mysql **/

try

{

Class.forName("com.mysql.jdbc.Driver").newInstance();

String url="jdbc:mysql://192.168.190.**:3306/reuri?rewriteBatchedStatements=true";

String user="root";

String password="123qwe";

Connection conn= DriverManager.getConnection(url,user,password);

conn.setAutoCommit(false);

/**

when stmt_type equals 1,produce datas by PreparedStatement class.

when stmt_type equals 2,produce datas by Statement class.

while the sql is called repeatedly, PreparedStatement class will be more ineffective.

84 minutes produce 5000,000 records by useing Statement class;

70.33 seconds produce 5000,000 records by useing PreparedStatement class when bachnum is 1000;

81.50 seconds produce 5000,000 records by useing PreparedStatement class when bachnum is 10000;

67.28 seconds produce 5000,000 records by useing PreparedStatement class when bachnum is 50000;

68.91 seconds produce 5000,000 records by useing PreparedStatement class when bachnum is 100000;

*/

intstmt_type=1;

long start_time = System.currentTimeMillis();

if (stmt_type==1)

{

String pbase_sql="insert into IVR_MENU_INFO (IVR_MENU_ID,CALLER_NUMBER,CALLED_NUMBER,SSP_ID,IVR_MENU_TYPE_CODE,CHANGE_CHOICE)"

+"values (?,?,?,?,?,?)";

PreparedStatement prepared_stmt=conn.prepareStatement(pbase_sql);

String str_[]={",'good'",",'great'",",'wonderful'",",'awesome'",",'fantastic'",",'beautiful'",",'perfect'",",'terrific'"};

for(int i=1;i<=5000000;i++){

int index =(int)(Math.random()*str_.length);

prepared_stmt.setInt(1, i);

prepared_stmt.setString(2, "10");

prepared_stmt.setString(3, "8");

prepared_stmt.setInt(4, i);

prepared_stmt.setString(5, str_[index]);

prepared_stmt.setString(6, "1");

prepared_stmt.addBatch();

if(i%100000==0){

prepared_stmt.executeBatch();

conn.commit();

System.out.println("正在执行第"+i+"条记录！");

}

}

prepared_stmt.close();

}else if(stmt_type==2){

Statement stmt=conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_UPDATABLE);

String base_sql="insert into IVR_MENU_INFO (IVR_MENU_ID,CALLER_NUMBER,CALLED_NUMBER,SSP_ID,IVR_MENU_TYPE_CODE,CHANGE_CHOICE) values (";

String str[]={",'good'",",'great'",",'wonderful'",",'awesome'",",'fantastic'",",'beautiful'",",'perfect'",",'terrific'"};

for(int i=1;i<=5000000;i++){

String insert_sql="";

int index =(int)(Math.random()*str.length);

insert_sql=base_sql+i+",'10'"+",'8',"+i+str[index]+",'1')";

stmt.addBatch(insert_sql);

if(i%1000==0){

stmt.executeBatch();

conn.commit();

System.out.println("正在执行第"+i+"条记录！");

}

}

stmt.close();

}

System.out.println("开始时间："+start_time);

System.out.println("结束时间："+System.currentTimeMillis());

conn.close();

}

catch(Exception e)

{

out.println(e);

}

【JDBC 连接 sqlserver】

/** JDBC connect to sqlserver **/

try

{

Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver").newInstance();

String url="jdbc:sqlserver://192.168.***.3:1433; DatabaseName=aaa";

String user="sa";

String password="cailiqiang";

Connection conn= DriverManager.getConnection(url,user,password);

conn.setAutoCommit(false);

long start_time = System.currentTimeMillis();

/**

55.54 seconds produce 1000,000 records by useing PreparedStatement class when bachnum is 1000;

46.49 seconds produce 1000,000 records by useing PreparedStatement class when bachnum is 10000;

45.78 seconds produce 1000,000 records by useing PreparedStatement class when bachnum is 50000;

45.78 seconds produce 1000,000 records by useing PreparedStatement class when bachnum is 100000;

**/

intstmt_type=1;

if (stmt_type==1)

{

String pbase_sql="insert into IVR_MENU_INFO (IVR_MENU_ID,CALLER_NUMBER,CALLED_NUMBER,SSP_ID,IVR_MENU_TYPE_CODE,CHANGE_CHOICE)"

+"values (?,?,?,?,?,?)";

PreparedStatement prepared_stmt=conn.prepareStatement(pbase_sql);

String str_[]={",'good'",",'great'",",'wonderful'",",'awesome'",",'fantastic'",",'beautiful'",",'perfect'",",'terrific'"};

for(int i=1;i<=1000000;i++){

int index =(int)(Math.random()*str_.length);

prepared_stmt.setInt(1, i);

prepared_stmt.setString(2, "10");

prepared_stmt.setString(3, "8");

prepared_stmt.setInt(4, i);

prepared_stmt.setString(5, str_[index]);

prepared_stmt.setString(6, "1");

prepared_stmt.addBatch();

if(i%1000==0){

prepared_stmt.executeBatch();

conn.commit();

System.out.println("正在执行第"+i+"条记录！");

}

}

prepared_stmt.close();

}else if(stmt_type==2){

Statement stmt=conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_UPDATABLE);

String base_sql="insert into IVR_MENU_INFO (IVR_MENU_ID,CALLER_NUMBER,CALLED_NUMBER,SSP_ID,IVR_MENU_TYPE_CODE,CHANGE_CHOICE) values (";

String str[]={",'good'",",'great'",",'wonderful'",",'awesome'",",'fantastic'",",'beautiful'",",'perfect'",",'terrific'"};

for(int i=1;i<=5000000;i++){

String insert_sql="";

int index =(int)(Math.random()*str.length);

insert_sql=base_sql+i+",'10'"+",'8',"+i+str[index]+",'1')";

stmt.addBatch(insert_sql);

if(i%1000==0){

stmt.executeBatch();

conn.commit();

System.out.println("正在执行第"+i+"条记录！");

}

}

stmt.close();

}

System.out.println("开始时间："+start_time);

System.out.println("结束时间："+System.currentTimeMillis());

conn.close();

}

catch(Exception e)

{

out.println(e);

}

【JDBC 连接 oracle】

/** JDBC connect to Oracle **/

try

{

Class.forName("oracle.jdbc.driver.OracleDriver").newInstance();

String url="jdbc:oracle:thin:@192.168.190.***:1521:****";

String user="G12ETEST";

String password="test123";

Connection conn= DriverManager.getConnection(url,user,password);

conn.setAutoCommit(false);

/**

161.80 seconds produce 1000,000 records by useing PreparedStatement class when bachnum is 1000;

101.05 seconds produce 1000,000 records by useing PreparedStatement class when bachnum is 10000;

110.84 seconds produce 1000,000 records by useing PreparedStatement class when bachnum is 50000;

207.16 seconds produce 1000,000 records by useing PreparedStatement class when bachnum is 100000;

**/

long start_time = System.currentTimeMillis();

intstmt_type=1;

if (stmt_type==1)

{

String base_sql_prepared="insert into IVR_MENU_INFO (IVR_MENU_ID,CALLER_NUMBER,CALLED_NUMBER,SSP_ID,IVR_MENU_TYPE_CODE,CHANGE_CHOICE)"

+"values (?,?,?,?,?,?)";

PreparedStatement insertStat = conn.prepareStatement(base_sql_prepared);

String str_[]={"good","great","wonderful","awesome","fantastic","beautiful","perfect","terrific"};

for(int i=1;i<=1000000;i++){

int index =(int)(Math.random()*str_.length);

insertStat.setInt(1, i);

insertStat.setString(2, "10");

insertStat.setString(3, "8");

insertStat.setInt(4, i);

insertStat.setString(5, str_[index]);

insertStat.setString(6, "1");

insertStat.addBatch();

if(i%100000==0){

insertStat.executeBatch();

conn.commit();

System.out.println("正在执行第"+i+"条记录！");

}

}

insertStat.close();

}else if(stmt_type==2){

Statement stmt=conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_UPDATABLE);

String base_sql="insert into IVR_MENU_INFO (IVR_MENU_ID,CALLER_NUMBER,CALLED_NUMBER,SSP_ID,IVR_MENU_TYPE_CODE,CHANGE_CHOICE) values (";

String str[]={",'good'",",'great'",",'wonderful'",",'awesome'",",'fantastic'",",'beautiful'",",'perfect'",",'terrific'"};

for(int i=1;i<=5000000;i++){

String insert_sql="";

int index =(int)(Math.random()*str.length);

insert_sql=base_sql+i+",'10'"+",'8',"+i+str[index]+",'1')";

stmt.addBatch(insert_sql);

if(i%1000==0){

stmt.executeBatch();

conn.commit();

System.out.println("正在执行第"+i+"条记录！");

}

}

stmt.close();

}

System.out.println("开始时间："+start_time);

System.out.println("结束时间："+System.currentTimeMillis());

conn.close();

}

catch(Exception e)

{

out.println(e);

}