Java tutorial
// $HeadURL$ // $Id$ // Copyright 2006, 2010, 2011, 2012 by the President and Fellows of Harvard College. // Screensaver is an open-source project developed by the ICCB-L and NSRB labs // at Harvard Medical School. This software is distributed under the terms of // the GNU General Public License. package edu.harvard.med.screensaver.db; import java.util.List; import java.util.Map; import com.google.common.collect.Maps; import com.google.common.collect.Sets; import org.apache.commons.collections.Transformer; import org.apache.log4j.Logger; import org.hibernate.CacheMode; import org.hibernate.Query; import org.hibernate.ScrollMode; import org.hibernate.ScrollableResults; import org.hibernate.Session; import edu.harvard.med.screensaver.db.Criterion.Operator; import edu.harvard.med.screensaver.db.hqlbuilder.HqlBuilder; import edu.harvard.med.screensaver.db.hqlbuilder.JoinType; import edu.harvard.med.screensaver.model.DataModelViolationException; import edu.harvard.med.screensaver.model.libraries.LibraryWellType; import edu.harvard.med.screensaver.model.libraries.Reagent; import edu.harvard.med.screensaver.model.libraries.Well; import edu.harvard.med.screensaver.model.libraries.WellKey; import edu.harvard.med.screensaver.model.screenresults.AnnotationType; import edu.harvard.med.screensaver.model.screenresults.AnnotationValue; import edu.harvard.med.screensaver.model.screenresults.AssayWell; import edu.harvard.med.screensaver.model.screenresults.DataColumn; import edu.harvard.med.screensaver.model.screenresults.DataType; import edu.harvard.med.screensaver.model.screenresults.ResultValue; import edu.harvard.med.screensaver.model.screenresults.ScreenResult; import edu.harvard.med.screensaver.model.screens.Screen; import edu.harvard.med.screensaver.model.screens.ScreenType; import edu.harvard.med.screensaver.util.CollectionUtils; public class ScreenResultsDAOImpl extends AbstractDAO implements ScreenResultsDAO { private static Logger log = Logger.getLogger(ScreenResultsDAOImpl.class); private GenericEntityDAO _dao; /** * @motivation for CGLIB dynamic proxy creation */ public ScreenResultsDAOImpl() { } public ScreenResultsDAOImpl(GenericEntityDAO dao) { _dao = dao; } public Map<WellKey, ResultValue> findResultValuesByPlate(final Integer plateNumber, final DataColumn col) { List<ResultValue> result = runQuery(new edu.harvard.med.screensaver.db.Query() { public List<?> execute(Session session) { // NOTE: added fetch of the library into the session to fix lazy update problem when calling rv.isEdgeWell()->well.isEdgeWell() which needs the library see: [#1376]- sde4 String hql = "select r from ResultValue r " + "left join fetch r.well w join fetch w.library l " + "where r.dataColumn.id = :colId and w.id >= :firstWellInclusive " + "and w.id < :lastWellExclusive"; Query query = session.createQuery(hql); query.setParameter("colId", col.getEntityId()); query.setParameter("firstWellInclusive", new WellKey(plateNumber, 0, 0).toString()); query.setParameter("lastWellExclusive", new WellKey(plateNumber + 1, 0, 0).toString()); return query.list(); } }); Map<WellKey, ResultValue> result2 = CollectionUtils.indexCollection(result, // note: calling rv.getWell().getWellId() does *not* require a db hit, since // a proxy can return its ID w/o forcing Hibernate to access the db; // so we use the id to instantiate the WellKey new Transformer() { public Object transform(Object rv) { return new WellKey(((ResultValue) rv).getWell().getWellId()); } }, WellKey.class, ResultValue.class); return result2; } public List<DataColumn> findMutualPositiveColumns(final ScreenResult screenResult) { return findMutualPositiveColumns(new HqlBuilderCallback() { @Override public void apply(HqlBuilder hql) { hql.where("aw1", "screenResult", Operator.EQUAL, screenResult).where("aw2", "screenResult", Operator.NOT_EQUAL, screenResult); // ignore data columns from the screen result passed in } }); } public List<DataColumn> findMutualPositiveColumns(final HqlBuilderCallback hqlBuilderCallback) { edu.harvard.med.screensaver.db.Query query = new edu.harvard.med.screensaver.db.Query() { public List<?> execute(Session session) { HqlBuilder hql = new HqlBuilder().select("dc").distinctProjectionValues() .from(AssayWell.class, "aw1").from(AssayWell.class, "aw2") .from("aw2", AssayWell.screenResult, "sr2", JoinType.INNER) .from("sr2", ScreenResult.dataColumns, "dc", JoinType.INNER) .where("aw1", "libraryWell", Operator.EQUAL, "aw2", "libraryWell") .where("aw1", "positive", Operator.EQUAL, Boolean.TRUE) .where("aw2", "positive", Operator.EQUAL, Boolean.TRUE) .whereIn("dc", "dataType", Sets.newHashSet(DataType.POSITIVE_INDICATOR_BOOLEAN, DataType.POSITIVE_INDICATOR_PARTITION)); hqlBuilderCallback.apply(hql); return hql.toQuery(session, true).list(); } }; return runQuery(query); } public void deleteScreenResult(ScreenResult screenResultIn) { Session session = getHibernateSession(); ScreenResult screenResult = (ScreenResult) session.get(ScreenResult.class, screenResultIn.getEntityId()); log.info("delete Assay Wells"); Query query = session.createQuery("delete AssayWell a where a.screenResult.id = :screenResultId"); query.setParameter("screenResultId", screenResult.getScreenResultId()); int rows = query.executeUpdate(); log.info("deleted " + rows + " AssayWells for " + screenResult); screenResult.getAssayWells().clear(); log.info("delete Assay Plates to be orphaned (i.e., without library screenings)"); query = session .createQuery("delete AssayPlate ap where ap.screen.id = :screenId and ap.libraryScreening is null"); query.setParameter("screenId", screenResult.getScreen().getScreenId()); rows = query.executeUpdate(); log.info("deleted " + rows + " AssayPlates for " + screenResult); log.info("delete ResultValues"); int cumRows = 0; query = session.createQuery("delete ResultValue v where v.dataColumn.id = :col"); for (DataColumn col : screenResult.getDataColumns()) { query.setParameter("col", col.getDataColumnId()); rows = query.executeUpdate(); cumRows += rows; log.debug("deleted " + rows + " result values for " + col); col.getResultValues().clear(); } log.info("deleted a total of " + cumRows + " result values"); //screenResult.getPlateNumbers().clear(); // dissociate ScreenResult from Screen screenResult.getScreen().clearScreenResult(); session.delete(screenResult); flush(); log.info("deleted " + screenResult); } //TODO: move to report class public int createScreenedReagentCounts(final ScreenType screenType, Screen study, AnnotationType positiveAnnotationType, AnnotationType overallAnnotationType) { // Break this into two separate queries because of Hibernate bug (http://opensource.atlassian.com/projects/hibernate/browse/HHH-1615): // when using the "group by" clause with a full object (as opposed to an attribute of the object/table), // Hibernate is requiring that every attribute of the object be specified in a "group by" and not // just the object itself. so the workaround is to query once to get the id's then once again to // get the objects. log.info("1. get the reagent id's for the positive counts"); ScrollableResults sr = runScrollQuery(new edu.harvard.med.screensaver.db.ScrollQuery() { public ScrollableResults execute(Session session) { HqlBuilder builder = new HqlBuilder(); builder.select("r", "id").selectExpression("count(*)").from(AssayWell.class, "aw") .from("aw", AssayWell.libraryWell, "w", JoinType.INNER) .from("w", Well.latestReleasedReagent, "r", JoinType.INNER) .from("w", Well.library, "l", JoinType.INNER) .where("l", "screenType", Operator.EQUAL, screenType) .where("w", "libraryWellType", Operator.EQUAL, LibraryWellType.EXPERIMENTAL); builder.where("aw", "positive", Operator.EQUAL, Boolean.TRUE); builder.groupBy("r", "id"); log.debug("hql: " + builder.toHql()); return builder.toQuery(session, true).setCacheMode(CacheMode.IGNORE) .scroll(ScrollMode.FORWARD_ONLY); } }); Map<Integer, Long> positivesMap = Maps.newHashMap(); while (sr.next()) { Object[] row = sr.get(); positivesMap.put((Integer) row[0], (Long) row[1]); } log.info("2. get the reagent id's for the overall counts"); sr = runScrollQuery(new edu.harvard.med.screensaver.db.ScrollQuery() { public ScrollableResults execute(Session session) { HqlBuilder builder = new HqlBuilder(); builder.select("r", "id").selectExpression("count(*)").from(AssayWell.class, "aw") .from("aw", AssayWell.libraryWell, "w", JoinType.INNER) .from("w", Well.library, "l", JoinType.INNER) .from("w", Well.latestReleasedReagent, "r", JoinType.INNER) .where("l", "screenType", Operator.EQUAL, screenType) .where("w", "libraryWellType", Operator.EQUAL, LibraryWellType.EXPERIMENTAL) .groupBy("r", "id"); log.debug("hql: " + builder.toHql()); return builder.toQuery(session, true).setCacheMode(CacheMode.IGNORE) .scroll(ScrollMode.FORWARD_ONLY); } }); Map<Integer, Long> overallMap = Maps.newHashMap(); while (sr.next()) { Object[] row = sr.get(); overallMap.put((Integer) row[0], (Long) row[1]); } log.info("3. get the Reagents"); sr = runScrollQuery(new edu.harvard.med.screensaver.db.ScrollQuery() { public ScrollableResults execute(Session session) { HqlBuilder builder = new HqlBuilder(); builder.select("r").distinctProjectionValues().from(AssayWell.class, "aw") .from("aw", AssayWell.libraryWell, "w", JoinType.INNER) .from("w", Well.library, "l", JoinType.INNER) .from("w", Well.latestReleasedReagent, "r", JoinType.INNER) .where("l", "screenType", Operator.EQUAL, screenType) .where("w", "libraryWellType", Operator.EQUAL, LibraryWellType.EXPERIMENTAL); log.debug("hql: " + builder.toHql()); return builder.toQuery(session, true).setCacheMode(CacheMode.IGNORE) .scroll(ScrollMode.FORWARD_ONLY); } }); log.info("4. build the Study: positives: " + positivesMap.size() + ", reagents: " + overallMap.size()); int count = 0; while (sr.next()) { Reagent r = (Reagent) sr.get()[0]; AnnotationValue av = new AnnotationValue(overallAnnotationType, r, null, (double) overallMap.get(r.getReagentId()).intValue()); _dao.saveOrUpdateEntity(av); Long positiveCount = positivesMap.get(r.getReagentId()); if (positiveCount != null) { av = new AnnotationValue(positiveAnnotationType, r, null, (double) positiveCount.intValue()); _dao.saveOrUpdateEntity(av); } // Note: due to memory performance, we will build the study_reagent_link later if (count++ % ROWS_TO_CACHE == 0) { log.debug("flushing"); _dao.flush(); _dao.clear(); } if (count % 10000 == 0) { log.info("" + count + " reagents processed"); } } log.info("save the study"); _dao.saveOrUpdateEntity(study); _dao.flush(); log.info("populateStudyReagentLinkTable"); int reagentCount = populateStudyReagentLinkTable(study.getScreenId()); log.info("done: positives: " + positivesMap.size() + ", reagents: " + overallMap.size()); return reagentCount; } public AssayWell findAssayWell(ScreenResult screenResult, WellKey wellKey) { List result = getHibernateSession() .createQuery("select a from AssayWell a where a.screenResult.id = ? and a.libraryWell.id = ?") .setParameter(0, screenResult.getEntityId()).setParameter(1, wellKey.toString()).list(); if (result.size() == 0) { return null; } if (result.size() > 1) { throw new DataModelViolationException("multiple assay wells found"); } return (AssayWell) result.get(0); } /** * Use SQL to populate the Study-to-Reagent link table: <br> * Reagents could be added to the study in java-hibernate, however, this * poses memory performance issues. */ //TODO: move this to the report class private int populateStudyReagentLinkTable(int screenId) { String sql = "insert into study_reagent_link " + "(study_id,reagent_id) " + "select :studyId as study_id, " + "reagent_id from " + "(select distinct(reagent_id) " + "from reagent " + "join annotation_value using(reagent_id) " + "join annotation_type using(annotation_type_id) " + "where study_id = :studyId ) a"; log.debug("sql: " + sql); javax.persistence.Query query = getEntityManager().createNativeQuery(sql); query.setParameter("studyId", screenId); int rows = query.executeUpdate(); log.info("study_reagent_link updated: " + rows); return rows; } public ScreenResult getLatestScreenResult() { List<ScreenResult> result = new HqlBuilder().select("sr").distinctProjectionValues() .from(ScreenResult.class, "sr").orderBy("sr", SortDirection.DESCENDING) .toQuery(getHibernateSession(), true).list(); if (result == null || result.isEmpty()) return null; return result.get(0); } }