com.act.lcms.db.model.Plate.java Source code

Java tutorial

Introduction

Here is the source code for com.act.lcms.db.model.Plate.java

Source

/*************************************************************************
*                                                                        *
*  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;
    }
}