jdbc 更新 测 并发

编程技术  /  houtizong 发布于 3年前   95

用到 junitperf-1.9.1.jar 这个包

用到 junit4

 

package com.bingfa;import java.sql.Connection;public final class JDBCUtils {private JDBCUtils() {}private static String url = "jdbc:mysql://localhost:3306/test";private static String user = "root";private static String password = "root";static {try {Class.forName("com.mysql.jdbc.Driver");} catch (ClassNotFoundException e) {throw new ExceptionInInitializerError(e);}}public static Connection getConnection() throws SQLException {return DriverManager.getConnection(url, user, password);}public static void freePs(ResultSet rs, PreparedStatement ps, Connection conn) {try {if (rs != null)rs.close();} catch (SQLException e) {e.printStackTrace();} finally {try {if (ps != null)ps.close();} catch (SQLException e) {e.printStackTrace();} finally {try {if (conn != null)conn.close();} catch (SQLException e) {e.printStackTrace();}}}}public static void freeSt(ResultSet rs, Statement st, Connection conn) {try {if (rs != null)rs.close();} catch (SQLException e) {e.printStackTrace();} finally {try {if (st != null)st.close();} catch (SQLException e) {e.printStackTrace();} finally {try {if (conn != null)conn.close();} catch (SQLException e) {e.printStackTrace();}}}}}

 

更新表，里面有用Statement和PreparedStatement两种方式

package com.bingfa;import java.sql.Connection;import java.sql.PreparedStatement;import java.sql.ResultSet;import java.sql.Statement;public class UploadCC {public static void JDBCStatment() throws Exception {Connection conn = null;Statement st = null;ResultSet rs = null;try {conn = (Connection) JDBCUtils.getConnection();conn.setAutoCommit(false);String sql = "";sql = "SELECT cc FROM test WHERE id=1 ";st = conn.createStatement();rs = st.executeQuery(sql);int c = 0;while (rs.next()) {c = rs.getInt(1);}c = c + 1;//用cc=cc+1，不会有并发问题，如果用c=c+1，则有问题sql = "UPDATE test SET cc = cc+1 WHERE id =1 ";st=(Statement) conn.createStatement();st.execute(sql);conn.commit();} finally {JDBCUtils.freeSt(rs, st, conn);}}public static void JDBCPrepare() throws Exception {Connection conn = null;PreparedStatement ps = null;ResultSet rs = null;try {conn = (Connection) JDBCUtils.getConnection();conn.setAutoCommit(false);String sql = "";sql = "SELECT cc FROM test WHERE id=1 ";ps = conn.prepareStatement(sql);rs = ps.executeQuery();int c = 0;while (rs.next()) {c = rs.getInt(1);}c = c + 1;sql = "UPDATE test SET cc = ? WHERE id =1 ";ps = conn.prepareStatement(sql);ps.setInt(1, c);ps.execute();conn.commit();} finally {JDBCUtils.freePs(rs, ps, conn);}}}

 

测试类，注意，测试并发并不用直接使用此类

package test;import junit.framework.TestCase;public class UploadCCTest extends TestCase {public UploadCCTest(String name) {super(name);}@org.junit.Testpublic void testJdbcStatment() {try {UploadCC.JDBCStatment();} catch (Exception e) {e.printStackTrace();}}@org.junit.Testpublic void testJdbcPrepare() {try {UploadCC.JDBCPrepare();} catch (Exception e) {e.printStackTrace();}}}

 

 

用这个类进行并发测试

package test;import com.clarkware.junitperf.LoadTest;import com.clarkware.junitperf.TestMethodFactory;import junit.framework.Test;public class TestSuite {public static Test suite() {Test suite = new TestMethodFactory(UploadCCTest.class, "testJdbcStatment");Test loadTest = new LoadTest(suite, 20, 10);//如果正常，cc的值应为200,但如果用c=c+1，值肯定小于200return loadTest;}//public static Test suite() {//Test suite = new TestMethodFactory(UploadCCTest.class, "testJdbcPrepare");//Test loadTest = new LoadTest(suite, 20, 10);//return loadTest;//}}