package com.web.pda.gltPda_wdPda.service.internal; import java.sql.CallableStatement; import java.sql.Connection; import java.sql.ResultSet; import java.sql.SQLException; import java.util.ArrayList; import java.util.HashMap; import java.util.List; import java.util.Map; import com.web.pda.gltPda_wdPda.service.WDApiService; import org.springframework.beans.factory.annotation.Autowired; import org.springframework.dao.DataAccessException; import org.springframework.jdbc.core.CallableStatementCallback; import org.springframework.jdbc.core.CallableStatementCreator; import org.springframework.jdbc.core.JdbcTemplate; import org.springframework.stereotype.Service; import org.springframework.transaction.annotation.Propagation; import org.springframework.transaction.annotation.Transactional; import com.alibaba.fastjson.JSONArray; import com.app.base.data.ApiResponseResult; @Service(value = "ApiService") @Transactional(propagation = Propagation.REQUIRED) public class WDApilmpl implements WDApiService { @Autowired private JdbcTemplate jdbcTemplate; // 用于设备数据上传 public ApiResponseResult uploadDeviceData(String factory, String company, String prono, JSONArray pData) throws Exception { if (pData.size()==0) { return ApiResponseResult.failure("上传数据不可为空。"); } try { //System.out.println("JSON: "+jsonObject); List list = uploadDeviDataPrc(factory, company, prono, pData.toString()); if (!list.get(0).toString().equals("0")) {// 存储过程调用失败 //判断返回游标 return ApiResponseResult.failure(list.get(1).toString()); } return ApiResponseResult.success("数据上传成功"); } catch (Exception e) { System.out.println(e); return ApiResponseResult.failure("上传数据JSON格式校验不通过。"+e.toString()); } } /** * 2022-05-23 用于设备数据上传 **/ public List uploadDeviDataPrc(String factory, String company, String prono, String pData) throws Exception { List resultList = (List) jdbcTemplate.execute(new CallableStatementCreator() { @Override public CallableStatement createCallableStatement(Connection con) throws SQLException { String storedProc = "{call PRC_IO_DATA (?,?,?,?,?,?)}";// 调用的sql CallableStatement cs = con.prepareCall(storedProc); cs.setString(1, factory); cs.setString(2, company); cs.setString(3, prono); cs.setString(4, pData);// json记录 cs.registerOutParameter(5, java.sql.Types.INTEGER);// 输出参数 返回标识 cs.registerOutParameter(6, java.sql.Types.VARCHAR);// 输出参数 返回标识 return cs; } }, new CallableStatementCallback() { public Object doInCallableStatement(CallableStatement cs) throws SQLException, DataAccessException { List result = new ArrayList<>(); cs.execute(); result.add(cs.getInt(5)); result.add(cs.getString(6)); return result; } }); return resultList; } public List> fitMap(ResultSet rs) throws Exception { List> list = new ArrayList<>(); if (null != rs) { Map map; int colNum = rs.getMetaData().getColumnCount(); List columnNames = new ArrayList(); for (int i = 1; i <= colNum; i++) { columnNames.add(rs.getMetaData().getColumnName(i)); } while (rs.next()) { map = new HashMap(); for (String columnName : columnNames) { map.put(columnName, rs.getString(columnName)); } list.add(map); } } return list; } }