?
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("================================");
?
?}
?
}