一次执行批量sql的方法

在javaweb开发中，免不了一次业务操作会设计到几个表之间的添加，获取主键插入子表

1.主表使用单条插入，获取id，自表获取id进行批量插入最后完成操作

SimpleJdbcTemplateDaoImpl .class 

/**
 * Copyright (C) DADICOM, Inc.
 */
package com.dadi.oa.dao.impl;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.BatchPreparedStatementSetter;
import org.springframework.jdbc.core.CallableStatementCallback;
import org.springframework.jdbc.core.CallableStatementCreator;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.PreparedStatementCreator;
import org.springframework.jdbc.core.support.JdbcDaoSupport;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;
import com.dadi.oa.dao.SimpleJdbcTemplateDao;
/**
 * @author shensheng
 *
 * Mar 15, 2009   7:00:16 PM
 */
public class SimpleJdbcTemplateDaoImpl extends JdbcDaoSupport implements SimpleJdbcTemplateDao {
	/* (non-Javadoc)
	 * @see com.dadi.chr.dao.SimpleJdbcTemplateDao#queryForList(java.lang.String)
	 */
	public List<Map<String, Object>> queryForList(String sql) throws Exception {
		return this.getJdbcTemplate().queryForList(sql);
	}
	
	public List<Map<String, Object>> queryForList(String sql,Object...params){
		return this.getJdbcTemplate().queryForList(sql, params);
	}
	/* (non-Javadoc)
	 * @see com.dadi.chr.dao.SimpleJdbcTemplateDao#update(java.lang.String)
	 */
	public Integer update(String sql) throws Exception {
		return this.getJdbcTemplate().update(sql);
	}
	/* (non-Javadoc)
	 * @see com.dadi.chr.dao.SimpleJdbcTemplateDao#update(java.lang.String,Object[])
	 */
	public Integer update(String sql, Object... params) {
		if(params.length == 0)return getJdbcTemplate().update(sql); 
		return getJdbcTemplate().update(sql,params);
	}
	public Long getCountBySql(String sql, Object...params) {
		return getJdbcTemplate().queryForLong(sql,params);
	}
	
    public void batchSaveOrUpdate(String sql,final List<Object[]> params) {
    	getJdbcTemplate().batchUpdate(sql, new BatchPreparedStatementSetter() {
            public void setValues(PreparedStatement pstat, int i) throws SQLException {  
            	Object[] ps = params.get(i);
            	for(int p = 0 ; p < ps.length ; p++){
            		pstat.setObject(p + 1, ps[p]);  
            	}
            }  
            public int getBatchSize() {  
                return params.size();  
            }  
        });  
    } 
    
    
    @SuppressWarnings("unchecked")
	public Map<String,Object> executeStoreProc(final String sql,final Object[] paramValues,final int[] outParamTypes){
    	
    	Map<String,Object> retMap = (Map<String,Object>)this.getJdbcTemplate().execute(new CallableStatementCreator() {
			public CallableStatement createCallableStatement(final Connection arg0) throws SQLException {
				final CallableStatement cs = arg0.prepareCall(sql);
				return cs;
			}
		}, new CallableStatementCallback() {
			public Object doInCallableStatement(final CallableStatement cs) throws SQLException, DataAccessException {
				Map<String,Object> resultMap = new HashMap<String,Object>();
				final List<List<Map<String,Object>>> allRsLst = new ArrayList<List<Map<String,Object>>>();
				if(paramValues != null){
					for(int i = 0 ; i < paramValues.length ; i++){
						cs.setObject(i + 1, paramValues[i]);
					}
				}
				if(outParamTypes != null){
					for(int i = 0 ; i < outParamTypes.length ;i++){
						cs.registerOutParameter(paramValues.length + i + 1, outParamTypes[i]);
					}
				}
				cs.execute();
				
				ResultSet rs = cs.getResultSet();  
	            while (rs == null && cs.getMoreResults()) {  
	                rs = cs.getResultSet();  
	            }
	            
	            if(rs == null){
	            	rs = cs.executeQuery();
	            }
	            
	            while (true) {  
	                if (rs != null) {  
	                	List<Map<String,Object>> rsLst = new ArrayList<Map<String,Object>>();
	                	ResultSetMetaData rsmd = rs.getMetaData();
	                	int count = rsmd.getColumnCount();
	                    while (rs.next()) {
	                    	Map map = new HashMap();
	                    	for (int i = 1; i <= count; i++) {  
					            final String key = rsmd.getColumnLabel(i);
					            final Object value = rs.getObject(key);  
					            map.put(key, value);  
					        }
	                    	rsLst.add(map);
	                    }
	                    allRsLst.add(rsLst);
	                }
	                
	                if (cs.getMoreResults()) {  
	                	rs.close();
	                    rs = cs.getResultSet();  
	                } else {  
	                	if(rs != null){
	                		rs.close();
	                	}
	                    break;
	                }
	            }
	            
	            resultMap.put("rs", allRsLst);
	            
	            if(outParamTypes != null){
	            	Map<Integer,Object> map = new HashMap<Integer,Object>();
					for(int i = 0 ; i < outParamTypes.length ;i++){
						Integer key = paramValues.length + i + 1;
						map.put(key,cs.getObject(key));
					}
					resultMap.put("out", map);
				}
				
				return resultMap;
			}
		});
		
		return retMap;
	}
	@Override
	public Integer updateGetKey(final String sql) throws Exception {
		KeyHolder keyHolder = new GeneratedKeyHolder();  
		getJdbcTemplate().update(new PreparedStatementCreator() {
			
			@Override
			public PreparedStatement createPreparedStatement(Connection arg0)
					throws SQLException {
				PreparedStatement ps = arg0.prepareStatement(sql,Statement.RETURN_GENERATED_KEYS);  
				return ps;
			}
		}, keyHolder);
		return keyHolder.getKey().intValue();
	}
	@Override
	public void batchSaveOrUpdate(String[] sqls) {
		getJdbcTemplate().batchUpdate(sqls);
		
	}
}

ReadExeclDataServiceImpl.class

package com.dadi.oa.service.impl;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import com.dadi.oa.dao.SimpleHibernateTempleDao;
import com.dadi.oa.dao.SimpleJdbcTemplateDao;
import com.dadi.oa.service.ReadExeclDataService;
import com.dadi.oa.system.ConstantsUtil;
import com.dadi.oa.util.ExeclReader;
import com.sun.star.uno.RuntimeException;
public class ReadExeclDataServiceImpl  implements ReadExeclDataService{
	private Log logger = LogFactory.getLog(this.getClass());
	
	private SimpleJdbcTemplateDao jdbcDao;
	private SimpleHibernateTempleDao hbnDao;
	public void setJdbcDao(SimpleJdbcTemplateDao jdbcDao) {
		this.jdbcDao = jdbcDao;
	}
	public void setHbnDao(SimpleHibernateTempleDao hbnDao) {
		this.hbnDao = hbnDao;
	}
	@Override
	public void saveGatherDataMsg(ExeclReader reader,
			HashMap<String, String> inputMap) {
		//HashMap retMap = new HashMap();
		try {
			String title = inputMap.get("title");
			String userid = inputMap.get("userid");
			Integer dataMsgId = jdbcDao.
					updateGetKey("INSERT INTO dbo.t_dataPublish " +
							     "   ( creatorId , "+
							     "    createDate , "+
							     "    title ,      "+
							     "     remark ,    "+
							     "     publishstatus , "+
							     "     deleteflag "+
							     "   )   "+
							     "VALUES  ( "+userid+" ,  "+
							     "     GETDATE() , " +
							     "     '"+title+"' ,  "+
							     "     '' ,  "+
							     "     0 ,  "+
							      "    0  "+
							      "  )");
			List headerData = reader.getHeaderData();
			inputMap.put("dataMsgId", String.valueOf(dataMsgId));
			Integer[] datacolumnids = new Integer[headerData.size()];
			for (int i = 0 ; i < headerData.size() ; i++) {
				HashMap<String, String> cellMap = (HashMap<String, String>) headerData.get(i);
				Integer datacolumnid = jdbcDao
						.updateGetKey("INSERT dbo.t_dataPublishColumn "+
								       " ( t_dataPublish_id , "+
								       "   columnName , " +
								       "    columnType "+
								       "  ) " +
								 	   "VALUES  (" +
								 	   "	"+dataMsgId+", "+
									   "       '"+cellMap.get("val")+"' ,  "+
									   " "+ConstantsUtil.gatherDataColumnStatus.get("COMMON_COLUMN")+
								       " )");
				cellMap.put("datacolumnid", String.valueOf(datacolumnid));
				datacolumnids[i] = datacolumnid;
			}
			ArrayList<String> datadetailsqls = new ArrayList<String>();
			for (int i = 1; i < reader.getDataMap().size(); i++) {
				ArrayList rowData = (ArrayList) reader.getDataMap().get(i);
				for (int j = 0; j < rowData.size(); j++) {
					HashMap<String, String> cellMap = (HashMap<String, String>) rowData.get(j);
					datadetailsqls.add("INSERT INTO dbo.t_dataPublishDetail "+
							      " ( t_dataPublishColumn_id , "+
							      "          rownum , "+
							      "         cellValue "+
							      "        ) "+
							      " VALUES  ( "+datacolumnids[j]+" ,  "+
							      "          "+cellMap.get("rowNum")+" ,  "+
							      "          '"+cellMap.get("val")+"'  "+
							      "       )");
				}
			}
			String[] batchsqls = new String[datadetailsqls.size()];
			jdbcDao.batchSaveOrUpdate(datadetailsqls.toArray(batchsqls));
		} catch (Exception e) {
			e.printStackTrace();
			logger.error("===========ERROR:ReadExeclDataServiceImpl.saveGatherDataMsg()");
			throw new RuntimeException("服务器异常！！！");
		}
		//retMap.put("resultStr", resultStr);
	}
	
}

2.后面还想到一种方式，可以根据业务需求拼成一长串sql语句,包括定义变量，循环数据等，后面木有采用