一、编写测试案例向MySQL数据库中插入百万条数据。测试数据表建表脚本如下：use db_xk;
|
drop table if exists tb_test2; create table tb_test2 ( id int primary key auto_increment, subject varchar(50) not null, description varchar(200) not null, teacher_id int(10) zerofill not null, student_id int(10) zerofill default null, state boolean not null default false );state boolean not null default false ); |
测试案例源码如下：
|
package test; import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.SQLException; import util.DBUtil; public class TestDataBase2 { public static void main(String[] args) { Connection conn = DBUtil.getConnection(); String sql = "insert into tb_test2(subject, description, teacher_id, student_id) values (?,?,?,?)"; try { PreparedStatement prep = conn.prepareStatement(sql); // 将连接的自动提交关闭，数据在传送到数据库的过程中相当耗时 conn.setAutoCommit(false); long start = System.currentTimeMillis(); for (int i = 0; i < 10; i++) { long start2 = System.currentTimeMillis(); // 一次性执行插入10万条数据 for (int j = 0; j < 100000; j++) { prep.setString(1, "test2"); prep.setString(2, "test3"); prep.setInt(3, 1234562); prep.setInt(4, 12354545); // 将预处理添加到批中 prep.addBatch(); } // 预处理批量执行 prep.executeBatch(); prep.clearBatch(); conn.commit(); long end2 = System.currentTimeMillis(); // 批量执行一次批量打印执行依次的时间 System.out.print("inner"+i+": "); System.out.println(end2 - start2); } long end = System.currentTimeMillis(); System.out.print("total: "); System.out.println(end - start); } catch (SQLException e) { e.printStackTrace(); } finally { DBUtil.close(conn); } } } |
最新内容请见作者的GitHub页：http://qaseven.github.io/
时间： 2024-11-03 22:28:23