This is an automated email from the git hooks/post-receive script. New commit to branch develop in repository echobase. See https://gitlab.nuiton.org/codelutin/echobase.git commit 38abb5d4c05b8ffc51961fa1699d973eb73691ec Author: Julien Ruchaud <julien.ruchaud@debux.org> Date: Mon Jun 27 16:22:50 2016 +0200 Improve the perfomance on search duplicate cell (ref #8165) --- .../echobase/entities/data/CellTopiaDao.java | 54 ++++++++++++++++++++++ .../WorkingDbMigrationCallBackForVersion3_906.java | 33 +++++++++++++ .../migration/workingDb/3.906-add-indexes.sql | 1 + .../services/service/UserDbPersistenceService.java | 2 +- .../src/test/resources/log4j.properties | 1 + 5 files changed, 90 insertions(+), 1 deletion(-) diff --git a/echobase-domain/src/main/java/fr/ifremer/echobase/entities/data/CellTopiaDao.java b/echobase-domain/src/main/java/fr/ifremer/echobase/entities/data/CellTopiaDao.java index 87f1d39..1b0a66d 100644 --- a/echobase-domain/src/main/java/fr/ifremer/echobase/entities/data/CellTopiaDao.java +++ b/echobase-domain/src/main/java/fr/ifremer/echobase/entities/data/CellTopiaDao.java @@ -53,6 +53,19 @@ public class CellTopiaDao extends AbstractCellTopiaDao<Cell> { return topiaSqlSupport.findMultipleResult(query); } + public List<String> getMooringCellIds(Mooring mooring) throws TopiaException { + TopiaSqlQuery<String> query = newMooringCellIdsQuery(mooring); + + return topiaSqlSupport.findMultipleResult(query); + } + + public Boolean containsCellByName(String name) throws TopiaException { + TopiaSqlQuery<Boolean> query = containsCellByNameQuery(name); + + Boolean result = topiaSqlSupport.findSingleResult(query); + return result != null; + } + protected TopiaSqlQuery<Long> newCountVoyageOrphanCellsQuery(final Voyage voyage) { return new TopiaSqlQuery<Long>() { @Override @@ -147,4 +160,45 @@ public class CellTopiaDao extends AbstractCellTopiaDao<Cell> { } }; } + + protected TopiaSqlQuery<String> newMooringCellIdsQuery(Mooring mooring) { + return new TopiaSqlQuery<String>() { + @Override + public PreparedStatement prepareQuery(Connection connection) throws SQLException { + String hql = "SELECT c.topiaid FROM DataAcquisition da, " + + " DataProcessing dp, " + + " Cell c " + + "WHERE da.mooring = ? " + + "AND da.topiaId = dp.dataacquisition " + + "AND dp.topiaId = c.dataprocessing"; + PreparedStatement result = connection.prepareStatement(hql); + result.setString(1, mooring.getTopiaId()); + return result; + } + + @Override + public String prepareResult(ResultSet set) throws SQLException { + return set.getString(1); + } + }; + } + + protected TopiaSqlQuery<Boolean> containsCellByNameQuery(String name) { + return new TopiaSqlQuery<Boolean>() { + @Override + public PreparedStatement prepareQuery(Connection connection) throws SQLException { + String hql = "SELECT 1 FROM Cell c " + + "WHERE c.name = ? " + + "LIMIT 1"; + PreparedStatement result = connection.prepareStatement(hql); + result.setString(1, name); + return result; + } + + @Override + public Boolean prepareResult(ResultSet set) throws SQLException { + return set.getInt(1) == 1; + } + }; + } } diff --git a/echobase-domain/src/main/java/fr/ifremer/echobase/persistence/migration/workingDb/WorkingDbMigrationCallBackForVersion3_906.java b/echobase-domain/src/main/java/fr/ifremer/echobase/persistence/migration/workingDb/WorkingDbMigrationCallBackForVersion3_906.java new file mode 100644 index 0000000..0677dae --- /dev/null +++ b/echobase-domain/src/main/java/fr/ifremer/echobase/persistence/migration/workingDb/WorkingDbMigrationCallBackForVersion3_906.java @@ -0,0 +1,33 @@ +package fr.ifremer.echobase.persistence.migration.workingDb; + +import org.nuiton.topia.persistence.TopiaException; +import org.nuiton.topia.persistence.support.TopiaSqlSupport; +import org.nuiton.version.Version; +import org.nuiton.version.Versions; + +import java.util.List; + +/** + * Created on 26/04/16. + * + * @author Julien Ruchaud - ruchaud@codelutin.com + * @since 4.0 + */ +public class WorkingDbMigrationCallBackForVersion3_906 extends WorkingDbMigrationCallBackForVersionSupport { + + @Override + public Version getVersion() { + return Versions.valueOf("3.907"); + } + + @Override + protected void prepareMigrationScript(TopiaSqlSupport sqlSupport, + List<String> queries, + boolean showSql, + boolean showProgression) throws TopiaException { + + // update the model structure + addScript("3.906-add-indexes.sql", queries); + } + +} diff --git a/echobase-domain/src/main/resources/migration/workingDb/3.906-add-indexes.sql b/echobase-domain/src/main/resources/migration/workingDb/3.906-add-indexes.sql new file mode 100644 index 0000000..e2ba3ad --- /dev/null +++ b/echobase-domain/src/main/resources/migration/workingDb/3.906-add-indexes.sql @@ -0,0 +1 @@ +CREATE INDEX IDX_CELL_NAME ON CELL (NAME); diff --git a/echobase-services/src/main/java/fr/ifremer/echobase/services/service/UserDbPersistenceService.java b/echobase-services/src/main/java/fr/ifremer/echobase/services/service/UserDbPersistenceService.java index 39b9a02..ac72bf8 100644 --- a/echobase-services/src/main/java/fr/ifremer/echobase/services/service/UserDbPersistenceService.java +++ b/echobase-services/src/main/java/fr/ifremer/echobase/services/service/UserDbPersistenceService.java @@ -218,7 +218,7 @@ public class UserDbPersistenceService extends EchoBaseServiceSupport { //------------------------------------------------------------------------// public boolean containsCellByName(String cellName) { - return persistenceContext.getCellDao().forNameEquals(cellName).exists(); + return persistenceContext.getCellDao().containsCellByName(cellName); } public boolean containsPostVoyageCellByName(Voyage voyage, String cellName) { diff --git a/echobase-services/src/test/resources/log4j.properties b/echobase-services/src/test/resources/log4j.properties index c4899ef..c9de6bb 100644 --- a/echobase-services/src/test/resources/log4j.properties +++ b/echobase-services/src/test/resources/log4j.properties @@ -28,3 +28,4 @@ log4j.appender.stdout.layout.ConversionPattern=%d{dd MMM yyyy HH:mm:ss} %5p [%t] # package level log4j.logger.fr.ifremer.echobase=INFO log4j.logger.org.nuiton.topia.migration=INFO +#log4j.logger.org.hibernate.SQL=DEBUG -- To stop receiving notification emails like this one, please contact codelutin.com SCM administrator <admin+scm@codelutin.com>.