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 java.sql.Connection; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; import java.sql.Types; import java.util.ArrayList; import java.util.Arrays; import java.util.Collections; import java.util.List; import java.util.Map; public class Plate { public static final String TABLE_NAME = "plates"; public enum CONTENT_TYPE { LCMS, STANDARD, DELIVERED_STRAIN, INDUCTION, PREGROWTH, FEEDING_LCMS, } private enum DB_FIELD implements DBFieldEnumeration { ID(1, -1, "id"), NAME(2, 1, "name"), DESCRIPTION(3, 2, "description"), BARCODE(4, 3, "barcode"), LOCATION(5, 4, "location"), PLATE_TYPE(6, 5, "plate_type"), SOLVENT(7, 6, "solvent"), TEMPERATURE(8, 7, "temperature"), CONTENT_TYPE(9, 8, "content_type") // proteins and ko_locus are ignored for now. ; 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; } } // TODO: it might be easier to use parts of Spring-Standalone to do named binding in these queries. 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 = Collections .unmodifiableList(ALL_FIELDS.subList(1, ALL_FIELDS.size())); protected static List<Plate> platesFromResultSet(ResultSet resultSet) throws SQLException { List<Plate> results = new ArrayList<>(); while (resultSet.next()) { Integer id = resultSet.getInt(DB_FIELD.ID.getOffset()); String name = resultSet.getString(DB_FIELD.NAME.getOffset()); String description = resultSet.getString(DB_FIELD.DESCRIPTION.getOffset()); String barcode = resultSet.getString(DB_FIELD.BARCODE.getOffset()); String location = resultSet.getString(DB_FIELD.LOCATION.getOffset()); String plateType = resultSet.getString(DB_FIELD.PLATE_TYPE.getOffset()); String solvent = resultSet.getString(DB_FIELD.SOLVENT.getOffset()); Integer temperature = resultSet.getInt(DB_FIELD.TEMPERATURE.getOffset()); if (resultSet.wasNull()) { temperature = null; } CONTENT_TYPE contentType = CONTENT_TYPE.valueOf(resultSet.getString(DB_FIELD.CONTENT_TYPE.getOffset())); results.add(new Plate(id, name, description, barcode, location, plateType, solvent, temperature, contentType)); } return results; } protected static Plate expectOneResult(ResultSet resultSet, String queryErrStr) throws SQLException { List<Plate> results = platesFromResultSet(resultSet); if (results.size() > 1) { throw new SQLException("Found multiple results where one or zero expected: %s", queryErrStr); } if (results.size() == 0) { return null; } return results.get(0); } // Select public static final String QUERY_GET_PLATE_BY_ID = StringUtils.join( new String[] { "SELECT", StringUtils.join(ALL_FIELDS, ", "), "from", TABLE_NAME, "where id = ?", }, " "); public static Plate getPlateById(DB db, Integer id) throws SQLException { try (PreparedStatement stmt = db.getConn().prepareStatement(QUERY_GET_PLATE_BY_ID)) { stmt.setInt(1, id); try (ResultSet resultSet = stmt.executeQuery()) { return expectOneResult(resultSet, String.format("id = %d", id)); } } } public static final String QUERY_GET_PLATE_BY_BARCODE = StringUtils.join( new String[] { "SELECT", StringUtils.join(ALL_FIELDS, ", "), "from", TABLE_NAME, "where barcode = ?", }, " "); public static Plate getPlateByBarcode(DB db, String barcode) throws SQLException { try (PreparedStatement stmt = db.getConn().prepareStatement(QUERY_GET_PLATE_BY_BARCODE)) { stmt.setString(1, barcode); try (ResultSet resultSet = stmt.executeQuery()) { return expectOneResult(resultSet, String.format("barcode = %s", barcode)); } } } public static final String QUERY_GET_PLATE_BY_NAME = StringUtils.join( new String[] { "SELECT", StringUtils.join(ALL_FIELDS, ", "), "from", TABLE_NAME, "where name = ?", }, " "); public static Plate getPlateByName(DB db, String name) throws SQLException { try (PreparedStatement stmt = db.getConn().prepareStatement(QUERY_GET_PLATE_BY_NAME)) { stmt.setString(1, name); try (ResultSet resultSet = stmt.executeQuery()) { return expectOneResult(resultSet, String.format("name = %s", name)); } } } public static final String QUERY_GET_ALL_PLATES_BY_TYPE = StringUtils .join(new String[] { "SELECT", StringUtils.join(ALL_FIELDS, ", "), "from", TABLE_NAME, "where", DB_FIELD.PLATE_TYPE.getFieldName(), "= ?", "order by barcode, id" }, " "); public static List<Plate> getPlatesByContentType(DB db, CONTENT_TYPE type) throws SQLException { try (PreparedStatement stmt = db.getConn().prepareStatement(QUERY_GET_ALL_PLATES_BY_TYPE)) { stmt.setString(1, type.name()); try (ResultSet resultSet = stmt.executeQuery()) { return platesFromResultSet(resultSet); } } } // Insert/Update public static final String QUERY_INSERT_PLATE = StringUtils.join(new String[] { "INSERT INTO", TABLE_NAME, "(", StringUtils.join(INSERT_UPDATE_FIELDS, ", "), ") VALUES (", "?,", // 1 = name "?,", // 2 = description "?,", // 3 = barcode "?,", // 4 = location "?,", // 5 = plate_type "?,", // 6 = solvent "?,", // 7 = temperature "?", // 8 = contentType ")" }, " "); protected static void bindInsertOrUpdateParameters(PreparedStatement stmt, String name, String description, String barcode, String location, String plateType, String solvent, Integer temperature, CONTENT_TYPE contentType) throws SQLException { stmt.setString(DB_FIELD.NAME.getInsertUpdateOffset(), trimAndComplain(name)); if (description != null) { stmt.setString(DB_FIELD.DESCRIPTION.getInsertUpdateOffset(), trimAndComplain(description)); } else { stmt.setNull(DB_FIELD.DESCRIPTION.getInsertUpdateOffset(), Types.VARCHAR); } if (barcode != null) { stmt.setString(DB_FIELD.BARCODE.getInsertUpdateOffset(), trimAndComplain(barcode)); } else { stmt.setNull(DB_FIELD.BARCODE.getInsertUpdateOffset(), Types.VARCHAR); } stmt.setString(DB_FIELD.LOCATION.getInsertUpdateOffset(), trimAndComplain(location)); stmt.setString(DB_FIELD.PLATE_TYPE.getInsertUpdateOffset(), trimAndComplain(plateType)); if (solvent != null) { stmt.setString(DB_FIELD.SOLVENT.getInsertUpdateOffset(), trimAndComplain(solvent)); } else { stmt.setNull(DB_FIELD.SOLVENT.getInsertUpdateOffset(), Types.VARCHAR); } if (temperature == null) { stmt.setNull(DB_FIELD.TEMPERATURE.getInsertUpdateOffset(), Types.INTEGER); } else { stmt.setInt(DB_FIELD.TEMPERATURE.getInsertUpdateOffset(), temperature); } stmt.setString(DB_FIELD.CONTENT_TYPE.getInsertUpdateOffset(), contentType.name()); } protected static void bindInsertOrUpdateParameters(PreparedStatement stmt, Plate plate) throws SQLException { bindInsertOrUpdateParameters(stmt, plate.getName(), plate.getDescription(), plate.getBarcode(), plate.getLocation(), plate.getPlateType(), plate.getSolvent(), plate.getTemperature(), plate.getContentType()); } public static Plate insertPlate(DB db, String name, String description, String barcode, String location, String plateType, String solvent, Integer temperature, CONTENT_TYPE contentType) throws SQLException { Connection conn = db.getConn(); try (PreparedStatement stmt = conn.prepareStatement(QUERY_INSERT_PLATE, Statement.RETURN_GENERATED_KEYS)) { bindInsertOrUpdateParameters(stmt, name, description, barcode, location, plateType, solvent, temperature, contentType); stmt.executeUpdate(); try (ResultSet resultSet = stmt.getGeneratedKeys()) { if (resultSet.next()) { // Get auto-generated id. int id = resultSet.getInt(1); return new Plate(id, name, description, barcode, location, plateType, solvent, temperature, contentType); } else { System.err.format("ERROR: could not retrieve autogenerated key for plate %s\n", name); return null; } } } } protected static final List<String> UPDATE_STATEMENT_FIELDS_AND_BINDINGS; static { List<String> fields = new ArrayList<>(INSERT_UPDATE_FIELDS.size()); for (String field : INSERT_UPDATE_FIELDS) { fields.add(String.format("%s = ?", field)); } UPDATE_STATEMENT_FIELDS_AND_BINDINGS = Collections.unmodifiableList(fields); } public static final String QUERY_UPDATE_PLATE_BY_ID = StringUtils.join(new String[] { "UPDATE ", TABLE_NAME, "SET", StringUtils.join(UPDATE_STATEMENT_FIELDS_AND_BINDINGS.iterator(), ", "), "WHERE", "id = ?", // 7 }, " "); public static boolean updatePlate(DB db, Plate plate) throws SQLException { Connection conn = db.getConn(); try (PreparedStatement stmt = conn.prepareStatement(QUERY_UPDATE_PLATE_BY_ID)) { bindInsertOrUpdateParameters(stmt, plate); stmt.setInt(UPDATE_STATEMENT_FIELDS_AND_BINDINGS.size() + 1, plate.getId()); return stmt.executeUpdate() > 0; } } public static Plate getOrInsertFromPlateComposition(DB db, PlateCompositionParser parser, CONTENT_TYPE contentType) throws SQLException { Plate p = Plate.getPlateByName(db, parser.getPlateProperties().get("name")); if (p == null) { Map<String, String> attrs = parser.getPlateProperties(); // TODO: check for errors and make these proper constants. String tempStr = attrs.get("temperature"); if (tempStr == null || tempStr.isEmpty()) { tempStr = attrs.get("storage"); } Integer temperature = tempStr == null || tempStr.isEmpty() ? null : Integer.parseInt(trimAndComplain(tempStr)); p = Plate.insertPlate(db, attrs.get("name"), attrs.get("description"), attrs.get("barcode"), attrs.get("location"), attrs.get("plate_type"), attrs.get("solvent"), temperature, contentType); } return p; } public static String trimAndComplain(String val) { String tval = val.trim(); if (!val.equals(tval)) { System.err.format("WARNING: trimmed spurious whitespace from '%s'\n", val); } return tval; } // Class Definition private Integer id; private String name; private String description; private String barcode; private String location; private String plateType; private String solvent; private Integer temperature; private CONTENT_TYPE contentType; protected Plate(Integer id, String name, String description, String barcode, String location, String plateType, String solvent, Integer temperature, CONTENT_TYPE contentType) { this.id = id; this.name = name; this.description = description; this.barcode = barcode; this.location = location; this.plateType = plateType; this.solvent = solvent; this.temperature = temperature; this.contentType = contentType; } public Integer getId() { return id; } public String getName() { return name; } public void setName(String name) { this.name = name; } public String getDescription() { return description; } public void setDescription(String description) { this.description = description; } public String getBarcode() { return barcode; } public void setBarcode(String barcode) { this.barcode = barcode; } public String getLocation() { return location; } public void setLocation(String location) { this.location = location; } public String getPlateType() { return plateType; } public void setPlateType(String plateType) { this.plateType = plateType; } public String getSolvent() { return solvent; } public void setSolvent(String solvent) { this.solvent = solvent; } public Integer getTemperature() { return temperature; } public void setTemperature(Integer temperature) { this.temperature = temperature; } public CONTENT_TYPE getContentType() { return contentType; } public void setContentType(CONTENT_TYPE contentType) { this.contentType = contentType; } @Override public boolean equals(Object o) { if (this == o) return true; if (o == null || getClass() != o.getClass()) return false; Plate plate = (Plate) o; if (!id.equals(plate.id)) return false; if (!name.equals(plate.name)) return false; if (description != null ? !description.equals(plate.description) : plate.description != null) return false; if (barcode != null ? !barcode.equals(plate.barcode) : plate.barcode != null) return false; if (!location.equals(plate.location)) return false; if (!plateType.equals(plate.plateType)) return false; if (solvent != null ? !solvent.equals(plate.solvent) : plate.solvent != null) return false; if (temperature != null ? !temperature.equals(plate.temperature) : plate.temperature != null) return false; return contentType == plate.contentType; } @Override public int hashCode() { int result = id.hashCode(); result = 31 * result + name.hashCode(); result = 31 * result + (description != null ? description.hashCode() : 0); result = 31 * result + (barcode != null ? barcode.hashCode() : 0); result = 31 * result + location.hashCode(); result = 31 * result + plateType.hashCode(); result = 31 * result + (solvent != null ? solvent.hashCode() : 0); result = 31 * result + (temperature != null ? temperature.hashCode() : 0); result = 31 * result + contentType.hashCode(); return result; } }