**
关系型数据库**
1：利用****jdbctemplate
a：execute方法单个执行sql
JdbcTemplate jdbc = DBTemplateUtil.getDBTemplate();
for (int i = 0; i < 100000; i++) {
sb.append("insert into m_test_insert values ('"+i+"','name1_" + i + "','120kg','name2_" + i + "','4','5','6','7'"
+ ",'8','9','10','11','12','13','14','15','16','17','18','19','20')");
jdbc.execute(sb.toString());
}
//634540
b：batchupdate方法批量执行sql
for (int i = 0; i < 100000; i++) {
Object[] obj = new Object[21];
obj[0] = i + "";
obj[1] = "name1_" + i + "'";
obj[2] = "name2_" + i + "'";
obj[3] = "3";
obj[4] = "3";
obj[5] = "3";
obj[6] = "3";
obj[7] = "3";
obj[8] = "3";
obj[9] = "3";
obj[10] = "3";
obj[11] = "3";
obj[12] = "3";
obj[13] = "3";
obj[14] = "14";
obj[15] = "3";
obj[16] = "3";
obj[17] = "3";
obj[18] = "3";
obj[19] = "3";
obj[20] = "3";
dataSet.add(obj);
if ((i + 1) % 10000 == 0) {
jdbc.batchUpdate("insert into m_test_insert values(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)",dataSet);
}
}
jdbc.batchUpdate("insert into m_test_insert values(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)",dataSet);
//13000
c：拼接多values值插入
StringBuilder sb = new StringBuilder("insert into m_test_insert values");
for (int i = 0; i < 100000; i++) {
if (i != 0){
sb.append(",");
}
sb.append("('"+i+"','name1_" + i + "','120kg','name2_" + i + "','4','5','6','7'"
+ ",'8','9','10','11','12','13','14','15','16','17','18','19','20')");
}
jdbc.execute(sb.toString());
//24020
2：利用statement（关闭事务自动提交）
a：execute方法单个执行
JdbcTemplate jdbc = DBTemplateUtil.getDBTemplate();
DataSource ds = jdbc.getDataSource();
Connection connection = null;
Statement statement = null;
long begin = System.currentTimeMillis();
try {
connection = ds.getConnection();
connection.setAutoCommit(false);
statement = connection.createStatement();
for (int i = 0; i < 100000; i++) {
String sql = "insert into m_test_insert values('"+i+"','name1_" + i + "','120kg','name2_" + i + "','4','5','6','7'"
+ ",'8','9','10','11','12','13','14','15','16','17','18','19','20')";
statement.execute(sql);
}
connection.commit();
}
//493602
b：executeBatch方法
for (int i = 0; i < 100000; i++) {
String sql = "insert into m_test_insert values('"+i+"','name1_" + i + "','120kg','name2_" + i + "','4','5','6','7'"
+ ",'8','9','10','11','12','13','14','15','16','17','18','19','20')";
statement.addBatch(sql);
if ((i + 1) % 10000 == 0) {
statement.executeBatch();
statement.clearBatch();
connection.commit();
}
}
statement.executeBatch();
statement.clearBatch();
connection.commit();
//30332
c：拼接多values值插入
StringBuilder sb = new StringBuilder("insert into m_test_insert values");
for (int i = 0; i < 100000; i++) {
if (i != 0){
sb.append(",");
}
sb.append("('"+i+"','name1_" + i + "','120kg','name2_" + i + "','4','5','6','7'"
+ ",'8','9','10','11','12','13','14','15','16','17','18','19','20')");
}
long begin = System.currentTimeMillis();
Statement statement = connection.createStatement();
statement.execute(sb.toString());
connection.commit();
//29647
d：preparestatement利用executeBatch方法
for (int i = 0; i < 100000; i++) {
statement.setString(1, i + "");
statement.setString(2,"name1_" + i + "'");
statement.setString(3,"name2_" + i + "'");
statement.setString(4,"3");
statement.setString(5, "3");
statement.setString(6,"3");
statement.setString(7,"3");
statement.setString(8,"3");
statement.setString(9, "3");
statement.setString(10,"3");
statement.setString(11,"3");
statement.setString(12,"3");
statement.setString(13, "3");
statement.setString(14,"3");
statement.setString(15,"3");
statement.setString(16,"3");
statement.setString(17,"3");
statement.setString(18, "3");
statement.setString(19,"3");
statement.setString(20,"3");
statement.setString(21,"3");
statement.addBatch();
if ((i + 1) % 10000 == 0) {
statement.executeBatch();
statement.clearBatch();
connection.commit();
}
}
statement.executeBatch();
statement.clearBatch();
connection.commit();
//11909
分布式数据库
1：利用jdbc方式
a： execute单条执行
JdbcTemplate hjdbc = PhoenixTemplateUtil.getHBaseTemplate();
for (int i = 0; i < 10000; i++) {
String sql = "upsert into m_test_insert values('"+i+"','name1_" + i + "','120kg','name2_" + i + "','4','5','6','7'"
+ ",'8','9','10','11','12','13','14','15','16','17','18','19','20')";
hjdbc.execute(sql);
}
//72480
b：batchUpdate方法
JdbcTemplate hjdbc = PhoenixTemplateUtil.getHBaseTemplate();
final List<Object[]> dataSet = new ArrayList<>();
for (int i = 0; i < 10000; i++) {
Object[] obj = new Object[21];
obj[0] = i + "";
obj[1] = "name1_" + i + "'";
obj[2] = "name2_" + i + "'";
obj[3] = "3";
obj[4] = "3";
obj[5] = "3";
obj[6] = "3";
obj[7] = "3";
obj[8] = "3";
obj[9] = "3";
obj[10] = "3";
obj[11] = "3";
obj[12] = "3";
obj[13] = "3";
obj[14] = "14";
obj[15] = "3";
obj[16] = "3";
obj[17] = "3";
obj[18] = "3";
obj[19] = "3";
obj[20] = "3";
dataSet.add(obj);
}
long begin = System.currentTimeMillis();
hjdbc.batchUpdate("upsert into m_test_insert values(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)",new BatchPreparedStatementSetter() {
@Override
public void setValues(PreparedStatement ps, int i) throws SQLException {
Object[] obj = dataSet.get(i);
try{
ps.setString(1, obj[0].toString());
ps.setString(2,obj[1].toString());
ps.setString(3,obj[2].toString());
ps.setString(4,obj[3].toString());
ps.setString(5, obj[4].toString());
ps.setString(6,obj[5].toString());
ps.setString(7,obj[6].toString());
ps.setString(8,obj[7].toString());
ps.setString(9, obj[8].toString());
ps.setString(10,obj[9].toString());
ps.setString(11,obj[10].toString());
ps.setString(12,obj[11].toString());
ps.setString(13, obj[12].toString());
ps.setString(14,obj[13].toString());
ps.setString(15,obj[14].toString());
ps.setString(16,obj[15].toString());
ps.setString(17,obj[16].toString());
ps.setString(18, obj[17].toString());
ps.setString(19,obj[18].toString());
ps.setString(20,obj[19].toString());
ps.setString(21,obj[20].toString());
} catch (Exception e){
e.printStackTrace();
}
}
@Override
public int getBatchSize() {
return dataSet.size();
}
});
//66954
2：statement方式（关闭自动提交事务）
a:execute直接执行
Connection connection = ds.getConnection();
connection.setAutoCommit(false);
Statement statement = connection.createStatement();
long begin = System.currentTimeMillis();
for (int i = 0; i < 10000; i++) {
String sql = "upsert into m_test_insert values('"+i+"','name1_" + i + "','120kg','name2_" + i + "','4','5','6','7'"
+ ",'8','9','10','11','12','13','14','15','16','17','18','19','20')";
try {
statement.execute(sql);
} catch (Exception e) {
}
}
connection.commit();
//6452
b：executeBatch方法
connection = ds.getConnection();
connection.setAutoCommit(false);
statement = connection.createStatement();
for (int i = 0; i < 100000; i++) {
String sql = "upsert into m_test_insert values('"+i+"','name1_" + i + "','120kg','name2_" + i + "','4','5','6','7'"
+ ",'8','9','10','11','12','13','14','15','16','17','18','19','20')";
statement.addBatch(sql);
if ((i + 1) % 10000 == 0) {
statement.executeBatch();
statement.clearBatch();
connection.commit();
}
}
statement.executeBatch();
statement.clearBatch();
connection.commit();
//39324
c：preparestatement利用executeBatch 方法
connection = ds.getConnection();
connection.setAutoCommit(false);
statement = connection.prepareStatement("upsert into m_test_insert values(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)");
for (int i = 0; i < 100000; i++) {
statement.setString(1, i + "");
statement.setString(2,"name1_" + i + "'");
statement.setString(3,"name2_" + i + "'");
statement.setString(4,"3");
statement.setString(5, "3");
statement.setString(6,"3");
statement.setString(7,"3");
statement.setString(8,"3");
statement.setString(9, "3");
statement.setString(10,"3");
statement.setString(11,"3");
statement.setString(12,"3");
statement.setString(13, "3");
statement.setString(14,"3");
statement.setString(15,"3");
statement.setString(16,"3");
statement.setString(17,"3");
statement.setString(18, "3");
statement.setString(19,"3");
statement.setString(20,"3");
statement.setString(21,"3");
statement.addBatch();
if ((i + 1) % 10000 == 0) {
statement.executeBatch();
statement.clearBatch();
connection.commit();
}
}
statement.executeBatch();
statement.clearBatch();
connection.commit();
//31809
**
**
**
**
