Yair Zaslavsky has uploaded a new change for review. Change subject: core: Introducing Batch updates to DAOs ......................................................................
core: Introducing Batch updates to DAOs The following patch introduces the ability to perform a batch update Due to the fact spring-jdbc does not "natively" support postgresql we had to query the stored procedures meta-data own our own. The patch is based on Liran Zelkha's work - see http://gerrit.ovirt.org/#/c/14188/ Change-Id: If5ee0aa90bca3b5c257beb7b0eaa236f02f0206f Signed-off-by: Yair Zaslavsky <yzasl...@redhat.com> --- M backend/manager/modules/dal/src/main/java/org/ovirt/engine/core/dal/dbbroker/SimpleJdbcCallsHandler.java 1 file changed, 193 insertions(+), 28 deletions(-) git pull ssh://gerrit.ovirt.org:29418/ovirt-engine refs/changes/39/15039/1 diff --git a/backend/manager/modules/dal/src/main/java/org/ovirt/engine/core/dal/dbbroker/SimpleJdbcCallsHandler.java b/backend/manager/modules/dal/src/main/java/org/ovirt/engine/core/dal/dbbroker/SimpleJdbcCallsHandler.java index 2e8bb61..6bed2aa 100644 --- a/backend/manager/modules/dal/src/main/java/org/ovirt/engine/core/dal/dbbroker/SimpleJdbcCallsHandler.java +++ b/backend/manager/modules/dal/src/main/java/org/ovirt/engine/core/dal/dbbroker/SimpleJdbcCallsHandler.java @@ -1,11 +1,21 @@ package org.ovirt.engine.core.dal.dbbroker; +import java.sql.CallableStatement; +import java.sql.Connection; +import java.sql.PreparedStatement; +import java.sql.ResultSet; +import java.sql.SQLException; +import java.util.HashMap; import java.util.List; import java.util.Map; import java.util.concurrent.ConcurrentHashMap; import java.util.concurrent.ConcurrentMap; import org.ovirt.engine.core.dao.BaseDAODbFacade; +import org.ovirt.engine.core.utils.log.Log; +import org.ovirt.engine.core.utils.log.LogFactory; +import org.springframework.dao.DataAccessException; +import org.springframework.jdbc.core.ConnectionCallback; import org.springframework.jdbc.core.JdbcTemplate; import org.springframework.jdbc.core.RowMapper; import org.springframework.jdbc.core.namedparam.MapSqlParameterSource; @@ -13,13 +23,18 @@ import org.springframework.jdbc.core.simple.SimpleJdbcCall; public class SimpleJdbcCallsHandler { + private static final Log log = LogFactory + .getLog(SimpleJdbcCallsHandler.class); - private ConcurrentMap<String, SimpleJdbcCall> callsMap = - new ConcurrentHashMap<String, SimpleJdbcCall>(); + private ConcurrentMap<String, SimpleJdbcCall> callsMap = new ConcurrentHashMap<String, SimpleJdbcCall>(); + + private ConcurrentMap<String, StoredProcedureMetaData> storedProceduresMap = new ConcurrentHashMap<String, StoredProcedureMetaData>(); private DbEngineDialect dialect; private JdbcTemplate template; + + // private Map<String, List<MapSqlParameterSource>> batchSqls; public void setDbEngineDialect(DbEngineDialect dialect) { this.dialect = dialect; @@ -33,19 +48,154 @@ SimpleJdbcCall createCall(); } - public Map<String, Object> executeModification(final String procedureName, final MapSqlParameterSource paramSource) { - return executeImpl(procedureName, paramSource, createCallForModification(procedureName)); + /** + * Runs a set of stored procedure calls in a batch. Only useful for update + * procedures that return no value + * @param procedureName + * The procedure name + * @param executions + * A list of parameter maps + * @return + */ + public void runBatch(final String procName, + final List<MapSqlParameterSource> executions) + throws DataAccessException { + + template.execute(new ConnectionCallback<Object>() { + + @Override + public Object doInConnection(Connection con) throws SQLException, + DataAccessException { + StoredProcedureMetaData procMetaData = getStoredProcedureMetaData( + procName, con); + + CallableStatement stmt = con.prepareCall(procMetaData + .getSqlCommand()); + + for (MapSqlParameterSource execution : executions) { + mapParams(stmt, execution, + procMetaData.getParamatersMetaData()); + stmt.addBatch(); + } + + try { + stmt.executeBatch(); + } catch (SQLException e) { + log.fatal("Can't execute batch: ", e); + log.fatal("Can't execute batch. Next exception is: ", + e.getNextException()); + throw e; + } + + stmt.close(); + + return null; + } + + private StoredProcedureMetaData getStoredProcedureMetaData( + String procName, Connection con) throws SQLException { + + if (!storedProceduresMap.containsKey(procName)) { + StoredProcedureMetaData procMetaData = new StoredProcedureMetaData(); + fillProcMetaData(procName, con, procMetaData); + storedProceduresMap.putIfAbsent(procName, procMetaData); + } + return storedProceduresMap.get(procName); + } + + private void fillProcMetaData(String procName, Connection con, StoredProcedureMetaData procMetaData) + throws SQLException { + SimpleJdbcCall call = getCall(procName, + createCallForModification(procName)); + + Map<String, SqlCallParameter> paramOrder = new HashMap<String, SqlCallParameter>(); + + ResultSet rs2 = con.getMetaData().getProcedureColumns(null, + null, call.getProcedureName().toLowerCase(), "%"); + + StringBuffer params = new StringBuffer(); + + String procNameFromDB = null; + String procSchemaFromDB = null; + int internalCounter = 1; + while (rs2.next()) { + + String colName = rs2.getString("COLUMN_NAME"); + int ordinal; + + try { + ordinal = rs2.getInt("ORDINAL_POSITION"); + } catch (SQLException e) { + // For some reason, some postgres drivers don't provide + // ORDINAL_POSITION + ordinal = internalCounter; + } + int dataType = rs2.getInt("DATA_TYPE"); + procSchemaFromDB = rs2.getString("PROCEDURE_SCHEM"); + procNameFromDB = rs2.getString("PROCEDURE_NAME"); + + if (!colName.equalsIgnoreCase("returnValue")) { + paramOrder.put(colName, new SqlCallParameter(ordinal, + colName, dataType)); + + String typeName = rs2.getString("TYPE_NAME"); + + params.append("CAST (? AS ").append(typeName) + .append("),"); + ++internalCounter; + } + } + + if (params.length() > 0) { + params.deleteCharAt(params.length() - 1); + } + rs2.close(); + + StringBuffer sqlCommand = new StringBuffer(); + sqlCommand.append("{call ").append(procSchemaFromDB) + .append(".").append(procNameFromDB).append("(") + .append(params).append(")}"); + + procMetaData.setSqlCommand(sqlCommand.toString()); + procMetaData.setDbName(procNameFromDB); + procMetaData.setParamatersMetaData(paramOrder); + procMetaData.setSchemaName(procSchemaFromDB); + } + + private void mapParams(PreparedStatement stmt, + MapSqlParameterSource paramSource, + Map<String, SqlCallParameter> paramOrder) + throws SQLException { + + Map<String, Object> values = paramSource.getValues(); + for (String paramName : paramOrder.keySet()) { + Object value = values.get(paramName); + SqlCallParameter sqlParam = paramOrder.get(paramName); + int ordinal = sqlParam.getOrdinal(); + stmt.setObject(ordinal, value); + } + } + }); } - public int executeModificationReturnResult(final String procedureName, final MapSqlParameterSource paramSource) { + public Map<String, Object> executeModification(final String procedureName, + final MapSqlParameterSource paramSource) { + return executeImpl(procedureName, paramSource, + createCallForModification(procedureName)); + } + + public int executeModificationReturnResult(final String procedureName, + final MapSqlParameterSource paramSource) { Integer procedureResult = null; - Map<String, Object> result = executeImpl(procedureName, paramSource, createCallForModification(procedureName)); + Map<String, Object> result = executeImpl(procedureName, paramSource, + createCallForModification(procedureName)); if (!result.isEmpty()) { List<?> resultArray = (List<?>) result.values().iterator().next(); if (resultArray != null && !resultArray.isEmpty()) { Map<?, ?> resultMap = (Map<?, ?>) resultArray.get(0); if (!resultMap.isEmpty()) { - procedureResult = (Integer) resultMap.values().iterator().next(); + procedureResult = (Integer) resultMap.values().iterator() + .next(); } } } @@ -55,7 +205,8 @@ public <T> T executeRead(final String procedureName, final RowMapper<T> mapper, final MapSqlParameterSource parameterSource) { - List<T> results = executeReadList(procedureName, mapper, parameterSource); + List<T> results = executeReadList(procedureName, mapper, + parameterSource); return results.isEmpty() ? null : results.get(0); } @@ -63,17 +214,19 @@ public <T> List<T> executeReadList(final String procedureName, final RowMapper<T> mapper, final MapSqlParameterSource parameterSource) { - Map<String, Object> resultsMap = executeReadAndReturnMap(procedureName, mapper, parameterSource); + Map<String, Object> resultsMap = executeReadAndReturnMap(procedureName, + mapper, parameterSource); List<?> resultList = (List<?>) (resultsMap .get(BaseDAODbFacade.RETURN_VALUE_PARAMETER)); return (resultList != null) ? (List<T>) resultList : null; } - public <T> Map<String, Object> executeReadAndReturnMap(final String procedureName, - final RowMapper<T> mapper, + public <T> Map<String, Object> executeReadAndReturnMap( + final String procedureName, final RowMapper<T> mapper, final MapSqlParameterSource parameterSource) { - Map<String, Object> resultsMap = - executeImpl(procedureName, parameterSource, createCallForRead(procedureName, mapper, parameterSource)); + Map<String, Object> resultsMap = executeImpl(procedureName, + parameterSource, + createCallForRead(procedureName, mapper, parameterSource)); return resultsMap; } @@ -81,18 +234,23 @@ final RowMapper<?> mapper, final MapSqlParameterSource parameterSource) { return new CallCreator() { - @Override - public SimpleJdbcCall createCall() { - SimpleJdbcCall call = - (SimpleJdbcCall) dialect.createJdbcCallForQuery(template).withProcedureName(procedureName); - call.returningResultSet(BaseDAODbFacade.RETURN_VALUE_PARAMETER, mapper); - // Pass mapper information (only parameter names) in order to supply all the needed - // metadata information for compilation. - call.getInParameterNames().addAll( - SqlParameterSourceUtils.extractCaseInsensitiveParameterNames(parameterSource).keySet()); - return call; - } - }; + @Override + public SimpleJdbcCall createCall() { + SimpleJdbcCall call = (SimpleJdbcCall) dialect + .createJdbcCallForQuery(template).withProcedureName( + procedureName); + call.returningResultSet(BaseDAODbFacade.RETURN_VALUE_PARAMETER, + mapper); + // Pass mapper information (only parameter names) in order to + // supply all the needed + // metadata information for compilation. + call.getInParameterNames().addAll( + SqlParameterSourceUtils + .extractCaseInsensitiveParameterNames( + parameterSource).keySet()); + return call; + } + }; } private CallCreator createCallForModification(final String procedureName) { @@ -104,8 +262,15 @@ }; } + private Map<String, Object> executeImpl(String procedureName, MapSqlParameterSource paramsSource, CallCreator callCreator) { + SimpleJdbcCall call = getCall(procedureName, callCreator); + + return call.execute(paramsSource); + } + + private SimpleJdbcCall getCall(String procedureName, CallCreator callCreator) { SimpleJdbcCall call = callsMap.get(procedureName); if (call == null) { // Creates a simple jdbc call object, and @@ -118,9 +283,9 @@ call = callCreator.createCall(); call.compile(); - callsMap.putIfAbsent(procedureName, - call); + callsMap.putIfAbsent(procedureName, call); } - return call.execute(paramsSource); + return call; } + } -- To view, visit http://gerrit.ovirt.org/15039 To unsubscribe, visit http://gerrit.ovirt.org/settings Gerrit-MessageType: newchange Gerrit-Change-Id: If5ee0aa90bca3b5c257beb7b0eaa236f02f0206f Gerrit-PatchSet: 1 Gerrit-Project: ovirt-engine Gerrit-Branch: master Gerrit-Owner: Yair Zaslavsky <yzasl...@redhat.com> _______________________________________________ Engine-patches mailing list Engine-patches@ovirt.org http://lists.ovirt.org/mailman/listinfo/engine-patches
