测试代码
package com.qiyibaba.obdemo;
import sun.misc.BASE64Encoder;
import java.io.UnsupportedEncodingException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class MainTest {
public static void main1(String[] args) throws ClassNotFoundException, SQLException, InterruptedException {
Connection conn = null;
Statement stmt = null;
// String DB_URL="jdbc:mysql://localhost:3306/test?characterEncoding=UTF-8&useSSL=false&socketTimeout=2000";
String DB_URL="jdbc:mysql://localhost:3306/test?characterEncoding=UTF-8&useSSL=false";
// 注册 JDBC 驱动
Class.forName("com.mysql.jdbc.Driver");
// 打开链接
System.out.println("连接数据库...");
conn = DriverManager.getConnection(DB_URL,"test@mysql","test");
// int i=0;
// try {
// while (i++ < 100) {
// // 执行查询
// PreparedStatement preStmt = conn.prepareStatement("select now() as now,sleep(3)");
//
// ResultSet rs = preStmt.executeQuery();
// while (rs.next()) {
// System.out.println(rs.getString("now"));
// }
// preStmt.close();
// Thread.sleep(200);
// // conn.commit();
// }
// }catch(Exception e){
// System.out.println(e);
// }
// System.out.println("Run again......");
// // 执行查询
// PreparedStatement preStmt = conn.prepareStatement("select now() as now");
//
// ResultSet rs = preStmt.executeQuery();
// while (rs.next()) {
// System.out.println(rs.getString("now"));
// }
// preStmt.close();
PreparedStatement preStmt = conn.prepareStatement("insert into test.ta values (?,?) ");
System.out.println(preStmt);
preStmt.setInt(1,2);
preStmt.setString(2,"abc'b");
preStmt.execute();
preStmt.close();
conn.close();
}
public static void main(String[] args) throws SQLException, ClassNotFoundException {
// String omsUser="admin";
// String omsPassword="admin";
// byte[] b= new byte[0];
// try {
// b = (omsUser + ":" + omsPassword).getBytes("utf-8");
// } catch (UnsupportedEncodingException e) {
// e.printStackTrace();
// }
// String basicString = new BASE64Encoder().encode(b);
// // Map<String, String> headers = new HashMap<>();
// // headers.put("Authorization", "Basic " + basicString);
// System.out.println(basicString);
mainTest2(100000,1000);
}
public static void mainTest2(int totalSize, int batchSize) throws SQLException, ClassNotFoundException {
Connection conn = null;
Statement stmt = null;
// &useCursorFetch=true
String DB_URL="jdbc:oceanbase://11.238.145.180:2883/sys?rewriteBatchedStatements=true";
Class.forName("com.alipay.oceanbase.jdbc.Driver");
System.out.println("连接数据库..." + DB_URL);
conn = DriverManager.getConnection(DB_URL,"sys@oboracle#ob312","1qaz@WSX3edc$RFV");
long start = System.currentTimeMillis();
try {
conn.setAutoCommit(false);
PreparedStatement preparedStatement = conn.prepareStatement("insert into sbtest1(c01,c02,c03,c04,c05,c06,c07,c08,c09,c10,c11,c12,c13,c14,c15,c16,c17,c18,c19,c20,c21,c22,c23,c24,c25,c26,c27,c28,c29,c30,c31,c32,c33,c34,c35,c36,c37,c38,c39,c40,c41,c42,c43,c44,c45,c46,c47,c48,c49,c50,c51,c52,c53,c54,c55,c56,c57,c58,c59,c60,c61,c62,c63,c64,c65,c66,c67,c68,c69,c70,c71,c72,c73,c74,c75,c76,c77,c78,c79) " +
"values (sbtest_id.nextval,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)");
int cycle = totalSize / batchSize;
for (int j = 0; j < cycle; j++) {
for (int i = 1; i <= batchSize; i++) {
int m = 0;
while (m++ < 78){
if (m % 2 == 1){
preparedStatement.setString(m, "ABCDEFGHIJ");
}else
{
preparedStatement.setInt(m, (int) (Math.random() * 100000000));
}
}
preparedStatement.addBatch();
}
preparedStatement.executeBatch();
conn.commit();
if (j % 100 == 0) {
System.out.println("commit " + j + " times,total need " + cycle + " times");
}
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
conn.commit();
conn.close();
}
long end = System.currentTimeMillis();
System.out.println("insert [" + totalSize + "," + batchSize + "] lines total cost " + (end - start) + "ms");
}
public static void mainTest(int totalSize, int batchSize) throws SQLException, ClassNotFoundException {
Connection conn = null;
Statement stmt = null;
// String DB_URL="jdbc:mysql://localhost:3306/test?characterEncoding=UTF-8&useSSL=false&socketTimeout=2000";
String DB_URL="jdbc:oceanbase://11.238.145.180:2883/sys?rewriteBatchedStatements=true&useCursorFetch=true";
// String DB_URL="jdbc:oceanbase://11.238.145.180:2883/sys?rewriteBatchedStatements=true&useCursorFetch=true";
// 注册 JDBC 驱动
Class.forName("com.alipay.oceanbase.jdbc.Driver");
// 打开链接
System.out.println("连接数据库...");
conn = DriverManager.getConnection(DB_URL,"sys@oboracle#ob312","1qaz@WSX3edc$RFV");
long start = System.currentTimeMillis();
try {
conn.setAutoCommit(false);
PreparedStatement preparedStatement = conn.prepareStatement("insert into sbtest(id,k,c,pad) values (sbtest_id.nextval,?,?,?)");
int cycle = totalSize / batchSize;
for (int j = 0; j < cycle; j++) {
for (int i = 1; i <= batchSize; i++) {
// preparedStatement.setInt(1, batchSize * j + i);
preparedStatement.setInt(1, (int) (Math.random() * 100000000));
preparedStatement.setString(2, "ABCDEFGHIJKLMNOPQRSTUVWXYZ-ABCDEFGHIJKLMNOPQRSTUVWXYZ-ABCDEFGHIJKLMNOPQRSTUVWXYZ-ABCDEFGHIJKLMNOPQRSTUVWXYZ");
preparedStatement.setString(3, "ABCDEFGHIJKLMNOPQRSTUVWXYZ-ABCDEFGHIJKLMNOPQRSTUVWXYZ");
preparedStatement.addBatch();
}
preparedStatement.executeBatch();
conn.commit();
if (j % 100 == 0) {
System.out.println("commit " + j + " times,total need " + cycle + " times");
}
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
conn.commit();
conn.close();
}
long end = System.currentTimeMillis();
System.out.println("insert [" + totalSize + "," + batchSize + "] lines total cost " + (end - start) + "ms");
}
}
/*
create sequence sbtest_id
minvalue 1
maxvalue 999999999999
start with 10000
increment by 1
cache 2000;
CREATE TABLE "SBTEST" (
"ID" NUMBER(38) NOT NULL ENABLE,
"K" NUMBER(38) DEFAULT '0' NOT NULL ENABLE,
"C" CHAR(120) DEFAULT '' NOT NULL ENABLE,
"PAD" VARCHAR2(90) DEFAULT NULL,
CONSTRAINT "SBTEST_OBPK_1642444381559230" PRIMARY KEY ("ID")
);
CREATE TABLE "SBTEST1" (
"C01" NUMBER(10,2) NOT NULL ENABLE,
"C02" CHAR(10) DEFAULT '' NOT NULL ENABLE,
"C03" NUMBER(10,2) NOT NULL ENABLE,
"C04" CHAR(10) DEFAULT '' NOT NULL ENABLE,
"C05" NUMBER(10,2) NOT NULL ENABLE,
"C06" CHAR(10) DEFAULT '' NOT NULL ENABLE,
"C07" NUMBER(10,2) NOT NULL ENABLE,
"C08" CHAR(10) DEFAULT '' NOT NULL ENABLE,
"C09" NUMBER(10,2) NOT NULL ENABLE,
"C10" CHAR(10) DEFAULT '' NOT NULL ENABLE,
"C11" NUMBER(10,2) NOT NULL ENABLE,
"C12" CHAR(10) DEFAULT '' NOT NULL ENABLE,
"C13" NUMBER(10,2) NOT NULL ENABLE,
"C14" CHAR(10) DEFAULT '' NOT NULL ENABLE,
"C15" NUMBER(10,2) NOT NULL ENABLE,
"C16" CHAR(10) DEFAULT '' NOT NULL ENABLE,
"C17" NUMBER(10,2) NOT NULL ENABLE,
"C18" CHAR(10) DEFAULT '' NOT NULL ENABLE,
"C19" NUMBER(10,2) NOT NULL ENABLE,
"C20" CHAR(10) DEFAULT '' NOT NULL ENABLE,
"C21" NUMBER(10,2) NOT NULL ENABLE,
"C22" CHAR(10) DEFAULT '' NOT NULL ENABLE,
"C23" NUMBER(10,2) NOT NULL ENABLE,
"C24" CHAR(10) DEFAULT '' NOT NULL ENABLE,
"C25" NUMBER(10,2) NOT NULL ENABLE,
"C26" CHAR(10) DEFAULT '' NOT NULL ENABLE,
"C27" NUMBER(10,2) NOT NULL ENABLE,
"C28" CHAR(10) DEFAULT '' NOT NULL ENABLE,
"C29" NUMBER(10,2) NOT NULL ENABLE,
"C30" CHAR(10) DEFAULT '' NOT NULL ENABLE,
"C31" NUMBER(10,2) NOT NULL ENABLE,
"C32" CHAR(10) DEFAULT '' NOT NULL ENABLE,
"C33" NUMBER(10,2) NOT NULL ENABLE,
"C34" CHAR(10) DEFAULT '' NOT NULL ENABLE,
"C35" NUMBER(10,2) NOT NULL ENABLE,
"C36" CHAR(10) DEFAULT '' NOT NULL ENABLE,
"C37" NUMBER(10,2) NOT NULL ENABLE,
"C38" CHAR(10) DEFAULT '' NOT NULL ENABLE,
"C39" NUMBER(10,2) NOT NULL ENABLE,
"C40" CHAR(10) DEFAULT '' NOT NULL ENABLE,
"C41" NUMBER(10,2) NOT NULL ENABLE,
"C42" CHAR(10) DEFAULT '' NOT NULL ENABLE,
"C43" NUMBER(10,2) NOT NULL ENABLE,
"C44" CHAR(10) DEFAULT '' NOT NULL ENABLE,
"C45" NUMBER(10,2) NOT NULL ENABLE,
"C46" CHAR(10) DEFAULT '' NOT NULL ENABLE,
"C47" NUMBER(10,2) NOT NULL ENABLE,
"C48" CHAR(10) DEFAULT '' NOT NULL ENABLE,
"C49" NUMBER(10,2) NOT NULL ENABLE,
"C50" CHAR(10) DEFAULT '' NOT NULL ENABLE,
"C51" NUMBER(10,2) NOT NULL ENABLE,
"C52" CHAR(10) DEFAULT '' NOT NULL ENABLE,
"C53" NUMBER(10,2) NOT NULL ENABLE,
"C54" CHAR(10) DEFAULT '' NOT NULL ENABLE,
"C55" NUMBER(10,2) NOT NULL ENABLE,
"C56" CHAR(10) DEFAULT '' NOT NULL ENABLE,
"C57" NUMBER(10,2) NOT NULL ENABLE,
"C58" CHAR(10) DEFAULT '' NOT NULL ENABLE,
"C59" NUMBER(10,2) NOT NULL ENABLE,
"C60" CHAR(10) DEFAULT '' NOT NULL ENABLE,
"C61" NUMBER(10,2) NOT NULL ENABLE,
"C62" CHAR(10) DEFAULT '' NOT NULL ENABLE,
"C63" NUMBER(10,2) NOT NULL ENABLE,
"C64" CHAR(10) DEFAULT '' NOT NULL ENABLE,
"C65" NUMBER(10,2) NOT NULL ENABLE,
"C66" CHAR(10) DEFAULT '' NOT NULL ENABLE,
"C67" NUMBER(10,2) NOT NULL ENABLE,
"C68" CHAR(10) DEFAULT '' NOT NULL ENABLE,
"C69" NUMBER(10,2) NOT NULL ENABLE,
"C70" CHAR(10) DEFAULT '' NOT NULL ENABLE,
"C71" NUMBER(10,2) NOT NULL ENABLE,
"C72" CHAR(10) DEFAULT '' NOT NULL ENABLE,
"C73" NUMBER(10,2) NOT NULL ENABLE,
"C74" CHAR(10) DEFAULT '' NOT NULL ENABLE,
"C75" NUMBER(10,2) NOT NULL ENABLE,
"C76" CHAR(10) DEFAULT '' NOT NULL ENABLE,
"C77" NUMBER(10,2) NOT NULL ENABLE,
"C78" CHAR(10) DEFAULT '' NOT NULL ENABLE,
"C79" NUMBER(10,2) NOT NULL ENABLE
);
* */
