package com.web.pda.lyt.lytPda.service.internal; import com.app.base.data.ApiResponseResult; import com.web.pda.lyt.lytPda.service.PQCPolepieceService; 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 java.sql.CallableStatement; import java.sql.Connection; import java.sql.ResultSet; import java.sql.SQLException; import java.util.ArrayList; import java.util.List; import java.util.Map; @Service(value = "PQCPolepieceService") @Transactional(propagation = Propagation.REQUIRED) public class PQCPolepiecelmpl extends AppUtills implements PQCPolepieceService { @Autowired private JdbcTemplate jdbcTemplate; /** * 获取列表页 **/ public ApiResponseResult getItemList(String factory, String company,String keyword ,String mid,int size,int page)throws Exception{ List list=getItemListPrc(factory,company,keyword,mid,size,page); if(!list.get(0).toString().equals("0")){ return ApiResponseResult.failure(list.get(1).toString()); } return ApiResponseResult.success().data(list.get(2)); } public List getItemListPrc(String factory, String company,String keyword,String mid, int size,int page) throws Exception { List resultList = (List) jdbcTemplate.execute(new CallableStatementCreator() { @Override public CallableStatement createCallableStatement(Connection con) throws SQLException { String storedProc = "{call sp_pqc_app_qt_polepiece_list(?,?,?,?,?,?,?,?,?)}";// 调用的sql CallableStatement cs = con.prepareCall(storedProc); cs.setString(1, factory); cs.setString(2, company); cs.setString(3, keyword); cs.setString(4, mid); cs.setInt(5, size); cs.setInt(6, page); cs.registerOutParameter(7, java.sql.Types.INTEGER);// 输出参数 返回标识 cs.registerOutParameter(8, java.sql.Types.VARCHAR);// 输出参数 返回标识 cs.registerOutParameter(9, -10);// 输出参数 return cs; } }, new CallableStatementCallback() { public Object doInCallableStatement(CallableStatement cs) throws SQLException, DataAccessException { List result = new ArrayList<>(); List> l = new ArrayList(); cs.execute(); result.add(cs.getInt(7)); result.add(cs.getString(8)); if (cs.getString(7).toString().equals("0")) { // 游标处理 ResultSet rs = (ResultSet) cs.getObject(9); try { l = fitMap(rs); } catch (Exception e) { // TODO Auto-generated catch block e.printStackTrace(); } result.add(l); } System.out.println(l); return result; } }); return resultList; } /** * 保存 **/ public ApiResponseResult saveData(String factory, String company,String userNo,String mid, String endTime,String foven)throws Exception{ List list=saveDataPrc(factory,company,userNo,mid,endTime,foven); if(!list.get(0).toString().equals("0")){ return ApiResponseResult.failure(list.get(1).toString()); } return ApiResponseResult.success().data(list.get(2)); } public List saveDataPrc(String factory, String company,String userNo,String mid, String endTime,String foven) throws Exception { List resultList = (List) jdbcTemplate.execute(new CallableStatementCreator() { @Override public CallableStatement createCallableStatement(Connection con) throws SQLException { String storedProc = "{call sp_pqc_app_qt_polepiece(?,?,?,?,?,?,?,?,?)}";// 调用的sql CallableStatement cs = con.prepareCall(storedProc); cs.setString(1, factory); cs.setString(2, company); cs.setString(3, userNo); cs.setString(4, mid); cs.setString(5, endTime); cs.setString(6, foven); cs.registerOutParameter(7, java.sql.Types.INTEGER);// 输出参数 返回标识 cs.registerOutParameter(8, java.sql.Types.VARCHAR);// 输出参数 返回标识 cs.registerOutParameter(9, -10);// 输出参数 return cs; } }, new CallableStatementCallback() { public Object doInCallableStatement(CallableStatement cs) throws SQLException, DataAccessException { List result = new ArrayList<>(); List> l = new ArrayList(); cs.execute(); result.add(cs.getInt(7)); result.add(cs.getString(8)); if (cs.getString(7).toString().equals("0")) { // 游标处理 ResultSet rs = (ResultSet) cs.getObject(9); try { l = fitMap(rs); } catch (Exception e) { // TODO Auto-generated catch block e.printStackTrace(); } result.add(l); } System.out.println(l); return result; } }); return resultList; } /** * 提交 **/ public ApiResponseResult sumbitData(String factory, String company,String mid,String userNo, String operaType ,String remark,String tableName)throws Exception { List list = sumbitDataPrc(factory, company, mid, userNo, operaType,remark,tableName); if (!list.get(0).toString().equals("0")) {// 存储过程调用失败 //判断返回游标 return ApiResponseResult.failure(list.get(1).toString()); } return ApiResponseResult.success(); } public List sumbitDataPrc(String factory, String company,String mid,String userNo, String operaType ,String remark,String tableName) throws Exception { List resultList = (List) jdbcTemplate.execute(new CallableStatementCreator() { @Override public CallableStatement createCallableStatement(Connection con) throws SQLException { String storedProc = "{call sp_pqc_other_submit(?,?,?,?,?,?,?,?,?)}";// 调用的sql CallableStatement cs = con.prepareCall(storedProc); cs.setString(1, factory); cs.setString(2, company); cs.setString(3, mid); cs.setString(4, userNo); cs.setString(5, operaType); cs.setString(6, remark); cs.setString(7, tableName); cs.registerOutParameter(8, java.sql.Types.INTEGER);// 输出参数 返回标识 cs.registerOutParameter(9, java.sql.Types.VARCHAR);// 输出参数 return cs; } }, new CallableStatementCallback() { public Object doInCallableStatement(CallableStatement cs) throws SQLException, DataAccessException { List result = new ArrayList<>(); List> l = new ArrayList(); cs.execute(); result.add(cs.getInt(8)); result.add(cs.getString(9)); return result; } }); return resultList; } }