/**
* Execute a stored procedure that updates data
*
* @param statementScope - the request scope
* @param conn - the database connection
* @param sql - the SQL to call the procedure
* @param parameters - the parameters for the procedure
* @return - the rows impacted by the procedure
* @throws SQLException - if the procedure fails
*/
public int executeUpdateProcedure(
StatementScope statementScope, Connection conn, String sql, Object[] parameters)
throws SQLException {
ErrorContext errorContext = statementScope.getErrorContext();
errorContext.setActivity("executing update procedure");
errorContext.setObjectId(sql);
CallableStatement cs = null;
setupResultObjectFactory(statementScope);
int rows = 0;
try {
errorContext.setMoreInfo("Check the SQL Statement (preparation failed).");
cs = prepareCall(statementScope.getSession(), conn, sql);
setStatementTimeout(statementScope.getStatement(), cs);
ParameterMap parameterMap = statementScope.getParameterMap();
ParameterMapping[] mappings = parameterMap.getParameterMappings();
errorContext.setMoreInfo("Check the output parameters (register output parameters failed).");
registerOutputParameters(cs, mappings);
errorContext.setMoreInfo("Check the parameters (set parameters failed).");
parameterMap.setParameters(statementScope, cs, parameters);
errorContext.setMoreInfo("Check the statement (update procedure failed).");
cs.execute();
rows = cs.getUpdateCount();
errorContext.setMoreInfo(
"Check the output parameters (retrieval of output parameters failed).");
retrieveOutputParameters(statementScope, cs, mappings, parameters, null);
} finally {
closeStatement(statementScope.getSession(), cs);
}
return rows;
}
/**
* Execute a stored procedure
*
* @param statementScope - the request scope
* @param conn - the database connection
* @param sql - the sql to call the procedure
* @param parameters - the parameters for the procedure
* @param skipResults - the number of results to skip
* @param maxResults - the maximum number of results to return
* @param callback - a row handler for processing the results
* @throws SQLException - if the procedure fails
*/
public void executeQueryProcedure(
StatementScope statementScope,
Connection conn,
String sql,
Object[] parameters,
int skipResults,
int maxResults,
RowHandlerCallback callback)
throws SQLException {
ErrorContext errorContext = statementScope.getErrorContext();
errorContext.setActivity("executing query procedure");
errorContext.setObjectId(sql);
CallableStatement cs = null;
ResultSet rs = null;
setupResultObjectFactory(statementScope);
try {
errorContext.setMoreInfo("Check the SQL Statement (preparation failed).");
Integer rsType = statementScope.getStatement().getResultSetType();
if (rsType != null) {
cs = prepareCall(statementScope.getSession(), conn, sql, rsType);
} else {
cs = prepareCall(statementScope.getSession(), conn, sql);
}
setStatementTimeout(statementScope.getStatement(), cs);
Integer fetchSize = statementScope.getStatement().getFetchSize();
if (fetchSize != null) {
cs.setFetchSize(fetchSize.intValue());
}
ParameterMap parameterMap = statementScope.getParameterMap();
ParameterMapping[] mappings = parameterMap.getParameterMappings();
errorContext.setMoreInfo("Check the output parameters (register output parameters failed).");
registerOutputParameters(cs, mappings);
errorContext.setMoreInfo("Check the parameters (set parameters failed).");
parameterMap.setParameters(statementScope, cs, parameters);
errorContext.setMoreInfo("Check the statement (update procedure failed).");
cs.execute();
errorContext.setMoreInfo("Check the results (failed to retrieve results).");
// Begin ResultSet Handling
rs = handleMultipleResults(cs, statementScope, skipResults, maxResults, callback);
// End ResultSet Handling
errorContext.setMoreInfo(
"Check the output parameters (retrieval of output parameters failed).");
retrieveOutputParameters(statementScope, cs, mappings, parameters, callback);
} finally {
try {
closeResultSet(rs);
} finally {
closeStatement(statementScope.getSession(), cs);
}
}
}
/**
* Long form of the method to execute a query
*
* @param statementScope - the request scope
* @param conn - the database connection
* @param sql - the SQL statement to execute
* @param parameters - the parameters for the statement
* @param skipResults - the number of results to skip
* @param maxResults - the maximum number of results to return
* @param callback - the row handler for the query
* @throws SQLException - if the query fails
*/
public void executeQuery(
StatementScope statementScope,
Connection conn,
String sql,
Object[] parameters,
int skipResults,
int maxResults,
RowHandlerCallback callback)
throws SQLException {
logger.info("执行query:{}", sql);
logger.info("参数:{}", parameters);
ErrorContext errorContext = statementScope.getErrorContext();
errorContext.setActivity("executing query");
errorContext.setObjectId(sql);
PreparedStatement ps = null;
ResultSet rs = null;
setupResultObjectFactory(statementScope);
try {
errorContext.setMoreInfo("Check the SQL Statement (preparation failed).");
Integer rsType = statementScope.getStatement().getResultSetType();
if (rsType != null) {
ps = prepareStatement(statementScope.getSession(), conn, sql, rsType);
} else {
ps = prepareStatement(statementScope.getSession(), conn, sql);
}
setStatementTimeout(statementScope.getStatement(), ps);
Integer fetchSize = statementScope.getStatement().getFetchSize();
if (fetchSize != null) {
ps.setFetchSize(fetchSize.intValue());
}
errorContext.setMoreInfo("Check the parameters (set parameters failed).");
statementScope.getParameterMap().setParameters(statementScope, ps, parameters);
errorContext.setMoreInfo("Check the statement (query failed).");
ps.execute();
errorContext.setMoreInfo("Check the results (failed to retrieve results).");
// Begin ResultSet Handling
rs = handleMultipleResults(ps, statementScope, skipResults, maxResults, callback);
// End ResultSet Handling
} finally {
try {
closeResultSet(rs);
} finally {
closeStatement(statementScope.getSession(), ps);
}
}
}
/**
* @param ps
* @param parameters
* @throws java.sql.SQLException
*/
public void setParameters(
StatementScope statementScope, PreparedStatement ps, Object[] parameters)
throws SQLException {
ErrorContext errorContext = statementScope.getErrorContext();
errorContext.setActivity("applying a parameter map");
errorContext.setObjectId(this.getId());
errorContext.setResource(this.getResource());
errorContext.setMoreInfo("Check the parameter map.");
if (parameterMappings != null) {
for (int i = 0; i < parameterMappings.length; i++) {
ParameterMapping mapping = parameterMappings[i];
errorContext.setMoreInfo(mapping.getErrorString());
if (mapping.isInputAllowed()) {
setParameter(ps, mapping, parameters, i);
}
}
}
}
/**
* Execute an update
*
* @param statementScope - the request scope
* @param conn - the database connection
* @param sql - the sql statement to execute
* @param parameters - the parameters for the sql statement
* @return - the number of records changed
* @throws SQLException - if the update fails
*/
public int executeUpdate(
StatementScope statementScope, Connection conn, String sql, Object[] parameters)
throws SQLException {
ErrorContext errorContext = statementScope.getErrorContext();
errorContext.setActivity("executing update");
errorContext.setObjectId(sql);
PreparedStatement ps = null;
setupResultObjectFactory(statementScope);
int rows = 0;
try {
errorContext.setMoreInfo("Check the SQL Statement (preparation failed).");
ps = prepareStatement(statementScope.getSession(), conn, sql);
setStatementTimeout(statementScope.getStatement(), ps);
errorContext.setMoreInfo("Check the parameters (set parameters failed).");
statementScope.getParameterMap().setParameters(statementScope, ps, parameters);
errorContext.setMoreInfo("Check the statement (update failed).");
ps.execute();
rows = ps.getUpdateCount();
} finally {
closeStatement(statementScope.getSession(), ps);
}
return rows;
}
