org.springframework.jdbc.core.CallableStatementCallback Java Examples

/**
 * 执行存储过程
 * @param psc
 * @param rsStatus
 * @param outParam
 * @return T
 * @throws DataAccessException
 */
@Nullable
private <T> T execProcedure(CallableStatementCreator csc,boolean rsStatus,String[] outParam)	throws DataAccessException {
	return namedParameterJdbcTemplate.getJdbcTemplate().execute(csc, new CallableStatementCallback<T>() {
		@SuppressWarnings("unchecked")
		@Override
		public T doInCallableStatement(CallableStatement cs) throws SQLException, DataAccessException {
			Map<String,Object> result = new HashMap<String, Object>();
			Map<String,Object> message = new HashMap<String, Object>();
			ResultSet rs = null;
			boolean sqlExecuteStatus = false;
			int outParamLen = outParam.length;
			try {
				if(rsStatus) {
					rs = cs.executeQuery();
					result.put("rows", createRows(cs, rs));
					sqlExecuteStatus = true;
				}else {
					sqlExecuteStatus = cs.execute();
				}
				for (int i = 0; i <  outParamLen; i++) {
					message.put(outParam[i], cs.getString(outParam[i]));
				}
				message.put("sqlExecuteStatus", sqlExecuteStatus);
				result.put("message", message);
			} catch (IOException e) {
			}finally {
				JdbcUtils.closeResultSet(rs);
			}
			return (T) result;
		}
	});
}
 

/**
 * 根据存储过程查询(MSSQL AS 后必须加 SET NOCOUNT ON)
 * @param procedure  procedure
 * @return return
 */
@Override
public DataSet query(final Procedure procedure){
	final List<ProcedureParam> inputs = procedure.getInputs();
	final List<ProcedureParam> outputs = procedure.getOutputs();
	long fr = System.currentTimeMillis();
	String random = "";
	if(showSQL){
		random = "[SQL:" + System.currentTimeMillis() + "-" + BasicUtil.getRandomNumberString(8) + "][thread:"+Thread.currentThread().getId()+"][ds:"+ DataSourceHolder.getDataSource()+"]";
		log.warn("{}[txt:\n{}\n]", random, procedure.getName());
		log.warn("{}[输入参数:{}]", random, paramLogFormat(inputs));
		log.warn("{}[输出参数:{}]", random, paramLogFormat(inputs));
	}
	final String rdm = random;
	DataSet set = null;
	try{
		set = (DataSet)getJdbc().execute(new CallableStatementCreator(){
			public CallableStatement createCallableStatement(Connection conn) throws SQLException {
				String sql = "{call " +procedure.getName()+"(";
				final int sizeIn = inputs.size();
				final int sizeOut = outputs.size();
				final int size = sizeIn + sizeOut;
				for(int i=0; i<size; i++){
					sql += "?";
					if(i < size-1){
						sql += ",";
					}
				}
				sql += ")}";

				CallableStatement cs = conn.prepareCall(sql);
				for(int i=1; i<=sizeIn; i++){
					ProcedureParam param = inputs.get(i-1);
					Object value = param.getValue();
					if(null == value || "NULL".equalsIgnoreCase(value.toString())){
						value = null;
					}
					cs.setObject(i, value, param.getType());
				}
				for(int i=1; i<=sizeOut; i++){
					ProcedureParam param = outputs.get(i-1);
					if(null == param.getValue()){
						cs.registerOutParameter(i+sizeIn, param.getType());
					}else{
						cs.setObject(i, param.getValue(), param.getType());
					}

				}
				return cs;
			}
		}, new CallableStatementCallback<Object>(){
			public Object doInCallableStatement(CallableStatement cs) throws SQLException, DataAccessException {
				ResultSet rs = cs.executeQuery();
				DataSet set = new DataSet();
				ResultSetMetaData rsmd = rs.getMetaData();
				int cols = rsmd.getColumnCount();
				for(int i=1; i<=cols; i++){
					set.addHead(rsmd.getColumnName(i));
				}
				long mid = System.currentTimeMillis();
				while(rs.next()){
					DataRow row = new DataRow();
					for(int i=1; i<=cols; i++){
						row.put(rsmd.getColumnName(i), rs.getObject(i));
					}
					set.addRow(row);
				}
				set.setDatalink(DataSourceHolder.getDataSource());
				if(showSQL){
					log.warn("{}[封装耗时:{}ms][封装行数:{}]", rdm, System.currentTimeMillis() - mid,set.size());
				}
				return set;
			}
		});
		if(showSQL){
			log.warn("{}[执行耗时:{}ms]", random,System.currentTimeMillis() - fr);
		}
	}catch(Exception e){
		e.printStackTrace();
		if(showSQLWhenError){
			log.error("{}[异常][txt:\n{}\n]",random,procedure.getName());
			log.error("{}[输入参数:{}]",random,paramLogFormat(inputs));
			log.error("{}[输出参数:{}]",random,paramLogFormat(inputs));
		}
		throw new SQLQueryException("查询异常:" + e + "\nPROCEDURE:" + procedure.getName());
	}finally{
		//自动切换回默认数据源
		if(DataSourceHolder.isAutoDefault()){
			DataSourceHolder.recoverDataSource();
		}
	}
	return set;
}
 

public <T> T execute(CallableStatementCreator csc,
    CallableStatementCallback<T> action) throws DataAccessException {
  return delegate.execute(csc, action);
}
 

public <T> T execute(String callString,
    CallableStatementCallback<T> action) throws DataAccessException {
  return delegate.execute(callString, action);
}
 

@Override
public <T> T execute(CallableStatementCreator csc, CallableStatementCallback<T> action) throws DataAccessException {

	return super.execute(csc, action);
}
 

@Override
public <T> T execute(String callString, CallableStatementCallback<T> action) throws DataAccessException {

	return super.execute(callString, action);
}