因为一些旧系统没有用hibernate,只用了jdbctemplate,最近为其加一个查询模块时发现一个大坑:
框架里面有一个支持事务的jdbctemplate扩展类:
package com.freestyle.common.spring;import javax.annotation.Resource;
import javax.sql.DataSource;import org.springframework.context.annotation.DependsOn;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;
import org.springframework.transaction.TransactionStatus;
import org.springframework.transaction.support.TransactionCallback;
import org.springframework.transaction.support.TransactionCallbackWithoutResult;
import org.springframework.transaction.support.TransactionTemplate;/***** 带有事务管理的JdbcTemplate* @author dgmislrh**/
@DependsOn({"dsTransactionManager"})
//@Repository("transJdbcTemplate")
public class TransJdbcTemplate extends org.springframework.jdbc.core.JdbcTemplate{@Resource(name="dsTransactionManager") private org.springframework.jdbc.datasource.DataSourceTransactionManager dsTm;public static interface TransCallback{public void onExecute(JdbcTemplate pvJt,NamedParameterJdbcTemplate pvNamedJt) throws Exception;}public static interface NamedTransCallback{public void onExecute(NamedParameterJdbcTemplate pvJt) throws Exception;}public TransJdbcTemplate(DataSource dataSource) {super (dataSource);}/***** 若需要回滚事务，在onExecute里面throw runtimeexception即可* @param lvCallback*/public Exception doNamedTransactionBatch(final NamedTransCallback lvCallback){if (dsTm==null){dsTm=(DataSourceTransactionManager) ContextHolder.getBean("dsTransactionManager");}TransactionTemplate lvTt = new TransactionTemplate (dsTm);return lvTt.execute(new TransactionCallback<Exception>() {@Overridepublic Exception doInTransaction(TransactionStatus arg0) {try { NamedParameterJdbcTemplate lvNamedJt=new NamedParameterJdbcTemplate(getDataSource());lvCallback.onExecute(lvNamedJt);return null;} catch (Exception e) { // 抛出异常时进行回滚 arg0.setRollbackOnly();return e;}}}); }/***** 若需要回滚事务，在onExecute里面throw runtimeexception即可* @param lvCallback*/public Exception doTransactionBatch(final TransCallback pvCallback){if (dsTm==null){dsTm=(DataSourceTransactionManager) ContextHolder.getBean("dsTransactionManager");}TransactionTemplate lvTt = new TransactionTemplate (dsTm);return lvTt.execute(new TransactionCallback<Exception>() { @Overridepublic Exception doInTransaction(TransactionStatus arg0) {try { JdbcTemplate lvJt=new JdbcTemplate(getDataSource());NamedParameterJdbcTemplate lvNamedJt=new NamedParameterJdbcTemplate(getDataSource());pvCallback.onExecute(lvJt,lvNamedJt);return null;} catch (Exception e) { // 抛出异常时进行回滚 arg0.setRollbackOnly();return e;} }}); }}
然后我们应该它来完成先生成临时表,再引用临时表的操作...
final JSONData lvRet = new JSONData();if (pvMgt==null||pvMgt.length()==0) {lvRet.errCode = 2;lvRet.errMsg = Util.X10getMessage("MSG54201");return lvRet;}final TransJdbcTemplate lvTj=ContextHolder.getTransJdbcTemplate();Exception lvException=lvTj.doTransactionBatch(new TransCallback() { @Overridepublic void onExecute(JdbcTemplate pvJt,NamedParameterJdbcTemplate lvNamedJt) throws Exception {String lvKey=pvMgt.toUpperCase().trim();//get header dataMap<String,Object> lvMRet=pvJt.queryForMap("select fp_remark,f9i_template,fp_jobno from t9i_pd_mstr left join t96_pd_log on f96_mgtbarcd=f9i_mgtbarcd left join tp_jobmstr on fp_plant='DG' and fp_jobno=f96_order where f9i_mgtbarcd=? "+"group by fp_remark,f9i_template ,fp_jobno",lvKey);String lvComment=(String) lvMRet.get("fp_remark");if (!lvComment.contains("SPEED")){lvComment="";}else{lvComment="SPEED"+( lvComment.split("SPEED")[1]);}String lvTemplate=(String) lvMRet.get("f9i_template");String lvJobno=(String) lvMRet.get("fp_jobno");HashMap<String,Object> lvResult=new HashMap<String,Object>();lvRet.result=lvResult;lvResult.put("comment", lvComment);lvResult.put("jobno", lvJobno);//收集t96表數據StringBuilder sb=new StringBuilder();pvJt.execute("drop table if exists tmp_t96");//sb.append("create temp table tmp_t96 as \n"); sb.append("SELECT recseq,f9i_template,f96_station,f96_result_type,f96_op,f96_mgtbarcd,f96_result ,f96_create_dt, \n");sb.append("exists(select * from t9i_pd_mstr where f9i_mgtbarcd=f96_result) as subflag \n");sb.append("FROM (SELECT DISTINCT ON (f96_result, f96_station, f96_op, f96_result_type) * FROM t96_pd_log \n");sb.append("WHERE f96_mgtbarcd = ? AND f96_qty > -1 AND f96_station || f96_result_type || f96_result \n");sb.append("in ( SELECT f96_station || f96_result_type || f96_result FROM t96_pd_log WHERE f96_mgtbarcd = ? \n");sb.append("group by f96_op, f96_station, f96_mgtbarcd, f96_result_type, f96_result HAVING SUM(f96_qty) > 0) ) \n");sb.append("as distinct_list \n");sb.append("left join t9i_pd_mstr on f9i_mgtbarcd=f96_mgtbarcd \n");sb.append("ORDER BY f96_station ASC, recseq ASC ; \n");boolean lvRunned=false;final ArrayList<String> lvMgts=new ArrayList<String>();final ArrayList<String> lvSubMgts=new ArrayList<String>();lvMgts.add(pvMgt);while (lvMgts.size()>0) {String lvSql=(!lvRunned?"create temp table tmp_t96 as ":"insert into tmp_t96 ")+sb.toString();pvJt.execute(lvSql, new PreparedStatementCallback<Boolean>() {@Overridepublic Boolean doInPreparedStatement(PreparedStatement arg0)throws SQLException, DataAccessException {arg0.setString(1, lvMgts.get(0));arg0.setString(2, lvMgts.get(0));return Boolean.valueOf(true);}});lvMgts.remove(0);//將半成品再加入到lvMgts中List<String> lvT= pvJt.queryForList("select f96_result from tmp_t96 where subflag group by f96_result;",String.class);for (String item:lvT){if (lvSubMgts.contains(item)) continue;lvSubMgts.add(item);lvMgts.add(item);}lvRunned=true; } ...执行到List<String> lvT= pvJt.queryForList("select f96_result from tmp_t96 where subflag group by f96_result;",String.class);时发生异常,提示说tmp_t96表找不到,后来将建立临时表tmp_t96的方法换成update: pvJt.update(lvSql, lvMgts.get(0),lvMgts.get(0)); 就可以了! 换成setter callback也可以:
pvJt.update(lvSql,new PreparedStatementSetter(){@Overridepublic void setValues(PreparedStatement arg0) throws SQLException {arg0.setString(1, lvMgts.get(0));arg0.setString(2, lvMgts.get(0));}});查日志, 用execute方法执行的SQL确实执行了也没发生错误, 但为什么后面找不到,但如果没带任何参数的execute方法也是可以的!
暂时无解.
总结: 在jdbctemplate事务里面,如果需要生成临时表让后面引用的话,最好用update方法来执行create temp table 语句!!