- package com.wfy.system.dao;
- import java.sql.CallableStatement;
- import java.sql.Connection;
- import java.sql.ResultSet;
- import java.sql.SQLException;
- import java.sql.Types;
- import java.text.DateFormat;
- import java.text.SimpleDateFormat;
- 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.ConnectionCallback;
- import org.springframework.jdbc.core.JdbcTemplate;
- import com.wfy.util.JDOM;
- /**
- *
- * 存储过程通过此类调用
- *
- * @author 金鑫
- *
- */
- public class DynamicDataProcedureExecuteDAO {
- private Map getSqlServerData(JdbcTemplate jdbcTemplate, final List<?> procedureList){
- Map returnMap = (Map) jdbcTemplate.execute(new ConnectionCallback(){
- public Map doInConnection(Connection conn) throws SQLException,
- DataAccessException {
- /**
- * 由于sqlserver跟oracle 的调用方式有所不同,所以需要分开调用,但是我对oracle存储过程不熟悉,所以没有写,以后会补上的。
- * 我的通用方式是将传入数据进行封装,再将传出数据封装为map返回给用户
- *
- * List.get(0) 中存放的是 存储过程的名称
- *
- * 从list.get(1)开始存放的是相应参数,参数以map类型存放
- *
- * map.put("name","")//传入传出的参数名字
- * map.put("value","")//传入的参数值,传出为null
- * map.put("type","input/output/outtable");//传入 传出 输出的结果集
- * map.put("dataType",type);//传入传出的数据类型。
- *
- * 返回的时候将type类型为output的封装为map返回前台。
- *
- * */
- //生成存储过程调用字符串
- String callStr = "{call ";
- callStr += procedureList.get(0).toString()+"(";
- for (int i = 1; i < procedureList.size(); i++) {
- if(!((Map)procedureList.get(i)).get("type").toString().equals("outtable"))
- callStr += "?,";
- }
- if(procedureList.size()>1){
- callStr = callStr.substring(0,callStr.length()-1);
- }
- callStr += ")}";
- System.out.println("callStr:"+callStr);
- CallableStatement cstmt = conn.prepareCall(callStr);
- for (int j = 1; j < procedureList.size(); j++) {
- Map<String, String> map = (Map<String, String>)procedureList.get(j);
- if(map.get("type").equals("input")){
- //说明此函数是传入函数
- if(map.get("dataType").toUpperCase().equals("STRING")){
- cstmt.setString(j, map.get("value"));
- }else if(map.get("dataType").toUpperCase().equals("INT")){
- try {
- cstmt.setInt(j, Integer.parseInt(map.get("value").toString()));
- } catch (Exception e) {
- throw new SQLException(map.get("value") + " INT 数据类型转换错误");
- }
- }else if(map.get("dataType").toUpperCase().equals("DECIMAL")){
- try {
- cstmt.setDouble(j, Double.parseDouble(map.get("value").toString()));
- } catch (Exception e) {
- throw new SQLException(map.get("value") + " DECIMAL 数据类型转换错误");
- }
- }else if(map.get("dataType").toUpperCase().equals("DATE")){
- try{
- DateFormat formatter1= new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
- cstmt.setDate(j, new java.sql.Date(formatter1.parse(map.get("value").toString()).getTime()));
- }catch(Exception e1){
- try{
- DateFormat formatter2= new SimpleDateFormat("yyyy-MM-dd");
- cstmt.setDate(j, new java.sql.Date(formatter2.parse(map.get("value").toString()).getTime()));
- }catch(Exception e2){
- throw new SQLException(map.get("value") + " DATE 数据类型转换错误");
- }
- }
- }
- }else if(map.get("type").equals("output")){
- //说明此函数是返回函数
- if(map.get("dataType").toUpperCase().equals("STRING")){
- cstmt.registerOutParameter(j, Types.CHAR);
- }else if(map.get("dataType").toUpperCase().equals("INT")){
- cstmt.registerOutParameter(j, Types.INTEGER);
- }else if(map.get("dataType").toUpperCase().equals("DECIMAL")){
- cstmt.registerOutParameter(j, Types.DECIMAL);
- }else if(map.get("dataType").toUpperCase().equals("DATE")){
- cstmt.registerOutParameter(j, Types.DATE);
- }
- }
- }
- //返回的map
- Map<String, Object> returnMap = new HashMap<String, Object>();
- cstmt.execute();
- int k = 1;
- while(cstmt.getMoreResults()){
- ResultSet rs = cstmt.getResultSet();
- List ls = new ArrayList();
- while(rs.next()){
- Map vc = new HashMap();
- for(int i=1;i<rs.getMetaData().getColumnCount()+1;i++){
- vc.put(rs.getMetaData().getColumnName(i), rs.getObject(i));
- }
- ls.add(vc);
- }
- for (; k < procedureList.size(); k++) {
- Map mp = (Map)procedureList.get(k);
- if(mp.get("type").equals("outtable")){
- returnMap.put(mp.get("name").toString(), ls);
- k++;
- break;
- }
- }
- }
- for (int j = 1; j < procedureList.size(); j++) {
- Map<String, String> map = (Map<String, String>)procedureList.get(j);
- if(map.get("type").equals("output")){
- //说明此函数是返回函数
- if(map.get("dataType").toUpperCase().equals("STRING")){
- returnMap.put(map.get("name").toString(), cstmt.getString(j));
- }else if(map.get("dataType").toUpperCase().equals("INT")){
- returnMap.put(map.get("name").toString(), cstmt.getInt(j));
- }else if(map.get("dataType").toUpperCase().equals("DECIMAL")){
- returnMap.put(map.get("name").toString(), cstmt.getDouble(j));
- }else if(map.get("dataType").toUpperCase().equals("DATE")){
- returnMap.put(map.get("name").toString(), cstmt.getDate(j));
- }
- }
- }
- cstmt.close();
- return returnMap;
- }
- });
- return returnMap;
- }
- }