2020-11-29

JDBC-2 Statement和PreparedStatement效率测试

package com.day1;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Statement;

import org.junit.Test;

public class StmtAndPsTest {
	// 测试Statement
	// 事务手动提交
	// 不使用批处理
	// 时间:5521 5792 4960
	@Test
	public void test1() {

		Connection conn = null;
		Statement stmt = null;
		try {
			conn = GetConnection.getConn();
			stmt = conn.createStatement();
			conn.setAutoCommit(false);

			long start = System.currentTimeMillis();
			stmt = conn.createStatement();
			String sql = null;
			for (int i = 1; i <= 10000; i++) {
				sql = "insert into stus(id,name) values(" + i + ",'tom" + i + "')";
				stmt.execute(sql);
			}
			conn.commit();
			long end = System.currentTimeMillis();

			System.out.println("共耗时" + (end - start) + "毫秒");

		} catch (Exception e) {
			try {
				conn.rollback();
			} catch (SQLException e1) {
				e1.printStackTrace();
			}
			e.printStackTrace();
		} finally {
			GetConnection.close(stmt, conn);
		}

	}

	// 测试PreparedStatement
	// 事务手动提交
	// 不使用批处理
	// 时间:2422 2089 1995
	@Test
	public void test2() {
		Connection conn = null;
		PreparedStatement ps = null;
		try {
			conn = GetConnection.getConn();
			conn.setAutoCommit(false);
			long start = System.currentTimeMillis();
			String sql = "insert into stus(id,name) values(?,?)";
			ps = conn.prepareStatement(sql);
			for (int i = 1; i <= 10000; i++) {
				ps.setLong(1, i);
				ps.setString(2, "tom" + i);
				ps.execute();
			}
			conn.commit();
			long end = System.currentTimeMillis();
			System.out.println("共耗时" + (end - start) + "毫秒");

		} catch (Exception e) {
			try {
				conn.rollback();
			} catch (SQLException e1) {
				e1.printStackTrace();
			}
			e.printStackTrace();
		} finally {
			try {
				if (ps != null)
					ps.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
			try {
				if (conn != null)
					conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}

	}

	// 测试Statement
	// 事务手动提交
	// 使用批处理
	// 时间:2172 2132 2152
	@Test
	public void test3() {

		Connection conn = null;
		Statement stmt = null;
		try {
			conn = GetConnection.getConn();
			stmt = conn.createStatement();
			conn.setAutoCommit(false);

			long start = System.currentTimeMillis();
			stmt = conn.createStatement();
			String sql = null;
			for (int i = 1; i <= 10000; i++) {
				sql = "insert into stus(id,name) values(" + i + ",'tom" + i + "')";
				stmt.addBatch(sql);
				if (i % 500 == 0) {
					stmt.executeBatch();
				}
			}
			stmt.executeBatch();
			conn.commit();
			long end = System.currentTimeMillis();

			System.out.println("共耗时" + (end - start) + "毫秒");

		} catch (Exception e) {
			try {
				conn.rollback();
			} catch (SQLException e1) {
				e1.printStackTrace();
			}
			e.printStackTrace();
		} finally {
			GetConnection.close(stmt, conn);
		}

	}

	// 测试PreparedStatement
	// 事务手动提交
	// 使用批处理
	// 时间:164 123 154 每700条执行一次批处理
	// 时间:198 155 185 每1000条执行一次批处理
	@Test
	public void test4() {

		Connection conn = null;
		PreparedStatement ps = null;
		try {
			conn = GetConnection.getConn();

			conn.setAutoCommit(false);

			long start = System.currentTimeMillis();
			String sql = "insert into stus(id,name) values(?,?)";
			ps = conn.prepareStatement(sql);
			for (int i = 1; i <= 10000; i++) {
				ps.setLong(1, i);
				ps.setString(2, "tom" + i);

				ps.addBatch();
				if (i % 1000 == 0) {
					ps.executeBatch();
				}
			}
			ps.executeBatch();
			conn.commit();
			long end = System.currentTimeMillis();
			System.out.println("共耗时" + (end - start) + "毫秒");

		} catch (Exception e) {
			try {
				conn.rollback();
			} catch (SQLException e1) {
				e1.printStackTrace();
			}
			e.printStackTrace();
		} finally {
			try {
				if (ps != null)
					ps.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
			try {
				if (conn != null)
					conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}

	}

}

最终结果是:PreparedStatement（预处理）和批处理一起使用时效率最高。
我们下期再见!