PrepareStatement和Batch性能

 

package test;

 

import java.sql.Connection;

import java.sql.PreparedStatement;

import java.sql.SQLException;

import java.sql.Statement;

 

import com.fastbo.util.Clock;

import com.fastbo.util.ConnectionFactory;

import com.fastbo.util.DbUtil;

 

/**

 * Description: Jdbc相关性能测试,batch处理，PrepareStatement，Statement等。

 * 

 * <p>

 * Mysql数据库：表结构为简单的id,name(varchar:255),type(varchar:255)字段，id自增

 * </p>

 * 

 * @author Peter Wei Email: <a href="mailto:weigbo@163.com">weigbo@163.com </a>

 * 

 * @version 1.0 2010-8-21

 */

public class JdbcTest {

 

 /**

  * 测试数据量

  */

 public static int TEST_NUM = 10000;

 

 /**

  * 批处理大小

  */

 public static int BATCH_SIZE = 300;

 

 /**

  * 清空数据表

  * 

  * @param con

  */

 public static void clear(Connection con) {

  PreparedStatement ps = null;

  StringBuffer buff = new StringBuffer();

  try {

   buff.append("truncate table bobo");

   ps = con.prepareStatement(buff.toString());

   ps.executeUpdate();

   System.out.println("清空表");

  } catch (SQLException e) {

   e.printStackTrace();

  } finally {

   DbUtil.close(ps);

  }

 }

 

 /**

  * 普通的Statement插入数据

  * 

  * @param con

  */

 public static int add(Connection con) {

  Statement stmt = null;

  int num = 0;

  String sql = "insert into bobo(name,type) values('Peter Wei','test')";

  try {

   stmt = con.createStatement();

   for (int i = 0; i < TEST_NUM; i++) {

    num += stmt.executeUpdate(sql);

   }

   System.out.println("插入数据量：" + num);

  } catch (SQLException e) {

   e.printStackTrace();

  } finally {

   DbUtil.close(stmt);

  }

  return num;

 

 }

 

 /**

  * 用PrepareStatement插入数据

  * 

  * @param con

  */

 public static void addByPrepareStatement(Connection con) {

 

  PreparedStatement ps = null;

  StringBuffer buff = new StringBuffer();

  int num = 0;

  try {

   buff.append("insert into bobo(name,type)");

   buff.append(" values(?,?)");

   ps = con.prepareStatement(buff.toString());

   con.setAutoCommit(false);

   for (int i = 0; i < TEST_NUM; i++) {

int index = 1;

ps.setString(index++, "Peter Wei");

ps.setString(index++, "test");

num += ps.executeUpdate();

   }

   con.commit();

   con.setAutoCommit(true);

   System.out.println("插入数据量：" + num);

  } catch (SQLException e) {

   e.printStackTrace();

  } finally {

   DbUtil.close(ps);

  }

 }

 

 /**

  * 用批处理插入数据

  * 

  * @param con

  */

 public static void addByBatch(Connection con) {

  PreparedStatement ps = null;

  StringBuffer buff = new StringBuffer();

  int sum = 0;

  int[] num = null;

  try {

   buff.append("insert into bobo(name,type) values(?,?)");

 

   con.setAutoCommit(false);

   ps = con.prepareStatement(buff.toString());

   for (int i = 0; i < TEST_NUM; i++) {

    int index = 1;

    ps.setString(index++, "Peter Wei");

    ps.setString(index++, "test");

    ps.addBatch();

    if (i != 0 && i % BATCH_SIZE == 0) {

     num = ps.executeBatch();

     sum += num.length;

     con.commit();

     // System.out.println("batch:" + i);

    }

 

   }

   num = ps.executeBatch();

   sum += num.length;

   con.commit();

   con.setAutoCommit(true);

   System.out.println("批量更新成功 " + sum + " 条记录!");

  } catch (SQLException e) {

   e.printStackTrace();

  } finally {

   DbUtil.close(ps);

  }

 }

 

 public static void main(String[] args) {

  Connection con = ConnectionFactory.getConnection();

  clear(con);

  Clock c = new Clock();

  // 普通的Statement插入数据

  System.out.println("普通的Statement插入数据:");

  c.start();

  add(con);

  c.stop();

  System.out.println(c.toString());

  c.readMilli();

  System.out.println(c.read());

  System.out.println("================================");

 

  clear(con);

  // 通过PrepareStatement插入数据

  System.out.println("通过PrepareStatement插入数据:");

  c = new Clock();

  c.start();

  addByPrepareStatement(con);

  c.stop();

  System.out.println(c.toString());

  c.readMilli();

  System.out.println(c.read());

  System.out.println("================================");

 

  clear(con);

  // 用批处理插入数据

  System.out.println("用批处理插入数据:");

  c = new Clock();

  c.start();

  addByBatch(con);

  c.stop();

  System.out.println(c.toString());

  c.readMilli();

  System.out.println(c.read());

  System.out.println("================================");

 

 }

 

}