java多线程 与 spring 连接池管理问题
需求描述： 通过多线程调用ORACLE 存过，存过部署在多个不同的数据库上。对于不同数据源，需要在线程中控制数据源切换。对于相同数据源，传入参数不同。问题： 测试发现，如果是针对同一数据库上的存过，程序在Fetching JDBC Connection from DataSource 的时候相当慢。
如果是不同数据库，速度会快很多，怀疑是多个线程在连接池中去到了同一个连接资源，造成了竞争。
测试类：
public class MutiThreadTest
{
/** log for this **/
private static final Logger logger = Logger.getLogger(MutiThreadTest.class);
public static void main(String[] args) throws JSONException, InterruptedException
{
CountDownLatch startSignal = new CountDownLatch(1);
CountDownLatch doneSignal = new CountDownLatch(3);
JSONObject obj = new JSONObject();
obj.put("REASON_TYPE", 3);
obj.put("ADJUST_REASON", "123");
obj.put("REQ_STAFF_ID", 10);
obj.put("ORG_NAME", "成都");
obj.put("BILLING_CYCLE_ID", "11206");
obj.put("REQ_STAFF_NAME", "10");
obj.put("REASON_TYPE_NAME", "3");
obj.put("ORG_ID", "280");
Map map = new HashMap();
map.put("param", obj);
map.put("sql", 1);
JSONObject obj1 = new JSONObject();
obj1.put("REASON_TYPE", 3);
obj1.put("ADJUST_REASON", "123");
obj1.put("REQ_STAFF_ID", 10);
obj1.put("ORG_NAME", "成都");
obj1.put("BILLING_CYCLE_ID", "11206");
obj1.put("REQ_STAFF_NAME", "10");
obj1.put("REASON_TYPE_NAME", "3");
obj1.put("ORG_ID", "830");
Map map1 = new HashMap();
map1.put("param", obj1);
map1.put("sql", 1);
JSONObject obj2 = new JSONObject();
obj2.put("REASON_TYPE", 3);
obj2.put("ADJUST_REASON", "123");
obj2.put("REQ_STAFF_ID", 10);
obj2.put("ORG_NAME", "成都");
obj2.put("BILLING_CYCLE_ID", "11206");
obj2.put("REQ_STAFF_NAME", "10");
obj2.put("REASON_TYPE_NAME", "3");
obj2.put("ORG_ID", "833");
Map map2 = new HashMap();
map2.put("param", obj2);
map2.put("sql", 1);
List<Map> list = new ArrayList<Map>();
list.add(map);
list.add(map1);
list.add(map2);
long start = System.currentTimeMillis();
logger.debug(">>>抽取开始时间:"+ start);
for (int i = 0; i < 3; ++i) // create and start threads
new Thread(new MutiThreadCall(startSignal, doneSignal,list.get(i))).start();
//TO-DO
// don't let run yet
startSignal.countDown(); // let all threads proceed
doneSignal.await(); // wait for all to finish
long middle = System.currentTimeMillis();
logger.debug(">>>抽取结束时间："+middle+"----花费时间："+(middle-start));
System.out.println("111111");
}
}
多线程处理类：
public class MutiThreadCall implements Runnable
{
/** log for this **/
private static final Logger logger = Logger.getLogger(MutiThreadCall.class);
private final CountDownLatch startSignal;
private final CountDownLatch doneSignal;
private final Map paramMap;
private JdbcTemplate dblink ;
private SimpleJdbcTemplate simpleJdbcTemplate;
/**
* 构造方法
* @param startSignal
* @param doneSignal
* @param paramMap
*/
public MutiThreadCall(CountDownLatch startSignal,CountDownLatch doneSignal,Map paramMap){
this.startSignal = startSignal;
this.doneSignal = doneSignal;
this.paramMap = paramMap;
}
@Override
public void run()
{
try
{
startSignal.await();
try
{
doCall(paramMap);
}
catch (JSONException e)
{
// TODO Auto-generated catch block
e.printStackTrace();
}
doneSignal.countDown();
}
catch (InterruptedException e)
{
logger.debug(e.getMessage());
}
}
private String doCall(Map paramMap) throws JSONException{
logger.debug("doCall>>>");
String result = "";
if(paramMap.containsKey("sql")){
if(paramMap.get("param") instanceof JSONObject){
final JSONObject rec = (JSONObject) paramMap.get("param");
dblink = getDataLink((String) DataSourceMap.dataSourceMap.get(rec.get("ORG_ID").toString()));
// dblink = getDataLink("CLOUD_TEST");
String param2Value = (String) dblink.execute(
new CallableStatementCreator() {
public CallableStatement createCallableStatement(Connection con) throws SQLException {
String storedProc = "{call pc_withdraw_table_new(?,?,?,?,?,?,?)}";// 调用的sql
CallableStatement cs = con.prepareCall(storedProc);
try
{
cs.setInt(1, Integer.parseInt(rec.get("ORG_ID").toString()));
// 设置输入参数的值
cs.setInt(2, Integer.parseInt(rec.get("BILLING_CYCLE_ID").toString()));
cs.setInt(3, Integer.valueOf(rec.get("REQ_STAFF_ID").toString()));
cs.setString(4, rec.get("ADJUST_REASON").toString());
cs.setInt(5, Integer.valueOf(rec.get("REASON_TYPE").toString()));
cs.setString(6, rec.get("REASON_TYPE").toString());
cs.registerOutParameter(7, OracleTypes.VARCHAR);// 注册输出参数的类型
}catch (JSONException e)
{
e.printStackTrace();
}
return cs;
}
}, new CallableStatementCallback() {
public Object doInCallableStatement(CallableStatement cs) throws SQLException, DataAccessException {
cs.execute();
return cs.getString(7);// 获取输出参数的值
}
});
result = param2Value;
}
}
return result;
}
}
请各位达人帮忙看看 --------------------编程问答-------------------- spring 配置：
<bean id="test1" class="com.web.adjust.model.DataSourceBean">
<property name="dataSource">
<ref local="atest" />
</property>
</bean>
<bean id="jdbcatest" class="org.springframework.jdbc.core.JdbcTemplate">
<property name="dataSource" ref="atest"></property>
</bean>
<bean id="atest" class="org.apache.commons.dbcp.BasicDataSource" destroy-method="close">
<property name="driverClassName">
<value>oracle.jdbc.driver.OracleDriver</value>
</property>
<property name="url">
<value>jdbc:oracle:thin:@127.0.0.1:1521:test1</value>
</property>
<property name="username">
<value>test1</value>
</property>
<property name="password">
<value>test1</value>
</property>
<property name="maxActive">
<value>10</value>
</property>
<property name="maxIdle">
<value>5</value>
</property>
<property name="minIdle">
<value>1</value>
</property>
<property name="initialSize">
<value>10</value>
</property>
<property name="minEvictableIdleTimeMillis">
<value>10000</value>
</property>
</bean>
<bean id="test2" class="com.web.adjust.model.DataSourceBean">
<property name="dataSource">
<ref local="btest" />
</property>
</bean>
<bean id="jdbcbtest" class="org.springframework.jdbc.core.JdbcTemplate">
<property name="dataSource" ref="btest"></property>
</bean>
<bean id="btest" class="org.apache.commons.dbcp.BasicDataSource" destroy-method="close">
<property name="driverClassName">
<value>oracle.jdbc.driver.OracleDriver</value>
</property>
<property name="url">
<value>jdbc:oracle:thin:@127.0.0.1:1521:test2</value>
</property>
<property name="username">
<value>test2</value>
</property>
<property name="password">
<value>test2</value>
</property>
<property name="maxActive">
<value>10</value>
</property>
<property name="maxIdle">
<value>5</value>
</property>
<property name="minIdle">
<value>1</value>
</property>
<property name="initialSize">
<value>10</value>
</property>
<property name="minEvictableIdleTimeMillis">
<value>10000</value>
</property>
</bean>
补充：Java , Java EE
