- 单线程(单条循环)插入50000条记录：
每执行一次就要访问一次数据库
import java.sql.Connection; import java.sql.DriverManager; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; public class Test { public static void main(String[] args) { Connection conn; Statement stmt; ResultSet rs = null; String url = "jdbc:sqlserver://localhost:1433;DatabaseName=test;"; String sql = "insert into student(name,age) values('wang',12)"; try { // 连接数据库 conn = DriverManager.getConnection(url, "sa", "123456"); // 建立Statement对象 stmt = conn.createStatement(); /** * Statement createStatement() 创建一个 Statement 对象来将 SQL 语句发送到数据库。 */ // 执行数据库查询语句 long starttime=System.currentTimeMillis(); for(int i=0; i<50000;i++){ stmt.executeUpdate(sql); } long spendtime=System.currentTimeMillis()-starttime; System.out.println( "单线程批处理花费时间:"+spendtime); /** * ResultSet executeQuery(String sql) throws SQLException 执行给定的 SQL * 语句，该语句返回单个 ResultSet 对象 */ if (rs != null) { rs.close(); rs = null; } if (stmt != null) { stmt.close(); stmt = null; } if (conn != null) { conn.close(); conn = null; } } catch (SQLException e) { e.printStackTrace(); System.out.println("数据库连接失败"); } } }
import java.sql.Connection; import java.sql.DriverManager; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; public class Test { public static void main(String[] args) { Connection conn; Statement stmt; ResultSet rs = null; String url = "jdbc:sqlserver://localhost:1433;DatabaseName=test;"; String sql = "insert into student(name,age) values('wang',12)"; try { // 连接数据库 conn = DriverManager.getConnection(url, "sa", "123456"); // 建立Statement对象 stmt = conn.createStatement(); /** * Statement createStatement() 创建一个 Statement 对象来将 SQL 语句发送到数据库。 */ // 执行数据库查询语句 long starttime=System.currentTimeMillis(); for(int i=0; i<50000;i++){ stmt.addBatch("insert into student(name,age) values('wang',12)"); } stmt.executeBatch(); long spendtime=System.currentTimeMillis()-starttime; System.out.println( "单线程批处理花费时间:"+spendtime); /** * ResultSet executeQuery(String sql) throws SQLException 执行给定的 SQL * 语句，该语句返回单个 ResultSet 对象 */ if (rs != null) { rs.close(); rs = null; } if (stmt != null) { stmt.close(); stmt = null; } if (conn != null) { conn.close(); conn = null; } } catch (SQLException e) { e.printStackTrace(); System.out.println("数据库连接失败"); } } }
- 多线程(单条循环)插入50000条记录：
启动5个线程，每个线程插入10000条记录
import java.sql.Connection; import java.sql.DriverManager; import java.sql.SQLException; import java.sql.Statement; import java.util.UUID; import java.util.concurrent.CountDownLatch; public class InsertTest { private String url="jdbc:sqlserver://localhost:1433;DatabaseName=test;"; private String user="sa"; private String password="123456"; public Connection getConnect(){ Connection con = null; try { Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver"); con=DriverManager.getConnection(url, user, password); } catch (Exception e) { e.printStackTrace(); } return con; } public void multiThreadImport( final int ThreadNum){ final CountDownLatch cdl= new CountDownLatch(ThreadNum); long starttime=System.currentTimeMillis(); for(int k=1;k<=ThreadNum;k++){ new Thread(new Runnable() { @Override public void run() { Connection con=getConnect(); try { Statement st=con.createStatement(); for(int i=1;i<=10000;i++){ //st.addBatch("insert into student(name,age) values('wang',12)"); st.executeUpdate("insert into student(name,age) values('wang',12)"); } //st.executeBatch(); cdl.countDown(); } catch (Exception e) { }finally{ try { con.close(); } catch (SQLException e) { e.printStackTrace(); } } } }).start(); } try { cdl.await(); long spendtime=System.currentTimeMillis()-starttime; System.out.println( ThreadNum+"个线程花费时间:"+spendtime); } catch (InterruptedException e) { e.printStackTrace(); } } public static void main(String[] args) throws Exception { InsertTest ti=new InsertTest(); ti.multiThreadImport(5); }
- 多线程+批处理插入50000条记录：
启动5个线程
import java.sql.Connection; import java.sql.DriverManager; import java.sql.SQLException; import java.sql.Statement; import java.util.UUID; import java.util.concurrent.CountDownLatch; public class InsertTest { private String url="jdbc:sqlserver://localhost:1433;DatabaseName=test;"; private String user="sa"; private String password="Rfid123456"; public Connection getConnect(){ Connection con = null; try { Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver"); con=DriverManager.getConnection(url, user, password); } catch (Exception e) { e.printStackTrace(); } return con; } public void multiThreadImport( final int ThreadNum){ final CountDownLatch cdl= new CountDownLatch(ThreadNum);//定义线程数量 long starttime=System.currentTimeMillis(); for(int k=1;k<=ThreadNum;k++){ new Thread(new Runnable() { @Override public void run() { Connection con=getConnect(); try { Statement st=con.createStatement(); for(int i=1;i<=50000/ThreadNum;i++){ st.addBatch("insert into student(name,age) values('wang',12)"); if(i%500 == 0){ st.executeBatch(); } } cdl.countDown(); //执行完一个线程，递减1 } catch (Exception e) { }finally{ try { con.close(); } catch (SQLException e) { e.printStackTrace(); } } } }).start(); } try { cdl.await(); //前面线程没执行完，其他线程等待，不往下执行 long spendtime=System.currentTimeMillis()-starttime; System.out.println( ThreadNum+"个线程花费时间:"+spendtime); } catch (InterruptedException e) { e.printStackTrace(); } } public static void main(String[] args) throws Exception { InsertTest ti=new InsertTest(); ti.multiThreadImport(5); } }
