Java tutorial
/************************************************************************* * * * This file is part of the 20n/act project. * * 20n/act enables DNA prediction for synthetic biology/bioengineering. * * Copyright (C) 2017 20n Labs, Inc. * * * * Please direct all queries to act@20n.com. * * * * This program is free software: you can redistribute it and/or modify * * it under the terms of the GNU General Public License as published by * * the Free Software Foundation, either version 3 of the License, or * * (at your option) any later version. * * * * This program is distributed in the hope that it will be useful, * * but WITHOUT ANY WARRANTY; without even the implied warranty of * * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the * * GNU General Public License for more details. * * * * You should have received a copy of the GNU General Public License * * along with this program. If not, see <http://www.gnu.org/licenses/>. * * * *************************************************************************/ package com.act.lcms.db.model; import com.act.lcms.db.io.DB; import com.act.lcms.db.io.parser.PlateCompositionParser; import org.apache.commons.lang3.StringUtils; import org.apache.commons.lang3.tuple.Pair; import java.io.IOException; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Types; import java.util.ArrayList; import java.util.Arrays; import java.util.Collections; import java.util.Comparator; import java.util.List; import java.util.Map; public class StandardWell extends PlateWell<StandardWell> { protected static final StandardWell INSTANCE = new StandardWell(); public static StandardWell getInstance() { return INSTANCE; } public static final String TABLE_NAME = "wells_standard"; public enum MEDIA_TYPE { WATER, MEOH, YEAST }; private enum DB_FIELD implements DBFieldEnumeration { ID(1, -1, "id"), PLATE_ID(2, 1, "plate_id"), PLATE_ROW(3, 2, "plate_row"), PLATE_COLUMN(4, 3, "plate_column"), CHEMICAL(5, 4, "chemical"), MEDIA(6, 5, "media"), NOTE(7, 6, "note"), CONCENTRATION(8, 7, "concentration"),; private final int offset; private final int insertUpdateOffset; private final String fieldName; DB_FIELD(int offset, int insertUpdateOffset, String fieldName) { this.offset = offset; this.insertUpdateOffset = insertUpdateOffset; this.fieldName = fieldName; } @Override public int getOffset() { return offset; } @Override public int getInsertUpdateOffset() { return insertUpdateOffset; } @Override public String getFieldName() { return fieldName; } @Override public String toString() { return this.fieldName; } public static String[] names() { DB_FIELD[] values = DB_FIELD.values(); String[] names = new String[values.length]; for (int i = 0; i < values.length; i++) { names[i] = values[i].getFieldName(); } return names; } } protected static final List<String> ALL_FIELDS = Collections.unmodifiableList(Arrays.asList(DB_FIELD.names())); // id is auto-generated on insertion. protected static final List<String> INSERT_UPDATE_FIELDS = INSTANCE.makeInsertUpdateFields(); @Override public String getTableName() { return TABLE_NAME; } @Override public List<String> getAllFields() { return ALL_FIELDS; } @Override public List<String> getInsertUpdateFields() { return INSERT_UPDATE_FIELDS; } protected static final String GET_BY_ID_QUERY = INSTANCE.makeGetByIDQuery(); @Override protected String getGetByIDQuery() { return GET_BY_ID_QUERY; } protected static final String GET_BY_PLATE_ID_QUERY = INSTANCE.makeGetByPlateIDQuery(); @Override protected String getGetByPlateIDQuery() { return GET_BY_PLATE_ID_QUERY; } protected static final String INSERT_QUERY = INSTANCE.makeInsertQuery(); @Override public String getInsertQuery() { return INSERT_QUERY; } protected static final String UPDATE_QUERY = INSTANCE.makeUpdateQuery(); @Override public String getUpdateQuery() { return UPDATE_QUERY; } @Override protected List<StandardWell> fromResultSet(ResultSet resultSet) throws SQLException { List<StandardWell> results = new ArrayList<>(); while (resultSet.next()) { Integer id = resultSet.getInt(DB_FIELD.ID.getOffset()); Integer plateId = resultSet.getInt(DB_FIELD.PLATE_ID.getOffset()); Integer plateRow = resultSet.getInt(DB_FIELD.PLATE_ROW.getOffset()); Integer plateColumn = resultSet.getInt(DB_FIELD.PLATE_COLUMN.getOffset()); String chemical = resultSet.getString(DB_FIELD.CHEMICAL.getOffset()); String media = resultSet.getString(DB_FIELD.MEDIA.getOffset()); String note = resultSet.getString(DB_FIELD.NOTE.getOffset()); Double concentration = resultSet.getDouble(DB_FIELD.CONCENTRATION.getOffset()); if (resultSet.wasNull()) { concentration = null; } results.add(new StandardWell(id, plateId, plateRow, plateColumn, chemical, media, note, concentration)); } return results; } public static Boolean isMediaWater(String media) { return media.toLowerCase().contains("water") || media.contains("h20"); } public static Boolean doesMediaContainYeastExtract(String media) { //TODO: Update this check once we have a new media ID that also corresponds to containing yeast extract. return media.toLowerCase().contains("gal"); } public static Boolean isMediaMeOH(String media) { return media.toLowerCase().contains("meoh"); } public static final String QUERY_GET_STANDARD_WELL_BY_PLATE_ID_AND_COORDINATES = StringUtils.join(new String[] { "SELECT", StringUtils.join(INSTANCE.getAllFields(), ','), "from", INSTANCE.getTableName(), "where plate_id = ?", " and plate_row = ?", " and plate_column = ?", }, " "); public StandardWell getStandardWellsByPlateIdAndCoordinates(DB db, Integer plateId, Integer plateRow, Integer plateColumn) throws SQLException, IOException, ClassNotFoundException { try (PreparedStatement stmt = db.getConn() .prepareStatement(QUERY_GET_STANDARD_WELL_BY_PLATE_ID_AND_COORDINATES)) { stmt.setInt(1, plateId); stmt.setInt(2, plateRow); stmt.setInt(3, plateColumn); try (ResultSet resultSet = stmt.executeQuery()) { return expectOneResult(resultSet, String.format("plate_id = %d, plate_row = %d, plate_column = %d", plateId, plateRow, plateColumn)); } } } public static final String QUERY_GET_STANDARD_WELLS_BY_CHEMICAL = StringUtils.join( new String[] { "SELECT", StringUtils.join(INSTANCE.getAllFields(), ','), "from", INSTANCE.getTableName(), "where chemical = ?", "order by plate_id, plate_row, plate_column" }, " "); public List<StandardWell> getStandardWellsByChemical(DB db, String chemical) throws SQLException { try (PreparedStatement stmt = db.getConn().prepareStatement(QUERY_GET_STANDARD_WELLS_BY_CHEMICAL)) { stmt.setString(1, chemical); try (ResultSet resultSet = stmt.executeQuery()) { return fromResultSet(resultSet); } } } public static final String QUERY_GET_STANDARD_WELLS_BY_CHEMICAL_AND_PLATE_ID = StringUtils.join(new String[] { "SELECT", StringUtils.join(INSTANCE.getAllFields(), ','), "from", INSTANCE.getTableName(), "where chemical = ?", " and plate_id = ?", "order by plate_id, plate_row, plate_column" }, " "); public List<StandardWell> getStandardWellsByChemicalAndPlateId(DB db, String chemical, Integer plateId) throws SQLException { try (PreparedStatement stmt = db.getConn() .prepareStatement(QUERY_GET_STANDARD_WELLS_BY_CHEMICAL_AND_PLATE_ID)) { stmt.setString(1, chemical); stmt.setInt(2, plateId); try (ResultSet resultSet = stmt.executeQuery()) { return fromResultSet(resultSet); } } } public static final String QUERY_GET_STANDARD_WELLS_BY_CHEMICAL_AND_PLATE_ID_AND_MEDIUM = StringUtils .join(new String[] { "SELECT", StringUtils.join(INSTANCE.getAllFields(), ','), "from", INSTANCE.getTableName(), "where chemical = ?", " and plate_id = ?", " and media = ?", "order by plate_id, plate_row, plate_column" }, " "); public List<StandardWell> getStandardWellsByChemicalAndPlateIdAndMedium(DB db, String chemical, Integer plateId, String medium) throws SQLException { try (PreparedStatement stmt = db.getConn() .prepareStatement(QUERY_GET_STANDARD_WELLS_BY_CHEMICAL_AND_PLATE_ID_AND_MEDIUM)) { stmt.setString(1, chemical); stmt.setInt(2, plateId); stmt.setString(3, medium); try (ResultSet resultSet = stmt.executeQuery()) { return fromResultSet(resultSet); } } } // Insert/update protected void bindInsertOrUpdateParameters(PreparedStatement stmt, Integer plateId, Integer plateRow, Integer plateColumn, String chemical, String media, String note, Double concentration) throws SQLException { stmt.setInt(DB_FIELD.PLATE_ID.getInsertUpdateOffset(), plateId); stmt.setInt(DB_FIELD.PLATE_ROW.getInsertUpdateOffset(), plateRow); stmt.setInt(DB_FIELD.PLATE_COLUMN.getInsertUpdateOffset(), plateColumn); stmt.setString(DB_FIELD.CHEMICAL.getInsertUpdateOffset(), chemical); stmt.setString(DB_FIELD.MEDIA.getInsertUpdateOffset(), media); stmt.setString(DB_FIELD.NOTE.getInsertUpdateOffset(), note); if (concentration != null) { stmt.setDouble(DB_FIELD.CONCENTRATION.getInsertUpdateOffset(), concentration); } else { stmt.setNull(DB_FIELD.CONCENTRATION.getInsertUpdateOffset(), Types.DOUBLE); } } @Override protected void bindInsertOrUpdateParameters(PreparedStatement stmt, StandardWell sw) throws SQLException { bindInsertOrUpdateParameters(stmt, sw.getPlateId(), sw.getPlateRow(), sw.getPlateColumn(), sw.getChemical(), sw.getMedia(), sw.getNote(), sw.getConcentration()); } public StandardWell insert(DB db, Integer plateId, Integer plateRow, Integer plateColumn, String chemical, String media, String note, Double concentration) throws SQLException, IOException { return INSTANCE.insert(db, new StandardWell(null, plateId, plateRow, plateColumn, chemical, media, note, concentration)); } // Parsing/loading public List<StandardWell> insertFromPlateComposition(DB db, PlateCompositionParser parser, Plate p) throws SQLException, IOException { Map<Pair<String, String>, String> msids = parser.getCompositionTables().get("chemical"); List<Pair<String, String>> sortedCoordinates = new ArrayList<>(msids.keySet()); Collections.sort(sortedCoordinates, new Comparator<Pair<String, String>>() { // TODO: parse the values of these pairs as we read them so we don't need this silly comparator. @Override public int compare(Pair<String, String> o1, Pair<String, String> o2) { if (o1.getKey().equals(o2.getKey())) { return Integer.valueOf(Integer.parseInt(o1.getValue())) .compareTo(Integer.parseInt(o2.getValue())); } return o1.getKey().compareTo(o2.getKey()); } }); List<StandardWell> results = new ArrayList<>(); for (Pair<String, String> coords : sortedCoordinates) { String chemical = parser.getCompositionTables().get("chemical").get(coords); if (chemical == null || chemical.isEmpty()) { continue; } Map<Pair<String, String>, String> mediaMap = parser.getCompositionTables().get("media"); if (mediaMap == null) { mediaMap = parser.getCompositionTables().get("solvent"); } String media = mediaMap != null ? mediaMap.get(coords) : null; Map<Pair<String, String>, String> notesMap = parser.getCompositionTables().get("note"); String note = notesMap != null ? notesMap.get(coords) : null; Pair<Integer, Integer> index = parser.getCoordinatesToIndices().get(coords); Map<Pair<String, String>, String> concentrationsMap = parser.getCompositionTables() .get("concentration"); Double concentration = concentrationsMap != null ? Double.parseDouble(concentrationsMap.get(coords)) : null; StandardWell s = INSTANCE.insert(db, p.getId(), index.getLeft(), index.getRight(), chemical, media, note, concentration); results.add(s); } return results; } private String chemical; private String media; private String note; private Double concentration; private StandardWell() { } protected StandardWell(Integer id, Integer plateId, Integer plateRow, Integer plateColumn, String chemical, String media, String note, Double concentration) { this.id = id; this.plateId = plateId; this.plateRow = plateRow; this.plateColumn = plateColumn; this.chemical = chemical; this.media = media; this.note = note; this.concentration = concentration; } public String getChemical() { return chemical; } public void setChemical(String chemical) { this.chemical = chemical; } public String getMedia() { return media; } public void setMedia(String media) { this.media = media; } public String getNote() { return note; } public void setNote(String note) { this.note = note; } public Double getConcentration() { return concentration; } @Override public boolean equals(Object o) { if (this == o) return true; if (o == null || getClass() != o.getClass()) return false; StandardWell that = (StandardWell) o; // TODO: what to do about INSTANCE? // TODO: move the BaseDBModel and PlateWell comparisons to the appropriate superclasses. // Note that we could probably just restrict this to use plate id, row, and column since those should be unique. if (this.id != null && that.id != null && !this.id.equals(that.id)) return false; if (!this.plateId.equals(that.plateId)) return false; if (!this.plateRow.equals(that.plateRow)) return false; if (!this.plateColumn.equals(that.plateColumn)) return false; if (!chemical.equals(that.chemical)) return false; if (media != null ? !media.equals(that.media) : that.media != null) return false; if (note != null ? !note.equals(that.note) : that.note != null) return false; return !(concentration != null ? !concentration.equals(that.concentration) : that.concentration != null); } @Override public int hashCode() { int result = id == null ? 0 : id.hashCode(); result = 31 * result + plateId.hashCode(); result = 31 * result + plateRow.hashCode(); result = 31 * result + plateColumn.hashCode(); result = 31 * result + chemical.hashCode(); result = 31 * result + (media != null ? media.hashCode() : 0); result = 31 * result + (note != null ? note.hashCode() : 0); result = 31 * result + (concentration != null ? concentration.hashCode() : 0); return result; } }