手头的项目采用Spring MVC框架,考虑到执行效率,需要在多线程下用native SQL访问数据库。留一份笔记备忘。
建立多线程:
执行子线程:
基本配置就不说了,网上有很多教程。
主叫类:
@Component("manage")
@Transactional
public class Manage implements IManage {
private String id;
private Map<String,String> sqlMap;
private MultiThread multiThread;
/*
* MultiThread继承Callable接口,执行有返回值的子进程
*/
@Override
public MultiThread getMultiThread() {
return multiThread;
}
@Resource(name="multiThread")
public void setMultiThread(MultiThread multiThread) {
this.multiThread = multiThread;
}
public Map<String, String> getSqlMap() {
return sqlMap;
}
/*
*@param sqlMap 动态生成的SQL语句
*/
public void setSqlMap(Map<String, String> sqlMap) {
this.sqlMap = sqlMap;
}
public void setId(String id) {
this.id = id;
}
/*
* @param resultMap保存SQL运行后的结果集
*/
@Override
public Map getDataById(String id){
this.multiThread.setSqlMap(newSqlMap);
Map resultMap = this.multiThread.createThread();
return resultMap;
}
}
建立多线程:
@Component("multiThread")
public class MultiThread {
private Map sqlMap;
private SessionFactory sessionFacotry;
private CountDownLatch endSingle;
public SessionFactory getSessionFacotry() {
return sessionFacotry;
}
/*
* Spring容器注入SessionFactory
*/
@Resource(name="refseqSessionFactory")
public void setSessionFacotry(SessionFactory sessionFacotry) {
this.sessionFacotry = sessionFacotry;
}
public Map getSqlMap() {
return sqlMap;
}
public void setSqlMap(Map sqlMap) {
this.sqlMap = sqlMap;
this.endSingle = endSingle;
}
public MultiThread(Map sqlMap) {
super();
this.sqlMap = sqlMap;
}
private MultiThread() {
super();
}
/*
* @param endSingle线程计数器
* @param pool线程池
*/
public Map createThread() {
CountDownLatch endSingle = new CountDownLatch(this.sqlMap.size());
Map resultMap = new HashMap();
ExecutorService pool = Executors.newCachedThreadPool();
Iterator iterator = this.sqlMap.keySet().iterator();
Future<?> future = null;
while (iterator.hasNext()) {
// tableName is the key of resultMap
String tableName = (String) iterator.next();
String sql = (String) this.sqlMap.get(tableName);
if (tableName != null && sql != null) {
// new 子线程
Callable task = new DaoThread(sessionFacotry,sql,endSingle);
//提交到线程池
future = pool.submit(task);
// 执行结果放在resultMap
resultMap.put(tableName, future);
}
}
//线程池关闭,不接受新任务,只执行已提交的任务
pool.shutdown();
//等待所有线程执行完毕
try {
endSingle.await();
} catch (InterruptedException e1) {
e1.printStackTrace();
}
return resultMap;
}
}
执行子线程:
/*
* @param threaLocal把线程与Hibernate session绑定
*/
public class DaoThread implements Callable{
private SessionFactory sessionFacotry;
private CountDownLatch endSingle;
//创建线程局部变量,用来保存Hibernate的session
private static final ThreadLocal threadLocal = new ThreadLocal();
public SessionFactory getSessionFacotry() {
return sessionFacotry;
}
public void setSessionFacotry(SessionFactory sessionFacotry) {
this.sessionFacotry = sessionFacotry;
}
private String sql;
public String getSql() {
return sql;
}
private DaoThread() {
}
public DaoThread(SessionFactory sessionFacotry, String sql,CountDownLatch endSingle ) {
super();
this.sessionFacotry = sessionFacotry;
this.sql = sql;
this.endSingle=endSingle;
}
@Override
public List<?> call() throws Exception {
//threadLocal得到每个子线程的变量副本
Session session = (Session) threadLocal.get();
//如果是该线程初次访问数据库(session==null)从SessionFactory得到一个Session
//SessionFactory是线程安全的
if (session == null){
session = this.sessionFacotry.openSession();
//把hibernate的session和当前线程绑定
threadLocal.set(session);
}
Query query = session.createSQLQuery(this.sql.toString());
List<String[]> list = (List<String[]>) query.list();
session.close();
threadLocal.set(null);
if (list == null || list.size()==0){
System.out.println("No validate entry");
//线程计数器减一
endSingle.countDown();
return null;
}
//线程计数器减一
endSingle.countDown();
return list;
}
}
结果集处理:
结果集内的返回值类型是Future,用get()来取得。
iterator = resultMap.keySet().iterator();
while(iterator.hasNext()){
String key = (String) iterator.next();
Future<?> f = (Future<?>) resultMap.get(key);
List lst = null;;
try {
lst = (List) f.get();
} catch (InterruptedException e) {
e.printStackTrace();
} catch (ExecutionException e) {
e.printStackTrace();
}
//检查从Hibernate返回的List
System.out.println(key+" :"+lst);
if (lst != null ){
for(int i=0;i<lst.size();i++){
Object[] row=(Object[])lst.get(i);
System.out.println("row[0]="+row[0].toString());
System.out.println("row[1]="+row[1].toString());
System.out.println("row[2]="+row[2].toString());
}
}
}