org.springframework.jdbc.core.CallableStatementCreator Java Examples

public void updateAllocationTag(AllocationInterface a, String tag) {
    getJdbcTemplate().update("UPDATE alloc SET str_tag=? WHERE pk_alloc=?",
            tag, a.getAllocationId());

    getJdbcTemplate().update("UPDATE host_tag SET str_tag=? WHERE " +
            "host_tag.str_tag_type='Alloc' AND pk_host IN " +
            "(SELECT pk_host FROM host WHERE host.pk_alloc=?)", tag,
            a.getAllocationId());

    for (Map<String, Object> e: getJdbcTemplate().queryForList(
            "SELECT pk_host FROM host WHERE pk_alloc=?",a.getAllocationId())) {
        final String pk_host = (String) e.get("pk_host");
        getJdbcTemplate().call(new CallableStatementCreator() {
            public CallableStatement createCallableStatement(Connection con) throws SQLException {
                CallableStatement c = con.prepareCall("{ call recalculate_tags(?) }");
                c.setString(1, pk_host);
                return c;
            }
        }, new ArrayList<SqlParameter>());
    }
}
 

public void updateAllocationTag(AllocationInterface a, String tag) {
    getJdbcTemplate().update("UPDATE alloc SET str_tag=? WHERE pk_alloc=?",
            tag, a.getAllocationId());

    getJdbcTemplate().update("UPDATE host_tag SET str_tag=? WHERE " +
            "host_tag.str_tag_type='Alloc' AND pk_host IN " +
            "(SELECT pk_host FROM host WHERE host.pk_alloc=?)", tag,
            a.getAllocationId());

    for (Map<String, Object> e: getJdbcTemplate().queryForList(
            "SELECT pk_host FROM host WHERE pk_alloc=?",a.getAllocationId())) {
        final String pk_host = (String) e.get("pk_host");
        getJdbcTemplate().call(new CallableStatementCreator() {
            public CallableStatement createCallableStatement(Connection con) throws SQLException {
                CallableStatement c = con.prepareCall("{ call recalculate_tags(?) }");
                c.setString(1, pk_host);
                return c;
            }
        }, new ArrayList<SqlParameter>());
    }
}
 

@Override
public void recalcuateTags(final String id) {
    getJdbcTemplate().call(new CallableStatementCreator() {
        public CallableStatement createCallableStatement(Connection con) throws SQLException {
            CallableStatement c = con.prepareCall("{ call recalculate_tags(?) }");
            c.setString(1, id);
            return c;
        }
    }, new ArrayList<SqlParameter>());
}
 

/**
 * Confirm no connection was used to get metadata. Does not use superclass replay
 * mechanism.
 *
 * @throws Exception
 */
@Test
public void testStoredProcedureConfiguredViaJdbcTemplateWithCustomExceptionTranslator()
		throws Exception {
	given(callableStatement.execute()).willReturn(false);
	given(callableStatement.getUpdateCount()).willReturn(-1);
	given(callableStatement.getObject(2)).willReturn(5);
	given(connection.prepareCall("{call " + StoredProcedureConfiguredViaJdbcTemplate.SQL + "(?, ?)}")
			).willReturn(callableStatement);

	class TestJdbcTemplate extends JdbcTemplate {

		int calls;

		@Override
		public Map<String, Object> call(CallableStatementCreator csc,
				List<SqlParameter> declaredParameters) throws DataAccessException {
			calls++;
			return super.call(csc, declaredParameters);
		}
	}
	TestJdbcTemplate t = new TestJdbcTemplate();
	t.setDataSource(dataSource);
	// Will fail without the following, because we're not able to get a connection
	// from the DataSource here if we need to to create an ExceptionTranslator
	t.setExceptionTranslator(new SQLStateSQLExceptionTranslator());
	StoredProcedureConfiguredViaJdbcTemplate sp = new StoredProcedureConfiguredViaJdbcTemplate(t);

	assertEquals(sp.execute(11), 5);
	assertEquals(1, t.calls);

	verify(callableStatement).setObject(1, 11, Types.INTEGER);
	verify(callableStatement).registerOutParameter(2, Types.INTEGER);
}
 

/**
 * Method to perform the actual call processing
 */
private Map<String, Object> executeCallInternal(Map<String, ?> params) {
	CallableStatementCreator csc = getCallableStatementFactory().newCallableStatementCreator(params);
	if (logger.isDebugEnabled()) {
		logger.debug("The following parameters are used for call " + getCallString() + " with: " + params);
		int i = 1;
		for (SqlParameter p : getCallParameters()) {
			logger.debug(i++ + ": " +  p.getName() + " SQL Type "+ p.getSqlType() + " Type Name " + p.getTypeName() + " " + p.getClass().getName());
		}
	}
	return getJdbcTemplate().call(csc, getCallParameters());
}
 

/**
 * Confirm no connection was used to get metadata. Does not use superclass replay
 * mechanism.
 *
 * @throws Exception
 */
@Test
public void testStoredProcedureConfiguredViaJdbcTemplateWithCustomExceptionTranslator()
		throws Exception {
	given(callableStatement.execute()).willReturn(false);
	given(callableStatement.getUpdateCount()).willReturn(-1);
	given(callableStatement.getObject(2)).willReturn(5);
	given(connection.prepareCall("{call " + StoredProcedureConfiguredViaJdbcTemplate.SQL + "(?, ?)}")
			).willReturn(callableStatement);

	class TestJdbcTemplate extends JdbcTemplate {

		int calls;

		@Override
		public Map<String, Object> call(CallableStatementCreator csc,
				List<SqlParameter> declaredParameters) throws DataAccessException {
			calls++;
			return super.call(csc, declaredParameters);
		}
	}
	TestJdbcTemplate t = new TestJdbcTemplate();
	t.setDataSource(dataSource);
	// Will fail without the following, because we're not able to get a connection
	// from the DataSource here if we need to to create an ExceptionTranslator
	t.setExceptionTranslator(new SQLStateSQLExceptionTranslator());
	StoredProcedureConfiguredViaJdbcTemplate sp = new StoredProcedureConfiguredViaJdbcTemplate(t);

	assertEquals(sp.execute(11), 5);
	assertEquals(1, t.calls);

	verify(callableStatement).setObject(1, 11, Types.INTEGER);
	verify(callableStatement).registerOutParameter(2, Types.INTEGER);
}
 

/**
 * Delegate method to perform the actual call processing.
 */
private Map<String, Object> executeCallInternal(Map<String, ?> args) {
	CallableStatementCreator csc = getCallableStatementFactory().newCallableStatementCreator(args);
	if (logger.isDebugEnabled()) {
		logger.debug("The following parameters are used for call " + getCallString() + " with " + args);
		int i = 1;
		for (SqlParameter param : getCallParameters()) {
			logger.debug(i + ": " +  param.getName() + ", SQL type "+ param.getSqlType() + ", type name " +
					param.getTypeName() + ", parameter class [" + param.getClass().getName() + "]");
			i++;
		}
	}
	return getJdbcTemplate().call(csc, getCallParameters());
}
 

/**
 * Delegate method to perform the actual call processing.
 */
private Map<String, Object> executeCallInternal(Map<String, ?> args) {
	CallableStatementCreator csc = getCallableStatementFactory().newCallableStatementCreator(args);
	if (logger.isDebugEnabled()) {
		logger.debug("The following parameters are used for call " + getCallString() + " with " + args);
		int i = 1;
		for (SqlParameter param : getCallParameters()) {
			logger.debug(i + ": " +  param.getName() + ", SQL type "+ param.getSqlType() + ", type name " +
					param.getTypeName() + ", parameter class [" + param.getClass().getName() + "]");
			i++;
		}
	}
	return getJdbcTemplate().call(csc, getCallParameters());
}
 

private void recurseParentChange(final String folderId, final String newParentId) {
    getJdbcTemplate().call(new CallableStatementCreator() {

        public CallableStatement createCallableStatement(Connection con) throws SQLException {
            CallableStatement c = con.prepareCall("{ call recurse_folder_parent_change(?,?) }");
            c.setString(1, folderId);
            c.setString(2, newParentId);
            return c;
        }
    }, new ArrayList<SqlParameter>());
}
 

public void reorderFilters(final ShowInterface s) {
    getJdbcTemplate().update("LOCK TABLE filter IN SHARE MODE");
    getJdbcTemplate().call(new CallableStatementCreator() {

        public CallableStatement createCallableStatement(Connection con) throws SQLException {
            CallableStatement c = con.prepareCall("{ call reorder_filters(?) }");
            c.setString(1, s.getShowId());
            return c;
        }
    }, new ArrayList<SqlParameter>());
}
 

@Override
public void recalcuateTags(final String id) {
    getJdbcTemplate().call(new CallableStatementCreator() {
        public CallableStatement createCallableStatement(Connection con) throws SQLException {
            CallableStatement c = con.prepareCall("{ call recalculate_tags(?) }");
            c.setString(1, id);
            return c;
        }
    }, new ArrayList<SqlParameter>());
}
 

private void recurseParentChange(final String folderId, final String newParentId) {
    getJdbcTemplate().call(new CallableStatementCreator() {

        public CallableStatement createCallableStatement(Connection con) throws SQLException {
            CallableStatement c = con.prepareCall("{ call recurse_folder_parent_change(?,?) }");
            c.setString(1, folderId);
            c.setString(2, newParentId);
            return c;
        }
    }, new ArrayList<SqlParameter>());
}
 

/**
 * 执行存储过程
 * @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;
		}
	});
}
 

/**
 * 引自NamedParameterJdbcTemplate
 * @param sql
 * @param paramSource
 * @return PreparedStatementCreator
 */
private CallableStatementCreator getCallableStatementCreator(String sql, Map<String,Object> paramMap , String[] outParam) {
	SqlParameterSource paramSource = new MapSqlParameterSource(paramMap);
	ParsedSqlBean parsedSqlBean = getParsedSqlBean(sql);
	parsedSqlBean.setOutParam(outParam);
	String sqlToUse = ProcedureParameterUtils.substituteNamedParameters(parsedSqlBean, paramSource);
	List<SqlParameter> declaredParameters = ProcedureParameterUtils.buildSqlParameterList(parsedSqlBean, paramSource);
	CallableStatementCreatorFactory cscf = new CallableStatementCreatorFactory(sqlToUse, declaredParameters);
	return cscf.newCallableStatementCreator(paramMap);
}
 

/**
 * Confirm no connection was used to get metadata. Does not use superclass replay
 * mechanism.
 *
 * @throws Exception
 */
@Test
public void testStoredProcedureConfiguredViaJdbcTemplateWithCustomExceptionTranslator()
		throws Exception {
	given(callableStatement.execute()).willReturn(false);
	given(callableStatement.getUpdateCount()).willReturn(-1);
	given(callableStatement.getObject(2)).willReturn(5);
	given(connection.prepareCall("{call " + StoredProcedureConfiguredViaJdbcTemplate.SQL + "(?, ?)}")
			).willReturn(callableStatement);

	class TestJdbcTemplate extends JdbcTemplate {

		int calls;

		@Override
		public Map<String, Object> call(CallableStatementCreator csc,
				List<SqlParameter> declaredParameters) throws DataAccessException {
			calls++;
			return super.call(csc, declaredParameters);
		}
	}
	TestJdbcTemplate t = new TestJdbcTemplate();
	t.setDataSource(dataSource);
	// Will fail without the following, because we're not able to get a connection
	// from the DataSource here if we need to create an ExceptionTranslator
	t.setExceptionTranslator(new SQLStateSQLExceptionTranslator());
	StoredProcedureConfiguredViaJdbcTemplate sp = new StoredProcedureConfiguredViaJdbcTemplate(t);

	assertEquals(5, sp.execute(11));
	assertEquals(1, t.calls);

	verify(callableStatement).setObject(1, 11, Types.INTEGER);
	verify(callableStatement).registerOutParameter(2, Types.INTEGER);
}
 

/**
 * Delegate method to perform the actual call processing.
 */
private Map<String, Object> executeCallInternal(Map<String, ?> args) {
	CallableStatementCreator csc = getCallableStatementFactory().newCallableStatementCreator(args);
	if (logger.isDebugEnabled()) {
		logger.debug("The following parameters are used for call " + getCallString() + " with " + args);
		int i = 1;
		for (SqlParameter param : getCallParameters()) {
			logger.debug(i + ": " +  param.getName() + ", SQL type "+ param.getSqlType() + ", type name " +
					param.getTypeName() + ", parameter class [" + param.getClass().getName() + "]");
			i++;
		}
	}
	return getJdbcTemplate().call(csc, getCallParameters());
}
 

public void reorderFilters(final ShowInterface s) {
    getJdbcTemplate().update("LOCK TABLE filter IN SHARE MODE");
    getJdbcTemplate().call(new CallableStatementCreator() {

        public CallableStatement createCallableStatement(Connection con) throws SQLException {
            CallableStatement c = con.prepareCall("{ call reorder_filters(?) }");
            c.setString(1, s.getShowId());
            return c;
        }
    }, new ArrayList<SqlParameter>());
}
 

/**
 * Delegate method to perform the actual call processing.
 */
private Map<String, Object> executeCallInternal(Map<String, ?> args) {
	CallableStatementCreator csc = getCallableStatementFactory().newCallableStatementCreator(args);
	if (logger.isDebugEnabled()) {
		logger.debug("The following parameters are used for call " + getCallString() + " with " + args);
		int i = 1;
		for (SqlParameter param : getCallParameters()) {
			logger.debug(i + ": " +  param.getName() + ", SQL type "+ param.getSqlType() + ", type name " +
					param.getTypeName() + ", parameter class [" + param.getClass().getName() + "]");
			i++;
		}
	}
	return getJdbcTemplate().call(csc, getCallParameters());
}
 

/**
 * Confirm no connection was used to get metadata. Does not use superclass replay
 * mechanism.
 */
@Test
public void testStoredProcedureConfiguredViaJdbcTemplateWithCustomExceptionTranslator()
		throws Exception {
	given(callableStatement.execute()).willReturn(false);
	given(callableStatement.getUpdateCount()).willReturn(-1);
	given(callableStatement.getObject(2)).willReturn(5);
	given(connection.prepareCall("{call " + StoredProcedureConfiguredViaJdbcTemplate.SQL + "(?, ?)}")
			).willReturn(callableStatement);

	class TestJdbcTemplate extends JdbcTemplate {

		int calls;

		@Override
		public Map<String, Object> call(CallableStatementCreator csc,
				List<SqlParameter> declaredParameters) throws DataAccessException {
			calls++;
			return super.call(csc, declaredParameters);
		}
	}
	TestJdbcTemplate t = new TestJdbcTemplate();
	t.setDataSource(dataSource);
	// Will fail without the following, because we're not able to get a connection
	// from the DataSource here if we need to create an ExceptionTranslator
	t.setExceptionTranslator(new SQLStateSQLExceptionTranslator());
	StoredProcedureConfiguredViaJdbcTemplate sp = new StoredProcedureConfiguredViaJdbcTemplate(t);

	assertEquals(5, sp.execute(11));
	assertEquals(1, t.calls);

	verify(callableStatement).setObject(1, 11, Types.INTEGER);
	verify(callableStatement).registerOutParameter(2, Types.INTEGER);
}
 

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

public Map<String, Object> call(CallableStatementCreator csc,
    List<SqlParameter> declaredParameters) throws DataAccessException {
  return delegate.call(csc, declaredParameters);
}
 

@Override
public Map<String, Object> call(CallableStatementCreator csc, List<SqlParameter> declaredParameters)
		throws DataAccessException {

	return super.call(csc, declaredParameters);
}
 

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

	return super.execute(csc, action);
}
 

/**
 * 根据存储过程查询(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;
}
 

/**
 * Return a CallableStatementCreator to perform an operation
 * with the parameters returned from this ParameterMapper.
 * @param inParamMapper parametermapper. May not be {@code null}.
 */
protected CallableStatementCreator newCallableStatementCreator(ParameterMapper inParamMapper) {
	Assert.state(this.callableStatementFactory != null, "No CallableStatementFactory available");
	return this.callableStatementFactory.newCallableStatementCreator(inParamMapper);
}
 

/**
 * Return a CallableStatementCreator to perform an operation
 * with the parameters returned from this ParameterMapper.
 * @param inParamMapper parametermapper. May not be {@code null}.
 */
protected CallableStatementCreator newCallableStatementCreator(ParameterMapper inParamMapper) {
	return this.callableStatementFactory.newCallableStatementCreator(inParamMapper);
}
 

/**
 * Return a CallableStatementCreator to perform an operation
 * with this parameters.
 * @param inParams parameters. May be {@code null}.
 */
protected CallableStatementCreator newCallableStatementCreator(Map<String, ?> inParams) {
	return this.callableStatementFactory.newCallableStatementCreator(inParams);
}
 

/**
 * Return a CallableStatementCreator to perform an operation
 * with the parameters returned from this ParameterMapper.
 * @param inParamMapper parametermapper. May not be {@code null}.
 */
protected CallableStatementCreator newCallableStatementCreator(ParameterMapper inParamMapper) {
	return this.callableStatementFactory.newCallableStatementCreator(inParamMapper);
}
 

/**
 * Return a CallableStatementCreator to perform an operation
 * with this parameters.
 * @param inParams parameters. May be {@code null}.
 */
protected CallableStatementCreator newCallableStatementCreator(Map<String, ?> inParams) {
	return this.callableStatementFactory.newCallableStatementCreator(inParams);
}
 

/**
 * Return a CallableStatementCreator to perform an operation
 * with this parameters.
 * @param inParams parameters. May be {@code null}.
 */
protected CallableStatementCreator newCallableStatementCreator(@Nullable Map<String, ?> inParams) {
	Assert.state(this.callableStatementFactory != null, "No CallableStatementFactory available");
	return this.callableStatementFactory.newCallableStatementCreator(inParams);
}