一次执行批量sql的方法

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

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

SimpleJdbcTemplateDaoImpl .class 

1. /**
2. * Copyright (C) DADICOM, Inc.
3. */
4. package com.dadi.oa.dao.impl;
6. import java.sql.CallableStatement;
7. import java.sql.Connection;
8. import java.sql.PreparedStatement;
9. import java.sql.ResultSet;
10. import java.sql.ResultSetMetaData;
11. import java.sql.SQLException;
12. import java.sql.Statement;
13. import java.util.ArrayList;
14. import java.util.HashMap;
15. import java.util.List;
16. import java.util.Map;
18. import org.springframework.dao.DataAccessException;
19. import org.springframework.jdbc.core.BatchPreparedStatementSetter;
20. import org.springframework.jdbc.core.CallableStatementCallback;
21. import org.springframework.jdbc.core.CallableStatementCreator;
22. import org.springframework.jdbc.core.JdbcTemplate;
23. import org.springframework.jdbc.core.PreparedStatementCreator;
24. import org.springframework.jdbc.core.support.JdbcDaoSupport;
25. import org.springframework.jdbc.support.GeneratedKeyHolder;
26. import org.springframework.jdbc.support.KeyHolder;
28. import com.dadi.oa.dao.SimpleJdbcTemplateDao;
32. /**
33. * @author shensheng
34. *
35. * Mar 15, 2009 7:00:16 PM
36. */
37. public class SimpleJdbcTemplateDaoImpl extends JdbcDaoSupport implements SimpleJdbcTemplateDao {
39. /* (non-Javadoc)
40. * @see com.dadi.chr.dao.SimpleJdbcTemplateDao#queryForList(java.lang.String)
41. */
42. public List<Map<String, Object>> queryForList(String sql) throws Exception {
43. return this.getJdbcTemplate().queryForList(sql);
44. }
46. public List<Map<String, Object>> queryForList(String sql,Object...params){
47. return this.getJdbcTemplate().queryForList(sql, params);
48. }
50. /* (non-Javadoc)
51. * @see com.dadi.chr.dao.SimpleJdbcTemplateDao#update(java.lang.String)
52. */
53. public Integer update(String sql) throws Exception {
54. return this.getJdbcTemplate().update(sql);
55. }
57. /* (non-Javadoc)
58. * @see com.dadi.chr.dao.SimpleJdbcTemplateDao#update(java.lang.String,Object[])
59. */
60. public Integer update(String sql, Object... params) {
61. if(params.length == 0)return getJdbcTemplate().update(sql);
62. return getJdbcTemplate().update(sql,params);
63. }
65. public Long getCountBySql(String sql, Object...params) {
66. return getJdbcTemplate().queryForLong(sql,params);
67. }
69. public void batchSaveOrUpdate(String sql,final List<Object[]> params) {
70. getJdbcTemplate().batchUpdate(sql, new BatchPreparedStatementSetter() {
71. public void setValues(PreparedStatement pstat, int i) throws SQLException {
72. Object[] ps = params.get(i);
73. for(int p = 0 ; p < ps.length ; p++){
74. pstat.setObject(p + 1, ps[p]);
75. }
76. }
77. public int getBatchSize() {
78. return params.size();
79. }
80. });
81. }
84. @SuppressWarnings("unchecked")
85. public Map<String,Object> executeStoreProc(final String sql,final Object[] paramValues,final int[] outParamTypes){
87. Map<String,Object> retMap = (Map<String,Object>)this.getJdbcTemplate().execute(new CallableStatementCreator() {
88. public CallableStatement createCallableStatement(final Connection arg0) throws SQLException {
89. final CallableStatement cs = arg0.prepareCall(sql);
90. return cs;
91. }
92. }, new CallableStatementCallback() {
93. public Object doInCallableStatement(final CallableStatement cs) throws SQLException, DataAccessException {
94. Map<String,Object> resultMap = new HashMap<String,Object>();
95. final List<List<Map<String,Object>>> allRsLst = new ArrayList<List<Map<String,Object>>>();
96. if(paramValues != null){
97. for(int i = 0 ; i < paramValues.length ; i++){
98. cs.setObject(i + 1, paramValues[i]);
99. }
100. }
101. if(outParamTypes != null){
102. for(int i = 0 ; i < outParamTypes.length ;i++){
103. cs.registerOutParameter(paramValues.length + i + 1, outParamTypes[i]);
104. }
105. }
106. cs.execute();
108. ResultSet rs = cs.getResultSet();
109. while (rs == null && cs.getMoreResults()) {
110. rs = cs.getResultSet();
111. }
113. if(rs == null){
114. rs = cs.executeQuery();
115. }
117. while (true) {
118. if (rs != null) {
119. List<Map<String,Object>> rsLst = new ArrayList<Map<String,Object>>();
120. ResultSetMetaData rsmd = rs.getMetaData();
121. int count = rsmd.getColumnCount();
122. while (rs.next()) {
123. Map map = new HashMap();
124. for (int i = 1; i <= count; i++) {
125. final String key = rsmd.getColumnLabel(i);
126. final Object value = rs.getObject(key);
127. map.put(key, value);
128. }
129. rsLst.add(map);
130. }
131. allRsLst.add(rsLst);
132. }
134. if (cs.getMoreResults()) {
135. rs.close();
136. rs = cs.getResultSet();
137. } else {
138. if(rs != null){
139. rs.close();
140. }
141. break;
142. }
143. }
145. resultMap.put("rs", allRsLst);
147. if(outParamTypes != null){
148. Map<Integer,Object> map = new HashMap<Integer,Object>();
149. for(int i = 0 ; i < outParamTypes.length ;i++){
150. Integer key = paramValues.length + i + 1;
151. map.put(key,cs.getObject(key));
152. }
153. resultMap.put("out", map);
154. }
156. return resultMap;
157. }
158. });
160. return retMap;
161. }
164. @Override
165. public Integer updateGetKey(final String sql) throws Exception {
166. KeyHolder keyHolder = new GeneratedKeyHolder();
167. getJdbcTemplate().update(new PreparedStatementCreator() {
169. @Override
170. public PreparedStatement createPreparedStatement(Connection arg0)
171. throws SQLException {
172. PreparedStatement ps = arg0.prepareStatement(sql,Statement.RETURN_GENERATED_KEYS);
173. return ps;
174. }
175. }, keyHolder);
176. return keyHolder.getKey().intValue();
177. }
179. @Override
180. public void batchSaveOrUpdate(String[] sqls) {
181. getJdbcTemplate().batchUpdate(sqls);
183. }
186. }

ReadExeclDataServiceImpl.class

1. package com.dadi.oa.service.impl;
3. import java.util.ArrayList;
4. import java.util.HashMap;
5. import java.util.List;
6. import java.util.Map;
8. import org.apache.commons.logging.Log;
9. import org.apache.commons.logging.LogFactory;
11. import com.dadi.oa.dao.SimpleHibernateTempleDao;
12. import com.dadi.oa.dao.SimpleJdbcTemplateDao;
13. import com.dadi.oa.service.ReadExeclDataService;
14. import com.dadi.oa.system.ConstantsUtil;
15. import com.dadi.oa.util.ExeclReader;
16. import com.sun.star.uno.RuntimeException;
18. public class ReadExeclDataServiceImpl implements ReadExeclDataService{
19. private Log logger = LogFactory.getLog(this.getClass());
21. private SimpleJdbcTemplateDao jdbcDao;
22. private SimpleHibernateTempleDao hbnDao;
23. public void setJdbcDao(SimpleJdbcTemplateDao jdbcDao) {
24. this.jdbcDao = jdbcDao;
25. }
26. public void setHbnDao(SimpleHibernateTempleDao hbnDao) {
27. this.hbnDao = hbnDao;
28. }
29. @Override
30. public void saveGatherDataMsg(ExeclReader reader,
31. HashMap<String, String> inputMap) {
32. //HashMap retMap = new HashMap();
33. try {
34. String title = inputMap.get("title");
35. String userid = inputMap.get("userid");
36. Integer dataMsgId = jdbcDao.
37. updateGetKey("INSERT INTO dbo.t_dataPublish " +
38. " ( creatorId , "+
39. " createDate , "+
40. " title , "+
41. " remark , "+
42. " publishstatus , "+
43. " deleteflag "+
44. " ) "+
45. "VALUES ( "+userid+" , "+
46. " GETDATE() , " +
47. " '"+title+"' , "+
48. " '' , "+
49. " 0 , "+
50. " 0 "+
51. " )");
52. List headerData = reader.getHeaderData();
53. inputMap.put("dataMsgId", String.valueOf(dataMsgId));
54. Integer[] datacolumnids = new Integer[headerData.size()];
55. for (int i = 0 ; i < headerData.size() ; i++) {
56. HashMap<String, String> cellMap = (HashMap<String, String>) headerData.get(i);
57. Integer datacolumnid = jdbcDao
58. .updateGetKey("INSERT dbo.t_dataPublishColumn "+
59. " ( t_dataPublish_id , "+
60. " columnName , " +
61. " columnType "+
62. " ) " +
63. "VALUES (" +
64. " "+dataMsgId+", "+
65. " '"+cellMap.get("val")+"' , "+
66. " "+ConstantsUtil.gatherDataColumnStatus.get("COMMON_COLUMN")+
67. " )");
68. cellMap.put("datacolumnid", String.valueOf(datacolumnid));
69. datacolumnids[i] = datacolumnid;
70. }
71. ArrayList<String> datadetailsqls = new ArrayList<String>();
72. for (int i = 1; i < reader.getDataMap().size(); i++) {
73. ArrayList rowData = (ArrayList) reader.getDataMap().get(i);
74. for (int j = 0; j < rowData.size(); j++) {
75. HashMap<String, String> cellMap = (HashMap<String, String>) rowData.get(j);
76. datadetailsqls.add("INSERT INTO dbo.t_dataPublishDetail "+
77. " ( t_dataPublishColumn_id , "+
78. " rownum , "+
79. " cellValue "+
80. " ) "+
81. " VALUES ( "+datacolumnids[j]+" , "+
82. " "+cellMap.get("rowNum")+" , "+
83. " '"+cellMap.get("val")+"' "+
84. " )");
85. }
86. }
87. String[] batchsqls = new String[datadetailsqls.size()];
88. jdbcDao.batchSaveOrUpdate(datadetailsqls.toArray(batchsqls));
89. } catch (Exception e) {
90. e.printStackTrace();
91. logger.error("===========ERROR:ReadExeclDataServiceImpl.saveGatherDataMsg()");
92. throw new RuntimeException("服务器异常！！！");
93. }
94. //retMap.put("resultStr", resultStr);
95. }
98. }

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