This is an automated email from the git hooks/post-receive script. New commit to branch feature/7739 in repository tutti. See https://gitlab.nuiton.org/codelutin/tutti.git commit b65e1ad8ee05b2caf7bf78b76a2d7b494abb5a11 Author: Tony CHEMIT <chemit@codelutin.com> Date: Wed Jun 29 12:37:35 2016 +0200 Introduction des générateurs de requètes sql (RAF celui de remplacement) (See #7739) --- .../synchro/DeleteSqlStatementGenerator.java | 41 ++++ .../synchro/InsertSqlStatementGenerator.java | 242 ++++++++++++++++++ .../synchro/UpdateSqlStatementGenerator.java | 269 +++++++++++++++++++++ 3 files changed, 552 insertions(+) diff --git a/observe-services-topia/src/main/java/fr/ird/observe/services/service/actions/synchro/DeleteSqlStatementGenerator.java b/observe-services-topia/src/main/java/fr/ird/observe/services/service/actions/synchro/DeleteSqlStatementGenerator.java new file mode 100644 index 0000000..c4339de --- /dev/null +++ b/observe-services-topia/src/main/java/fr/ird/observe/services/service/actions/synchro/DeleteSqlStatementGenerator.java @@ -0,0 +1,41 @@ +package fr.ird.observe.services.service.actions.synchro; + +import org.apache.commons.logging.Log; +import org.apache.commons.logging.LogFactory; +import org.nuiton.topia.persistence.metadata.TopiaMetadataEntity; + +/** + * Pour générer une requète sql de suppression à partir d'un référentiel donné. + * + * Created on 29/06/16. + * + * @author Tony Chemit - chemit@codelutin.com + */ +public class DeleteSqlStatementGenerator { + + /** Logger. */ + private static final Log log = LogFactory.getLog(DeleteSqlStatementGenerator.class); + + private static final String DELETE_STATEMENT = "DELETE FROM %s.%s WHERE topiaId = '%s';\n"; + + private final String schemaName; + private final String tableName; + + public DeleteSqlStatementGenerator(TopiaMetadataEntity metadataEntity) { + this.schemaName = metadataEntity.getDbSchemaName(); + this.tableName = metadataEntity.getDbTableName(); + } + + public String generateSql(String id) { + + String result = String.format(DELETE_STATEMENT, schemaName, tableName, id); + + if (log.isDebugEnabled()) { + log.debug("sql: " + result); + } + + return result; + + } + +} diff --git a/observe-services-topia/src/main/java/fr/ird/observe/services/service/actions/synchro/InsertSqlStatementGenerator.java b/observe-services-topia/src/main/java/fr/ird/observe/services/service/actions/synchro/InsertSqlStatementGenerator.java new file mode 100644 index 0000000..65fcac6 --- /dev/null +++ b/observe-services-topia/src/main/java/fr/ird/observe/services/service/actions/synchro/InsertSqlStatementGenerator.java @@ -0,0 +1,242 @@ +package fr.ird.observe.services.service.actions.synchro; + +import fr.ird.observe.services.dto.referential.ReferentialDto; +import fr.ird.observe.services.dto.referential.ReferentialReference; +import org.apache.commons.collections.CollectionUtils; +import org.apache.commons.logging.Log; +import org.apache.commons.logging.LogFactory; +import org.nuiton.topia.persistence.TopiaEntity; +import org.nuiton.topia.persistence.metadata.TopiaMetadataEntity; +import org.nuiton.util.beans.Binder; +import org.nuiton.util.beans.BinderFactory; + +import java.util.Collection; +import java.util.Date; +import java.util.LinkedHashSet; +import java.util.Map; +import java.util.Set; + +/** + * Pour générer une requète sql d'ajout à partir d'un référentiel donné. + * + * Created on 29/06/16. + * + * @author Tony Chemit - chemit@codelutin.com + */ +public class InsertSqlStatementGenerator<R extends ReferentialDto> { + + /** Logger. */ + private static final Log log = LogFactory.getLog(InsertSqlStatementGenerator.class); + + private static final String INSERT_STATEMENT = "INSERT INTO %s.%s(%s) VALUES (%s);\n"; + private static final String NM_ASSOCIATION_INSERT_STATEMENT = "INSERT INTO %s.%s(%s, %s) VALUES ('%s', '%s');\n"; + + private final Set<String> columnNames; + private final String schemaName; + private final String tableName; + private final Binder<R, R> binder; + private final String[] simplePropertyNames; + private final String[] compositionPropertyNames; + private final Set<NmAssociation> nmAssociations; + + /** + * Pour décrire une association nm. + */ + private static class NmAssociation { + + /** + * Le nom de la propriété dans l'objet. + */ + private final String propertyName; + /** + * Le nom de la colonne de l'association dans la table d'association. + */ + private final String dbColumnName; + /** + * Le nom de la table d'association. + */ + private final String tableName; + + private NmAssociation(String propertyName, String dbColumnName, String tableName) { + this.propertyName = propertyName; + this.dbColumnName = dbColumnName; + this.tableName = tableName; + } + + public String getPropertyName() { + return propertyName; + } + + public String getDbColumnName() { + return dbColumnName; + } + + public String getTableName() { + return tableName; + } + } + + public InsertSqlStatementGenerator(TopiaMetadataEntity metadataEntity, Class<R> dtoType) { + this.schemaName = metadataEntity.getDbSchemaName(); + this.tableName = metadataEntity.getDbTableName(); + Set<String> propertyNamesSet = metadataEntity.getProperties().keySet(); + this.simplePropertyNames = propertyNamesSet.toArray(new String[propertyNamesSet.size()]); + Set<String> compositionPropertyNamesSet = metadataEntity.getRequired().keySet(); + this.compositionPropertyNames = propertyNamesSet.toArray(new String[compositionPropertyNamesSet.size()]); + + Map<String, String> nmAssociationsMap = metadataEntity.getNmAssociations(); + this.nmAssociations = new LinkedHashSet<>(); + for (Map.Entry<String, String> entry : nmAssociationsMap.entrySet()) { + String propertyName = entry.getKey(); + String dbColumnName = metadataEntity.getDbColumnName(propertyName); + String tableName = metadataEntity.getBdNmAssociationName(propertyName); + NmAssociation nmAssociation = new NmAssociation(propertyName, dbColumnName, tableName); + nmAssociations.add(nmAssociation); + } + this.columnNames = computeColumnNames(metadataEntity, simplePropertyNames, compositionPropertyNames); + this.binder = BinderFactory.newBinder(dtoType); + } + + public String generateSql(R referentialDto) { + + Set<String> parameters = new LinkedHashSet<>(); + + addStringParameter(referentialDto.getId(), parameters); + addOtherTypeParameter(referentialDto.getVersion(), parameters); + addOtherTypeParameter(referentialDto.getCreateDate(), parameters); + + Map<String, Object> simpleParameters = binder.obtainProperties(referentialDto, true, simplePropertyNames); + for (Object parameterValue : simpleParameters.values()) { + + if (parameterValue == null) { + addNullParameter(parameters); + continue; + } + if (parameterValue instanceof String) { + addStringParameter((String) parameterValue, parameters); + continue; + } + if (parameterValue instanceof Date) { + addDateParameter((Date) parameterValue, parameters); + continue; + } + if (parameterValue instanceof Enum) { + addEnumParameter((Enum) parameterValue, parameters); + continue; + } + addOtherTypeParameter(parameterValue, parameters); + + } + + Map<String, Object> compositionParameters = binder.obtainProperties(referentialDto, true, compositionPropertyNames); + for (Object parameterValue : compositionParameters.values()) { + + if (parameterValue == null) { + addNullParameter(parameters); + continue; + } + if (parameterValue instanceof ReferentialDto) { + addReferentialDtoParameter((ReferentialDto) parameterValue, parameters); + continue; + } + if (parameterValue instanceof ReferentialReference) { + addReferentialReferenceParameter((ReferentialReference) parameterValue, parameters); + } + + } + + String result = String.format(INSERT_STATEMENT, + schemaName, + tableName, + String.join(",", columnNames), + String.join(",", parameters)); + + if (log.isDebugEnabled()) { + log.debug("sql: " + result); + } + for (NmAssociation nmAssociation : nmAssociations) { + String nmAssociationSql = generateNmAssociationSql(referentialDto, nmAssociation); + result += nmAssociationSql; + } + + return result; + + } + + private String generateNmAssociationSql(R referentialDto, NmAssociation nmAssociation) { + + StringBuilder builder = new StringBuilder(); + + Collection<ReferentialReference<?>> nmAssociationValues = binder.obtainSourceProperty(referentialDto, nmAssociation.getPropertyName()); + if (CollectionUtils.isNotEmpty(nmAssociationValues)) { + + String nmAssociationTableName = nmAssociation.getTableName(); + String nmAssociationDbColumnName = nmAssociation.getDbColumnName(); + String referentialDtoId = referentialDto.getId(); + + for (ReferentialReference<?> nmAssociationValue : nmAssociationValues) { + + String sql = String.format(NM_ASSOCIATION_INSERT_STATEMENT, + schemaName, + nmAssociationTableName, + this.tableName, + nmAssociationDbColumnName, + referentialDtoId, + nmAssociationValue.getId()); + if (log.isDebugEnabled()) { + log.debug("sql: " + sql); + } + builder.append(sql); + + } + } + + return builder.toString(); + + } + + private Set<String> computeColumnNames(TopiaMetadataEntity metadataEntity, + String[] simplePropertyNames, + String[] compositionPropertyNames) { + Set<String> columnNames = new LinkedHashSet<>(); + columnNames.add(TopiaEntity.PROPERTY_TOPIA_ID); + columnNames.add(TopiaEntity.PROPERTY_TOPIA_VERSION); + columnNames.add(TopiaEntity.PROPERTY_TOPIA_CREATE_DATE); + + for (String propertyName : simplePropertyNames) { + columnNames.add(metadataEntity.getDbColumnName(propertyName)); + } + for (String propertyName : compositionPropertyNames) { + columnNames.add(metadataEntity.getDbColumnName(propertyName)); + } + return columnNames; + } + + private void addNullParameter(Set<String> parameters) { + parameters.add("NULL"); + } + + private void addStringParameter(String parameter, Set<String> parameters) { + parameters.add("'" + parameter.replaceAll("'", "''") + "'"); + } + + private void addDateParameter(Date parameter, Set<String> parameters) { + parameters.add("'" + parameter + "'"); + } + + private void addEnumParameter(Enum parameter, Set<String> parameters) { + parameters.add("" + parameter.ordinal()); + } + + private void addOtherTypeParameter(Object parameter, Set<String> parameters) { + parameters.add("" + parameter); + } + + private void addReferentialReferenceParameter(ReferentialReference parameter, Set<String> parameters) { + addStringParameter(parameter.getId(), parameters); + } + + private void addReferentialDtoParameter(ReferentialDto parameter, Set<String> parameters) { + addStringParameter(parameter.getId(), parameters); + } +} diff --git a/observe-services-topia/src/main/java/fr/ird/observe/services/service/actions/synchro/UpdateSqlStatementGenerator.java b/observe-services-topia/src/main/java/fr/ird/observe/services/service/actions/synchro/UpdateSqlStatementGenerator.java new file mode 100644 index 0000000..d238410 --- /dev/null +++ b/observe-services-topia/src/main/java/fr/ird/observe/services/service/actions/synchro/UpdateSqlStatementGenerator.java @@ -0,0 +1,269 @@ +package fr.ird.observe.services.service.actions.synchro; + +import fr.ird.observe.services.dto.referential.ReferentialDto; +import fr.ird.observe.services.dto.referential.ReferentialReference; +import org.apache.commons.collections.CollectionUtils; +import org.apache.commons.logging.Log; +import org.apache.commons.logging.LogFactory; +import org.nuiton.topia.persistence.TopiaEntity; +import org.nuiton.topia.persistence.metadata.TopiaMetadataEntity; +import org.nuiton.util.beans.Binder; +import org.nuiton.util.beans.BinderFactory; + +import java.util.Collection; +import java.util.Date; +import java.util.LinkedHashSet; +import java.util.Map; +import java.util.Set; +import java.util.TreeMap; + +/** + * Pour générer une requète sql de mise à jour à partir d'un référentiel donné. + * + * Created on 29/06/16. + * + * @author Tony Chemit - chemit@codelutin.com + */ +public class UpdateSqlStatementGenerator<R extends ReferentialDto> { + + /** Logger. */ + private static final Log log = LogFactory.getLog(UpdateSqlStatementGenerator.class); + + private static final String UPDATE_STATEMENT = "UPDATE %s.%s %s WHERE topiaId ='%s';\n"; + private static final String NM_ASSOCIATION_DELETE_STATEMENT = "DELETE FROM %s.%s WHERE %s = '%s';\n"; + private static final String NM_ASSOCIATION_INSERT_STATEMENT = "INSERT INTO %s.%s(%s, %s) VALUES ('%s', '%s');\n"; + + private final Map<String, String> columnNames; + private final String schemaName; + private final String tableName; + private final Binder<R, R> binder; + private final String[] simplePropertyNames; + private final String[] compositionPropertyNames; + private final Set<NmAssociation> nmAssociations; + + /** + * Pour décrire une association nm. + */ + private static class NmAssociation { + + /** + * Le nom de la propriété dans l'objet. + */ + private final String propertyName; + /** + * Le nom de la colonne de l'association dans la table d'association. + */ + private final String dbColumnName; + /** + * Le nom de la table d'association. + */ + private final String tableName; + + private NmAssociation(String propertyName, String dbColumnName, String tableName) { + this.propertyName = propertyName; + this.dbColumnName = dbColumnName; + this.tableName = tableName; + } + + public String getPropertyName() { + return propertyName; + } + + public String getDbColumnName() { + return dbColumnName; + } + + public String getTableName() { + return tableName; + } + } + + public UpdateSqlStatementGenerator(TopiaMetadataEntity metadataEntity, Class<R> dtoType) { + this.schemaName = metadataEntity.getDbSchemaName(); + this.tableName = metadataEntity.getDbTableName(); + Set<String> propertyNamesSet = metadataEntity.getProperties().keySet(); + this.simplePropertyNames = propertyNamesSet.toArray(new String[propertyNamesSet.size()]); + Set<String> compositionPropertyNamesSet = metadataEntity.getRequired().keySet(); + this.compositionPropertyNames = propertyNamesSet.toArray(new String[compositionPropertyNamesSet.size()]); + + Map<String, String> nmAssociationsMap = metadataEntity.getNmAssociations(); + this.nmAssociations = new LinkedHashSet<>(); + for (Map.Entry<String, String> entry : nmAssociationsMap.entrySet()) { + String propertyName = entry.getKey(); + String dbColumnName = metadataEntity.getDbColumnName(propertyName); + String tableName = metadataEntity.getBdNmAssociationName(propertyName); + NmAssociation nmAssociation = new NmAssociation(propertyName, dbColumnName, tableName); + nmAssociations.add(nmAssociation); + } + this.columnNames = computeColumnNames(metadataEntity, simplePropertyNames, compositionPropertyNames); + this.binder = BinderFactory.newBinder(dtoType); + } + + public String generateSql(R referentialDto) { + + StringBuilder parameters = new StringBuilder(); + + addStringParameter(TopiaEntity.PROPERTY_TOPIA_ID, referentialDto.getId(), parameters); + addOtherTypeParameter(TopiaEntity.PROPERTY_TOPIA_VERSION, referentialDto.getVersion(), parameters); + addOtherTypeParameter(TopiaEntity.PROPERTY_TOPIA_CREATE_DATE, referentialDto.getCreateDate(), parameters); + + Map<String, Object> simpleParameters = binder.obtainProperties(referentialDto, true, simplePropertyNames); + for (Map.Entry<String, Object> entry : simpleParameters.entrySet()) { + + String parameterName = entry.getKey(); + String columnName = columnNames.get(parameterName); + Object parameterValue = entry.getValue(); + + if (parameterValue == null) { + addNullParameter(columnName, parameters); + continue; + } + if (parameterValue instanceof String) { + addStringParameter(columnName, (String) parameterValue, parameters); + continue; + } + if (parameterValue instanceof Date) { + addDateParameter(columnName, (Date) parameterValue, parameters); + continue; + } + if (parameterValue instanceof Enum) { + addEnumParameter(columnName, (Enum) parameterValue, parameters); + continue; + } + addOtherTypeParameter(columnName, parameterValue, parameters); + + } + + + Map<String, Object> compositionParameters = binder.obtainProperties(referentialDto, true, compositionPropertyNames); + for (Map.Entry<String, Object> entry : compositionParameters.entrySet()) { + + String parameterName = entry.getKey(); + String columnName = columnNames.get(parameterName); + Object parameterValue = entry.getValue(); + + if (parameterValue == null) { + addNullParameter(columnName, parameters); + continue; + } + if (parameterValue instanceof ReferentialDto) { + addReferentialDtoParameter(columnName, (ReferentialDto) parameterValue, parameters); + continue; + } + if (parameterValue instanceof ReferentialReference) { + addReferentialReferenceParameter(columnName, (ReferentialReference) parameterValue, parameters); + } + + } + + String result = String.format(UPDATE_STATEMENT, + schemaName, + tableName, + parameters.substring(2), + referentialDto.getId()); + + if (log.isDebugEnabled()) { + log.debug("sql: " + result); + } + + for (NmAssociation nmAssociation : nmAssociations) { + String nmAssociationSql = generateNmAssociationSql(referentialDto, nmAssociation); + result += nmAssociationSql; + } + + return result; + + } + + private String generateNmAssociationSql(R referentialDto, NmAssociation nmAssociation) { + + StringBuilder builder = new StringBuilder(); + + String referentialDtoId = referentialDto.getId(); + String nmAssociationTableName = nmAssociation.getTableName(); + + // On commence toujours par supprimer toutes les anciennes associations, elles seront ré-ajoutées juste après + String deleteSql = String.format(NM_ASSOCIATION_DELETE_STATEMENT, + schemaName, + nmAssociationTableName, + tableName, + referentialDtoId); + builder.append(deleteSql); + if (log.isDebugEnabled()) { + log.debug("sql: " + deleteSql); + } + + Collection<ReferentialReference<?>> nmAssociationValues = binder.obtainSourceProperty(referentialDto, nmAssociation.getPropertyName()); + if (CollectionUtils.isNotEmpty(nmAssociationValues)) { + + + String nmAssociationDbColumnName = nmAssociation.getDbColumnName(); + + + for (ReferentialReference<?> nmAssociationValue : nmAssociationValues) { + + String sql = String.format(NM_ASSOCIATION_INSERT_STATEMENT, + schemaName, + nmAssociationTableName, + tableName, + nmAssociationDbColumnName, + referentialDtoId, + nmAssociationValue.getId()); + if (log.isDebugEnabled()) { + log.debug("sql: " + sql); + } + builder.append(sql); + + } + } + + return builder.toString(); + + } + + private Map<String, String> computeColumnNames(TopiaMetadataEntity metadataEntity, + String[] simplePropertyNames, + String[] compositionPropertyNames) { + Map<String, String> columnNames = new TreeMap<>(); + + for (String propertyName : simplePropertyNames) { + columnNames.put(propertyName, metadataEntity.getDbColumnName(propertyName)); + } + for (String propertyName : compositionPropertyNames) { + columnNames.put(propertyName, metadataEntity.getDbColumnName(propertyName)); + } + return columnNames; + } + + private void addNullParameter(String columnName, StringBuilder parameters) { + addParameter0(columnName, "NULL", parameters); + } + + private void addStringParameter(String columnName, String parameter, StringBuilder parameters) { + addParameter0(columnName, "'" + parameter.replaceAll("'", "''") + "'", parameters); + } + + private void addDateParameter(String columnName, Date parameter, StringBuilder parameters) { + addParameter0(columnName, "'" + parameter + "'", parameters); + } + + private void addEnumParameter(String columnName, Enum parameter, StringBuilder parameters) { + addParameter0(columnName, "" + parameter.ordinal(), parameters); + } + + private void addOtherTypeParameter(String columnName, Object parameter, StringBuilder parameters) { + addParameter0(columnName, "" + parameter, parameters); + } + + private void addReferentialReferenceParameter(String columnName, ReferentialReference parameter, StringBuilder parameters) { + addStringParameter(columnName, parameter.getId(), parameters); + } + + private void addReferentialDtoParameter(String columnName, ReferentialDto parameter, StringBuilder parameters) { + addStringParameter(columnName, parameter.getId(), parameters); + } + + private void addParameter0(String columnName, String value, StringBuilder parameters) { + parameters.append(", SET ").append(columnName).append(" = ").append(value); + } +} -- To stop receiving notification emails like this one, please contact codelutin.com SCM administrator <admin+scm@codelutin.com>.