java并发查询数据库设计_Java版数据库并发查询测试程序

package demo;

import java.io.BufferedWriter;

import java.sql.*;

import javax.sql.*;

import java.util.*;

import oracle.jdbc.*;

import oracle.jdbc.pool.OracleDataSource;

public class ConcurrencyTest implements Runnable{

private static final String CONCURRENCY_DEGREE = "2";

private static final int EXECUTION_NUMBER = 10;

private static final boolean EVENT_10046_ON = true;

private static OracleDataSource ods = null;

private static final String URL = "jdbc:oracle:thin:@//192.168.1.118:1521/orcl";

//private static final String URL = "jdbc:oracle:thin:@//10.28.121.47:1521/orcl;"

private static final String USER = "SCOTT";

private static final String PASSWORD = "a123456";

private static boolean policyExists = false;

public ConcurrencyTest() {

}

private static void initPool() {

try {

ods = new OracleDataSource();

java.util.Properties prop = new java.util.Properties();

prop.setProperty("MinLimit", "2");

prop.setProperty("InitialLimit", CONCURRENCY_DEGREE);

prop.setProperty("MaxLimit", "30");

ods.setURL(URL);

ods.setUser(USER);

ods.setPassword(PASSWORD);

ods.setConnectionCachingEnabled(true);

ods.setConnectionCacheProperties (prop);

ods.setConnectionCacheName("ConcurrencyTest");

}

catch(Exception e) {

e.printStackTrace();

}

}

public void run() {

String sql = null;

Connection conn = null;

try {

//Obtaining connection from pool

conn = ods.getConnection();

//Turn on event 10046?

if(EVENT_10046_ON) {

conn.createStatement().execute("alter session set timed_statistics=true");

conn.createStatement().execute("alter session set events '10046 trace name context forever, level 12'");

}

//Baseline

sql = "select count(*) from emp0 where deptno=sys_context('my_context','deptno')";

test(conn, sql,"baseline");

//Testing static policy type

addPolicy(conn, true);

sql = "select count(*) from emp0";

test(conn, sql,"Static policy type");

//Testing dynamic policy type

dropPolicy(conn);

addPolicy(conn, false);

test(conn, sql, "Dynamic policy type");

dropPolicy(conn);

}

catch(Exception ex) {

ex.printStackTrace();

}

finally{

try {

if(conn != null) {

conn.close();

}

}

catch(Exception ex) {

ex.printStackTrace();;

}

}

}

private void test(Connection conn, String sql, String tag1) {

PreparedStatement stmt = null;

double elapsed = 0;

int testID = 0;

try {

//Generate test_id

stmt = conn.prepareStatement("select max(test_id) from scott.perf_analyze");

ResultSet rset = stmt.executeQuery();

rset.next();

testID = rset.getInt(1) + 1;

//Start testing

stmt = conn.prepareStatement(sql);

//EXECUTION_NUMBER executions of static policy type

long startTime = System.currentTimeMillis();

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

rset = stmt.executeQuery();

}

elapsed = (double)(System.currentTimeMillis() - startTime)/1000;

String insertSql = "Insert into scott.perf_analyze values(" + testID + "," +

"'" + sql.replaceAll("'","''") + "'," +

EXECUTION_NUMBER + "," +

"sysdate," +

elapsed +

",'" + tag1 + "')";

stmt = conn.prepareStatement(insertSql);

stmt.execute();

conn.commit();

}

catch(Exception e) {

e.printStackTrace();

}

finally {

try {

if(stmt != null) {

stmt.close();

}

}

catch(Exception e) {

e.printStackTrace();

}

}

}

private synchronized void dropPolicy(Connection conn) {

try {

if(policyExists) {

conn.createStatement().execute("call dbms_rls.drop_policy('SCOTT','EMP0','EMP0_POLICY')");

}

}

catch(Exception ex) {

ex.printStackTrace();

}

}

private synchronized void addPolicy(Connection conn, boolean staticFlag) {

if(! policyExists ) {

return;

}

String policySql = "begin DBMS_RLS.ADD_POLICY (" +

"object_schema => 'SCOTT'," +

"object_name => 'EMP0'," +

"policy_name => 'EMP0_POLICY'," +

"function_schema => 'SCOTT'," +

"policy_function => 'EMP_POLICY_1'," +

"statement_types => 'SELECT',";

try {

if(staticFlag) {

policySql += " policy_type => dbms_rls.static); end;";

}

else {

policySql += " policy_type => dbms_rls.dynamic); end;";

}

conn.createStatement().execute(policySql);

}

catch(Exception ex) {

ex.printStackTrace();

}

}

public static void main(String args[]) {

try {

//Initialize Connection pool

initPool();

for(int i = 0; i < Integer.parseInt(CONCURRENCY_DEGREE); i ++) {

(new Thread(new ConcurrencyTest())).start();

}

}

catch(Exception ex) {

ex.printStackTrace();

}

}

}