下面列出了org.springframework.jdbc.core.CallableStatementCallback#org.springframework.jdbc.core.CallableStatementCreator 实例代码，或者点击链接到github查看源代码，也可以在右侧发表评论。
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>());
}
}
/**
* 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);
}
/**
* 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.
*
* @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);
}
/**
* 引自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);
}
/**
* 执行存储过程
* @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;
}
});
}
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>());
}
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>());
}
@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>());
}
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>());
}
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>());
}
@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>());
}
/**
* 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());
}
/**
* 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);
}
/**
* 根据存储过程查询(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 Map<String, Object> call(CallableStatementCreator csc,
List<SqlParameter> declaredParameters) throws DataAccessException {
return delegate.call(csc, declaredParameters);
}
@Override
public <T> T execute(CallableStatementCreator csc, CallableStatementCallback<T> action) throws DataAccessException {
return super.execute(csc, action);
}
@Override
public Map<String, Object> call(CallableStatementCreator csc, List<SqlParameter> declaredParameters)
throws DataAccessException {
return super.call(csc, declaredParameters);
}
/**
* 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);
}
/**
* 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 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);
}
/**
* 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 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);
}